MySQL好好学八（性能优化和sql优化）

大纲

SQL优化
性能优化
explain介绍

SQL优化

优化sql，首先需要使用慢查询日志功能记录查询较慢的sql语句，查看sql执行计划，最后使用show profile分析sql语句性能消耗情况。

1、慢查询日志配置

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

默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

-- 查看是否开启慢查询日志
mysql> show variables like '%slow_query_log%';
-- 开启慢查询日志（对当前数据库有效，MySQL重启后则会失效。）
mysql> set global slow_query_log=1;
-- 所有数据库永久开启
修改my.cnf文件，增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。
slow_query_log =1
slow_query_log_file=/usr/local/mysql/data/localhost-slow.log 

参数说明：
slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭。 
slow_query_log_file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name_slow.log

long_query_time：慢查询阈值。当查询时间大于设定的阈值时记录日志。long_query_time的默认值为10，意思是记录运行10S以上的语句。
从MySQL 5.1开始，long_query_time开始以微秒记录SQL语句运行时间，之前仅用秒为单位记录。如果记录到表里面，只会记录整数部分，不会记录微秒部分 

log_output：日志存储方式。log_output=FILE表示将日志存入文件。log_output=TABLE表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。
默认值是FILE。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=FILE,TABLE。
日志记录到系统的专用日志表中比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志。


-- 修改慢查询阈值
mysql> set global long_query_time=4;
-- 查询全局慢查询阈值
mysql> show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec) 


--查看日志存储方式
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

-- 查看是开启记录未使用索引sql
mysql> show variables like 'log_queries_not_using_indexes';
-- 开启记录未使用索引的sql
mysql> set global log_queries_not_using_indexes=1; 

-- 查询慢查询记录的条数
mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 4     |
+---------------+-------+
1 row in set (0.00 sec)

2、分析慢查询日志的工具

1、mysqldumpslow工具

mysqldumpslow是MySQL自带的慢查询日志工具。可以使用mysqldumpslow工具搜索慢查询日志中的SQL语句。

-- 常用参数说明：
-s：是表示按照何种方式排序。
c：访问计数
l：锁定时间
r：返回记录数
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
-t：是top的意思。-t n，即为返回前面n条的数据。
-g：后边可以写一个正则匹配模式，大小写不敏感的。

-- 示例
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log 

#得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log 

#得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g "left join" /database/mysql/mysql06_slow.log 

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

2、percona-toolkit工具

percona-toolkit是一组高级命令行工具的集合，可以查看当前服务的摘要信息，磁盘检测，分析慢查询日志，查找重复索引，实现表同步等等。

-- 下载工具
wget https://www.percona.com/downloads/percona-toolkit/3.0.11/binary/tarball/percona-toolkit-3.0.11_x86_64.tar.gz
-- 安装
tar -zxvf percona-toolkit-3.0.11_x86_64.tar.gz 
cd percona-toolkit-3.0.11 
perl Makefile.PL 
make make install 

#Can't locate ExtUtils/MakeMaker.pm in @INC
yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker 

#Can't locate Time/HiRes.pm in @INC
yum install -y perl-Time-HiRes 

#Can't locate Digest/MD5.pm in @INC
yum install perl-Digest-MD5.x86_64 

-- 查看慢查询日志
pt-query-digest /var/lib/mysql/localhost-slow.log

pt-query-digest 语法

-- 语法
pt-query-digest [OPTIONS] [FILES] [DSN]

-- 参数说明
--create-review-table 
    当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。 
--create-history-table 
    当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。 
--filter 
    对输入的慢查询按指定的字符串进行匹配过滤后再进行分析 
--limit 
    限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，
    如果是50%则按总响应时间占比从大到小排序，输 出到总和达到50%位置截止。 
--host mysql服务器地址 
--user mysql用户名 
--password mysql用户密码 
--history 
    将分析结果保存到表中，分析结果比较详细，下次再使用--history时，
    如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，
    可以通过查询同一CHECKSUM来比较某类型查询的历史变化。 
--review 
    将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。
    当下次使用-- review时，如果存在相同的语句分析，就不会记录到数据表中。 
--output 
    分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon。
    一般使用report，以便于阅读。 
--since 
    从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，
    也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。 
--until 
    截止时间，配合—-since可以分析一段时间内的慢查询。

用法示例

-- 1.直接分析慢查询文件:
pt-query-digest slow.log > slow_report.log
-- 2.分析最近12小时内的查询：
pt-query-digest --since=12h slow.log > slow_report2.log
-- 3.分析指定时间范围内的查询：
pt-query-digest slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00' > slow_report3.log 
-- 4.分析指含有select语句的慢查询
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log 
-- 5.针对某个用户的慢查询
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log
-- 6.查询所有所有的全表扫描或full join的慢查询
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event-> {Full_join} || "") eq "yes")' slow.log> slow_report6.log
-- 7.把查询保存到query_review表
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review--create-review-table slow.log
-- 8.把查询保存到query_history表
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history--create-review-table slow.log_0001 pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history--create-review-table slow.log_0002
-- 9.通过tcpdump抓取mysql的tcp协议数据，然后再分析
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log
-- 10.分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log
-- 11.分析general log
pt-query-digest --type=genlog localhost.log > slow_report11.log

