关于mysql优化:霜皮剥落紫龙鳞下里巴人再谈数据库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