欢迎您访问 最编程 本站为您分享编程语言代码，编程技术文章！

热门搜索/Hot Search

您现在的位置是： 首页

MySQL 学习记录 2-14 索引--查询优化

最编程 2024-07-18 16:58:09
...
\begin{array}{|l|l|}
\hline
索引结构 & 描述 \\
\hline
B+Tree & 最常见的索引类型，大部分存储引擎都支持。 \\
Hash索引 & 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。\\
R-tree & 这是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，使用较少。 \\
Full-text & 通过建立倒排索引，快速匹配文档的方式。\\
\hline
\end{array}

\begin{array}{|l|l|l|l|}
\hline
索引 & InnoDB & MyISAM & Memory \\
\hline
B+tree & 支持 & 支持 & 支持 \\
Hash & 不支持 & 不支持 & 支持 \\
R-tree & 不支持 & 支持 & 不支持 \\
Full-text & 支持（5.6版本后） & 支持 & 不支持 \\
\hline
\end{array}

数据结构可视化：https://iyatt.com/tools/DataStructureVisualizations/Algorithms.html

B+tree 依次插入100、65、169、368、900、556、780、35、215、1200、234、888、158、90、1000、88、120、268、250
file

\begin{array}{|l|l|l|l|}
\hline
分类 & 含义 & 特点 & 关键字 \\
\hline
主键索引 & 针对表中主键创建的索引 & 默认自动自动创建，只有一个 & PRIMARY \\
唯一索引 & 避免同一个表中某列数据重复 & 可以有多个 & UNIQUE \\
常规索引 & 快速定位特定数据 & 可以有多个 &  \\
全文索引 & 全文索引查找的是文本中的关键词，而不是比较索引中的值 & 可以有多个 & FULLTEXT \\
\hline
\end{array}

在 InnoDB 中

\begin{array}{|l|l|l|}
\hline
分类 & 含义 & 特点 \\
\hline
聚集索引（Clustered Index） & 将数据存储与索引放在了一块，索引结构的叶子节点保存了行数据 & 必须有，且只有一个 \\
二级索引（Secondary Index） & 将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键 & 可以存在多个 \\
\hline
\end{array}

聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一索引作为聚集索引
  • 如果前两者都没有合适的，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

创建索引
（如果要创建常规索引，则不指定 UNIQUE 或 FULLTEXT）

CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名 (索引列名);

查看索引

SHOW INDEX FROM 表名;

删除索引

DROP INDEX 索引名 ON 表名;

14.1 语法

创建一张表，创建语句使用：https://blog.iyatt.com/?p=12631#101_%E4%B8%80%E8%88%AC%E7%BA%A6%E6%9D%9F%E7%A4%BA%E4%BE%8B
查看这张表的索引
（加上 \G 会按行显示）

SHOW INDEX FROM new_user\G;

可以看到列名 id 的 Key_name 是 PRIMARY 主键索引（创建表指定了主键约束），列名 name 的 Key_name 就是列名，该列在创建表时指定了唯一约束，其它列则没有索引。
file

现在手动为 age 列创建一个常规索引名为 index_age

CREATE INDEX index_age ON new_user(age);

再次查看可以看到 age 列的索引
file

删除创建的常规索引

DROP INDEX index_age ON new_user;

再次为 age 和 status 同时创建一个唯一索引（联合索引）

CREATE UNIQUE INDEX index_age ON new_user(age, status);

查看索引
file

14.2 性能分析

14.2.1 查询 SQL 执行频次

查询状态信息

SHOW [SESSIOn | GLOBAL] STATUS;

筛选出 SQL 语句执行次数，全局查询含有 Com 的且后续还有 7 个字母的变量，用 7 个下划线 _ 匹配

SHOW GLOBAL STATUS LIKE 'Com_______';

这里我在当前博客的服务器数据库上查询（Mariadb 和 MySQL 基本上兼容）
可以看到插入了 948768 次，删除了 339467 次，查询了 34850658 次，修改了 667195 次。可以看到里面查询次数是最多的，毕竟博客大多数时候都是浏览查看，所以优化的重点就在查询上。
file

14.2.2 慢查询日志

当 MySQL 中某个语句执行超过设定时间，就会记录到日志中，默认是没有打开的。

查看是否开启慢查询日志
当前是关闭的

SELECT @@slow_query_log;

file

