首页 > 其他分享 >第10章_索引优化与查询优化

第10章_索引优化与查询优化

时间：2024-04-02 22:33:36浏览次数：23  
标签：10 age 查询 索引 SELECT student 优化 card

都有哪些维度可以进行数据库调优？简言之：

  • 索引失效、没有充分利用到索引——建立索引
  • 关联查询太多JOIN（设计缺陷或不得已的需求）——SQL优化
  • 服务器调优及各个参数设置（缓冲、线程数等）——调整my.cnf
  • 数据过多——分库分表

关于数据库调优的知识非常分散。不同的DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。
虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。

  • 物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
  • 逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法效率可能更高。

1. 数据准备

学员表 插 50万 条，班级表 插 1万 条。

CREATE DATABASE atguigudb2;
USE atguigudb2;

步骤1：建表

CREATE TABLE `class` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `className` VARCHAR(30) DEFAULT NULL,
  `address` VARCHAR(40) DEFAULT NULL,
  `monitor` INT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `stuno` INT NOT NULL ,
  `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
  `classId` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
  #CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步骤2：设置参数

  • 命令开启：允许创建函数设置：
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

步骤3：创建函数
保证每条数据都不同。

#随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
#假如要删除
#drop function rand_string;

随机产生班级编号

#用于随机产生多少到多少的编号
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;
#假如要删除
#drop function rand_num;

步骤4：创建存储过程

#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, name ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
#假如要删除
#drop PROCEDURE insert_stu;

创建往class表中插入数据的存储过程

#执行存储过程，往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
#假如要删除
#drop PROCEDURE insert_class;

步骤5：调用存储过程
class

#执行存储过程，往class表添加1万条数据
CALL insert_class(10000);

student

#执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);

步骤6：删除某表上的索引
创建存储过程

DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 AND index_name <>'PRIMARY' ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
#若没有数据返回,程序继续,并将变量done设为2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index " , _index , " on " , tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END //
DELIMITER ;

执行存储过程

CALL proc_drop_index("dbname","tablename");

2. 索引失效案例

MysQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

  • 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
  • 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下都(默认）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持 hash索引。
其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器?基于cost开销
(CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

2.1 全值匹配我最爱

系统中经常出现的sql语句如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';

建立索引前执行：（关注执行时间）

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';
Empty set, 1 warning (0.28 sec)

建立索引

CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_classid ON student(age,classId);
CREATE INDEX idx_age_classid_name ON student(age,classId,name);

建立索引后执行：

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';
Empty set, 1 warning (0.01 sec)

2.2 最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
举例1 下面的SQL将使用索引idx_age：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd';

举例2 下面的sql不会使用索引，因为我没有创建classId或者name的索引：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=1 AND student.name = 'abcd';

Q:为什么不会使用idx_age_classid索引？
索引idx_age_classid的B+树会先使用age排序，在使用classId给age相同的数据排序，这个索引根本用不上哟。这就是下面的最左前缀原则。

举例3：索引idx_age_classid_name还能否正常使用？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.age=30 AND student.name = 'abcd';

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd';

image.png
虽然可以正常使用，但是只有部分被使用到了。

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=1 AND student.name = 'abcd';

image.png
完全没有使用上索引。
结论：MySQL可以为多个字段创建索引，一个索引可以包含16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有用这些字段中第一个字段时，多列（或联合）索引不会被使用。

拓展：Alibaba《Java开发手册》
索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

2.3 主键插入顺序

对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间:
image.png
如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：
image.png
可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。 所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 ， 比如： person_info 表：

CREATE TABLE person_info(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  birthday DATE NOT NULL,
  phone_number CHAR(11) NOT NULL,
  country varchar(100) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

2.4 计算、函数、类型转换(自动或手动)导致索引失效

  1. 这两条sql哪种写法更好
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
  1. 创建索引
CREATE INDEX idx_name ON student(NAME);
  1. 第一种：索引优化生效
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

mysql> SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 5301379 | 1233401 | AbCHEa | 164 | 259 |
| 7170042 | 3102064 | ABcHeB | 199 | 161 |
| 1901614 | 1833636 | ABcHeC | 226 | 275 |
| 5195021 | 1127043 | abchEC | 486 | 72 |
| 4047089 | 3810031 | AbCHFd | 268 | 210 |
| 4917074 | 849096 | ABcHfD | 264 | 442 |
| 1540859 | 141979 | abchFF | 119 | 140 |
| 5121801 | 1053823 | AbCHFg | 412 | 327 |
| 2441254 | 2373276 | abchFJ | 170 | 362 |
| 7039146 | 2971168 | ABcHgI | 502 | 465 |
| 1636826 | 1580286 | ABcHgK | 71 | 262 |
| 374344 | 474345 | abchHL | 367 | 212 |
| 1596534 | 169191 | AbCHHl | 102 | 146 |
...
| 5266837 | 1198859 | abclXe | 292 | 298 |
| 8126968 | 4058990 | aBClxE | 316 | 150 |
| 4298305 | 399962 | AbCLXF | 72 | 423 |
| 5813628 | 1745650 | aBClxF | 356 | 323 |
| 6980448 | 2912470 | AbCLXF | 107 | 78 |
| 7881979 | 3814001 | AbCLXF | 89 | 497 |
| 4955576 | 887598 | ABcLxg | 121 | 385 |
| 3653460 | 3585482 | AbCLXJ | 130 | 174 |
| 1231990 | 1283439 | AbCLYH | 189 | 429 |
| 6110615 | 2042637 | ABcLyh | 157 | 40 |
+---------+---------+--------+------+---------+
401 rows in set, 1 warning (0.01 sec)
  1. 第二种：索引优化失效
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499907 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

# 模糊查询
mysql> SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 5301379 | 1233401 | AbCHEa | 164 | 259 |
| 7170042 | 3102064 | ABcHeB | 199 | 161 |
| 1901614 | 1833636 | ABcHeC | 226 | 275 |
| 5195021 | 1127043 | abchEC | 486 | 72 |
| 4047089 | 3810031 | AbCHFd | 268 | 210 |
| 4917074 | 849096 | ABcHfD | 264 | 442 |
| 1540859 | 141979 | abchFF | 119 | 140 |
| 5121801 | 1053823 | AbCHFg | 412 | 327 |
| 2441254 | 2373276 | abchFJ | 170 | 362 |
| 7039146 | 2971168 | ABcHgI | 502 | 465 |
| 1636826 | 1580286 | ABcHgK | 71 | 262 |
| 374344 | 474345 | abchHL | 367 | 212 |
| 1596534 | 169191 | AbCHHl | 102 | 146 |
...
| 5266837 | 1198859 | abclXe | 292 | 298 |
| 8126968 | 4058990 | aBClxE | 316 | 150 |
| 4298305 | 399962 | AbCLXF | 72 | 423 |
| 5813628 | 1745650 | aBClxF | 356 | 323 |
| 6980448 | 2912470 | AbCLXF | 107 | 78 |
| 7881979 | 3814001 | AbCLXF | 89 | 497 |
| 4955576 | 887598 | ABcLxg | 121 | 385 |
| 3653460 | 3585482 | AbCLXJ | 130 | 174 |
| 1231990 | 1283439 | AbCLYH | 189 | 429 |
| 6110615 | 2042637 | ABcLyh | 157 | 40 |
+---------+---------+--------+------+---------+
401 rows in set, 1 warning (3.62 sec)

type为“ALL”，表示没有使用到索引，查询时间为 3.62 秒，查询效率较之前低很多。

再举例：

  • student表的字段stuno上设置有索引
CREATE INDEX idx_sno ON student(stuno);
  • 索引优化失效：（假设：student表的字段stuno上设置有索引）
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

运行结果：
image.png
可以看到如果对索引进行了表达式计算，索引就失效了。这是因为我们需要把索引字段的值都取出来，然后一次进行表达式的计算来进行条件判断，因此采用的就是全表扫描 的方式，运行时间也会慢很多。

  • 索引优化生效：
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

image.png

再举例：

  • student表的字段name上设置有索引
CREATE INDEX idx_name ON student(NAME);

EXPLAIN SELECT id, stuno, name FROM student WHERE SUBSTRING(name, 1,3)='abc';

image.png

  • 索引优化生效
EXPLAIN SELECT id, stuno, NAME FROM student WHERE NAME LIKE 'abc%';

image.png

2.5 类型转换导致索引失效

下列哪个sql语句可以用到索引。（假设name字段上设置有索引）

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

image.png

# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

image.png
name=123发生类型转换，索引失效。

2.6 范围条件右边的列索引失效

  1. 系统经常出现的sql如下：
ALTER TABLE student DROP INDEX idx_name;
ALTER TABLE student DROP INDEX idx_age;
ALTER TABLE student DROP INDEX idx_age_classid;

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

image.png

  1. 那么索引 idx_age_classId_name 这个索引还能正常使用么？
  • 不能，范围右边的列不能使用。比如：(<) (<=) (>) (>=) 和 between 等
  • 如果这种sql出现较多，应该建立：
create index idx_age_name_classId on student(age,name,classId);
  • 将范围查询条件放置语句最后：
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。（创建的联合索引中，务必把范围涉及到的字段写在最后）

  1. 效果

image.png

2.7 不等于(!= 或者<>)索引失效

  • 为name字段创建索引
CREATE INDEX idx_name ON student(NAME);
  • 查看索引是否失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';

image.png
或者

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc';

image.png
场景举例：用户提出需求，将财务数据，产品利润金额不等于0的都统计出来。

2.8 is null可以使用索引，is not null无法使用索引

  • IS NULL: 可以触发索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
  • IS NOT NULL: 无法触发索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

image.png

结论：最好在设计数据库的时候就将字段设置为 NOT NULL 约束，比如你可以将 INT 类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串('')。
扩展：同理，在查询中使用not like也无法使用索引，导致全表扫描。

2.9 like以通配符%开头索引失效

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为'%'，索引就不会起作用。只有'%'不在第一个位置，索引才会起作用。

  • 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'ab%';

image.png

  • 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab%';

image.png

拓展：Alibaba《Java开发手册》
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

2.10 OR 前后存在非索引的列，索引失效

在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。
因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此所以的条件列也会失效。
查询语句使用OR关键字的情况：

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

image.png
因为classId字段上没有索引，所以上述查询语句没有使用索引。

#使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';

image.png
因为age字段和name字段上都有索引，所以查询中使用了索引。你能看到这里使用到了index_merge，简单来说index_merge就是对age和name分别进行了扫描，然后将这两个结果集进行了合并。这样做的好处就是避免了全表扫描。

2.11 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

2.12 练习及一般性建议

练习：假设：index(a,b,c)
image.png
一般性建议

  • 对于单列索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  • 在选择组合索引的时候，尽量选择能够当前query中where子句中更多的索引。
  • 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

总之，书写SQL语句时，尽量避免造成索引失效的情况

3. 关联查询优化

3.1 数据准备

# 分类
CREATE TABLE IF NOT EXISTS `type` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `card` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
);
#图书
CREATE TABLE IF NOT EXISTS `book` (
  `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `card` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`bookid`)
);

