霜皮剥落紫龙鳞，下里巴人再谈数据库SQL优化，索引(一级/二级/聚簇/非聚簇)原理

原文转载自「刘悦的技术博客」https://v3u.cn/a_id_206

举凡后端面试，面试官不言数据库则已，言则必称SQL优化，说起SQL优化，网络上各种“指南”和“圣经”难以枚举，不一而足，仿佛SQL优化已然是妇孺皆知的理论常识，然后根据多数无知（Pluralistic ignorance）理论，人们印象里觉得多数人会怎么想怎么做，但这种印象往往是不准确的。那SQL优化到底应该怎么做？本次让我们褪去SQL华丽的躯壳，以最浅显，最粗俗，最下里巴人的方式讲解一下SQL优化的前因后果，前世今生。

SQL优化背景

首先要明确一点，SQL优化不是为了优化而优化，就像冬天要穿羽绒服，不是因为有羽绒服或者羽绒服本身而穿，是因为天儿太冷了！那SQL优化的原因是什么？是因为SQL语句太慢了！从广义上讲，SQL语句包含增删改查，但一般的业务场景下，SQL的读写比例应该是一比十左右，而且写操作很少出现性能问题，即使出现，大多数也是慢查询阻塞导致。生产环境中遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是第一要务。

那我们怎么知道那条SQL慢？开启慢查询日志(slow\_query\_log)

将 slow\_query\_log 全局变量设置为“ON”状态

mysql> set global slow_query_log='ON';

设置慢查询日志存放的位置

mysql> set global slow_query_log_file='c:/log/slow.log';

查询速度大于1秒就写日志：

mysql> set global long_query_time=1;

当然了，这并不是标准化流程，如果是实时业务，500ms的查询也许也算慢查询，所以一般需要根据业务来设置慢查询时间的阈值。

当然了，本着“防微杜渐”的原则，在慢查询出现之前，我们完全就可以将其扼杀在摇篮中，那就是写出一条sql之后，使用查询计划(explain)，来实际检查一下查询性能，关于explain命令，在返回的表格中真正有决定意义的是rows字段，大部分rows值小的语句执行并不需要优化，所以基本上，优化sql，实际上是在优化rows，值得注意的是，在测试sql语句的效率时候，最好不要开启查询缓存，否则会影响你对这条sql查询时间的正确判断：

SELECT SQL_NO_CACHE

SQL优化手段(索引)

除了避免诸如select *、like、order by rand()这种老生常谈的低效sql写法，更多的，我们依靠索引来优化SQL，在使用索引之前，需要弄清楚到底索引为什么能帮我们提高查询效率，也就是索引的原理，这个时候你的脑子里肯定浮现了图书的目录、火车站的车次表，是的，网上都是这么说的，事实上是，如果没坐过火车，没有使用过目录，那这样的生活索引样例就并不直观，作为下里巴人，我们一定吃过包子：

毫无疑问，当我们在吃包子的时候，其实是在吃馅儿，如果没有馅儿，包子就不是包子，而是馒头。那么问题来了，我怎么保证一口就能吃到馅儿呢？这里的馅儿，可以理解为数据，海量数据的包子，可能直径几公里，那么我怎么能快速得到我想要的数据(馅儿)？有生活经验的吃货一定会告诉你，找油皮儿，因为馅儿里面有油脂，更贴近包子皮儿的地方，或者包子皮儿簙的地方，都会被油脂浸透，也就形成了油皮儿，所以如果照着油皮儿下嘴，至少要比咬其他地方更容易吃到馅儿，那么，索引就是油皮儿，有索引的数据就是有油皮儿的大包子，没有索引的数据就是没有油皮儿的大包子，如此一来，索引的原理显而易见，通过缩小数据范围（油皮儿）来筛选出最终想要的结果（馅儿），同时把随机的查询（随便咬）变成顺序的查询（先找油皮儿），也就是我们总是通过同一种查询方式来锁定数据。

SQL索引的数据结构B+tree

