SequoiaDB-MySql执行计划分析

执行计划分析

通过 mysqldumpslow 只能找出慢SQL，分析一条SQL为什么执行慢需要查看它的执行计划。在MySQL中，可以通过 explain 命令查看一条sql的执行计划。而执行 explain 的分析指标解释如下：

id:记录 select 序列号，有几个 select 就有几个 id，id 的顺序按select 出现的顺序递增
table：执行计划所在的表，可能是实际存在的，也可能是临时表，临时表由系统起名

select_type：表示 select 的类型，常见取值如下：

类型	说明
SIMPLE	简单表，不使用表连接或子查询
PRIMARY	主查询，即外层的查询
UNION	UNION中的第二个或者后面的查询语句
SUBQUERY	子查询中的第一个

partitions：如果查询是基于分区表的话，会显示查询将访问的分区
possible_keys：可能用到的索引
key：实际用到的索引，这个字段很重要，如果实际key为空，想强制使用前面的possible_keys，可以在查询中使用force index
key_len：索引的长度，一般规律是所用到索引的列数越多，key_len 越长，SQL的性能也越佳
ref：显示在key列记录的索引中，表查找值所用到的列或常量，该列不需要太过关注
rows：读取并扫描的行数，行数越少，效率越高
filtered：存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例(百分比)
extra：额外信息，主要是不适合在其他列中显示，但十分重要的信息，比如下压给引擎的条件
type：这一列表示关联类型或访问类型，即 MySQL 决定如何查找表中的行
type 取值有如下几种，依次从最优到最差分别为：NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

创建数据库及数据表

创建 company 数据库和 employee 数据表用于后续小节分析执行计划。

1）登录 MySQL Shell 连接 MySQL 实例；

/opt/sequoiasql/mysql/bin/mysql -h 127.0.0.1 -P 3306 -u root

2）在 MySQL 实例中创建新数据库 company，并切换到 company；

CREATE DATABASE company;
USE company;

3）在 MySQL 实例 company 数据库中创建分区表 employee；

CREATE TABLE employee 
(
empno INT,
ename VARCHAR(128),
age INT,
PRIMARY KEY (empno)
) ENGINE = sequoiadb COMMENT = "雇员表, sequoiadb: { table_options: { ShardingKey: { 'empno': 1 }, ShardingType: 'hash', 'Compressed': true, 'CompressionType': 'lzw', 'AutoSplit': true, 'EnsureShardingIndex': false } }";

构造数据

使用 MySQL 的存储过程，构造出测试数据用于计划分析。

构造数据

使用存储过程构造数据，生成 1000 条测试数据到 employee 表中；

DROP PROCEDURE IF EXISTS gen_data;
USE company;
-- 创建存储过程
DELIMITER $$

CREATE PROCEDURE gen_data(IN num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = 0;
WHILE i < num DO
INSERT INTO employee VALUES (i, 'Name', 21);
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;

-- 调用存储过程生成 1000 条数据
CALL gen_data(1000);

操作截图:

注意:
仅为实验需要，仅用了1000条数据

数据检查

检查存储过程构造的数据；

SELECT COUNT(1) FROM employee;
SELECT * FROM employee LIMIT 10;

操作截图:

退出MySQL；

\q

慢查询日志配置

开启慢查询日志需要修改配置文件并重启 MySQL 数据库实例。

配置

1)修改MySQL配置文件；

cat >> /opt/sequoiasql/mysql/database/3306/auto.cnf <<EOF
# 开启慢查询日志
slow_query_log=on 
# 慢查询日志存放路径
slow_query_log_file = /opt/sequoiasql/mysql/database/sdb-mysql-slow.log 
# 超过多长时间的查询为慢查询
long_query_time = 1 
EOF

2）重启 MySQL 数据库实例生效；

/opt/sequoiasql/mysql/bin/sdb_sql_ctl restart myinst

3）登录MySQL；

/opt/sequoiasql/mysql/bin/mysql -h 127.0.0.1 -P 3306 -u root

4）进行数据查询；

USE company;
SELECT sleep(2), empno, ename, age FROM employee WHERE empno = 2;

SELECT empno, ename, age FROM employee WHERE empno = 0;

SELECT sleep(2), empno, ename, age FROM employee WHERE empno = 20;

注意:
因为数据量不大，为了构造慢查询情况，其中 sleep(2) 是为了构造查询 SQL 比较慢。

操作截图:

5）退出MySQL；

\q

6）分析慢查询SQL；

mysqldumpslow /opt/sequoiasql/mysql/database/sdb-mysql-slow.log

注意: 开启慢查询日志后，MySQL 会记录执行时间超出 long_query_time 的 sql 语句，对于生成的慢查询日志，可以使用 MySQL 自带的 mysqldumpslow 工具进行分析，mysqldumpslow 有比较多的分析参数，可以通过 mysqldumpslow –help 查看。

操作截图:

执行计划分析

查看执行计划对于分析慢查询的优化具有重要意义。

1）登录 MySQL 实例；

/opt/sequoiasql/mysql/bin/mysql -h 127.0.0.1 -P 3306 -u root

2）查看执行计划；通过 mysqldumpslow 只能找出慢 SQL，分析一条SQL为什么执行慢需要查看它的执行计划。在 MySQL 中，可以通过 explain 命令查看一条sql的执行计划。如查看刚才使用 mysqldumpslow 找到的慢 SQL 的执行计划；

USE company;
EXPLAIN SELECT * FROM employee WHERE age = 20;

操作截图:

注意:
如图可通过 EXPALIN 分析 SQL 的执行计划，看到此到 SQL 没有走索引。

创建索引

3）观察索引对执行计划的影响，为数据表 employee 的 age 查询字段创建索引；

CREATE INDEX age_Index ON employee(age);

操作截图:

再次执行计划分析

4）在创建好索引的表上再次进行执行计划分析；

EXPLAIN SELECT * FROM employee WHERE age = 20;

操作截图: