MySQL底层详解及优化

南辞灬

已于 2022-11-28 16:03:52 修改

阅读量780

点赞数

分类专栏：热门技术文章标签： mysql b树数据库

于 2022-11-28 15:55:53 首次发布

本文链接：https://blog.csdn.net/GodTao111/article/details/128081416

版权

热门技术专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL底层和优化

索引结构

什么是索引？

索引是帮助MySQL高效获取数据的排好序的数据结构

索引数据结构

二叉树
红黑树
Hash表
B-Tree

红黑树存储索引怎么样？

红黑树是一颗平衡二叉树，数据量大的时候，树的深度也很深，如果树的深度有20层，而查找的数据在叶子节点，就要进行20次IO操作，性能低。

为什么不用二叉树？

如果碰到下面这种单边增长的极端情况，查找节点4和顺序查找没区别

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8SyK2tvx-1669621823991)(MySQL底层和优化.assets/image-20221125175429825.png)]

为什么不用B树？

B树的特点：

叶子节点具有相同的深度，叶子节点的指针为空
所有索引元素不重复
叶子节点中的数据key从左到右递增排列

其实B树就是在横向做了文章，一个节点可以存储更多数据(大节点包含很多小节点)，这样相对来说，深度就会变浅。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AnmkX7Cq-1669621823992)(MySQL底层和优化.assets/image-20221125175619435.png)]

提问：横向的节点怎么查，比如说查找上图中的节点77 ？

从磁盘中把大节点查找出来，把这个大节点加载进内存中，节点77实际上是在内存中查找的，在内存中做的是随机访问，速度很快，跟磁盘的寻道和旋转相比的话，基本可以忽略不计。
提问：为什么不可以让B树横向的度无限增大，这样不就深度为1，查找不就更快了？(度的含义：节点的数据存储个数)

本来是想通过一次IO操作把一个大节点加载进内存，如果一个大节点的数据量太大的话，则内存和硬盘一次交互没办法交换那么多数据，假设一次只能交换1页(4k)的数据(有上限，也有可能是几十页，和计算机硬件有关)，意味着CPU去硬盘上做一次IO操作只能取1页的数据，那么当一个大节点的数据量太大时，仍要进行多次IO操作。因此，度是有上限的，MySQL会根据计算机硬件自动进行度的优化，

为什么使用B+树？(B+树是B树的变种，索引做了冗余，存了多份，但是没关系，索引只占很小空间，比如下图中的15节点)

B+树的特点：

非叶子节点不存储data，只存储key，可以增大度(相比B树，B+树的深度更浅)
叶子节点不存储指针
顺序访问指针，提高区间访问的性能(实际上是双向指针)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oRkKdZLK-1669621823992)(MySQL底层和优化.assets/image-20221125175858469.png)]

B+树索引的性能分析：

一般使用磁盘I/O次数评价索引结构的优劣

预读：磁盘一般会顺序向后读取一定长度的数据(页的整数倍)放入内存

局部性原理：当一个数据被用到时，其附近的数据也通常会立马被使用

B+树的大节点大小设为等于一个页，每次新建大节点直接申请一个页的空间，这能保证一个大节点物理上也存储在一个页里，大节点载入只需一次IO操作

B+树的度d一般会超过100，因此高度h非常小(一般为3~5之间)

适合做数据库的存储结构

一般存储方式：内存(适合小数据量)、磁盘(大数据量)。
磁盘的运转方式：速度 + 旋转

磁盘页的概念：每一页大概16KB。

不适合做MySql的数据结构及其原因

数组和链表的缺点就是数据量大的时候存不了，也就是说不合适大数据量。

哈希是通过hash函数计算出一个hash值的，存在哈希碰撞的情况，另外哈希也不支持部分索引查询以及范围查找。但是哈希的优点就是查找的时间复杂度是O(1)，那么什么情况下可以使用hash索引呢？就是查询条件不会变，而且没有部分查询和范围查询的时候。

红黑树存储的数据量大的时候，红黑树的节点层数多，也就是树的高度比较高，查找的底层数据时，查找次数就比较多，即对磁盘IO使用比较频繁。总结为以下两点：

