mysql测试数据生成

前言

索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的 IO 成本。MySQL 在 300w 条记录左右性能开始逐渐下降，虽然官方文档说 500~800w 记录，所以大数据量建立索引是非常有必要的。MySQL 提供了 EXPLAIN，用于显示 SQL 执行的详细信息，可以进行索引的优化。使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的，分析你的查询语句或是表结构的性能瓶颈。可以帮助选择更好的索引和写出更优化的查询语句。

本章首先介绍如何通过存储过程随机生成大量随机数据作为 EXPLIAN 的测试数据，然后通过例子详解 EXPLIAN 用法以及各字段含义，最后对 EXPLIAN 用途进行总结。

EXPLAIN 概述

EXPLAIN 命令是查看查询优化器如何决定执行查询的主要方法，使用 EXPLAIN，只需要在查询中的 ｓｅｌｅｃｔ 关键字之前增加 EXPLAIN 这个词即可，MYSQL 会在查询上设置一个标记，当执行查询时，这个标记会使其返回关于在执行计划中每一步的信息，而不是执行它，它会返回一行或多行信息，显示出执行计划中的每一部分和执行的次序，从而可以从分析结果中找到查询语句或是表结构的性能瓶颈。

通过 EXPLAIN，我们可以分析出以下结果：

表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询随机生成大量测试数据

利用 MySQL 内存表插入速度快的特点，先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中。

（1）登录 MySQL

# 1. 连接到远程主机上的 MySQLnbsp;mysql -h [host] -u [username] -p [password]# 2. 查看所有的数据库mysql> show databases;# 3. 选择数据库mysql> use [table_name];# 4. 查看数据库中的表 mysql> show tables;

（2）创建内存表

如果一条一条插入普通表的话，效率太低下，但内存表插入速度是很快的，可以先建立一张内存表，插入数据后，在导入到普通表中。

ｄｒｏｐ TABLE IF EXISTS `big_data_user_memory`;CREATE TABLE `big_data_user_memory` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键ID‘,  `user_id` varchar(20) DEFAULT NULL COMMENT ‘用户ID‘,  `user_name` varchar(20) DEFAULT NULL COMMENT ‘用户名称‘,  `age` tinyint(3) DEFAULT NULL COMMENT ‘年龄‘,  `gender` tinyint(1) DEFAULT NULL COMMENT ‘性别 [0: 男性; 1: 女性]‘,  `phone` varchar(20) DEFAULT NULL COMMENT ‘手机‘,  `group_id` int(11) DEFAULT NULL COMMENT ‘分组ID‘,  `join_time` datetime DEFAULT NULL COMMENT ‘加入时间‘,  `gmt_create` datetime DEFAULT NULL COMMENT ‘创建时间‘,  `gmt_modified` datetime DEFAULT NULL COMMENT ‘更新时间‘,  PRIMARY KEY (`id`),  KEY `index_user_id` (`user_id`) USING HASH) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

（3）创建普通表

创建普通表，参数设置和内存表相同，否则从内存表往普通标导入数据会报错。

ｄｒｏｐ TABLE IF EXISTS `big_data_user`;CREATE TABLE `big_data_user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键ID‘,  `user_id` varchar(20) DEFAULT NULL COMMENT ‘用户ID‘,  `user_name` varchar(20) DEFAULT NULL COMMENT ‘用户名称‘,  `age` tinyint(3) DEFAULT NULL COMMENT ‘年龄‘,  `gender` tinyint(1) DEFAULT NULL COMMENT ‘性别 [0: 男性; 1: 女性]‘,  `phone` varchar(20) DEFAULT NULL COMMENT ‘手机‘,  `group_id` int(11) DEFAULT NULL COMMENT ‘分组ID‘,  `join_time` datetime DEFAULT NULL COMMENT ‘加入时间‘,  `gmt_create` datetime DEFAULT NULL COMMENT ‘创建时间‘,  `gmt_modified` datetime DEFAULT NULL COMMENT ‘更新时间‘,  PRIMARY KEY (`id`),  KEY `index_user_id` (`user_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

