[Mysql教程系列]介绍一下MySQL索引设计规范

最新推荐文章于 2024-07-25 08:25:52 发布

2401_85766992

最新推荐文章于 2024-07-25 08:25:52 发布

阅读量993

点赞数 7

文章标签： mysql 设计规范 adb

本文链接：https://blog.csdn.net/2401_85766992/article/details/139758465

版权

点击上方“Coder编程”，选择“置顶公众号”

技术文章第一时间送达！

![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9kQ0NqMWJ5ajgwdGNpYUhUQmg1aWFJZ3JuWWljMmpQSlNrazJNOG9rWGc5QlJTcW5Da0lFRjBwcTJ1SXV3dVo3dVkwUGtJV2JyWW9JZmliNmc2UlpyQ2RpY3RnLzY0MA?x
-oss-process=image/format,png)

mysql.jpg

索引设计规范

MySQL的查询速度依赖良好的索引设计，因此索引对于高性能至关重要。合理的索引会加快查询速度（包括UPDATE和DELETE的速度，MySQL会将包含该行的page加载到内存中，然后进行UPDATE或者DELETE操作），不合理的索引会降低速度。MySQL索引查找类似于新华字典的拼音和部首查找，当拼音和部首索引不存在时，只能通过一页一页的翻页来查找。当MySQL查询不能使用索引时，MySQL会进行全表扫描，会消耗大量的IO。索引的用途：去重、加速定位、避免排序、覆盖索引。

什么是覆盖索引？

InnoDB存储引擎中，secondary index（非主键索引）中没有直接存储行地址，存储主键值。如果用户需要查询secondary index中所不包含的数据列时，需要先通过secondary index查找到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。覆盖索引的概念就是查询可以通过在一个索引中完成，覆盖索引效率会比较高，主键查询是天然的覆盖索引。合理的创建索引以及合理的使用查询语句，当使用到覆盖索引时可以获得性能提升。比如SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主键，适当时候可以将索引添加为index(uid,email)，以获得性能提升。

索引的基本规范

1、索引数量控制，单张表中索引数量不超过5个，单个索引中的字段数不超过5个。

综合评估数据密度和分布
考虑查询和更新比例

为什么一张表中不能存在过多的索引？

InnoDB的secondary index使用b+tree来存储，因此在UPDATE、DELETE、INSERT的时候需要对b+tree进行调整，过多的索引会减慢更新的速度。

2、对字符串使用前缀索引，前缀索引长度不超过8个字符，建议优先考虑前缀索引，必要时可添加伪列并建立索引。

不要索引blob/text等字段,不要索引大型字段,这样做会让索引占用太多的存储空间

什么是前缀索引？

前缀索引说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快。前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

建立前缀索引的语法：ALTER TABLE table_name ADD KEY(column_name(prefix_length));

3、主键准则

表必须有主键
不使用更新频繁的列
尽量不选择字符串列
不使用UUID MD5 HASH
默认使用非空的唯一键
建议选择自增或发号器

4、重要的SQL必须被索引，核心SQL优先考虑覆盖索索引

UPDATE、DELETE语句的WHERE条件列
ORDER BY、GROUP BY、DISTINCT的字段
多表JOIN的字段

5、区分度最大的字段放在前面

选择筛选性更优的字段放在最前面，比如单号、userid等，type，status等筛选性一般不建议放在最前面
索引根据左前缀原则，当建立一个联合索引(a,b,c)，则查询条件里面只有包含(a)或(a,b)或(a,b,c)的时候才能走索引,(a,c)作为条件的时候只能使用到a列索引,所以这个时候要确定a的返回列一定不能太多，不然语句设计就不合理,(b,c)则不能走索引
合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)

6、索引禁忌