**innoDB存储引擎(**5.5版本开始默认) : 支持事务 ,占用磁盘空间大 ,支持并发控制。表结构保存在.frm文件中，如果是共享表空间，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。如果是多表空间存储，每个表的数据和索引单独保存在 .ibd 中。

MEMORY存储引擎 :

内存存储 , 速度快 ,不安全 ,适合小量快速访问的数据。表结构保存在.frm中。

特性对比 :

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储限制	64TB	有	有	没有	有
事务安全	==支持==	ㅤ	ㅤ	ㅤ	ㅤ
锁机制	==行锁(适合高并发)==	==表锁==	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引	ㅤ	ㅤ	支持	ㅤ	ㅤ
全文索引	支持(5.6版本之后)	支持	ㅤ	ㅤ	ㅤ
集群索引	支持	ㅤ	ㅤ	ㅤ	ㅤ
数据索引	支持	ㅤ	支持	ㅤ	支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	ㅤ	支持	ㅤ	ㅤ	ㅤ
空间使用	高	低	N/A	低	低
内存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外键	==支持==	ㅤ	ㅤ	ㅤ	ㅤ

1.2.3 如何选择不同类型的引擎

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

以下是几种常用的存储引擎的使用环境。

InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包含更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。

MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供极快的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。

1.3 SQL的执行流程是什么样的

客户端发送一条查询给服务器。

服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。

服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。

MySQL根据优化器生成的执行计划，再调用存储引擎的API来执行查询。

将结果返回给客户端。

2 MySQL存储引擎调优

2.1 MySQL服务器硬件优化

tips
硬件（cpu、内存等）相关
了解即可

关于提升硬件设备性能：

例如选择尽量高频率的**内存（**频率不能高于主板的支持）、提升网络带宽、使用SSD高速磁盘、提升CPU性能等。

CPU的选择:

对于数据库并发比较高的场景，CPU的数量比频率重要。

对于CPU密集型场景和频繁执行复杂SQL的场景，CPU的频率越高越好

磁盘的选择

影响数据库最大的性能问题就是磁盘I/O 为提高数据库的IOPS性能，可使用SSD或PCIE-SSD高速磁盘设备

磁盘IO的优化

可以用RAID来进行优化

常用RAID（磁盘阵列）级别：

RAID0：也称为条带，就是把多个磁盘链接成一个硬盘使用，这个级别IO最好 RAID1：也称为镜像，要求至少有两个磁盘，每组磁盘存储的数据相同 RAID5：也是把多个（最少3个）硬盘合并成一个逻辑盘使用，数据读写时会建立奇偶校验信息，并且奇偶校验信息和相对应的数据分别存储在不同的磁盘上。当RAID5的一个磁盘数据发生损坏后，利用剩下的数据和响应的奇偶校验信息去恢复被损坏的数据

RAID1+0（建议使用）：就是RAID0和RAID1的组合。同时具备两个级别的优缺点，一般建议数据库使用这个级别。

2.2 MySQL数据库配置优化

tips：
以下为生产环境中最常用的DB参数配置

表示缓冲池字节大小，大的缓冲池可以减少磁盘IO次数。 innodb_buffer_pool_size = 推荐值为物理内存的50%~80%。

用来控制redo log buffer刷新到磁盘的策略。 innodb_flush_log_at_trx_commit=1

每提交1次事务就同步写到磁盘中，可以设置为1。 sync_binlog=1

脏页占innodb_buffer_pool_size的比例，触发刷脏页到磁盘。推荐值为25%~50%。 innodb_max_dirty_pages_pct=30

后台进程最大IO性能指标。默认200，如果SSD，调整为5000~20000

PCIE-SSD可调整为5w左右

默认：innodb_io_capacity=200

指定innodb共享表空间文件的大小。 innodb_data_file_path ＝ ibdata:1G:autoextend：默认10M，

一般设置为1GB

慢查询日志的阈值设置，单位秒。 long_query_time=0.3

合理设置区间0.1s~0.5s，

mysql复制的形式，row为MySQL8.0的默认形式。 binlog_format=row

建议binlog的记录格式为row模式

降低interactive_timeout、wait_timeout的值。

交互等待时间和非交互等待时间，值一致，建议300~500s，默认8小时

数据库最大连接数max_connections=200

过大，实例恢复时间长；过小，造成日志切换频繁。 innodb_log_file_size=默认

redo log空间大小

全量日志建议关闭。默认关闭general_log=0

2.3 Mysql中查询缓存优化

