MySQL索引（优化篇）

已于 2025-01-26 11:13:18 修改
阅读量882 收藏 15
点赞数 14
CC 4.0 BY-SA版权
分类专栏： # MySQL索引 文章标签： mysql 数据库 MySql linux centos
于 2025-01-16 14:02:51 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/aabbdnsm/article/details/145152426

后面也会持续更新，学到新东西会在其中补充。

建议按顺序食用，欢迎批评或者交流！

缺什么东西欢迎评论！我都会及时修改的！

大部分截图和文章采用该书，谢谢这位大佬的文章，在这里真的很感谢让迷茫的我找到了很好的学习文章。我只是加上了自己的拙见。我只是记录学习没有任何抄袭意思

MySQL 是怎样运行的：从根儿上理解 MySQL - 小孩子4919 - 掘金小册

(五)MySQL索引应用篇：建立索引的正确姿势与使用索引的最佳指南！索引大家都用过，但你的SQL与索引真的合格吗？本章则 - 掘金

索引失效有哪些？ | 小林coding

MySQL 使用 like “%x“，索引一定会失效吗？ | 小林coding

一条 SQL 语句引发的思考

操作环境

MySQL9.0.1 MySQL5.7 

前言

  • 每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点(非叶子节点)。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。

  • InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。

  • 二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。

  • B+树中每层节点（页）都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。

  • 通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快。

索引的代价

  • 空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，占一大片存储空间。

  •  时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。

B+树每层节点（页）都是按照索引列的值从小到大的顺序排序而组成了双向链表。

不论是叶子节点中的记录，还是内节点（非叶子节点）中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。

而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收啥的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，影响性能！

一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。 

B+树索引适用的条件

CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

 person_info表我们需要注意两点：

  • 表中的主键是id列，它存储一个自动递增的整数。所以InnoDB存储引擎会自动为id列建立聚簇索引。

  • 我们额外定义了一个二级索引idx_name_birthday_phone_number，它是由3个列组成的联合索引(二级索引)。所以在这个索引对应的B+树的叶子节点处存储的用户记录只保留name、birthday、phone_number这三个列的值以及主键id的值，并不会保存country列的值。

一个表中有多少索引就会建立多少棵B+树 ，因此person_info有两颗索引树。

 idx_name_birthday_phone_number索引对应的B+树中页面和记录的排序方式就是这样的：

  • 先按照name列的值进行排序。
  • 如果name列的值相同，则按照birthday列的值进行排序。
  • 如果birthday列的值也相同，则按照phone_number的值进行排序。

全值匹配

 搜索条件中的列和索引列一致的话，这种情况就称为全值匹配。

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

 idx_name_birthday_phone_number索引包含的3个列在这个查询语句中都展现出来了。

这里我已经实验过啦，详情看这里。  

MySQL索引（基础篇）_mysql 博客园-CSDN博客

  • 因为B+树的数据页和记录先是按照name列的值进行排序的，所以先可以很快定位name列的值是Ashburn的记录位置。

  • 在name列相同的记录里又是按照birthday列的值进行排序的，所以在name列的值是Ashburn的记录里又可以快速定位birthday列的值是'1990-09-27'的记录。

  • 如果name和birthday列的值都是相同的，那记录是按照phone_number列的值排序的，所以联合索引中的三个列都可能被用到。

 匹配左边的列

 尊重最左原则

#索引name, birthday, phone_number
#包含最左列
SELECT * FROM person_info WHERE name = 'Ashburn';
#包含多个最左列
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';

 下边的语句就用不到这个B+树索引

SELECT * FROM person_info WHERE birthday = '1990-09-27';

 name是有序的，name相同再排birthday。

那我问你，我直接查birthday会走索引吗？当然不会！之前说过了这种情况birthday是全局无序的。

两种解决方式：

1. SELECT * FROM person_info WHERE name = 'Ashburn' birthday = '1990-09-27';
2. 创建birthday索引

如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列。比方说联合索引idx_name_birthday_phone_number中列的定义顺序是name、birthday、phone_number，如果我们的搜索条件中只有name和phone_number，而没有中间的birthday，比方说这样：

