Mysql索引相关
一、索引的类型（索引的数据结构）
Hash
哈希索引是基于哈希表实现, 只有精确匹配索引所有列的查询才有效;
存储引擎会为每行数据的hash索引列计算一个哈希码,hash索引会将所有的hash码存放在索引中,同时在哈希表中保存指向每个数据行的指针;
优点
因为索引自身只需要存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。
缺点
hash索引数据不是按照索引值的顺序存储,所以不能用于排序
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引内的全部内容来计算哈希值的。例如，在数据列（A，B）上建立哈希索引，如果查询只有数据列A，则无法使用该索引。
哈希索引只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如WHERE price > 100。
哈希冲突
B-Tree
存储数量大,查询速度快,天然有序.
优点
单次请求涉及的磁盘IO次数少（出度d大，且非叶子节点不包含表数据，树的高度小）；
查询效率稳定（任何关键字的查询必须走从根结点到叶子结点，查询路径长度相同）；
遍历效率高（从符合条件的某个叶子节点开始遍历即可）；
缺点
B+树最大的性能问题在于会产生大量的随机IO，主要存在以下两种情况：
主键不是有序递增的，导致每次插入数据产生大量的数据迁移和空间碎片；
即使主键是有序递增的，大量写请求的分布仍是随机的；
二、聚簇索引
聚簇索引不是一种单独的索引类型,而是一种数据存储方式;
聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。
优点
数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快
缺点
插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
InnoDB中聚簇索引的格式
InnoDB中辅助索引的格式
三、索引优化
索引优化的相关优化点概念
回表
回表即先通过辅助索引查找出数据行主键, 再通过主键找到具体数据;具体概念可参考上面 InnoDB中辅助索引的格式 ;
#1. 假设有张表,表中有3个字段, id(主键),name(辅助索引), age
select * from table where name = 'zhangsan'
#2. 上述有两个索引, 一个主键索引,一个辅助索引, 我们通过辅助索引会先查询 辅助索引的那棵B+树
#3. 在从查找的数据中取出id值,然后拿着这个id值, 去主键id的B+树种检索数据, 在检索过程种, 我们先查了第一个辅助索引的树 再查了 主键id的树, 这就叫做回表索引覆盖
索引覆盖是跟回表相反的概念, 在索引的叶子节点中如果能获取到查询的所有列数据,则无需回表,这就是索引覆盖;
#1. 假设有张表,表中有3个字段, id(主键),name(辅助索引), age
select id,name from table where name = 'zhangsan'
#2. 此时我们能发现,在name 的索引树的叶子节点中,存在id和name两个列的值, 所以通过name检索,可以直接获取到所有需要查询的列, 而不需要再去主键id 的索引树种检索, 这就是索引覆盖最左匹配
如果有组合索引,那么在使用组合索引查询时, 需要遵循最左匹配原则, 表示必须要先匹配到第一个索引列后才能继续匹配下一个索引列;
#1. 假设有张表,表中有3个字段, id(主键),name, age (其中name和age组合成一个索引,其顺序是 name,age)
select * from table where name = 'zhangsan' and age = 12 //这个查询能用到索引
select * from table where age = 12 and name = 'zhangsan' //这个查询能用到索引,mysql 的优化器会优化
select * from table where name = 'zhangsan' //这个查询也能用到索引
select * from table where age = 12 //这个查询不能用到索引,不满足最左匹配原则索引下推
索引下推表示的是数据筛选的过程下移到存储引擎层来完成,而不是在server层完成;
索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
# mysql包含了三个架构: 客户端 , server , 存储引擎
# 那么下列sql的执行顺序是:
select * from table where name = 'zhangsan' and age = 12
###### 在没有索引下推前的顺序:
# 1. 先通过索引name从存储引擎中找到所有符合条件的数据加载到server端
# 2. 然后server对这部分数据按照 age进行条件筛选
###### 现在的顺序(5.7以后默认开启):
# 1. 在查询时, 会在存储引擎直接将数据进行查找过滤,直接返回索引的可用性
union all , in , or 都能使用索引
强制类型转换会导致全表扫描, 比如索引列 phone 是varchar类型, 查询时 phone = 110 ,这时会导致全表扫描
更新频繁,数据区分度不高的字段上不宜创建索引
更新会导致变更B+树, 更新频繁会导致数据库性能下降
类似于性别这种区分度不高的字段,不能有效过滤数据不宜创建索引
一般区分度在 80% 以上可以创建索引, 区分度计算: count(distinct (列名)) / count(*)
单索引字段不能超过5个, 比如组合索引, 如果字段过多会占用过多的存储空间, 导致树的深度变深,从而影响检索效率
force index强制指定索引,select * from table force index (索引名) where name = 'xxx'