（4）创建存储函数

-- 生成随机 UserIdCREATE DEFINER=`root`@`localhost` FUNCTION `generateCode`( n int ) RETURNS varchar(20) CHARSET utf8    DETERMINISTIC BEGIN     ｄｅｃｌａｒｅ chars_str VARCHAR ( 100 ) DEFAULT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789‘;     ｄｅｃｌａｒｅ return_str VARCHAR ( 255 ) DEFAULT ‘‘;     ｄｅｃｌａｒｅ i INT DEFAULT 0;     WHILE         i  0.400 THEN            SET return_str = ｃｏｎｃａｔ(return_str, SUBSTRING(first_name, FLOOR(1 + RAND() * first_name_length), 1));    END IF;    RETURN return_str;END# 生成随机手机号CREATE DEFINER=`root`@`localhost` FUNCTION `generatePhone`() RETURNS ｃｈａｒ(11) CHARSET utf8    DETERMINISTICBEGIN    #Routine body goes here...    ｄｅｃｌａｒｅ head VARCHAR(256) DEFAULT ‘133、153、180、181、189、177、173、149、130、131、132、155、156、145、185、186、176、175、134、135、136、137、138、139、150、151、152、157、158、159、182、183、184、187、188、147、178‘;    ｄｅｃｌａｒｅ content ｃｈａｒ(10) DEFAULT ‘0123456789‘;    ｄｅｃｌａｒｅ phone ｃｈａｒ(11) DEFAULT substring( head, 1 + ( FLOOR(( RAND() * 37 ))* 4 ), 3 );    ｄｅｃｌａｒｅ i int DEFAULT 1;  ｄｅｃｌａｒｅ len int DEFAULT LENGTH(content);    WHILE i  SHOW VARIABLES LIKE ‘%max_heap_table_size%‘;# 3. 修改 tmp_table_size 大小mysql> SET SESSION tmp_table_size = 1024 * 1024 * 1024;# 4. 修改 max_heap_table_size 大小mysql> SET SESSION max_heap_table_size = 1024 * 1024 * 1024;

（7）将内存表中的数据导入普通表

mysql> ｉｎｓｅｒｔ INTO big_data_user ｓｅｌｅｃｔ * FROM big_data_user_memory;

以上，我们通过存储过程快速产生百万条随机测试数据的工作就大功告成了。接下来，我们将用我们产生的数据为基础详解 EXPLIAN 用法以及各字段含义。

（8）准备关联查询数据

CREATE TABLE `big_data_group` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键ID‘,  `group_code` varchar(20) DEFAULT NULL COMMENT ‘分组编码‘,  `number_of_people` int(11) DEFAULT NULL COMMENT ‘人数‘,  `gmt_create` datetime DEFAULT NULL COMMENT ‘创建时间‘,  `gmt_modified` datetime DEFAULT NULL COMMENT ‘更新时间‘,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;CREATE DEFINER=`root`@`localhost` PROCEDURE `generateBigDataGroup`(IN num INT)BEGIN    #Routine body goes here...    ｄｅｃｌａｒｅ i INT DEFAULT 1;    #当i小于传入的参数时执行循环插入    WHILE  i  CALL generateBigDataGroup(100)EXPLIAN 用法以及各字段含义

EXPLIAN 模拟优化器执行 SQL 语句，在 5.6 以及以后的版本中，除过 ｓｅｌｅｃｔ，其他比如 ｉｎｓｅｒｔ，ｕｐｄａｔｅ 和 ｄｅｌｅｔｅ 均可以使用 EXPLIAN 查看执行计划，从而知道 MySQL 是如何处理 SQL 语句，分析查询语句或者表结构的性能瓶颈。

本次 EXPLIAN 以根据手机号码过滤测试数据中手机号码重复的、保留 ID 最小数据的滤重 SQL 语句为例子。

