索引类似书的目录，可以快速定位

一，查看表格索引

mysql> show keys from app_user_info;
mysql> show index from app_user_info;

字段解释：
· Table
表的名称。
· Non_unique
如果索引不能包括重复词，则为0。如果可以，则为1。
· Key_name
索引的名称。
· Seq_in_index
索引中的列序列号，从1开始。
· Column_name
列名称。
· Collation
列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
· Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机 会就越大。
· Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
· Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。
· Null
如果列含有NULL，则含有YES。如果没有，则该列含有NO。
· Index_type
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
· Comment

二，查看SQL是否使用索引

mysql> explain select * from `app_user_info` a  where a.dev_code = '5100823332';
+----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | a     | const | dev_code      | dev_code | 33      | const |    1 |       |
+----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+
1 row in set (0.00 sec)

解释几个主要字段：
1，possible_keys ：可能会使用到的索引，按需建立索引；

2，key ：真正使用到的索引，使用索引效率更高；

3，rows ：需要扫描的行数，越少越好；

4，type ：按照最优排序，常见类型包括

· const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次，上面的例子就是，使用索引直接取指定值；

· eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型，比如使用索引进行左链接

mysql> explain select * from `app_user_info` a LEFT JOIN `app_develop_staff_info` b on a.dev_code = b.dev_code;
+----+-------------+-------+--------+----------------------+---------+---------+---------------+------+-------+
| id | select_type | table | type   | possible_keys        | key     | key_len | ref           | rows | Extra |
+----+-------------+-------+--------+----------------------+---------+---------+---------------+------+-------+
|  1 | SIMPLE      | a     | ALL    | NULL                 | NULL    | NULL    | NULL          |   82 |       |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY,IDX_DEV_CODE | PRIMARY | 50      | gd.a.dev_code |    1 |       |
+----+-------------+-------+--------+----------------------+---------+---------+---------------+------+-------+
2 rows in set (0.00 sec)

· range:只检索给定范围的行,使用一个索引来选择符合条件的行，比如使用索引取范围值的行

mysql> explain select * from `app_user_info` a  where a.dev_code <= '5100823332';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | a     | range | dev_code      | dev_code | 33      | NULL |   11 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.01 sec)

· all:对于每个来自于先前的表的行组合,没有使用索引，进行完整的表扫描，这是最不好的选择

mysql> explain select * from `app_develop_staff_info` ;
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table                  | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | app_develop_staff_info | ALL  | NULL          | NULL | NULL    | NULL | 334050 |       |
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)

三，使用索引效果

1，不使用索引

mysql> explain select * from app_develop_staff_info where dev_code = 5105650940;
+----+-------------+------------------------+------+----------------------+------+---------+------+--------+-------------+
| id | select_type | table                  | type | possible_keys        | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------------------+------+----------------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | app_develop_staff_info | ALL  | PRIMARY,IDX_DEV_CODE | NULL | NULL    | NULL | 276159 | Using where |
+----+-------------+------------------------+------+----------------------+------+---------+------+--------+-------------+
1 row in set (0.19 sec)
mysql> select * from app_develop_staff_info where dev_code = 5105650940;
+------------+----------+--------------------------------------+-----------+---------+-------+-----------+----------+-------------+
| dev_code   | dev_type | dev_name                             | chnl_code | chnl_id | state | city_code | staff_id | phone_num   |
+------------+----------+--------------------------------------+-----------+---------+-------+-----------+----------+-------------+
| 5105650940 | 01       | 佛科院河滨青创社（锦新）             | 51b0p2y   | 8A5FD   | 0     | 530       |          | 18665540383 |
+------------+----------+--------------------------------------+-----------+---------+-------+-----------+----------+-------------+
1 row in set, 24 warnings (0.68 sec)

2，使用索引

mysql> explain select * from app_develop_staff_info where dev_code = '5105650940';
+----+-------------+------------------------+-------+----------------------+---------+---------+-------+------+-------+
| id | select_type | table                  | type  | possible_keys        | key     | key_len | ref   | rows | Extra |
+----+-------------+------------------------+-------+----------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | app_develop_staff_info | const | PRIMARY,IDX_DEV_CODE | PRIMARY | 50      | const |    1 | NULL  |
+----+-------------+------------------------+-------+----------------------+---------+---------+-------+------+-------+
1 row in set (0.02 sec)

mysql> select * from app_develop_staff_info where dev_code = '5105650940';
+------------+----------+--------------------------------------+-----------+---------+-------+-----------+----------+-------------+
| dev_code   | dev_type | dev_name                             | chnl_code | chnl_id | state | city_code | staff_id | phone_num   |
+------------+----------+--------------------------------------+-----------+---------+-------+-----------+----------+-------------+
| 5105650940 | 01       | 佛科院河滨青创社（锦新）             | 51b0p2y   | 8A5FD   | 0     | 530       |          | 18665540383 |
+------------+----------+--------------------------------------+-----------+---------+-------+-----------+----------+-------------+
1 row in set (0.00 sec)

四，得出结论：

1，合理建立索引，可以提高查询效率

2，给参数添加单引号，以便使用索引