面试再也不用惧怕MYSQL优化，优化详解

一位爱好技术的橘右京的哥哥橘左京

1. SQL优化

在项目的前期数据量一般较小，开发者更加注重功能实现，发生SQL性能问题可能性较小。随着时间的推移，数据量会越来越大，就可能就会出现SQL性能问题，所以必须要对有性能问题的SQL进行优化。

1.1 查询SQL执行频率

可通过命令查询数据库或存储引擎的执行频率，语句：show [ session / global ] status like ‘名称’ ；若不写查询级别默认为Session。
释义：session（当前会话） global（全局，在这指的是数据库从启动到现在）

1.查询当前数据库全局状态信息
show global status like ‘Com_______’;

2.查询当前存储引擎状态信息
show global status like ‘Innodb_rows_%’;

1.2 定位低效率执行SQL

可以通过两种方式定位低效率SQL

1） 慢查询日志：通过慢查询日志可以定位效率低的SQL语句;
2） show processlist：上述慢查询日志需要在查询结束以后才记录，出现效率问题时并不能定位问题，可以用show processlist查询mysql当前的线程及线程状态，是否锁表等，可实时查看SQL执行情况，同时对一些锁表操作进行优化。

ID：用户登录Mysql时，系统分配"connection_id",可以使用函数connection_id()查看；
User：显示当前用户，若不是"Root"用户，就只显示权限范围的SQL语句；
Host：显示这个语句是从哪个主机哪个端口发送的；
db：数据库名称；
Command：显示当前连接的执行命令，一般为sleep(休眠)、query(查询)、connect(连接)等；
Time：显示这个状态的持续时间
State：显示当前连接SQL语句的某个状态，以查询为例，需要经过copying to tmp table、sorting result、sending data等状态才可以完成；
Info：显示这条SQL语句是判断问题语句的重要依据；

1.3 explain分析执行计划

//通过以上方法定位到低效率SQL，可通过explain查询该Select语句执行信息，包含在SELECT语句执行过程中表如何连接和连接的顺序。
explain select * from res_resource;

1.3.1 explain之ID

ID字段是Select查询的序列号，有三种情况

1. ID相同表示的是执行顺序从上到下；

 explain select * from sys_user su,sys_role sr,sys_user_role sur where su.user_id = sur.user_id and sr.role_id = sur.role_id;

2. ID值越大，加载的优先级越高；

explain select * from sys_user su where su.user_id = (select sur.user_id from sys_user_role sur where sur.role_id = '1');

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

explain select * from sys_user su
 left join sys_user_role sur1 ON su.user_id = sur1.user_id
 where sur1.role_id = ( select role_id from sys_role sr where sr.role_id = '1' )

1.3.2 explain之select_type

1）SIMPLE ：执行最简单的MYSQL查询，查询中不包含子查询或UNION。

2）PRIMARY：查询中包含复杂的子查询，外层查询为该标记。

3）SUBQUERY：在SELECT 或WHERE 包含子查询

4）DERVIED：在From列表包含子查询，被标注Dervide（衍生）MYSQL会递归执行

5）UNION：若第二个SELECT语句出现在UNION之后，则标记为UNION；若UNION出现在子语句的子查询中，外层SELECT将标记为：Dervide

1.3.3 explain之select_table

select_table这一列表示的是当前执行的SQL语句是关于哪个表

1.3.4 explain之type

type字段是访问类型

1）NULL：MYSQL执行操作不访问任何一个表及索引。
2）System：数据表中只有一条记录。
3）const：根据主键或唯一索引字段查询出一条数据
4）eq_ref：类似ref，使用唯一或主键联合查询得到一条结果
5）ref：不使用唯一索引联查可能得到多条结果
6）range：where 之后出现between，>、<、in等操作
7）index：全表扫描得到的一个带有索引字段的结果
8）ALL：全表扫描得到的结果

1.3.5 explain之key

