介绍了mysql的两种存储引擎的索引信息和mysql在不同查询语句中访问索引的方式
定义:索引是存储引擎用于快速找到记录的一种数据结构。举例说明:如果查找一本书中的某个特定主题,一般会先看书的目录(类似索引),找到对应页面。在MySQL,存储引擎采用类似的方法使用索引,高效获取查找的数据。
索引的分类
上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用,因为B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该咋办呢?这时就可以多建几棵B+树,不同的B+树中的数据采用不同的排序规则,这就是二级索引。
比方说我们用c2列的大小作为数据页、页中记录的排序规则,再建一棵B+树,效果如下图所示:
这个B+树与上边介绍的聚簇索引有几处不同:
使用二级索引与聚簇索引时的区别:由于聚簇索引即数据,所以在使用时可以直接找到数据信息,而二级索引由于只包含索引值(上图的c2)和聚簇索引(主键)信息,所以根据二级索引查找到信息时,必须再根据主键值去聚簇索引中再查找一遍完整的用户记录,当然如果只需要返回索引包含的字段信息,是可以直接返回的(例如,select c2)。
我们也可以同时以多个列的大小作为排序规则,也就是同时为多个列建立索引,比方说我们想让B+树按照c2和c3列的大小进行排序,这个包含两层含义:
以c2和c3列的大小为排序规则建立的B+树称为联合索引,本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的,不同点如下:
我们知道InnoDB中索引即数据,也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了,而MyISAM的索引方案虽然也使用树形结构,但是却将索引和数据分开存储:
c1+c2+c3
,我们使用c1
或c1+c2
都可以使用到索引,但是c2+c3
不能使用到索引c1
,类型为字符串,当我们使用like 'a%'
时可以使用到索引,但是匹配的中间,或者后面则不能,例如like '%a%'
、like %a
where 'A'<c1 and c1<'C'
还有更多的使用情况就不一一列举,都大同小异
在MySql中执行查询语句时,查询的执行方式大致分为两种:
有的时候我们可以通过主键列来定位一条记录,比方说这个查询:SELECT * FROM single_table WHERE id = 1438;
类似的,我们根据唯一二级索引列来定位一条记录,比如下边这个查询:SELECT * FROM single_table WHERE key2 = 3841;
对于唯一二级索引来说,查询该列为NULL值的情况比较特殊,比如这样:SELECT * FROM single_table WHERE key2 IS NULL;
因为唯一二级索引列并不限制 NULL 值的数量,所以上述语句可能访问到多条记录,也就是说上边这个语句不可以使用const访问方法来执行
有时候我们对某个普通的二级索引列与常数进行等值比较,比如这样:SELECT * FROM single_table WHERE key1 = 'abc';
由于普通二级索引并不限制索引列值的唯一性,所以可能找到多条对应的记录,也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少,则回表的代价还是比较低的,所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。
这种搜索条件为二级索引列与常数等值比较,采用二级索引来执行查询的访问方法称为:ref。
特殊情况:
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';
不仅想找出某个二级索引列的值等于某个常数的记录,还想把该列的值为NULL的记录也找出来,就像下边这个查询:SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;
当使用二级索引而不是全表扫描的方式执行该查询时,这种类型的查询使用的访问方法就称为ref_or_null
之前介绍的几种访问方法都是在对索引列与某一个常数进行等值比较的时候才可能使用到,但是有时候我们面对的搜索条件更复杂,比如下边这个查询:SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);
这种利用索引进行范围匹配的访问方法称之为:range
其实对于B+树索引来说,只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=(不等于也可以写成<>)或者LIKE操作符连接起来,就可以产生一个所谓的区间。
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';
该索引为 key_part1, key_part2, key_part3
由于key_part2并不是联合索引idx_key_part最左索引列,所以我们无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下边这两个条件:
也就是说我们可以直接通过遍历idx_key_part索引的叶子节点的记录来比较key_part2 = ‘abc’这个条件是否成立,把匹配成功的二级索引记录的key_part1, key_part2, key_part3列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多(聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列,而二级索引记录只需要存放索引列和主键),而且这个过程也不用进行回表操作,所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多,把这种采用遍历二级索引记录的执行方式称之为:index。
最直接的查询执行方式就是全表扫描,对于InnoDB表来说也就是直接扫描聚簇索引,把这种使用全表扫描执行查询的方式称之为:all。
ps:以上所有访问方式速度大部分情况下是依次递减的
以上是最近学习MySql索引相关内容后的一个简单的总结
参考
socialShare('.social-share', { sites: [ 'qq' , 'wechat' , 'weibo' , 'twitter' , 'facebook' ], wechatQrcodeTitle: "分享到微信朋友圈", wechatQrcodeHelper: '期待在朋友圈见到这篇文章' });