数据准备与索引失效的11种情况1、索引失效的11种情况2、外连接与内连接的查询优化、JOIN语句的底层原理、子查询优化与排序优化、GROUP BY优化、分页查询优化、覆盖索引的使用、索引条件下推(ICP)、其他查询优化策略、淘宝数据库的主键如何设计

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

# 06-索引优化与查询优化

# 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表添加1万条数据
CALL insert_class(10000);

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

SELECT COUNT(*) FROM class;

SELECT COUNT(*) FROM student;

# 步骤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. 索引失效案例

# 2.1 全值匹配我最爱

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';

SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';
#165 ms     #61 ms      #40 ms       #21 ms

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);

# 2.2 最佳左前缀法则

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

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

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

DROP INDEX idx_age ON student;

DROP INDEX idx_age_classid ON student;

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

# 2.3 主键插入顺序

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

#此语句比下一条要好！（能够使用上索引）
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';

CREATE INDEX idx_name ON student(name);

# 再举例：
CREATE INDEX idx_sno ON student(stuno);

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;    #135 ms

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;  #31 ms

# 再举例：

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

# 2.5 类型转换导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 123;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = '123';

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

SHOW INDEX FROM student;

CALL proc_drop_index('atguigudb2','student');

CREATE INDEX idx_age_classId_name ON student(age,classId,name);

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

CREATE INDEX idx_age_name_cid ON student(age,name,classId);

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

CREATE INDEX idx_name ON student(name);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';

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

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

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

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

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'ab%';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab%';

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

SHOW INDEX FROM student;

CALL proc_drop_index('atguigudb2','student');

CREATE INDEX idx_age ON student(age);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

CREATE INDEX idx_cid ON student(classId);

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

# 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)));

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

# 3.2 采用左外连接

# 情况1：左外连接

#添加索引
CREATE INDEX Y ON book(card);

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

CREATE INDEX X ON type(card);

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

DROP INDEX Y ON book;

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

# 3.3 采用内连接

# 情况2：内连接

DROP INDEX X ON type;

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

#添加索引
CREATE INDEX Y ON book(card);

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

CREATE INDEX X ON type(card);

#结论：对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

#删除索引
DROP INDEX Y ON book;

#结论：对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

CREATE INDEX Y ON book(card);

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

#向type表中添加数据（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)));

#结论：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。"小表驱动大表"
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

# 3.4 JOIN语句原理

#JOIN的底层原理
# ① 驱动表和被驱动表
CREATE TABLE a(f1 INT, f2 INT, INDEX(f1))ENGINE=INNODB;

CREATE TABLE b(f1 INT, f2 INT)ENGINE=INNODB;

INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);

INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

#测试1
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);

#测试2
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) AND (a.f2=b.f2);

#测试3
EXPLAIN SELECT * FROM a JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);

# ② Simple Nested-Loop Join(简单嵌套循环连接)

# ③ Index Nested-Loop Join(索引嵌套循环连接)

# ④ Block Nested-Loop Join(块嵌套循环连接)
SHOW VARIABLES LIKE '%optimizer_switch%';

SHOW VARIABLES LIKE '%join_buffer%';

# ⑤ 小结
# 1. 整体效率比较：INLJ > BNLJ > SNLJ
# 2. 永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）（小的度量单位指的是 表行数*每行大小）
SELECT t1.b,t2 * FROM t1 STRAIGHT_JOIN t2 ON (t1.b=t2.b) WHERE t2.id <= 100;    #推荐
SELECT t1.b,t2 * FROM t2 STRAIGHT_JOIN t1 ON (t1.b=t2.b) WHERE t2.id <= 100;    #不推荐
# 3. 为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
# 4. 增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数越少）
# 5. 减少驱动表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

# 4. 子查询优化

#创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);

#查询班长的信息
EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
);

EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` IS NOT NULL;

#查询不为班长的学生信息
EXPLAIN SELECT SQL_NO_CACHE a.*
FROM student a
WHERE  a.stuno  NOT  IN (
			SELECT monitor FROM class b
			WHERE monitor IS NOT NULL);


EXPLAIN SELECT SQL_NO_CACHE a.*
FROM  student a LEFT OUTER JOIN class b
ON a.stuno =b.monitor
WHERE b.monitor IS NULL;

# 5. 排序优化

# 5.1 排序优化
# 优化建议：
# 1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，
# 目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。
# 当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
# 2. 尽量使用 Index 完成 ORDER BY 排序。
# 如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
# 3. 无法使用 Index 时，需要对 FileSort 方式进行调优。

# 5.2 测试
#删除student和class表中的非主键索引
CALL proc_drop_index('atguigudb2','student');
CALL proc_drop_index('atguigudb2','class');

SHOW INDEX FROM student;
SHOW INDEX FROM class;

#过程一：
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

#过程二：order by时不limit，索引失效
#创建索引
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);

#不限制,索引失效
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

#EXPLAIN  SELECT SQL_NO_CACHE age,classid,name,id FROM student ORDER BY age,classid;

#增加limit过滤条件，使用上索引了。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

#过程三：order by时顺序错误，索引失效

#创建索引age,classid,stuno
CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno);

#以下哪些索引失效?
EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY classid,NAME LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY age,classid LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;

#过程四：order by时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）

EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

#过程五：无过滤，不索引

EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;

EXPLAIN  SELECT * FROM student WHERE  age=45 ORDER BY classid,NAME;

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age LIMIT 10;

CREATE INDEX idx_cid ON student(classid);
EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;

#小结
# INDEX a_b_c(a,b,c)

# order by 能使用索引最左前缀
# - ORDER BY a
# - ORDER BY a,b
# - ORDER BY a,b,c
# - ORDER BY a DESC,b DESC,c DESC

# 如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
# - WHERE a = const ORDER BY b,c
# - WHERE a = const AND b = const ORDER BY c
# - WHERE a = const ORDER BY b,c
# - WHERE a = const AND b > const ORDER BY b,c

# 不能使用索引进行排序
# - ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
# - WHERE g = const ORDER BY b,c /*丢失a索引*/
# - WHERE a = const ORDER BY c /*丢失b索引*/
# - WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
# - WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

# 5.3 案例实战

#实战：测试filesort和index排序
CALL proc_drop_index('atguigudb2','student');

# 场景:查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME; #143 ms

# 方案一: 为了去掉filesort我们可以把索引建成
CREATE INDEX idx_age_name ON student(age,name);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME; #76 ms

# 方案二：
CREATE INDEX idx_age_stuno_name ON student(age,stuno,name);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;  #43 ms

# 结论：
# 1. 两个索引同时存在，mysql自动选择最优的方案。（对于这个例子，mysql选择idx_age_stuno_name）。
# 但是， 随着数据量的变化，选择的索引也会随之变化的 。
# 2. 当【范围条件】和【group by 或者 order by】的字段出现二选一时，
# 优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，
# 优先把索引放在范围字段上。反之，亦然。

DROP INDEX idx_age_stuno_name ON student;

CREATE INDEX idx_age_stuno ON student(age,stuno);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;

# 6. GROUP BY优化

# group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。

# group by 先排序再分组，遵照索引建的最佳左前缀法则

# 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置

# where效率高于having，能写在where限定的条件就不要写在having中了

# 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。
# Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

# 包含了order by、group by、distinct这些查询的语句，
# where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

# 7. 优化分页查询

EXPLAIN SELECT * FROM student LIMIT 2000000,10;

# 优化思路一
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;

# 优化思路二
EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

# 8. 优先考虑覆盖索引

#删除之前的索引
#举例1：
DROP INDEX idx_age_stuno ON student;

CREATE INDEX idx_age_name ON student (age,NAME);

EXPLAIN SELECT * FROM student WHERE age <> 20;

EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;

#举例2：
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

CREATE INDEX idx_age_name ON student (age,name);

EXPLAIN SELECT id,age FROM student WHERE NAME LIKE '%abc';

# 10. 索引条件下推(ICP)

#举例1：
USE atguigudb1;

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

#举例2：
# 建表
CREATE TABLE `people` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `zipcode` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
      `firstname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
      `lastname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
      `address` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `zip_last_first` (`zipcode`,`lastname`,`firstname`)
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

# 插入数据
INSERT INTO `people` VALUES
    ('1', '000001', '三', '张', '北京市'),
    ('2', '000002', '四', '李', '南京市'),
    ('3', '000003', '五', '王', '上海市'),
    ('4', '000001', '六', '赵', '天津市');

# 查询操作
EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';

EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '张%'
AND firstname LIKE '三%';

#ICP的开启/关闭

#关闭
SET optimizer_switch = 'index_condition_pushdown=off';

#开启
SET optimizer_switch = 'index_condition_pushdown=on';

# 开启和关闭ICP的性能对比

#创建存储过程，向people表中添加1000000条数据，测试ICP开启和关闭状态下的性能
DELIMITER //
CREATE PROCEDURE  insert_people( max_num INT )
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO people ( zipcode,firstname,lastname,address ) VALUES ('000001', '六', '赵', '天津市');
        UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //

DELIMITER ;

#调用存储过程
CALL insert_people(1000000);

#首先打开profiling。
SET PROFILING = 1;

#执行SQL语句，此时默认打开索引下推
SELECT * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%';

#再次执行SQL语句，不使用索引下推
SELECT /*+ no_icp (people) */ * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%';

#查看当前会话所产生的所有profiles
SHOW PROFILES;

# 12. 其它查询优化策略

# 12.1 EXISTS 和 IN 的区分
# 索引是个前提，其实选择与否还会要看表的大小。你可以将选择的标准理解为小表驱动大表。

# 12.2 COUNT(*)与COUNT(具体字段)效率
# 环节1：COUNT(*)和COUNT(1)都是对所有结果进行COUNT，
# COUNT(*)和COUNT(1)本质上并没有区别（二者执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的）。
# 如果有WHERE子句，则是对所有符合筛选条件的数据行进行统计；
# 如果没有WHERE子句，则是对数据表的数据行数进行统计。

# **环节2：如果是MyISAM存储引擎，统计数据表的行数只需要O(1)的复杂度，
# 这是因为每张MyISAM的数据表都有一个meta信息存储了row_count值，而一致性则是由表级锁来保证的。

# 如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，
# 所以无法像MyISAM一样，维护一个row_count变量，因此需要采用扫描全表，是O(n)的复杂度，
# 进行循环+计数的方式来完成统计。

# **环节3：在InnoDB引擎中，如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引。
# 因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。
# 对于COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。

# 如果有多个二级索引，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

# 12.3 关于SELECT(*)
# 在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原因：

# ① MySQL 在解析的过程中，会通过查询数据字典将"*"按序转换成所有列名，这会大大的耗费资源和时间。

# ② 无法使用覆盖索引

# 12.4 LIMIT 1 对优化的影响
# 针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上LIMIT 1的时候，
# 当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

# 如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT 1了。

# 12.5 多使用COMMIT
# 只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。

# COMMIT 所释放的资源：

# 回滚段上用于恢复数据的信息

# 被程序语句获得的锁

# redo / undo log buffer 中的空间

# 管理上述 3 种资源中的内部花费

# 13. 淘宝数据库，主键如何设计的？

# 13.1 自增ID的问题

# 自增ID做主键，简单易懂，几乎所有数据库都支持自增类型，只是实现上各自有所不同而已。
# 自增ID除了简单，其他都是缺点，总体来看存在以下几方面的问题：

# 1. 可靠性不高
# 存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。

# 2. 安全性不高
# 对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/这样的接口，
# 可以非常容易猜测用户ID的值为多少，总用户数量有多少，也可以非常容易地通过接口进行数据的爬取。

# 3. 性能差
# 自增ID的性能较差，需要在数据库服务器端生成。

# 4. 交互多
# 业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，
# 这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。

# 5. 局部唯一性
# 最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，
# 在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。

# 13.2 业务字段做主键

# 建议尽量不要用跟业务有关的字段做主键。毕竟，作为项目设计的技术人员，
# 我们谁也无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，
# 或者重用之类的情况出现。

# 经验：
# 刚开始使用 MySQL 时，很多人都很容易犯的错误是喜欢用业务字段做主键，想当然地认为了解业务需求，
# 但实际情况往往出乎意料，而更改主键设置的成本非常高。

# 13.3 淘宝的主键设计

# 大胆猜测，淘宝的订单ID设计应该是：
# 订单ID = 时间 + 去重字段 + 用户ID后6位尾号
# 这样的设计能做到全局唯一，且对分布式系统查询及其友好。

# 13.4 推荐的主键设计

# 非核心业务 ：对应表的主键自增ID，如告警、日志、监控等信息。
# 核心业务 ：主键设计至少应该是全局唯一且是单调递增。
# 全局唯一保证在各系统之间都是唯一的，单调递增是希望插入时不影响数据库性能。

# 这里推荐最简单的一种主键设计：UUID。

# UUID的特点：
# 全局唯一，占用36字节，数据无序，插入性能差。

# MySQL数据库的UUID组成如下所示：
# UUID = 时间+UUID版本（16字节）- 时钟序列（4字节）- MAC地址（12字节）

SELECT UUID() FROM dual;

# 改造UUID
# 若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。
# MySQL 8.0可以更换时间低位和时间高位的存储方式，这样UUID就是有序的UUID了。

# MySQL 8.0还解决了UUID存在的空间占用的问题，除去了UUID字符串中无意义的"-"字符串，
# 并且将字符串用二进制类型保存，这样存储空间降低为了16字节。

# 可以通过MySQL8.0提供的uuid_to_bin函数实现上述功能，同样的，
# MySQL也提供了bin_to_uuid函数进行转化：

SET @uuid = UUID();

SELECT @uuid,UUID_TO_BIN(@uuid),UUID_TO_BIN(@uuid,TRUE);

# 通过函数uuid_to_bin(@uuid,true)将UUID转化为有序UUID了。全局唯一 + 单调递增，
# 这不就是我们想要的主键！

# 4、有序UUID性能测试
# 在当今的互联网环境中，非常不推荐自增ID作为主键的数据库设计。
# 更推荐类似有序UUID的全局唯一的实现。

# 另外在真实的业务系统中，主键还可以加入业务和系统属性，如用户的尾号，机房的信息等。
# 这样的主键设计就更为考验架构师的水平了。

# 如果不是MySQL8.0 肿么办？

# 手动赋值字段做主键！

# 比如，设计各个分店的会员表的主键，因为如果每台机器各自产生的数据需要合并，
# 就可能会出现主键重复的问题。

# 可以在总部 MySQL 数据库中，有一个管理信息表，在这个表中添加一个字段，
# 专门用来记录当前会员编号的最大值。

# 门店在添加会员的时候，先到总部 MySQL 数据库中获取这个最大值，在这个基础上加 1，
# 然后用这个值作为新会员的“id”，同时，更新总部 MySQL 数据库管理信息表中的当 前会员编号的最大值。

# 这样一来，各个门店添加会员的时候，都对同一个总部 MySQL 数据库中的数据表字段进 行操作，
# 就解决了各门店添加会员时会员编号冲突的问题。
2025 年 1 月
一	二	三	四	五	六	日
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31
发送评论 编辑评论

推荐文章

发送评论编辑评论