#向分类表中添加20条记录
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));

#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

3.2 采用左外连接

我们知道多表查询分为外连接和内连接，而外连接又分为左外连接，右外连接和满外连接。其中外连接中，左外连接与右外连接可以通过交换表来相互改造，其原理也是类似的，而满外连接无非是二者的一个综合，因此外连接我们只介绍左外连接的优化即可。
下面开始 EXPLAIN 分析

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

image.png
在上面的查询sql中，type表是驱动表，book表是被驱动表。在执行查询时，会先查找驱动表中符合条件的数据，再根据驱动表查询到的数据在被驱动表中根据匹配条件查找对应的数据。因此被驱动表嵌套查询的次数是20*20=400次。实际上，由于我们总是需要在被驱动表中进行查询，优化器帮我们已经做了优化，上面的查询结果中可以看到，使用了join buffer，将数据缓存起来，提高检索的速度。

添加索引优化

ALTER TABLE book ADD INDEX Y (card); # [被驱动表]，可以避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

image.png
对于外层表来说，虽然其查询仍然是全表扫描，但是因为是左外连接，LEFT JOIN左边的表的数据无论是否满足条件都会保留，因此全表扫描也是不赖的。另外可以看到第二行的 type 变为了 ref，rows 也变成了1，优化比较明显。这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引