读取浪费太多：通过计算本来树的每一层大概需要分配16KB的数据，但是对于红黑树来说，实际存的节点数比较少，即存的数据大小远远小于16KB，从而造成存储空间的浪费
读取磁盘的次数过多：树的层数越多，查找数据时读取磁盘的次数也就越多

MySQL引擎

Mysql中存在两种存储引擎：Myisam存储引擎以及InnoDB存储引擎
Myisam属于表级别的非聚集存储引擎，而InnoDB则属于表级别的聚集存储引擎。

非聚集以及聚集的区别：

通过数据是否与索引隔离而判断：在Mysql文件中默认MyISAM存储引擎的表中存在两个文件即 tableName.MYI 以及 tableName.MYD ，以MYI结尾的文件中存储的是该表的索引，以MYD结尾的存储是数据。
而默认InnoDB存储引擎的表中只存在.idb结尾的文件，该文件中存放了索引以及文件。

1. InnoDB（聚集索引：数据和索引放在一块）现在用的最多的

frm文件是表结构信息

ibd文件是索引和数据放一块

InnoDB引擎采用B+Tree结构来作为索引结构

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，B-Tree中每个节点中有key，也有data，而每一页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小。当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

在B+Tree中所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度；

B+Tree在B-Tree的基础上有两点变化：

数据是存在叶子节点中的
数据节点之间是有指针指向的

由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aqkq7dbW-1669621823993)(MySQL底层和优化.assets/70-1669621730825-3.png)]

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。

因此可以对B+Tree进行两种查找运算，一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

InnoDB是以ID为索引的数据存储

采用InnoDB引擎的数据存储文件有两个，一个定义文件，一个是数据文件。

InnoDB通过B+Tree结构对ID建索引，然后在叶子节点中存储记录

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oUrGkBcm-1669621823993)(MySQL底层和优化.assets/70.png)]

若建立索引的字段不是主键ID，则对该字段建索引，然后在叶子节点中存储的是该记录的主键，然后通过主键索引找到对应记录

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID）

1.事务的ACID属性：即原子性、一致性、隔离性、持久性

原子性：原子性也就是说这组语句要么全部执行，要么全部不执行，如果事务执行到一半出现错误，数据库就要回滚到事务开始执行的地方。
一致性：事务开始前和结束后，数据库的完整性约束没有被破坏。（eg:比如A向B转账，不可能A扣了钱，B却没有收到）
隔离性：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰；

如果不考虑隔离性则会出现几个问题：

**a、脏读：**是指在一个事务处理过程里读取了另一个未提交的事务中的数据（当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致）；（读取了另一个事务未提交的脏数据）

**b、不可重复读：**在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了；（读取了前一个事务提交的数据，查询的都是同一个数据项）

**c、虚读（幻读）：**是事务非独立执行时发生的一种现象（eg:事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样）；（读取了前一个事务提交的数据，针对一批数据整体）

持久性：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚

2.InnoDB是MySql默认的存储引擎

默认的隔离级别是RR，并且在RR的隔离级别下更近一步，通过多版本并发控制（MVCC）解决不可重复读问题，加上间隙锁（也就是并发控制）解决幻读问题。因此InnoDB的RR隔离级别其实实现了串行化级别的效果，而保留了比较好的并发性能。

MySQL数据库为我们提供的四种隔离级别：

a、Serializable（串行化）：可避免脏读、不可重复读、幻读的发生；

b、Repeatable read（可重复读）：可避免脏读、不可重复读的发生；

c、Read committed（读已提交）：可避免脏读的发生；

d、Read uncommitted（读未提交）：最低级别，任何情况都无法保证；

从a----d隔离级别由高到低，级别越高，执行效率越低

3. InnoDB支持行级锁。行级锁可以最大程度的支持并发，行级锁是由存储引擎层实现的。

**锁：**锁的主要作用是管理共享资源的并发访问，用于实现事务的隔离性

类型：共享锁（读锁）、独占锁（写锁)

**MySQL锁的力度：**表级锁（开销小、并发性低），通常在服务器层实现

行级锁（开销大、并发性高），只会在存储引擎层面进行实现

4. InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何基于磁盘的关系型数据库引擎所不能匹敌的