知道了背景，了解了原理，现在我们需要某种容器（数据结构）来帮我们实现包子的油皮儿，这种容器可以协助我们每次查找数据时把咬包子次数控制在一个很小的数量级，最好是常数数量级。于是B+tree闪亮登场。

那么，假设数据库中有1-7条数据，一次查询，B+tree到底怎么帮我们快速检索到数据呢？

SELECT SQL_NO_CACHE id from article where id = 4

如图所示，如果要查找数据4，那么首先会把B+tree的根节点加载到内存，此时发生一次咬包子(IO读操作)，在内存中用二分查找确定4在3和5之间，通过根节点所存储的指针加载叶子节点(3,4)到内存中，发生第二次咬包子，结束查询，总计两次。如果不使用索引，我们需要咬四口包子才能把4咬出来。而在生产环境中，2阶的B+树可以表示上百万的数据，如果上百万的数据查找只需要两次IO读操作，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO读取，那么总共需要百万次的IO，显然成本是巨大的。

同时，我们知道IO次数读写取决于B+树的层级，也就是高度h，假设当前数据表的数据为N，每个存储容器的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 存储容器的大小 / 数据项的大小，存储容器的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么B+树要求把真实的数据放到叶子节点而不是非叶子节点，一旦放到非叶子节点，存储容器的数据项会大幅度下降，导致树的层数增高。当数据项等于1时将会退化成线性表，又变成了顺序查找，所以这也是为啥索引用B+tree，而不用B-tree，根本原因就是叶子节点存储数据高度就会减小，而高度减小才能帮我们更快的吃到馅儿。

说白了就是B-tree也能实现索引，也能让我们更快的访问数据，但是B-tree每个节点上都带着一点儿馅儿，而这个馅儿占据了本来油皮的空间，所以为了扩容，只能增加B-tree的高度进行扩容，随着馅儿越来越多，导致B-tree的高度也越来越高，高度越高，我们咬包子的次数也越来越频繁，读写效率则越来越慢。

当B+树的数据项是复合的数据结构，即所谓的联合索引，比如(name,age,sex)的时候，B+树是按照从左到右的顺序来建立搜索树的，比如当(小明,20,男)这样的数据来检索的时候，B+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,男)这样的没有name的数据来的时候，B+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(小明,F)这样的数据来检索时，B+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于小明的数据都找到，然后再匹配性别是男的数据了， 这个是非常重要的性质，即索引的最左匹配特性，关于最左原则可以参照这篇文章：mysql联合索引的最左前缀原则以及b+tree。

最基本的索引建立原则无外乎以下几点：

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4.索引列不能参与计算，保持列“干净”，比如from\_unixtime(create\_time) = ’2020-01-01’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create\_time = unix\_timestamp(’2020-01-01’)。

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

索引类型(聚簇(一级)/非聚簇(二级))

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据。

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据。

上文说了，由于数据本身会占据索引结构的存储空间，因此一个表仅有一个聚簇索引，也就是我们通常意义上认为的主键(Primary Key)，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。如果你已经设置了主键为聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。除了聚簇索引，其他的索引都是非聚簇索引，比如联合索引，需要遵循“最左前缀”原则。

一般情况下，主键(聚簇索引)通常建议使用自增id，因为聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

非索引优化

是的，SQL优化包含但并不限于索引优化，索引可以帮助我们优化效率，但索引也并非万能，比如著名的SQL分页偏移优化问题：

select * from table_name limit 10000,10  
  
select * from table_name limit 0,10

limit 分页算法带来了极大的遍历，但数据偏移量一大，limit 的性能就急剧下降。

造成效率问题的根源是查询逻辑：

1.从数据表中读取第N条数据添加到数据集中

2.重复第一步直到 N = 10000 + 10

3.根据 offset 抛弃前面 10000 条数

4.返回剩余的 10 条数据

一般情况下，可以通过增加筛选条件限制查询范围而优化：

select * from table_name where (id >= 10000) limit 10

