分析工具
1通过慢查日志,在mysql中开启man查日志,通过设置一个阈值,所有执行时间大于这个阈值的查询都会被记录在日志中。
通过一下命令打开慢查日志:
show variables like ‘%log%’;
将这个变量设置为on,记录那些没有使用索引的sql;
show variables like 'long_qury_time';这里的设置的阈值是10毫秒。
set global slow_query_log=on;
打开慢查日志。
、
2 通过执行计划
在sql语句前加上关键字explain
执行计划模拟了sql的执行过程,具体每个关键字的含义如下:
id | SELECT识别符。这是SELECT的查询序列号 |
select_type | SELECT类型,可以为以下任何一种:
|
table | 输出的行所引用的表 |
type | 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
|
possible_keys | 指出MySQL能使用哪个索引在该表中找到行 |
key | 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 |
key_len | 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。 |
ref | 显示使用哪个列或常数与key一起从表中选择行。 |
rows | 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 |
filtered | 显示了通过条件过滤出的行数的百分比估计值。 |
Extra | 该列包含MySQL解决查询的详细信息
|
mysql优化
1子查询转化为join
嵌套的子查询是比较低效的,因为每一条记录都要进行匹配,如果记录长度比较大的话,那么我们的查询就有可能非常的低耗,我们应该尽量避免使用子查询,而是使用表连接。
SELECT t1.id FROM t1 WHERE t1.id in(SELECT t2.id from t2)
改写为:
SELECT t1.id FROM t1 join t2 on t2.id=t1.id
2limit限制返回的行数
当需要下一页数据时:
由执行计划看,虽然我们要返回100条数据,可是却遍历了201行数据,所以在limit时要尽量加上条件,使得遍历的行数减少
这次遍历的行数就是100了,如果表里没有自增长的字段,可以自己建一个,根据这个字段来限制范围。
3尽量减少使用select *,减少返回无用的字段
4join语句的优化规则
1.内循环的次数:现在考虑这么一个场景,当t1有100条记录,t2有10000条记录。那么,t1驱动t2与t2驱动t1,他们之间在效率上孰优孰劣?如果是单纯的分析指令执行次数,他们都是100*10000,但是考虑到加载表的次数呢。首先分析t1驱动t2,t1表加载1次,t2表需要加载100次。然后分析t2驱动t1,t2表首先加载1次,但是t1表要加载10000次。所以,t1驱动t2的效率要优于t2驱动t1的效率。由此得出,小表驱动大表能够减少内循环的次数从而提高连接效率。
另外,如果使用Block Nested Loop Join算法的话,通过扩大一次缓存区的大小也能减小内循环的次数。由此又可得,设置合理的缓冲区大小能够提高连接效率
2.快速匹配:扫描被驱动表寻找合适的记录可以看做一个查询操作,如何提高查询的效率呢?建索引啊!由此还可得出,在被驱动表建立索引能够提高连接效率
5索引使用注意事项
我们现在表t1上的num和count字段建立联合索引。
1索引按照从左匹配原则,num和count的联合索引实际是 (num)、(num,count)两个索引
可以看出使用索引只遍历了一行数据
可以看到索引没有用,遍历了3行数,因为不满从最左边匹配原则
查询范围也会让组合索引部分失效
2索引失效 的几种情况
1如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
2.对于多列索引,不是使用的第一部分,则不会使用索引
3.like查询以%开头
4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.查询时,采用is null条件时,不能利用到索引,只能全表扫描
6.查询条件使用函数在索引列上