索引优化分析下 【MySQL高级篇3】

转x33g5p2x  于2021-12-06 转载在 Mysql  
字(12.6k)|赞(0)|评价(0)|浏览(307)

1、索引优化

1.1、索引单表优化案例

1. 建表sql语句

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');

2. 没建索引之前

  • 查询的sql语句
SELECT * FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1
  • 结果

  • 性能分析

3. 建了idx_article_ccv索引之后

  • 建索引的语句
CREATE INDEX idx_article_ccv ON `article` (category_id,comments,views)
  • 建索引后的性能分析

4. 建了idx_article_cv索引之后

  • 建索引的sql语句
CREATE INDEX idx_article_cv ON `article` (category_id,views)
  • 建索引后的性能分析

1.2、索引两表优化案例

  • 建表和插入数据的sql语句
CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

1. 没建索引之前

  • 查询的sql语句
SELECT * FROM book LEFT JOIN class ON book.card = class.card
  • 结果演示

  • 性能分析的sql语句
EXPLAIN SELECT * FROM book LEFT JOIN class ON book.card = class.card
  • 性能分析结果

2. 在左表card字段建索引

  • 建索引的sql语句
CREATE INDEX Y ON book(card)
  • 性能分析的sql语句
EXPLAIN SELECT * FROM book LEFT JOIN class ON book.card = class.card
  • 性能分析结果

3. 在右表card字段建索引

  • 建索引的sql语句
CREATE INDEX X ON class(card)
  • 性能分析的sql语句
EXPLAIN SELECT * FROM book LEFT JOIN class ON book.card = class.card
  • 性能分析结果

4. 总结

所以说，如果是左连接的话，就将索引建立在右表上；如果是右连接的话，就将索引建立在左表上。

1.3、索引三表优化案例

在这个案例上使用到的表是在上一个案例上添加了一张表。

  • 建表和插入数据的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)));

1. 没建索引之前

  • 查询数据的sql语句
SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card
  • 查询结果演示

  • 性能分析的sql语句
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card=phone.card;
  • 性能分析结果

2. 在book和phone表上建了索引之后

  • 性能分析的sql语句
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card=phone.card;
  • 性能分析结果

1.4、 Join语句的优化

  1. 尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集”。
  2. 优先优化NestedLoop的内层循环。
  3. 保证Join语句中被驱动表上的Join条件字段已经被索引。
  4. 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置。

1.5、怎样避免索引失效？

  • 建表和插入数据的sql语句
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

insert into staffs(NAME,age,pos,add_time) values('z3',22,'manager',NOW());
insert into staffs(NAME,age,pos,add_time) values('July',23,'dev',NOW());
insert into staffs(NAME,age,pos,add_time) values('2000',23,'dev',NOW());

1.5.1、全值匹配我最爱

全值匹配：就是查询字段在索引中可以全部匹配到。

1.第一条sql语句的性能分析

SELECT * FROM staffs WHERE NAME='July'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME='July'
  • 性能分析的结果演示

2.第二条sql语句的性能分析

SELECT * FROM staffs WHERE NAME='July' AND age = 23
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age = 23
  • 性能分析的结果演示

3.第三条sql语句的性能分析

SELECT * FROM staffs WHERE NAME='July' AND age = 23 AND pos = 'dev'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age = 23 AND pos = 'dev'
  • 性能分析的结果演示

1.5.2、最佳左前缀法则

最佳左前缀法则：如果索引了多列，要遵守最佳左前缀法则。指的是擦查询从索引的最左列开始并且不跳过索引中的列。

1.第一条sql语句的性能分析

SELECT * FROM staffs WHERE pos = 'dev'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev'
  • 性能分析的结果演示

2.第二条sql语句的性能分析

SELECT * FROM staffs WHERE age = 23 AND pos = 'dev'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev'
  • 性能分析的结果演示

3.第三条sql语句的性能分析