** 我们当然也可以给type表建立索引**

CREATE INDEX X ON `type`(card); #【驱动表】，无法避免全表扫描
# ALTER TABLE `type` ADD INDEX X (card);

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

image.png

注意，外连接的关联条件中，两个关联字段的类型、字符集一定要保持一致，否则索引会失效哦。

接着删除索引Y，我们继续查询：

DROP INDEX Y ON book;
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

image.png
book表使用join buffer，再次验证了左外连接左边的表是驱动表，右边的表是被驱动表，后面我们将与内连接在这一点进行对比。

左外链接左表是驱动表右表是被驱动表，右外链接和此相反，内链接则是按照数据量的大小，数据量少的是驱动表，多的是被驱动表

3.3 采用内连接

删除现有的索引，换成 inner join(MySQL自动选择驱动表)

drop index X on type;
drop index Y on book;（如果已经删除了可以不用再执行该操作）

换成 inner join（MySQL自动选择驱动表）

EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

image.png

添加索引优化

ALTER TABLE book ADD INDEX Y (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

image.png

向type表中再增加20条数据，为type表增加索引优化，观察情况

ALTER TABLE type ADD INDEX X (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

image.png

上面的两次查询中，第一次是使用type作为驱动表，book作为被驱动表。而第二次是使用book作为驱动表，type作为被驱动表。对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
接着删除被驱动表的索引：

DROP INDEX X ON `type`;
EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

image.png

再加上索引，观察结果

ALTER TABLE `type` ADD INDEX X (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;

image.png

被驱动表又进行了反转~

结论：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”

如果没有出现以上情况可以执行以下SQL语句：

#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

ALTER TABLE book ADD INDEX Y (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON `type`.card = book.card;

3.4 join语句原理

join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会很长。在MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

1. 驱动表和被驱动表

驱动表就是主表，被驱动表就是从表、非驱动表。

  • 对于内连接来说：
SELECT * FROM A JOIN B ON ...

A一定是驱动表吗？不一定，优化器会根据你查询语句做优化，决定先查哪张表。先查询的那张表就是驱动表，反之就是被驱动表。通过explain关键字可以查看。

3.2 已经总结了优化器选择的规律：两表中一个表有索引，一个表没有索引，则没有索引的为驱动表，有索引的为被驱动表；两个表都没有索引，则小表驱动大表。

标签：10,age,查询,索引,SELECT,student,优化,card
From： https://www.cnblogs.com/lymf/p/18111646

相关文章

  • 10秒钟用python接入讯飞星火API（保姆级）
    正文：        科大讯飞是中国领先的人工智能公众公司，其讯飞星火API为开发者提供了丰富的接口和服务，以支持各种语音和语言技术的应用。步骤一：注册账号并创建应用首先，您需要访问科大讯飞开放平台官网，注册一个账号。注册完成后，登录账号，在控制台中创建一个新应用。创建......
  • 洛谷 P1008 [NOIP1998 普及组] 三连击
    这道题我们可以用桶排序来做，代码如下：#include<bits/stdc++.h>//万能头 usingnamespacestd;//好习惯 inta[10];//一个桶数组，来确定是否有重复的 intmain(){   ints1,s2,s3;//定三个函数，用于判断    intsum=0;//用于判断数字是否重复    for(int......
  • 粒子群算法（主要针对连续型函数优化问题）
    文章主要参考了以下博文：https://zhuanlan.zhihu.com/p/5648197181.简介粒子群算法是一种解决最优化问题的通用方法，其优点是求解速度快，数值相对稳定，算法简单。粒子群算法分为连续型粒子群算法和离散型粒子群算法，分别用于解决连续型问题和离散型问题。粒子群优化算法源自对鸟......
  • Install astah-professional (9.2.0.0248cd.0) to Ubuntu 23.10
    Unpackingastah-professional(9.2.0.0248cd.0)...Settingupastah-professional(9.2.0.0248cd.0)...PleaseinstallAdoptiumTemurin8u402orlater.HoweverJDK9orlatercannotbeaccepted.Andthentrythefollowingcommandforrunningastah*profession......
  • 就业班 第二阶段 2401--4.1 day10 shell之“三剑客”+Expect
    十一、shell编程-grepegrep支持正则表达式的拓展元字符（或grep -E）#egrep'[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}'file1.txt[root@newrain~]#num1=11、运用正则，判断需要[[]][root@newrain~]#[[$num1=~^[0-9]+$]]&&echo"yes"||echo"n......
  • MIT 6.S081入门lab10 mmap
    MIT6.S081入门lab10mmap一、参考资料阅读与总结1.JournalingtheLinuxext2fsFilesystem文件系统可靠性：磁盘崩溃前数据的稳定性；故障模式的可预测性；操作的原子性-论文核心：将日志事务系统加入Linux的文件系统中；事务系统的要求：元数据的更新；事务系统的顺序性；数据块写入磁......
  • Yang 提取Cifar-100的特征
    不同的点：特征选择需要4096维的特征，而我们的代码可能64维就可以了步骤：①保存模型②提取模型特征，然后应保存为mat备注：MetaSAug_1①保存模型importosimporttimeimportargparseimportrandomimportcopyimporttorchimporttorchvisionimportnumpyasnpimporttorc......
  • Spring Data Elasticsearch String类型不指定filed 索引创建情况
    对于String类型的字段如果不指定类型会默认创建两种倒排索引       "itemSkuCodes":{         "type":"text",         "fields":{           "keyword":{             "type":"keyword",           ......
  • 利用 Optimum Intel 和 fastRAG 在 CPU 上优化文本嵌入
    嵌入模型在很多场合都有广泛应用，如检索、重排、聚类以及分类。近年来，研究界在嵌入模型领域取得了很大的进展，这些进展大大提高了基于语义的应用的竞争力。BGE、GTE以及E5等模型在MTEB基准上长期霸榜，在某些情况下甚至优于私有的嵌入服务。HuggingFace模型hub提供了多种尺......
  • 【数据处理包Pandas】多级索引的创建及使用
    目录一、元组作为一级索引（一）示例1（二）示例2二、引入多级索引（一）多级索引的创建（二）多级索引中的数学选取 首先，导入NumPy库和Pandas库。importnumpyasnpimportpandasaspd一、元组作为一级索引如果想产生如下图所示的学生成绩表：因为DataFrame的行索......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99