Mysql多表关联不走索引的原因及分析

目录
  • 一、准备过程
  • 二、比较
  • 三、千万级别的数据查询个人优化建议
  • 四、MYSQL多表查询的区别
  • 总结

刚入职第一天,有个大佬写了一个统计函数count(*)需要对两张表a,b做统计。咋一看挺简单的,可是表a有1000万条数据,表b有300万条数据。使用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:LOOP
            INSERT INTO `large_student_tb`(`id`,`username`,`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万条??我少写了一个零。

为了科学的严谨。我还得再跑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'  

-- index 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操作来实现。

总结

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。

(0)

相关推荐

  • Mysql跨表更新 多表update sql语句总结

    假定我们有两张表,一张表为Product表存放产品信息,其中有产品价格列Price:另外一张表是ProductPrice表,我们要将ProductPrice表中的价格字段Price更新为Price表中价格字段的80%. 在Mysql中我们有几种手段可以做到这一点,一种是update table1 t1, table2 ts ...的方式: 复制代码 代码如下: UPDATE product p, productPrice pp SET pp.price = pp.price * 0.8 WHER

  • mysql实现多表关联统计(子查询统计)示例

    本文实例讲述了mysql实现多表关联统计的方法.分享给大家供大家参考,具体如下: 需求: 统计每本书打赏金额,不同时间的充值数据统计,消费统计, 设计四个表,book 书本表,orders 订单表  reward_log打赏表   consume_log 消费表 ,通过book_id与book表关联, 问题: 当关联超过两张表时导致统计时数据重复,只好用子查询查出来,子查询只能查一个字段,这里用CONCAT_WS函数将多个字段其拼接 实现: 查询代码如下 SELECT b.id, b.book_

  • Mysql update多表联合更新的方法小结

    下面我建两个表,并执行一系列sql语句,仔细观察sql执行后表中数据的变化,很容易就能理解多表联合更新的用法 student表 class表 1. 执行 UPDATE student s , class c SET s.class_name='test00',c.stu_name='test00' WHERE s.class_id = c.id student表 class表 2. 执行 UPDATE student s JOIN class c ON s.class_id = c.id SET

  • Mysql多表关联不走索引的原因及分析

    目录 一.准备过程 二.比较 三.千万级别的数据查询个人优化建议 四.MYSQL多表查询的区别 总结 刚入职第一天,有个大佬写了一个统计函数count(*)需要对两张表a,b做统计.咋一看挺简单的,可是表a有1000万条数据,表b有300万条数据.使用LEFT JOIN进行查询.结果,一直查询不出来,可能时间就很久了.然后,这个锅就甩给第一天入职的我(我???). 接下来,就研究一下如何对海量数据的查询进行优化. 一.准备过程 1.创建两张表,表A large_student_tb(幼儿园大班学

  • MySQL 多表关联一对多查询实现取最新一条数据的方法示例

    本文实例讲述了MySQL 多表关联一对多查询实现取最新一条数据的方法.分享给大家供大家参考,具体如下: MySQL 多表关联一对多查询取最新的一条数据 遇到的问题 多表关联一对多查询取最新的一条数据,数据出现重复 由于历史原因,表结构设计不合理:产品告诉我说需要导出客户信息数据,需要导出客户的 所属行业,纳税性质 数据:但是这两个字段却在订单表里面,每次客户下单都会要求客户填写:由此可知,客户数据和订单数据是一对多的关系:那这样的话,问题就来了,我到底以订单中的哪一条数据为准呢?经过协商后一致同

  • 你知道mysql哪些查询情况不走索引吗

    目录 前言 mysql哪些查询情况不走索引 不走索引的情况: 总结 前言 在MySQL中,并不是你建立了索引,并且你在SQL中使用到了该列,MySQL就肯定会使用到那些索引的,有一些情况很可能在你不知不觉中,你就“成功的避开了”MySQL的所有索引. mysql哪些查询情况不走索引 1.索引列参与计算,不走索引 SELECT `username` FROM `t_user` WHERE age=20;-- 会使用索引 SELECT `username` FROM `t_user` WHERE a

  • Mysql多表关联更新方式

    目录 Mysql多表关联更新 Mysql多表关联update 总结 Mysql多表关联更新 日常的开发中一般都是写的单表update语句,很少写多表关联的update. 在MySQL中我们有几种方法可以做到多表关联更新: 在两张表之间做关联,可以更新关联的多张表 update  biao1,表2 set 表1.列名=表2.列名  --外键或关联字段 where 更新条件:--例如:表1.列名=表2.列名,或字段aa>1等等 通过 INNER JOIN,可以更新关联的多张表 update 表1 i

  • MySQL的id关联和索引使用的实际优化案例

    昨晚收到客服MM电话,一用户反馈数据库响应非常慢,手机收到load异常报警,登上主机后发现大量sql执行非常慢,有的执行时间超过了10s 优化点一: SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10; 表结构为: CREATE TABLE `game_shares_buy_list` ( `tran_id` int(10) unsigned NOT

  • mysql回表致索引失效案例讲解

    简介 mysql的innodb引擎查询记录时在无法使用索引覆盖的场景下,需要做回表操作获取记录的所需字段. mysql执行sql前会执行sql优化.索引选择等操作,mysql会预估各个索引所需要的查询代价以及不走索引所需要的查询代价,从中选择一个mysql认为代价最小的方式进行sql查询操作.而在回表数据量比较大时,经常会出现mysql对回表操作查询代价预估代价过大而导致索引使用错误的情况. 案例 示例如下,在5.6版本的mysql.1CPU2G内存的Linux环境下,新建一个测试表,并创建将近

  • MySQL详细讲解多表关联查询

    目录 数据库设计范式 外键 内连接 外连接 结语 数据库设计范式 目前数据库设计有五种范式 , 一般我们数据库只需要满足前三项即可 第一范式 : 确保每列保持原子性 什么是原子性? 意思就是不可再分的,例如下 联系方式有 QQ,微信 , 电话等等 , 显然此列不满足原子性, 如果是单独的QQ或者电话等,则只有一个, 满足第一范式 第二范式 : 要有主键,要求其他字段都依赖于主键 为什么主键这么重要? 我们可以这样理解, 如果把表当作一个队伍, 那么主键就是这个队伍的队旗 • 没有主键就没有唯一性

  • MySQL中的全表扫描和索引树扫描 的实例详解

    目录 引言 实例 引言 在学习mysql时,我们经常会使用explain来查看sql查询的索引等优化手段的使用情况.在使用explain时,我们可以观察到,explain的输出有一个很关键的列,它就是type属性,type表示的是扫描方式,代表 MySQL 使用了哪种索引类型,不同的索引类型的查询效率是不一样的. 在type这一列,有如下一些可能的选项: system:系统表,少量数据,往往不需要进行磁盘IOconst:常量连接eq_ref:主键索引(primary key)或者非空唯一索引(u

  • SQL为什么不建议执行超过3表以上的多表关联查询

    目录 摘要: MySQL多表关联查询效率高点还是多次单表查询效率高? 对关联查询进行分解 概述:前段时间在跟其他公司DBA交流时谈到了mysql跟PG之间在多表关联查询上的一些区别,相比之下mysql只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join),而PG是都支持的,而且mysql是往简单化方向去设计的,如果多个表关联查询(超过3张表)效率上是比不上PG的. 摘要: 不超过3层是为了效率.更通用 ,

  • delete in子查询不走索引问题分析

    目录 问题复现 原因分析 优化方案 为什么加个别名就可以走索引了呢? 总结 文章开篇前,先问大家一个问题:delete in子查询,是否会走索引呢?很多伙伴第一感觉就是:会走索引.最近我们有个生产问题,就跟它有关.本文将跟大家一起探讨这个问题,并附上优化方案. 问题复现 MySQL版本是5.7,假设当前有两张表account和old_account,表结构如下: CREATE TABLE `old_account` (   `id` int(11) NOT NULL AUTO_INCREMENT

随机推荐