mysql 中的索引

索引介绍

索引(index)是帮助 mysql 高效获取数据的数据结构(有序)；在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

索引类型

普通索引：普通索引是最基本的索引，它没有任何限制，值可以为空，仅加速查询
唯一索引：唯一索引与普通索引类似，不同点：索引列的值必须唯一，但允许有空值；如果是组合索引，则列值的组合必须唯一
主键索引：主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值
组合索引：组合索引指在多个字段上创建的索引，只有在查询条件中使用创建索引时的第一个字段，索引才会被使用，使用组合索引时遵循最左前缀集合
全文索引：全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的 where语句的参数匹配

索引的使用场景

索引可能失效场景

聚族索引和非聚族索引

MyISAM 非聚族索引，B+Tree 作为索引结构；InnoDB 聚族索引，B+Tree 作为索引结构
聚集索引与非聚集索引的区别
- 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
- 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引
聚簇索引(左图)：
- 一级索引：表数据和主键一起存储的，一级索引的叶结点存储行数据(包含了主键值)
- 二级索引：叶节点存储行的主键值，使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，非叶子节点中的关键字中不存储对应记录的具体内容或内容地址，叶子节点上的数据是主键与具体记录(数据内容)，就是主键和执行一级索引的内容
非聚簇索(右图)：
- 表数据和索引是分成两部分存储的，一级索引和二级索引存储上没有任何区别，使用的是B+树作为索引的存储结构，所有的节点都是索引，叶子节点存储的是索引+索引对应的记录的数据

特定语句索引优化

join
- 外层循环-驱动表，内层循环-被驱动表，小表驱动大表，外层循环扫描次数越少，性能就越好
- join 字段类型要保持一致，是不同数据类型时，会进行全表扫描，join 字段可以加索引
- 连表不超过3张，如果业务需要超过，可以进行分开查询，在 service 层业务中处理数据(阿里规范手册)
limit
- offset 数值比较大的时候，查询的性能相对较差，原因是先扫描前面的数据丢弃之后，在去符合条件的分页数据页码数禁止传入过大的页码，业务妥协
- 覆盖索引 + 子查询 (利用索引去查询分页)
  ex: select * from employees where emp_no >= (select emp_no from employees limit 300000,1) limit 10;
count
- count(*) 和 count(1) 按照官方的说法，没有区别
- count(*) 会选择最小的非主键索引，如果不存在非主键索引，则会使用主键索引
- count(*) 不会排除为 null 的行，而 count(字段) 会排除，没有特殊需求尽量用 count(*)
order by
- 利用索引避免排序，索引本身就具有排序性
- 无法利用索引，优化 file sort
group by
- 参考地址： http://www.weijingbiji.com/1939/