查看慢查询时间
默认是 10s，查询时间超过它就会记录日志

SELECT @@long_query_time;

file

如果要开启慢查询日志可以配置：

  • Windows：前往路径 C:\ProgramData\MySQL\MySQL Server 版本，编辑 my.ini（打开显示隐藏文件，不然看不到这个路径）
    file
    这个文件默认是没有编辑权限的，可以在这个文件上右键打开属性
    file

给自己的当前用户添加修改权限
file

这样就可以编辑这个文件了，Windows 默认是打开状态的
slow-query-log 设置 1 就是开启，设置 0 就是关闭。
slow_query_log_file 设置文件名，日志文件位于 C:\ProgramData\MySQL\MySQL Server 版本\Data 下。
long_query_time 设置超时时间。
file

修改完保存，并重启 MySQL 服务器
file

  • Linux：以 root 权限编辑 /etc/my.cnf，我博客服务器用的 Mariadb 10.3.38 中这个文件在 /etc/mysql/my.cnf。配置参数方法同上。

如果要临时设置可以使用下面命令（重启恢复为配置文件中的默认状态），后续其它变量一样

# SESSION 只在当前会话中，GLOBAL 在所有客户端都生效
SET [SESSION | GLOBAL] 变量名 = 变量值；

在 Windows 中默认试打开的，在 Linux 中默认是关闭的。估计因为一般开发是在 Windows 上，这个打开本来就是用于调试，而实际生产环境部署一般是在 Linux 上，所以默认是关闭的，在生产环境上开启这些记录只会增加资源消耗，浪费本该用于业务执行的性能。

14.2.3 profile

查看语句执行耗时

查看是否支持 profile

SELECT @@have_profiling;

file

查看打开状态

SELECT @@profiling;

使用 SET 把这个变量改为 1 即可开启，在执行语句后会记录执行时间，通过命令可以查询

SHOW PROFILES ;

file

查看指定 query_id 的语句的详细耗时

SHOW PROFILE FOR QUERY 查询ID;

file

查看指定 query_id 的语句的 CPU 使用情况

SHOW PROFILE CPU FOR QUERY 查询ID;

file

14.2.4 explain 执行计划

在执行的语句前面加上 EXPLAIN 或 DESC，查询项含义：

  • id：表查询的序列号，相同则从上往下，越大的越先执行
  • select_type：查询类型，常见的有 SIMPLE（简单表，不使用表连接或子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（包含子查询）等
  • type：连接类型，性能从低到高：NULL、system、const、eq_ref、ref、range、index、all
  • possible_key：表中可能用到的索引。
  • key：实际使用的索引
  • key_len：索引字段的最大可能长度
  • rows：必须要执行查询的行数，在 InnoDB 中是一个估计值
  • filltered：结果返回的行数占需要读取行数的百分比，越大越好。

采用这里的三张表来演示：https://blog.iyatt.com/?p=12631#1113_%E5%A4%9A%E5%AF%B9%E5%A4%9A

内连接

EXPLAIN SELECT * FROM stu s, course c, stu_course sc WHERE s.id = sc.stu_id AND c.id = sc.course_id \G;

file

子查询

EXPLAIN SELECT * FROM stu s WHERE s.id IN (
    SELECT stu_id FROM stu_course sc WHERE sc.course_id
                                     = (SELECT id FROM course c WHERE c.name = '高数')
    ) \G;

file

14.3 使用

14.3.1 索引有效条件 - 最左前缀法则

如果索引了多列（联合索引），要遵循最左前缀法则。

创建一张表

CREATE TABLE info (
                      id tinyint,
                      age tinyint,
                      name char(2),
                      class int
);

插入数据

INSERT INTO info VALUES
                     (1, 21, '小强', 1),
                     (2, 21, '小红', 2),
                     (3, 20, '小张', 1);

file

创建联合索引（后续提到的左右都是基于创建索引时指定的列顺序）

CREATE INDEX index_info ON info(age, name, class);

获取每个列的 key_len
age name class 分别为 2 9 5
file

同时指定三列查询

EXPLAIN SELECT * FROM info WHERE age = 21 and name = '小强' and class = 1;

使用了全部索引
file

指定最左列和最右列查询

EXPLAIN SELECT * FROM info WHERE age = 21 and class = 1;

key_len 为 2，只有最左列 age 使用索引
file

不指定最左侧列