1）possible_keys：可能有的索引
2）key：实际应用的索引
3）key_len：索引使用的最大字节数，非实际长度，不损失精确度的情况下越短越好

1.3.6 explain之rows

扫描行的数量

1.3.7 explain之filterred

查询到的行数占有总扫描行数的百分比

1.3.8 explain之extra

其他的额外的执行信息

1）using filesort：表示MYSQL会使用一个外部的索引排序，而不会使用内部的，称为"文件排序"；
2）using temporay：使用临时表保存中间的结果，常见在order by 或 group by时；
3）using index：表示相应的查询操作使用了覆盖索引，避免访问表的数据行内容，效率高。

1.4 show profile分析SQL

在MySql5.0.37版本及以上增加了对show profiles和show profile的支持，能够了解到时间耗费到哪去了。
可通过select @@have_profiling；查询当前MYSQL是否支持profile；

YES表示当前环境支持profile；
默认的profiling是关闭的，可以在会话窗口级别中将他开启， 先进行查询查看是否已开启

0表示未开启，1表示已开启，使用 set profiling=1; 进行开启
在执行了多条SQL后，可以通过show profiles；查看SQL的执行时间及SQL信息；

而且可以对具体的SQL执行情况进行查看 show profile for query 2;

1.5 SQL语句的优化

1.5.1 优化insert语句

原：

insert into sys_user values (1,‘小明’)
insert into sys_user values (2,‘小刚’)

优化后：

insert into sys_user values (1,‘小明’)， (2,‘小刚’)

1.5.2 优化order by语句

order by 有两个排序方式，分别为filesort和using index

1）filesort：文件排序，通过不是对索引字段的返回字段进行排序，效率低；

2）using index：索引排序，通过有序索引扫描直接返回有序数据，不需要额外排序，效率较高；
说明：想要避免产生filesort排序，①多字段排序要选择索引列 ②多字段排序不可一个正序一个倒序③多字段排序字段顺序要和索引顺序一致

总结：尽量减少额外操作，通过索引返回索引字段的有序数据，where条件和order by使用相同的索引，order by的索引字段顺序要和索引顺序相同，order by 多字段排序要同时使用正序或倒序，否则会造成额外操作。

filesort的优化：

在进行order by优化时能尽量避免filesort，但某些情况下，因为条件限制不能解决filesort，那么只能对filesort进行优化。MySql有两种排序方式：

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

2）一次扫描法：一次性取出满足条件的所有字段，然后在排序区sort buffer排序后直接返回结果集，内存开销较大，但效率高。
Mysql通过系统变量max_length_for_sort_data的大小和Query进行比较，来决定使用哪种扫描方式，若前者更大就选择一次扫描法，否则使用第一种。
可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序效率。

1.5.3 优化group by 语句

group by会进行先排序后分组的操作，若在SQL运用了聚合函数会先执行聚合函数，group by在实现中同样可运用索引来实现。

本条SQL使用了临时表和文件排序，两者都很慢，可以使用 order by null 取消group by时的排序。

这样就取消了文件排序，同样也可对排序字段建立索引增加查询速度。

1.5.4 优化嵌套子查询

MySql在4.1之后支持子查询，使用SQL去等于另一个SQL的执行结果完成查询操作，可被更有效的(JOIN)代替。

1.5.5 优化or语句

对于or语句，若使用了索引，那么每个条件列都要使用索引，且不能使用复合索引，否则会失效。若无索引，则考虑增加索引。

使用了主键索引进行筛选查询。

使用了主键ID与另一个普通字段进行or，结果索引失效。

使用union去代替or，这样避免了索引失效问题。

可以看到user表存在的索引。

判断条件都为联合索引，但是失效了。

使用联合索引的话查询字段要使用覆盖索引。

1.5.6 优化limit语句

进行分页操作时，通过创建覆盖索引进行分页效率要高很多，若进行limit 7000000的操作会抛弃掉前面7000000条只执行后十条，效率很低。