5、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。

InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）；

6、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按照主键顺序存放，如果没有显示在表定义时指定主键。

InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键

7、InnoDB被用在众多需要高性能的大型数据库站点上

2. Myisam（非聚集索引：数据和索引不放在一个文件里）

优点：可被压缩，节省空间，可以转化为只读表，提高检索效率

缺点：不支持事务

frm文件是表结构信息

MYD文件是表数据：D data

MYI文件是表索引：I index

Memory存储引擎

每个表均以.frm文件存储

表级锁机制

不包含TEXT和BLOB字段

缺点：不支持事务，容易丢失，所有数据和索引存储在内存当中，断电就没了

有点查询速度快

3. 存储结构

InnoDB 和 Myisam 都是用 B+Tree 来存储数据的。

MySQL日志

MySQL中有以下日志文件，分别是：

1：重做日志（redo log）

2：回滚日志（undo log）

3：二进制日志（binlog）

4：错误日志（errorlog）

5：慢查询日志（slow query log）

6：一般查询日志（general log）

7：中继日志（relay log）。

其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系，这三种日志，对理解MySQL中的事务操作有着重要的意义。

一、重做日志（redo log）

作用：

确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

内容：

物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

什么时候产生：

事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。

什么时候释放：

当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

对应的物理文件：

默认情况下，对应的物理文件位于数据库的data目录下的ib_logfile1&ib_logfile2

innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。

innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认2

关于文件的大小和数量，由以下两个参数配置：

innodb_log_file_size 重做日志文件的大小。

innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1

redo log是什么时候写盘的？前面说了是在事物开始之后逐步写盘的。

之所以说重做日志是在事务开始之后逐步写入重做日志文件，而不一定是事务提交才写入重做日志缓存，原因就是，重做日志有一个缓存区Innodb_log_buffer，Innodb_log_buffer的默认大小为8M(这里设置的16M),Innodb存储引擎先将重做日志写入innodb_log_buffer中。

然后会通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘

Master Thread 每秒一次执行刷新Innodb_log_buffer到重做日志文件。
每个事务提交时会将重做日志刷新到重做日志文件。
当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件

由此可以看出，重做日志通过不止一种方式写入到磁盘，尤其是对于第一种方式，Innodb_log_buffer到重做日志文件是Master Thread线程的定时任务。

因此重做日志的写盘，并不一定是随着事务的提交才写入重做日志文件的，而是随着事务的开始，逐步开始的。

另外引用《MySQL技术内幕 Innodb 存储引擎》（page 37）上的原话：

即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。

这一点是必须要知道的，因为这可以很好地解释再大的事务的提交（commit）的时间也是很短暂的

二、回滚日志（undo log）

作用：

保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

内容：

逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

什么时候产生：

事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

什么时候释放：

当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

对应的物理文件：

MySQL 5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。

MySQL 5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数

如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

关于MySQL 5.7之后的独立undo 表空间配置参数如下：

　innodb_undo_directory = /data/undospace/ –undo独立表空间的存放目录 innodb_undo_logs = 128 –回滚段为128KB innodb_undo_tablespaces = 4 –指定有4个undo log文件

　　如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为与MySQL的数据目录下面，其属性由参数innodb_data_file_path配置。

其他：

undo是在事务开始之前保存的被修改数据的一个版本，产生undo日志的时候，同样会伴随类似于保护事务持久化机制的redolog的产生。

默认情况下undo文件是保持在共享表空间的，也即ibdatafile文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的undo信息，全部保存在共享表空间中的。

因此共享表空间可能会变的很大，默认情况下，也就是undo 日志使用共享表空间的时候，被“撑大”的共享表空间是不会也不能自动收缩的。

因此，mysql 5.7之后的“独立undo 表空间”的配置就显得很有必要了。

三、二进制日志（bin log）

作用：

用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。

用于数据库的基于时间点的还原。

内容：

逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。

但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。

在使用mysql binlog解析binlog之后一些都会真相大白。

因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

什么时候产生：

事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。

这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。

因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。

这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

什么时候释放：

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

对应的物理文件：

配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。