EXPLAINDELETE FROM big_data_user WHERE phone IN (ｓｅｌｅｃｔ phone FROM (ｓｅｌｅｃｔ phone FROM big_data_user bdu1 GROUP BY bdu1.phone HAVING count(*)> 1) p) AND id NOT IN (ｓｅｌｅｃｔ id FROM (ｓｅｌｅｃｔ min(bdu2.id) FROM big_data_user bdu2 GROUP BY bdu2.phone HAVING count(*)> 1) b);

EXPLIAN 出来的信息有 12 列，分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra

id - 查询标识

查询标识，表示 SQL 语句中执行 ｓｅｌｅｃｔ 子句或者是操作的顺序。

id 相同时执行顺序从上至下。id 不同时，如果是子查询，id 的序号会递增，序号越大的越先执行。id 相同，不同都存在时，id 相同的可以认为是一组查询按从上至下的顺序执行，id 值越大越优先执行。id 为 NULL，如果行引用其他行的联合结果，则值可以为 NULL。在这种情况下，表列显示像 这样的值，以指示该行引用 id 值为 M 和 N 的行的并。select_type - 查询类型

查询类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询。

SIMPLE：简单的 ｓｅｌｅｃｔ 查询，查询中不包含子查询或者 UNIONEXPLAIN ｓｅｌｅｃｔ * FROM big_data_user WHERE user_id=‘Jt2BHyxQqsPBoZAO9adp‘;PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为 PRIMARYEXPLAIN ｓｅｌｅｃｔ *, (ｓｅｌｅｃｔ group_code FROM big_data_group WHERE id=group_id) AS group_code FROM big_data_user WHERE user_id=‘Jt2BHyxQqsPBoZAO9adp‘;SUBQUERY：在 ｓｅｌｅｃｔ 或 WHERE 列表中包含了子查询EXPLAIN ｓｅｌｅｃｔ * FROM big_data_user WHERE group_id = (ｓｅｌｅｃｔ id FROM big_data_group WHERE group_code=‘cqlhc1nBKNAlOTQ‘);DEPENDENT SUBQUERY：在 ｓｅｌｅｃｔ 或 WHERE 列表中包含了子查询，该子查询依赖外层查询。EXPLAIN ｓｅｌｅｃｔ *, (ｓｅｌｅｃｔ group_code FROM big_data_group WHERE id=group_id) AS group_code FROM big_data_user WHERE user_id=‘Jt2BHyxQqsPBoZAO9adp‘;DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED（衍生），MySQL 会递归执行这些子查询，把结果放在临时表中EXPLAIN ｓｅｌｅｃｔ * FROM (ｓｅｌｅｃｔ * FROM big_data_user LIMIT 5) AS bduUNION：若第二个 ｓｅｌｅｃｔ 出现在ｕｎｉｏｎ之后，则被标记为 UNION；若ｕｎｉｏｎ包含在 FROM 子句的子查询中，外层 ｓｅｌｅｃｔ 将被标记为DERIVEDEXPLAIN ｓｅｌｅｃｔ * FROM big_data_user WHERE user_id = ‘Jt2BHyxQqsPBoZAO9adp‘ UNION ｓｅｌｅｃｔ * FROM big_data_user WHERE phone = ‘13982711661‘;UNION RESULT：从ｕｎｉｏｎ表获取结果的 ｓｅｌｅｃｔEXPLAIN ｓｅｌｅｃｔ * FROM big_data_user WHERE user_id = ‘Jt2BHyxQqsPBoZAO9adp‘ UNION ｓｅｌｅｃｔ * FROM big_data_user WHERE phone = ‘13982711661‘;table - 查询涉及表

查询涉及表，显示这一行的数据是关于哪张表的。这也可以是下列值之一：

：输出行引用了 id 值为 M 和 N 的行的ｕｎｉｏｎ结果。< derivedN >：该行引用了一个 id 值为 n 的行的派生表结果。< subqueryN >：输出行引用了 id 值为 N 的行的物化子查询的结果。partitions - 匹配到的分区信息

