MySQL数据索引与优化

2022-05-18 02:26:54

内容包括：

  • 概念和基本使用
  • 索引的优缺点及使用场景
  • 索引底层结构(B,B+树及优缺点对比)
  • 高效使用索引
  • 聚簇索引与非聚簇索引

概念

索引存储在内存中，为服务器存储引擎为了快速找到记录的一种数据结构。

基本操作

为数据表添加索引：

ALTER TABLE table_name ADD INDEX index_name (column_list)  //普通索引
ALTER TABLE table_name ADD UNIQUE (column_list)   //唯一性索引，设置后，不允许插入重复值
ALTER TABLE table_name ADD PRIMARY KEY (column_list) //唯一索引，通常用于自增主键。

删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

查看索引

 SHOW INDEX FROM tblname;
 SHOW KEYS FROM tblname;

优缺点及使用场景

  • 减少表的检索行数，提高查询效率
  • 建立唯一索引或者主键索引,保证数据字段的唯一性
  • 检索时有分组和排序需求时，减少服务器排序的时间

缺点：

  • 创建和维护索引需要消耗时间及内存，随着数据的增加而增加
  • 索引字段过多，数据量巨大时，索引占据空间可能比表更大。
  • 当对表的数据进行更新操作时,索引也要动态的维护,这样就会降低数据的维护速度。

使用注意：

  • 表数据较小时不建议使用，此时全表扫描可能效率更好。
  • 在经常需要where、排序、分组、取区间的列上建议使用。
  • 列不能作为表达式的一部分，或者用作函数参数，否则失效。
  • 当表更新操作远大于select操作时，不建议添加索引。

索引底层数据结构了解

数据组织方面

  • 选择树形存储
    基础数据结构中，hash时间复杂度(O(1))但支持顺序查找困难。数组链表复杂度(O(n))。树在查找上时间复杂度居中(O(logn))，天然支持顺序。
  • 存储引擎等块
    每块数据长度不定，索引中至少必须存储磁盘id、起始号、偏移号这三个值。由此问题，设计出以块为单位，避免跨页读取数据，块的单位一般等同磁盘的页或其倍数。(innodb块大小为16k，操作系统一页=4k)
  • 有序存储
    索引有序 + 磁盘内有序，加速查找时间 且 更好的支持顺序查找。
  • 聚簇索引
    这是数据组织方式。innodb使用聚簇索引，叶子节点中包含索引+数据； MyIsm引擎非聚簇，叶子节点中包含索引+数据指针，数据被存储在其他地方。

B树

平衡多路查找树，一棵m阶的B树。
特性：

  1. 树中每个结点最多含有m个孩子( m >= 2 );
  2. 除根结点和叶子结点外，其他每个结点至少 m/2 个孩子。
  3. 若根结点不是叶子，至少2个孩子。
  4. 有 j 个孩子的非叶节点恰好有 j-1 个关键码，关键码按递增次序排序。
    MySQL数据索引与优化

B树存在磁盘中，我们想要查找29，查找过程：
1. 根据根结点找到文件目录的根磁盘块1，将其中信息导入内存。 【磁盘IO操作一次】
2. 此时内存中有两个文件17,35和三个存储其他磁盘页面地址的数据。 比较：17<29<35，因此我们访问指针P2
3. 根据P2指针，我们定位到磁盘3，并将其信息导入内存。【磁盘IO操作2次】
4. 此时内存中有两个文件26，30和三个存储其他磁盘页面地址信息的指针，26<29<30，因此我们找到P2指针。
5. 根据P2指针，定位到磁盘8，将其中信息导入内存。【磁盘IO操作3次】

B+

相对B树的不同特性：

  1. 非叶子节点的值会以最大或最小值出现在其子节点中，即叶子节点包含所有元素。
  2. 非叶子节点带有索引数据和指向叶子节点的指针，不包含指向实际元素数据的地址信息。仅叶子节点有所有元素信息。
  3. 每个元素不保存数据，只保存索引值即主键。
  4. 所有叶子节点形成一个有序链表。
    MySQL数据索引与优化