对于每个binlog日志文件，通过一个统一的index文件来组织。

其他：

二进制日志的作用之一是还原数据库的，这与redo log很类似，很多人混淆过，但是两者有本质的不同

作用不同：redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。

内容不同：redo log是物理日志，是数据页面的修改之后的物理记录，binlog是逻辑日志，可以简单认为记录的就是sql语句

另外，两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。

恢复数据时候的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的binlog

关于事务提交时，redo log和binlog的写入顺序，为了保证主从复制时候的主从一致（当然也包括使用binlog进行基于时间点还原的情况），是要严格一致的，MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

四、错误日志

错误日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。

指定日志路径两种方法:

编辑my.cnf 写入 log-error=[path]
通过命令参数错误日志 mysqld_safe –user=mysql –log-error=[path] &

显示错误日志的命令：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qz3qsPYn-1669621823993)(MySQL底层和优化.assets/image-20221128114529358.png)]

五、普通查询日志 general query log

记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来，记录的格式为 {Time ，Id ，Command，Argument }。也正因为mysql服务器需要不断地记录日志，开启General log会产生不小的系统开销。因此，Mysql默认是把General log关闭的。

查看日志的存放方式：

show variables like ‘log_output’;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2rt4KNsO-1669621823993)(MySQL底层和优化.assets/885859-20190418111501042-772781517.png)]

如果设置mysql> set global log_output=’table’ 的话，则日志结果会记录到名为gengera_log的表中，这表的默认引擎都是CSV

如果设置表数据到文件

set global log_output=file;

设置general log的日志文件路径：

set global general_log_file=’/tmp/general.log’;

开启general log：

set global general_log=on;

关闭general log：

set global general_log=off;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LfAfkUFS-1669621823994)(MySQL底层和优化.assets/885859-20190418111533830-280934584.png)]

查看

show global variables like ‘general_log’

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7TqpCGyO-1669621823994)(MySQL底层和优化.assets/885859-20190418111551171-1738333923.png)]

六、慢查询日志

慢日志记录执行时间过长和没有使用索引的查询语句，报错select、update、delete以及insert语句，慢日志只会记录执行成功的语句。

查看慢查询时间：

show variables like “long_query_time”; 默认10s

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0MEInDRG-1669621823994)(MySQL底层和优化.assets/885859-20190418111638450-1186782386.png)]

查看慢查询配置情况：

show status like “%slow_queries%”;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gHAShxk6-1669621823994)(MySQL底层和优化.assets/885859-20190418111656450-521011638.png)]

查看慢查询日志路径：

show variables like “%slow%”;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YdZNJGPz-1669621823994)(MySQL底层和优化.assets/885859-20190418111712973-766266117.png)]

开启慢日志

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0G3EAkW5-1669621823995)(MySQL底层和优化.assets/885859-20190418111737882-1420825238.png)]

查看已经开启：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cdzycA5p-1669621823995)(MySQL底层和优化.assets/885859-20190418111753391-1884429309.png)]

优化

MySQL层优化我一般遵从五个原则：

减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO
返回更少的数据：只返回需要的字段和数据分页处理减少磁盘io及网络io
减少交互次数：批量DML操作，函数存储等减少数据连接次数
减少服务器CPU开销：尽量减少数据库排序操作以及全表查询，减少cpu 内存占用
利用更多资源：使用表分区，可以增加并行操作，更大限度利用cpu资源

总结到SQL优化中，就三点:

最大化利用索引；
尽可能避免全表扫描；
减少无效数据的查询；

SELECT语法顺序：

1. SELECT 
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10.LIMIT <limit_number>

SELECT执行顺序：

FROM <表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
ON <筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN<join, left join, right join…>
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE <where条件> # 对上述虚表进行筛选
GROUP BY <分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING <分组筛选> # 对分组后的结果进行聚合筛选
SELECT <返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT # 数据除重
ORDER BY <排序条件> # 排序
LIMIT <行数限制>

from -> on|using ->where -> group by -> having ->select ->order by -> limit可以看到，连接的条件是先于where的，也就是先连接获得结果集后，才对结果集进行where筛选，所以在使用join的时候，我们要尽可能提供连接的条件，而少用where的条件，这样才能提高查询性能。