3、开启profile功能

Profile 功能由MySQL会话变量 profiling 控制，默认是OFF关闭状态。

-- 查看是否开启profile
select @@profiling; 
show variables like ‘%profil%’; 
-- 开启
mysql> set profiling=1; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
mysql> select @@profiling; 
+-------------+ 
| @@profiling | 
+-------------+ 
| 1           | 
+-------------+ 
1 row in set, 1 warning (0.00 sec) 

-- 示例
mysql> select count(id) from tuser; 
ERROR 1046 (3D000): No database selected 
mysql> use test; 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
Database changed 
mysql> select count(id) from tuser; 
+-----------+ 
| count(id) | 
+-----------+ 
| 10000000  | 
+-----------+ 
1 row in set (4.62 sec) 

mysql> show profiles; 
+-----------+------------+------------------------------+ 
| Query_ID  | Duration   | Query                        | 
+-----------+------------+------------------------------+ 
| 1         | 0.00016275 | select @@profiling           | 
| 2         | 0.00009200 | select count(id) from tuser  | 
| 3         | 0.00014875 | SELECT DATABASE()            | 
| 4         | 0.00066875 | show databases               | 
| 5         | 0.00021050 | show tables                  | 
| 6         | 4.61513875 | select count(id) from tuser  | 
+-----------+------------+------------------------------+ 
6 rows in set, 1 warning (0.13 sec) 

mysql> show profile for query 6; 
+----------------------+----------+ 
| Status | Duration | 
+----------------------+----------+ 
| starting | 0.000228 | 
| checking permissions | 0.000018 | 
| Opening tables | 0.000035 | 
| init | 0.000204 | 
| System lock | 0.000071 | 
| optimizing | 0.000013 | 
| statistics | 0.000067 |
| preparing | 0.000027 | 
| executing | 0.000004 | 
| Sending data | 4.614239 | 
| end | 0.000045 | 
| query end | 0.000009 | 
| closing tables | 0.000026 | 
| freeing items | 0.000019 | 
| logging slow query | 0.000124 | 
| cleaning up | 0.000011 | 
+----------------------+----------+ 
16 rows in set, 1 warning (0.00 sec) 

#查看CPU、I/O及swaps的消耗情况
mysql> show profile cpu,block io,swaps for query 6; 
+-------------+----------+----------+------------+--------------+---------------+-------+ 
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps | 
+-------------+----------+----------+------------+--------------+---------------+-------+ 
| starting | 0.000228 | 0.000361 | 0.000000 | 0 | 0 | 0 | 
| checking permissions | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | 0 | 
| Opening tables | 0.000035 | 0.000000 | 0.000000 | 0 | 0 | 0 | 
| init | 0.000204 | 0.000224 | 0.000000 | 0 | 0 | 0 | 
| System lock | 0.000071 | 0.000000 | 0.000000 | 0 | 0 | 0 | 
| optimizing | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | 0 | 
| statistics | 0.000067 | 0.000131 | 0.000000 | 0 | 0 | 0 | 
| preparing | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 
| executing | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | 0 | 
| Sending data | 4.614239 | 3.648639 | 0.543410 | 55280 | 0 | 0 | 
| end | 0.000045 | 0.000202 | 0.000000 | 0 | 0 | 0 | 
| query end | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | 0 | 
| closing tables | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 
| freeing items | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 | 
| logging slow query | 0.000124 | 0.000155 | 0.000000 | 0 | 8 | 0 |
| cleaning up | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 0 | 
+-------------+----------+----------+------------+--------------+---------------+-------+

性能优化

1、服务器层面优化

配置buffer pool大小

按存储量规划 buffer pool的容量，直接从内存中获取数据，降低磁盘io操作，buffer pool 默认大小是128M，可以通过修改innodb_buffer_pool_size 参数来调整buffer pool 的大小，理论上将其设置为内存的3/4或4/5。

mysql> show global status like 'innodb_buffer_pool_pages_%'; 
+-----------------------------------+-------+ 
| Variable_name                     | Value |
+-----------------------------------+-------+ 
| Innodb_buffer_pool_pages_data     | 8190  | 
| Innodb_buffer_pool_pages_dirty    | 0     |
| Innodb_buffer_pool_pages_flushed  | 12646 | 
| Innodb_buffer_pool_pages_free     | 0     |   
| Innodb_buffer_pool_pages_misc     | 1     | 
| Innodb_buffer_pool_pages_total    | 8191  |  

发现Innodb_buffer_pool_pages_free为0，则说明 buffer pool 已经被用光，须要增innodb_buffer_pool_size。

