MySQL索引下篇

SQL性能分析

# 查看当前数据库insert\update\delete\select的频次
show global status like 'Com_______';

# 慢查询日志
# 记录了所有执行时间超过指定参数(long_query_time,单位秒,默认10秒)的所有SQL语句的日志
# 慢查询日志默认没有开启
show variables like 'slow_query_log';
# 在配置文件中开启
slow_query_log=1 # 开启
long_query_time=2 # 设置超过2秒为慢查询
# 慢查询日志文件所在位置
/var/lib/mysql/localhost-slow.log

# profiling工具
select @@have_profiling;# 查看是否支持该功能
select @@profiling; # 查看是否打开
set profiling = 1; # 打开
# 使用
show profile; # 上一条sql语句各个阶段的耗时
show profiles;# 查看最近几条SQL的耗时情况
show profile for query query_id;# 查看指定query_id的SQL语句各个阶段的耗时
# eg.
# show profile for query 16;
show profile cpu,block io for query 134; # 时长,CPU和IO相关的资源消耗数据

# explain执行计划
id越大越先执行
id一样,执行顺序从上到下
type:访问类型,性能由好到差(null,system,const,eq_ref,ref,range,index,all)
根据主键或者唯一索引,一般会出现const
possible_key:可能用到的索引
key:实际用到的索引
filtered:查询返回的行数占读取行数的百分比

索引使用原则

# 最左前缀法则
# create index idx_user_pro_age_sta on tb_user(profession,age,status); #括号里的顺序是有讲究的
针对联合索引,查询时where语句中联合索引的最左列profession必须存在,不包含最左列则没有走索引
从索引的最左列开始,且不跳过索引中的列
如果跳过了某一列,索引将部分失效(后面的字段索引失效)

# 范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效
create index Idx_3 on test(f1,f2,f3);
explain select * from test where f2 = 2 and f1 > 2;
此时f2索引失效,解决办法:改为f1 >= 2

# 索引列运算
不要在索引列上进行运算操作,索引将失效

# 字符串不加引号
字符串类型的列,不加引号,索引将失效
select * from tb_user where phone = 1776549;# phone是varchar类型,存在隐式类型转换

# 模糊查询
# 尾部模糊匹配,索引不会失效
# 前面模糊匹配(like '%工程'),索引失效

# or连接的条件
# 只有两端都有索引,才会生效

# 数据分布影响
# 受表中数据的分布情况
如果mysql的优化器评估,使用索引比全表还慢,则不使用索引

# SQL提示
# 在SQL语句中加入一些人为提示,达到优化操作的目的
# 放在 from table_name 后面
# eg.
# select * from tb_user force index(index_name) where profession = '软件工程';
use index(index_name) # 建议使用
ignore index(index_name) # 忽略索引
force index(index_name) # 强制使用该索引

# 覆盖索引
尽量使用覆盖索引(查询时使用了索引,且需要返回的列,在该索引中能全部找到)
避免使用select *,容易出现回表查询
# using index condition:查询使用了索引,但需要回表查询数据
# using where;using index:查询使用了索引,且需要返回的列数据在索引中都能找到,不需要回表查询

# 前缀索引
# 当字段类型为字符串(varchar,text等)时,有时需要索引很长的字符串,这会让索引变得很大
# 此时可以只将字符串的一部分前缀,建立索引,可以大大节约索引空间
# create index index_name on table_name(col_name(n));
# 具体截取长度n,取决于前几个字符的唯一率多高
select count(email) from tb_user; #email字段不为null的有多少
select count(distinct email) from tb_user; # 不为null,且去重后有多少
select count(distinct substring(email,1,5))/count(*) from tb_user;

索引设计原则

1.针对数据量较大,且查询比较频繁的表建立索引(1张表超过100w条记录)
2.针对常作为查询条件(where),排序(order by),分组(group by)操作的字段建立索引
3.尽量选择区分度高的列(数据重复的情况不多)作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高
4.如果字符串类型的字段,字段的长度较长,可以针对字段的特点,建立前缀索引
5.尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表查询
6.控制索引的数量,索引不是多多益善,索引越多,维护索引结构的代价也越大,会影响增删改的效率
7.如果索引列不能存储null值,请在创建表时使用not null约束.当优化器知道每列是否包含null值时,它可以更好地确定哪个索引最有效地用于查询

索引是高效获取数据的数据结构
mysql中profile的使用