方法一：使用子查询关联排序再进行查询，效率提高很多。

方法二：若主键是自增ID，使用某个位置的查询，但不可有断层。

1.5.7 使用SQL提示

SQL提示，是优化数据库的重要手段，可以在SQL语句加入人为的指令达到优化的目的。
可以查看到共有两个索引，主键索引和联合索引。

若使用SQL提示，有可能会使用主键和联合索引，但只是用了主键索引。

1）use index
使用在查询语句表名的后面，作用是建议Mysql使用人为推荐的索引。

2）Ignore index
忽略掉某个索引，不让MYSQL使用。

3）force index
强制使用特定的索引。

1.6 应用优化

在生产环境中，由于数据库本身的性能局限，就必须要对前台的应用进行一些优化。

1.6.1 使用连接池

对于访问数据库来说，建立连接的代价是比较昂贵的。频繁的开关链接耗费大量资源，所以要建立数据库连接池，提高访问性能。

1.6.2 减少对MySql的访问

1）若一次能获取结果，不需要分多步。
① 需要获取姓名和年龄
select name,age from sys_user;
② 之后的业务逻辑需要获取备注
select remark from sys_user;
③ 完全可以合二为一
select name,age,remark from sys_user;

2)增加cache层
缓存能提升MYSQL性能，可以使用Mybatis,Hibernate提供的一二级缓存，或者使用Redis。

1.6.3 负载均衡

可以通过负载均衡让负载量分摊到各个服务器上，降低单台MYSQL的压力，提升效率。

1）利用MYSQL赋值分流查询
通过主从复制来实现读写分离，使增删改走主节点，查询走子节点，从而降低服务器的压力。

2）采用分布式数据库架构
分布式数据架构是通过多台服务器分布数据，可以实现多台服务器的负载均衡，适合大数据量，有更好的拓展和可用性 。

1.7 查询缓存优化 (MYSQL8已废弃)

1.7.1 了解查询缓存

概述：

开启查询缓存后，当执行完全相同的SQL时，服务器直接从缓存读取结果，不会再进行查询，若数据被改变，则查询缓存失效，适用于增删改操作较少的表。

过程：

1.客户端发送一条查询给服务器；
2.服务器去查询缓存查看当前SQL是否命中缓存，若命中则返回，否则进入下一阶段；
3.服务器对SQL进行解析、预处理，再由优化器生成执行计划；
4.MYSQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
5.将结果返回给客户端；

1.7.2 查询缓存配置

1）查看当前MYSQL是否支持查询缓存
show variables like ‘hava_query_cache’;

2）查询当前MYSQL是否开启查询缓存
show variables like ‘query_cache_type’;

3）查询缓存大小
show variables like ‘query_cache_size’;

4）查询缓存状态
show status like ‘Qcache%’;

Query_free_blocks：可用内存块数
Query_free_memory：可用内存空间
Query_hits:查询缓存的命中次数
Query_inserts:添加到查询缓存的次数
Query_lowmem_prunes：若内存空间不足，将数据移除的次数
Query_not_cached：未走缓存查询的次数
Query_queries_in_cache：查询缓存中注册的查询数
Query_total_blocks：查询缓存中的总块数

1.7.3 开启查询缓存

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

1）OFF或0：查询缓存功能关闭
2）OF或1：查询缓存功能开启，SELECT的结果符合缓存条件即会缓存，否则不予缓存，显示指定SQL_NO_CACHE，不与缓存。
3）Demand 或2：查询缓存功能按需缓存，显示指定SQL_CACHE语句才会缓存，否则不予缓存。

Liunx：my.conf 配置 query_cache_type=1
Windows：my.ini配置query_cache_type=1

1.7.4 查询缓存SELECT选项

1）SQL_CACHE：若查询结果是可缓存的，并Query_cache_type系统变量值为ON或DEMAND,则缓存查询结果。
select SQL_CACHE，id，name from sys_user;