MySQL查询过程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2CdSGDRD-1669621823995)(MySQL底层和优化.assets/v2-3158800935bdbd30a57c2263ac8b5eb4_720w.webp)]

索引相关优化

前缀索引

如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率。

多列索引和索引顺序

索引的顺序对于查询是至关重要的，很明显应该把选择性更高的字段放到索引的前面，这样通过第一个字段就可以过滤掉大多数不符合条件的数据。

索引选择性是指不重复的索引值和数据表的总记录数的比值，选择性越高查询效率越高，因为选择性越高的索引可以让MySQL在查询时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

避免多个范围条件

select user.* from user where login_time > '2017-04-01' and age between 18 and 30;

这个查询有一个问题：它有两个范围条件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但无法同时使用它们。

覆盖索引

如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：

索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量
索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

使用索引扫描来排序

MySQL有两种方式可以生产有序的结果集，其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的。如果explain的结果中type列的值为index表示使用了索引扫描来做排序。

扫描索引本身很快，因为只需要从一条索引记录移动到相邻的下一条记录。但如果索引本身不能覆盖所有需要查询的列，那么就不得不每扫描一条索引记录就回表查询一次对应的行。这个读取操作基本上是随机I/O，因此按照索引顺序读取数据的速度通常要比顺序地全表扫描要慢。

在设计索引时，如果一个索引既能够满足排序，又满足查询，是最好的。

有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向也一样时，才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序。ORDER BY子句和查询的限制是一样的，都要满足最左前缀的要求（有一种情况例外，就是最左的列被指定为常数，下面是一个简单的示例），其他情况下都需要执行排序操作，而无法利用索引排序。

冗余和重复索引

冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。比如有一个索引(A,B)，再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)。

大多数情况下都应该尽量扩展已有的索引而不是创建新索引。但有极少情况下出现性能方面的考虑需要冗余索引，比如扩展已有索引而导致其变得过大，从而影响到其他使用该索引的查询。

删除长期未使用的索引

定期删除一些长时间未使用过的索引是一个非常好的习惯。索引并不总是最好的工具，只有当索引帮助提高查询速度带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，简单的全表扫描更高效。对于中到大型的表，索引就非常有效。对于超大型的表，建立和维护索引的代价随之增长，这时候其他技术也许更有效，比如分区表。

避免不走索引的场景

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t WHERE username LIKE '%陈%'

**优化方式：**尽量在字段后面使用模糊查询。

SELECT * FROM t WHERE username LIKE '陈%'

如果需求是要在前面使用模糊查询

使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置
使用FullText全文索引，用match against 检索
数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级
当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like ‘%xx%’。

2. 尽量避免使用in 和not in，会导致引擎走全表扫描。

SELECT * FROM t WHERE id IN (2,3)

优化方式：如果是连续数值，可以用between代替。

如果是子查询，可以用exists代替。

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。

5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

优化方式：可以将表达式、函数操作移动到等号右侧。

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

6. 当数据量大时，避免使用where 1=1的条件。

通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。

SELECT username, age, sex FROM T WHERE 1=1

**优化方式：**用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。或者直接在mybatis里面使用标签

7. 查询条件不能用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

8. where条件仅包含复合索引非前置列

复合（联合）索引包含key_part 1，key_part 2，key_part 3三列，但SQL语句没有包含索引前置列"key_part1"，按照MySQL联合索引的最左匹配原则，不会走联合索引。

select col1 from table where key_part2=1 and key_part3=2

9. 隐式类型转换造成不使用索引

由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

select col1 from table where col_varchar=123;

10. order by 条件要与where中条件一致，否则order by不会利用索引进行排序

-- 不走age索引
SELECT * FROM t order by age;

-- 走age索引
SELECT * FROM t where age > 0 order by age;

数据库的处理顺序是：

第一步：根据where条件和统计信息生成执行计划，得到数据。
第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
第三步：返回排序后的数据。

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

SELECT语句其他优化

**1. 避免出现select **

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

2. 避免出现不确定结果的函数

特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。

3.多表关联查询时，小表在前，大表在后。

在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。

