MySQL索引优化

news/2024/11/9 2:44:21/

5 索引优化

5.1 索引单表优化案例

建表：

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 VARCHAR(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'),
(1,1,3,3,'3','3');//查询
mysql> select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
|  1 |         1 |           1 |     1 |        1 | 1     | 1       |
|  2 |         2 |           2 |     2 |        2 | 2     | 2       |
|  3 |         1 |           1 |     3 |        3 | 3     | 3       |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)

案例

要求：查询 category_id 为 1 且 comments 大于1 的情况下，views 最多的 article_id

//功能实现
mysql> SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
|  3 |         1 |
+----+-----------+
1 row in set (0.00 sec)//explain分析
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

结论：很显然，type是ALL，即最坏的情况。Extra里还出现了Using filesort，也是最坏的情况。优化是必须的

开始优化

新建索引（给WHERE语句后使用的字段添加索引）

创建方式：

  • create index idx_article_ccv on article(category_id,comments,views);
  • ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );
    在这里插入图片描述

索引用处不大，删除：DROP INDEX idx_article_ccv ON article;

结论：

  • type变成了range，这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。

  • 但是我们已经建立了索引，为啥没用呢？

  • 这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。

  • 当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

改进

上次创建索引相比，这次不为comments字段创建索引
在这里插入图片描述

结论：type变为了ref，ref 中是 const，Extra 中的 Using filesort也消失了，结果非常理想

5.2 索引两表优化案例

建表：

CREATE TABLE IF NOT EXISTS class(id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,card INT(10) UNSIGNED NOT NULL,PRIMARY KEY(id)
);CREATE TABLE IF NOT EXISTS book(bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,card INT(10) UNSIGNED NOT NULL,PRIMARY KEY(bookid)
);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 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)));//查询
mysql> select * from class;
+----+------+
| id | card |
+----+------+
|  1 |   17 |
|  2 |    2 |
|  3 |   18 |
|  4 |    4 |
|  5 |    4 |
|  6 |    8 |
|  7 |    9 |
|  8 |    1 |
|  9 |   18 |
| 10 |    6 |
| 11 |   15 |
| 12 |   15 |
| 13 |   12 |
| 14 |   15 |
| 15 |   18 |
| 16 |    2 |
| 17 |   18 |
| 18 |    5 |
| 19 |    7 |
| 20 |    1 |
| 21 |    2 |
+----+------+
21 rows in set (0.00 sec)mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
|      1 |    8 |
|      2 |   14 |
|      3 |    3 |
|      4 |   16 |
|      5 |    8 |
|      6 |   12 |
|      7 |   17 |
|      8 |    8 |
|      9 |   10 |
|     10 |    3 |
|     11 |    4 |
|     12 |   12 |
|     13 |    9 |
|     14 |    7 |
|     15 |    6 |
|     16 |    8 |
|     17 |    3 |
|     18 |   11 |
|     19 |    5 |
|     20 |   11 |
+--------+------+
20 rows in set (0.00 sec)

开始Explain分析：type都是all，需要优化（总有一个表来添加索引驱动）
在这里插入图片描述

  • 左连接为左表加索引
    在这里插入图片描述

删除索引：drop index y on class;

  • 左连接为右表添加索引
    在这里插入图片描述

删除索引：drop index Y on book;

  • 案例：如果别人建的索引位置不对，只需要自己查询时调整左右表的顺序即可
    在这里插入图片描述

结论：

  • 第二行的type变为了ref，rows也变少了，优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要在右表建立索引（小表驱动大表）。
  • 左连接，右表加索引
  • 同理：右连接，左表加索引

5.3 索引三表优化案例

建表：

