「MySQL系列」索引设计原则、索引失效场景、limit 、order by、group by 等常见场景优化

一 索引使用

1.1 概述

1. 定义
索引帮助MySQL高效获取数据的数据结构(按照一定规则)。
2. 定义解释
MySQL在存储数据之外，数据库系统还维护者满足特定查找算法的 数据结构，这些数据结构以某种方式引用（指向）数据， 这样就 可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
3. 优缺点

优点 
提高数据检索效率，降低数据库IO成本。通过索引对数据进
行排序降低数据排序成本，降低CPU消耗。

缺点 
实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。更新表时，MySQL 不
仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字
段，都会调整因为更新所带来的键值变化后的索引信息。

1.2 索引结构(InnoDB)

MySQL数据库中默认的存储引擎InnoDB的索引结构为B+树，而根据
叶子节点的内存存储不同，索引类型分为主键索引和非主键索引。
1. 主键索引（聚簇索引）
主键索引的叶子节点存储的是整行数据，其结构如下：

2. 非主键索引(二级索引或辅助索引)
而非主键索引的叶子节点内容存储时的主键的值，其结构如下：

1.3 索引使用规则

没有建立索引，执行计划如下

建立索引

create index idx_seller_name_status_address on tb_seller(name, status, seller);

1. 全值匹配，对索引所有列都制定具体值

explain select * from tb_seller where name='小米科技' and status='1' and
address='北京市';

2. 最左前缀法制

违背最左法则，索引失效

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

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

根据前面的两个字段name，status查询是走索引的，但是最后一 个条件address 没有用到索引。
4. 索引列上进行运算操作，索引失效

5. 字符串不加单引号，造成索引失效