例如：表1有50条数据，表2有30亿条数据；如果全表扫描表2，你品，那就先去吃个饭再说吧是吧。

4. 使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。

5. 用where字句替换HAVING字句

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

where和having的区别：where后面不能使用组函数

6.调整Where字句中的连接顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

增删改 DML 语句优化

1. 大批量插入数据

如果同时执行大量的插入，建议使用多个值的INSERT语句(方法二)。这比使用分开INSERT语句快（方法一），一般情况下批量插入效率有几倍的差别。

方法一：

insert into T values(1,2); 
insert into T values(1,3); 
insert into T values(1,4);

方法二：

Insert into T values(1,2),(1,3),(1,4);

选择方法二的原因：

减少SQL语句解析的操作，MySQL没有类似Oracle的share pool，采用方法二，只需要解析一次就能进行数据的插入操作；
在特定场景可以减少对DB连接次数
SQL语句较短，可以减少网络传输的IO。

2. 适当使用commit

适当使用commit可以释放事务占用的资源而减少消耗，commit后能释放的资源如下：

事务占用的undo数据块；
事务在redo log中记录的数据块；
释放事务施加的，减少锁争用影响性能。特别是在需要使用delete删除大量数据的时候，必须分解删除量并定期commit。

3. 避免重复查询更新的数据

针对业务中经常出现的更新行同时又希望获得改行信息的需求，MySQL并不支持PostgreSQL那样的UPDATE RETURNING语法，在MySQL中可以通过变量实现。

例如，更新一行记录的时间戳，同时希望查询当前记录中存放的时间戳是什么，简单方法实现

Update t1 set time=now() where col1=1; 
Select time from t1 where id =1;

使用变量，可以重写为以下方式：

Update t1 set time=now () where col1=1 and @now: = now (); 
Select @now;

前后二者都需要两次网络来回，但使用变量避免了再次访问数据表，特别是当t1表数据量较大时，后者比前者快很多。

查询条件优化

1. 对于复杂的查询，可以使用中间临时表暂存数据；

2. 优化group by语句

默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，如 “GROUP BY col1，col2，…;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，…;” 如果显式包括一个包含相同的列的 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。

因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序。例如：

SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

3. 优化join语句

MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)…替代。

例子：假设要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

如果使用连接(JOIN)来完成这个查询工作，速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话，性能将会更好，查询如下：

SELECT col1 FROM customerinfo 
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID 
WHERE salesinfo.CustomerID IS NULL

连接(JOIN)之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

4. 优化union查询

MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。

高效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
UNION ALL 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

低效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
UNION 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

5.拆分复杂SQL为多个小SQL，避免大事务

简单的SQL容易使用到MySQL的QUERY CACHE；
减少锁表时间特别是使用MyISAM存储引擎的表；
可以使用多核CPU。

6. 使用truncate代替delete

当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。

使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零。

7. 使用合理的分页方式以提高分页效率

使用合理的分页方式以提高分页效率针对展现等分页需求，合适的分页方式能够提高分页的效率。

案例1：

select * from t where thread_id = 10000 and deleted = 0 
order by gmt_create asc limit 0, 15;

上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引IO+索引全部记录结果对应的表数据IO。因此，该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。

**适用场景：**当中间结果集很小（10000行以下）或者查询条件复杂（指涉及多个不同查询字段或者多表连接）时适用。

案例2：

select t.* from (select id from t where thread_id = 10000 and deleted = 0
order by gmt_create asc limit 0, 15) a, t 
where a.id = t.id;

上述例子必须满足t表主键是id列，且有覆盖索引secondary key:(thread_id, deleted, gmt_create)。通过先根据过滤条件利用覆盖索引取出主键id进行排序，再进行join操作取出其他字段。数据访问开销=索引IO+索引分页后结果（例子中是15行）对应的表数据IO。因此，该写法每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。

适用场景：当查询和排序字段（即where子句和order by子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。

建表优化

1. 在表中建立索引，优先考虑where、order by使用到的字段。

2.尽量使用数字型字段（如性别，男：1 女：2）

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

3.查询数据量大的表会造成查询缓慢。

主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。要查询100000到100050的数据

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* 
FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

4.用varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。