MySQL的性能调优已经随着逐年内卷，越来越多的进入到高级程序员的面试中；而调优这件事儿根据不同的场景也有不同的技巧，和方法策略。下面我们通过，性能监控，数据类型优化，执行计划，索引优化，SQL优化等5个方面展开。
性能监控
在进行调优之前，我们应当对目前生产环境MySQL性能有一定的了解，可以通过以下3种方式。
show profile
-- 默认关闭，如下配置开启
set profiling=1;
-- 查看最近的数条
show profiles;
-- 查看最近的1条
show profile;
performance schema
MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。
show processlist
show processlist
结果截图：
数据类型优化
在表设计之初，我们应当选择合适数据类型；不仅可以节省表空间，可以提高索引的效率。
1.整数类型
MySQL的整数类型有：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。尽量使用满足需求的最小数据类型。
2.字符或字符串类型
varchar根据实际内容长度保存数据，char固定长度的字符串。
3.BLOB和TEXT类型
MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。
两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。
4.datetime和timestamp
datetime占8个字节，与时区无关，数据库底层时区配置，对datetime无效；可保存到毫秒。
timestamp占4个字节，时间范围：1970-01-01到2038-01-19，精确到秒，采用整形存储，时间范围：1970-01-01到2038-01-19；依赖数据库底层设置时区。
date占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节，使用date类型还可以利用日期时间函数进行日期之间的计算，date类型用于保存1000-01-01到9999-12-31之间的日期。
5.主键的选择
代理主键：与业务无关的，无意义的数字序列
自然主键：事物属性中的自然唯一标识
推荐使用代理主键，它们不与业务耦合，因此更容易维护。
6.范式与反范式的合理选择
执行计划
可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。
调优人员需要重点关注：type，key，rows，extra等输出的信息。
执行计划包含的信息
| Column | Meaning |
|---|---|
| id | The SELECT identifier |
| select_type | The SELECT type |
| table | The table for the output row |
| partitions | The matching partitions |
| type | The join type |
| possible_keys | The possible indexes to choose |
| key | The index actually chosen |
| key_len | The length of the chosen key |
| ref | The columns compared to the index |
| rows | Estimate of rows to be examined |
| filtered | Percentage of rows filtered by table condition |
| extra | Additional information |
id
select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序
id号分为三种情况：
1、如果id相同，那么执行顺序从上到下
explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;
2、如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');
3、id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行
explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');
select_type
主要用来分辨查询的类型，是普通查询还是联合查询还是子查询
select_type Value |
Meaning |
|---|---|
| SIMPLE | Simple SELECT (not using UNION or subqueries) |
| PRIMARY | Outermost SELECT |
| UNION | Second or later SELECT statement in a UNION |
| DEPENDENT UNION | Second or later SELECT statement in a UNION, dependent on outer query |
| UNION RESULT | Result of a UNION. |
| SUBQUERY | First SELECT in subquery |
| DEPENDENT SUBQUERY | First SELECT in subquery, dependent on outer query |
| DERIVED | Derived table |
| UNCACHEABLE SUBQUERY | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
| UNCACHEABLE UNION | The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) |
--sample:简单的查询，不包含子查询和union
explain select * from emp;
--primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;
--union:若第二个select出现在union之后，则被标记为union
explain select * from emp where deptno = 10 union select * from emp where sal >2000;
--dependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
explain select * from emp e where e.empno in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)
--union result:从union表获取结果的select
explain select * from emp where deptno = 10 union select * from emp where sal >2000;
--subquery:在select或者where列表中包含子查询
explain select * from emp where sal > (select avg(sal) from emp) ;
--dependent subquery:subquery的子查询要受到外部表查询的影响
explain select * from emp e where e.deptno in (select distinct deptno from dept);
--DERIVED: from子句中出现的子查询，也叫做派生类，
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;
--UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存
explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
--uncacheable union:表示union的查询结果不能被缓存：sql语句未验证
table
对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集
1、如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
2、表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
3、当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id
type
type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情况下，得保证查询至少达到range级别，最好能达到ref
--all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
explain select * from emp;
--index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
explain select empno from emp;
--range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()
explain select * from emp where empno between 7000 and 7500;
--index_subquery：利用索引来关联子查询，不再扫描全表
explain select * from emp where emp.job in (select job from t_job);
--unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引
explain select * from emp e where e.deptno in (select distinct deptno from dept);
--index_merge：在查询过程中需要多个索引组合使用，没有模拟出来
--ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式
explain select * from emp e where e.mgr is null or e.mgr=7369;
--ref：使用了非唯一性索引进行数据的查找
create index idx_3 on emp(deptno);
explain select * from emp e,dept d where e.deptno =d.deptno;
--eq_ref ：使用唯一性索引进行数据查找
explain select * from emp,emp2 where emp.empno = emp2.empno;
--const：这个表至多有一个匹配行，
explain select * from emp where empno = 7369;
--system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现
possible_keys
显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
key
实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
key_len
表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
rows
根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好
explain select * from emp;
extra
包含额外的信息。
--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
explain select * from emp order by sal;
--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;
--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;
--using where:使用where进行条件过滤
explain select * from t_user where id = 1;
--using join buffer:使用连接缓存，情况没有模拟出来
--impossible where：where语句的结果总是false
explain select * from emp where empno = 7469;
索引优化
索引的分类
索引通常分为：主键索引，唯一索引，普通索引，组合索引，全文索引
索引的数据结构
通常索引采用的数据结构有：哈希表，B+树
具体的跟存储引擎相关，例如：Memory存储引擎所采用的是哈希表，MyISAM 和 InnoDB采用的是B+树。
索引的匹配方式
案例为：name，age，pos的组合索引
- 全值匹配
全值匹配指的是和索引中的所有列进行匹配
explain select * from staffs where name = 'July' and age = '23' and pos = 'dev';
- 匹配最左前缀
只匹配前面的几列
explain select * from staffs where name = 'July' and age = '23';
- 匹配列前缀
可以匹配某一列的值的开头部分
explain select * from staffs where name like 'J%';
- 匹配范围值
可以查找某一个范围的数据
explain select * from staffs where name > 'Mary';
- 精确匹配某一列并范围匹配另外一列
可以查询第一列的全部和第二列的部分
explain select * from staffs where name = 'July' and age > 25;
- 只访问索引的查询
查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引
explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';
组合索引
当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要。
案例，建立组合索引a,b,c
优化小细节
1、当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层。
select actor_id from actor where actor_id=4;
select actor_id from actor where actor_id+1=5;
2、尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询
3、可以使用前缀索引
4、尽量使用索引来排序，因为索引本身就是有序的。
5、union all,in,or都能够使用索引，但是推荐使用in；明确数据无交集时，union ，union all都可以使用，通常选择union all，因为union默认会有distinct操作。
explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id =2;
6、范围列可以用到索引
范围条件是：<、>等
范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列
7、强制类型转换会全表扫描
--不会触发索引
explain select * from user where phone=13800001234;
--触发索引
explain select * from user where phone='13800001234';
8、更新十分频繁，数据区分度不高的字段上不宜建立索引
-
更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能；
-
类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据，
-
一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算
9、创建索引的列，尽量不允许为null，可能会得到不符合预期的结果
10、当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致
11、明确数据返回量的情况下，能使用limit的时候尽量使用limit
12、单表索引建议控制在5个以内
13、单索引字段数不允许超过5个（组合索引）
索引监控
show status like 'Handler_read%';
Handler_read_first：读取索引第一个条目的次数
Handler_read_key：通过index获取数据的次数
Handler_read_last：读取索引最后一个条目的次数
Handler_read_next：通过索引读取下一条数据的次数
Handler_read_prev：通过索引读取上一条数据的次数
Handler_read_rnd：从固定位置读取数据的次数
Handler_read_rnd_next：从数据节点读取下一条数据的次数
常用服务器参数配置
general
| 参数名 | 含义 |
|---|---|
| datadir=/var/lib/mysql | 数据文件存放的目录 |
| socket=/var/lib/mysql/mysql.sock | mysql.socket表示server和client在同一台服务器，并且使用localhost进行连接，就会使用socket进行连接 |
| pid_file=/var/lib/mysql/mysql.pid | 存储mysql的pid |
| port=3306 | mysql服务的端口号 |
| default_storage_engine=InnoDB | mysql存储引擎 |
| skip-grant-tables | 当忘记mysql的用户名密码的时候，可以在mysql配置文件中配置该参数，跳过权限表验证，不需要密码即可登录mysql |
character
| 参数名 | 含义 |
|---|---|
| character_set_client | 客户端数据的字符集 |
| character_set_connection | mysql处理客户端发来的信息时，会把这些数据转换成连接的字符集格式 |
| character_set_results | mysql发送给客户端的结果集所用的字符集 |
| character_set_database | 数据库默认的字符集 |
| character_set_server | mysql server的默认字符集 |
connection
| 参数名 | 含义 |
|---|---|
| max_connections | mysql的最大连接数，如果数据库的并发连接请求比较大，应该调高该值 |
| max_user_connections | 限制每个用户的连接个数 |
| back_log | mysql能够暂存的连接数量，当mysql的线程在一个很短时间内得到非常多的连接请求时，就会起作用，如果mysql的连接数量达到max_connections时，新的请求会被存储在堆栈中，以等待某一个连接释放资源，如果等待连接的数量超过back_log,则不再接受连接资源 |
| wait_timeout | mysql在关闭一个非交互的连接之前需要等待的时长 |
| interactive_timeout | 关闭一个交互连接之前需要等待的秒数 |
log
| 参数名 | 含义 |
|---|---|
| log_error | 指定错误日志文件名称，用于记录当mysqld启动和停止时，以及服务器在运行中发生任何严重错误时的相关信息 |
| log_bin | 指定二进制日志文件名称，用于记录对数据造成更改的所有查询语句 |
| binlog_do_db | 指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将忽略，不记录在日志中 |
| binlog_ignore_db | 指定不将更新记录到二进制日志的数据库 |
| sync_binlog | 指定多少次写日志后同步磁盘 |
| general_log | 是否开启查询日志记录 |
| general_log_file | 指定查询日志文件名，用于记录所有的查询语句 |
| slow_query_log | 是否开启慢查询日志记录 |
| slow_query_log_file | 指定慢查询日志文件名称，用于记录耗时比较长的查询语句 |
| long_query_time | 设置慢查询的时间，超过这个时间的查询语句才会记录日志 |
cache
| 参数名 | 含义 |
|---|---|
| key_buffer_size | 索引缓存区的大小（只对myisam表起作用） |
| query_cache_size | 查询缓存的大小，未来版本被删除 |
| query_cache_limit | 超出此大小的查询将不被缓存 |
| query_cache_min_res_unit | 缓存块最小大小 |
| query_cache_type | 缓存类型，决定缓存什么样的查询 |
| sort_buffer_size | 每个需要排序的线程分派该大小的缓冲区 |
| max_allowed_packet=32M | 限制server接受的数据包大小 |
| join_buffer_size=2M | 表示关联缓存的大小 |
InnoDB
| 参数名 | 含义 |
|---|---|
| innodb_buffer_pool_size | 该参数指定大小的内存来缓冲数据和索引，最大可以设置为物理内存的80% |
| innodb_flush_log_at_trx_commit | 主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为0，1，2 |
| innodb_thread_concurrency | 设置innodb线程的并发数，默认为0表示不受限制，如果要设置建议跟服务器的cpu核心数一致或者是cpu核心数的两倍 |
| innodb_log_buffer_size | 此参数确定日志文件所用的内存大小，以M为单位 |
| innodb_log_file_size | 此参数确定数据日志文件的大小，以M为单位 |
| innodb_log_files_in_group | 以循环方式将日志文件写到多个文件中 |
| read_buffer_size | mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区 |
| read_rnd_buffer_size | mysql随机读的缓冲区大小 |
| innodb_file_per_table | 此参数确定为每张表分配一个新的文件 |
- 本文链接： https://www.sunce.wang/archives/mysql系列四性能调优
- 版权声明： 本博客所有文章除特别声明外，均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处！