关于Mysql索引的优化以及原理学习

一、建索引的几大原则 （参考：https://tech.meituan.com/2014/06/30/mysql-index.html）

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

二、MySQL索引优化规则 （参考：https://www.cnblogs.com/hoewang/p/10257190.html）

1.模糊查询不能使用索引

2.union、in、or 都能够命中索引 （优化 CPU耗费 union < in < or）

3.负向条件查询不能使用索引，可以优化为 in 查询 (负向条件有：!=、<>、not in、not exists、not like 等)

4.联合索引最左前缀原则（又叫最左侧查询） (如果在(a,b,c)三个字段上建立联合索引，那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。)
建联合索引的时候，区分度最高的字段在最左边。
如果建立了(a,b)联合索引，就不必再单独建立 a 索引。同理，如果建立了(a,b,c)联合索引，就不必再单独建立 a、(a,b) 索引。
存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如 where a>? and b=?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。
最左侧查询需求，并不是指 SQL 语句的 where 顺序要和联合索引一致。

5.范围列可以用到索引（联合索引必须是最左前缀）。
范围条件有：<、<=、>、>=、between等。
范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

6.把计算放到业务层而不是数据库层。
select * fromdoc where YEAR(create_time) <= '2016'
即使 date 上建立了索引，也会全表扫描，可优化为值计算，如下：
select * fromdoc where create_time <= '2016-01-01'

7.强制类型转换会全表扫描
如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引。
select * fromuser where phone=13800001234
可以优化为：
select * fromuser where phone='13800001234'

8.更新十分频繁、数据区分度不高的字段上不宜建立索引。
更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。
“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。
一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。

9.如果有 order by、group by 的场景，请注意利用索引的有序性。
order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。
如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;，索引(a,b)无法排序。

10.使用短索引（又叫前缀索引）来优化索引。
前缀索引，就是用列的前缀代替整个列作为索引 key，当前缀长度合适时，可以做到既使得前缀索引的区分度接近全列索引，同时因为索引 key 变短而减少了索引文件的大小和维护开销，可以使用 count(distinct left(列名, 索引长度))/count(*) 来计算前缀索引的区分度。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引（Covering Index，即当索引本身包含查询所需全部数据时，不再访问数据文件本身），很多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
例如对于下面的 SQL 语句：
SELEC *FROM employees.employees WHERE first_name='Eric'AND last_name='Anido';
我们可以建立索引：(firstname, lastname(4))。

11.建立索引的列，不允许为 null。
单列索引不存 null 值，复合索引不存全为 null 的值，如果列允许为 null，可能会得到“不符合预期”的结果集，所以，请使用 not null 约束以及默认值。

12.利用延迟关联或者子查询优化超多分页场景。
MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。
示例如下，先快速定位需要获取的 id 段，然后再关联:
selecta.* from 表1 a,(select id from 表1 where 条件 limit100000,20 ) b where a.id=b.id

13.业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

14.超过三个表最好不要 join。
需要 join 的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。

15.如果明确知道只有一条结果返回，limit 1 能够提高效率。

16.SQL 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
ref：使用普通的索引（Normal Index）。
range：对索引进行范围检索。
当 type=index 时，索引物理文件全扫，速度非常慢。

17.单表索引建议控制在5个以内。

18.单索引字段数不允许超过5个。字段超过5个时，实际已经起不到有效过滤数据的作用了。

19.创建索引时避免以下错误观念
索引越多越好，认为一个查询就需要建一个索引。
宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度。
抵制惟一索引，认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。
过早优化，在不了解系统的情况下就开始优化。