MySQL的优化

优化角度

• 设计上
• 查询上
• 索引上
• 存储上

一、查看SQL执行频率

show session status like 'Com_______';
show global status like 'Com_______';
show status like 'Innodb_rows_%';

二、定位低效率执行SQL

慢查询日志

记录查询时间偏长的SQL

select sleep(10) #执行10秒的查询

show processlist

查看连接线程的执行状况

三、分析执行计划，使用explain关键字

id字段：

id相同，顺序执行；id不同，从大到小执行；

select_type字段：

type字段：

四、查看SQL执行的耗时

select @@having_profiling;
set profiling=1;

show profiles;

show profile for query #{id};

show profile cpu for query #{id};

五、查看trace优化器

通过trace的json文件，查看MySQL内部优化的过程。

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

- 需在终端执行
select * from information_schema.optimizer_trace \G;

六、索引优化

创建组合索引，避免索引失效

全值匹配
最左前缀原则

其他匹配原则

范围查询右边的列，不能使用索引
索引列不进行运算
字符串加单引号
用覆盖索引，不用select *
条件中有or，索引失效
以%开头的模糊查询，索引失效
MySQL评估索引比全表更慢，索引失效
is NULL，is NOT NULL，有时索引失效
in索引有效，not in索引失效
单列索引和复合索引，使用复合索引

七、SQL优化

大批量数据加载
主键有序
关闭唯一性校验

insert优化

order by优化
using index优于using filesort；
提高sort_buffer_size和max_length_for_sort_data系统变量，增大排序区大小，使用一次扫描算法；

子查询优化
多表查询 优于 子查询

limit查询优化

八、JDBC操作

概念图

执行流程

防SQL注入

PreparedStatement代替Statement

123456' or '1=1

九、pymysql操作

常用存储过程

DELIMITER $$
CREATE PROCEDURE insertData()
BEGIN
SET @i=0;
WHILE @i<5000000 DO
INSERT INTO tb_order(price,user_id,status) VALUES(@i,1,"SUCCESS");
SET @i=@i+1;
END WHILE;
END $$
DELIMITER;

call insertData();