EXPLAIN SELECT * FROM info WHERE name = '小强' and class = 1;

没有使用索引
file

age 列指定范围

EXPLAIN SELECT * FROM info WHERE age > 20 and name = '小强' and class = 1;

key_len 为 2，从 age 列右侧下一列开始没有使用索引
file

打乱顺序

EXPLAIN SELECT * FROM info WHERE class = 1 and name = '小强' and age = 21;

顺序不影响索引使用
file

即联合索引必须包含最左列才会使用索引，且中间如果有留空，则从留空列开始不使用索引

14.3.2 索引失效情况 1 - 索引列运算

用上面的表演示，在这个表中查询年龄为 21，名字第二个字为“强”的

EXPLAIN SELECT * FROM info WHERE age = 21 and substring(name, 2, 1) = '强';

key_len 为 2，即 name 字段索引失效了
file

14.3.3 索引失效情况 2 - 范围索引

上面最左前缀法则中，age 指定范围演示过。从范围索引的右侧列开始索引失效。

14.3.4 索引失效情况 3 - 模糊查询

第一个字采用模糊匹配

EXPLAIN SELECT * FROM info WHERE age = 21 AND name LIKE '_强';

key_len 为 2，name 索引失效
file

非第一个字模糊匹配

EXPLAIN SELECT * FROM info WHERE age = 21 AND name LIKE '小_';

索引正常工作
file

第一个字符模糊查询会导致索引失效，非第一个字符模糊查询索引正常工作。

14.3.5 索引失效情况 4 - OR 连接的条件

OR 连接的条件，一个有索引，一个没有索引，那么此时两者都不会使用索引。

这里的 age 有索引，id 没有索引

EXPLAIN SELECT * FROM info WHERE age = 21 OR id = 1;

结果都没有使用索引
file

14.3.6 索引失效情况 5 - 数据分布影响（优化）

当使用索引可能更慢的时候，MySQL 会决定不使用索引。

创建一张表用于演示

CREATE TABLE info1 (
    id int,
    name char(2)
);

INSERT INTO info1 (id, name) VALUES
                                (1, '张三'),
                                (2, '李四'),
                                (3, '王五'),
                                (4, '赵六'),
                                (5, '孙七'),
                                (6, '周八'),
                                (7, '吴九'),
                                (8, '郑十'),
                                (9, '陈一'),
                                (10, '林二'),
                                (11, '罗三'),
                                (12, '何四'),
                                (13, '高五'),
                                (14, '马六'),
                                (15, '刘七'),
                                (16, '梁八'),
                                (17, '黄九'),
                                (18, '曾十'),
                                (19, '彭一'),
                                (20, '胡二'),
                                (21, '许三'),
                                (22, '沈四'),
                                (23, '韩五'),
                                (24, '杨六'),
                                (25, '朱七'),
                                (26, '秦八'),
                                (27, '尤九'),
                                (28, '许十'),
                                (29, '薛一'),
                                (30, '侯二'),
                                (31, '夏三'),
                                (32, '邱四'),
                                (33, '方五'),
                                (34, '石六'),
                                (35, '姚七'),
                                (36, '谭八'),
                                (37, '廖九'),
                                (38, '范十'),
                                (39, '汪一'),
                                (40, '陆二'),
                                (41, '金三'),
                                (42, '魏四'),
                                (43, '陶五'),
                                (44, '戴六'),
                                (45, '郭七'),
                                (46, '洪八'),
                                (47, '邹九'),
                                (48, '江十'),
                                (49, '章一'),
                                (50, '董二');

CREATE INDEX index_info1 ON info1(id);

查询 id > 10 的数据

EXPLAIN SELECT * FROM info1 WHERE id > 10;

没有使用索引
file

查询 id > 30 的数据

EXPLAIN SELECT * FROM info1 WHERE id > 30;

使用了索引
file

当查询的数据是表中的少部分的时候，MySQL 会使用索引，这样速度更快，但是查询的是表中的大部分数据的时候，可能不如直接暴力遍历的速度。

14.3.7 索引提示

当一个列存在多个索引时，可以指定使用某个索引。

这里示例还是使用上面创建的 info 表
为 age 列再创建一个单列索引

CREATE INDEX index_age ON info(age);

此时 age 同时具有前面的联合索引，又有了一个单列索引

EXPLAIN SELECT * FROM info WHERE age = 21;

