MySQL百万级数据优化实战

首先我们需要准备好相应的sql数据内容：

导入数据之后，有三张测试使用的数据表：

goods

user

g_u

三张表的关系比较简单，user的id和goods里面的id合并生成关联数据，存储在了g_u里面。

三张数据库表的设计如下所示：

 1CREATE TABLE `g_u` (
 2  `id` int(11) NOT NULL AUTO_INCREMENT,
 3  `g_id` int(11) NOT NULL COMMENT '商品id',
 4  `u_id` int(11) NOT NULL COMMENT '用户id',
 5  PRIMARY KEY (`id`)
 6) ENGINE=InnoDB AUTO_INCREMENT=2800001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 7
 8CREATE TABLE `user` (
 9  `id` int(11) NOT NULL AUTO_INCREMENT,
10  `username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
11  `age` tinyint(3) NOT NULL,
12  `sex` tinyint(1) NOT NULL COMMENT '年龄',
13  PRIMARY KEY (`id`)
14) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
15
16CREATE TABLE `goods` (
17  `id` int(11) NOT NULL,
18  `name` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
19  `price` decimal(6,1) NOT NULL,
20  `des` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
21  PRIMARY KEY (`id`)
22) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

然后可以通过一些专业工具例如说jmeter来模拟生成百万级别的测试数据，我这里提供了相应的数据下载地址，方便大家进行测试数据的下载进行模拟练习：

链接: https://pan.baidu.com/s/1BfddJ8MBtnpeiV84gNmClA 

提取码: 4kmp

数据存储好了之后，g_u表里面存储了1900000条数据记录，百万级别的数据查询大概是个什么概念呢？我们可以先简单来进行一次普通查询的测试，测试sql如下所示：

1SELECT * FROM g_u 

查询效率不高，大概需要耗时2.6s左右

假设现在有这么一个业务场景：

根据商品id查询购买过该商品的用户信息

1.in查询

首先我们来试一下in的嵌套查询：

 1SELECT
 2    *
 3FROM
 4    USER AS u
 5WHERE
 6    u.id IN (
 7        SELECT
 8            u_id
 9        FROM
10            g_u AS gu
11        WHERE
12            gu.g_id = 1
13    )

这种查询的结果是属于极端情况，肯定是不推荐使用的，这里面的查询结果是一个笛卡尔乘积，我试了一下，大概1分钟都没有出结果，所以直接终止了。

不推荐使用in查询进行实际操作，sql改为普通的联表查询

2.普通where查询

1SELECT
2    u.username,u.age,u.sex
3FROM
4    g_u AS gu JOIN user as u 
5WHERE
6    gu.u_id =u.id and gu.g_id=1;

查询时间：1.7s

这里面的执行有一定的先后顺序，通常是先进行表的join操作，然后再是where操作，倘若我们将where操作置前，再进行join操作，或许效率会提升一些。

MySQL里面的查询顺序：

1(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list> 
2(1) FROM <left_table> 
3(3) <join_type> JOIN <right_table> 
4(2) ON <join_condition> 
5(4) WHERE <where_condition> 
6(5) GROUP BY <group_by_list> 
7(6) WITH {CUBE | ROLLUP} 
8(7) HAVING <having_condition> 
9(10) ORDER BY <order_by_list> 

改善之后的sql如下所示：

1SELECT
2u.username,u.age,u.sex
3FROM
4(SELECT u_id FROM g_u as gu where gu.g_id=1) as temp_gu
5JOIN user as u
6ON
7temp_gu.u_id =u.id

发现执行效果稍微有一点提升

查询时间：1.592s

通过适当调整sql语句的顺序可以适当提高查询效率

3.索引优化

但是由于没有加入索引，所以查询的结果无论如何进行调整始终没有太大的变化。于是我们不妨通过使用执行计划来进行sql分析，发现结果如下所示：

sql查询的内容里面的gu表查询的extra是using where，u表查询的extra为空，仍然需要进行进一步的优化处理。

此时不妨试试加入索引进行优化处理。

在g_u表里面加入一个联合索引：g_id和u_id

由于where查询的时候只是用了g_id，因此需要满足索引最左前缀规则，将g_id设置在复合索引的最左列。

这个时候，我们再来进行一次sql的执行计划查看：

这时候我们会发现查询的rows列大大减少了，而且id为2的查询里面extra变为了using index。

查询时间：0.005s

和之前的1.5s进行对比，发现效率一下子提升了数百倍，索引优化的效果果真是很明显啊。

• 适当的加入索引确实可以提高我们的查询效率。

• MySQL使用嵌套查询确实会影响我们的查询效率。

• 熟悉和掌握了联合索引，覆盖索引这些技能的原理可以在性能调优的时候事半功倍。

分页查询

当查询的数据量到达了百万数据级别的时候，我们进行常规的分页会发现越是靠在后边的数据，查询愈发缓慢。

1 SELECT * FROM  g_u as gu  ORDER BY gu.id ASC limit 1800000,100

我们来进行一次执行计划分析，可以发现结果如下，Extra为空。

不妨我们可以利用id为主键索引的优势来进行相应的分页查询提速：

1SELECT * FROM g_u as gu WHERE gu.id>($firstId+$pageSize*$pageSize)  limit 100

测试了一下，确实查询速度提升了不少。

查询时间：0.001s

通过执行计划来进行分析：

sql查询走的是主键索引，而不是之前的辅助索引，确实效率提高了些

但是这种方式的查询有相应的弊端：

如果说id并不是连续的那么查询就会有出入了。

这个时候我们不妨可以试试采用另外一种思路，对于index索引存储在另外一张表来进行数据记录。

这里我们创建另外一张表来进行索引数据的存储：

通过该表，我们可以将g_u表里面原本乱序的id存储在g_u_index表中的index字段中，在g_u_index表里面，通过有序的id来映射原来无序的index字段。

1CREATE TABLE `g_u_index` (
2  `id` int(11) NOT NULL AUTO_INCREMENT,
3  `index` int(11) DEFAULT NULL,
4  PRIMARY KEY (`id`)
5) ENGINE=InnoDB AUTO_INCREMENT=1900539 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

使用辅助表g_u_index的意义在于，通过借助辅助表的顺序id来通过分页计算，从而查找到需要进行分页的index值。

1.通过对于g_u_index表，我们可以根据一下sql来计算相应的id：

1SELECT id FROM g_u_index WHERE id=($firstId+$pageSize*$pageSize) limit 1

由于已经确认查询结果只有一条，所以加入limit 1来提高sql查询效率，保证最终的type为const类型

使用执行计划来进行相应的sql分析：

这条sql的查询时间为：0.001s

对之前的分页sql进行相应的优化处理，sql如下所示：

1SELECT * from g_u as gu WHERE
2gu.id>(
3SELECT id FROM g_u_index WHERE id=($firstId+$pageSize*$pageSize) limit 1
4) limit $pageSize

假设当pageSize参数为10的时候，进行一次测试：

通过上述的查询，时间长度为：0.001s

从一开始查询的1.6秒，提升到了现在的0.001s，性能一下子提升了1600倍左右。

常用的sql优化技巧有哪些？

1.数据量大的时候，应尽量避免全表扫描，应考虑在 where及 order by 涉及的列上建立索引，建索引可以大大加快数据的检索速度。

2.适当的使用Explain可以对sql进行相应的深入分析。

3.当只要一行数据时使用LIMIT 1。

4.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

5.不要在 where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

6.适当的时候采用覆盖索引可以提高查询的效率。

三星索引

如果对索引有深入了解的朋友应该对索引的级别评估有所听闻，对于一个查询而言，一个三星索引，可能是其最好的索引。如果查询使用三星索引，一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描，因此其相应时间通常比使用一个普通索引的响应时间少几个数量级。

对于索引的创建，通常我们会用三颗星来进行等级的评定：

第一颗星：就是where后面的查询字段，可以匹配索引列顺序。

第二颗星：涉及到排序的时候可以直接使用顺序读，避免随机读，在查询的时候通过索引默认排序，避免了文件排序。

第三颗星：索引行查询语句中所有的列。

举个例子

这里有一张测试用的test表，里面结构如下所示：

 1CREATE TABLE `test` (
 2  `id` int(11) NOT NULL AUTO_INCREMENT,
 3  `user_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 4  `sex` tinyint(1) DEFAULT NULL,
 5  `age` tinyint(1) DEFAULT NULL,
 6  `c_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 7  PRIMARY KEY (`id`),
 9  ) ENGINE=InnoDB AUTO_INCREMENT=23459 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

针对下列查询：

 1SELECT
 2    user_name,
 3    sex,
 4    c_date
 5FROM
 6    test
 7WHERE
 8    user_name = 'your word'
 9AND sex = '1'
10ORDER BY
11    c_date

针对上述sql，如果需要设置索引，那么不妨看看以下的索引设计：

（user_name,sex）这里的索引只匹配了where后边的谓词user_name和sex，但是并没有覆盖查询列字段,由于c_date没有在索引列里面，所以需要进行额外的文件排序。通过执行计划进行分析之后，截图如下所示：

（user_name,sex,age）也是没能完全进行索引覆盖，需要进行额外的文件排序。通过执行计划进行分析之后，截图如下所示：

（c_date,sex）这里设计的索引由于没有满足复合索引里面的最左索引匹配原则，因此查询的时候并没有走索引，所以通过执行计划进行分析之后，截图如下所示：

（sex,user_name,c_date）有人可能对于这种索引比较疑惑，因为索引的顺序和where后边的谓词顺序不一致，但是MySQL底层还有一套sql优化器，会将where后边查询的字段进行顺序调整，但是通常不建议这么做，尽量保证查询字段的顺序和所设置的索引字段顺序一致。

（user_name,sex,c_date）既覆盖了查询列的字段，同时where后边的谓词全部都匹配了，利用了索引排序，避免了额外排序的缺陷，正好满足了三星索引的定义。

索引总结

  1.索引大大减少了服务器需要扫描的数据量。

  2.索引可以帮助服务器避免排序和临时表。

  3.索引可以将随机I/O变为顺序I/O。

  4.使用三星索引的查询效果最佳，但是需要结合实际业务场景来进行设计。