MySQL索引
索引
除了数据之外,数据库系统还维护为满足特定查找算法的数据结构,这些数据结构以某种方式引用数据.这种数据结构就是索引
实现的数据结构：
mysql 主要提供两种索引类型
-
B-Tree索引
底层是基于B+Tree实现
B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN)。相当于二分查找。 -
Hash索引
哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。
显然，如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。
如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。
索引类型：
聚簇索引、非聚簇索引
-
聚簇索引
* 如果表设置了主键，则主键就是聚簇索引
* 如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
* 以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引
InnoDB的聚簇索引的叶子节点存储的是行记录（其实是页结构，一个页包含多行数据），InnoDB必须要有至少一个聚簇索引。
由此可见，使用聚簇索引查询会很快，因为可以直接定位到行记录。
-
普通索引
普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。
InnoDB的普通索引叶子节点存储的是主键（聚簇索引）的值，而MyISAM的普通索引存储的是记录指针。
回表查询
先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。
索引覆盖：
只需要在一颗索引树上就能获取sql所需要的所有列数据，无需回表，速度更快。
explain的输出结果Extra字段为Using index 时，表示触发了索引覆盖，没有回表查询。
如何实现覆盖索引：
常见的方法是：将被查询的字段，建立到组合索引里去。
比如：
user表有 id、name、age 三个字段，id为主键（聚簇索引）,name为普通索引
查询
select id,name,age from user where name ='张三'
此时由于name是普通索引，所以会从name的索引树上面找到id的值，再去根据id的值扫描聚簇索引找到对应的数据，此时就出现了回表查询。
为name和age创建一个索引树：
create INDEX idx_age_name ON user(name,age)
再次查询使用age做条件（或附带age做条件）就能出发组合索引
select id,name,age from user where age > 20
此时字段age和name是组合索引idx_age_name，查询的字段id、age、name的值刚刚都在索引树上，只需扫描一次组合索引B+树即可，这就是实现了索引覆盖，此时的Extra字段为Using index表示使用了索引覆盖。
索引覆盖优化limit
当mysql 数据量过大时，此时即使有索引，直接使用limit 分页查询，偏移量过大时速度也会很慢
--此时limit 跨度为600w条数据，查询时间为44s
SELECT * FROM `sys_user` LIMIT 6000000,10;
优化方式1：
SELECT
*
FROM
`sys_user`
WHERE
id >= ( SELECT id FROM sys_user LIMIT 6000000, 1 )
LIMIT 10
优化方式2：
SELECT
*
FROM
`sys_user` a
JOIN ( SELECT id FROM sys_user LIMIT 6000000, 10 ) b USING ( id )
第二种方式比第一种方式快一点
索引失效的情况：
- 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
- 使用索引字段like查询的时候，用%开头
- 对索引字段进行函数计算
- where出来的数据超过全表的30%(大概)，就不会走索引，mysql会认为这次的效率不如直接全表查（如果mysql估计使用全表扫描要比使用索引快,则不使用索引）