SELECT * FROM person_info WHERE name = 'Ashburn' AND phone_number = '15123983239';

这样只能用到name列的索引，birthday和phone_number的索引就用不上了，因为name值相同的记录先按照birthday的值进行排序，birthday值相同的记录才按照phone_number值进行排序。

大人时代变啦！ 

这里会把stu_phone 的 where 条件下推到索引判断减少回表！

进行了优化！

匹配列前缀 

为某个列建立索引的意思其实就是在对应的B+树的记录中使用该列的值进行排序，比方说person_info表上建立的联合索引idx_name_birthday_phone_number会先用name列的值进行排序，所以这个联合索引对应的B+树中的记录的name列的排列就是这样的：

Aaron
Aaron
...
Aaron
Asa
Ashburn
...
Ashburn
Baird
Barlow
...
Barlow

 字符串排序的本质就是比较哪个字符串大一点儿，哪个字符串小一点，比较字符串大小就用到了该列的字符集和比较规则，一般的比较规则都是逐个比较字符的大小，也就是说我们比较两个字符串的大小的过程其实是这样的：

所以一个排好序的字符串列其实有这样的特点：

  • 先比较字符串的第一个字符，第一个字符小的那个字符串就比较小。

  • 如果两个字符串的第一个字符相同，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小。依此类推

所以一个排好序的字符串列其实有这样的特点：

  • 先按照字符串的第一个字符进行排序。

  • 如果第一个字符相同再按照第二个字符进行排序。

  • 如果第二个字符相同再按照第三个字符进行排序，依此类推。

也就是说这些字符串的前n个字符，也就是前缀都是排好序（像这样Asa，Ashburn As是相同的,a 的ascii码小于 h 因此放到前面）的，所以对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的，比方说我们想查询名字以'As'开头的记录，那就可以这么写查询语句：

SELECT * FROM person_info WHERE name LIKE 'As%';

 如果只给出后缀或者中间的某个字符串，比如这样：

SELECT * FROM person_info WHERE name LIKE '%As%';

 MySQL就无法快速定位记录位置了，因为字符串中间有'As'的字符串并没有排好序，所以只能全表扫描了。

有时候我们有一些匹配某些字符串后缀的需求，比方说某个表有一个url列，该列中存储了许多url： 

+----------------+
| url            |
+----------------+
| www.baidu.com  |
| www.google.com |
| www.gov.cn     |
| ...            |
| www.wto.org    |
+----------------+

 假设已经对该url列创建了索引，如果我们想查询以com为后缀的网址的话可以这样写查询条件：WHERE url LIKE '%com'

但是这样的话无法使用该url列的索引（索引会失效）。为了在查询时用到这个索引而不至于全表扫描，我们可以把后缀查询改写成前缀查询，不过我们就得把表中的数据全部逆序存储一下，也就是说我们可以这样保存url列中的数据：

+----------------+
| url            |
+----------------+
| moc.udiab.www  |
| moc.elgoog.www |
| nc.vog.www     |
| ...            |
| gro.otw.www    |
+----------------+

这样再查找以com为后缀的网址时搜索条件便可以这么写：WHERE url LIKE 'moc%'，这样就可以用到索引了。

匹配范围值 

idx_name_birthday_phone_number索引，所有记录都是按照索引列的值从小到大的顺序排好序的，所以这极大的方便我们查找索引列的值在某个范围内的记录。

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

由于B+树中的数据页和记录是先按name列排序的，所以上边的查询过程其实是这样的： 

  • 通过B+树在叶子节点中找到第一条name值大于Asa的二级索引记录，读取该记录的主键值进行回表操作，获得对应的聚簇索引记录后发送给客户端。
  • 根据上一步找到的记录，沿着记录所在的链表向后查找（同一页面中的记录使用单向链表连接起来，数据页之间用双向链表连接起来）下一条二级索引记录，判断该记录是否符合name < 'Barlow'条件，如果符合，则进行回表操作后发送至客户端。
  • 重复上一步骤，直到某条二级索引记录不符合name <'Barlow'条件为止。

