Mysql优化之Sql语句

如何取得查询慢的Sql语句：

1.  测试人员或终端用户反馈页面变慢，检查是不是查询数据库很慢；
2.  打开Mysql慢查询日志，分析慢查询日志，找出存在性能问题的Sql语句；
3. 查询information_schema数据库processlist表可以实时取得查询慢的Sql语句：
   查询语句：SELECT id,`user`,`host`,DB,command,`time`,state,info
   FROM information_schema.PROCESSLIST
   WHERE TIME >= 60 // 查询执行时间超过60秒的Sql语句
   LIMIT 100;

 

Sql语句执行的步骤

1. 客户端发送Sql请求给Mysql服务器；
2. 如果开启查询缓存，则检查是否可以在查询缓存中命中该Sql，命中则直接返回缓存数据；
3. Mysql服务器端进行Sql解析，预处理，再由优化器生成对应的执行计划；
4. 选择最优的执行计划，调用存储引擎API来查询数据；
5. Mysql服务器将结果返回给客户端；

 

Sql语句执行每一步的耗时

使用 profile 查看Sql语句执行的耗时（已弃用，未来版本会移除）

1. 打开profile：# set profile=1;
2. 显示全部Sql语句的执行耗时：# show profiles;
3. 显示指定Sql语句每步执行的耗时：# show profile for query {query_id};
4. 显示指定Sql语句每步执行对CPU的消耗：# show profile cpu for query {query_id};

使用 performance schema 查看Sql语句的执行耗时（需要mysql5.5及以上的版本）
mysql5.5版本开启此功能会有一定的性能开销，默认是不启用的，之后的版本性能开销少了，建议一直启用改功能

1. 启用 performance schema 功能：
   use performance_schema;
   UPDATE `setup_instruments` SET enabled='YES',TIMED='YES' WHERE NAME LIKE 'stage%';
   UPDATE `setup_consumers` SET enabled='YES' WHERE NAME LIKE 'events%';
2. 使用Sql语句查询Sql执行消耗的时间：
   use performance_schema;
   SELECT a.THREAD_ID,SQL_TEXT,c.EVENT_NAME,(c.TIMER_END-c.TIMER_START)/1000000000 AS 'DURATION(ms)'
   FROM events_statements_history_long AS a
   JOIN threads AS b ON a.`THREAD_ID`=b.`THREAD_ID`
   JOIN events_stages_history_long AS c ON c.`THREAD_ID`=b.`THREAD_ID`
   WHERE b.`PROCESSLIST_ID`=CONNECTION_ID()
   AND a.EVENT_NAME='statement/sql/select'
   ORDER BY a.THREAD_ID,c.EVENT_ID LIMIT 100 \G;

 

分析SQL

使用 explain 关键字分析Sql语句，检查检索是否会使用索引，分析出来的信息包括

1. id // 查询ID，值越大优先查询
2. select_type // 查询类型
3. table // 查询的表
4. type // 查询使用哪种类型，从好到差：system => const => eq_ref => ref => range => index => all
5. prossible_keys // 显示这张表可能使用的索引
6. key // 真实使用的索引，如果为null说明没有使用索引，可能是索引失效
7. key_len // 表示索引中使用的字节数
8. ref // 显示索引的哪一列被使用了
9. rows // 大致估算出需要读取的行数
10. extra // 不适合在其他列中显式但十分重要的额外信息，包括：
        Using filesort // 使用文件排序（九死一生）
        Using temporary // 使用临时表保存中间结果（十死无生）
        Using index // 使用了索引，很好非常好
        Using where // 表明使用了where过滤
        Using join buffer // 表明使用了连接缓存
        distinct // 优化distinct操作

SQL语句优化技巧

* 分批更新大表的多行数据；

使用程序或者存储过程都是可以的

* 谨慎更改大表的表结构；

虽然可以在线更改数据表结构，但是更改时会锁表，对业务造成影响；
1> 可以先建立一个与老表一模一样的新表，然后把老表的数据导入新表中，同时在老表建立一系列的触发器，把老表的更改同步到新表，然后在老表增加排它锁，之后重命名新表与老表，最后删除老表；
操作有些复杂，可以使用工具：pt-online-schema-change实现，例：
pt-online-schema-change \
--alter="MODIFY c VARCHAR(150) NOT NULL DEFAULT ''" \
--uroot --password=123456 D=database,t=table_name \
--charset=utf8 --execute
2> 如果有做数据库复制，可以先在从库里更改表，等数据完全同步后把从库改为主库，之前的主库改为从库，再同步，之后改回主从身份即可；

* 只查询你所需要的数据，避免使用 select * ...

* 尽量少的使用子查询或使用关联查询代替子查询

* 避免使用LIKE '%..%' 应使用LIKE '...%'

* 先查询出主键ID，再通过主键ID查询需要的数据

* Sql语句尽量的简单，能拆小就拆小，不要做复杂运算

* 不要在Where子句里使用函数和运算