这种优化手段简单粗暴，但是需要有一些前提：首先必须要有聚簇索引列，而且数据在逻辑上必须是连续的，其次，你还必须知道特征值，也就是每页的最后一条逻辑数据id，如果增加其他的范围筛选条件就会很麻烦。

所以，单纯的关键字优化又需要索引的参与：

Select * From table_name Where id in (Select id From table_name where ( user = xxx ))

给user字段设置索引，子查询只用到了索引列，没有取实际的数据，只取主键，我们知道，聚簇索引是把数据和索引放在一起的，所以把原来的基于 user 的搜索转化为基于主键（id）的搜索，主查询因为已经获得了准确的索引值，所以查询过程也相对较快。

但优化并未结束，由于外层查询没有where条件（因为子查询还未执行），结果就是将分页表的全部数据都扫描了出来load到了内存，然后进行nested loop，循环执行子查询，根据子查询结果对外层查询结果进行过滤。

select * from table_name a inner join ( select id from table_name where (user = xxx) limit 10000,10) b on a.id = b.id

所以，如果外层没有筛选范围，慎用in关键字，因为in子查询总是以外层查询的table作为驱动表，所以如果想用in子查询的话，一定要将外层查询的结果集降下来，降低io次数，降低nested loop循环次数，即：永远用小结果集驱动大的结果集。

SQL优化瓶颈(成也优化，败也优化)

SQL优化能解决所有问题吗？并非如此：

select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_ROWS,TABLE_COLLATION,ENGINE,group_concat(case CONSTRAINT_NAME when NULL then '' else CONSTRAINT_NAME end) CN,group_concat(case CONSTRAINT_TYPE when NULL then '' else CONSTRAINT_TYPE end) PF from (select a.TABLE_SCHEMA,a.TABLE_NAME,a.TABLE_TYPE,a.TABLE_ROWS,a.TABLE_COLLATION,a.ENGINE,b.CONSTRAINT_NAME,b.CONSTRAINT_TYPE,b.key_cols  
from INFORMATION_SCHEMA.TABLES a  
LEFT JOIN  
(SELECT  
t.TABLE_SCHEMA,  
t.TABLE_NAME,  
t.CONSTRAINT_NAME,  
t.CONSTRAINT_TYPE,  
group_concat(c.COLUMN_NAME) key_cols  
FROM  
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,  
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c  
WHERE  
t.TABLE_NAME = c.TABLE_NAME  
AND t.CONSTRAINT_TYPE in ('PRIMARY KEY','FOREIGN KEY')  
AND t.CONSTRAINT_NAME=c.CONSTRAINT_NAME  
and c.table_schema=t.table_schema  
group by TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE) b  
on (a.TABLE_NAME = b.TABLE_NAME and a.table_schema=b.table_schema)  
WHERE a.TABLE_TYPE='BASE TABLE' and a.TABLE_SCHEMA = database()) ccc GROUP BY TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION,ENGINE;

是的，有时候，我们往往忽略了一个关键问题，就是需求，当出现了上面这种SQL的时候，我们脑子里想的不应该是优化，因为就算优化了，也是饮鸩止渴，由于SQL用例回归时落掉一些极端情况，可能会造成比原来还严重的后果。

那我们应该怎么解决这种“非优化之罪”的情况呢？答案从业务出发，对业务进行解耦，复杂SQL的出现，往往是因为业务频繁变动导致之前设计的表结构无法支撑业务的原子性扩容，所以，从源头出发，对表结构重新设计，或者干脆写一个脚本将慢查询结果集导入到一张新的结果表中，这样往往更加简单和节省时间。

结语：任何一款开源数据库，国内外大厂在用，三流的草台班子也在用，但是用起来的效果不尽相同，同样地，一套太祖长拳，在寻常武师和丐帮帮主乔峰手底下施展出来的威力更是天差地别，其实这道理与武学一般，看似简单的业务更能提现个人实力，貌似稀松平常的索引优化才能检测出一个人的SQL功底，能在平淡之中现神奇，才说得上是大宗匠的手段。

原文转载自「刘悦的技术博客」 https://v3u.cn/a_id_206