如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引，比方说这样：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

 上边这个查询可以分成两个部分：

  1. 通过条件name > 'Asa' AND name < 'Barlow' 来对name进行范围，查找的结果可能有多条name值不同的记录。

  2. 对这些name值不同的记录继续通过birthday > '1980-01-01'条件继续过滤（通过MySQL Server过滤）。

这样子对于联合索引idx_name_birthday_phone_number来说，只能用到name列的部分，而用不到birthday列的部分，因为只有name值相同的情况下才能用birthday列的值进行排序，而这个查询中通过name进行范围查找的记录中可能并不是按照birthday列进行排序的，所以在搜索条件中继续以birthday列进行查找时是用不到这个B+树索引的。

精确匹配某一列并范围匹配另外一列

对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找，比方说这样： 

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';

 这个查询的条件可以分为3个部分：

  1. name = 'Ashburn'，对name列进行精确查找，当然可以使用B+树索引了。

  2. birthday > '1980-01-01' AND birthday < '2000-12-31'，由于name列是精确查找，所以通过name = 'Ashburn'条件查找后得到的结果的name值都是相同的，它们会再按照birthday的值进行排序。所以此时对birthday列进行范围查找是可以用到B+树索引的。

  3. phone_number > '15100000000'，通过birthday的范围查找的记录的birthday的值可能不同，所以这个条件无法再利用B+树索引了，只能遍历上一步查询得到的记录。

 同理，下边的查询也是可能用到这个idx_name_birthday_phone_number联合索引的：

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1980-01-01' AND phone_number > '15100000000';

个人理解：(name,age)联合索引

select * from student where name >=  '王' and age = 18;

可以怎么理解呢？name 相同 age根据name排序

可以拆分成这两句

select * from student where name =  '王' and age = 18; 这句sql就会使用到索引name，age。

select * from student where name >  '王' and age = 18;这句sql就会使用到索引name。

问题一

看看是否真正掌握了！ 

下面这句sql会用到card吗？ 

explain select * from type where age > 1 and card = 11;

不会用到card列，只有再age 相同的时候 card才能参与判断。

上面这句就用到了card 列。

问题二

explain select * from student where stu_name like 'w%';

explain select * from student where stu_name like 'w%' and stu_age = 12;

为什么此时也用了 stu_age 列呢？

like 'w%' 想象一下 有啥呢？

w 1 w 2 w 3

ww 1 ww 2 ww 3

age 列是否对于name是有序的呢？是肯定有序的！因此stu_age列也被用到查询里面了 。

用于排序

 之前说过了 order by 采用文件排序(file sorting)是非常慢的！

但是如果ORDER BY子句里使用到了索引列，就有可能省去在内存或文件中排序的步骤

SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

这个查询的结果集需要先按照name值排序，如果记录的name值相同，则需要按照birthday来排序，如果birthday的值相同，则需要按照phone_number排序。

哇这个过程，是不是和联合索引建立一模一样！

所以直接从索引中提取数据，然后进行回表操作取出该索引中包含的列就好了。

使用联合索引进行排序注意事项 

同理，ORDER BY name、ORDER BY name, birthday这种匹配索引左边的列的形式可以使用部分的B+树索引。当联合索引左边列的值为常量，也可以使用后边的列进行排序。

SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;

name 相同 birthday 有序

不可以使用索引进行排序的几种情况

对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。 

默认是升序排序 

如果查询中的各个排序列的排序顺序是一致的，比方说下边这两种情况：

  • ORDER BY name, birthday LIMIT 10

    这种情况直接从索引的最左边开始往右读10行记录就可以了。

  • ORDER BY name DESC, birthday DESC LIMIT 10，

    这种情况直接从索引的最右边开始往左读10行记录就可以了。

先按照name列进行升序排列，再按照birthday列进行降序排列的话 