CREATE TABLE IF NOT EXISTS phone(phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,card INT(10) UNSIGNED NOT NULL,PRIMARY KEY(phoneid)
)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)));//查询
mysql> select * from phone;
+---------+------+
| phoneid | card |
+---------+------+
|       1 |   10 |
|       2 |   13 |
|       3 |   17 |
|       4 |    5 |
|       5 |   12 |
|       6 |    7 |
|       7 |   15 |
|       8 |   17 |
|       9 |   17 |
|      10 |   14 |
|      11 |   19 |
|      12 |   13 |
|      13 |    5 |
|      14 |    8 |
|      15 |    2 |
|      16 |    8 |
|      17 |   11 |
|      18 |   14 |
|      19 |   13 |
|      20 |    5 |
+---------+------+
20 rows in set (0.00 sec)

用上一节两个表，删除他们的索引：

在这里插入图片描述
三表查询语句应为：SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;

创建索引：

  • 应该为第一个LFET JOIN 的右表 book 建索引

    alter table `book` add index Y(`card`);

  • 应该为第二个LFET JOIN 的右表 phone 建索引

    alter table `phone` add index z(`card`);

Explain分析：
在这里插入图片描述
后2行的 type 都是ref且总 rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中

结论：

  • Join语句的优化
  • 尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集（比如：书的类型表驱动书的名称表）”。
  • 优先优化NestedLoop的内层循环，保证Join语句中被驱动表上Join条件字段已经被索引。
  • 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置

5.4 索引失效案例

建表：

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());ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);

  • 1、全值匹配我最爱
    在这里插入图片描述

  • 2、最佳左前缀法则（重要！）：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过复合索引中间列。
    在这里插入图片描述中间列不能断：
    在这里插入图片描述

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

  • 4、存储引擎不能使用索引中范围条件右边的列（范围之后全失效，范围列并不是做的查询而是排序）。
    在这里插入图片描述

  • 5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。
    在这里插入图片描述

  • 6、mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
    在这里插入图片描述

  • 7、is null, is not null 也无法使用索引。
    在这里插入图片描述

  • 8、like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描的操作（%写在最右边索引不会失效，或覆盖索引）。
    在这里插入图片描述
    问题：解决like '%字符串%'时索引不被使用的方法？ 采用覆盖索引的方法！
    建表：

    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('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');//查询
mysql> select * from tbl_user;
+----+------+------+-----------+
| id | name | age  | email     |
+----+------+------+-----------+
|  1 | 1aa1 |   21 | a@163.com |
|  2 | 2bb2 |   23 | b@163.com |
|  3 | 3cc3 |   24 | c@163.com |
|  4 | 4dd4 |   26 | d@163.com |
+----+------+------+-----------+
4 rows in set (0.00 sec)

    创建索引：

    CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);

    索引成功使用：
    在这里插入图片描述
    在这里插入图片描述
    索引失效：
    在这里插入图片描述
    在这里插入图片描述总结：%写在最右边，如果非要写在最左边，就使用覆盖索引

  • 9、字符串不加单引号索引失效。
    在这里插入图片描述在这里插入图片描述
    Explain分析：
    在这里插入图片描述

  • 10、少用or，用它来连接时会索引失效
    在这里插入图片描述

5.5 索引面试题分析

建表：

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','b2','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');
//查看表结构
mysql> select * from test03;
+----+------+------+------+------+------+
| 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)

建索引：

create index idx_test03_c1234 on test03(c1,c2,c3,c4);
//查看索引
mysql> show index from test03;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test03 |          0 | PRIMARY          |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            1 | c1          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            2 | c2          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            3 | c3          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            4 | c4          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

1）逐一增加列
在这里插入图片描述
2）交换条件顺序不影响索引，但最好按照建索引顺序来写SQL
在这里插入图片描述
3) 限定范围

在这里插入图片描述
4）order by
在这里插入图片描述在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
5）group by
在这里插入图片描述
定值、范围还是排序，一般order by是给个范围

group by基本上都需要进行排序，会有临时表产生

建议：

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

5.6 总结

在这里插入图片描述在这里插入图片描述

在这里插入图片描述

优化总结口诀

全值匹配我最爱， 最左前缀要遵守；

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

索引列上少计算， 范围之后全失效；

LIKE 百分写最右， 覆盖索引不写 *；

不等空值还有OR， 索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍。