在配置文件my.cnf中修改 innodb_buffer_pool_size的大小
innodb_buffer_pool_size = 750M

内存预热

默认情况，仅仅有某条数据被读取一次，才会缓存在 innodb_buffer_pool。所以，数据库刚刚启动，须要进行数据预热，将磁盘上的全部数据缓存到内存中。数据预热能够防止磁盘读增加I/O提高读取速度。可以配置如下参数，在关闭实例时dump一个 ib_buffer_pool 文件来存放上次关闭时的内存数据，当再次启动实例时加载进去。

-- 启动MySQL服务时，MySQL将本地热数据加载到InnoDB缓冲池中预热
innodb_buffer_pool_load_at_startup = 1

-- 停止MySQL服务时，InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘
innodb_buffer_pool_dump_at_shutdown = 1

-- 关闭mysql服务时，转储活跃使用的innodb buffer pages的比例，默认25%；
-- 配合innodb_buffer_pool_load_at_startup和innodb_buffer_pool_dump_at_shutdown两个参数同时使用
-- 如果启用新的参数，比如40 ，每个innodb buffer pool instance中有100个pages ，每次转储每个innodb buffer实例中的40个pages#
innodb_buffer_pool_dump_pct = 40

降低磁盘写入次数

-- 1、合理设置redo log的大小
redo log较大，则落盘次数就较少（innodb_log_file_size设置成innodb_buffer_pool_size的0.25倍）
-- 2、合理设置redolog的落盘策略
innodb_flush_log_at_trx_commit和写磁盘操作密切相关。
-- 3、避免双写入缓冲
innodb_flush_method=O_DIRECT
-- 4、关闭某些日志
关闭通用查询日志、慢查询日志，只开启二进制日志。

提高磁盘读写速度

采用固态硬盘（SSD）

2、SQL设计层面优化

-- 1、 选择合适的存储引擎: InnoDB
-- 2、设计中间表，一般针对于统计分析功能，或者实时性不高的需求
-- 3、为减少关联查询，创建合理的冗余字段（考虑数据库的三范式和查询性能的取舍，创建冗余字段还需要注意数据一致性问题）
-- 4、对于字段太多的大表，考虑拆表
-- 5、对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表（比如商品表中会存储商品介绍，此时可以将商品介绍字段单独拆解到另一个表中，使用商品ID关联）
-- 6、建议每张表都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布式系统的情况下）。

3、SQL语句优化

-- 1、充分使用索引
where字段 、组合索引（最左前缀） 、 索引下推（非选择行，不加锁）、索引覆盖（不回表）、on两边排序、分组统计不要用星。
-- 2、Limit 优化
单条查询最后添加 LIMIT 1，停止全表扫描
-- 3、其他优化
count(*) ：找普通索引 ,找到最小的那棵树来遍历（包含空值）
count('字段')：走缓存（不包含空值）
count(1)： 忽略字段（包含空值）
不用 MySQL 内置的函数，因为内置函数不会建立查询缓存

explain介绍

使用EXPLAIN关键字，可以获取关于查询执行计划的详细信息，包括表的访问顺序、连接方式、索引使用情况等，这些信息能够帮助你确定查询是否有效利用了索引，以及是否存在潜在的性能瓶颈。

要使用EXPLAIN关键字，只需在查询语句前加上"EXPLAIN"关键字即可，例如：

select * from TS_CompareDetailLog where  fund_id = 2 and business_date = 20231128;

执行以上语句后，MySQL会返回一个结果集，包含了查询执行计划的详细信息。该结果集中的每一行对应查询执行计划中的一步操作，你可以通过分析这些数据来确定查询的性能瓶颈以及优化的方向。

这些信息包括以下列：

id: 查询执行计划中每个操作的唯一标识，
select_type: 操作类型，如简单查询、联合查询等，
table: 操作涉及的表名，
type: 表访问的方式，如全表扫描、索引扫描等，
possible_keys: 可能使用的索引，
key: 实际使用的索引，
key_len: 实际使用的索引长度，
ref: 与索引比较的列或常量值，
rows: 预估的结果集行数，
Extra: 额外的信息，如排序、临时表等。
通过分析这些信息，你可以更好地理解查询的执行过程，并且采取相应的优化措施，例如创建合适的索引、优化查询语句等，以提高MySQL查询的性能。

1、explain 的id字段介绍

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

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

2、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表获取结果的SELEC

4、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	将遍历全表以找到匹配的行

结果值从最好到最坏以此是：null > system > const > eq_ref > ref > range > index > ALL

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

5、explain的key字段

possible_keys : 显示可能应用在这张表的索引，一个或多个。

key ：实际使用的索引，如果为NULL，则没有使用索引。

key_len : 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

6、explain的table，rows，extra字段

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

-- rows
扫描行的数量。

-- extra
其他的额外的执行计划信息，在该列展示,有以下取值：
using filesort、 using temporary、using index

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