做阿里巴巴网站找谁,电子商务以后的就业方向,功能类似淘宝的网站建设,服务器连接wordpress书接上文#xff0c;继续使用上次创建的 student 表#xff0c;表里已经被我们装填了大量数据#xff0c;接下来做分页查询的索引优化。
分页查询优化
普通的分页查询语句#xff1a;
SELECT * FROM student LIMIT 10000,10;这条语句是从 student 表中取出 10001 行开始…书接上文继续使用上次创建的 student 表表里已经被我们装填了大量数据接下来做分页查询的索引优化。
分页查询优化
普通的分页查询语句
SELECT * FROM student LIMIT 10000,10;这条语句是从 student 表中取出 10001 行开始的10条数据不过看起来只查询了十条但实际上是将 10010 条记录读取只显示后面十条数据抛弃前面的 10000 条记录所以执行效率很低。
根据自增且连续的主键排序的分页查询
上一条语句就是这样的例子表通过主键 id 进行排序看一下执行计划 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------- ------ ------- ------ ------ -------- --------1 SIMPLE student (NULL) ALL (NULL) (NULL) (NULL) (NULL) 97664 100.00 (NULL) type 类型 ALL全表扫描查询效率很低。对于这种情况可以改写一下id 连续且自增直接按照 id 进行筛选查询 10000 后五条的数据
EXPLAIN SELECT * FROM student WHERE id 10000 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) 48832 100.00 Using where 显然使用了索引扫描的行数少了很多执行效率也提高了很多。但这样的改写对于数据有很高的要求比如数据不能物理删除物理删除导致主键非连续从而使得结果不一致使用逻辑删除的情况可以采用上面的优化方法。 这种方式必须满足这两个条件
主键连续且自增结果集按照主键排序
根据非主键字段排序的分页查询
SELECT * FROM student ORDER BY NAME LIMIT 10000, 10;结果集 id name age school start_time
------ -------- ------ ------------ ---------------------19002 老18999 18999 老大小学 2024-03-29 16:03:2122 老19 19 老大小学 2024-03-29 16:01:23193 老190 190 老大小学 2024-03-29 16:01:241903 老1900 1900 老大小学 2024-03-29 16:01:3419003 老19000 19000 老大小学 2024-03-29 16:03:2119004 老19001 19001 老大小学 2024-03-29 16:03:2119005 老19002 19002 老大小学 2024-03-29 16:03:2119006 老19003 19003 老大小学 2024-03-29 16:03:2119007 老19004 19004 老大小学 2024-03-29 16:03:2119008 老19005 19005 老大小学 2024-03-29 16:03:21查询计划 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------- ------ ------- ------ ------ -------- ----------------1 SIMPLE student (NULL) ALL (NULL) (NULL) (NULL) (NULL) 97664 100.00 Using filesort Usiing filesortALL 全表扫描使用文件排序并没有使用 name 字段的索引扫描整个索引并查找到没索引的行的成本可能比全表扫描更高所以优化器放弃使用索引。优化的关键是让排序时返回的字段尽可能少可以用下面的优化
SELECT id FROM student ORDER BY NAME LIMIT 10000, 10) t WHERE student.id t.id;查询结果 id name age school start_time id
------ -------- ------ ------------ ------------------- --------19002 老18999 18999 老大小学 2024-03-29 16:03:21 1900222 老19 19 老大小学 2024-03-29 16:01:23 22193 老190 190 老大小学 2024-03-29 16:01:24 1931903 老1900 1900 老大小学 2024-03-29 16:01:34 190319003 老19000 19000 老大小学 2024-03-29 16:03:21 1900319004 老19001 19001 老大小学 2024-03-29 16:03:21 1900419005 老19002 19002 老大小学 2024-03-29 16:03:21 1900519006 老19003 19003 老大小学 2024-03-29 16:03:21 1900619007 老19004 19004 老大小学 2024-03-29 16:03:21 1900719008 老19005 19005 老大小学 2024-03-29 16:03:21 19008执行计划 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------- ---------- ------ ------------- ------------------- ------- ------ ------ -------- -------------1 PRIMARY derived2 (NULL) ALL (NULL) (NULL) (NULL) (NULL) 10010 100.00 (NULL) 1 PRIMARY student (NULL) eq_ref PRIMARY PRIMARY 4 t.id 1 100.00 (NULL) 2 DERIVED student (NULL) index (NULL) idx_name_age_school 140 (NULL) 10010 100.00 Using index 可以看到查询结果是一致的执行计划中 extra 列也从 Using filesort 变成 Using index可以对比查询时间显然看到查询效率变高了。
Join 关联查询优化
创建表
CREATE TABLE temp1 (id INT(11) NOT NULL AUTO_INCREMENT,first INT(11) DEFAULT NULL,second INT(11) DEFAULT NULL,PRIMARY KEY(id),KEY idx_first (first)
) ENGINEINNODB DEFAULT CHARSETutf8;DROP PROCEDURE IF EXISTS insert_temp1;
DELIMITER ;;
CREATE PROCEDURE insert_temp1()
BEGINDECLARE i INT;SET i1;WHILE(i100)DOINSERT INTO temp1(first,second) VALUES(i, i);SET ii1;END WHILE;
END;;
DELIMITER ;
CALL insert_temp1();先来看一个查询语句
SELECT * FROM student INNER JOIN temp1 ON student.idtemp1.id;执行计划 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------- ------- ------- ----------------------- ------ -------- --------1 SIMPLE temp1 (NULL) ALL PRIMARY (NULL) (NULL) (NULL) 100 100.00 (NULL) 1 SIMPLE student (NULL) eq_ref PRIMARY PRIMARY 4 multiplefather.temp1.id 1 100.00 (NULL) MySQL 表关联常见有两种算法
Nested-Loop Join 算法Block Nested-Loop Join 算法
Nested-Loop Join 算法
嵌套循环连接算法NLJ一次一次循环地从第一张表驱动表中读取行这行数据取出关联字段再根据关联字段在另一张表被驱动表里取到满足条件的数据然后取出两张表的结果合集。
从执行计划中可以看出驱动表是 temp1。id 相同按顺序执行先执行的是驱动表优化器一般优先选择小标做驱动表而不是按照 Inner Join 使用时两张表的顺序。
当使用 Left Join 时左表是驱动表右表是被驱动表当使用 Right Join 时右表是驱动表左表是被驱动表当使用 Join 时MySQL 会选择小表作为驱动表大表作为被驱动表。
使用了 NLJ 算法一般 Join 语句中如果执行计划 Extra 中未出现 Using Join Buffer 表示使用的 Join 算法是 NLJ。
综合这些信息再看上面语句的执行流程
从小表 temp1 中取出一条记录从该语句中找到关联字段再去 student 表查询取出 student 表中满足条件的行跟 temp1 表中的结果进行合并返回并重复执行这三步。
整个过程中会读取驱动表 temp1 的所有数据遍历出关联字段 id 的值再根据 id 的值索引扫描 student 表中的对应行因为 temp1 表中有 100 条数据扫描 100 次 student 表的索引1次扫描可以理解为最终只扫描 student 表一行完整数据也就是总共 student 表也扫描了 100 行。因此整个过程扫描了 200 行。但如果被驱动表关联字段没有索引使用 NLJ 算法性能较低。下面对比两种算法。
Block Nested-Loop JoinBNL 算法
基于块的嵌套循环连接算法会先把驱动表的数据都读取到 join_buffer 中然后扫描被驱动表把被驱动表的每一行数据取出跟 join_buffer 中的数据做对比。
执行下面的查询计划
EXPLAIN SELECT * FROM temp1 INNER JOIN student ON temp1.secondstudent.age;执行计划 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------- ------ ------- ------ ------ -------- --------------------------------------------1 SIMPLE temp1 (NULL) ALL (NULL) (NULL) (NULL) (NULL) 100 100.00 (NULL) 1 SIMPLE student (NULL) ALL (NULL) (NULL) (NULL) (NULL) 97664 10.00 Using where; Using join buffer (hash join) 没想到吧不是 Using join buffer(Block Nested Loop)是因为 MySQL 在 8.0.20 版本以后就已经把 BNL 移除了使用 hash join 代替。我用的 MySQL 版本是 8.0.28复现不了 BNL但思路还是要总结一下的。
BNL 算法语句执行流程是
把 temp1 表所有数据都放入到 join_buffer 中把表 student 中的每一行取出来跟 join_buffer 中的数据做对比返回满足 join 条件的数据
整个过程对表 temp1 和 student 表都做了一次全表扫描因此扫描的总行数为 100000表student 的数据总量 100表 temp1 的数据总量100100join_buffer 中的数据都是无序的因此对表 student 中的每行数据都要做 100 次判断所以内存中判断总数是 100000*1001000 万次。如果 temp1 表的数据很大在 join_buffer 中放不下就会将数据分段分段放进 join_buffer再进行判断。
对比 BNL 和 NLJ 两种算法 如果使用 NLJ 算法扫描的行数是 100 * 1000001000 万次但这个是磁盘扫描。而 BNL 算法是在内存中进行判断相比磁盘扫描肯定快得多。 如果被驱动表的关联字段没有索引那就选择 BNL 算法有索引就选择 NLJ 算法。
而 hash join基本思想是根据驱动表在内存中建立一个 hash table然后用大表来探测这个 hash table。这样这需要遍历一遍内表就可以完成 join 操作输出匹配的记录。NLJ 的复杂度是驱动表记录数*被驱动表的记录数hash join 只需要遍历一次内表就可以完成查询效率有所提升。
in 和 exists 优化
主要原则就是小表驱动大表把前面的表称为 A 表后面的表称为 B 表。当 B 表的数据小于 A 表的数据时in 优于 exists。
select * from A where id in (select id from B);A 表的数据小于 B 表时exists 优于 in。
select * from A where exists (select 1 from B where B.idA.id);总结
这篇文章主要对分页查询、join 连接查询以及 in 和 exists 的索引查询优化也比较浅显还要更努力的学习希望各位都能从中有所收获。
