关于Mysql索引
1 索引是什么
索引是存储引擎用于快速找到记录的一种数据结构。 – 《高性能Mysql》
1.1 优点 & 缺点
Mysql 作为存储系统,功能无非有二,读和写。 通常情况下,对于 Mysql 实例的请求量读远远大于写,所以通常的系统瓶颈都表现在读上。 在进一步讲,Insert 或者 Update 操作的表现形式更少, 无非
- Insert into X values (…)
- Update X set …
但是对于读,可以出现 where/order by/group by 等等的组合, 特别针对 where, 更有 A= and/or B=; A= and B>; A> and B= or C< 等等。
相比来讲,在大量数据的情况下,单词写操作涉及到的内部操作更少,对于整体数据的敏感度更低。而读操作,稍不小心, 做了一个全表扫描,在大量数据的情境下,这种扫描耗费/占用的 大量的 cpu 资源,恰逢赶上高并发下,其他请求被堵死 或被拖慢(锁),机器 load 飙高,于是一来二去,说不定就垮了。
优点
一句话,索引用来解决 读 的问题。
方式就是:
- 减少需要扫描的数据量
- 避免第二次排序或者临时表
- 将随机 IO 变为顺序 IO
缺点:
2 索引的原理是什么
以下提到的索引实现是 B-TREE
根本无非有二:
- 随机IO/顺序IO(关于二者可参看:http://blog.csdn.net/dba_waterbin/article/details/8937441)
- 使用索引扫描代替全表扫描
部分内容来源于《Mysql索引与sql调优》(玄惭)。见过的最好的Mysql PPT。
基本概念:
- 第一索引 – 主键索引,没有设定主键情况下 Mysql 会采用默认机制设置
- 第二索引 – 非主键索引的索引
第二索引在叶子节点上包含了主键, 在查找数据时在通过主键(第一索引)定位数据。
2.1 索引是怎样加速查询的?
在 资料(Mysql索引与sql调优)中,分别阐述了在如下几种查询中索引起的作用,说的太好太详细,我在粘贴一遍 都没有原PPT粗暴精彩,这里就不脱了裤子放屁了,我已经将资料保存在 印象笔记 中了。
2.1.1 对于主键查询
2.1.2 第二索引查询
2.1.3 非唯一索引查询
2.1.4 范围扫描
2.1.5 全表扫描
2.1.6 覆盖索引扫描
3 精髓
抓住两点就抓住了精髓。
- 第一,索引扫描如二分查找一样,将 找到 的目的更快达到。
- 第二,如果不是覆盖索引,数据找到(主键)还要回磁盘中取数据(大部分都是随机IO), 这里也是性能的关键点。
似乎又是鱼和熊掌不可兼得的问题。但是在结果集少的情况下,多次的随机IO是和全表扫描相比是微不足道的。
注意点: 一般对于索引,根节点和分支节点是被缓存的,而叶子节点不会,所以访问叶子节点和根据叶子节点找到的主键回磁盘取数据是所有IO 的来源。
4 最佳实践
考虑点:
1 是否能使用多列索引、覆盖索引(Mysql 在大部分情况下一次只能使用一个索引)
2 多列索引的字段排列顺序是字段的选择性从高到低(考虑二分查找)
3 是否能减少随机IO
4 使用索引扫描来做排序。如果排序字段不在筛选索引中,那么必然要随机IO回磁盘取数据,然后进行排序。除此之外,由于单次查询一般 只能使用到一个索引,这就面临排序索引和筛选索引二选一的问题,最终Mysql的筛选结果是不确定的。所以最好的方式将排序字段加在多列索引 之中(参考《高性能Mysql》5.3.7章节)
5 索引为维护是需要资源的,排除冗余索引,不建立不需要的索引
6 关于 IN 查询,mysql在一些情况下会以 range 查询的形式操作,这样一来,联合索引可能就无法覆盖使用,我在构建 user 维度表时遇到了 这种情况。具体的分析可见http://myrock.github.io/2014/09/24/in-and-range/。
7 关于 Explain 中 Extra 的 Using index condition 和 Using index 的不同。简单的说, 后者代表的就是覆盖索引,不用再回磁盘取 记录,而前者代表的是此次查询命中了索引,但是还要回磁盘取额外的数据才能完成此次查询,有可能是因为返回的字段不包含在索引中,或者是除了 索引,还需要其他的字段进行筛选才能得到结果。具体可见 http://stackoverflow.com/questions/1687548/mysql-explain-using-index-vs-using-index-condition
5 书籍
- 《高性能Mysql》
- 《Mysql技术内幕:InnoDB存储引擎》