索引三表优化实战

最新推荐文章于 2022-07-29 19:27:23 发布
最新推荐文章于 2022-07-29 19:27:23 发布
阅读量130 收藏
点赞数
分类专栏： Mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chengqiuming/article/details/120360665
版权

一 脚本

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
mysql> select * from phone;
+---------+------+
| phoneid | card |
+---------+------+
|       1 |    5 |
|       2 |   15 |
|       3 |    2 |
|       4 |    3 |
|       5 |    8 |
|       6 |   12 |
|       7 |   15 |
|       8 |   17 |
|       9 |   19 |
|      10 |    3 |
|      11 |   17 |
|      12 |   17 |
|      13 |   13 |
|      14 |   15 |
|      15 |   13 |
|      16 |   20 |
|      17 |    2 |
|      18 |    9 |
|      19 |   17 |
|      20 |   17 |
+---------+------+
20 rows in set (0.00 sec)

# 其它两张表脚本参考《索引两表优化实战》

二 实战

1 确保要操作的三张表都没有建索引

mysql> show index from class;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| class |          0 | PRIMARY  |            1 | id          | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from book;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | PRIMARY  |            1 | bookid      | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from phone;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| phone |          0 | PRIMARY  |            1 | phoneid     | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

2 需要执行的业务操作

mysql> select * from class LEFT JOIN book on class.card = book.card LEFT JOIN phone on book.card = phone.card;
+----+------+--------+------+---------+------+
| id | card | bookid | card | phoneid | card |
+----+------+--------+------+---------+------+
|  7 |    3 |      9 |    3 |       4 |    3 |
| 12 |    3 |      9 |    3 |       4 |    3 |
|  7 |    3 |     17 |    3 |       4 |    3 |
| 12 |    3 |     17 |    3 |       4 |    3 |
| 14 |    8 |      6 |    8 |       5 |    8 |
|  2 |   12 |     14 |   12 |       6 |   12 |
|  3 |   12 |     14 |   12 |       6 |   12 |
| 16 |   12 |     14 |   12 |       6 |   12 |
|  2 |   12 |     18 |   12 |       6 |   12 |
|  3 |   12 |     18 |   12 |       6 |   12 |
| 16 |   12 |     18 |   12 |       6 |   12 |
| 10 |   17 |      2 |   17 |       8 |   17 |
| 20 |   17 |      2 |   17 |       8 |   17 |
| 10 |   17 |     15 |   17 |       8 |   17 |
| 20 |   17 |     15 |   17 |       8 |   17 |
|  7 |    3 |      9 |    3 |      10 |    3 |
| 12 |    3 |      9 |    3 |      10 |    3 |
|  7 |    3 |     17 |    3 |      10 |    3 |
| 12 |    3 |     17 |    3 |      10 |    3 |
| 10 |   17 |      2 |   17 |      11 |   17 |
| 20 |   17 |      2 |   17 |      11 |   17 |
| 10 |   17 |     15 |   17 |      11 |   17 |
| 20 |   17 |     15 |   17 |      11 |   17 |
| 10 |   17 |      2 |   17 |      12 |   17 |
| 20 |   17 |      2 |   17 |      12 |   17 |
| 10 |   17 |     15 |   17 |      12 |   17 |
| 20 |   17 |     15 |   17 |      12 |   17 |
|  8 |   13 |     19 |   13 |      13 |   13 |
|  8 |   13 |     19 |   13 |      15 |   13 |
| 13 |   20 |      5 |   20 |      16 |   20 |
| 17 |   20 |      5 |   20 |      16 |   20 |
| 10 |   17 |      2 |   17 |      19 |   17 |
| 20 |   17 |      2 |   17 |      19 |   17 |
| 10 |   17 |     15 |   17 |      19 |   17 |
| 20 |   17 |     15 |   17 |      19 |   17 |
| 10 |   17 |      2 |   17 |      20 |   17 |
| 20 |   17 |      2 |   17 |      20 |   17 |
| 10 |   17 |     15 |   17 |      20 |   17 |
| 20 |   17 |     15 |   17 |      20 |   17 |
|  5 |   10 |      1 |   10 |    NULL | NULL |
|  1 |   16 |      3 |   16 |    NULL | NULL |
|  5 |   10 |      4 |   10 |    NULL | NULL |
|  4 |    1 |      7 |    1 |    NULL | NULL |
| 19 |   11 |     10 |   11 |    NULL | NULL |
| 19 |   11 |     12 |   11 |    NULL | NULL |
|  4 |    1 |     13 |    1 |    NULL | NULL |
|  6 |    7 |     16 |    7 |    NULL | NULL |
| 18 |    7 |     16 |    7 |    NULL | NULL |
|  9 |   15 |   NULL | NULL |    NULL | NULL |
| 11 |   19 |   NULL | NULL |    NULL | NULL |
| 15 |   19 |   NULL | NULL |    NULL | NULL |
+----+------+--------+------+---------+------+
51 rows in set (0.00 sec)

3 没优化前

查询效率很差，需要优化

mysql> explain select * from class LEFT JOIN book on class.card = book.card LEFT JOIN phone on book.card = phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | phone | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

4 建立索引进行优化

mysql> alter table phone add index z(card);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table book add index z(card);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

5 优化后的效果

mysql> explain select * from class LEFT JOIN book on class.card = book.card LEFT JOIN phone on book.card = phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref             | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL            |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | z             | z    | 4       | db01.class.card |    1 |   100.00 | Using index |
|  1 | SIMPLE      | phone | NULL       | ref  | z             | z    | 4       | db01.book.card  |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+