tips：
在MySQL 8.0之后废弃这个功能
原理：复杂、实用性不高
作为了解即可

1）查询缓存概述

开启Mysql的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。

2）操作流程

回顾

客户端发送一条查询给服务器；

服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；

服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；

MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；

将结果返回给客户端。

3）查询缓存配置

查看当前的MySQL数据库是否支持查询缓存：

2. 查看当前MySQL是否开启了查询缓存：

3. 查看查询缓存的占用大小：

4. 查看查询缓存的状态变量：

各个变量的含义如下：

参数	含义
Qcache_free_blocks	查询缓存中的可用内存块数
Qcache_free_memory	查询缓存的可用内存量
Qcache_hits	查询缓存命中数
Qcache_inserts	添加到查询缓存的查询数
Qcache_lowmen_prunes	由于内存不足而从查询缓存中删除的查询数
Qcache_not_cached	非缓存查询的数量（由于 query_cache_type 设置而无法缓存或未缓存）
Qcache_queries_in_cache	查询缓存中注册的查询数
Qcache_total_blocks	查询缓存中的块总数

4）开启查询缓存

MySQL的查询缓存默认是关闭的，需要手动配置参数 query_cache_type ，来开启查询缓存。query_cache_type 该参数的可取值有三个：

值	含义
OFF 或 0	查询缓存功能关闭
ON 或 1	查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定 SQL_NO_CACHE，不予缓存
DEMAND 或 2	查询缓存功能按需进行，显式指定 SQL_CACHE 的SELECT语句才会缓存；其它均不予缓存

在 my.cnf 配置中，增加以下配置：

配置完毕之后，重启服务既可生效；

然后就可以在命令行执行SQL语句进行验证，执行一条比较耗时的SQL语句，然后再多执行几次，查看后面几次的执行时间；获取通过查看查询缓存的缓存命中数，来判定是否走查询缓存。

5）查询缓存SELECT选项

可以在SELECT语句中指定两个与查询缓存相关的选项：

SQL_CACHE : 如果查询结果是可缓存的，并且 query_cache_type 系统变量的值为ON或 DEMAND ，则缓存查询结果。

SQL_NO_CACHE : 服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。

例子：

6）查询缓存失效的情况

tips
需要注意的问题

1） SQL 语句不一致的情况，要想命中查询缓存，查询的SQL语句必须一致。

2）当查询语句中有一些不确定的值，则不会缓存。如： now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。

3）不使用任何表查询语句。

4）查询 mysql， information_schema或 performance_schema 数据库中的表时，不会走查询缓存。

5）在存储的函数，触发器或事件的主体内执行的查询。

6）如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用MERGE映射到已更改表的表的查询。一个表可以被许多类型的语句，如被改变 INSERT， UPDATE， DELETE， TRUNCATE TABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE 。

将查询缓存关闭，因为后面还需要进行索引的验证，所以不希望走查询缓存

2.4. Mysql内存管理及优化

1）内存优化原则

1）将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序预留足够内存。

2） MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。

3）排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽。

2） MyISAM 内存优化

MyISAM 存储引擎使用 key_buffer 缓存索引块，加速myisam索引的读写速度。对于myisam表的数据块，mysql没有特别的缓存机制，完全依赖于操作系统的IO缓存。

key_buffer_size

key_buffer_size决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率。可以在MySQL参数文件中设置key_buffer_size的值，对于一般MyISAM数据库，建议至少将1/4可用内存分配给key_buffer_size。

在my.cnf 中做如下配置：

read_buffer_size

如果需要经常顺序扫描MyISAM 表，可以通过增大read_buffer_size的值来改善性能。但需要注意的是read_buffer_size是每个session独占的，如果默认值设置太大，就会造成内存浪费。

read_rnd_buffer_size

对于需要做排序的MyISAM 表的查询，如带有order by子句的sql，适当增加 read_rnd_buffer_size 的值，可以改善此类的sql性能。

但需要注意的是 read_rnd_buffer_size 是每个session独占的，如果默认值设置太大，就会造成内存浪费。

3） InnoDB 内存优化

innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块。

innodb_buffer_pool_size

该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高。

innodb_log_buffer_size

决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。

2.5. Mysql并发参数调整

从实现上来说，MySQL Server 是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。在Mysql中，控制并发连接和线程的主要参数包括 max_connections、back_log、thread_cache_size、table_open_cahce。

1） max_connections

最大可支持的连接数

采用max_connections 控制允许连接到MySQL数据库的最大数量，默认值是 151。如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这时可以考虑增大max_connections 的值。

