索引分析
单表索引分析
数据准备
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语句并查看其执行计划
需要优化全表扫描ALL
和文件内排序Using filesort
2.创建索引idx_article_ccv
。
CREATE INDEX idx_article_ccv ON article(category_id,comments,views);
3.查看当前索引。
4.查看目前SQL语句的执行计划。
创建idx_article_ccv
之后,虽然解决了全表扫描的问题,但是在order by
排序的时候并没有用到索引,依然使用的是文件内排序,为什么?
5.尝试把SQL修改为EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments =1 ORDER BY views DESC LIMIT 1;
查看执行计划。
推论:当comments>1
的时候order by
排序views
字段索引就用不上,但是当comments=1
的时候order by
排序views
字段索引就可以用上!!!所以,范围之后的索引会失效。
6.我们现在知道范围之后的索引会失效,原来的索引idx_article_ccv
最后一个字段views
会失效,那么我们如果删除这个索引,创建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
CREATE INDEX idx_book_card ON book(card);
目前的索引是idx_book_card
,查看SQL执行计划
2.删除book
表的索引,右表(class
表)创建索引。
创建索引idx_class_card
CREATE INDEX idx_class_card ON class(card);
在class
表中存在idx_class_card
索引的情况下,查看SQL的执行计划
由此可见,左连接将索引创建在右表上更合适,右连接将索引创建在左表上更合适。
三表索引分析
数据准备
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
三张表连接查询SQL优化
1.不加任何索引,查看SQL执行计划。
2.根据两表查询优化经验,左连接需要在右表上创建索引,所以尝试在book
和phone
表上添加索引。
CREATE INDEX idx_book_card ON book(card);
CREATE INDEX idx_phone_card ON phone(card);
再次查看SQL的执行计划
结论
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`);
索引失效的情况
- 全值匹配我最爱;
- 最佳左前缀法则;
- 不在索引列上做任何操作(计算,函数,(自动或者手动)类型转换),会导致索引失效而转向全表扫描;
- 索引中范围条件右边的字段会全部失效;
- 尽量使用覆盖索引(只访问索引的查询,索引列和查询列一致),减少
SELECT *
; - MySQL在使用
!=
或者<>
的时候无法使用索引会导致全表扫描; is null / is not null
也无法使用索引;like
以通配符开头%abc
索引失效会变成全表扫描(可以使用覆盖索引解决这个问题);- 字符串不加单引号索引失效;
- 少用
or
,用它来连接时会索引失效。
最佳左前缀法则
案例
/* 用到了idx_staffs_name_age_pos索引中的name字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo';
/* 用到了idx_staffs_name_age_pos索引中的name, age字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18;
/* 用到了idx_staffs_name_age_pos索引中的name,age,pos字段 这是属于全值匹配的情况!!!*/
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';
/* 索引没用上,ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `age` = 18 AND `pos` = 'manager';
/* 索引没用上,ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `pos` = 'manager';
/* 用到了idx_staffs_name_age_pos索引中的name字段,pos字段索引失效 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `pos` = 'manager';
概念
最佳左前缀法则:如果索引时多字段的复合索引,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的字段。
口诀:带头大哥不能死,中间兄弟不能断。
索引列上下不计算
案例
# 现在要查询`name` = 'Ringo'的记录下面有两种方式来查询!
# 1、直接使用 字段 = 值的方式来计算
mysql> SELECT * FROM `staffs` WHERE `name` = 'Ringo';
+----+-------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+-------+-----+---------+---------------------+
| 1 | Ringo | 18 | manager | 2020-08-03 08:30:39 |
+----+-------+-----+---------+---------------------+
1 row in set (0.00 sec)
# 2、使用MySQL内置的函数
mysql> SELECT * FROM `staffs` WHERE LEFT(`name`, 5) = 'Ringo';
+----+-------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+-------+-----+---------+---------------------+
| 1 | Ringo | 18 | manager | 2020-08-03 08:30:39 |
+----+-------+-----+---------+---------------------+
1 row in set (0.00 sec)
我们发现以上两条SQL的执行结果都是一样的,但是执行效率有没有差距呢???
通过分析两条SQL的执行计划来分析性能。
由此可见,在索引列上进行计算,会使索引失效。
口诀:索引列上不计算。
范围之后全失效
案例
/* 用到了idx_staffs_name_age_pos索引中的name,age,pos字段 这是属于全值匹配的情况!!!*/
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';
/* 用到了idx_staffs_name_age_pos索引中的name,age字段,pos字段索引失效 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '张三' AND `age` > 18 AND `pos` = 'dev';
查看其执行计划
由此可知,查询范围的字段使用到了索引,但是范围之后的索引字段会失效。
口诀:范围之后全失效。
覆盖索引尽量用
在写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';
口诀:查询一定不用*
。
不等有时会失效
/* 会使用到覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` != 'Ringo';
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` != 'Ringo';
like百分加右边
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing%';
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing';
/* 使用索引范围查询 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE 'Rin%';
口诀:like
百分加右边。
如果一定要使用%like
,而且还要保证索引不失效,那么就要使用覆盖索引。
/* 使用到了覆盖索引 */
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';
口诀:覆盖索引保两边。
字符要加单引号
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` = 'Ringo';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` = 2000;
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 2000;
这里name=2000在Mysql中会发生强制类型转换,将数字转成字符串。
口诀:字符要加单引号。