SQL优化
慢查询
可使用使用MySQL自带的mysqldumpslow 命令可对慢查询日志进行分析，也可以使用percona公司开发的percona toolkit工具集的工具进行分析
show variables like "slow_query_log"; -- 查看慢查询日志是否开启
set global slow_query_log = on -- 开启慢查询
show variables like "slow_query_log_file"; -- 查看慢查询日志的存放位置
set global slow_query_log_file = "/var/lib/mysql_log/slowlog.log" --指定慢查询日志位置
show variables like "long_qoery_time"; -- 查看慢查询时间阈值
set global long_qoery_time=0.001 -- 时间超过0.001秒就是慢查询
show variables like "log_queries_not_using_indexes"; -- 查看慢查询是否开启非索引就是慢查询
set global log_queries_not_using_indexes=on --开启未使用索引的查询就是慢查询
安装percona工具
可从percona官网获取工具安装包
wget https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/tarball/percona-toolkit-3.2.1_x86_64.tar.gz #下载工具包
tar -xf percona-toolkit-3.2.1_x86_64.tar.gz #解压安装包
cp ./percona-toolkit-3.2.1/bin/* /usr/local/bin #将命令拷贝到/usr/local/bin目录下
rm -rf ./percona-toolkit-3.2.1* #删除安装包
yum install perl-Time-HiRes #安装依赖
yum install perl-DBI #安装依赖
yum install perl-DBD-MySQL #安装依赖
yum install perl-IO-Socket-SSL #安装依赖
yum install perl-TermReadKey #安装依赖
yum install perl-Digest-MD5 #安装依赖
实时sql监控
information_schema.processlist视图下记录了当前所有正在执行的sql，可以通过查询该视图监控sql的执行情况
select id,user,host,db,command,time,state,info from information_schema.processlist where time>0;
-- 查询当前正在执行的sql，并且执行时间超过了0秒的信息
执行计划
在sql查询语句前增加explain即可查看执行计划，一条查询语句会包含多个查询子过程
| 执行计划中字段 | 含义 |
|---|---|
id |
ID表示执行顺序，相同时由上到下，不同时由大到小 |
select_type |
查询类型，值可能为以下其中之一：simple：不包含子查询或union操作的简单查询primary：包含子查询的最外层查询，或union前的查询subquery：子查询（select子句中）dependent subquery：依赖外部结果子查询（where、having子句中）derived：子查询（from子句中）union：union操作之后的查询dependent union：union子查询中union操作之后的查询union result：union产生的结果集 |
table |
数据来源，值可能是表名或表别名，也可能是以下其中之一：<union M,N>：由ID为M和N的查询产生的结果集<derived N>：由ID为N的查询产生的结果集（from子句中）<subquery N>：由ID为N的查询产生的结果集（select子句中） |
partitions |
对于分区表来说显示分区ID，非分区表则显示null |
type |
访问类型，值可能为以下其中之一：system：要查询的表只有一行数据const：要查询的表中只有一行数据符合条件，即对主键或唯一索引进行等值查询eq_ref：要查询的表中每个索引键都有且只有一行数据匹配，即有唯一索引的等值连接查询ref：要查询的表中有多行数据匹配，即没有唯一索引字段等值连接查询ref_or_null：与ref类似，要查询的表中有多行数据匹配，但包括了值为null的列index_merge：要查询的表中多个字段时，使用了索引合并进行优化range：要查询的表中具有唯一索引字段的范围，即有唯一索引的范围查询index：全索引扫描all：全表扫描 |
possible_keys |
可能用到的索引名，根据查询语句中使用到的列进行判断 |
key |
实际使用到的索引名 |
key_len |
实际使用索引的最大长度（单位字节） |
ref |
用于索引查找的列，或常量（若是常量则值为const） |
rows |
根据统计信息预估当前查询的扫描行数，单条SQL有多个查询计划时该值相乘才是总的扫描行数 |
filtered |
根据统计信息预估命中的百分比 |
extra |
额外信息，值可能为以下的不同组合，分号分隔：distinct：优化了distinct操作，在找到第一个匹配的元组后就停止找相同值的动作not exists：使用not exists来优化查询using filesort：使用了外部索引进行排序，即非索引顺序的排序using index：使用了覆盖索引using temporary：使用了临时表来处理查询using where：使用where条件来过滤数据using join buffer：使用了连接缓存impossible where：where总是返回false，无需查询select tables optimized away：优化到甚至未遍历索引 |
索引优化
根据执行计划，尽量让每个执行计划中的查询都使用到索引，但是索引并非越多越好，因为对于插入更新时需要维护索引，降低了插入更新效率，而且若同时有很多索引都可以用于查询时，索引优化器会更具统计信息评估好多个索引，生成一个最优的执行计划，大大拖长了索引优化器的查询时间，也就降低了查询性能
索引建立规则
- 经常出现在where子句中的字段，并且该列有很好的筛选性（即不相同的值很多），该字段很少DML操作
- 多表关联查询中关联字段上（on其实也可以看成where）
- 包含order by中的字段，要求如下才能使用到索引
- 索引列的顺序和升降序方向完全一致
- 关联查询中索引全部在关联表的第一张表中
- 包含group by、distinct中的字段（分组其实也可以看作等值查询）
组合索引排序规则
要知道索引是支持前缀子查询的，所以在复合索引的顺序一个遵循以下规则
- 筛选度高的字段放在组合索引的左左侧
- 使用最频繁的字段放在组合索引的最左侧
- 尽量把字段长度小的字段放在索引的最左侧
SQL改写
- 使用外连接来代替not in（MySQL5.7及其以上版本会自动转化）
-- t1表和t2表有关联字段t_id，t_id为t1表的主键，t_id为t2表的普通非空列
-- 要求查询出t1表中有但t2表中没有的t_id的值
create index t2_tid on t2(t_id) -- 在t2表的t_id列添加索引
explain
select t1.t_id
from t1
where t_id not in(select t_id from t2);
/*
Extra: Using where; Using index
未使用Not exists;进行优化
*/
explain
select t1.t_id
from t1 left outer join t2
on(t1.t_id = t2.t_id)
where t2.t_id is null;
/*
Extra: Using where; Not exists; Using index
使用了Not exists;进行优化
*/
- 使用公共表表达式代替子查询，从而减少临时表的生成（MySQL8支持）
- 拆分复杂的大SQL为多个简单的小SQL，因为MySQL中并不支持并发查询，也就是说一个SQL只能用一个CPU核心，SQL的拆分就可以使得使用多个CPU核心了
- 使用虚拟列优化使用函数或计算的列（MySQL5.7及其以上版本支持）
alter table 表名 add 虚拟列列名 as (列计算表达式或函数); -- 该虚拟列的值为其他列的值计算得来
create index 索引名 on 表名(虚拟列列名); -- 为虚拟列添加索引
Comments NOTHING