江苏龙网

  1. 主页 > 生活百科 > >

MySQL 如何正确的使用索引( 五 )

索引mysql

possible_keys：列出了这个查询可能会走两个索引（idx1、idx2）
实际上走的却是idx1（key列：实际走的索引） 。
当多个条件中有索引的时候，并且关系是and的时候，会走索引区分度高的，显然name字段重复度很低，走name查询会更快一些 。
模糊查询
看两个查询
mysql> select count(*) from test1 a where a.name like 'javacode1000%';+----------+| count(*) |+----------+| 1111 |+----------+1 row in set (0.00 sec)mysql> select count(*) from test1 a where a.name like '%javacode1000%';+----------+| count(*) |+----------+| 1111 |+----------+1 row in set (1.78 sec)
上面第一个查询可以利用到name字段上面的索引，下面的查询是无法确定需要查找的值所在的范围的，只能全表扫描，无法利用索引，所以速度比较慢，这个过程上面有说过 。
回表
当需要查询的数据在索引树中不存在的时候，需要再次到聚集索引中去获取，这个过程叫做回表，如查询：
mysql> select * from test1 where name='javacode3500000';+---------+-----------------+-----+-------------------------+| id | name | sex | email |+---------+-----------------+-----+-------------------------+| 3500000 | javacode3500000 | 2 | javacode3500000@163.com |+---------+-----------------+-----+-------------------------+1 row in set (0.00 sec)
上面查询是*，由于name列所在的索引中只有name、id两个列的值，不包含sex、email，所以上面过程如下：
走name索引检索javacode3500000对应的记录，取出id为3500000
在主键索引中检索出id=3500000的记录，获取所有字段的值
索引覆盖
查询中采用的索引树中包含了查询所需要的所有字段的值，不需要再去聚集索引检索数据，这种叫索引覆盖 。
我们来看一个查询：
select id,name from test1 where name='javacode3500000';
name对应idx1索引，id为主键，所以idx1索引树叶子节点中包含了name、id的值，这个查询只用走idx1这一个索引就可以了，如果select后面使用*，还需要一次回表获取sex、email的值 。
所以写sql的时候，尽量避免使用*，*可能会多一次回表操作，需要看一下是否可以使用索引覆盖来实现，效率更高一些 。
索引下推
简称ICP，Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式，ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数 。
举个例子来说一下：
我们需要查询name以javacode35开头的，性别为1的记录数，sql如下：
mysql> select count(id) from test1 a where name like 'javacode35%' and sex = 1;+-----------+| count(id) |+-----------+| 55556 |+-----------+1 row in set (0.19 sec)过程：
走name索引检索出以javacode35的第一条记录，得到记录的id
利用id去主键索引中查询出这条记录R1
判断R1中的sex是否为1，然后重复上面的操作，直到找到所有记录为止 。
上面的过程中需要走name索引以及需要回表操作 。
如果采用ICP的方式，我们可以这么做，创建一个(name,sex)的组合索引，查询过程如下：
走(name,sex)索引检索出以javacode35的第一条记录，可以得到(name,sex,id)，记做R1
判断R1.sex是否为1，然后重复上面的操作，知道找到所有记录为止
这个过程中不需要回表操作了，通过索引的数据就可以完成整个条件的过滤，速度比上面的更快一些 。
数字使字符串类索引失效
mysql> insert into test1 (id,name,sex,email) values (4000001,'1',1,'javacode2018@163.com');Query OK, 1 row affected (0.00 sec)mysql> select * from test1 where name = '1';+---------+------+-----+----------------------+| id | name | sex | email |+---------+------+-----+----------------------+| 4000001 | 1 | 1 | javacode2018@163.com |+---------+------+-----+----------------------+1 row in set (0.00 sec)mysql> select * from test1 where name = 1;+---------+------+-----+----------------------+| id | name | sex | email |+---------+------+-----+----------------------+| 4000001 | 1 | 1 | javacode2018@163.com |+---------+------+-----+----------------------+1 row in set, 65535 warnings (3.30 sec)
上面3条sql，我们插入了一条记录 。
第二条查询很快，第三条用name和1比较，name上有索引，name是字符串类型，字符串和数字比较的时候，会将字符串强制转换为数字，然后进行比较，所以第二个查询变成了全表扫描，只能取出每条数据，将name转换为数字和1进行比较 。


推荐阅读


上一篇：松树油干了怎么能洗掉 衣服上的松节油印记怎么去除

下一篇：分布式寻址算法