可能用到的索引有两个，实际用的是联合索引
file

指定使用 index_age 索引（单列）- 建议 MySQL 使用，MySQL 通过一定算法判断是否使用
USE INDEX (索引名)

EXPLAIN SELECT * FROM info USE INDEX (index_age) WHERE age = 21;

file

忽略索引 index_info（联合）
IGNORE INDEX (索引名)

EXPLAIN SELECT * FROM info IGNORE INDEX (index_info) WHERE age = 21;

file

强制使用索引 index_age（单列）
FORCE INDEX (索引名)

EXPLAIN SELECT * FROM info FORCE INDEX (index_age) WHERE age = 21;

file

14.3.8 覆盖索引

查询的列数据都包含在索引中

使用上面的 info 表，删掉创建的索引，只保留原先的联合索引

当查询项都在索引中时

EXPLAIN SELECT age, name, class FROM info WHERE age = 21 AND name = '小强' AND class = 1;

file

当查询项多了一个 id（不在索引中）

EXPLAIN SELECT id, age, name, class FROM info WHERE age = 21 AND name = '小强' AND class = 1;

或者查 *

EXPLAIN SELECT * FROM info WHERE age = 21 AND name = '小强' AND class = 1;

file

  • Using index condition：表示使用了索引条件下推（Index Condition Pushdown，ICP）的优化，即在存储引擎层对索引进行过滤，减少回表查询的次数。
  • Using where：表示在服务器层对数据进行过滤，通常是因为索引不能完全满足查询条件，或者没有使用索引。
  • Using index：表示使用了覆盖索引（Covering Index）的优化，即索引已经包含了所需的所有列，无需访问数据表

14.3.9 前缀索引

当字段类型为字符串时，有时候需要索引很长的字符串，但是这样会让索引变得很大，查询的时候磁盘 IO 占用会非常高，影响查询效率。因此可以只将字符串的一部分前缀建立索引，这样可以提高索引效率。

CREATE INDEX 索引名 ON 表名(列名(前n个字符))

至于这前n个字符具体取多少个，可以参考“索引的选择性”。比如某列的字段内容较长，考虑建立前缀索引，所在的表中有100行数据，如果每行的该字段都只取前 10 个字符，结果这 100 行都没有重复，那么选择性 = 没有重复的行数 100 / 总行数 100 = 1。然后又尝试往前推进看看，该字段取前 9 个字符，结果有 10 行和已经存在的重复，那么选择性= 去除重复的行数 (100-10) / 总行数 100 = 0.9。前一种前缀取 10，每行都是独一无二的，这种情况下索引速度必然好，后一种情况前缀取 9，但是只有 0.9 的比例为非重复，会影响一定效率（遇到重复等于索引失效，要回表查询），但是索引能节省一个字符的空间。索引的选择性就是一种作为参考的参数，来辅助选择前缀大小，综合前缀长度减小又能保证较好的选择性数值。

下面是一个示例表，创建它

CREATE TABLE info2 (
    name char(2),
    email varchar(32)
);

INSERT INTO info2 VALUES
                      ('小强', '123456789@qq.com'),
                      ('小王', '123459875@foxmail.com'),
                      ('小李', 'xiaoli@baidu.com'),
                      ('小红', '1234567@douyin.com'),
                      ('小张', '123986@360.com'),
                      ('小谢', 'xioaoxie@a.com'),
                      ('小陈', 'xiaochen@b.cn'),
                      ('小杨', 'xiaoyang@c.cpn'),
                      ('小赵', 'xiaozhao@d.com'),
                      ('小唐', '1234587@t.com');

file

SELECT count(DISTINCT substring(email, 1, 前缀长度)) / count(*) FROM info2;

file

前缀取 8 的时候选择性为 1，前缀取到 7、6 都是 0.9，取到 5 就只有 0.7 了，那我就取 6

CREATE INDEX prefix_index_email ON info2(email(6));

查看索引时，其中 Sub_part 字段为 6，非前缀索引这个值就是 NULL
file

第一个 rows 为 1，第二个为 2。因为第一个在前缀索引取 6 时没有重复的索引，而后一个索引有重复的，那么就需要回到数据表中去确认是否重复，也就还需要额外查一次。
file

file

上一篇： 嵌入式汇编语言的基本实现

下一篇： 对于爬虫的学习

推荐阅读