SELECT * FROM person_info ORDER BY name, birthday DESC LIMIT 10;

 这样如果使用索引排序的话过程就是这样的：

  • 先从索引的最左边确定name列最小的值，然后找到name列等于该值的所有记录，然后从name列等于该值的最右边的那条记录开始往左找10条记录。

  • 如果name列等于最小的值的记录不足10条，再继续往右找name值第二小的记录，重复上边那个过程，直到找到10条记录为止。

很麻烦，想象一下一会在最左边，一会再最右边。索引本来就是为了提高效率的。

因此还不如使用文件排序(file sorting)

排序列包含非同一个索引的列

有时候用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序，比方说：

explain select stu_name,stu_age from student order by stu_name,stu_sex;

 都不是索引了，当然没有顺序因此需要文件排序(file sorting)

排序列使用了复杂的表达式

explain select stu_name,stu_age from student order by upper(stu_name);

 用了函数会修改原表数据值，和原数据不一样因此一样需要文件排序(file sorting)

用于分组 

SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

这个查询语句相当于做了3次分组操作：

  1. 先把记录按照name值进行分组，所有name值相同的记录划分为一组。

  2. 将每个name值相同的分组里的记录再按照birthday的值进行分组，将birthday值相同的记录放到一个小分组里，所以看起来就像在一个大分组里又化分了好多小分组。

  3. 再将上一步中产生的小分组按照phone_number的值分成更小的分组，所以整体上看起来就像是先把记录分成一个大分组，然后把大分组分成若干个小分组，然后把若干个小分组再细分成更多的小小分组。

随后针对那些小小分组进行统计，比如在我们这个查询语句中就是统计每个小小分组包含的记录条数。

这个分组过程全部需要在内存里实现，而如果有了索引的话，恰巧这个分组顺序又和我们的B+树中的索引列的顺序是一致的，所以可以直接使用B+树索引进行分组。

 没有用索引列，使用了临时表排序(using temporary)。

回表的代价(重要)

那么古尔丹代价是什么？

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

在使用idx_name_birthday_phone_number索引进行查询时大致可以分为这两个步骤：

  1. 从索引idx_name_birthday_phone_number对应的B+树中取出name值在Asa～Barlow之间的用户记录。

  2. 由于索引idx_name_birthday_phone_number对应的B+树用户记录中只包含name、birthday、phone_number、id这4个字段，而查询列表是*，意味着要查询表中所有字段，也就是还要包括country字段。这时需要把从上一步中获取到的每一条记录的id字段都到聚簇索引对应的B+树中找到完整的用户记录，也就是我们通常所说的回表，然后把完整的用户记录返回给查询用户。

索引idx_name_birthday_phone_number对应的B+树中的记录首先会按照name列的值进行排序，所以值在Asa～Barlow之间的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，我们可以很快的把这些连着的记录从磁盘中读出来，这种读取方式我们也可以称为顺序I/O(访问速度很快称之为数组可能更加贴切)。 根据第1步中获取到的记录的id字段的值可能并不相连（二级索引只是索引键有顺序但是主键值是没有顺序的），而在聚簇索引中记录是根据id（也就是主键）的顺序排列的，所以根据这些并不连续的id值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页，这种读取方式我们也可以称为随机I/O。

总结一下

  • 会使用到两个B+树索引，一个二级索引，一个聚簇索引。

  • 访问二级索引使用顺序I/O，访问聚簇索引使用随机I/O。

 需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。

比方说name值在Asa～Barlow之间的用户记录数量占全部记录数量90%以上，那么如果使用idx_name_birthday_phone_number索引的话，有90%多的id值需要回表，这不是吃力不讨好么，还不如直接去扫描聚簇索引（也就是全表扫描）。 

 那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方式去执行查询呢？

这个就是传说中的查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' LIMIT 10;

添加了LIMIT 10的查询更容易让优化器采用二级索引 + 回表的方式进行查询。 

 对于有排序需求的查询，上边讨论的采用全表扫描还是二级索引 + 回表的方式进行查询的条件也是成立的，比方说下边这个查询：

SELECT * FROM person_info ORDER BY name, birthday, phone_number;