后两行的 type 都是 ref 且总 rows 优化很好，效果不错。因此索引最好建立在需要经常查询的字段中。

三 小结

尽可能减少 join 语句中嵌套的循环总次数，永远用小结果集驱动大的结果集。即小表驱动大表。（书多于类别，书是大表，类别是小表，类别驱动书）

优先优化嵌套循环中的内层循环。

保证 join 语句中被驱动表上 join 条件字段已经被索引。（被驱动的表为 book 和 phone，给它们对应的条件字段 card 建立索引）

当无法保证被驱动表的 Join 条件字段被索引且内层资源充足的前提下，不要太吝啬 JoinBuffer 的设置。

chengqiuming
关注
专栏目录
MySQL优化｜一分钟带你了解三表联查优化
m0_62222133的博客
10-20 1970
创建表 其余两张边在双表优化的案例说过，过去直接复制即可 双表优化 1 CREATE TABLE IF NOT EXISTS `phone` ( 2 `phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 3 `card` INT(10) UNSIGNED NOT NULL, 4 PRIMARY KEY (phoneid) 5 ); 模拟数据 1 INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() *
04-VIP-Mysql索引优化实战一.pdf
11-30
MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...
MySQL高级：三表索引优化案例（三表）
qq_46096321的博客
11-29 656
注： 索引的建立：索引理解 单表索引优化：单表索引优化 三表案例是在两表基础上添加的！如果两表的没看可以先看两表案例 1.建表： 1.1 class表： create table if not exists class( id int(10) unsigned not null primary key auto_increment, card int(10) unsigned not null )ENGINE=InnoDB DEFAULT CHARSET=utf8; 1.2 book 表：
mysql三表关联加索引_MySQL索引优化（索引三表优化案例）
weixin_42398141的博客
02-07 370
【子查询优化】用in 还是 exists1、实验有索引 小表驱动大表select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e where exists (select 1 from emp where e.deptno=emp.deptno);select sql_no_cache sum(e.sa...
MySQL高级篇（高阳）建表sql语句大全
诗和远方，代码和你
07-29 1342
MySQL 高级篇
MySQL多表查询核心优化
weixin_30293135的博客
03-22 1673
概述 在一般的项目开发中，对数据表的多表查询是必不可少的。而对于存在大量数据量的情况时（例如百万级数据量），我们就需要从数据库的各个方面来进行优化，本文就先从多表查询开始。其他优化操作，后续另外更新，敬请关注。 版权说明 著作权归作者所有。 商业转载请联系作者获得授权，非商业转载请注明出处。 作者：Coding-Naga 发表日期： 2016年3月22日 链接...
藏经阁-MySQL表和索引优化实战-11页
06-01
MySQL 表和索引优化实战 MySQL 表和索引优化实战是指在 MySQL 数据库中对表和索引进行优化，以提高查询效率和存储空间利用率。本文将从 MySQL 表和索引的基本概念出发，探讨 MySQL 表和索引优化的实战经验和技巧。...
Oracle DBA实战索引优化
最新发布
03-05
### Oracle DBA实战索引优化 #### 一、索引概述与原理 索引是数据库管理系统(DBMS)中一种可选的数据结构，它的主要目的是为了加快数据查询的速度。Oracle数据库中的索引通常由一系列行标识符（ROWID）及其对应的...
多表查询的优化
bruce128的专栏
09-27 3388
开发的时候遇到了一个数据库查询性能的问题。输出每个查询的时间耗费之后发现，我的一个多表连接查询耗费了很多的时间。网上搜了一下性能优化的办法，大部分的方案是在表的连接字段建索引。     我的三表连接查询语句：                 SELECT count(DISTINCT(c.pin)) as cnt FROM server_jd as a, server_jd_org as b
MySql（十六）--索引三表优化
csdn_kenneth的博客
10-03 382
在上一篇的基础上 添加表 create table if not exists `phone`( `phoneid` INT(10) unsigned NOT NULL auto_increment, `card` INT(10) unsigned NOT NULL, primary key(`phoneid`) )ENGINE=INNODB AUTO_INCREMENT=1 DEF...
多表联合查询速度巨慢解决办法之一
热门推荐
xiangzhihaoCSDN的博客
09-15 3万+
在实际工作当中,有一次碰到了多表联合查询但两分钟才出来结果,但A表和B表都不算大,A表只有五万多条数据,B表只有两万条数据,从中查出符合条件的数据并不需耗时那么长.最后经组长指导才发现A表没有建立索引,最后用CREATE INDEX  索引名 ON 表名(字段名) 为A表添加了一个普通索引之后才解决了问题.         对索引的解释,我觉得                 http://w
多表查询SQL优化
chajinglong的专栏
08-07 9680
多表连接LEFT JOIN  是一个例子：三张表，也就是多表联查，使用聚集函数SUM，用到了GROUP BY select C.channel_short_name,u.standby2,u.user_name,bs.enroll_batch,bs.pay_school_year,bs.settlement_amount,bs.received_amount, (bs.settlement_
SQLServer索引设计与优化实战指南
"本文档是关于SQL Server索引设计与优化的综合指南，涵盖了从集簇索引设计、非聚簇索引创建到索引优化和维护等多个方面，旨在提升SQL Server数据库系统的性能。" 在SQL Server中，索引的设计与优化是至关重要的，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值