# 1 数据表设计

```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_binBEGIN
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 获取随机汉字

```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 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 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 生成数据

```[SQL] call createRandomUser(100);

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

# 4 索引对查询性能的影响

```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

```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

```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```

```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)```

## 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```

```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)```

## 4.5 终极版-覆盖索引

```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```

```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)```

0 条评论

• ### 多War项目中静态文件的共享方案

在互联网产品中，一般会有多个项目（Jar、WAR）组成一个产品线。这些WAR项目，因为使用相同的前端架构（jQuery、easyui等），在各个项目中都会存在这...

• ### SpringBoot项目的代理机制【一】

这是了解Spring代理机制的第一篇，尝试了解Spring如何实现Bean的注册和代理。这篇文章会抛出问题：Spring注册Bean，都会用Jdk代理或cgli...

• ### 项目参数外部配置化

开发一个项目，参数是必不可少的，规模越大参数越多。在不同的测试环境中部署，或者是依赖项目的信息发生了变化，你有没有想跳楼的感觉？如果有，恭喜你，你至少已经不是在...

• ### React Native探索（四）Flexbox布局详解

前言 在Android开发中我们有很多种布局，比如LinearLayout和RelativeLayout，同样在React Native也有它的布局，这个布局就...

• ### 如何接手一个新项目

2、为技术服务的项目，比如开源中间件项目（dubbo、spring cloud、各种数据库中间件、各种缓存方案等）；

• ### 6元解决网站托管烦恼！

运维工程师、开发工程师，系统管理员，网站托管庞大的人员团队成本令人头秃！ 拿什么来拯救你！我的网站托管！ ? 咚咚咚，敲黑板，划重点 ? 为您低价打造开通云开...

• ### 6元解决网站托管烦恼！

? 运维工程师、开发工程师、系统管理员...... 网站托管庞大的人力成本令人头秃！ 呜呼，拿什么来拯救你？我的网站托管！ ? 咚咚咚，敲黑板，划重点！ 云开...

• ### 新能力| 云开发静态网站托管能力正式上线

随着云开发用户的数量不断增多，开发者们对于云开发的使用程度越来越深，不少开发者提出了疑问，云开发除了支持小程序以外，还能支持其他平台么？