MySQL索引概述
为什么要使用索引
-
索引底层使用B+树，可以加快查询的速度
-
索引大大减少了存储引擎需要扫描的数据量 （INNODB 最小一页 16k）
-
索引可以帮助我们进行排序以避免以避免使用临时表
-
索引可以将随机的I/O转为顺序的I/O
btree索引的存储是有序的，所以访问索引是顺序io，而通过索引访问数据时确实是随机的
索引有什么缺点
- 建立索引需要占用磁盘空间，是空间换时间的体现
- 频繁维护索引耗费大量资源
什么字段适合建立索引
- 读多写少的表适合建索引，因为写多的话需要频繁的对索引进行维护，消耗大量的资源，得不偿失
- 区分度不大的字段不适合建索引，因为数据不宜筛选，比如性别字段
- UUID类型的不适合建索引，因为UUID是随机的，因为B+树在建立索引时是根据大小来比较的，使用UUID没有任何意义，而且会造成频繁调整，资源浪费严重
- 经常出现在where中的字段适合建立索引，因为建索引消耗的资源相对于不建索引查询数据消耗的资源来说很小
- 如果没有主键，且没有unique字段，MySQL会用一个隐藏的自增列来建立索引
- 字段比较小的适合建索引，字段大的话占用空间较多
- .......
索引常用概念
索引下推：
一般都是存储引擎搜索结果后交给服务层进行过滤，而索引下推就是由存储引擎直接按条件过滤后交给服务层
- 索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。
- 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
- 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
- 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
回表
对于InnoDB来说，普通索引经过索引查询最终查找的是主键的id，然后根据主键id去主键索引进行查找，需要查找两次，这就是回表查询
覆盖索引
不用进行回表，直接使用索引里的字段就是覆盖索引
聚簇索引与非聚簇索引
聚簇索引是对于InnoDB来说的，因为MyISAM是非聚簇索引。
聚簇索引
就是索引和数据文件放到一起，可以通过主键直接的查询到具体的数据，在B+树的叶子节点中存储着对应的数据，而不是地址
非聚簇索引
就是索引文件与数据文件分开存放，在主键索引、普通索引的叶子节点中存储的是数据所在的地址(myisam)。所以无论如何都需要查两次，第一次得到数据地址，第二次根据地址去获取数据。
对于InnoDB来说，非聚簇索引存放的是主键id，根据查到的主键id进行回表查询
因为myisam是用的是非聚簇索引，所以对于myisam来说，主键索引与普通索引都要进行回表，只不过主键索引不能重复
唯一索引、普通索引、联合索引
唯一索引
此索引中数据不重复，用unique或者primary表示的字段，索引文件与数据文件放在一起，不需要进行回表，可以直接得到数据
普通索引
就是根据普通的字段建立的索引，查询时可能需要进行回表
联合索引
多个字段建立的索引，要遵循最左匹配，MySQL也进行优化，使其尽量满足最左匹配
索引失效的情况
模糊查询
like “%lll”
范围查询
如果说建立了联合索引，那么范围查询字段后面的索引字段会失效
类型转换
比如说本来是字符串，结果在查询时忘记引号，造成类型转换
不符合最左匹配
建立了联合索引，而第一个字段没有用到，索引会失效
MySQL估计全表扫面比索引快，也不会使用索引
or语句
or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
索引列参与计算
索引列使用函数
索引底层的数据结构
索引底层数据结构有两种：
hash表：利用hash一一对应，但是不可以实现范围查询，等值查询很快
B+树：可以实现范围查找与等值查找，因为B+树的数据都存储在叶子节点，那样非叶子节点就可以存储很多的索引字段的值
而且叶子节点之间通过双向链表进行连接，可以实现范围查询