制作mysql大数据表验证覆盖索引

昨天跟同事聊起数据表性能的问题，能不能仅用覆盖索引实现数据的汇总统计。找了一个开发环境已有的数据表进行测试，通过explain命令，能看到mysql通过覆盖索引就能实现sum的需求，而无须去读取实际行数据。

但开发环境数据量太小，对执行时间的优化，没有直观感受，于是决定做一个数据量能到千万级的数据表，方便测试。写个java程序来填充随机数据是第一选择，但还要动用ide太麻烦，尝试直接使用mysql的函数来实现。

1     数据表设计

目的是演示如何生成千万级数据，只设计了一个最简单常用的数据表:user。

create table `user` (
  `user_id` bigint(20) not null auto_increment,
  `account` varchar(32) collate utf8_bin not null,
  `password` varchar(128) collate utf8_bin not null,
  `name` varchar(32) collate utf8_bin not null,
  `email` varchar(64) collate utf8_bin default null,
  `mobile` varchar(20) collate utf8_bin default null,
  `age` int(10) unsigned not null default 0,
  primary key (`user_id`)
) engine=innodb auto_increment=1 default charset=utf8 collate=utf8_bin;

2     编写函数/过程

mysql的rand()函数，返回的是一个随机浮点数。为了实现随机插入数据，将基于这个函数实现。

2.1     获取随机整数

create function `getrandomint`(`maxvalue` int) returns int(11)
begin
  declare randomint int default 0;
  set randomint = floor(rand() * `maxvalue`);
  return randomint;
end

2.2     获取随机字符串

create function `getrandomstring`(`length` int) returns varchar(128) charset utf8 collate utf8_bin
begin
  declare result varchar(128) default '';
  declare chars varchar(30) default 'abcdefghijklmnopqrstuvwxyz';  #全小写字母
  declare charindex int default 0;
  while length > 0 do
    set charindex = getrandomint(26);
    set result = concat(result, substring(chars, charindex + 1, 1));
    set length  = length - 1;
  end while;
  return result;
end

2.3     获取随机手机号

11位手机号，必须1开始，后续10位只要是数字就行，有点不符合现在的手机号规则。

create function `getrandommobile`() returns varchar(128) charset utf8 collate utf8_bin
begin
  declare result varchar(128) default '1';
  declare chars varchar(30) default '123456789';
  declare charindex int default 0;
  declare length int default 10;
  while length > 0 do
    set charindex = getrandomint(9);
    set result = concat(result, substring(chars, charindex + 1, 1));
    set length  = length - 1;
  end while;
  return result;
end

2.4     获取随机汉字

中文汉字的unicode，是从0x4e00(19968)开始的，写个函数随机从前2000个汉字中读出一个。这儿要注意的是char的方法，想生成汉字要使用 using utf16。实测生成的数据存入到 utf8 编码的数据表字段中，能正确显示。

create function `getrandomchinesechar`() returns varchar(2) charset utf8
begin
  declare charvalue int default 19968;
  set charvalue = charvalue + getrandomint(2000);
  return char(charvalue using utf16);
end

2.5     获取随机姓名

姓名还不能完全使用随机汉字，“姓”我决定从百家姓里取前两百个。贴出来的代码中字符串不完整，感兴趣的自己上网查下来补一下就行。

create function `getrandomchinesename`() returns varchar(20) charset utf8
begin
  declare last_names varchar(300) default '赵钱孙李周吴郑王...';
  declare chinesename varchar(20) default '';
  set chinesename = substring(last_names, getrandomint(200) + 1, 1);
  set chinesename = concat(chinesename, getrandomchinesechar());
  set chinesename = concat(chinesename, getrandomchinesechar());
  return chinesename;
end

2.6     插入随机用户数据

在这个过程中实现真正插入用户数据。

create procedure `createrandomuser`(in `count` int)
begin
  declare usercount decimal(10) default 0;

  declare account varchar(32) default '';
  declare thepassword varchar(128) default '';
  declare thename varchar(32) default '';
  declare email varchar(64) default '';
  declare mobile varchar(20) default '';
  declare age int default 0;
 
  while usercount < `count` do
    set account = getrandomstring(10);
    set thepassword = getrandomstring(20);
    set thename = getrandomchinesename();
    set email = concat(account, '@codestory.tech');
    set mobile = getrandommobile();
    set age = 10 + getrandomint(50); #年龄10-60岁
 
    insert into user values(null, account, thepassword, thename, email, mobile, age);
    set usercount = usercount + 1;
  end while;
end 

3     生成数据

执行过程，就可以生成相应的数据。如下代码生成100行

[sql] call createrandomuser(100);
受影响的行: 100
时间: 1.004s

我电脑上这个表的数据行数

mysql> select count(*) from user\g;
*************************** 1. row ***************************
count(*): 10001102
1 row in set (5.70 sec)

如下是我生成的部分数据

  

4     索引对查询性能的影响

设计一个简单的查询：所有赵姓用户且手机号139开头，平均年龄是多少?

测试sql，以及查看执行情况

select count(user_id), avg(age) from user where name like '赵%' and mobile like '139%'\g;
explain select count(user_id), avg(age) from user where name like '赵%' and mobile like '139%'\g;

4.1     只有主键的情况

我们前面创建数据表时，只设置了主键，没有创建任何索引。这时候执行情况

