买房网站排名,搜索引擎收录查询,wordpress电影怎么做,wordpress重定向传递权重索引创建原则
先写代码#xff0c;再建索引 不应该在创建完表之后立马就创建索引#xff0c;等主体业务开发完毕以后#xff0c;再把涉及到该表的 sql 分析过后再建立索引。联合索引尽量覆盖查询条件 在设计一个联合索引的时候#xff0c;让联合索引尽可能的包含 sql 语句…索引创建原则
先写代码再建索引 不应该在创建完表之后立马就创建索引等主体业务开发完毕以后再把涉及到该表的 sql 分析过后再建立索引。联合索引尽量覆盖查询条件 在设计一个联合索引的时候让联合索引尽可能的包含 sql 语句中的 where、order by、group by 的字段还要确保这些字段尽量满足 sql 最左前缀原则。选取索引字段选值比较多、区分度高的字段 比如我有一张用户表它有一个性别字段只包含男女两种值这就没有办法进行快速的二分查找不如全表扫描索引也就失去意义了。建立索引尽量选取值比较多的字段才能更好的发挥 B 树二分查找的优势。长字符串可以采用前缀索引 选取索引尽量对索引字段类型较小的列设计索引字段小占用磁盘空间也小搜索的时候性能也会好一些。如果需要给长字符串建立索引比如 varchar(255)比如这时就可以针对这个字段的前 20 歌字符建立索引也就是说把这个字段每个值的前面 20 位放到索引树里比如 KEY index(name(20),age,position)。where 和 order by 冲突时优先 where 在 where 和 order by 出现索引设计冲突时优先让 where 条件去使用索引快速筛选出一部分数据再进行排序。因为大多数情况下基于索引进行 where 筛选往往可以最快速筛选出需要的少部分数据再进行排序的成本可能就会降低很多。数据量足够大的情况下再建立索引 一般单表超过十万条数据以后为了改善用户体验再建立索引。
索引优化案例演示
示例表结构和数据
CREATE TABLE student (id INT(11) NOT NULL AUTO_INCREMENT,name VARCHAR(24) NOT NULL DEFAULT COMMENT 姓名,age INT(11) NOT NULL DEFAULT 0 COMMENT 年龄,school VARCHAR(20) NOT NULL DEFAULT COMMENT 学校名,start_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入学时间,PRIMARY KEY (id),KEY idx_name_age_school (name,age,school) USING BTREE
) ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8 COMMENT学生表;INSERT INTO student(NAME,age,school,start_time) VALUES(老大,11,老大小学,NOW());
INSERT INTO student(NAME,age,school,start_time) VALUES(老二, 12,老大小学,NOW());
INSERT INTO student(NAME,age,school,start_time) VALUES(老三,13,老大小学,NOW());DROP PROCEDURE IF EXISTS insert_student;
DELIMITER ;;
CREATE PROCEDURE insert_student()
BEGINDECLARE i INT;SET i1;WHILE(i100000)DOINSERT INTO student(NAME,age,school) VALUES(CONCAT(老,i),i,老大小学);SET ii1;END WHILE;
END;;
DELIMITER ;
CALL insert_student();覆盖查询优化
第一个例子联合索引的首个字段用范围查询不会走索引
EXPLAIN SELECT * FROM student WHERE NAME 老1 AND age 11 AND school老大小学;id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ --------------------- ------ ------- ------ ------ -------- -------------1 SIMPLE student (NULL) ALL idx_name_age_position (NULL) (NULL) (NULL) 97664 0.50 Using where 从上面的输出结果可以看得到没有使用索引mysql 内部可能判断结果集很大回表效率不高直接使用全表扫描我们再来看强制使用索引的情况
EXPLAIN SELECT * FROM student FORCE INDEX(idx_name_age_school) WHERE NAME 老1 AND age 11 AND school老大小学;id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------------- ------------------- ------- ------ ------ -------- -----------------------1 SIMPLE student (NULL) range idx_name_age_school idx_name_age_school 74 (NULL) 48832 1.00 Using index condition 看似结果集数量少了但是回表查询效率不高所以最重的查询效率不一定比全表扫描高可以自己尝试一下这两条语句看看执行时间的区别我就不截图了。
使用覆盖索引查询优化那条查询语句
EXPLAIN SELECT NAME, age, school FROM student WHERE NAME 老1 AND age 11 AND school老大小学;id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------------- ------------------- ------- ------ ------ -------- --------------------------1 SIMPLE student (NULL) range idx_name_age_school idx_name_age_school 74 (NULL) 48832 1.00 Using where; Using index 可以看到我们使用了 idx_name_age_school 索引查询的数目也少了extra 列也显示出使用了索引提高了查询速度。
like kk%一般会使用索引
EXPLAIN SELECT * FROM student_copy WHERE NAME LIKE 老% AND age 11 AND school老大小学;id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------ ---------- ------ ------------------- ------------------- ------- ------ ------ -------- -----------------------1 SIMPLE student_copy (NULL) range idx_name_age_school idx_name_age_school 140 (NULL) 3 33.33 Using index condition 可以看到在 key 的字段里使用了索引这个主要是用到了索引下推优化。
正常联合索引name, age, school是按照最左原则上面这个查询语句会查询 name 字段的索引根据 name 字段过滤完得到的索引行里的 age 和 school 是无序的没有办法很好的利用。MySQL 5.6 之前的版本这个查询语句只能查询名字是老开头的索引然后拿着这些索引的主键一个个回表到主键索引上找出相应的记录再对比 age 和 school 这两个字段是否符合。
MySQL 5.6 引入索引下推可以在遍历索引过程中对索引中包含的所有字段先判断过滤掉不符合的记录后再进行回表能够有效的减少回表次数。使用索引下推优化后上面那个查询在联合索引里匹配老开头的索引之后同时也会在索引里过滤 age 和 school 两个字段拿着过滤完剩下的索引对应的主键 id 再回表查整行数据。
Order by 的优化
EXPLAIN SELECT * FROM student WHERE NAME 老1 AND school 老大小学 ORDER BY age;id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------------- ------------------- ------- ------ ------ -------- -----------------------1 SIMPLE student (NULL) ref idx_name_age_school idx_name_age_school 74 const 1 10.00 Using index condition 利用最左前缀原则查询匹配的条件中并不包含 age 字段只有 name 和 school 两个条件key_len74可以看到 Extra 字段的结果是 Using index condition 而不是 Using filesort。
EXPLAIN SELECT * FROM student WHERE NAME 老1 ORDER BY school;id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------------- ------------------- ------- ------ ------ -------- ----------------1 SIMPLE student (NULL) ref idx_name_age_school idx_name_age_school 74 const 1 100.00 Using filesort 看结果 key_len74查询使用了 name 索引由于用了 school 进行排序跳过了ageextra 结果 Using filesort。
对比下一条语句
EXPLAIN SELECT * FROM student WHERE NAME 老1 ORDER BY age, school;id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------------- ------------------- ------- ------ ------ -------- --------1 SIMPLE student (NULL) ref idx_name_age_school idx_name_age_school 74 const 1 100.00 (NULL) 查询只用了索引 nameage 和 school 用于排序没有 Using filesort。
将 age 和 school 调换
EXPLAIN SELECT * FROM student WHERE NAME 老1 ORDER BY school, age;id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------------- ------------------- ------- ------ ------ -------- ----------------1 SIMPLE student (NULL) ref idx_name_age_school idx_name_age_school 74 const 1 100.00 Using filesort 又出现了 Using filesort因为索引的创建顺序是 nameageschool但是排序的顺序颠倒了不符合最左前缀原则 又使用 Using filesort。
EXPLAIN SELECT * FROM student WHERE NAME 老1 AND age 11 ORDER BY school, age;id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------------- ------------------- ------- ----------- ------ -------- --------1 SIMPLE student (NULL) ref idx_name_age_school idx_name_age_school 78 const,const 1 100.00 (NULL) 虽然排序的字段与索引顺序还是不一样但 age 是常量在排序中被优化了所以索引未颠倒不会出现 Using filesort。
EXPLAIN SELECT * FROM student WHERE NAME 老1 ORDER BY age ASC, school DESC;id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------------- ------------------- ------- ------ ------ -------- ----------------1 SIMPLE student (NULL) ref idx_name_age_school idx_name_age_school 74 const 1 100.00 Using filesort 虽然排序的字段和索引顺序一样了但在排序的时候 age 正序school 倒序这与索引中的排序方式不同从而产生 Using file sort。
EXPLAIN SELECT * FROM student WHERE NAME 老1 ORDER BY NAME;id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------------- ------ ------- ------ ------ -------- -----------------------------1 SIMPLE student (NULL) ALL idx_name_age_school (NULL) (NULL) (NULL) 97664 50.00 Using where; Using filesort 使用范围查找的时候这种情况可以通过使用覆盖索引优化避免 Using filesort
EXPLAIN SELECT NAME, age, school FROM student WHERE NAME 老1 ORDER BY NAME;id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------------- ------------------- ------- ------ ------ -------- --------------------------1 SIMPLE student (NULL) range idx_name_age_school idx_name_age_school 74 (NULL) 48832 100.00 Using where; Using index Group by 和 Order by 类似的以上的情况都差不多实质是先排序后分组遵照索引创建顺序的最左前缀原则。对于 Group by 的优化如果不需要排序的可以加上 Order by null 禁止排序。能写在 where 中的不要用 having。
order by 总结
MySQL 支持用两种方式排序filesort 和 index。Using index 指的是只需要扫描索引本身就能完成排序。index效率高filesort 效率低。
order by 满足两种情况会使用 Using index。
order by 语句满足最左前缀原则。where 子句和 order by 子句组合使用最左前缀原则。 尽量在索引列上完成排序遵循索引建立时的最左前缀原则。 能用覆盖索引尽量用覆盖索引。
总结
这篇文章包含了建立索引的原则联合索引查询优化like XX% 类型查询以及 order by、group by 查询的优化。最重要的部分还是最左前缀原则的理解如果把最左前缀原则理解通透这些优化都会融会贯通。下一篇继续索引优化。