http://www.ppmy.cn/news/31129.html

相关文章

DEJA_VU3D - Cesium功能集 之 102-简单曲线箭头（标绘+编辑）

DEJA_VU3D - Cesium功能集 之 102-简单曲线箭头（标绘+编辑）

前言 编写这个专栏主要目的是对工作之中基于Cesium实现过的功能进行整合，有自己琢磨实现的，也有参考其他大神后整理实现的，初步算了算现在有差不多实现小140个左右的功能，后续也会不断的追加，所以暂时打算一周2-3更的样子来更新本专栏（每篇博文都会奉上完整demo的源代码，…
阅读更多...
C++ 手写一个高性能json生成与解析器

C++ 手写一个高性能json生成与解析器

文章目录 前言一、了解json格式二、统一数据类型：TJson三、解析json数据四、输出json数据五、实现便利的修改与访问六、性能优化七、源码下载与解析前言 由于C++标准库中并不存在解析json数据库，但json格式又非常的常见 如今绝大部分网络数据传输都采用的json数据传输格式，…
阅读更多...
Linux操作系统安装——服务控制

Linux操作系统安装——服务控制

个人简介&#xff1a;云计算网络运维专业人员&#xff0c;了解运维知识&#xff0c;掌握TCP/IP协议&#xff0c;每天分享网络运维知识与技能。座右铭&#xff1a;海不辞水&#xff0c;故能成其大&#xff1b;山不辞石&#xff0c;故能成其高。个人主页&#xff1a;小李会科技的…
阅读更多...
STM32移植FreeRTOS操作系统

STM32移植FreeRTOS操作系统

一、FreeRTOS源码下载&#xff08;1&#xff09;移植钱得准备前菜对吧&#xff0c;我们先来去官网瞄一瞄网址&#xff1a;https://freertos.org/zh-cn-cmn-s/ 第一步&#xff1a;点击下载FreeRTOS第二步&#xff1a;选择版本下载&#xff08;我选择稳定版本&#xff09;注&…
阅读更多...
Java进阶（下篇2）

Java进阶（下篇2）

Java进阶&#xff08;下篇2&#xff09;一、IO流01.File类的使用1.1、File类的实例化1.2、File类的常用方法11.3、File类的常用方法21.4、课后练习02、IO流原理及流的分类2.1、IO流原理2.2、流的分类2.3、IO 流体系03、节点流(或文件流)3.1、FileReader读入数据的基本操作3.2、…
阅读更多...
ESLint、Prettier插件的安装与使用

ESLint、Prettier插件的安装与使用

在统一代码风格这一块&#xff0c;通常大家都会用到ESLint。虽然 ESLint 本身具备自动格式化代码的功能&#xff0c;但ESLint 的主要优势在于代码的风格检查并给出提示&#xff0c;而在代码格式化这一块 Prettier 做的更加专业&#xff0c;因此在实际项目开发中我们经常将 ESLi…
阅读更多...
JAVA架构与开发（JAVA架构是需要考虑的几个问题）

JAVA架构与开发（JAVA架构是需要考虑的几个问题）

在企业中JAVA架构师主要负责企业项目技术架构&#xff0c;企业技术战略制定&#xff0c;技术框架搭建&#xff0c;技术培训和技术攻坚的工作。 在JAVA领域&#xff0c;比较多的都是web项目。用于解决企业的数字化转型。对于JAVA架构师而言&#xff0c;平时对项目的架构主要考虑…
阅读更多...
如何成为一名全栈工程师：专业建议与技能要求

如何成为一名全栈工程师：专业建议与技能要求

作为一名全栈工程师&#xff0c;你需要拥有跨越前端、后端、数据库等多个领域的技能&#xff0c;并能够将它们整合起来构建出完整的应用程序。因此&#xff0c;成为一名全栈工程师需要你掌握多种技术&#xff0c;具备较强的编程能力和系统设计能力。下面&#xff0c;我将从以下…
阅读更多...
最新文章

Copyright @ 2022~2023