MySQL实战45讲笔记（四）

yuanbo_小菜鸡

已于 2022-11-08 16:47:10 修改

阅读量1.4k

点赞数

分类专栏： MySQL 文章标签： mysql

于 2022-11-03 17:43:28 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_39892358/article/details/127672623

版权

MySQL 专栏收录该内容

5 篇文章

订阅专栏

第九讲：普通索引和唯一索引

1. 普通索引与唯一索引的区别

普通索引可重复，唯一索引和主键一样不能重复。唯一索引可作为数据的一个合法验证手段，例如学生表的身份证号码字段，我们人为规定该字段不得重复，那么就使用唯一索引。（一般设置学号字段为主键）

主键保证数据库里面的每一行都是唯一的，比如身份证，学号等，在表中要求唯一，不重复。唯一索引的作用跟主键的作用一样。 不同的是唯一索引可以有多个，唯一索引可以有一条记录为空，即保证跟别人不一样就行。

2.执行查询的语句流程：select id from T where k=5

对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

3. change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。虽然名字叫作change buffer，实际上它是可以持久化的数据。change buffer在内存中有拷贝，也会被写入到磁盘上。

4. 什么条件下可以使用change buffer呢？

对于唯一索引来说，所有的更新操作都要先判断操作是否违反唯一性约束。因此 唯一索引的更新不能使用change buffer，实际上也只有普通索引可以使用。

5. 如果要在这张表中插入新记录，InnoDB的处理流程是怎样的。

第一种情况，这个记录要更新的目标页在内存中：（普通索引和唯一索引对更新语句性能影响的差别不大）

对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

第二种情况，这个记录要更新的目标页不在内存中 ：（change buffer因为减少了随机磁盘访问，对更新性能的提升很明显。）

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。

6. change buffer的使用场景

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。

7. redo log 与 change buffer的区别

redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

第十讲：MySQL为什么有时候会选错索引？

对于由于索引统计信息不准确导致的问题，你可以用analyze table来解决。
而对于其他优化器误判的情况，你可以在应用端用force index来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

第十一讲：怎么给字符串字段加索引？

当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？
实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

查询一个字段不同值的个数：

mysql> select count(distinct email) as L from SUser;

mysql> select
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;

创建索引的方式有：
1. 直接创建完整索引，这样可能比较占用空间；
2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4. 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

第十二讲：SQL语句，平时特别快，偶尔变得特别慢并很难复现的原因

InnoDB在处理更新语句的时候，只做了写日志redo log（重做日志）这一个磁盘操作。在更新内存写完redo log后，就返回给客户端，本次更新成功。

flush：把内存里的数据写入磁盘的过程。

当内存数据页跟磁盘数据页内容不一致的时候，称这个内存页为“脏页”。

内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

不论是脏页还是干净页，都在内存中。

平时执行很快的更新操作，其实就是在写内存和日志，而MySQL偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

利用WAL（Write-Ahead Logging 预写日志系统）技术，数据库将随机写转换成了顺序写，大大提升了数据库的性能。但是，由此也带来了内存脏页的问题。脏页会被后台线程自动flush，也会由于数据页淘汰而触发flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。

第十三讲：为什么表数据删掉一半，表文件大小不变？

数据定义语言 (Data Definition Language, DDL) 负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成。
数据操纵语言（Data Manipulation Language, DML）负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。
数据查询语言（Data Query Language, DQL）负责进行数据查询而不会对数据本身进行修改的语句，保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有FROM，WHERE，GROUP BY，HAVING和ORDER BY。
数据控制语言 (Data Control Language) 是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制用户的访问权限为主，GRANT为授权语句，对应的REVOKE是撤销授权语句。
MDL锁（metadata lock）保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。

delete命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

实际上，不止是删除数据会造成空洞，插入数据也会。如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。（插入数据的顺序不同，b+树的样子不同）

MySQL 5.6版本开始引入的Online DDL，重建表的流程：

1. 建立一个临时文件，扫描表A主键的所有数据页；
2. 用数据页中表A的记录生成B+树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（rowlog）中，对应的是图
中state2的状态；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的
数据文件，对应的就是图中state3的状态；
5. 用临时文件替换表A的数据文件。

结论：如果要收缩一个表，只是delete掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过alter table命令重建表，才能达到表文件变小的目的。我跟你介绍了重建表的两种实现方式，1. Online DDL的方式是可以考虑在业务低峰期使用的，2. MySQL 5.5及之前的版本，这个命令是会阻塞DML的

ALTER TABLE用法

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

ALTER 语句中可以指定参数 ALGORITHM 和 LOCK 分别指定 DDL 执行的方式和 DDL 期间 DML 的兵法控制

ALGORITHM=INPLACE 表示执行DDL的过程中不发生表拷贝，过程中允许并发执行DML（INPLACE不需要像COPY一样占用大量的磁盘I/O和CPU，减少了数据库负载。同时减少了buffer pool的使用，避免 buffer pool 中原有的查询缓存被大量删除而导致的性能问题）。
如果设置 ALGORITHM=COPY，DDL 就会按 MySQL 5.6 之前的方式，采用表拷贝的方式进行，过程中会阻塞所有的DML。另外也可以设置 ALGORITHEM=DAFAULT，让 MySQL 以尽量保证 DML 并发操作的原则选择执行方式。
LOCK=NONE 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的请求，适用于需要尽快完成DDL或者服务库空闲的场景）、SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE，适用于数据仓库等可以允许数据写入延迟的场景）和 DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）