MySQL索引-性能分析

SQL性能分析

SQL执行频率

SHOW [SESSION|GLOBAL] STATUS命令可以提供服务器状态信息。

show global status like ‘Com_______’;该指令可以查看当前数据库INSERT、DELETE、SELECT、UPDATE的访问频次

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL 语句的日志。

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

shop variables like ‘slow_query_log’;来查询慢查询日志开启状态

#配置/etc/my.cnf
[mysqld]

# 服务器的唯一编号，默认是1。

server-id = 1

# 服务器的地址和端口，默认是127.0.0.1:3306。

bind-address = 127.0.0.1

port = 3306

# 数据目录，存储数据库表文件的位置。

datadir = /var/lib/mysql

# 临时文件目录，用于存储排序数据等。

tmpdir = /tmp

# 启用/禁用MySQL的查询缓存。

query_cache_size = 0

query_cache_type = 0

# 设置最大连接数，默认是151。

max_connections = 151

# 设置每个连接的默认字符集。

character-set-server = utf8

# 创建新表时将使用的默认存储引擎。

default-storage-engine = INNODB

# 设置InnoDB的数据文件和日志文件的大小。

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_file_size = 5M

innodb_log_files_in_group = 2

# 启用/禁用InnoDB的redo日志检查。

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

[mysqld_safe]

log-error = /var/log/mysqld.log

pid-file = /var/run/mysqld/mysqld.pid

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock


#开启MySQL慢查询日志开关
show_query_log=1
#设置慢查询日志的时间为2秒，SQL语句超时就会被视作慢查询，记录慢查询日志
long_query_time=2
#设置log文件保存位置
slow_query_log_file = /usr/local/mysql/data/slow.log


#重启mysql
systemctl restart mysql

慢查询日志的存放位置/var/lib/mysql/localhost-slow.log

profile详情

SHOW profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。 通过 have_profiling参数，能够看到当前MySQL是否支持profile操作

SELECT @@have_profiling;

打开profile开关 SET profiling=1;

查询操作的具体耗时

SHOW profiles;
SHOW profile [CPU] query query_id;

explain 执行计划

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

语法：直接在SELECT语句之前加关键字EXPLAIN/DESC

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

EXPLAIN各字段的含义：

• Id：SELECT 查询的序列号，表示查询过程中执行SELECT语句或者是操作表的顺序（ID相同从上到下；ID不同，值大的先执行）

• select_type：

  表示SELECT的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、

  UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）

• type：

  表示连接类型，性能由好到差的连接类型为NULL、system(访问系统表)、const(主键或唯一索引)、eq_ref、ref(非唯一性索引)、index、range、all

  优化时，尽量把 type 转为性能较好的连接类型

• possible_key

  显示可能应用在这张表上多个索引，一个或多个

• KEY

  实际用到的索引，如果没有使用索引则为NULL

• Key_len

  表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的情况下，长度越短越好

• rows

  MySQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不准确

• filtered

  表示返回结果行数占需读取行数的百分比，filtered的值越大越好