676   MySQL

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

 

一,查看表格索引

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,给参数添加单引号,以便使用索引




Leave a Reply

Your email address will not be published. Required fields are marked *