MySQL简单三步模拟创建百万数据

简单三部创建模拟数据

你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。

1、创建测试数据库sql

 CREATE TABLE `app_user` (
   `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
   `email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
   `phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
   `gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
   `password` VARCHAR(100) NOT NULL COMMENT '密码',
   `age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
   `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
   `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

2、navicat 创建插入函数

 -- 写函数之前必须要写，标志
 DELIMITER $$
 CREATE FUNCTION `mock_data` ()
   RETURNS INT
 BEGIN
   DECLARE num INT DEFAULT 1000000;
   DECLARE i INT DEFAULT 0;
   WHILE i<num DO
 INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`) VALUES ( CONCAT('用户',i), '12345678@qq.com', CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))), 0,UUID(), FLOOR(RAND()*100) );
     SET i=i+1;
   END WHILE;
   RETURN i;
 END;

注意出现异常不允许创建函数执行

 set global log_bin_trust_function_creators=TRUE;

3、执行函数

 SELECT mock_data() -- 执行此函数 生成一百万条数据

count(*)、count(1)和count(column)区别以及执行效率高低比较

【mysql】count(*)、count(1)和count(column)区别

小结：   count(*) 对行的数目进行计算，包含NULL。   count(column) 对特定的列的值具有的行数进行计算，不包含NULL值。   count(1) 这个用法和count(*)的结果是一样的。

性能问题：   1、任何情况下 SELECT COUNT(*) FROM tablename 是最优选择；   2、尽量减少 SELECT COUNT(*) FROM tablename WHERE COL = 'value' 这种查询；   3、杜绝 SELECT COUNT(COL) FROM tablename WHERE COL2 = 'value' 的出现。

如果表没有主键，那么count(1)比count(*)快。

如果有主键，那么count(主键,联合主键)比count(*)快。

如果表只有一个字段，count(*)最快。

count(1)跟count(主键)一样，只扫描主键。

count(*)跟count(非主键)一样，扫描整个表。

count(*)、count(1)、count(column)执行效率高低比较

count(column) 会忽略为 null 的列，其他两个不会。

执行效率：

  它们三个的效率如何呢？网上说的各有各的理，当表中存在索引和主键的时候（我还没接触过设计表时不设计主键的），三者效率差不多。而我在《高性能MySQL》一书中看到这样一段话：当我们使用 count(*) 时，通配符 * 并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。我们发现最常见的错误就是，在括号内指定一个列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用 count(*），这样写意义清晰，性能也会很好。

测试：   我用100万数据进行测试，发现当且仅当三者有主键时，他们的执行时间几乎相等。

 # 有主键：0.139s，无主键：0.283s
 select count(*) from shop_order
 ​
 # 有主键：0.154s，无主键：0.474s
 select count(id) from shop_order
 ​
 # 有主键：0.139s，无主键：0.285s
 select count(1) from shop_order
 ​
 # 有主键但不使用主键：0.383s （count(普通列)）
 select count(old_id) from shop_order

用75570717条数据进行测试，分别执行两次，测试结果如下：

 # 第一次：50.660s  第二次：45.891s
 select count(*) from tb_mattress_sleep_raw_data
 ​
 # 第一次：59.925s  第二次：46.948s
 select count(`mattress_id`) from tb_mattress_sleep_raw_data
 ​
 # 第一次：90.997s  第二次：70.314s
 select count(`time_zone`) from tb_mattress_sleep_raw_data

另外，在 MyISAM 中，count() 函数总是非常快的，不过这也是有前提条件的，即只有没有任何 where 条件的 count(*)才非常快，这是这个引擎的特性。