SELECT * FROM staffs WHERE NAME = 'July' AND pos = 'dev'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND pos = 'dev'
  • 性能分析的结果演示

4. 最佳左前缀法则总结

带头大哥不能死，中间兄弟不能断

1.5.3、不在索引列上做任何操作

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。

1.第一条sql语句的性能分析

SELECT * FROM staffs WHERE NAME='July'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME='July'
  • 性能分析的结果演示

2.第二条sql语句的性能分析(在索引列使用了函数)

SELECT * FROM staffs WHERE LEFT(NAME,4) = 'July'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME,4) = 'July'
  • 性能分析的结果演示

3.口诀总结

索引列上少计算

1.5.4、存储引擎不能使用范围条件右边的列的索引

  • 存储引擎不能使用范围条件右边的列的索引，但自己这一列的索引有效。

1.第一条sql语句的性能分析

SELECT * FROM staffs WHERE NAME = 'July' AND age > 22 AND pos='dev'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age > 22 AND pos='dev'
  • 性能分析的结果演示

2.多条sql语句的性能分析对比

3.口诀总结

范围之后全失效

1.5.5、尽量使用覆盖索引

  • 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。
  • 覆盖索引深入理解：如果查询列的字段名和字段个数和索引列的字段名和个数完全一致，或者索引列的字段名和个数真包含查询列的字段名和字段个数。以上两种情况都属于覆盖索引。

1.第一条sql语句的性能分析

SELECT NAME,age,pos FROM staffs WHERE NAME = 'July' AND age = 23 AND pos='dev'
  • 性能分析的sql语句
EXPLAIN SELECT NAME,age,pos FROM staffs WHERE NAME = 'July' AND age = 23 AND pos='dev'
  • 性能分析的结果演示

2.多条sql语句的性能分析对比

1.5.6、在使用不等于（!= 或 <>）的时候会导致索引失效

  • MySQL在使用不等于（!= 或 <>）的时候无法使用索引会导致全表扫描。

1.第一条sql语句的性能分析

SELECT * FROM staffs WHERE NAME != 'July'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME != 'July'
  • 性能分析的结果演示

1.5.7、is null、is not null 也会使索引失效

1.第一条sql语句的性能分析

SELECT * FROM staffs WHERE NAME IS NOT NULL
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME IS NOT NULL
  • 性能分析的结果演示

2.多条sql语句的性能分析对比

1.5.8、like以通配符开头会导致MySQL索引失效

建表和插入数据的sql语句

CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2aa2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3aa3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4aa4',26,'d@163.com');

创建索引的sql语句

CREATE INDEX idx_name_age ON tbl_user(NAME,age)

1.第一条sql语句的性能分析

SELECT * FROM tbl_user WHERE NAME LIKE '%aa%'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%'
  • 性能分析的结果演示

2.第二条sql语句的性能分析

SELECT * FROM tbl_user WHERE NAME LIKE '%aa'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa'
  • 性能分析的结果演示

3.第三条sql语句的性能分析

SELECT * FROM tbl_user WHERE NAME LIKE 'aa%'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE 'aa%'
  • 性能分析的结果演示

4.口诀总结

Like百分写最右

5.出现的问题

  • 对此，我们需要使用覆盖索引。就是我们查询的字段需要被在相应的索引字段中。这样我们可以提高查询速率。

举例说明

  • sql语句
EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%'
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%'
EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%'
EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%'
  • 性能说明图示

  • sql语句
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%'
EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%'
  • 性能说明图示

1.5.9、字符串要加单引号

  • 如果你的字段类型是VARCHAR的话，那么相关的字符串不加单引号会导致其字段的索引失效。
  • 因为这样会导致隐式的类型转换，所以会导致全表扫描。

1.5.10、使用or连接会使索引失效

1.第一条sql语句的性能分析

SELECT * FROM tbl_user WHERE NAME = '1aa1' OR NAME = '2aa2'
  • 查询结果

  • 性能分析的sql语句
