文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

Mysql 索引该如何设计与优化

2022-05-28 04:46

关注

什么是索引?

数据库索引是一种数据结构,它以额外的写入和存储空间为代价来提高数据库表上数据检索操作的速度。通俗来说,索引类似于书的目录,根据其中记录的页码可以快速找到所需的内容。——维基百科
常见索引有哪些?

这里以相对复杂的组合为例,介绍如何优化。

最左前缀匹配原则

首先我们要知道什么是最左前缀匹配原则。

最左前缀匹配原则是指在使用 B+Tree 联合索引进行数据检索时,MySQL 优化器会读取谓词(过滤条件)并按照联合索引字段创建顺序一直向右匹配直到遇到范围查询或非等值查询后停止匹配,此字段之后的索引列不会被使用,这时计算 key_len 可以分析出联合索引实际使用了哪些索引列。

如何计算 key_len

通过 key_len 计算也帮助我们了解索引的最左前缀匹配原则。

key_len 表示得到结果集所使用的选择索引的长度[字节数],不包括 order by,也就是说如果 order by 也使用了索引则 key_len 不计算在内。

在计算 key_len 之前,先来温习一下基本数据类型(以UTF8 编码为例):

类型 所占空间 不允许为NULL额外占用
char 一个字符三个字节 一个字节
varchar 一个字符三个字节 一个字节
int 四个字节 一个字节
tinyint 一个字节 一个字节

测试数据表如下:


CREATE TABLE `test_table` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NOT NULL,
 `b` int(11) DEFAULT NOT NULL,
 `c` int(11) DEFAULT NOT NULL,
 PRIMARY KEY (`id`),
 KEY `test_table_a_b_c_index` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

命中索引:


mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys     | key          | key_len | ref        | rows | filtered | Extra    |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE   | test_table | NULL    | ref | test_table_a_b_c_index | test_table_a_b_c_index | 12   | const,const,const |  1 |  100.00 | Using index |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+

可以看到 key_len = 12,这是如何计算的呢?
因为字符集是 UTF8,一个字段占用四个字节,三个字段就是 4 * 3 = 12 字节。

是否允许为 NULL,如果允许为 NULL,则需要用额外的字节来标记该字段,不同的数据类型所需的字节大小不同。


mysql> ALTER TABLE `test_table` CHANGE `a` `a` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `c` `c` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `b` `b` INT(11) NULL;
mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys     | key          | key_len | ref        | rows | filtered | Extra    |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE   | test_table | NULL    | ref | test_table_a_b_c_index | test_table_a_b_c_index | 15   | const,const,const |  1 |  100.00 | Using index |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+

可以看到,当字段允许为空时,这时的key_len 变成了15 = 4 3 + 1 3(INT 类型为空时,额外占用一个字节)。

索引优化

有了这些基础知识之后,再来根据实际的SQL 判断索性性能好坏。

还是以上面那张数据表为例,为 a、b、c 三个字段创建联合索引。

SQL 语句 是否索引
explain select * from test_table where a = 1 and b = 2 and c = 3; Extra:Using index key_len: 15
explain select * from test_table where a = 1 and b = 2 and c = 3 order by c; Extra:Using index key_len: 15
explain select * from test_table where b = 2 and c = 3; Extra:Using where; Using index key_len: 15
explain select * from test_table where a = 1 order by c; Extra:Using where; Using index; Using filesort key_len: 5
explain select * from test_table order by a, b, c; Extra:Using index key_len: 15
explain select * from test_table order by a, b, c desc; Extra:Using index; Using filesort key_len:15
explain select * from test_table where a in (1,2) and b in (1,2,3) and c = 1; Extra:Using where; Using index key_len: 15

通常在查看执行计划时, Extra 列为 Using index 则表示优化器使用了覆盖索引。

创建索引规范

以上就是Mysql 索引该如何设计与优化的详细内容,更多关于MySQL 索引设计与优化的资料请关注自学编程网其它相关文章!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