如何设计最优的数据库表结构,如何建立最好的索引,以及如何扩展数据库的查询,这些对于高性能来说都是必不可少的。但是只有这些还不够,要获得良好的数据库性能,我们还要设计合理的数据库查询,如果查询设计的很糟糕,即使增加再多的只读从库,表结构设计的再合理,索引再合适,只要查询不能使用到这些东西,也无法实现高性能的查询。所以说查询优化,索引优化,库表结构优化需要齐头并进。
在进行库表结构设计时,我们要考虑到以后的查询要如何的使用这些表,同样,编写 SQL 语句的时候也要考虑到如何使用到目前已经存在的索引,或是如何增加新的索引才能提高查询的性能。
想要对存在性能问题的查询进行优化,需要能够找到这些查询,下面先看下如何获取有性能问题的 SQL。
1.获取有性能问题的SQL
获取有性能问题的 SQL 的三种方法:
- 通过用户反馈获取存在性能问题的 SQL;
- 通过慢查日志获取存在性能问题的 SQL;
- 实时获取存在性能问题的 SQL;
1.慢查询日志获取性能问题SQL
MySQL 慢查询日志是一种性能开销比较低的获取存在性能问题 SQL 的解决方案,其主要的性能开销在磁盘 IO 和存储日志所需要的磁盘空间。对于磁盘 IO 来说,由于写日志是顺序存储,开销基本上忽略不计,所以主要需要关注的还是磁盘空间。
MySQL 提供了以下参数用于控制慢查询日志:
-
slow_query_log:是否启动慢查询日志,默认不启动,on 启动;
-
slow_query_log_file:指定慢查询日志的存储路径及文件,默认情况下保存在 MySQL 的数据目录中;
-
long_query_time:指定记录慢查询日志 SQL 执行时间的阈值,单位秒,默认 10 秒,通常对于一个繁忙的系统来说,改为0.001秒比较合适;
-
log_queries_not_using_indexes:是否记录未使用索引的 SQL;
和二进制日志不同,慢查询日志会记录所有符合条件的 SQL,包括查询语句、数据修改语句、已经回滚的 SQL。
慢查询日志中记录的内容:
-
# Query_time: 0.000220 //执行时间,可以精确到毫秒,220毫秒
-
# Lock_time: 0.000120 //所使用锁的时间,可以精确到毫秒
-
# Rows_sent: 1 //返回的数据行数
-
# Rows_examined: 1 //扫描的数据行数
-
SET timestamp=1538323200; //执行sql的时间戳
-
SELECT c FROM test1 WHERE id =100; //sql