Mysql 最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。

2） back_log

积压请求栈大小

back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ，之后的版本默认为 50 + （max_connections / 5），但最大不超过900。

如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log 的值。

3） table_open_cache

执行线程可打开表缓存个数

该参数用来控制所有SQL语句执行线程可打开表缓存的数量，而在执行SQL语句时，每一个SQL执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定：

4） thread_cache_size

缓存客户服务线程的数量

为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数 thread_cache_size 可控制 MySQL 缓存客户服务线程的数量。

5）lock_wait_timeout

innodb_lock_wait_timeout

事务等待行锁的时间

该参数是用来设置InnoDB 事务等待行锁的时间，默认值是50ms ，可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起；对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作。

3 常用慢查询分析工具

3.1 调优工具mysqldumpslow

3.1.1 调优工具常用设置

1、什么是MySQL 慢查询日志

MySQL提供的一种慢查询日志记录，用来记录在MySQL查询中响应时间超过阀值的记录具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中

2、如何查看慢查询设置情况

慢查询的时间阈值设置

解释

slow_query_log //是否开启，默认关闭，建议调优时才开启

slow_query_log_file //慢查询日志存放路径

3、如何开启慢查询日志记录

1) 命令开启

执行成功

再次执行

先关闭客户端连接，再进行重新连接，即可看到设置生效

发现开启了mysqldumpslow调优工具

2）配置文件开启

修改并且重启后

发现开启了mysqldumpslow调优工具

3）哪些 SQL 会记录到慢查询日志

默认值是10秒

4）如何设置查询阀值

命令设置

备注：另外开一个session或重新连接，才会看到变化

执行成功发发现慢sql的时间变成了1秒

配置文件设置

执行成功发发现慢sql的时间变成了1秒

5）如何把未使用索引的 SQL 记录写入慢查询日志

我们发现，未使用索引的sql默认是不记录到慢查询日志的

开启配置

执行如下

6）模拟数据

我们发现，每执行一次select sleep(2)，之后，再通过show global status ...命令，他的值就会+1

3.1.2 调优工具常用命令

语法格式

常用到的格式组合

1、拿到慢日志路径

日志路径为：/opt/mysql-5.7.28/data/linux-141-slow.log

查看日志

2、得到访问次数最多的10条SQL

3、按照时间排序的前10条里面含有左连接的SQL

3.1.3 慢日志文件分析

1、查看慢查询日志

属性解释

3.2 调优工具show profile

tips：
show profile，它也是调优工具
也是MySQL服务自带的分析调优工具
不过这款更高级
比较接近底层硬件参数的调优。

简介：

show profile是MySQL服务自带更高级的分析调优工具

比较接近底层硬件参数的调优

1、查看show profile设置

通过上面我们发现，show profile工具默认是关闭状态，15表示保存了近15次的运行结果。

2、开启调优工具

执行下面的命令开启

再次查看状态

3、查看最近15次的运行结果

显示最近15次的运行结果

4、诊断运行的SQL

接下来，我们一起诊断一下query id为23的慢查询

开始执行

重要提示

4 如何定位不合理的SQL

4.1 如何查看SQL执行频率

MySQL 客户端连接成功后，通过

命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的统计结果和 global 级（自数据库上次启动至今）的统计结果。

如果不写，默认使用参数是“session”。

下面的命令显示了当前 session 中所有统计参数的值：

Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

参数	含义
Com_select	执行 select 操作的次数，一次查询只累加 1。
Com_insert	执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update	执行 UPDATE 操作的次数。
Com_delete	执行 DELETE 操作的次数。
Innodb_rows_read	select 查询返回的行数。
Innodb_rows_inserted	执行 INSERT 操作插入的行数。
Innodb_rows_updated	执行 UPDATE 操作更新的行数。
Innodb_rows_deleted	执行 DELETE 操作删除的行数。
Connections	试图连接 MySQL 服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。

Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

4.2 如何定位低效率SQL

以下两种方式：

慢查询日志（重要） : 通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。

tips：
关于慢查询SQL如何获取
参看上个章节

show processlist （重要） :

慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题。

可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