由于查询列表是*，所以如果使用二级索引进行排序的话，需要把排序完的二级索引记录全部进行回表操作，这样操作的成本还不如直接遍历聚簇索引然后再进行文件排序（filesort）低，所以优化器会倾向于使用全表扫描的方式执行查询。如果加了LIMIT子句，比如这样：

SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

这样需要回表的记录特别少，优化器就会倾向于使用二级索引 + 回表的方式执行查询。 

如何挑选索引

只为用于搜索、排序或分组的列创建索引

只为出现在WHERE子句中的列、连接子句中的连接列，或者出现在ORDER BY或GROUP BY子句中的列创建索引。 

SELECT birthday, country FROM person_name WHERE name = 'Ashburn';

 像查询列表中的birthday、country这两个列就不需要建立索引，我们只需要为出现在WHERE子句中的name列创建索引就可以了。

考虑列的基数 

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2, 5, 8, 2, 5, 8, 2, 5, 8，虽然有9条记录，但该列的基数却是3。 

在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。 

假设某个列的基数为1，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，因为所有值都一样就无法排序，无法进行快速查找了～ 而且如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记录还可能要做回表操作，这样性能损耗就更大了。

所以结论就是：最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。 

索引列的类型尽量小

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT这么几种，它们占用的存储空间依次递增，我们这里所说的类型大小指的就是该类型表示的数据范围的大小。 

能表示的整数范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT 。

  • 数据类型越小，在查询时进行的比较操作越快（CPU层次）

  • 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

 这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

索引字符串值的前缀

一个字符串其实是由若干个字符组成，如果我们在MySQL中使用utf8字符集去存储字符串的话，编码一个字符需要占用1~3个字节。假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。

  • B+树索引中的记录需要把该列的完整字符串存储起来，而且字符串越长，在索引中占用的存储空间越大。

  • 如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。 

这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，再对比就好了。 

CREATE TABLE person_info(
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

 name(10)就表示在建立的B+树索引中只保留记录的前10个字符的编码，这种只索引字符串值的前缀的策略是非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候。

create index idx_name on student(stu_name(10));

索引列前缀对排序的影响

使用前缀索引只能采用文件排序（file sorting)了。

主键插入顺序

 插入主键为9记录可这个数据页已经满了啊，再插进来咋办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗！

让主键具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入。 

