Mysql多表关联不走索引的原因及分析
目录
- 一、准备过程
- 二、比较
- 三、千万级别的数据查询个人优化建议
- 四、mysql多表查询的区别
- 总结
刚入职第一天,有个大佬写了一个统计函数count(*)需要对两张表a,b做统计。咋一看挺简单的,可是表a有1000万条数据,表b有300万条数php据。使用LEFT JOIN进行查询。结果,一直查询不出来,可能时间就很久了。然后,这个锅就甩给第一天入职的我(我???)。
接下来,就研究一下如何对海量数据的查询进行优化。
一、准备过程
1.创建两张表,表A large_student_tb(幼儿园大班学生哈哈):1000万条。表B samll_student_tb(小班学生orzzzzzzz):300万条。不建立索引的情况。
a,建立存储过程:插入1000万条数据。n=10000000+1//为结束判断条件
-- 创建存储过程 DROP PROCEDURE IF EXISTS my_insert; CREATE PROCEDURE 开发者_大数据my_insert() BEGIN DECLARE n int DEFAULT 1; loopname:LOOPphp INSERT INTO `large_student_tb`(`id`,`bxwpuusername`,`password`) VALUES ( n,CONCAT('myname',n),CONCAT('password',n)); SET n=n+1; IF n=1000000+1 THEN LEAVE loopname; END IF; END LOOP loopname; END; -- 执行存储过程 CALL my_insert(); -- 数据插入成功后修改表模式InnoDB 时间稍微久点 alter table `large_student_tb` engine=InnoDB;
鹅,确实很慢了。跑了1000s还没有跑完
继续让它跑一下吧。
笑了,这么久跑完了。可怜的电脑~~
b.查询一下条数
SELECT COUNT(*) FROM LARGE_STUDENT_TB
??懵了,是100万条??我少写了一个零。js
为了科学的严谨。我还得再跑900万条。1万s??
先记录一下,100w条:
查所有:1.3s~1.5s。
查某条 username999999:0.6s
继续插入表剩下的900万条。。来把英雄联盟吧哈哈哈哈
还是先查询一下如何进行表的迁移吧。因为预期想来,1000万的表,加入索引,会加快查询速度和聚簇函数的计算速度。从而进行优化。但是我之前在办公室试过,往一张1000万的表里面加索引,速度很慢很慢,第一个想法是先建立一个一样的表,先加上索引,再进行表的迁移。相关操作如下
1.表的迁移:
insert into db1.table1 select * from DB2.table2 #完全复制
-- 创建存储过程 DROP PROCEDURE IF EXISTS my_insert; CREATE PROCEDURE my_insert() BEGIN DECLARE n int DEFAULT 1000000+1; loopname:LOOP INSERT INTO `large_student_tb`(`id`,`username`,`password`) VALUES ( n,CONCAT('myname',n),CONCAT('password',n)); SET n=n+1; IF n=10000000+1 THEN LEAVE loopname; END IF; END LOOP loopname; END; -- 执行存储过程 CALL my_insert(); -- 数据插入成功后修改表模式InnoDB 时间稍微久点 alter table `large_student_tb` engine=InnoDB;
二、比较
1.对增加了索引和没有索引的效果。查询速度是指数级别的增加,如下
SELECT * FROM LARGE_STUDENT_TB a where a.username = 'myname1002554' -- indexjavascript before 5.532s --index after 0.037s
我查询 username。没有对username增加索引的时候,需要5s才能从千万数据级别中查出某一条数据,增加了username字段为索引,秒查询。
2.索引增加后所占据的空间大小,以及表本身的空间大小
1.查询表的大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='simonsdb' and table_name='large_student_tb'; 550.00MB
2.查询该索引的大小
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES WHERE table_schema = 'simonsdb' and table_name='large_student_tb' 235.94MB
如上,索引的增加会带来存储空间的增加。但是速度却是很快。以牺牲空间换取这么大倍数的时间效率,值得。
3.多表连接查询的比较
-- 两表联查 EXPLAIN SELECT * FROM small_student_tb a left join large_student_tb b on a.username = 'myname1002554' ---这个查询不出来,有索引也没有用。待优化 SELECT * FROM small_student_tb a left join small_student_tb b on a.username = b.username; --这个可以查询出来,用时间55s左右,需要优化
3.1 多表查询没有用上索引的原因。 如上3所显示,有个多表查询。我们需要用EXPLAIN关键字来排查原因。
1.单表可快速查询EXPLAIN
EXPLAIN SELECT * FROM LARGE_STUDENT_TB a where a.username = 'myname1002554'
2.两表连接查询ON。可以查出来,但是速度很慢55s。EXPLAIN一下
EXPLAIN SELECT * FROM small_student_tb a left join small_student_tb b on a.username = b.username
我们可以看到表a 也就是 small_student_tb在possible_keys中,没有用上索引。是什么原因导致它没有用上索引。会不会用上了以后就变快了?
综合比较,得出的结论是,左连接会做全盘扫描。类型为ALL,自然就不能使用索引了。因为左表a要全部扫描一遍。
3.查询不出来的语句。
EXPLAIN SELECT * FROM small_student_tb a left join large_student_tb b on a.username = 'myname1002554'
三、千万级别的数据查询个人优化建议
1.加索引。千万级别数据查询需要增加索引,索引在数据越多的情况下,效率越加明显
2.单独查表。两张千万级别的表查询,不建议用联表查。查一张结果,输出一个数据。去查询另外一张。
3.实在需要多表联查,应该注意两张表的字符编码级别是否相同。
四、MYSQL多表查询的区别
1.笛卡尔积:CROSS JOIN
笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以,如果A表有n条记录,B表有m条记录,笛卡尔积产生的结果就会产生n*m条记录。下面的例子,t_blog有10条记录,t_type有5条记录,所有他们俩的笛卡尔积有50条记
2.内连接INNER JOIN
内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。有INNER JOIN,WHERE(等值连接)
SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId=t_type.id; SELECT * FROM t_blog,t_type WHERE t_blog.typeId=t_type.id;
3.左连接LEFT JOIN
左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲,就是先从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录(见最后三条)。
SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;
左边的表格t_blog会全部输出来,右边的表格,没有的数据会为NULL
4.右连接RIGHT JOIN
同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。
5.外连接:OUTER JOIN
外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录,最后加上右表中剩余的记录。另外MySQL不支持OUTER JOIN,但是我们可以对左连接和右连接的结果做UNION操作来实现。
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。
精彩评论