EXPLAIN SELECT * FROM tbl_user WHERE NAME = '1aa1' OR NAME = '2aa2'
  • 性能分析的结果演示

1.5.11、小结

优化口诀总结

  • 全值匹配我最爱，最左前缀要遵守；
  • 带头大哥不能死，中间兄弟不能断；
  • 索引列上少计算，范围之后全失效；
  • Like百分写最右，覆盖索引不写星；
  • 不等空值还有or，索引失效要少用；
  • VAR引号不可丢，SQL高级也不难！

1.6、索引优化面试题

1.6.1、创建数据库

create table test(
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 test(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);
mysql> select * from test;
+----+------+------+------+------+------+
| id | c1   | c2   | c3   | c4   | c5   |
+----+------+------+------+------+------+
|  1 | a1   | a2   | a3   | a4   | a5   |
|  2 | b1   | b2   | b3   | b4   | b5   |
|  3 | c1   | c2   | c3   | c4   | c5   |
|  4 | d1   | d2   | d3   | d4   | d5   |
|  5 | e1   | e2   | e3   | e4   | e5   |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)

1.6.2、创建索引

# 创建索引
mysql> create index idx_test_c1234 on test(c1,c2,c3,c4);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 查询索引
mysql> show index from test;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY        |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | idx_test_c1234 |            1 | c1          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_test_c1234 |            2 | c2          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_test_c1234 |            3 | c3          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_test_c1234 |            4 | c4          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 se

1.6.3、分析以下SQL执行情况

1、基本查询
explain select * from test where c1 = 'a1';
explain select * from test where c1 = 'a1' and c2 = 'a2';
explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 = 'a3';
explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';
2、基本查询2
explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';
explain select * from test where c1 = 'a1' and c3 = 'a3' and c2 = 'a2' and c4 = 'a4';
explain select * from test where c4 = 'a4' and c3 = 'a3' and c2 = 'a2' and c1 = 'a1';
3、范围查询
explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 > 'a3' and c4 = 'a4';
explain select * from test where c1 = 'a1' and c2 = 'a2' and c4 > 'a4' and c3 = 'a3';
4、单值排序查询
explain select * from test where c1 = 'a1' and c2 = 'a2' and c4 = 'a4' order by c3;
explain select * from test where c1 = 'a1' and c2 = 'a2' order by c3;
explain select * from test where c1 = 'a1' and c2 = 'a2' order by c4;
5、多值排序查询
explain select * from test where c1 = 'a1' and c5 = 'a5' order by c2,c3;
explain select * from test where c1 = 'a1' and c5 = 'a5' order by c3,c2;
explain select * from test where c1 = 'a1' and c2 = 'a2' order by c2,c3;
explain select * from test where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c2,c3;
explain select * from test where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c3,c2;
explain select * from test where c1 = 'a1' and c5 = 'a5' order by c3,c2;
6、分组查询
explain select * from test where c1 = 'a1' and c4 = 'a4' group by c2,c3;
explain select * from test where c1 = 'a1' and c4 = 'a4' group by c3,c2;

1.6.4、面试题总结

  • 定值、范围实际上还是排序，通常 order by 给定的是个范围；
  • group by 一般都需要进行排序，会产生临时表；

1.7、优化建议

  • 对于单键索引，尽量选择针对当前 query 过滤性更好的字段；
  • 在选择组合索引时，当前 query 中过滤性最好的字段在索引字段顺序中越靠前越好；
  • 在选择组合索引时，尽量选择能包含当前 query 中 WHERE 子句中更多字段的索引；
  • 尽可能通过分析统计信息和调整 query 写法来达到选择合适索引的目的；

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://blog.csdn.net/weixin_56727438/article/details/121713056

内容来源于网络，如有侵权，请联系作者删除！

sqlmysql数据库

相关文章

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多

    目录

    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com