CREATE TABLE person_info(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

 主键列id拥有AUTO_INCREMENT属性，在插入记录时存储引擎会自动为我们填入自增的主键值。

 为什么大厂在大表做水平分表时严禁使用自增主键

大表为什么不能用自增主键？UUID是好的替代方案嘛？SnowFlake雪花算法是什么？

自增主键，在分布式数据环境下有着严重的问题。

由于自增主键必须连续，所以按范围法进行分片，ID的数量已固定。无法进行动态扩展。

自增主键必须连续，只能采用“范围分片“形式，会产生”尾部热点“效应

Hash分片的效率更高。 

尾部热点：即按范围法进行分片后，前面的分片已储存数据，最后一个分片的压力很大。

使用UUID替代自增主键吗？不可以。 

使用UUID唯一无序，作为主键会涉及大量索引重排。

怎么解决？分布式且有序的主键生成算法？

雪花算法（SnowFlake）

生成一个定长的数字，数字就可以作为记录的新主键，生成的ID还是唯一的。

雪花算法生成的id在集群每台机器中是有序的，不会造成索引重排(会直接添加到尾部)

符号位，并没有实际用处，主要为了兼容长整型的格式。

时间戳，用来记录本地的毫秒时间。

机器ID，机器生成ID的节点，用10位长度为机器做编码，那意味着最大规模可以达到1024个节点

序列，序列长度直接决定了一个节点1毫秒能产生的ID数量，12位就是4096。（每毫秒生成419万个ID）

 雪花算法需要注意时间回拨（往前调时间）带来的影响。可能出现id重复的可能。

为什么表的主键要使用代理主键（自增编号），不建议使用自然主键？

【IT老齐040】面试官：为什么表的主键要使用代理主键（自增编号），不建议使用自然主键？_哔哩哔哩_bilibili

在不做数据库集群的时候，自增组件是最优解；需要集群的时候，用雪花算法生成主键更合适。但不管是自增主键还是雪花算法生成的主键，都属于代理主键。 

业务主键更浪费空间

身份证18位肯定要比uid1位浪费空间。

业务主键无顺序，可能会造成写入数据时需要更长组织索引

就是插入数据的时候，可能会改变B+树结构。

而uid字段数据都是递增的，插入都是在索引树的末端。

业务主键如果是字符串，在分库分表时，无法直接取模运算，需要先转换为数字，处理更麻烦。

架构角度

因为数据的原因导致了系统间的耦合。 

 业务角度

 和业务数据相关的数据是不会物理删除的！把当前状态设为离职状态已经算成无效了。

是新增一条数据，还是更新一条数据呢？

肯定是新增一条数据，为什么不把状态从离职改成在职呢？但状态和离职时间是一个完整的周期。

改的话可能会出问题。

新增数据因为身份证的主键冲突导致无法新增。

主键复杂度越高处理数据越慢!

为啥禁用外键约束

【IT老齐012】阿里开发规范解读：为啥禁用外键约束？_哔哩哔哩_bilibili

每次做DELETE 或者 UPDATE都必须考虑外键约束，会导致开发的时候很痛苦，测试数据极为不方便。

性能问题：额外的数据一致性校验查询。

当插入一条订单明细记录，还得上订单表查询是否有对应的订单ID。 （这是主外键的强制约束）

并发问题：外键约束会启用行级锁，主表写入时会进入阻塞状态。

并发环境下，往订单明细表添加一条数据，会强制查询对应订单表中的订单ID是否存在，所以订单表对应订单ID开启共享锁（共享锁【S锁】，又称为读锁，可以查看但无法修改和删除的一种数据锁）。

某种情况下对订单ID进行更新操作，这时该订单表数据开启排它锁（排他锁【X锁】，又称写锁）。若写锁不被释放，订单明细表处于被锁定的状态。所有订单明细的写操作都会因为订单表的锁定进入一个阻塞的状态，会造成线层积压，系统崩溃。

级联删除问题：多层级联删除会让数据变得不可控，触发器严禁使用。

级联就是自动删除以当前记录的相应字段为外键的记录

比如删除类型ID为1的数据，相应的订单表中订单类型为1的数据都被删除了（订单ID也被删除了），因此订单明细表中的订单ID也被删除。假如十几张表都产生了关联的情况，删了什么数据产生了什么影响，都是在数据库层面上自动完成的无法追溯的很麻烦。

因此基于主外键级联更新和级联删除都是严格禁用的！

 触发器也严格被禁用

比如在某个字段绑定的触发条件，数据被删除了触发后续操作，这些措施都是为了保证数据的健壮以及数据的可追溯。

数据耦合：数据库层面数据关系产生耦合，数据迁移维护困难。

订单明细表数据增长，数据量10亿后，需要迁移到Hbase大数据nosql进行有效存储。这时数据的类型都不一样了，数据不在同一个库，没有了主外键约束，程序中无校验代码，就会产生属于一致性问题。

冗余和重复索引

有时候有意或者无意的就对同一个列创建了多个索引，比方说这样写建表语句： 

CREATE TABLE person_info(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
    KEY idx_name (name(10)) /**数据name列上建了两个索引*/
);

通过idx_name_birthday_phone_number索引就可以对name列进行快速搜索，再创建一个专门针对name列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

另一种情况：

CREATE TABLE repeat_index_demo (
    c1 INT PRIMARY KEY,
    c2 INT,
    UNIQUE uidx_c1 (c1),
    INDEX idx_c1 (c1)
);

c1既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

索引失效

全表扫描还是走索引（重点）

也就是通过索引扫了表的30%的数据，会导致走全表扫描。

然而这个由优化器决定，不一定就是30%。 

对索引使用左或者左右模糊匹配

使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。

联合索引为(stu_name,stu_age,stu_phone)

explain select stu_name  from student where stu_name like '%B';
explain select * from student where stu_name like '%B';

 type 为 all 代表着全表扫描！

explain select * from student where stu_name like 'B%';
explain select stu_name from student where stu_name like 'B%';

type 为 range 用索引范围扫描！并且用了索引下推节省磁盘IO。

为什么左模糊匹配就能走索引呢？

之前讲过的了其实，只需要想象用name排序比如（aa ， ab）

用 like a% 能很快匹配 但是用 like %a就没法匹配。

对索引使用函数

select * from student where length(stu_name) = 5;

type = all 走的全表扫描 

为什么走的全表扫描？很简单因为函数计算出来的值和原表数据不一致。

从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引。

alter table student add key idx_stu_name_length ((length(stu_name)));

key = idx_stu_name_length 代表走了该索引。

type = ref 索引查找后返回所有匹配 length(stu_name) = 5的记录。 

对索引进行表达式计算

explain select * from student where id = 5 - 1;
explain select * from student where id - 1 = 5;

type = const 代表当前SQL查询条件中的值是唯一的 

key = primary 走的是主键索引。

对索引隐式类型转换 

 stu_name 类型为varchar 但是输入的是 int类型导致全表扫描。

id 类型为 int 输入的是 字符串类型 为什么还是走了索引？ 

MySQL的数据类型转换规则，也就是数字转字符串，字符串转数字。

 select "10" > 9

字符串转数字  10 > 9 结果应是1

数字转字符串 "10" > "9"  "1"和"9"比较ascii 1字符比9字符小，结果应是0

 

MySQL会把字符串和数字比较时，会把字符串转为数字。 

联合索引非最左匹配 

联合索引为(stu_name,stu_age,stu_phone)

如(stu_name,stu_phone)可以采用索引下推 来避免全表扫描！

 如 stu_phone 全表扫描索引失效

 如 stu_age,stu_phone 全表扫描索引失效

如  stu_age 全表扫描索引失效

WHERE 子句中的 OR（补档）

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

OR 的含义就是两个只要满足一个即可，只有一个条件列是索引列是没有意义的，因此会触发全表扫描。

 就算是联合索引，age还是无序的因此还是全表扫描！

type=index merge， index merge 的意思就是对 name和 id_card分别进行了扫描，然后将这两个结果集进行了合并，这样做的好处就是避免了全表扫描。

补档：

七分钟实操 MySQL 初级到高级调优 #MySQL#Java#优化#后端#源码#程序员_哔哩哔哩_bilibili

or 的两边都是索引列 

modifed是索引列

explain select * from order_info where modified = '2019-05-21 02:10:11' or modified = '2019-05-27 16:32:54';

 or 的两边是不同索引列

explain select * from order_info where modified = '2019-05-21 02:10:11' or period = 202201;

type = index_merge 索引合并

 用 union all 替代 or呢？

explain select * from order_info where modified = '2019-05-21 02:10:11' 
union all
select * from order_info where period = 202201;

 A 也就是 modified 的值

B 也就是 period 的值

A union all B => A ∪ B

A or B => A ∪ B 但是会去重！

执行union会去重 

是否要全表扫描呢？

create table t(
a int not null,
b int not null,
c int not null,
id int not null auto_increment,
primary key (id) using btree,
key abc (a,b,c)
);

insert into t values(1,2,3,1),(2,3,4,2),(3,4,5,3);

主键：id

联合索引： a,b,c

explain select * from t where c = 1;

为什么没有遵守最左原则也可以走索引。 

type = index 代表全索引扫描

 * 中包含 a,b,c,id

联合索引树也就是二级索引树，包含a,b,c,id（主键）因此就不用全表扫描了，直接通过索引树返回就行。

 遵守最左原则的话找的更快 type = ref 效率高于 index。

再加一个字段呢？

alter table t add column(d int);

因为d字段不是索引 ，需要在原表查找。

那创建一个索引呢？

create index idx_d on t (d);

还是全表扫描，为什么？d字段和a，b，c字段分别是两颗b+树 

哎！我有个天才想法！

总结

所有结论都需要反复测试！如果有错误欢迎指正！一起努力！

如果喜欢的话，请点个赞吧就算鼓励我一下。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值