查看数据库的执行频次
show global status like ‘com_______’;
慢查询日志
介绍
慢查询日志记录了所有超过指定时间(默认10s)的SQL语句。
默认mysql不开启慢查询
配置
my.cnf
#开启mysql慢日志开关
slow_query_log=1
#设置慢日志的时间为2s
long_query_time=2
查看日志
打开文件localhost-slow.log查看即可
profile
介绍
profile是mysql提供的用来监测查询语句执行情况的功能
查看你的mysql版本是否支持profile功能
select @@have_profiling;
如果显示YES,则说明支持
开启profile
set [session|global] profiling = 1;
#如果指定session则只对当前会话生效,如果指定global则对全局生效
查看profiles
show profiles;#查询每一条sql耗时情况
show profile for query query_id;#查看指定id的sql语句详情耗时
show profile cpu for query query_id;#查看指定id的sql语句cpu使用情况
explain 执行计划
使用方法
在sql语句前面加一个explain或者desc关键字即可
explain select * from emp where id=5;
执行结果
部分列含义
id:主键
select_type:表示select类型,常见的取值有simple(简单表,即不适用表连接或者子查询),primary(主查询,即外层的查询),union(union中的第二个或者后面的语句),subquery(子查询)
type:表示访问类型,表示连接类型,性能由好到差依次为NULL、system、const、eq_ref、ref、range、index、all
possible_key:可能引用的索引
key:实际应用的索引
key_len:使用到的索引的字节数
rows:执行查询的行数,是一个估值
filtered:表示返回的行数占需要读取的行数的百分比,filtered的值越大越好
索引优化
最左前缀法则
如果索引了多列,要遵守最左前缀法则。最左前缀法则是指查询从索引的最左开始,且不跳过索引中的列。如果跳过某一列,那么这一列及往后的索引全部失效。
范围查询
在联合索引中如果出现范围查询,范围右侧的列索引失效
列运算
如果索引列参与了运算,那么索引就是失效了
字符串列
如果字符串列不用引号,就不会使用索引。不加单引号会存在隐式类型转化或者直接报错,不会走索引。
模糊查询
如果尾部进行模糊查询,索引生效,如果头部进行模糊查询,索引不生效。
or查询
使用or查询时,只要其中有一个条件没有索引,那么整体就会失效
数据分布预测
如果mysql预测使用索引效率还不如全表扫描的时候,就会直接进行全表扫描,比如有一个字段是age,age的最小值是10,那么如果有一个条件是age>=10,mysql就会直接全盘扫描,因为age>=10一看就知道数据量覆盖了全表,就没必要用索引了。
SQL提示
use index
为sql语句提供参考索引
select * from table use index(name,age);
ignore index
告诉mysql不要使用某些索引
select * from table ignore index(name,age);
force index
强制mysql使用指定索引
select * from table_name force index(index_name) where xxx;
覆盖索引
查询的列尽量包括在索引中,这样子效率高,否则需要回表查询才能拿到目标列的值
前缀索引
如果有的字段是很长的字符串,那么拿这个字段来创建索引就会消耗大量的空间,得不偿失,mysql为我们提供了前缀索引,可以将字符串的前几个字符提取出来作为索引,降低索引空间。
create index index_name on table_name(column(n));
单列索引与联合索引的选择
设想这一种情况
info表结构
id:主键
name:姓名
phone:电话
且三个字段均有索引,id对应着唯一索引(聚合索引),name和phone是普通的B+Tree索引
当我们这样子查询时:
select id,name,phone from info where name=‘xxx’ and phone=‘xxxx’;
由于name和phone都是单列索引,mysql就会选择其中一个去使用,不会两个都使用。
假设选择是name索引,那么根据name索引查出来的数据只有name和id信息,并没有phone信息,所以必须进行一次回表查询才能查出完整的数据。
而当我们为name,phone建立一个联合索引后,mysql只需要对这个联合索引进行一次查询就可以返回我们需要的信息,少了一次回表查询,效率更高。