SQL 应用程序中一个常见的问题就是性能退化
处理慢查询的三个技巧:
调优查询本身， 调优表(包括增加索引)， 调优服务器
查看用户正在运行的线程 show processlist
+---------+-------+------------------+-------------------+---------+------+-------+------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+---------+-------+------------------+-------------------+---------+------+-------+------+
| 8434174 | xxx | 10.0.0.207:34819 | sssssssss | Sleep | 1365 | | NULL |
| 8383010 | aaa | 10.0.0.212:44942 | ooooooooooooooooo | Sleep | 701 | | NULL |
+---------+-------+------------------+-------------------+---------+------+-------+------+
show processlist 显示的信息都是来自MySQL系统库 information_schema 中的 processlist 表
select * from information_schema.processlist
字段对应值:
Id: 就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。前面我们说了show processlist 显示的信息时来自information_schema.processlist 表，所以这个Id就是这个表的主键。
User: 就是指启动这个线程的用户。
Host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。
DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。
Command: 是指此刻该线程正在执行的命令。这个很复杂，下面单独解释
Time: 表示该线程处于当前状态的时间。
State: 线程的状态，和 Command 对应，下面单独解释。
Info: 一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。
Command 的值：
Binlog Dump: 主节点正在将二进制日志 ，同步到从节点
Change User: 正在执行一个 change-user 的操作
Close Stmt: 正在关闭一个Prepared Statement 对象
Connect: 一个从节点连上了主节点
Connect Out: 一个从节点正在连主节点
Create DB: 正在执行一个create-database 的操作
Daemon: 服务器内部线程，而不是来自客户端的链接
Debug: 线程正在生成调试信息
Delayed Insert: 该线程是一个延迟插入的处理程序
Drop DB: 正在执行一个 drop-database 的操作
Execute: 正在执行一个 Prepared Statement
Fetch: 正在从Prepared Statement 中获取执行结果
Field List: 正在获取表的列信息
Init DB: 该线程正在选取一个默认的数据库
Kill : 正在执行 kill 语句，杀死指定线程
Long Data: 正在从Prepared Statement 中检索 long data
Ping: 正在处理 server-ping 的请求
Prepare: 该线程正在准备一个 Prepared Statement
ProcessList: 该线程正在生成服务器线程相关信息
Query: 该线程正在执行一个语句
Quit: 该线程正在退出
Refresh：该线程正在刷表，日志或缓存；或者在重置状态变量，或者在复制服务器信息
Register Slave： 正在注册从节点
Reset Stmt: 正在重置 prepared statement
Set Option: 正在设置或重置客户端的 statement-execution 选项
Shutdown: 正在关闭服务器
Sleep: 正在等待客户端向它发送执行语句
Statistics: 该线程正在生成 server-status 信息
Table Dump: 正在发送表的内容到从服务器
Time: Unused
常用的分析sql :
# 1 按客户端 IP 分组，看哪个客户端的链接数最多
select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from processlist ) as connect_info group by client_ip order by client_num desc;
# 2 查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程
select * from information_schema.processlist where Command != 'Sleep' order by Time desc;
# 3找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀
select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;
通过EXPLAIN的信息调优查询
explain 命令 其主要功能是用来分析 select 语句的运行效果
例如 select语句使用的索引情况、排序的情况等等。
explain 的extended 扩展 能够在原本explain的基础上额外的提供一些查询优化的信息，这些信息可以通过MySQL的show warnings命令得到
explain extended select * from account\G;
show warnings\G; # 查看优化信息建议
-
Type展示了连接(join)的执行方式 -
Rows展示了在查询执行过程中检查的行数的估计(如果查询要扫描整个表, rows的数值于表行数相同)
多表连接需要检查的行数是每个表 检查行数的笛卡尔积
一个表20行， 另一个表是30行， 连接一共执行600次检查
``
id select_type TABLE TYPE possible_keys KEY key_len ref ROWS filtered Extra
1 SIMPLE gg_customer ALL \N \N \N \N 78 100.00 \N
表调优和索引
在 WHERE， JOIN， GROUP BY， ORDER BY语句中加入索引可以加速查询
索引的设计原则
-
创建索引的列并不一定是select操作中要查询的列，
最适合做索引的列是出现在where子句中经常用作筛选条件或连表子句中作为表连接条件的列。 -
具有唯一性的列，索引效果好；重复值较多的列，索引效果差。 -
如果为字符串类型创建索引，最好指定一个前缀长度，创建短索引。短索引可以减少磁盘I/O而且在做比较时性能也更好，更重要的是MySQL底层的高速索引缓存能够缓存更多的键值。
-
创建一个包含
N列的复合索引（多列索引）时，相当于是创建了N个索引，此时应该利用最左前缀进行匹配。 -
不要过度使用索引。索引并不是越多越好，索引需要占用额外的存储空间而且会影响写操作的性能（插入、删除、更新数据时索引也需要更新）。MySQL在生成执行计划时，要考虑各个索引的使用，这个也是需要耗费时间的。 -
要注意可能使
索引失效的场景，例如：模糊查询使用了前置通配符、使用负向条件进行查询等 -
索引也有减慢查询的时候， 这时候应该
忽略 IGNORE INDEX或者强制指定索引 FORCE INDEX
修改数据的调优 (UPDATE, DELETE)
在优化某个动作的时候， 要注意整个应用程序的性能
查看当前会话的状态
SHOW STATUS LIKE 'HANDLER_%'
Handler_commit 9
Handler_delete 0
Handler_discover 0
Handler_external_lock 18
Handler_mrr_init 0
Handler_prepare 0
Handler_read_first 6
Handler_read_key 6
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 3809 表示从datafile读取下一个值的频繁程度， 过高代表全表扫描
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 707
FLUSH STATUS 可以重置
Handler_read_rnd_next 表示从datafile读取下一个值的频繁程度， 过高代表全表扫描
Handler_read_key 读取索引的请求数目， 过低表明没有使用索引
Handler_commit 事务提交次数
Handler_read_first 读取索引中第一项的次数, 如果是1 ， 表示我们请求服务器读取索引的第一条记录，即全表扫描
表中的索引会影响 INSERT 操作的效率
插入操作会阻塞行甚至是整张表
表数据分区 partiation
MySQL支持做数据分区，通过分区可以存储更多的数据、优化查询，获得更大的吞吐量并快速删除过期的数据
RANGE分区：基于连续区间范围，把数据分配到不同的分区。
CREATE TABLE tb_emp (
eno INT NOT NULL,
ename VARCHAR(20) NOT NULL,
job VARCHAR(10) NOT NULL,
hiredate DATE NOT NULL,
dno INT NOT NULL
)
PARTITION BY RANGE( YEAR(hiredate) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
-
LIST分区：基于枚举值的范围，把数据分配到不同的分区。 -
HASH分区 / KEY分区：基于分区个数，把数据分配到不同的分区。
CREATE TABLE tb_emp (
eno INT NOT NULL,
ename VARCHAR(20) NOT NULL,
job VARCHAR(10) NOT NULL,
hiredate DATE NOT NULL,
dno INT NOT NULL
)
PARTITION BY HASH(dno)
PARTITIONS 4;
查询数据表大小以及索引大小
select table_name ,
ROUND(data_length/1024/1024,2) as "数据容量(MB)",
ROUND(index_length/1024/1024,2) as "索引容量(MB)"
FROM information_schema.TABLES
WHERE table_schema = 'bv_crm' AND table_name = 'kpi_account_daily_statistic'
table_name |数据容量(MB)|索引容量
---------------------------+--------+--------+
kpi_account_daily_statistic| 2469.00| 495.00|
建立索引的原则参考
1.最左前缀匹配原则
非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4
如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
-
=和in可以乱序(索引可以任意顺序)比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
-
尽量选择区分度高的列作为索引区分度的公式是count(distinct col)/count(*)表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0
一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
4.索引列不能参与计算，保持列“干净”
比如 from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。
所以语句应该写成 create_time = unix_timestamp(’2014-05-29’)
5.尽量的扩展索引，不要新建索引
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
慢查询优化基本步骤
0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表
这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析
优化示例
(1)
select
distinct cert.emp_id
from
cm_log cl
inner join
(
select
emp.id as emp_id,
emp_cert.id as cert_id
from
employee emp
left join
emp_certificate emp_cert
on emp.id = emp_cert.emp_id
where
emp.is_deleted=0
) cert
on (
cl.ref_table='Employee'
and cl.ref_oid= cert.emp_id
)
or (
cl.ref_table='EmpCertificate'
and cl.ref_oid= cert.cert_id
)
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00';
0.先运行一下
53条记录 1.87秒，又没有用聚合语句，比较慢
53 rows in set (1.87 sec)
1.explain
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where; Using temporary |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 63727 | Using where; Using join buffer |
| 2 | DERIVED | emp | ALL | NULL | NULL | NULL | NULL | 13317 | Using where |
| 2 | DERIVED | emp_cert | ref | emp_certificate_empid | emp_certificate_empid | 4 | meituanorg.emp.id | 1 | Using index |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
简述一下执行计划
首先mysql根据 idx_last_upd_date 索引扫描cm_log表获得 379条记录；
然后查表扫描了 63727条记录，分为两部分
derived表示构造表，也就是不存在的表，可以简单理解成是一个语句形成的结果集，后面的数字表示语句的ID。
derived2表示的是 ID = 2 的查询构造了虚拟表，并且返回了63727条记录
我们再来看看ID = 2的语句究竟做了写什么返回了这么大量的数据
首先全表扫描 employee表 13317条记录，
然后根据索引emp_certificate_empid关联emp_certificate表，rows = 1表示，每个关联都只锁定了一条记录，效率比较高。
获得后，再和cm_log的 379条记录根据规则关联。
从执行过程上可以看出返回了太多的数据，返回的数据绝大部分cm_log都用不到，因为cm_log只锁定了379条记录。
如何优化
可以看到我们在运行完后还是要和cm_log做join
之前和cm_log做join 拆成两部分，并用union连接起来(注意这里用union，而不用union all)
是因为原语句有“distinct”来得到唯一的记录，而union恰好具备了这种功能。
如果原语句中没有distinct不需要去重，我们就可以直接使用union all了，因为使用union需要去重的动作，会影响SQL性能。
优化过的语句如下：
select
emp.id
from
cm_log cl
inner join
employee emp
on cl.ref_table = 'Employee'
and cl.ref_oid = emp.id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00'
and emp.is_deleted = 0
union
select
emp.id
from
cm_log cl
inner join
emp_certificate ec
on cl.ref_table = 'EmpCertificate'
and cl.ref_oid = ec.id
inner join
employee emp
on emp.id = ec.emp_id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00'
and emp.is_deleted = 0
4.不需要了解业务场景，只需要改造的语句和改造之前的语句保持结果一致
5.现有索引可以满足，不需要建索引
6.用改造后的语句实验一下，只需要10ms 降低了近200倍！
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |
| 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | Using where |
| 2 | UNION | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |
| 2 | UNION | ec | eq_ref | PRIMARY,emp_certificate_empid | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | |
| 2 | UNION | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.ec.emp_id | 1 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
53 rows in set (0.01 sec)
(2)
select
*
from
stage_poi sp
where
sp.accurate_result=1
and (
sp.sync_status=0
or sp.sync_status=2
or sp.sync_status=4
);
0.先看看运行多长时间
951 rows in set (6.22 sec)
1.先explain，rows达到了361万，type = ALL表明是全表扫描。
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | sp | ALL | NULL | NULL | NULL | NULL | 3613155 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
2.所有字段都应用查询返回记录数，因为是单表查询 0已经做过了951条。
3.让explain的rows 尽量逼近951。
看一下accurate_result = 1的记录数：
select count(*),accurate_result from stage_poi group by accurate_result;
+----------+-----------------+
| count(*) | accurate_result |
+----------+-----------------+
| 1023 | -1 |
| 2114655 | 0 |
| 972815 | 1 |
+----------+-----------------+
我们看到accurate_result这个字段的区分度非常低，整个表只有-1,0,1三个值，加上索引也无法锁定特别少量的数据。
再看一下sync_status字段的情况：
select count(*),sync_status from stage_poi group by sync_status;
+----------+-------------+
| count(*) | sync_status |
+----------+-------------+
| 3080 | 0 |
| 3085413 | 3 |
+----------+-------------+
同样的区分度也很低，根据理论，也不适合建立索引
两个列的区分度都很低，即便加上索引也只能适应这种情况，很难做普遍性的优化，比如当sync_status 0、3分布的很平均，那么锁定记录也是百万级别的。
4.找业务方去沟通，看看使用场景。
业务方是这么来使用这个SQL语句的，每隔五分钟会扫描符合条件的数据，处理完成后把sync_status这个字段变成1,五分钟符合条件的记录数并不会太多，1000个左右。 了解了业务方的使用场景后，优化这个SQL就变得简单了，因为业务方保证了数据的不平衡，如果加上索引可以过滤掉绝大部分不需要的数据。
5.根据建立索引规则，使用如下语句建立索引
alter table stage_poi add index idx_acc_status(accurate_result,sync_status);
6.观察预期结果,发现只需要200ms，快了30多倍。
