- 通过
show status
命令了解SQL的执行效率
具体查看什么信息要对应到具体参数,而且show [session|global] status
默认是session下的。 - 定位执行效率低的SQL语句
通过慢查询日志定位这些信息,慢查询日志记录了所有执行时间超过参数long_query_time
且扫描记录不少于min_examined_row_limit
的所有记录,查看该日志命令more localhost-slow.log
,用set
命令设置long_query_time=2
单位秒,show variables like 'long%'
查看设定值 - 通过explain分析低效SQL的执行计划
通过上述,我们得知哪些语句效率低后,可以用explain或者desc命令获取如何执行该语句的信息,explain (后接该语句)
select - 通过show profile 分析SQL
有时候仅通过explain不能定位问题,这里可以用profile联合分析,首先select @@having_profile
看该数据库是否支持profile,通过set profiling=1
启动profile,show profiles
当前SQL的Query ID,然后通过show profile for query 4(Query ID)
查看每个过程中线程的每个状态和消耗的时间。这样可以帮助我们明确时间的主要消耗在哪。 - 通过trace分析优化器如何选择执行计划
这个帮助我们更好的理解优化的行为,为什么选择该优化计划。set OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on
打开trace,设置格式位json;set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000
设置最大的trace空间,select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE
获取trace得到的跟踪文件,下面就是分析了。 - 确定问题并采取相应优化策略
至此分析完了,根据具体情况采取相应措施了。 - 索引
7.1 索引分类
索引有BTree、Hash、R-Tree、Full-text索引,其中BTree(平衡二叉树)索引用的范围比较广,Hash索引用于Memory/Heap引擎,R比较小众,全文索引InnoDB5.6版本后也开始支持。
7.2 索引使用
(1)匹配全值:对查询指定具体值,索引中所有列都有等值匹配。
(2)匹配值的范围查询:查找满足一定条件范围的索引匹配记录。
(3)匹配最左前缀:仅仅使用索引中最左边的列进行查找。
(4)仅仅对索引进行查询,当查询的列在索引字段中时,无需通过索引回表,Extra
部分变成Using index
,Extra
部分变成Using where
表示需要通过索引回表。
(5)匹配列前缀,仅仅使用索引中的第一列,并且只包含索引其中开头的一部分进行查询(加like
模糊)。
(6)部分精确部分范围。
7.3 存在索引但不能使用
(1)以%开头的like查询不能使用BTree索引,因为BTree的结构问题。
(2)后续待补充 - 两个简单实用的优化方法
8.1定期分析表和检查表
(1)analyze table tablename
对表进行分析
(2)analyze table tablename
检查表是否有错误
8.2定期优化表
optimize table tablename
对表增删产生的碎片进行整理,另外alter table tablename engine=innodb
在不修改引擎的情况下也可以对表进行优化。
注意:analyze、analyze、optimize、alter table执行期间会对表进行锁定
数据库调优
最新推荐文章于 2024-05-14 17:43:32 发布