性能分析

Sql执行频率

查看当前数据库的INSTERT UPDARTE DELETE SELECT 的访问频次

SHOW GLOBAL STATUS LIKE 'Com_______'

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位：s 默认 10s)所有sql语句的日志

查看是否开启了慢查询

SHOW VARIABLES LIKE 'slow_query_log'

默认没有开启 需要在配置文件/etc/my.cnf中配置如下信息：

#开启慢查询日志开关
slow_query_log=1
#设置慢查询时间
long_query_time=2

profile详情

show profiles能够在做sql优化是帮助我们了解时间都耗费到哪里去了

# 通过have_profiling参数 能够看到当前mysql是否支持profile操作
SELECT @@have_profiling;

默认profiling是关闭的 可通过set语句开启

SET profiling=1;

开启后 执行一系列的业务sql的操作 通过如下指令查看指令的执行耗时：

# 查看每一条SQL的耗时基本情况
show profiles;
# 查看指定的query_id的sql语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的sql语句cpu的使用情况
show profile cpu for query query_id;

explain执行计划

EXPLAIN 或者 DESC 命令获取mysql如何执行select语句的信息 包括select语句执行过程中表如何连接和连接顺序

EXPLAIN select 字段列表 from 表名 where 条件;

explain执行计划各个字段含义：

  • id select查询的序列号 表示查询中执行select子句或者是操作表的顺序（ID相同顺序从上到下 ID不同值越大 越先执行）
  • select_type 表示select的类型 常见的取值有simple(简单表，即不使用表连接或者子查询) primary(主查询 即外层的查询)
  • type 表示连接类型 性能由好到差的连接类型为NULL system const(访问主键或者唯一索引时type为const) eq_ref ref(访问非唯一索引) rang index all
  • possible_key 显示可能应用在这张表上的索引 一个或多个
  • key 实际使用的索引 如果为null 则没有使用索引
  • key_len 表示索引中使用的字节数 该值为索引字段最大可能长度 并非实际长度 在不损失精确性的前提下 长度越短越好
  • rows mysql认为必须要执行查询的行数 在innodb引擎中是一个估计值 可能并不总是准确的
  • filterd 表示返回结果的行数占需读取行数的百分比 filtered的值越大越好

着重看的字段type possible_keys key

索引失效问题

最左前缀法则

如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。

如果跳过某一列 索引部分失效(后面的字段索引失效)

范围查询

联合索引中，出现范围查询(> <) 范围查询呢右侧的索引失效

需要把>修改为>=

索引列运算

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

字符串不加引号

字符串类型字段使用时 不加引号 索引将失效

模糊查询

如果仅仅是尾部模糊匹配 索引不会失效。如果是头部模糊匹配 索引失效。

or连接的条件

用or分割开的条件 如果or前的条件中的列有索引 而后面列中没有索引 那么涉及的索引都不会被用到。

数据分布影响

如果mysql评估使用索引比全表更慢 则不使用索引

优化

sql提示

sql提示 是优化数据库的一个重要手段，简单来说就是在sql语句中加入一些人为的提示来达到优化操作的目的

  1. use index
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程'
  1. ignore index
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程'
  1. force index
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程'

覆盖索引

尽量使用覆盖索引(查询使用了索引 并且需要返回的列 在该索引中已经全部能够找到)，减少select * （容易造成回表查询）