动易学校网站系统,重庆提供行业网站建站报价,未备案网站通知,wordpress 允许用户上传图片业务场景 一般在项目开发中会有很多的统计数据需要进行上报分析#xff0c;一般在分析过后会在后台展示出来给运营和产品进行分页查看#xff0c;最常见的一种就是根据日期进行筛选。这种统计数据随着时间的推移数据量会慢慢的变大#xff0c;达到百万、千万条数据只是时间问…业务场景 一般在项目开发中会有很多的统计数据需要进行上报分析一般在分析过后会在后台展示出来给运营和产品进行分页查看最常见的一种就是根据日期进行筛选。这种统计数据随着时间的推移数据量会慢慢的变大达到百万、千万条数据只是时间问题。
一、数据准备生成百万数据
sql将your_table_name 改成自己的表名目前我的表中有idnamepassword、create_time四个字段(这个是生成一百万数据的会有点影响性能插入比较耗时
INSERT INTO your_table_name (name, password, create_time, age)
SELECT SUBSTRING(MD5(RAND()), 1, 10),SUBSTRING(MD5(RAND()), 1, 10),NOW() - INTERVAL FLOOR(RAND() * 31536000) SECOND,FLOOR(RAND() * 100) 1
FROM(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t1,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t2,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t3,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t4,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t5,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t6,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t7,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t8,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t9,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t10;
可以选择每次插入10万条数据多次插入效果比一次插入效果更好。 建表SQL
CREATE TABLE user (id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 主键,name varchar(50) DEFAULT NULL COMMENT 名字,password varchar(50) DEFAULT NULL COMMENT 密码,age int(3) DEFAULT NULL COMMENT 年龄,create_time datetime DEFAULT NULL COMMENT 创建时间,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8;
二、场景复现
创建了一张user表给create_time字段添加了索引。并在该表中添加了100w条数据。
CREATE TABLE user (id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 主键,name varchar(50) DEFAULT NULL COMMENT 名字,password varchar(50) DEFAULT NULL COMMENT 密码,age int(3) DEFAULT NULL COMMENT 年龄,create_time datetime DEFAULT NULL COMMENT 创建时间,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8;
查询前10条基本上不消耗什么时间
SELECT SQL_NO_CACHE *
FROM user
WHERE create_time BETWEEN 2023-01-01 AND 2023-05-23
LIMIT 1,10; 从第100w开始取数据的时候查询耗时1.5秒。
SELECT SQL_NO_CACHE *
FROM user
WHERE create_time BETWEEN 2023-01-01 AND 2023-05-23
LIMIT 1000000,10; SQL_NO_CACHE 这个关键词是为了不让SQL查询走缓存。 同样的SQL语句不同的分页条件两者的性能差距如此之大那么随着数据量的增长往后页的查询所耗时间按理会越来越大。
三、问题分析
1、回表
我们一般对于查询频率比较高的字段会建立索引。索引会提高我们的查询效率。我们上面的语句使用了SELECT * FROM user但是我们并不是所有的字段都建立了索引。当从索引文件中查询到符合条件的数据后还需要从数据文件中查询到没有建立索引的字段。那么这个过程称之为回表。
2、覆盖索引
如果查询的字段正好创建了索引了比如 SELECT create_time FROM user我们查询的字段是我们创建的索引那么这个时候就不需要再去数据文件里面查询也就不需要回表。这种情况我们称之为覆盖索引。
3、IO
回表操作通常是IO操作因为需要根据索引查找到数据行后再根据数据行的主键或唯一索引去聚簇索引中查找具体的数据行。聚簇索引一般是存储在磁盘上的数据文件因此在执行回表操作时需要从磁盘读取数据而磁盘IO是相对较慢的操作。
4、问题衍生
当我们查询 LIMIT 2000,10 会不会扫描1-2000行你之前有没有跟我一样觉得数据是直接从2000行开始取的前面的根本没扫描或者不回表。其实这样的写法一个完整的流程是查询数据如果不能覆盖索引那么也是要回表查询数据的。
所以越到后面大概率是会查询越慢
四、问题总结
我们现在知道了LIMIT 遇到后面查询的性能越差性能差的原因是因为要回表既然已经找到了问题那么我们只需要减少回表的次数就可以提升查询性能了。
五、解决方案
既然覆盖索引可以防止数据回表那么我们可以先查出来主键id主键索引然后将查出来的数据作为临时表然后 JOIN 原表就可以了这样只需要对查询出来的5条结果进行数据回表大幅减少了IO操作。
六、优化前后性能对比
我们看下执行效果 优化前1.5s
SELECT SQL_NO_CACHE *
FROM user
WHERE create_time BETWEEN 2003-01-01 AND 2003-05-23
LIMIT 1000000,10; 优化后0.6s
SELECT SQL_NO_CACHE *
FROM user
WHERE create_time BETWEEN 2003-01-01 AND 2023-05-23
LIMIT 1000000,10;SELECT SQL_NO_CACHE *
FROM (SELECT SQL_NO_CACHE id
FROM user
WHERE create_time BETWEEN 2003-01-01 AND 2023-05-23
LIMIT 1000000,10) AS temp
INNER JOIN user AS u ON u.id temp.id; 查询耗时性能大幅提升。这样如果分页数据很大的话也不会像普通的limit查询那样慢。
总结 其实实际业务场景数据达到百万了都会选择三方工具了比如ES本文只是拿分页数据做例子探讨一下SQL的查询效率。
