Mysql 索引该如何设计与优化

2023-07-04 12:16:03 156

什么是索引？

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

普通索引：最基本的索引，没有任何限制
唯一索引：与”普通索引“类似，不同的就是：索引列的值必须是唯一，但允许有空值
主键索引：它是一种特殊的索引，不允许有空值
全文索引：仅可用于MyISAM表，针对较大的数据，生成全文索引很耗时占空间
组合索引：为了提高多条件查询效率，可建立组合索引，遵循"最左前缀匹配原则"

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

最左前缀匹配原则

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

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

如何计算key_len

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

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

在计算key_len之前，先来温习一下基本数据类型（以UTF8编码为例）：

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

测试数据表如下：

CREATETABLE`test_table`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`a`int(11)DEFAULTNOTNULL,
`b`int(11)DEFAULTNOTNULL,
`c`int(11)DEFAULTNOTNULL,
PRIMARYKEY(`id`),
KEY`test_table_a_b_c_index`(`a`,`b`,`c`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

命中索引：

mysql>explainselect*fromtest_tablewherea=1andb=2andc=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|Usingindex|
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+

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

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

mysql>ALTERTABLE`test_table`CHANGE`a``a`INT(11)NULL;
mysql>ALTERTABLE`test_table`CHANGE`c``c`INT(11)NULL;
mysql>ALTERTABLE`test_table`CHANGE`b``b`INT(11)NULL;
mysql>explainselect*fromtest_tablewherea=1andb=2andc=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|Usingindex|
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+

可以看到，当字段允许为空时，这时的key_len变成了15=43+13（INT类型为空时，额外占用一个字节）。

索引优化

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

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

SQL语句	是否索引
explainselect*fromtest_tablewherea=1andb=2andc=3;	Extra:Usingindexkey_len:15
explainselect*fromtest_tablewherea=1andb=2andc=3orderbyc;	Extra:Usingindexkey_len:15
explainselect*fromtest_tablewhereb=2andc=3;	Extra:Usingwhere;Usingindexkey_len:15
explainselect*fromtest_tablewherea=1orderbyc;	Extra:Usingwhere;Usingindex;Usingfilesortkey_len:5
explainselect*fromtest_tableorderbya,b,c;	Extra:Usingindexkey_len:15
explainselect*fromtest_tableorderbya,b,cdesc;	Extra:Usingindex;Usingfilesortkey_len:15
explainselect*fromtest_tablewhereain(1,2)andbin(1,2,3)andc=1;	Extra:Usingwhere;Usingindexkey_len:15

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

SQL1可以使用覆盖索引，性能好
SQL2可以使用覆盖索引，同时避免排序，性能好
SQL3可以使用覆盖索引，但是需要根据where字句进行过滤
SQL4可以使用部分索引a，但无法避免排序，性能差
SQL5可以完全使用覆盖索引，同时可以避免排序，性能好
SQL6可以使用覆盖索引，但无法避免排序，（这是因为 MySQLInnoDB 创建索引时默认asc升序，索引无法自动倒序排序）
SQL7可以使用覆盖索引，但是需要根据 where 子句进行过滤（非定值查询）

创建索引规范

考虑到索引维护的成本，单张表的索引数量不超过 5 个，单个索引中的字段数不超过 5 个
不在低基数列上建⽴索引，例如“性别”。在低基数列上创建的索引查询相比全表扫描不一定有性能优势，特别是当存在回表成本时。
合理创建联合索引，(a,b,c)相当于(a)、(a,b)、(a,b,c)。
合理使用覆盖索引减少IO，避免排序。

以上就是Mysql索引该如何设计与优化的详细内容，更多关于MySQL索引设计与优化的资料请关注毛票票其它相关文章！

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。