发布时间:2024-06-30 12:01
数据准备:
DROP TABLE IF EXISTS `article`;
CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT \'主键\',
`author_id` INT(10) UNSIGNED NOT NULL COMMENT \'作者id\',
`category_id` INT(10) UNSIGNED NOT NULL COMMENT \'分类id\',
`views` INT(10) UNSIGNED NOT NULL COMMENT \'被查看的次数\',
`comments` INT(10) UNSIGNED NOT NULL COMMENT \'回帖的备注\',
`title` VARCHAR(255) NOT NULL COMMENT \'标题\',
`content` VARCHAR(255) NOT NULL COMMENT \'正文内容\'
) COMMENT \'文章\';
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(1,1,1,1,\'1\',\'1\');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(2,2,2,2,\'2\',\'2\');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(3,3,3,3,\'3\',\'3\');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(1,1,3,3,\'3\',\'3\');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(1,1,4,4,\'4\',\'4\');
案例:查询category_id
为1且comments
大于1的情况下,views
最多的article_id
。
1、编写SQL语句并查看SQL执行计划。
# 1、sql语句
SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
# 2、sql执行计划
mysql> EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: article
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 20.00
Extra: Using where; Using filesort # 产生了文件内排序,需要优化SQL
1 row in set, 1 warning (0.00 sec)
2、创建索引idx_article_ccv
。
CREATE INDEX idx_article_ccv ON article(category_id,comments,views);
3、查看当前索引。
4、查看现在SQL语句的执行计划。
我们发现,创建符合索引idx_article_ccv
之后,虽然解决了全表扫描的问题,但是在order by
排序的时候没有用到索引,MySQL居然还是用的Using filesort
,为什么?
5、我们试试把SQL修改为SELECT id,author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;
看看SQL的执行计划。
推论:当comments > 1
的时候order by
排序views
字段索引就用不上,但是当comments = 1
的时候order by
排序views
字段索引就可以用上!!!所以,范围之后的索引会失效。
6、我们现在知道范围之后的索引会失效,原来的索引idx_article_ccv
最后一个字段views
会失效,那么我们如果删除这个索引,创建idx_article_cv
索引呢????
/* 创建索引 idx_article_cv */
CREATE INDEX idx_article_cv ON article(category_id,views);
查看当前的索引
7、当前索引是idx_article_cv
,来看一下SQL执行计划。
数据准备:
DROP TABLE IF EXISTS `class`;
DROP TABLE IF EXISTS `book`;
CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT \'主键\',
`card` INT(10) UNSIGNED NOT NULL COMMENT \'分类\'
) COMMENT \'商品类别\';
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT \'主键\',
`card` INT(10) UNSIGNED NOT NULL COMMENT \'分类\'
) COMMENT \'书籍\';
两表连接查询的SQL执行计划:
1、不创建索引的情况下,SQL的执行计划。
book
和class
两张表都是没有使用索引,全表扫描,那么如果进行优化,索引是创建在book
表还是创建在class
表呢?下面进行大胆的尝试!
2、左表(book
表)创建索引。
创建索引idx_book_card
/* 在book表创建索引 */
CREATE INDEX idx_book_card ON book(card);
在book
表中有idx_book_card
索引的情况下,查看SQL执行计划
3、删除book
表的索引,右表(class
表)创建索引。
创建索引idx_class_card
/* 在class表创建索引 */
CREATE INDEX idx_class_card ON class(card);
在class
表中有idx_class_card
索引的情况下,查看SQL执行计划
由此可见,左连接将索引创建在右表上更合适,右连接将索引创建在左表上更合适。
数据准备:
DROP TABLE IF EXISTS `phone`;
CREATE TABLE IF NOT EXISTS `phone`(
`phone_id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT \'主键\',
`card` INT(10) UNSIGNED NOT NULL COMMENT \'分类\'
) COMMENT \'手机\';
三表连接查询SQL优化
1、不加任何索引,查看SQL执行计划。
2、根据两表查询优化的经验,左连接需要在右表上添加索引,所以尝试在book
表和phone
表上添加索引。
/* 在book表创建索引 */
CREATE INDEX idx_book_card ON book(card);
/* 在phone表上创建索引 */
CREATE INDEX idx_phone_card ON phone(card);
JOIN
语句的优化:
JOIN
语句中的NestedLoop
(嵌套循环)的总次数:永远都是小的结果集驱动大的结果集。(小表驱动大表)NestedLoop
的内层循环。JOIN
语句中被驱动表上JOIN
条件字段已经被索引。JOIN
条件字段被索引且内存资源充足的前提下,不要太吝惜Join Buffer
的设置。数据准备
CREATE TABLE `staffs`(
`id` INT(10) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT \'\' COMMENT \'姓名\',
`age` INT(10) NOT NULL DEFAULT 0 COMMENT \'年龄\',
`pos` VARCHAR(20) NOT NULL DEFAULT \'\' COMMENT \'职位\',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT \'入职时间\'
)COMMENT \'员工记录表\';
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES(\'Ringo\', 18, \'manager\');
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES(\'张三\', 20, \'dev\');
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES(\'李四\', 21, \'dev\');
/* 创建索引 */
CREATE INDEX idx_staffs_name_age_pos ON `staffs`(`name`,`age`,`pos`);
可以看到,复合索引都被用到了,并且SQL中查询字段的顺序,跟使用索引中字段的顺序,没有关系。优化器会在不影响 SQL 执行结果的前提下,自动地优化
结论:全职匹配我最爱指的是,查询的字段按照顺序在索引中都可以匹配到
总结:
最佳左前缀法则:如果索引是多字段的复合索引,要遵守最佳左前缀法则
。指的是查询从索引的最左前列开始并且不跳过索引中的字段。
口诀:带头大哥不能死,中间兄弟不能断。
由此可见,在索引列上进行计算,会使索引失效。
口诀:索引列上不计算。
查看上述SQL的执行计划
由此可知,查询范围的字段使用到了索引,但是范围之后的索引字段会失效。
口诀:范围之后全失效。
在写SQL的不要使用SELECT *
,用什么字段就查询什么字段。
/* 没有用到覆盖索引 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = \'Ringo\' AND `age` = 18 AND `pos` = \'manager\';
/* 用到了覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` = \'Ringo\' AND `age` = 18 AND `pos` = \'manager\';
结论:尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select
mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描口
like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
模糊查询时,尽量不要把%通配符写在开头。只有当百分号在右边,索引才会生效
如果一定要使用%like
,而且还要保证索引不失效,那么使用覆盖索引来编写SQL。
# 创建索引
CREATE INDEX idx_user_nameAge ON tbl_user(name,age);
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id` FROM `staffs` WHERE `name` LIKE \'%in%\';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `name` FROM `staffs` WHERE `name` LIKE \'%in%\';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `age` FROM `staffs` WHERE `name` LIKE \'%in%\';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `pos` FROM `staffs` WHERE `name` LIKE \'%in%\';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` LIKE \'%in%\';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `age` FROM `staffs` WHERE `name` LIKE \'%in%\';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`,`name`, `age`, `pos` FROM `staffs` WHERE `name` LIKE \'%in\';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `pos` LIKE \'%na\';
/* 索引失效 全表扫描 */
EXPLAIN SELECT `name`, `age`, `pos`, `add_time` FROM `staffs` WHERE `name` LIKE \'%in\';
因为name为varchar型,mysql会在底层进行隐式的类型转换。
所以,varchar类型的字段不能忘了加引号,因为会导致进行全表扫描。
mysql5.5.48:
mysql8.0.18
这里的索引没失效,应该是mysql版本升级后的优化效果,老师的是5.5,虽然这里索引没失效,不过还是尽量少用or。
小练习:
假设index(a,b,c)
Where语句 | 索引是否被使用 |
---|---|
where a = 3 | Y,使用到a |
where a = 3 and b = 5 and c = 5 | Y,使用到a,b |
where a = 3 and b = 5 | Y,使用到a,b,c |
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | N,没有用到a字段 |
where a = 3 and c = 5 | 使用到a,但是没有用到c,因为b断了 |
where a = 3 and b > 4 and c = 5 | 使用到a,b,但是没有用到c,因为c在范围之后 |
where a = 3 and b like ‘kk%’ and c = 4 | Y,a,b,c都用到 |
where a = 3 and b like ‘%kk’ and c = 4 | 只用到a |
where a = 3 and b like ‘%kk%’ and c = 4 | 只用到a |
where a = 3 and b like ‘k%kk%’ and c = 4 | Y,a,b,c都用到 |
数据准备
/* 创建表 */
CREATE TABLE `test03`(
`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
`c1` CHAR(10),
`c2` CHAR(10),
`c3` CHAR(10),
`c4` CHAR(10),
`c5` CHAR(10)
);
/* 插入数据 */
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES(\'a1\',\'a2\',\'a3\',\'a4\',\'a5\');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES(\'b1\',\'b22\',\'b3\',\'b4\',\'b5\');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES(\'c1\',\'c2\',\'c3\',\'c4\',\'c5\');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES(\'d1\',\'d2\',\'d3\',\'d4\',\'d5\');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES(\'e1\',\'e2\',\'e3\',\'e4\',\'e5\');
/* 创建复合索引 */
CREATE INDEX idx_test03_c1234 ON `test03`(`c1`,`c2`,`c3`,`c4`);
第一题:
explain select * from test03 where c1=\'a1\' and c2=\'a2\' and c4=\'a4\' and c3=\'a3\';
explain select * from test03 where c4=\'a4\' and c3=\'a3\' and c2=\'a2\' and c1=\'a1\';
# 复合索引并不会失效
# 因为mysql底层的优化器会进行优化,所以可以使用到创建的索引,尽量不要这么写,避免mysql底层进行翻译和转换。
第二题
explain select * from test03 where c1=\'a1\' and c2=\'a2\' and c3>\'a3\' and c4=\'a4\';
# 范围之后全失效(包含那个进行范围查找的字段,范围查找字段之后失效)
第三题
explain select * from test03 where c1=\'a1\' and c2=\'a2\' and c4>\'a4\' and c3=\'a3\';
# 4个字段都生效
# 因为底层会进行优化,把c3提到c4前面,然后c4范围之后全失效,但是c4是生效的,c4之后已经没有字段了,所以4个字段都生效。
第四题
explain select * from test03 where c1=\'a1\' and c2=\'a2\' and c4=\'a4\' order by c3;
# c1,c2,c3都用到了
# 因为索引有两大功能:查找和排序,c3在这里查找没用到,而是用到了排序,但是没统计到里面。
第五题
explain select * from test03 where c1=\'a1\' and c2=\'a2\' order by c3;
# 结果和第四题一样
# 说明在第四题里,也底层也进行了优化,把c3提到c4前面,然后由于c3为排序,所以c3排序完后,不到c4就结束了。
第六题
explain select * from test03 where c1=\'a1\' and c2=\'a2\' order by c4;
# 中间兄弟不能断
# 因为没c3,想到c4,底层就会进行文件内排序。
第七题
explain select * from test03 where c1=\'a1\' and c5=\'a5\' order by c2,c3;
# c1、c2、c3都用到了索引,c2、c3用于排序,不统计到里面。
# 因为c5不在复合索引内,所以没有filesort
第八题
explain select * from test03 where c1=\'a1\' and c5=\'a5\' order by c3,c2;
# 由于c2和c3的位置颠倒了,所以出现了filesort
# 结果和第七题一样,但中间的过程不一样,多了一个using filesort
# 所以要按照创建的复合索引的顺序进行书写
第九题
explain select * from test03 where c1=\'a1\' and c2=\'a2\' order by c2,c3;
# c1、c2用于查找,c2、c3用于排序
# 按照查找和排序都是按照顺序来的,所以没问题
第十题
explain select * from test03 where c1=\'a1\' and c2=\'a2\' and c5=\'a5\' order by c2,c3;
# c1、c2用于查找,c2、c3用于排序
# 结果与第九题类似,所以跟c5没关系,没问题。
第十一题
explain select * from test03 where c1=\'a1\' and c2=\'a2\' and c5=\'a5\' order by c3,c2;
#第八题:explain select * from test03 where c1=\'a1\' and c5=\'a5\' order by c3,c2;
#(出现filesort)
# 因为在第十一题这里,c2先进行查找,变成一个常量,用一个常量去order by \'a2\' 是无效的,所以第十一题的order by c3,c2 等同于 order by c3
第十二题
explain select * from test03 where c1=\'a1\' and c5=\'a5\' order by c3,c2;
# 出现filesort
# 因为c2和c3没有按照索引的顺序
第十三题
explain select * from test03 where c1=\'a1\' and c4=\'a4\' group by c2,c3;
# c4没用到索引,否则会统计到里面
# c1、c2、c3用到索引,c2、c3用来排序,没统计到里面
第十四题
explain select * from test03 where c1=\'a1\' and c4=\'a4\' group by c3,c2;
# 出现temporary(创建临时表)
# c1用到了索引,c2、c3、c4都失效了
/* 最好索引怎么创建的,就怎么用,按照顺序使用,避免让MySQL再自己去翻译一次 */
/* 1.全值匹配 用到索引c1 c2 c3 c4全字段 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c2` = \'a2\' AND `c3` = \'a3\' AND `c4` = \'a4\';
/* 2.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c2` = \'a2\' AND `c4` = \'a4\' AND `c3` = \'a3\';
/* 3.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c4` = \'a4\' AND `c3` = \'a3\' AND `c2` = \'a2\' AND `c1` = \'a1\';
/* 4.用到索引c1 c2 c3字段,c4字段失效,范围之后全失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c2` = \'a2\' AND `c3` > \'a3\' AND `c4` = \'a4\';
/* 5.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c2` = \'a2\' AND `c4` > \'a4\' AND `c3` = \'a3\';
/*
6.用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中,c4字段失效
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c2` = \'a2\' AND `c4` = \'a4\' ORDER BY `c3`;
/* 7.用到了索引c1 c2 c3三个字段,c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c2` = \'a2\' ORDER BY `c3`;
/*
8.用到了索引c1 c2两个字段,c4失效,c1和c2两个字段用于查找,c4字段排序产生了Using filesort说明排序没有用到c4字段
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c2` = \'a2\' ORDER BY `c4`;
/* 9.用到了索引c1 c2 c3三个字段,c1用于查找,c2和c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c5` = \'a5\' ORDER BY `c2`, `c3`;
/* 10.用到了c1一个字段,c1用于查找,c3和c2两个字段索引失效,产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c5` = \'a5\' ORDER BY `c3`, `c2`;
/* 11.用到了c1 c2 c3三个字段,c1 c2用于查找,c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c2` = \'a2\' ORDER BY c2, c3;
/* 12.用到了c1 c2 c3三个字段,c1 c2用于查找,c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c2` = \'a2\' AND `c5` = \'a5\' ORDER BY c2, c3;
/*
13.用到了c1 c2 c3三个字段,c1 c2用于查找,c2 c3用于排序 没有产生Using filesort
因为之前c2这个字段已经确定了是\'a2\'了,这是一个常量,再去ORDER BY c3,c2 这时候c2已经不用排序了!
所以没有产生Using filesort 和(10)进行对比学习!
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c2` = \'a2\' AND `c5` = \'a5\' ORDER BY c3, c2;
/* GROUP BY 表面上是叫做分组,但是分组之前必定排序。 */
/* 14.用到c1 c2 c3三个字段,c1用于查找,c2 c3用于排序,c4失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c4` = \'a4\' GROUP BY `c2`,`c3`;
/* 15.用到c1这一个字段,c4失效,c2和c3排序失效产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = \'a1\' AND `c4` = \'a4\' GROUP BY `c3`,`c2`;
query
过滤性更好的索引。query
中过滤性最好的字段在索引字段顺序中,位置越靠前越好。query
中的where
子句中更多字段的索引。query
的写法来达到选择合适索引的目的。口诀:
带头大哥不能死。
中间兄弟不能断。
索引列上不计算。
范围之后全失效。
覆盖索引尽量用。
不等有时会失效。
like百分加右边。
字符要加单引号。
一般SQL少用or。