MySQL-索引

什么是索引

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

聚集索引和非聚集索引（二级索引）

聚集索引(Clustered Index)

含义：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

特点：必须有,而且只有一个

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

非聚集索引(Secondary Index)

含义：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键

特点：可以存在多个

回表查询

含义：通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表

覆盖索引

含义：覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

简单来说就是不需要回表查询的索引

MYSQL超大分页处理

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

执行limit分页查询耗时对比:

因为，当在进行分页查询时，如果执行limit 9000000,10，此时需要MySQL排序前9000010记录，仅仅返回9000000 - 9000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

创建索引的原则

索引失效情况

• 违反最左前缀法则
• 范围查询右边的列，不能使用索引
• 在索引列上进行运算操作或者类型转换 ，索引将失效
• 以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

例表：

违反最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引:

范围查询右边的列，不能使用索引

根据前面的两个字段name ,status查询是走索引的，但是最后一个条件address没有用到索引。

在索引列上进行运算操作或者类型转换

以%开头的Like模糊查询

 如果仅仅是尾部模糊匹配，索引不会失效。

总结