4.3 使用explain分析执行计划

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
partitions	匹配的分区
type	表示表的连接类型，性能由好到差的连接类型为( system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
filtered	按表条件过滤的行百分比
extra	执行情况的说明和描述

4.3.1 环境准备

4.3.2 explain 之 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。

id 情况有三种：

1） id 相同表示加载表的顺序是从上到下。

2） id 不同id值越大，优先级越高，越先被执行。

3） id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

4.3.3 explain 之 select_type

表示 SELECT 的类型，常见的取值，如下表所示：

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在SELECT 或 WHERE 列表中包含了子查询
DERIVED	在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后，则标记为UNION ；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为： DERIVED
UNION RESULT	从UNION表获取结果的SELECT

4.3.4 explain 之 table

展示这一行的数据是关于哪一张表的

4.3.5 explain 之 type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。const会将 "主键" 或 "唯一" 索引的所有部分与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index	index 与 ALL的区别为 index 类型只是遍历了索引树，通常比ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行

结果值从最好到最坏依次是：

一般来说，我们需要保证查询至少达到 range 级别，最好达到ref 。

4.3.6 explain 之 key

4.3.7 explain 之 rows

扫描行的数量。

4.3.8 explain 之 extra

其他的额外的执行计划信息，在该列展示。

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为 “文件排序”, 效率低。
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by；效率低
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错。

5 如何合理使用索引加速

tips：
500万条建表sql参照网盘sql脚本

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

5.1 验证索引提升查询效率

在我们准备的表结构product_list 中，一共存储了 500多万记录；

1）根据ID查询

查询速度很快，接近0s ，主要的原因是因为id为主键，有索引；

2). 根据store_name进行精确查询

执行用时4分钟

查看SQL语句的执行计划：

处理方案，针对store_name字段，创建索引：

索引创建完成之后，再次进行查询：

通过explain ，查看执行计划，执行SQL时使用了刚才创建的索引

5.2 索引的使用

5.2.1 准备环境

5.2.2 避免索引失效

组合索引(name,status,address)

1) 全值匹配

对索引中所有列都指定具体值。

2) 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

匹配最左前缀法则，走索引：

违反最左前缀法则，索引失效：

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

3) 范围查询右边的列

根据前面的两个字段name ， status 查询是走索引的，但是最后一个条件address 没有用到索引。

4) 禁止列运算

5) 字符串不加单引号

造成索引失效。

由于，在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

6) 尽量使用覆盖索引

避免select *

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

如果查询列，超出索引列，也会降低性能。

7) 合理使用or条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

示例，name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的：

8) 合理使用like查询

以%开头的Like模糊查询，索引失效。

解决方案：通过覆盖索引来解决

9) 合理评估索引执行

如果MySQL评估使用索引比全表更慢，则不使用索引。

10) is NULL和 is NOT NULL

有时索引失效。

解决方案：把null值设置一个默认值

11) in和not in

in 走索引， not in 索引失效。

12) 单列索引和复合索引

尽量使用复合索引，而少使用单列索引。

创建复合索引

创建单列索引

数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。

5.3 查看索引使用情况

6 SQL优化的一些建议

6.1 优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句

这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。

使得效率比分开执行的单个insert语句快。

示例，原始方式为：

优化后的方案为：

数据有序插入

优化后

6.2 优化order by语句

6.2.1 环境准备

6.2.2 两种排序方式

1). 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序

tips
不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

2). 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，

不需要额外排序，操作效率高。

多字段排序

总结：

了解了MySQL的排序方式，优化目标就清晰了：

尽量减少额外的排序，通过索引直接返回有序数据。

where 条件和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同，并且Order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort。

6.2.3 Filesort 的优化原理

tips
了解即可

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作。对于Filesort ， MySQL 有两种排序算法：

1）两次扫描算法：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。

2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小，来判定使用哪种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

6.3 优化group by 语句

tips
延续使用6.2 优化order by语句中创建的表emp

创建索引（准备工作）：

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。

当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗，则可以执行order by null 禁止排序。如下：

优化后

从上面的例子可以看出，第一个SQL语句需要进行"filesort"，而第二个SQL由于order by null 不需要进行 "filesort"，而上文提过Filesort往往非常耗费时间。

6.4 优化嵌套查询

tips
延续使用4.3 使用explain分析执行计划中创建的表

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

示例，查找有角色的所有的用户信息 :

执行计划为 :

优化后 :

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

6.5 使用SQL提示

tips：
延续使用5.2.1 准备环境中创建的表

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

6.5.1 USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

6.5.2 IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

6.5.3 FORCE INDEX

为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint 。

Last update: 2023-8-1

自身优化

0. 目录

1 MySQL总体架构介绍

1.1 MySQL总体架构介绍

1.2 MySQL存储引擎介绍

1.2.1 如何更改数据库表引擎

1.2.2 常用引擎及其特性对比