单行查询时与B树相同
范围查询时，比如查找大于3小于8的数据，根据单行查找方式查找到3之后，通过链表直接遍历后面的元素。

B+树优势:

  1. B+树的磁盘读写代价更低/效率更高。同样的一块磁盘大小，B树需要存储表元素数据，B+只需要存储索引，可以存储更多节点。同等元素数据量下，B+树层数更少。
  2. B+树的查询效率稳定。因为非终结点只是关键字的索引，所以任何关键字的查找必须走一条根到叶子的路。
  3. B+树中叶子结点也形成一个链表，所以B+树在面对范围查询时比B树更加高效。

InnoDB索引使用

索引分主索引和辅助索引
主索引在表创建后即存在。以主键为索引，叶子节点存储元素数据。
为主键外的字段添加的索引为辅助索引。以字段内容为索引，叶子节点存储元素对应主键。
MyISAM不同点在于叶子存储的不是元素数据，而是元素数据地址。实现索引与实际数据分离。

如何高效率使用索引

独立列查询

SQL语句使用不当时，将无法使用现存索引而去全表扫描。所以需要注意：索引列不能是表达式的一部分，也不能是函数的参数。
通过在查询SQL前加explain，查看是否有使用索引。
MySQL数据索引与优化
上图中，为timestamp字段添加了索引。 明显使用DATE()函数后，timestamp不使用索引，rows行数为总数据行数。

前缀索引查询（注意选择性把握）

选择性指不重复的索引值和数据表的记录总数的比值。选择性最高时，即所有键不重复时选择性为1。
由上面对索引内部实现的描述我们得知，我们索引的字段越长时，所占内存也就越大。前缀索引意在保持较高选择性的情况下，取字段的前缀部分用于索引，降低内存使用率。
我对测试表中pdl字段及前缀部分的选择性进行观测如下：
MySQL数据索引与优化
MySQL数据索引与优化
MySQL数据索引与优化
如图，前缀为9时选择性已经较高，再增加时，没有明显提升。这时，如果pdl字段很长，就可以考虑使用pdl的前缀9个字符作为前缀索引。

alter table com_pdl_stat add key `left_pdl`(pdl(9));

注意：无法使用前缀索引做ORDER BY 和 GROUP BY，考虑业务场景做取舍。

多列索引合并

很多时候我们为了查询方便，为很多列单独创建索引。但我们在使用where筛选时，却多使用AND，OR等条件。
当我为表的pdl，timestamp字段单独设置索引时，and查询为：
MySQL数据索引与优化
通过key标志知道此时仅使用了pdl字段的索引。filtered仅17.92.
这里的仅使用了pdl字段索引。（高性能提到5.0之后的版本会各自使用pdl和timestamp字段，然后SQL服务器对多个索引结果做相交(AND)或联合操作(OR)操作，通过extra可查询，但是我的5.7没有这种优化，不知道为什么~~）

如上，仅使用where条件的第一个字段索引 或者 服务器消耗CPU，内存等资源去做合并工作，都会影响查询性能。

这是有必要合并索引，创建pdl_time(pdl, timestamp)索引后同样的查询结果如下：
MySQL数据索引与优化
pdl_time索引被使用，filtered达到100%。
在创建多列索引时注意：
- 通常将选择性高的字段放在前面
- 多列字段的前缀也可以作为索引(例如(a,b)索引时，可以单独使用a索引，但不能单独使用b索引)

聚簇索引

聚簇索引指的是一种数据组织结构。判断标准为：索引的叶子节点中，存储的是数据还是只想数据块的指针。如果是指向数据块指针，则为非聚簇索引。

索引类型依赖存储引擎，Innodb使用的是聚簇索引，MyISAM使用非聚簇索引

Innodb主键索引图：

MySQL数据索引与优化
如图为Innodb存储引擎生成的主键索引结构。非叶子节点存储主键，叶子节点存储主键和行数据(还有事务ID和回滚指针)。

Innodb辅助索引图：
MySQL数据索引与优化
如图为Innodb存储引擎生成的辅助索引结构。叶子节点存储索引字段和对应的主键值，索引到主键值后，根据主键值再去主键索引中查找对应的数据。