mysql> select count(user_id), avg(age) from user where name like '赵%' and mobile like '139%'\g;
*************************** 1. row ***************************
count(user_id): 682
    avg(age): 34.4296
1 row in set (7.03 sec)

执行耗时7.03秒

mysql> explain select count(user_id), avg(age) from user where name like '赵%' and mobile like '139%'\g;
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: user
         type: all
possible_keys: null
          key: null
      key_len: null
          ref: null
         rows: 9928072
        extra: using where
1 row in set (0.00 sec)

可以看到，查询使用的是全表查询，读了所有的数据行。

4.2     单字段索引-name

首先在name字段创建一个单字段索引

mysql>alter table `user` add index `idx_user_name` (`name`) using btree ;
query ok, 0 rows affected (1 min 34.35 sec)
records: 0  duplicates: 0  warnings: 0

执行sql

mysql> select count(user_id), avg(age) from user where name like '赵%' and mobile like '139%'\g;
*************************** 1. row ***************************
count(user_id): 682
    avg(age): 34.4296
1 row in set (3.52 sec)

耗时3.52秒

mysql> explain select count(user_id), avg(age) from user where name like '赵%' and mobile like '139%'\g;
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: user
         type: range
possible_keys: idx_user_name
          key: idx_user_name
      key_len: 98
          ref: null
         rows: 100634
        extra: using index condition; using where
1 row in set (0.00 sec)

使用索引进行检索，读取的数据减少到 10万行。

4.3     单字段索引-mobile

为了测试方便，先删除name字段的索引，再创建一个mobile字段索引

mysql> alter table `user` drop index `idx_user_name`;
query ok, 0 rows affected (0.05 sec)
records: 0  duplicates: 0  warnings: 0
 
mysql>alter table `user` add index `idx_user_mobile` (`mobile`) using btree ;
query ok, 0 rows affected (1 min 27.50 sec)
records: 0  duplicates: 0  warnings: 0

执行sql

mysql> select count(user_id), avg(age) from user where name like '赵%' and mobile like '139%'\g;
*************************** 1. row ***************************
count(user_id): 682
      avg(age): 34.4296
1 row in set (9.93 sec)

耗时9.93秒

mysql> explain select count(user_id), avg(age) from user where name like '赵%' and mobile like '139%'\g;
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: user
         type: range
possible_keys: idx_user_mobile
          key: idx_user_mobile
      key_len: 63
          ref: null
         rows: 233936
        extra: using index condition; using where
1 row in set (0.00 sec)

尽管我们的sql语句将mobile字段作为第二个查询条件，mysql仍然使用了mobile上的索引进行检索。mobile索引过滤出来的数据有23万行，比基于name的更多，所以耗时也就更长。

4.4     双字段索引-name & mobile

这次我们将两个字段建成一个联合索引。

mysql> alter table `user` drop index `idx_user_mobile`;
query ok, 0 rows affected (0.07 sec)
records: 0  duplicates: 0  warnings: 0
 
mysql> alter table `user` add index `idx_user_name_mobile` (`name`, `mobile`) using btree ;
query ok, 0 rows affected (1 min 54.81 sec)
records: 0  duplicates: 0  warnings: 0

执行sql

mysql> select avg(age) as age_avg from user where name like '赵%' and mobile like '139%'\g;
*************************** 1. row ***************************
age_avg: 34.4296
1 row in set (0.06 sec)

执行时间大大缩短，只需要0.06秒

mysql> explain select avg(age) as age_avg from user where name like '赵%' and mobile like '139%'\g;
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: user
         type: range
possible_keys: idx_user_name_mobile
          key: idx_user_name_mobile
      key_len: 161
          ref: null
         rows: 100764
        extra: using index condition
1 row in set (0.00 sec)

读取的行数还是10万行，但时间大大缩短。从这个时间，我们应该能够猜出mysql的过滤数据的过程。mysql执行where过滤时仅仅通过索引即可完成，然后根据索引中的user_id去数据页面读取相应的age值出来做平均。

4.5     终极版-覆盖索引

前面的分析可以看到，为了计算平均值，mysql还需要读取行数据。如果age字段也在这个索引中，查询性能会进一步提升吗？因为不再读行数据。

调整索引

mysql> alter table `user` drop index `idx_user_name_mobile`;
query ok, 0 rows affected (0.06 sec)
records: 0  duplicates: 0  warnings: 0

mysql> alter table `user` add index `idx_user_name_mobile_age` (`name`, `mobile`, `age`) using btree ;
query ok, 0 rows affected (1 min 55.32 sec)
records: 0  duplicates: 0  warnings: 0

执行sql

mysql> select avg(age) as age_avg from user where name like '赵%' and mobile like '139%'\g;
*************************** 1. row ***************************
age_avg: 34.4296
1 row in set (0.04 sec)

执行时间更短，仅为0.04秒。数据量可能还不够大，同上一个执行的区别不是太大。

mysql> explain select avg(age) as age_avg from user where name like '赵%' and mobile like '139%'\g;
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: user
         type: range
possible_keys: idx_user_name_mobile_age
          key: idx_user_name_mobile_age
      key_len: 161
          ref: null
         rows: 103688
        extra: using where; using index
1 row in set (0.00 sec)

最重要的变化是extra信息：using index condition 变成 using index。using index condition 表示使用了索引作为查询过滤的条件；using index表示整个sql只使用了索引。