匹配到的分区信息，由查询匹配记录的分区。对于非分区表，值为 NULL。

type - 连接类型

连接类型，对表访问方式，表示 MySQL 在表中找到所需行的方式，又称 “访问类型”。常用的类型有：ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好。

system: 表中只有一条数据， 这个类型是特殊的 const 类型。const: 针对主键或唯一索引的等值查询扫描，最多只返回一行数据。const 查询速度非常快， 因为它仅仅读取一次即可。EXPLAIN ｓｅｌｅｃｔ * FROM big_data_user WHERE id = 1;eq_ref: 此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作通常是 =，查询效率较高。EXPLAIN ｓｅｌｅｃｔ * FROM big_data_user bdu LEFT JOIN big_data_group bdg ON bdu.group_id = bdg.id;ref: 此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了最左前缀规则索引的查询。EXPLAIN ｓｅｌｅｃｔ * FROM big_data_user WHERE user_id=‘Jt2BHyxQqsPBoZAO9adp‘;range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, , >, >=,  1) b);Using filesort : 表示 MySQL 需额外的排序操作，不能通过索引顺序达到排序效果，MySQL Query Optimizer 不得不选择相应的排序算法来实现。一般有 using filesort 都建议优化去掉，因为这样的查询 cpu 资源消耗大。Using temporary : 使用了临时表保存中间结果，MySQL 在对查询结果排序时使用了临时表。常见于 order by， group by， join 操作，查询效率不高，建议优化。Using index : 发生了索引覆盖 ， 查询时在索引树上取到了需要查询的数据，不需要再进行回行操作。Using join buffer : 使用了连接缓存，Block Nested Loop，连接算法是块嵌套循环连接；Batched Key Access，连接算法是批量索引连接。Using where : 表示 MySQL 服务器从存储引擎收到查询数据，再进行 “后过滤”（Post-filter）。所谓 “后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为 “后过滤”。注意：Extra 列出现 Using where 表示 MySQL 服务器将存储引擎返回服务层以后再应用 WHERE 条件过滤。Impossible WHERE : where 子句的值总是 false，不能用来获取任何数据。distinct : 查找 distinct 值，当 MySQL 找到了第一条匹配的结果时，将停止该值的查询，转为后面其他值查询。SQL 执行顺序

想要优化 SQL，必须清楚知道 SQL 的执行顺序，这样再配合 explain 才能事半功倍！

完整 SQL 语句：

ｓｅｌｅｃｔ distinct    from      join  on where    group by    having    order by    limit 

SQL 执行顺序：

1、from 2、on 3、 join 4、where 5、group by 6、having 7、select8、distinct 9、order by 10、limit

总结

我们使用 EXPLAIN 解析 SQL 执行计划时，如果有下面几种情况，就需要特别关注下了：

首先看下 type 这列的结果，如果有类型是 ALL 时，表示预计会进行全表扫描（full table scan）。通常全表扫描的代价是比较大的，建议创建适当的索引，通过索引检索避免全表扫描。再来看下 Extra 列的结果，如果有出现 Using temporary 或者 Using filesort 则要多加关注：Using temporary，表示需要创建临时表以满足需求，通常是因为 GROUP BY 的列没有索引，或者 GROUP BY 和 ORDER BY 的列不一样，也需要创建临时表，建议添加适当的索引；Using filesort，表示无法利用索引完成排序，也有可能是因为多表连接时，排序字段不是驱动表中的字段，因此也没办法利用索引完成排序，建议添加适当的索引；Using where，通常是因为全表扫描或全索引扫描时（type 列显示为 ALL 或 index），又加上了 WHERE 条件，建议添加适当的索引；其他状态例如：Using index、Using index condition、Using index for group-by 则都还好，不用紧张。source：https://morning-pro.github.io/archives/46faebc7.html