优点在于：
  • 减少磁盘IO次数。使用索引查询数据时，索引节点和数据被一起载入内存，不需要根据指针再进行一次IO读取。
  • 无需维护辅助索引。当出现数据页分裂时，无需更新索引中的数据块指针。

非聚簇索引图：
MySQL数据索引与优化
非聚簇索引主键索引和辅助索引结构一致。

SQL慢查询原因

先确保服务响应慢时，不是一些偶然情况或者机器性能问题，确定响应慢源头是SQL操作。

  • 定位慢查询
// 查看是否开启了慢查询日志
show variables like ‘%slow_query_log‘;
// 开启
set global slow_query_log=‘ON‘;
// 执行show variables like ‘%slow_query_log‘就有log_file地址
+---------------------+--------------------------------------------+
| Variable_name       | Value                                      |
+---------------------+--------------------------------------------+
| slow_query_log      | ON                                         |
| slow_query_log_file | /usr/local/mysql/data/TEST-slow.log |
+---------------------+--------------------------------------------+
// 查看慢查询阈值
show variables like ‘%long_query_time%‘;
// 调整阈值
set global long_query_time = 1;
// 关闭当前session再开启，执行一个超过1s的SQL时，将被记录在 `/usr/local/mysql/data/TEST-slow.log`里
  • explain (??使用过)
    在指令前?explain，打印如下
// 使用JOIN时会有两条记录
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | a2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  180 |   100.00 | NULL                                               |
|  1 | SIMPLE      | a1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  732 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
type字段含义
 	- all  全数据表扫描 == 这种情况下的是急需优化的
 	- index  全索引扫描，此时如果extra = Using index，则索引字段覆盖select的字段，不需要到回表扫描，效率更高些。
	- range  在索引中进行范围查找
	- index_merge 使用多个单列索引合并查找
- filtered 命中率。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，图为百分之10。此值过低也不行。
- key 查询中使用到的索引
  • profile (相对explain分析更详细)
// 查询profile是否开启
show variables like ‘profiling‘;
// 开启
set profiling = ‘ON‘;
// 再执行以下，将显示 id -> 最近操作的SQL
show profiles;

// show profile for query $id时，展示某个指令详细消耗情况。以下子命令可跟在profile后展示。
// help profile 可查看show profile子命令
type: {
    ALL  			// 展示所有
  | BLOCK IO		// 显示磁盘块IO消耗时间
  | CONTEXT SWITCHES	// 上下文切换开销
  | CPU				// 用户+系统cpu消耗时间
  | IPC				// 通信发送/接受消息消耗时间
  | MEMORY			// 暂未处理
  | PAGE FAULTS		// 页面错误消耗时间
  | SOURCE			// 显示和source_funcation、source_file、source_line相关的开销信息
  | SWAPS			// 显示交换次数
}

// 样例
mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000351 | 	// 开始
| checking permissions | 0.000009 |		// 检查权限
| checking permissions | 0.000007 |		// 检查权限(有两个表)
| Opening tables       | 0.000624 |		// 打开表
| init                 | 0.000116 |		// 初始化
| System lock          | 0.000154 |		// 系统??
| optimizing           | 0.000039 |		// 优化
| statistics           | 0.000047 |		// 统计
| preparing            | 0.000164 |		// 准备
| executing            | 0.000007 |		// 执行
| Sending data         | 0.012874 |		// 发送+接收数据
| end                  | 0.000008 |		// 结束
| query end            | 0.000019 |		// 查询结束标志
| closing tables       | 0.000032 |		// 关闭表
| freeing items        | 0.000017 |		// 释放信息
| logging slow query   | 0.000917 |		// 记录慢查询日志
| cleaning up          | 0.000078 |		// 清理
+----------------------+----------+

我的博客即将同步至腾讯云+社区，邀请大家一同入驻：https://cloud.tencent.com/developer/support-plan?invite_code=3ce1xz2ij8ys0

MySQL数据索引与优化

上一篇：Ubuntu 配置数据库开发环境(mysql oracle mssqlserver sybase)


下一篇：CentOS 8.2安装DB2_10.5.0.3步骤及错误解决