MySQL索引优化与查询优化
一：优化方式及数据准备
1：优化方式
在日常开发中都有哪些维度可以进行数据库调优？如下：
①：索引失效、没有充分利用到索引 -- 需要合理建立索引和利用索引
②：关联查询太多JOIN(设计缺陷或不得已的需求) -- 需要对SQL优化
③：服务器调优及各个参数设置(缓冲、线程数等) -- 需要调整my.cnf
④：数据过多 -- 需要分库分表
关于数据库调优的知识点非常分散。不同的DBMS、不同的公司、不同的职位，不同的项目遇到的问题都不尽相同。
对此我分为三个章节进行细致讲解：
第一章：索引优化与查询
第二章：
第三章：
虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。
①：物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
②：逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。
2：数据准备
-- 创建数据库并使用数据库 CREATE DATABASE IF NOT EXISTS demo_index_school; USE demo_index_school; -- 创建表 -- 班级表 CREATE TABLE IF NOT EXISTS class ( id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '班级ID', className VARCHAR(30) DEFAULT NULL COMMENT '班级名称', address VARCHAR(40) DEFAULT NULL COMMENT '位置', monitor INT NULL COMMENT '班长' ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; -- 学生表 CREATE TABLE IF NOT EXISTS student ( id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID', stuno INT NOT NULL COMMENT '学生编号', name VARCHAR(20) DEFAULT NULL COMMENT '学生姓名', age INT(3) DEFAULT NULL COMMENT '学生年龄', classId INT(11) DEFAULT NULL COMMENT '学生班级' -- CONSTRAINT fk_class_id FOREIGN KEY (classId) REFERENCES class (id) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; -- 创建函数（最好Navicat下执行，若报错DETERMINISTIC看下面） -- 该函数会返回一个指定长度的字符串（若删除这个函数执行：DROP FUNCTION rand_string;） 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 -- 该函数用于随机产生多少到多少的编号（若删除这个函数执行：DROP FUNCTION rand_num;） 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 -- 创建存储过程（最好Navicat下执行，若报错DETERMINISTIC看下面） -- 创建往stu表中插入数据的存储过程（若删除这个存储过程执行：DROP PROCEDURE insert_stu;） 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 -- 创建往class表中插入数据的存储过程（若删除这个存储过程执行：DROP PROCEDURE insert_stu;） 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 -- 调用存储过程插入数据 CALL insert_class(10000); CALL insert_stu(100000,500000); -- 补充存储过程（删除指定表上的索引） 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 执行存储过程：CALL proc_drop_index("dbname","tablename"); 注：若创建函数时报错 This function has none of DETERMINISTIC...... 由于开启过慢查询日志bin-log, 我们就必须为我们的function指定一个参数。 查看mysql是否允许创建函数： SHOW VARIABLES LIKE 'log_bin_trust_function_creators'; 命令开启：允许创建函数设置（不加global只是当前窗口有效）： SET GLOBAL log_bin_trust_function_creators=1; mysqld重启，上述参数又会消失。永久方法： 在Windows下：my.ini[mysqld]加上：log_bin_trust_function_creators=1 在Linux下：/etc/my.cnf下my.cnf[mysqld]加上：log_bin_trust_function_creators=1
二：索引失效问题
MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响：
①：使用索引可以快速地定位表中的某条记录从而提高数据库查询的速度,提高数据库的性能。
②：如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。
多数情况下都(默认)采用B+树构建索引。空间列类型的索引使用R-树，MEMORY表还支持hash索引。但用不用索引，最终都是优化器说了算。优化器是基于cost开销(CostBaseOptimizer)，cost就是我们使用 EXPLAIN FORMAT=JSON... 格式分析的执行计划里的cost_info属性，它不是基于规则(Rule-Basedoptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。
说明：在SQL5.7或之前版本需要加上SQL_NO_CACHE用说明不走缓存查询；而高版本查询携带这个会报警告： 'SQL_NO_CACHE' is deprecated and will be removed in a future release. MySQL5.7及之前版本查询方式：EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30; 说明：(查询索引语句) SHOW INDEX FROM student; Ⅰ：自动选择联合索引的全值匹配速度快 -- 如以下SQL语句 EXPLAIN SELECT * FROM student WHERE age=30 AND classId=4 AND name='abc'; 输出：type:ALL、key:NULL、key_len:NULL；代表走了全表扫描，没使用索引，因为压根就没这几个字段的索引 -- 创建age字段索引并查询上面的SQL ALTER TABLE student ADD INDEX idx_age(age); EXPLAIN SELECT * FROM student WHERE age=30 AND classId=4 AND name='abc'; 输出：type:ref、key:idx_age、key_len:5；说明当前查询走了idx_age的单列索引，其它字段则没走 -- 创建age字段和classId两个字段的组合索引并执行上面的SQL ALTER TABLE student ADD INDEX idx_age_classid(age,classId); EXPLAIN SELECT * FROM student WHERE age=30 AND classId=4 AND name='abc'; 输出：type:ref、key:idx_age_classid、key_len:10；说明当前查询走了idx_age_classid组合索引，它没选择idx_age 的单列索引，其实想想，若一个age和一个classid都有各自索引，那么查询也会进行索引覆盖，把这2个索引合并再查询 -- 创建age字段和classId和name三个字段的组合索引 ALTER TABLE student ADD INDEX idx_age_classid_name(age,classId,name); EXPLAIN SELECT * FROM student WHERE age=30 AND classId=4 AND name='abc'; 输出：type:ref、key:idx_age_classid_name、key_len:93；说明当前查询走了idx_age_classid_name组合索引， 其实key_len:93是因为age和classId类型一样都是int四个字节，可以为空则需要再加1字节，那就是说这2个字段 加起来就10了；因为在utf8mb4下一个字符展4字节，那就是说name varchar(20)代表80，其中在这里会腾出2个 字节用来记录变长和1个字节记录可以为空，全部加起来则5+5+83=93 说明：在字段中有组合索引则会先使用组合索引（前提遵循最左前缀原则和字段匹配等），否则使用单列的索引； Ⅱ：最左前缀原则 在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。 -- 如以下SQL语句 EXPLAIN SELECT * FROM student WHERE age=30 AND classId=4 AND name='abc'; 输出：type:ref、key:idx_age_classid_name、key_len:93；说明当前查询走了idx_age_classid_name组合索引， -- 若是如下SQL语句 EXPLAIN SELECT * FROM student WHERE classId=4 AND name='abc'; 输出：type:ALL、key:NULL、key_len:NULL；代表走了全表扫描，没使用上索引 -- 执行如下SQL语句 EXPLAIN SELECT * FROM student WHERE age=30 AND name='abc'; 输出：type:ref、key:idx_age、key_len:5；代表走了idx_age索引，但是只利用上组合索引的第一个字段 删除idx_age组合索引再次查询：DROP INDEX idx_age ON student; 输出：type:ref、key:idx_age_classid_name、key_len:5；说明当前查询走了这个索引的第一个age字段，key_len:5 结论:MySQL可以为多个字段创建索引，-个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序， 依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时， 多列(或联合)索引不会被使用。因为索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。 比如：有个组合索引（a,b,c） 查询(a),(ab),(abc)都可以命中索引；但是(a,c)只会命中a这个字段索引，而c失效；若使用(bc),(c)则直接索引失效；记住的 是查询字段的顺序可以随意，因为MySQL会自动调整字段顺序 Ⅲ：计算、函数或类型转换（自动或手动）都会导致索引失效 -- 调用删除全部索引并创建2个索引分别为age字段和name字段 CALL proc_drop_index('demo_index_school','student'); ALTER TABLE student ADD INDEX idx_age(age); ALTER TABLE student ADD INDEX idx_name(name); -- 函数转换会导致索引失效 EXPLAIN SELECT * FROM student WHERE LEFT(name,3) = 'abc' LIMIT 3; 输出：type:ALL、key:NULL、key_len:NULL；代表未利用上索引，因为查询时需要把每个name的值进行LEFT函数计算，取出前 三个字母和后面的比较，这显然用了函数处理 -- 计算处理会导致索引失效 EXPLAIN SELECT * FROM student WHERE age+1 = 3 LIMIT 5; 输出：type:ALL、key:NULL、key_len:NULL；代表未利用上索引，因为查询age为2年龄的，并且查询时age+1=3，那么这个age 进行了进算了 -- 类型转换导致索引失效 EXPLAIN SELECT * FROM student WHERE name = 22222222 LIMIT 5; EXPLAIN SELECT * FROM student WHERE age = "3" LIMIT 5; 输出：type:ALL、key:NULL、key_len:NULL；代表未利用上索引，因为在查询字符串类型的name时我直接使用了数值，但是数值 类型我们用字符串如 age = "3" 是可以利用上索引，因为在优化器阶段直接转换为了数值 Ⅳ：范围条件右侧的列失效 -- 调用删除全部索引并创建1个索引为age、classId、name组合字段 CALL proc_drop_index('demo_index_school','student'); ALTER TABLE student ADD INDEX idx_age_classid_name(age,classId,name); -- 执行如下SQL EXPLAIN SELECT * FROM student WHERE age=30 AND classId > 4 AND name='abc'; 输出：type:range、key:idx_age_classid_name、key_len:10；为什么是key_len:10,因为它只用到组合索引的前两个字段， 而从范围查询的字段后面则不在使用索引；所有范围列的后面不能使用比如<、<=、>、>=、between等 -- 优化可以使用到组合索引 -- 我们优化索引就是创建一个索引，我们把组合索引里要查询的范围列放在最后一个，这样前面的等值匹配，后面的才范围查询 ALTER TABLE student ADD INDEX idx_age_name_classid(age,name,classId); -- 执行之前的SQL EXPLAIN SELECT * FROM student WHERE age=30 AND classId > 4 AND name='abc'; 输出：type:range、key:idx_age_name_classid、key_len:93；完全利用上索引 为什么范围后索引会失效？ 举个例子（组合索引的构建会先根据b字段排序，然后b一样的，再对b里面的c排序，c一样的再对里面的d排序）： 1条：(b=1,c=4,d = 10) 2条：(b=2,c=5,d = 6) 3条：(b=2,c=5,d = 7) 4条：(b=3,c=1,d = 2) 5条：(b=3,c=5,d = 1) 如：找出 b=2 AND c=5 AND d=6 这时MySQL可以直接定位到第二条 如：找出 b=2 AND c=5 AND d>5 这时MySQL可以直接定位到第二条和第三条 如：找出 b>1 AND c=5 AND d=6 先查出 b>1 可以查询到2，3，4，5条记录，过滤出（2，2，3，3） 此时后面就索引失效了。遍历一次结果找到四条数据 这时会发现再往后查询就不是按照顺序的了，c的列是（5，5，1，5），这我查询c=5该怎么查询，只能全部遍历，因为 我不确定后面的值是大是小，如上面（5，5，1，5）里面就包含个1；这就是为什么使用范围查询后，后面索引失效。 总结：我们一般再金额查询，日期查询往往都是范围查询，在创建索引时应该把这类字段定义在组合索引的最后一列 Ⅴ：查询时使用不等于（!= 或 <>）会导致索引失效 -- 调用删除全部索引并创建2个索引分别为age字段和name字段 CALL proc_drop_index('demo_index_school','student'); ALTER TABLE student ADD INDEX idx_name(name); -- 查询SQL EXPLAIN SELECT * FROM student WHERE name <> 'abc'; 输出：type:ALL、key:NULL、key_len:NULL；代表走了全表扫描，没使用索引； 说明：利用主键进行不等于操作则会使用索引，并且range方式执行SQL,因为这种方式不用回表；反过来说为什么其它索引则走全表扫 描，是因为优化器发现全表扫描比走索引效率更高，因为走索引还得对每个数据进行回表，因此就放弃了走索引。就是说,在数据量 大的情况下，Mysql发现通过索引扫描的行记录数超过全表的10%-30%时,优化器可能会放弃走索引；反之数据量小的情况下走索引 Ⅵ：IS NULL可以使用索引，IS NOT NULL无法使用索引 EXPLAIN SELECT * FROM student WHERE name IS NULL; 输出：type:ref、key:idx_name、key_len:83；走索引； EXPLAIN SELECT * FROM student WHERE name IS NOT NULL; 输出：type:ALL、key:NULL、key_len:NULL；代表走了全表扫描，没使用索引； 说明：为什么IS NULL走索引而IS NOT NULL不走；主要因为数据不在一个级别上，正常情况下IS NULL的数据是少的，所以查询全部 的数据再依次回表查询具体记录行；但是IS NOT NULL不走索引是因为数据量太大了，除非空外的全是，而且还得依次回表查询具 体记录行数据，所以优化器直接放弃索引；但是若数据量很小的情况下依然会走索引 结论：最好在设计数据表的时候就将字段设置为NOT NULL约束， 比如你可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串("")。I 拓展：在查询中使用NOT LIKE也无法使用索引，导致全表扫描。 Ⅶ：LIKE以通配符%开头索引失效 在使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为“%”, 索引就不会起作用。只有“%”不在第一个位置，索引才会起作用 -- 执行索引失效的SQL语句 EXPLAIN SELECT * FROM student WHERE name LIKE '%ab'; EXPLAIN SELECT * FROM student WHERE name LIKE '%ab%'; 输出：type:ALL、key:NULL、key_len:NULL；代表走了全表扫描，没使用索引，因为开头我没法确定，所以我要遍历全表来 找每一个name里面包含"ab"的字符串； 总结：页面搜索严禁左模糊或者全模糊，如果需要请使用如ES搜索引擎来解决。 Ⅷ：使用 OR 时前后存在非索引的列，索引失效 在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件 中的列都是索引时，查询中才使用索引。因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的， 只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。 -- 执行以下失效SQL（现在只有一个主键和name字段存在索引） EXPLAIN SELECT * FROM student WHERE name LIKE 'ab%' OR classid = 3; 输出：type:ALL、key:NULL、key_len:NULL；代表走了全表扫描，没使用索引；因为就算name查询走索引，classid也不走，会 导致name虽然筛选出来了，但是我不能确定name非"ab%"的字段就不存在classid为3的 总结：若想使用到索引，要么建立一个name和classid的组合索引，要么建立一个classid的索引，查询时会自动优化为临时组合索引 Ⅸ：数据库和表的字符集统一使用utf8mb4或utf8mb3 统一使用utf8mb4(5.5.3版本以上支持)兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需 要进行转换会造成索引失效。
思考题：假设现在有一个组合索引index(a,b,c)进行查询是否用到索引
如：... WHERE a=2 AND b LIKE 'ab%' AND c=4;
如：... WHERE a=4 AND b LIKE 'a%b%c' AND c=5;
说明：上面2个都可以用到组合索引，使用到索引字段（a,b,c）;以第一个来说先查询a=2的，再查询b前缀为'ab'的，'ab'前缀都
是一样，那么也是按照字母顺序从小到大排序的，再后c字段可以直接定位位置；
三：关联查询优化
1：外连接内连接方式说明
-- 创建数据库表 create database if not exists demo_test_school charset utf8; -- 使用表 USE demo_test_school -- 创建授课科目表 CREATE TABLE `teaching` ( teid int PRIMARY KEY AUTO_INCREMENT COMMENT '授课编号', tename varchar(10) COMMENT '授课名称', temsg varchar(10) COMMENT '课程信息', fid int COMMENT '连接老师信息' )charset utf8; -- 创建辅导员老师表 create table if not exists teacher( tid int PRIMARY KEY AUTO_INCREMENT COMMENT '教师编号', tname varchar(10) COMMENT '姓名', tmsg varchar(10) COMMENT '教师信息', fid int COMMENT '连接授课表信息' )charset utf8; -- 添加课程数据 INSERT INTO teaching (tename,temsg)VALUES ('数学', '未知课程'),( '政治','未知课程'),('语文','未知课程'), ('生物', '未知课程'),( '化学','未知课程'),('地理','未知课程'), ('英语', '未知课程'),( '法语','未知课程'),('日语','未知课程'), ('俄语', '未知课程'),( '汉语','未知课程'),('JAVA','未知课程'), ('C#', '未知课程'),( 'C++','未知课程'),('PHP','未知课程'), ('Python', '未知课程'),( '汇编','未知课程'),('E语言','未知课程'), ('Ruby', '未知课程'),( 'Go','未知课程'),('德语','未知课程'); -- 添加老师数据 insert into teacher (tname,tmsg)values ('王生安', '优秀老师'),( '李鑫灏','优秀老师'),('薛佛世','优秀老师'), ('钱勤堃', '优秀老师'),( '潘恩依','优秀老师'),('陈国柏','优秀老师'), ('周卓浩', '优秀老师'),( '汤辟邦','优秀老师'),('张顺谷','优秀老师'), ('张灶冲', '优秀老师'),( '章晞旺','优秀老师'),('易江维','优秀老师'), ('饶展林', '优秀老师'),( '岳列洋','优秀老师'),('时党舒','优秀老师'), ('廉梦容', '优秀老师'),( '朱付流','优秀老师'),('陆丛枫','优秀老师'), ('萧百徽', '优秀老师'),( '田壮善','优秀老师'),('莫立恩','优秀老师'); update teaching set fid=ceil(rand()*20); update teacher set fid=ceil(rand()*20);
情况1：左外连接和右外连接（一般左连接以左表为驱动表，一般右连接为右表为驱动表） -- 没有任何索引的情况下 EXPLAIN SELECT * FROM teacher t LEFT JOIN teaching te ON t.fid = te.fid; +--+-----------+-----+..+----+...+----+--------+------------------------------------------+ |id|select_type|table|..|type|...|rows|filtered|Extra | +--+-----------+-----+..+----+...+----+--------+------------------------------------------+ | 1|SIMPLE |t |..|ALL |...| 21| 100.00|NULL | | 1|SIMPLE |te |..|ALL |...| 21| 100.00|Using where; Using join buffer (hash join)| +--+-----------+-----+..+----+...+----+--------+------------------------------------------+ 可以看出没有索引的情况下连接查询的type都是ALL；驱动表t而被驱动表是te，从上面可以看出，驱动表t（21条记录）查询出一条信 息就需要在被驱动表te查询一圈(21条记录)，找匹配的数据，这样的方式是很慢的；不过MySQL好在把被驱动表的数据进行 了缓存（Using join buffer） -- 为teaching表的fid字段添加普通索引 ALTER TABLE teaching ADD INDEX idx_fid(fid); -- 执行带索引的情况下执行 EXPLAIN SELECT * FROM teacher t LEFT JOIN teaching te ON t.fid = te.fid; +--+-----------+-----+..+----+-------------+-------+-------+----------------------+----+--------+-----+ |id|select_type|table|..|type|possible_keys|key |key_len|ref |rows|filtered|Extra| +--+-----------+-----+..+----+-------------+-------+-------+----------------------+----+--------+-----+ | 1|SIMPLE |t |..|ALL |NULL |NULL |NULL |NULL | 21| 100.00|NULL | | 1|SIMPLE |te |..|ref |idx_fid |idx_fid|5 |demo_test_school.t.fid| 1| 100.00|NULL | +--+-----------+-----+..+----+-------------+-------+-------+----------------------+----+--------+-----+ 可以看出此时的被驱动表te走了普通索引可以被直接定位；也就是说驱动表t依次取出一条数据后在被驱动表里只需查询一次可直接定位， （这里未计算索引里的查询时间和回表时间成本；若被驱动表数据量多的话会优化成Extra：Using index） 情况2：内连接 -- 先删除全部索引 DROP INDEX idx_fid ON teaching; -- 查询老师表和老师授课表进行内关联 EXPLAIN SELECT * FROM teacher t INNER JOIN teaching te ON t.fid = te.fid; +--+-----------+-----+..+----+...+------+----------+--------------------------------------------+ |id|select_type|table|..|type|...| rows | filtered | Extra | +--+-----------+-----+..+----+...+------+----------+--------------------------------------------+ | 1|SIMPLE |t |..|ALL |...| 6 | 100.00 | NULL | | 1|SIMPLE |te |..|ALL |...| 6 | 10.00 | Using where; Using join buffer (hash join) | +--+-----------+-----+..+----+...+------+----------+--------------------------------------------+ 可以看出没有索引的情况下连接查询的type都是ALL；驱动表t而被驱动表是te，虽然是内连接，但是优化器还是以t为驱动表，te为被 驱动表，因为它们两个都差不多，数据差不多也都没有索引；所以谁为驱动表，谁为被驱动表都差不多 -- 为老师授课表的fid（连接老师表的fid）添加普通索引；再执行上面SQL ALTER TABLE teaching ADD INDEX idx_fid(fid); EXPLAIN SELECT SQL_NO_CACHE * FROM teacher t INNER JOIN teaching te ON t.fid = te.fid; +--+-----------+-----+----------+----+..+-------+-------+----------------------+----+--------+-----------+ |id|select_type|table|partitions|type|..|key |key_len|ref |rows|filtered|Extra | +--+-----------+-----+----------+----+..+-------+-------+----------------------+----+--------+-----------+ | 1|SIMPLE |t |NULL |ALL |..|NULL |NULL |NULL | 21| 100.00|Using where| | 1|SIMPLE |te |NULL |ref |..|idx_fid|5 |demo_test_school.t.fid| 1| 100.00|NULL | +--+-----------+-----+----------+----+..+-------+-------+----------------------+----+--------+-----------+ 这句没啥特殊的，被驱动表使用上了索引，所以被驱动表查询数据可以直接定位数据 -- 为老师表的fid添加普通所以（这时候两个表的fid都是有索引了） ALTER TABLE teacher ADD INDEX idx_fid(fid); EXPLAIN SELECT SQL_NO_CACHE * FROM teacher t INNER JOIN teaching te ON t.fid = te.fid; +--+-----------+-----+----------+----+.+-------+-------+-----------------------+----+--------+-----------+ |id|select_type|table|partitions|type|.|key |key_len|ref |rows|filtered|Extra | +--+-----------+-----+----------+----+.+-------+-------+-----------------------+----+--------+-----------+ | 1|SIMPLE |te |NULL |ALL |.|NULL |NULL |NULL | 21| 100.00|Using where| | 1|SIMPLE |t |NULL |ref |.|inx_fid|5 |demo_test_school.te.fid| 1| 100.00|NULL | +--+-----------+-----+----------+----+.+-------+-------+-----------------------+----+--------+-----------+ 发现与上面不同，驱动表和被驱动表可能会调换位置；所以对于内连接来说，查询优化器可任意决定谁作为驱动表，谁作为被驱动表的 -- 现在删除被驱动表的索引teacher的索引(优化器对驱动表的选择是要么有索引，要么被驱动表数据少) DROP INDEX idx_fid ON teacher; EXPLAIN SELECT SQL_NO_CACHE * FROM teacher t INNER JOIN teaching te ON t.fid = te.fid; +--+-----------+-----+----------+----+..+-------+-------+----------------------+----+--------+-----------+ |id|select_type|table|partitions|type|..|key |key_len|ref |rows|filtered|Extra | +--+-----------+-----+----------+----+..+-------+-------+----------------------+----+--------+-----------+ | 1|SIMPLE |t |NULL |ALL |..|NULL |NULL |NULL | 21| 100.00|Using where| | 1|SIMPLE |te |NULL |ref |..|idx_fid|5 |demo_test_school.t.fid| 1| 100.00|NULL | +--+-----------+-----+----------+----+..+-------+-------+----------------------+----+--------+-----------+ 总结：对于内连接来说，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现 -- 添加索引teacher（这时候两个表的fid都存在索引了）；再为teacher表里面加几十条数据 ALTER TABLE teacher ADD INDEX idx_fid(fid); EXPLAIN SELECT * FROM teacher t INNER JOIN teaching te ON t.fid = te.fid; +--+-----------+-----+..+----+.+-------+-------+-----------------------+----+--------+-----------+ |id|select_type|table|..|type|.|key |key_len|ref |rows|filtered|Extra | +--+-----------+-----+..+----+.+-------+-------+-----------------------+----+--------+-----------+ | 1|SIMPLE |te |..|ALL |.|NULL |NULL |NULL | 21| 100.00|Using where| | 1|SIMPLE |t |..|ref |.|idx_fid|5 |demo_test_school.te.fid| 4| 100.00|NULL | +--+-----------+-----+..+----+.+-------+-------+-----------------------+----+--------+-----------+ 总结：对于内连接来说，两张表连接条件都存在索引的情况下，会选择小表（数据少）作为驱动表，俗称”小表驱动大表“
2：JOIN语句原理
虽然前面简短说了关联查询得怎么写，但是真正的重点是MySQL采用了什么样的算法来进行表与表之间的连接，了解了这个之后，才能明白为什么有的连接查询快，有的却很慢。
Ⅱ：简单嵌套循环连接(Simple Nested-Loop Join) 对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录 条数。对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左(外)连接的驱动表就是左边的那个表， 右(外)连接的驱动表就是右边的那个表。 步骤1：选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。 步骤2：对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。 如果有3个表进行连接的话，那么步骤2中得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，重复上面过程，也就是步 骤2中得到的结果集中的每一条记录都需要到t3表中找有没有匹配的记录，用伪代码表示一下这个过程就是这样: for each row in t1 { -- 比处表示遍历满足对t1单表查询结果集中的每一条记录 for each row in t2 { -- 比处表示对 于某条t1表的记录来说，遍历满足对t2单表查询结果集中的每一条记录! for each row in t3 {..} -- #此处表示对于某条t1和t2表的记录组合来说，对t3表进行单表查询 } } 这个过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的 结果集中的记录条数的连接执行方式称之为嵌套循环连接(Nested-Loop Join)，这是最简单，也是最笨拙的一种连接查询算法。 比如现在2张表连接，a表被当为驱动表有2万条，b表被当为被驱动表1万条，那么嵌套连接次数可达到2亿次；所以MySQL也不会傻傻 的使用这种笨拙的方式，具体如下几种方式； 补充：简单嵌套循环连接就是在驱动表取出一条数据，然会去被驱动表遍历一遍（要把所有被驱动表数据加载到内存,一次加载不完就多 几次IO操作，查询完以后释放内存，这时候驱动表的第一条数据连接就完成了，第二轮还是一样和上面的操作）
Ⅲ：索引嵌套循环连接（Index Nested-Loop Join） 嵌套循环连接的步骤2中可能需要访问多次被驱动表，如果访问被驱动表的方式都是全表扫描的话，那得多慢呀，还得IO读取被驱动表数据， 查询t2表(被驱动表)其实就相当于一次单表扫描，我们可以利用索引来加快查询速度。所以要求被驱动表上必须有索引才行。通过外层表 匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。 驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故MySQL优化器都倾向于使用记录数少的表作为驱 动表(外表)。所以被驱动表加索引，效率是非常高的，但如果索引不是主键索引，还得进行一次回表查询。相比被驱动表的索引是主键索 引，效率会更高。
Ⅳ：块嵌套循环连接（Block Nested-Loop Join） 如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的 记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存,然后再从驱动表中加载一条记录，然后把被驱动表的记 录再加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的10次数，就出现了Block Nested-Loop Join的方式。 不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小受join buffer 的限制)缓存到join buffer中,然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存 中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。 注意:这里缓存的不只是关联表的列，select 后面的列也会缓存起来。在一个有N个join关联的SQL中会分配N-1个join buffer。 所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。 参数设置： 查看是否开启了块嵌套循环： SHOW VARIABLES LIKE '%optimizer_switch%'; 查看block_nested_loop属性，默认是on 开启状态（不要关闭哟，若是简单嵌套循环连接效率特别慢） 还有就是驱动表不可能一次性把驱动表数据都加入的，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256k SHOW VARIABLES LIKE '%join_buffer_size%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | join_buffer_size | 262144 | +------------------+--------+ join_buffer_size的最大值在32位系统可以申请4G，而在64位操做系统下可以申请大于4G的Join_Buffer空间(64 位Windows除外, 若超过4G则会被截断为4GB并发出警告)。
总结： ①：整体效率比较：索引嵌套循环连接 > 块嵌套循环连接 > 简单嵌套循环连接 ②：之前介绍的，两个表连接条件（如连接字段都有索引）一样的情况下"小表驱动大表"是不太正确的；应该是小结果集驱动大结果集 其本质就是减少外层循环的数据数量，因为来回的加载被驱动表数据会造成IO操作耗费性能 小的度量单位指的是表行数*每行大小（因为被驱动表的字段在SELECT查询里面也会占用块空间的join buffer） 如下（straight_join防止优化器优化连接顺序）： select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100; #推荐 -- t1为驱动表，在加载join buffer时只加载t1.b字段 select t1.b, t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100; #不推荐 -- t2为驱动表，在加载join buffer时只加载t2.*（不推荐，因为字段加到join buffer，代表加入条数变少） ③：为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数) ④：增大join buffer size的大小(一次缓存的数据越多，那么内层包的扫表次数就越少) ⑤：减少驱动表不必要的字段查询（字段越少, join buffer所缓存的每行数据就越多，而加载到join buffer条数就越少）
⑥：需要保证被驱动表JOIN字段已经创建了索引，并且连接的两张表的字段类型必须一致
⑦：LEFT JOIN时，我们尽量选择小表作为驱动表，大表作为被驱动表。减少外层循环的次数
⑧：INNER JOIN时，MySQL会自动将小结果集的表当作驱动表，选择相信MySQL优化策略
⑨：能够直接多表关联的查询的，就不要使用子查询（减少查询趟数）
⑩：不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或者使用JOIN来代替子查询
注意：从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join
四：子查询优化
MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。
子查询是MySQL的一项重要的功能，可以帮助我们通过一个SQL语句实现比较复杂的查询。但是子查询的执行效率不高。原因:
①：执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
②：子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
③：对于返回结果集比较大的子查询，其对查询性能的影响也就越大。在MySQL中，可以使用连接(JOIN) 查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。
-- 创建数据库表 create database if not exists demo_sub_school charset utf8; -- 使用表 USE demo_sub_school -- 班级表 create table if not exists class ( cid int(11) PRIMARY KEY AUTO_INCREMENT COMMENT '班级编号编号', cname varchar(10) COMMENT '班级名称', cmonitor int(11) COMMENT '班级班长' )charset utf8; -- 创建学生表 create table if not exists student ( sid int(11) PRIMARY KEY AUTO_INCREMENT COMMENT '编号', sname varchar(5) not null COMMENT '姓名', ssex enum('男','女') default '男' COMMENT '性别', sage tinyint(11) unsigned not null default 20 COMMENT '年龄', saddress varchar(10) COMMENT '住址', senrol date default '2020-12-12' COMMENT '入学时间' )charset utf8; -- 添加课程数据 INSERT INTO class (cname,cmonitor)VALUES ('Java班', 1),('C#班', 4),('PHP班', 3),('GO班', null),('Python班', null),('汇编班', 5); -- 添加学生数据 insert into student(sid,sname,saddress)values (1 ,"王生安","安徽六安"),(2 ,"李鑫灏","安徽合肥"),(3 ,"薛佛世","安徽蚌埠"),(4 ,"蔡壮保","安徽安庆"), (5 ,"钱勤堃","安徽合肥"),(6 ,"潘恩依","安徽合肥"),(7 ,"陈国柏","安徽六安"),(8 ,"魏皑虎","安徽六安"), (9 ,"周卓浩","安徽六安"),(10,"汤辟邦","安徽六安"),(11,"张顺谷","安徽淮南"),(12,"张悌斯","安徽淮南"), (13,"张灶冲","安徽蚌埠"),(14,"章晞旺","安徽淮南"),(15,"易江维","安徽合肥"),(16,"孙生笙","安徽蚌埠"), (17,"饶展林","安徽蚌埠"),(18,"岳列洋","安徽合肥"),(19,"时党舒","安徽六安"),(20,"周迟蒲",null), (21,"廉梦容","安徽合肥"),(22,"朱付流","安徽蚌埠"),(23,"陆丛枫","安徽淮南"),(24,"吴 邪","安徽六安"), (25,"萧百徽","安徽六安"),(26,"田壮善","安徽六安"),(27,"莫立恩","安徽六安"),(28,"戚渊苏","安徽六安"), (29,"邱靖祈","安徽合肥"),(30,"刘鲜发","安徽六安"),(31,"郭磊留","安徽合肥"),(32,"张 磊","安徽淮南"), (33,"鲁贯栾","安徽蚌埠"),(34,"张 杰","安徽合肥"),(35,"姬行轮","安徽合肥"),(36,"严愚贵","安徽蚌埠"), (37,"龚银桓","安徽蚌埠"),(38,"盛丹革","安徽合肥"),(39,"张蓝彪","安徽六安"),(40,"成品彤","安徽六安"), (41,"方昂庄","安徽合肥"),(42,"区胄法","安徽蚌埠"),(43,"丘耿徽","安徽淮南"),(44,"张城庸","安徽六安"), (45,"陈浩森","安徽六安"),(46,"丘枝皆","安徽六安"),(47,"秦正莱","安徽六安"),(48,"张桥共",null), (49,"莫征意","安徽合肥"),(50,"张棉党","安徽合肥"),(51,"葛星申","安徽六安"),(52,"张 婷","安徽六安"), (53,"王 珊","安徽合肥"),(54,"王觉飘","安徽合肥"),(55,"魏爱來","安徽蚌埠"),(56,"池樵霆","安徽淮南"), (57,"李澜才","安徽六安"),(58,"孙 熹","安徽六安"),(59,"黎宝安","安徽六安"),(60,"夏两吟","安徽六安"), (61,"易 鲁","安徽六安"),(62,"路党拓","安徽合肥"),(63,"魏沁霭","安徽合肥"),(64,"鲍大莉","安徽六安"); -- 处理数据 update student set ssex=ceil(rand()*2),sage=ceil(rand()*5+20), senrol=concat(ceil(rand()*3+2017),'-' , ceil(rand()*12) , '-',ceil(rand()*20));
例1： 查询学生表中班长的学生详细 -- 使用子查询方式（尽量不要这么写） SELECT * FROM student WHERE sid IN (SELECT cmonitor FROM class WHERE cmonitor IS NOT NULL); -- 使用连接查询的方式（推荐） SELECT * FROM class c LEFT JOIN student s ON s.sid=c.cmonitor WHERE c.cmonitor IS NOT NULL; 例2： 取所有不为班长的信息 -- 使用子查询方式（尽量不要这么写） SELECT * FROM student WHERE sid NOT IN (SELECT cmonitor FROM class WHERE cmonitor IS NOT NULL); -- 使用连接查询的方式（推荐） SELECT * FROM class c RIGHT JOIN student s ON s.sid=c.cmonitor WHERE c.cmonitor IS NULL; 结论:尽量不要使用NOT IN或者NOT EXISTS，用 LEFT JOIN Xxx ON xx WHERE Xx IS NULL替代
五：ORDER BY排序优化
1：优化方式
在WHERE条件字段上加索引可以理解，但是为什么在ORDER BY字段上还要加索引呢？其实在MySQL中，支持两种排序方式，分别是FileSort和Index排序。其中Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。而使用EXPLAIN分析的Extra字段存在Using filesort代表排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大,会产生临时文件I/0到磁盘进行排序的情况，效率较低。所以优化如下：
①：SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。但某些情况下会全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
②：尽量使用Index完成ORDER BY排序。若WHERE和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引。
③：无法使用Index时，需要对FileSort方式进行调优。
数据准备及处理：使用文章开头的demo_index_school数据库 删除这表里面的全部索引： CALL proc_drop_index("demo_index_school","student"); CALL proc_drop_index("demo_index_school","class"); Ⅰ：以下SQL使用了Using Filesort -- 第一条SQL对age和classid字段排序；第二条SQL是针对上一条SQL查询前10行数据 EXPLAIN SELECT * FROM student ORDER BY age,classid; EXPLAIN SELECT * FROM student ORDER BY age,classid LIMIT 10; +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+--------------+ |id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra | +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+--------------+ | 1|SIMPLE |student|NULL |ALL |NULL |NULL|NULL |NULL|499084| 100.00|Using filesort| +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+--------------+ 上面2条SQL都未走任何索引，并且排序使用到了Using filesort，基于内存里排序；假设有索引的话，索引数据是排好序的，直接取。 Ⅱ：ORDER BY时不加LIMIT可能会导致索引失效 -- 添加一个联合索引 ALTER TABLE student ADD INDEX idx_age_classid_name(age,classid,name); -- 查询SQL并排序 EXPLAIN SELECT * FROM student ORDER BY age,classid; +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+--------------+ |id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra | +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+--------------+ | 1|SIMPLE |student|NULL |ALL |NULL |NULL|NULL |NULL|499084| 100.00|Using filesort| +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+--------------+ 会发现有索引也没用上，那时因为虽然有组合索引，但是优化器没用索引；因为这个索引是二级索引，虽然数据都排好序了，但是获取的 每一条二级索引数据还得回表查询主键索引才能拿到具体数据；所以优化器干脆直接把表数据加载到内存自己排序，但注意的是，数据量 大是时候是放弃了走索引，但是数据量小的话还是会走索引的，比如使用LIMIT或者使用覆盖索引 -- 第一条SQL使用覆盖索引，第二条SQL使用LIMIT EXPLAIN SELECT age,classid FROM student ORDER BY age,classid; +----+-------------+---------+.+-----+..+--------------------+-------+----+------+--------+-----------+ | id | select_type | table |.|type |..|key |key_len|ref |rows |filtered|Extra | +----+-------------+---------+.+-----+..+--------------------+-------+----+------+--------+-----------+ | 1 | SIMPLE | student |.|index|..|idx_age_classid_name|93 |NULL|499084| 100.00|Using index| +----+-------------+---------+.+-----+..+--------------------+-------+----+------+--------+-----------+ EXPLAIN SELECT * FROM student ORDER BY age,classid LIMIT 100; +---+-----------+-------+..+-------+..+--------------------+---------+------+------+----------+-------+ | id|select_type|table |..| type |..|key | key_len | ref | rows | filtered | Extra | +---+-----------+-------+..+-------+..+--------------------+---------+------+------+----------+-------+ | 1|SIMPLE |student|..| index |..|idx_age_classid_name| 93 | NULL | 100 | 100.00 | NULL | +---+-----------+-------+..+-------+..+--------------------+---------+------+------+----------+-------+ Ⅲ：ORDER BY排序时顺序错误也会导致索引失效 -- 添加一个联合索引 ALTER TABLE student ADD INDEX idx_age_classid_stuno(age,classid,stuno); -- 执行如下SQL排序不会导致索引失效 EXPLAIN SELECT * FROM student ORDER BY age LIMIT 100; -- 使用到 idx_age_classid_name索引 EXPLAIN SELECT * FROM student ORDER BY age,classid LIMIT 100; -- 使用到 idx_age_classid_name索引 EXPLAIN SELECT * FROM student ORDER BY age,classid,stuno LIMIT 100; -- 使用到 idx_age_classid_stuno索引 -- 执行如下SQL排序会导致索引失效 EXPLAIN SELECT * FROM student ORDER BY classid LIMIT 100; EXPLAIN SELECT * FROM student ORDER BY classid,name LIMIT 100; Ⅳ：ORDER BY时规则不一致，索引失效（顺序错，不索引；方向反(DEAC/ASC)，不索引） -- 执行如下SQL排序不会导致索引失效 EXPLAIN SELECT * FROM student ORDER BY age DESC,classid DESC LIMIT 100; 可以命中索引，因为当前SQL排序字段都是DESC，在查询时把数据反过来即可 -- 执行如下SQL排序会导致索引失效 EXPLAIN SELECT * FROM student ORDER BY age DESC ,classid ASC LIMIT 10 因为在创建单列、多列索引时都是按照ASC依次排序，这个查询一个升序一个降序就不行；若有这种情况可以使用MySQL8.0的降序索引 EXPLAIN SELECT * FROM student ORDER BY classid DESC,name DESC LIMIT 100; 用不到是因为没有遵循最左前缀原则 EXPLAIN SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 100; 排序的顺序不一样，一个ASC一个DESC无法命中，因为索引都是默认ASC的，若想有不同的排序顺序可以使用MySQL8.0降序索引 Ⅵ：无过滤，不索引 -- 下面两条SQL都可以走索引，但是排序没走索引 EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid; EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid,name; +--+-----------+-------+----------+----+..+---------------------+-------+-----+-----+--------+-----+ |id|select_type|table |partitions|type|..|key |key_len|ref |rows |filtered|Extra| +--+-----------+-------+----------+----+..+---------------------+-------+-----+-----+--------+-----+ | 1|SIMPLE |student|NULL |ref |..|idx_age_classid_stuno|5 |const|18420| 100.00|NULL | +--+-----------+-------+----------+----+..+---------------------+-------+-----+-----+--------+-----+ 可以看出索引走的是type:ref，但是用到索引长度只有key_len:5，那就是说当前组合索引只用到了WHERE的age字段，为什么排序 没用到索引，是因为已经被筛选出来一部分数据大约18420条，优化器直接在内存里排序了 EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age; +--+-----------+-------+..+----+.+----+-------+----+------+--------+---------------------------+ |id|select_type|table |..|type|.|key |key_len|ref |rows |filtered|Extra | +--+-----------+-------+..+----+.+----+-------+----+------+--------+---------------------------+ | 1|SIMPLE |student|..|ALL |.|NULL|NULL |NULL|499084| 10.00|Using where; Using filesort| +--+-----------+-------+..+----+.+----+-------+----+------+--------+---------------------------+ 没用到索引，并且查询数据是全表扫描Using where；排序也是在内存排序Using filesort EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age LIMIT 100; +--+-----------+-------+----------+-----+..+--------------------+-------+----+----+--------+-----------+ |id|select_type|table |partitions|type |..|key |key_len|ref |rows|filtered|Extra | +--+-----------+-------+----------+-----+..+--------------------+-------+----+----+--------+-----------+ | 1|SIMPLE |student|NULL |index|..|idx_age_classid_name|93 |NULL| 100| 10.00|Using where| +--+-----------+-------+----------+-----+..+--------------------+-------+----+----+--------+-----------+ 可以看出这个SQL语句执行完为index覆盖索引（也就是说虽然使用了索引，但是它在索引的基础上把索引遍历了一份）；同时key_len是 93，那就代表idx_age_classid_name索引上的字段都用上了，在遍历的每条索引记录时过滤出classid=45的数据；这样就完成了过滤 至于对age排序根本没必要，因为当时过滤出来的每个age字段是有序的 说明： 所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤掉大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能， 即使索引优化了排序,但实际提升性能很有限。相对的stuno<101000这个条件,如果没有用到索引的话，要对几万条的数据进行扫描，这 是非常消耗性能的，所以给这个字段加上索引性价比最高，是最优选择。 结论： 创建一个索引：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 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 -- 对于排序来说，多个相等条件也是范围查询 ①：两个索引同时存在，MySQL自动选择最优的方案。但是，随着数据量的变化，选择的索引也会随之变化的。 ②：当[范围条件]和[GROUP BY或者ORDER BY]的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序 的数据并不多时，优先把索引放在范围字段上。反之亦然。
2：filesort排序算法
排序的字段若如果不在索引列上，则filesort会有两种算法: 双路排序和单路排序 双路排序(慢)： MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，首先读取行指针和ORDER BY排序的列数据,对数据进行排序， 然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出（查询当前排序字段的每一个元素对应的具体数据行） 从磁盘取排序字段，在buffer进行排序，再从磁盘取其它字段。取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的， 所以在MySQL4.1之后，出现了第二种改进的算法，就是单路排序。 单路排序(快)： 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第 二次读取数据。并且把随机IO变成了顺序I0，但是它会使用更多的空间，因为它把每一行都保存在内存中了。 两种排序结论： ①：单路排序以空间换时间，而双路以时间换空间 ②：但是用单路有问题： 在sort_buffe中，单路比多路要多占用很多空间，因为单路是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer 的容量，导致每次只能取sort_buffer容大小的数据，进行排序(创建tmp文件，多路合并)，排完再取sort_buffer容量大小， 再排.... ,从而多次1/0。 单路本来想一次I0读取全部数据到内存操作，但数据量特别大反而导致了大量的I/0操作，反而得不偿失。 单路排序优化策略： ①：尝试提高sort_buffer_size 不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程(connection)的1M-8M之间调 整。MySQL5.7，InnoDB存储引擎默认值是1048576字节（1MB）. SHOW VARIABLES LIKE '%sort_buffer_size%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_sort_buffer_size | 1048576 | | myisam_sort_buffer_size | 8388608 | | sort_buffer_size | 262144 | +-------------------------+---------+ ②：尝试提高max_length_for_sort_data 提高这个参数，会增加用改进算法的概率。 SHOW VARIABLES LIKE '%max_length_for_sort_data%'; -- 高版本默认4096字节 +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | max_length_for_sort_data | 4096 | +--------------------------+-------+ 但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。 如果需要返回的列的总长度大于max_length_for_sort_data，使用双路算法，否则使用单路算法。1024-8192字节之间调整。 ③：Order by时select * 是一个大忌。最好只Query需要的字段。原因: 当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT或BLOB类型时， 会用改进后的算法单路排序，否则用老算法多路排序。 两种算法的数据都有可能超出sort_buffer_size的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO,但是用单路排序算 法的风险会更大一些,所以要提高sort_buffer_size。
六：GROUP BY分组优化
①：GROUP BY使用索引的原则几乎跟ORDER 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会很慢。
七：LIMIT分页优化
一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是LIMIT 20000,10；此时就需要MySQL排序前20000,10 记录，仅仅返回20001~20010的记录，虽然其它记录丢弃了，但是在分页前的查询排序的代价非常大。
如下面的一条SQL语句： EXPLAIN SELECT * FROM student LIMIT 20000,10; +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----+ |id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra| +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----+ | 1|SIMPLE |student|NULL |ALL |NULL |NULL|NULL |NULL|499084| 100.00|NULL | +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----+ 从上面的SQL语句可以分析type:ALL；代表效率是极差的，并且预估扫描的行rows:499084，就查询这10条数据，却扫描这么多数据； 优化思路一： -- 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其它内容 EXPLAIN SELECT * FROM student s,(SELECT id FROM student ORDER BY id LIMIT 20000,10) st WHERE s.id=st.id; +--+-----------+----------+...+------+-------------+-------+-------+-----+-----+--------+-----------+ |id|select_type|table |...|type |possible_keys|key |key_len|ref |rows |filtered|Extra | +--+-----------+----------+...+------+-------------+-------+-------+-----+-----+--------+-----------+ | 1|PRIMARY |<derived2>|...|ALL |NULL |NULL |NULL |NULL |20010| 100.00|NULL | | 1|PRIMARY |s |...|eq_ref|PRIMARY |PRIMARY|4 |st.id| 1| 100.00|NULL | | 2|DERIVED |student |...|index |NULL |PRIMARY|4 |NULL |20010| 100.00|Using index| +--+-----------+----------+...+------+-------------+-------+-------+-----+-----+--------+-----------+ 分析一下，其实有个表子查询，会查询到具体的id，然后再去和主表关联进行查询，表子查询的好处是在里面的表利用索引获取到主键id， 然后再获取关联查询根据ID等值匹配；（这里的表子查询用的ORDER BY id不是太形象，用普通索引就可以体现速度快了） 优化思路二： -- 适用于主键自增的表，可以把LIMIT查询转换成某个位置的查询 EXPLAIN SELECT * FROM student WHERE id > 20000 LIMIT 10; +--+-----------+-------+----------+-----+-------------+-------+-------+----+------+--------+-----------+ |id|select_type|table |partitions|type |possible_keys|key |key_len|ref |rows |filtered|Extra | +--+-----------+-------+----------+-----+-------------+-------+-------+----+------+--------+-----------+ | 1|SIMPLE |student|NULL |range|PRIMARY |PRIMARY|4 |NULL|249542| 100.00|Using where| +--+-----------+-------+----------+-----+-------------+-------+-------+----+------+--------+-----------+ 这种的得注意可能不是特别准确，因为有的id是跳跃性的，所以对要求不是太高的可以使用这种方式，效率最高
八：优先考虑覆盖索引
什么是覆盖索引： ①：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们 索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。 ②：覆盖索引是非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列(建立索引的字段正好是覆盖查 询条件中所涉及的字段)。 简单说就是，索引列+主键 包含在SELECT到FROM之间查询的索引字段和主键字段被称为覆盖索引。 案例一： -- 删除student表里的所有索引 CALL proc_drop_index('demo_index_school','student'); -- 创建组合索引age和name字段 ALTER TABLE student ADD INDEX idx_age_name(age,name); -- 非覆盖索引查询数据 EXPLAIN SELECT * FROM student WHERE age <> 20; +--+-----------+-------+----------+-----+-------------+----+-------+----+------+--------+-----------+ |id|select_type|table |partitions|type |possible_keys|key |key_len|ref |rows |filtered|Extra | +--+-----------+-------+----------+-----+-------------+----+-------+----+------+--------+-----------+ | 1|SIMPLE |student|NULL |ALL |idx_age_name |NULL|NULL |NULL|499084| 100.00|Using where| +--+-----------+-------+----------+-----+-------------+----+-------+----+------+--------+-----------+ 可以发现type:ALL，未用上索引，虽然age是组合索引第一个，但是也没用上，是因为之前有说过，反向判断是不走索引的，如<>、!=； 但是我们要知道，其实它不是不走，只是反向判断筛选出的数据量大，导致每条数据都需要回表，效率低，你可以试试主键反向判断是走 索引的； --覆盖索引查询 EXPLAIN SELECT age,name,id FROM student WHERE age <> 20; +--+-----------+-------+.+-----+.+------------+-------+----+------+--------+------------------------+ |id|select_type|table |.|type |.|key |key_len|ref |rows |filtered|Extra | +--+-----------+-------+.+-----+.+------------+-------+----+------+--------+------------------------+ | 1|SIMPLE |student|.|index|.|idx_age_name|88 |NULL|499084| 100.00|Using where; Using index| +--+-----------+-------+.+-----+.+------------+-------+----+------+--------+------------------------+ 可以发现type:index，并且走了idx_age_name索引；为什么上面不走索引，下面查询指定字段就走索引；那是因为走了覆盖索引， 查询时只查询主键字段+非聚簇索引字段，所以不用回表操作，这样查询优化器觉得走非聚簇索引反而更快 案例二： -- 非覆盖索引查询数据 EXPLAIN SELECT * FROM student WHERE name LIKE '%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|499084| 11.11|Using where| +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+ 未使用上索引，其实和案例一差不多；但是我们之前说过使用模糊查询时’%‘在条件前面，如'%abc'，就不会命中索引，其实不是不想 命中索引，而是使用非聚簇索引查询时，查询每条记录还要回表操作，效率太慢，还不如直接扫描聚簇索引查询数据 --覆盖索引查询 EXPLAIN SELECT age,name,id FROM student WHERE name LIKE '%abc'; +--+-----------+-------+.+-----+.+------------+-------+----+------+--------+------------------------+ |id|select_type|table |.|type |.|key |key_len|ref |rows |filtered|Extra | +--+-----------+-------+.+-----+.+------------+-------+----+------+--------+------------------------+ | 1|SIMPLE |student|.|index|.|idx_age_name|88 |NULL|499084| 11.11|Using where; Using index| +--+-----------+-------+.+-----+.+------------+-------+----+------+--------+------------------------+ 命中索引，被称为覆盖索引，其实案例一和案例二差不多，只不过是因为不用回表操作，查询优化器优先选择了非聚簇索引查询 覆盖索引的好处： ①：避免Innodb表进行索引的二次查询(回表)： Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数 据，在查找到相应的键值后，还需通过主键进行二次查询（携带主键值去聚簇索引里查询）才能获取我们真实所需要的数据。而在覆盖 索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。 ②：可以把随机IO变成顺序IO加快查询效率 由于非聚簇索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比从非聚簇索引表里查询到的主键值后回表随机从磁盘读取 每一行的数据IO要少的多，因此利用上覆盖索引在访问时也可以把磁盘的随机读取的I0转变成索引查找的顺序I0。由于覆盖索引可以减 少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。 覆盖索引的弊端: 索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA, 或者称为业务数据架构师的工作 总结：覆盖索引就是查询当前非聚簇索引之内的字段和主键字段从而不需要回表操作
九：索引下推
Index Condition Pushdown（ICP）是MySQL5.6中新特性，是一种在存储引擎层使用索引过滤数据的优化方式。
索引下推说明（ICP）： ①：在不使用ICP的情况下，使用非主键索引(又叫非聚簇索引)进行查询时，存储引擎通过非聚簇索引检索到数据，然后返回给MySQL服务 器，再由MySQL服务器评估WHERE后面的条件是否保留行。 ②：在使用ICP的情况下，如果部分WHERE条件可以使用非聚簇索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛 选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。 好处：ICP可以减少存储引擎必须访问聚簇索引的次数和MySQL服务器必须访问存储引擎的次数。但是，ICP的加速效果取决于在 存储引擎内通过ICP筛选掉的数据的比例。 索引下推的过程： 不使用索引下推的情况下，首先根据索引来查找记录，然后再根据where条件来过滤记录，具体查询过程： 首先，存储引擎读取符合条件的索引记录。 其次，根据索引中的主键值，定位并读取完整的行记录。 最后，存储引擎把记录交给服务层去检测该记录是否满足WHERE条件。 使用索引下推的情况下，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部 分过滤操作，具体查询过程： 首先，存储引擎读取索引记录（不是完整的行记录）。 其次，判断WHERE部分条件能否用索引中的列来做检查，条件不满足，则处理下一行索引记录。条件满足，使用索引中的主键去定位 并读取完整的行记录。 最后，存储引擎把记录交给服务层，服务层检测该记录是否满足WHERE条件的其余部分。 举例一说明： -- 创建一个普通二级索引并执行如下SQL语句 ALTER TABLE student ADD INDEX idx_name(name); EXPLAIN SELECT * FROM student WHERE name LIKE 'abc%' AND name LIKE '%___d%'; 或者使用这条语句： EXPLAIN SELECT * FROM student WHERE name LIKE 'abc%' AND name LIKE '%\_\_\_d%'; +--+-----------+-------+.+-----+-------------+--------+-------+----+----+--------+---------------------+ |id|select_type|table |.|type |possible_keys|key |key_len|ref |rows|filtered|Extra | +--+-----------+-------+.+-----+-------------+--------+-------+----+----+--------+---------------------+ | 1|SIMPLE |student|.|range|idx_name |idx_name|83 |NULL| 23| 100.00|Using index condition| +--+-----------+-------+.+-----+-------------+--------+-------+----+----+--------+---------------------+ 可以看出使用到了Using index condition索引下推；其实我们可以看出key_len：83，代表命中了第一个name LIKE 'abc%'查询条件 而name LIKE '%___d%'无法命中索引的，但是上面使用了索引下推，直接查询出第一个条件的后面紧接着过滤第二个条件后回表； 举例二说明： -- 创建一个表并插入数据 CREATE TABLE people( id INT PRIMARY KEY AUTO_INCREMENT, zipcode VARCHAR(20), firstname VARCHAR(20), lastname VARCHAR(20), address VARCHAR(50), KEY zip_last_first(zipcode,lastname,firstname) ) ENGINE=INNODB CHARSET=utf8mb4; INSERT INTO people VALUES('1','00001','三','张','北京市'), ('2','00002','四','李','南京市'),('3','00003','五','王','上海市'),('4','00004','六','赵','天津市'); -- 查询数据 EXPLAIN SELECT * FROM people WHERE zipcode='00001' AND lastname LIKE '%张%' AND address LIKE '%北京市%'; +--+-----------+------+.+----+.+--------------+-------+-----+----+.+----------------------------------+ |id|select_type|table |.|type|.|key |key_len|ref |rows|.|Extra | +--+-----------+------+.+----+.+--------------+-------+-----+----+.+----------------------------------+ | 1|SIMPLE |people|.|ref |.|zip_last_first|83 |const| 1|.|Using index condition; Using where| +--+-----------+------+.+----+.+--------------+-------+-----+----+.+----------------------------------+ 上面使用到了zip_last_first索引，但用到了这个组合索引的部分key_len:83，也就zipcode字段，而查询的第二个lastname字段 却没使用到索引，但是它利用上了Using index condition（覆盖索引）；通过这个条件过滤出部分信息后再带着主键信息回表查询 具体记录行再过滤address信息 举例三说明： EXPLAIN SELECT * FROM people WHERE zipcode='00001' AND lastname LIKE '张%' AND firstname LIKE '三%'; +--+-----------+------+.+-----+.+--------------+-------+----+----+--------+---------------------+ |id|select_type|table |.|type |.|key |key_len|ref |rows|filtered|Extra | +--+-----------+------+.+-----+.+--------------+-------+----+----+--------+---------------------+ | 1|SIMPLE |people|.|range|.|zip_last_first|249 |NULL| 1| 25.00|Using index condition| +--+-----------+------+.+-----+.+--------------+-------+----+----+--------+---------------------+ 可以发现这个也是使用的索引下推，但是索引没失效为什么是索引下推；因为索引下推的作用更多的是体现在索引失效的情况下 ICP的开启与关闭 其实默认情况下是启用索引条件下推。可以通过设置系统变量optimizer_switch控制：index_condition_pushdown -- 查询是否开启索引下推（@@SESSION当前连接）： SELECT @@GLOBAL.optimizer_switch; 或 SELECT @@SESSION.optimizer_switch; +---------------------------------------------------------------------------------+ | index_merge=on,...engine_condition_pushdown=on,index_condition_pushdown=on,... | +---------------------------------------------------------------------------------+ -- 打开索引下推（@@SESSION当前连接） SET @@GLOBAL.optimizer_switch = 'index_condition_pushdown=on'; -- 关闭索引下推（@@SESSION当前连接） SET @@GLOBAL.optimizer_switch ='index_condition_pushdown=off'; ICP的使用条件 ①：如果表访问的类型type为range、ref、eq_ref和ref_or_null可以使用ICP ②：ICP可以用于InnoDB和MyISAM表，包括分区表InnoDB和MyISAM表 ③：对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取次数从而减少I/O操作。 ④：当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP不会减少I/O。 ⑤：相关子查询的条件不能使用ICP
十：前缀索引
数据准备 -- 创建表和添加数据 CREATE TABLE IF NOT EXISTS prefix_index_test( `id` INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT "主键ID", `name` VARCHAR(3) NOT NULL COMMENT "姓名", `address` VARCHAR(30) COMMENT "地址" )CHARACTER SET utf8mb4 ENGINE INNODB; INSERT INTO prefix_index_test(`id`,`name`,`address`)VALUES (1,'腾讯','亚洲中国深圳市南山区科技园飞亚达大厦3-10楼'),(2,'阿里','亚洲中国浙江省杭州市余杭区文一西路969号'), (3,'百度','亚洲中国北京市海淀区上地十街10号百度大厦'),(4,'奇瑞','亚洲中国安徽省芜湖市鞍山路8号奇瑞汽车股份有限公司'); 创建前缀索引： MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。 -- 创建索引 ALTER TABLE prefix_index_test ADD INDEX idx_addressA(`address`); -- 这个创建全长度索引 ALTER TABLE prefix_index_test ADD INDEX idx_addressB(`address`(4)); -- 这个创建长度为4的索引 上面两个索引的区别是：全长度的索引会把当前address字段的全部信息都保存在索引里；而创建长度为7的前缀索引会把部分数据 放在索引里，而超出的则不保存。 全长度字符串索引和前缀索引执行规则： 查询语句：SELECT * FROM prefix_index_test WHERE address ='亚洲中国北京市海淀区上地十街10号百度大厦'; 》如果使用的是idx_addressA（即address整个字符串的索引结构），执行顺序是这样的： ①：从idx_addressA索引树找到满足索引值是 '亚洲中国北京市海淀区...' 的这条记录，取得主键id为3的值； ②：到主键上查到主键id值是3的行，判断address的值是正确的，将这行记录加入结果集； ③：取idx_addressA索引树上刚刚查到的位置的下一条记录，发现已经不满足 address='亚洲中国北京市海淀区...' 的条件了，循环结束。 其实这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。 》如果使用的是idx_addressB（即address(4)索引结构），执行顺序是这样的： ①：从idx_addressB索引树找到满足索引值是 '亚洲中国' 的记录，找到第一个是id为1的记录； ②：到主键上查到主键id值是1的行，判断出address的值不是 '亚洲中国北京市海淀区...' ，这行记录丢弃； ③：取idx_addressB上刚刚查到的位置的下一条记录，发现仍然是'亚洲中国'，取出主键id为2，去聚簇索引上查询主键 id为2的值，发现找出的address的值不是 '亚洲中国北京市海淀区...'，这行记录又丢弃； ④：再取idx_addressB上刚刚查到的位置的下一条记录，发现仍然是'亚洲中国'，取出主键id为3，再到主键索引上取整行然 后判断，这次值对了，将这行记录加入结果集； ⑤：重复上一步，直到在idx_addressB上取到的值不是'亚洲中国'时，循环结束。 也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。 前缀索引的创建规则： 其实我们使用字符串前缀多少来创建索引呢？上面用到的前缀是4，那么到底截取多少呢？截取得多了，达不到节省索引存储空间的目的； 截取得少了，重复内容太多，字段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢？ -- 先看一下字段在全部数据中的选择度： SELECT COUNT(DISTINCT address) / COUNT(*) FROM prefix_index_test; -- 再通过不同长度去计算，与全表的选择性对比： SELECT COUNT(DISTINCT LEFT(address,10)) / COUNT(*) AS SUB10, -- 截取前10个字符的选择度 COUNT(DISTINCT LEFT(address,15)) / COUNT(*) AS SUB11, -- 截取前15个字符的选择度 COUNT(DISTINCT LEFT(address,20)) / COUNT(*) AS SUB12, -- 截取前20个字符的选择度 COUNT(DISTINCT LEFT(address,25)) / COUNT(*) AS SUB13 -- 截取前25个字符的选择度 FROM prefix_index_test; 前缀索引对覆盖索引的影响 使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。
十一：COUNT(*)和COUNT(1)效率
我们可能好奇，在MySQL中统计数据表的行数，可以使用三种方式: SELECT COUNT(*)、 SELECT COUNT(1)和SELECT COUNT(具体字段)，使用这三者之间的查询效率是怎样的?
说明：
COUNT(*)和COUNT(1)它都是用来统计当前表里的全部数据行记录，包含某个字段为空的数据行统计；（包含NULL值的行数）
COUNT(具体字段)它是统计当前字段列下的所有非空的数据行；（若指定列存在NULL则不统计）
所有看具体是要统计所有行还是要统计非空字段的总数，COUNT(具体字段)效率最低，因为统计时还要判断是不是非空
解释一：COUNT(*)和COUNT(1)都是对所有结果进行COUNT，COUNT(*)和COUNT(1)本质上并没有区别（二者执行时间可能略有差别，不过你还
是可以把它俩的执行效率看成是相等的)。如果有WHERE子句，则是对所有符合筛选条件的数据行进行统计；如果没有WHERE子句，则是
对数据表的数据行数进行统计。
解释二：如果是MyISAM存储引擎，统计数据表的行数只需要O(1)的复杂度，这是因为每张MyISAM的数据表都有一个meta信息存储了row_count
值，而一致性则由表级锁来保证。如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，
维护一个row_count变量，因此需要采用扫描全表，它会通过遍历最小可用的二级索引来处理count(*)，若没有二级索引则会扫描聚簇
索引（因为聚簇索引是重量级的，保存的是整个表的全部数据），是O(n)的复杂度，进行循环+计数的方式来完成统计。
解释三：在InnoDB引擎中，如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含
的信息多，明显会大于二级索引(非聚簇索引)。对于COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自
动采用占用空间更小的二级索引来进行统计。如果有多个二级索引，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，
才会采用主键索引|来进行统计。
十二：其它优化策略
Ⅰ：关于SELECT(*)
其实在表查询中，建议明确字段，不要使用*作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原因如下:
①：MySQL在解析的过程中，会通过查询数据字典将 "*" 按序转换成所有列名，这会大大的耗费资源和时间。
②：无法使用覆盖索引，因为使用 "*" 就会通过回表的方式查询全部数据，而且还不是个顺序I/O读取。
Ⅱ：LIMIT 1 对优化的影响
针对的是会扫描全表的SQL语询，如果你可以确定结果集只有一条，那么加上LIMIT 1的时候，当找到一条结果的时候就不会继续扫描了，
这样会加快查询速度。如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT 1。
十三：主键如何设计
如何设计数据库主键，其实站在以前有人会说使用4字节的INT来做自增主键，若数据量大则可以使用8字节的BIGINT来作为主键；但是现在的设计使用这种方式是不可以的，因为使用这种方式的主键设计，都是站在数据库这一层，而没有从业务的角度思考主键。主键就是一个自增ID吗?
1：自增ID的问题
自增ID做主键，几乎数据库都支持自增类型，只是实现上各自有所不同而已。自增ID除了简单，其它都是缺点，总体来看存在以下几方面的问题:
①：可靠性不高：
存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。
②：安全性不高：
对外暴露的接口可以非常容易猜测对应的信息。比如: /User/1/这样的接口，可以非常容易猜测用户ID的值为多少,总用户数量有多少,也可
以非常容易地通过接口进行数据的爬取。
③：性能差：
自增ID的性能较差，需要在数据库服务器端生成。
④：交互多：
业务还需要额外执行一次类似last_insert_id()的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，
多1条SQL, 就多一次性能上的开销。
⑤：局部唯一性：
最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来
说，这简直就是噩梦。
2：不推荐的主键设计
说明：在设计一些数据库主表的时候，我们尽量不要使用业务字段来当作主键，因为刚开始使用MySQL时，很多人都很容易犯的错误是喜欢
用业务字段做主键，想当然地认为了解业务需求，但实际情况往往出乎意料，而更改主键设置的成本非常高。如以下几个不推荐的业务字段：
①：使用卡号当作主键，因为会员卡号不能为空，而且有唯一性，可以用来标识一条会员记录；但是一张卡上就有一个唯一ID，假如张三的
卡号销户了，但是那个卡是得继续使用的，虽然把卡号改为其它用户上了，但是可能会出现问题，如上一个人绑定的卡号消费记录会
携带下次查询出来，会发现另外一个人查询出来另外销户的用户的消费记录；要不就把每个关联信息都修改了，这样必然耗费时间。
②：使用会员电话或身份证号，但是也存在问题，在实际操作中，手机号也存在被运营商收回，重新发给别人用的情况；那身份证号码看似
可以，也是全国唯一的，但是得注意的是，身份证号码是敏感信息，很多人不愿意录入身份证号码的
总结：建议尽量不要用跟业务有关的字段做主键。毕竟作为项目设计的技术人员，我们谁也无法预测在项目的整个生命周期中，哪个业务字段会
因为项目的业务需求而有重复，或者重用之类的情况出现。
3：推荐的主键设计
Ⅰ：表主键创建分类： 非核心业务表： 对应表的主键可以使用自增ID，如告警、日志、监控等一些简单的信息表。 核心业务表： 主键设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的，单调递增是希望插入时不影响数据库性能。 Ⅱ：主键推荐使用UUID的特点： 从上面的业务表设计来看，推荐使用UUID作为主键（后面会进行优化），因为UUID全局唯一，占用36字节，数据无序，插入性能差。 Ⅲ：UUID的时间顺序改造： 但是从现在的UUID来看虽然是全局唯一的，但是无法满足单调递增，为了解决这一问题下面改造UUID，把时间低中高位调换； 若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。MySQL8.0可以更换时间低位和时间高位的存储方式，这样 UUID就是有序的UUID了。MySQL8.0还解决了UUID存在的空间占用的问题，除去了UUID字符串中无意义的"-"字符串，并且将字符 串用二进制类型保存，这样存储空间降低为了16字节。可以通过MySQL8.0提供的uuid_to_bin函数实现上述功能，同样的，MySQL 也提供了bin_to_uuid函数进行转化： -- 生成UUID并保存到@uuid的自定义变量中 SET @uuid = UUID(); -- 查询并转换UUID SELECT @uuid, HEX(UUID_TO_BIN(@uuid)), HEX(UUID_TO_BIN(@uuid,TRUE)); +------------------------------------+--------------------------------+--------------------------------+ |@uuid |HEX(UUID_TO_BIN(@uuid)) |HEX(uuid_to_bin(@uuid,TRUE)) | +------------------------------------+--------------------------------+--------------------------------+ |6a8651a8-c2ff-11ed-a7b0-525400296625|6A8651A8C2FF11EDA7B0525400296625|11EDC2FF6A8651A8A7B0525400296625| +------------------------------------+--------------------------------+--------------------------------+ 通过函数UUID_TO_BIN(@uuid,TRUE)将UUID转化为有序UUID了。全局唯一 + 单调递增，这不就是我们想要的主键么。 Ⅳ：UUID效率比较说明： MySQL8.0推出的16字节的有序UUID，相比之前BIGINT的8字节的自增ID和普通的UUID效率比较； 插入1亿条数据，每条数据占用500字节，含有3个二级索引，最终的结果如下所示： 时间（秒） 表大小（G） 自增ID 2712 240 UUID 3396 250 有序UUID 2624 243 可以看到插入1亿条数据有序UUID是最快的，而且在实际业务使用中有序UUID在业务端就可以生成。还可以进一步减少SQL的交互次数。 另外，虽然有序UUID相比自增ID多了8个字节，但实际只增大了3G的存储空间，还可以接受。 在当今的互联网环境中，非常不推荐自增ID作为主键的数据库设计。更推荐类似有序UUID的全局唯一的实现。 另外在真实的业务系统中，主键还可以加入业务和系统属性，如用户的尾号，机房的信息等。这样的主键设计就更为考验架构师的水平了。 Ⅴ：若不是MySQL8.0如何使用有序UUID： 手动赋值字段做主键！从服务端就生成有序的UUID主键，或者使用什么雪花算法，或者使用特定的字段信息组合生成主键 Ⅵ：基于MySQL8.0有序UUID测试： -- 创建数据库表并添加数据 CREATE TABLE IF NOT EXISTS uuid_test( id BINARY(16) PRIMARY KEY COMMENT '主键信息', msg VARCHAR(10) NOT NULL COMMENT '备注信息' )CHARSET=utf8mb4; INSERT INTO uuid_test (id,msg) VALUES(UUID_TO_BIN(UUID(),TRUE),'第一次插入'); INSERT INTO uuid_test (id,msg) VALUES(UUID_TO_BIN(UUID(),TRUE),'第二次插入'); INSERT INTO uuid_test (id,msg) VALUES(UUID_TO_BIN(UUID(),TRUE),'第三次插入'); INSERT INTO uuid_test (id,msg) VALUES(UUID_TO_BIN(UUID(),TRUE),'第四次插入'); INSERT INTO uuid_test (id,msg) VALUES(UUID_TO_BIN(UUID(),TRUE),'第五次插入'); 说明：我们知道聚簇索引是按照主键字段值大小的顺序依次排序，若是无须的UUID则插入数据后再查询不可能顺序的，但是 使用顺序的UUID则会按照顺序依次输出； -- 查询数据（因为是二进制存储，所以我转换成十六进制输出） SELECT HEX(id),msg FROM uuid_test; +----------------------------------+-----------------+ | HEX(id) | msg | +----------------------------------+-----------------+ | 11EDC30B5AC796DAA7B0525400296625 | 第一次插入 | | 11EDC30B5DE16D92A7B0525400296625 | 第二次插入 | | 11EDC30B607055CFA7B0525400296625 | 第三次插入 | | 11EDC30B63534910A7B0525400296625 | 第四次插入 | | 11EDC30B665941B4A7B0525400296625 | 第五次插入 | +----------------------------------+-----------------+
.
作者：蚂蚁小哥
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须在文章页面给出原文链接，否则保留追究法律责任的权利。