2）SQL_NO_CACHE：服务器不使用查询缓存，也不检查是否已缓存，也不检查查询结果。
select SQL_NO_CACHE，id，name from sys_user;

1.7.4 查询缓存失效的情况

1）语句不一致；
select * from sys_user;
Select * from sys_user;

2）语句有动态改变函数时，不缓存，如now()，curdate()，rand()，UUID()；
select * from sys_user where update_time > now();
select user();
select database();

3）不使用任何表查询语句；
select ‘A’；

4）查询mysql，information_schema或performance_schema数据库表时，不会走缓存查询。
select * from information_schema.engines;

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

6）更改表；

1.8 MYSQL内存优化与管理

1.8.1 内存优化原则

1）尽量将更多的内存分配给MYSQL作为缓存，但要给操作系统和其他应用预留足够的内存空间。

2）MyISam存储引擎读取数据文件依赖于操作系统和自身的IO，若有MyISAm表要预留足够的内存空间。

3）排序区、连接区是分配给每个数据库会话专用的，默认值的大小要根据最大连接数合理分配，若太大就会浪费资源，而且并发连接较高会导致物理内存耗尽。

1.8.2 MyISAm内存优化

MyISAm存储引擎使用key_buffer缓存索引块来加快MyISam的读取速度，没有特别的缓存机制，完全依靠与操作系统的IO缓存。

1）key_buffer_size：决定索引缓冲区的大小，直接影响存取速度，对于一般MyISAm数据库可将1/4分配给数据库，可在my.ini设置 key_buffer_size=512M

2）read_buffer_size：若经常对表进行读取，可将其值增大，每个read_buffer_size是session级的，如果设置默认值太大，可能会造成内存浪费。

3）read_rnd_buffer_size：对于查询MyISAm表需要做排序的，进行order by操作可以增大其值，来加快查询速度，每个read_rnd_buffer_size是session级的，如果设置默认值太大，可能会造成内存浪费。

1.8.3 InnoDB内存优化

innoDB使用一块内存区来做IO缓冲池，不仅缓存索引块，也缓存数据块。

1）innodb_buffer_pool_size：其作用是调整最大缓冲区大小，在保证操作系统和其他软件正常运行后，设置的越大磁盘IO就越少，性能也就越高。
innodb_buffer_pool_size=512M

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

1.9 MYSQL并发参数调整

MySql服务是多线程结构，包括后台线程和客户服务线程，多线程可以有效利用服务器资源，提升数据库的性能。在MySql中控制并发连接和线程的主要参数包括max_connetctions, back_log,table_open_cache, thread_cache_size ,innodb_lock_wait_timeout 。

1） max_connetctions：MYSQL的最大连接数，默认值151，如果连接超过151，则会进入back_log进行等待前面的连接释放，一般的liunx能够支持500-1000问题不大。

2）back_log：其参数控制MYSQL监听TCP端口时的积压请求栈大小，如果数量超过了max_connections的设置就会进入等待，若超过了back_log设置的数量大小，就会直接报错，5.6.6之前默认是20个，之后的版本是50+（max_connetcions / 5），最大不超过九百。若短时间内需要大量连接，可以考虑调整其大小。

3）table_open_cache：该参数设置SQL语句执行可打开表缓存的数量，一条SQL至少操作一张表，最少使用一个表缓存，多个表需要多个。该参数的值由最大连接数和每个连接执行关联查询中表的最大个数来决定的。
最大连接数 * N；

4）thread_cache_size：为了加快连接数据库的速度，MYSQL会缓存一定数目的服务线程做备用，可通过其参数设置最大线程数大小，类似于JAVA的线程池。

5）innodb_lock_wait_timeout：该参数用来设置Innodb等待行锁的时间，默认50S，若业务是需要快速反馈的，则需要将值调小，避免长时间等待；若业务是不需要快速反馈，则可以将值调大，以免发生回滚降低性能。