Mysql数据库之左连接left join 右连接right join 内连接inner join |
您所在的位置:网站首页 › 用于左连接的是 › Mysql数据库之左连接left join 右连接right join 内连接inner join |
最近,公司的用户达到了700+万,意味着数据库已经达到700+万,聊聊傻傻分不清的连接查询吧! 前提:数据库中一共有三个表:class,book,phone,而且每个数据库表中都有10万条数据,三个表一共有30万条数据,从大数据量的角度来检测所写的sql语句性能是如何的. 一.左连接left join 是left outer join的简写,它的全称是左外连接,是外连接中的一种。 注:左(外)连接,左表(a_table)的记录将会全部表示出来,而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。 用sql语句查询前10000条数据,sql语句表示如下: select * from class LEFT JOIN book on class.card=book.card limit 10000 查询时间为: 用explain检测性能,语句为: EXPLAIN select * from class LEFT JOIN book on class.card=book.card limit 10000 其结果: 从此我们可以看出两个的type都是all ,其中class表需要检测的行数是100398,book表需要检测的行数是100453,可以看出这是一个多么大的工程. 往book表中插入索引: ALTER TABLE `book` ADD INDEX y ( `card`);其结果为: 查询时间为: 查询速度是没有添加索引时的31.6倍,那么再来看看它的性能 从结果中,我们可以看出第二行的type变成了ref ,其中rows也从原来的100453变成了523.优化的比较明显.left join条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引. 删除旧索引,建立class表的新索引 DROP INDEX y ON book;ALTER TABLE `class` ADD INDEX x ( `card`);SQL语句执行所需要的时间: 查询时间和未添加索引的时候索引值差不错. 那看看性能: Class表的type变成了index,还是很糟糕的,而且两个表的rows都是100453,这还是一个很大工程. 二.右连接right join是right outer join的简写,它的全称是右外连接,是外连接中的一种 注:右(外)连接,左表(a_table)只会显示符合搜索条件的记录,而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。 其中还是保持class表中的card字段建立索引,其sql语句为: select * from class RIGHT JOIN book on class.card=book.card limit 10000 执行时间为: 其性能为: 优化较明显,这是因为Right join是先执行book表,在执行class表格,right join条件用于确定如何从左表搜索行,右边一定都有,所以左边是我们的关键点,一定需要建立索引. 删除就索引,在book表中建立新索引: DROP INDEX y ON book;ALTER TABLE `book` ADD INDEX y ( `card`);其sql语句查询时间为: 其sql语句性能: 其情况和两个表都不添加索引的没有多大的变化. 三.内连接组合两个表中的记录,返回关联字段相符的记录,也就是返回两个表的交集(阴影)部分。 注:组合两个表中的记录,返回关联字段相符的记录,也就是返回两个表的交集(阴影)部分。 最后来看看inner join的情况,其book表的card字段建立了索引,class表未建立索引,其sql语句: select * from class INNER JOIN book on class.card=book.card limit 10000 sql语句执行时间: 其性能: 再在class表中建立索引试试: ALTER TABLE `class` ADD INDEX x ( `card`);其查询时间为: 查询时间没有多大变化,但是sql语句的性能呢? 结果是没有多大的变化. 从以上的测试我们可以得出: inner join和 left join差不多,都需要优化右表。而 right join需要优化左表。 四.三表连接 那三个表又该如何优化呢?三个都无索引的时候,sql查询语句如下: select * from class left join book on class.card=book.card left join phone on book.card = phone.card limit 10000 查询时间: 还在可以接受的范围内. 其性能: 从结果可以看出,结果并不太理想.再来看看添加索引的情况 分别给phone表和book表,class表添加索引,sql语句如下: ALTER TABLE `class` ADD INDEX x ( `card`);ALTER TABLE `phone` ADD INDEX z ( `card`);ALTER TABLE `book` ADD INDEX y ( `card`);查询时间为: 其性能为: 最后两行的type都是ref ,且rows的值是500左右,优化效果还是很不错的. 在建立数据的初期,可以根据业务的需要,适当的建立索引还是很有必要的,适当的索引可以大大提高sql语句的性能. |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |