sql性能分析
- sql执行频率
show global status like 'Com_______'
- 慢查询日志
执行时间超过10秒的sql语句
- profile详情
show profiles帮助我们了解时间都耗费到哪里了
#查看每一条sql的耗时情况
show profiles
#查看指定query_id的sql语句各个阶段的耗时情况
show profile for query query_id
#查看指定query_id的sql语句cpu的耗时情况
show profile cpu for query query_id- explain执行计划
- id值相同，执行顺序从上到下
explain select * from emp e, dept d where e.dept_id = d.id;
- id不同，值越大越先执行
查询选修sql的学生，涉及学生表、课程表、中间表。
先从课程表通过mysql查询课程id
再从中间表通过课程id查询学生id
最后从学生表通过学生id获得行信息
- type
表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。
explain select 'A', 这种无聊没意义的语句的type是null
system访问系统表
const 主键、唯一索引查询：select *from emp where id = 1
ref 非唯一性索引查询：select *from emp where name = 'Xiaohong'
- possible_key
可能用到的索引
- key
用到的索引
- key_len
索引字段最大可能长度
- row
mysql认为必须要执行查询的行数
- filtered
返回结果的行数占需读取行数的百分比
索引使用
索引对效率的提升
针对sn字段创建索引
create index idx_sku_sn on tb_sku(sn);接下来执行索引的话就很快啦！
select * from tb_sku where sn = '100000000153'
最左前缀法则
- 索引了多列
- 查询从索引最左列开始，并且不跳过索引中的列
如果最左侧索引列不存在，则全部失效
如果跳跃某一列，后面的字段索引失效
注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段 ( 即是第一个字段 ) 必须存在，与我们编写 SQL 时，条件编写的先后顺序无关。
范围查询
- 索引了多列
- 范围查询右边的列索引将会失效
- 在不影响业务的情况下尽量使用>=或<=
索引列运算
- 不要对有索引的列进行运算，如下对字符串取子串操作索引失效
explain select * from tb_user where substring(phone,10,2) = '15';
字符串不加引号，索引将会失效
模糊查询
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= 0;
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
explain select * from tb_user where profession like '软件%'; -- 走
explain select * from tb_user where profession like '%工程'; -- 不走
explain select * from tb_user where profession like '%工%'; -- 不走or连接的条件
or分隔开的条件，如果or前面的条件中的列有索引，而后面的条件列中没有索引，那么涉及的索引都不会被用到
explain select * from tb_user where id = 10 or age = 23;数据分布影响
mysql评估使用索引比扫描全表更慢，则不使用索引
select * from tb_user where phone >= '17799990005'; --全表扫描
select * from tb_user where phone >= '17799990015'; --索引生效explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;
如果把表里的profession全都set为null，那么is null就查选表，is not null就走索引
sql提示
加入人为提示达到优化操作
1). use index
： 建议
MySQL
使用哪一个索引完成此次查询（仅仅是建议，
mysql
内部还会再次进
行评估）。
explain select * from tb_user use index(idx_user_pro) where profession = '软件工
程';
2). ignore index
： 忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工
程';
3). force index
： 强制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession = '软件工
程';覆盖索引
explain select id, profession from tb_user where profession = '软件工程' and age =
31 and status = '0' ;
explain select id, profession,age, status from tb_user where profession = '软件工程'
and age = 31 and status = '0' ;
explain select id, profession,age, status, name from tb_user where profession = '软
件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';
select id,name,gender from tb_user where name = 'Arm';需要使用二级索引，name要到聚集索引中根据id查询，也就是需要回表查询，效率比较低
使用select * 很容易出现回表查询的情况
前缀索引
将字符串的一部分前缀建立索引
create index idx_email_5 on tb_user(email(5));
可以根据索引的选择性来决定前缀长度，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值， 索引选择性越高则查询效率越高， 唯一索引的选择性是1
，这是最好的索引选择性，性能也是最好的。
distinct email是求不重复的email字段，选择性是1
select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;单列索引和联合索引
- 单列索引包含一个列
- 联合索引包含多个列
在
and
连接的两个字段
phone
、
name
上都是有单列索引的，但是最终mysql
只会选择一个索引，也就是说，只能走一个字段的索引，此时是会回表查询的
推荐使用联合索引！联合索引也是二级索引，叶子结点是对应行的主键id
在创建联合索引的时候，需要考虑索引的顺序。
索引设计原则
- 数据量超过100w，查询比较频繁的表
- 常作为查询条件的字段建立索引
- 选择区分度高的索引，尽量选择唯一索引
- 字符串类型建立前缀索引
- 尽量使用联合索引，注意遵循最左前缀法则
- 控制索引的数量
