索引的创建与设计原则
索引分类
1、按照功能逻辑:普通索引、唯一索引、主键索引、全文索引
2、按照物理实现方式:聚簇索引、非聚簇索引
3、按照作用字段个数:单列索引、联合索引
4、总共 7 种
(1)普通索引
(2)唯一性索引
(3)主键索引
(4)单列索引
(5)多列(组合、联合)索引
(6)全文索引
(7)空间索引
5、不同的存储引擎支持的索引类型不同
(1)InnoDB:支持 B-tree、Full-text 等,不支持 Hash
(2)MyISAM: 支持 B-tree、Full-text 等,不支持 Hash
(3)Memory:支持 B-tree、Hash 等,不支持 Full-text
(4)NDB:支持 Hash 索引,不支持 B-tree、Full-text 等
(5)Archive:不支持 B-tree、Hash、Full-text 等
显式创建表时,创建索引
CREATE TABLE 表名 (
[列名 数据类型],
……
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC],
);
1、UNIQUE、FULLTEXT、SPATIAL 为可选参数,分别表示唯一索引、全文索引、空间索引
2、INDEX 与 KEY 为同义词,两者的作用相同,用来指定创建索引,不指定(1)表示普通索引
3、index_name:指定索引的名称,为可选参数,如果不指定,MySQL 默认 col_name 为索引名
4、col_name:为需要创建索引的字段列,该列必须从数据表中定义的多个列中选择
5、length:为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度
6、ASC 或 DESC 指定升序或降序的索引值存储
7、主键索引:PRIMARY KEY
8、全文索引
(1)MATCH + AGAINST 方式查询
SELECT 列名1, 列名2…… FROM 表名 WHERE MATCH(列名1, 列名2,……) AGAINST ('查询字符串');
(2)全文索引比 LIKE + % 快,但可能存在精度问题
(3)如果需要全文索引的是大量数据,建议先添加数据,再创建索引
(4)在 MySQL 5.7 及之后版本中,可以不指定最后 ENGINE,因为在此版本中 InnoDB 支持全文索引
9、创建空间索引:要求空间类型的字段必须为非空
在已经存在的表上创建索引
1、ALTER TABLE
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[索引名] (列名 [length],……) [ASC | DESC];
2、CREATE INDEX
(1)在 MySQL 中,CREATE INDEX 被映射到 ALTER TABLE 上
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名
ON 表名 (列名 [length],……) [ASC | DESC];
删除索引
1、ALTER TABLE
ALTER TABLE 表名 DROP INDEX 索引名;
2、DROP INDEX
DROP INDEX 索引名 ON 表名;
3、删除表中的列时
(1)如果要删除的列,为索引的组成部分,则该列也会从索引中删除
(2)如果组成索引的所有列都被删除,则整个索引将被删除
MySQL 8.0 索引新特性
1、支持降序索引
2、隐藏索引
降序索引
1、MySQL 5.7,使用 Using filesort
(1)Using filesort 是 MySQL 中一种速度比较慢的外部排序
(2)多数情况下,通过优化索引来尽量避免出现 Using filesort,从而提高数据库执行速度
2、MySQL 8.0,没有使用 Using filesort
3、降序索引只对查询中特定的排序顺序有效,如果使用不当,反而查询效率更低
隐藏索引
1、在 MySQL 5.7 版本及之前,只能通过显式的方式删除索引
(1)如果发现删除索引后出现错误,只能通过显式创建索引的方式将删除的索引创建回来
(2)如果数据表中的数据量非常大,或数据表本身比较大,这种操作就会消耗系统过多的资源,操作成本非常高
2、从 MySQL 8.x 开始支持隐藏索引
(1)只需要将待删除的索引设置为隐藏索引,使查询优化器不再使用这个索引
(2)即使使用 force index(强制使用索引),优化器也不会使用该索引
3、软删除:先将索引设置为隐藏索引,确认将索引设置为隐藏索引后,系统不受任何响应,再彻底删除索引
4、创建表时创建
CREATE TABLE 表名 (
[列名 数据类型],
……
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC] INVISIBLE
);
5、在已经存在的表上创建
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[索引名] (列名 [length],……) [ASC | DESC] INVISIBLE;
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名
ON 表名 (列名 [length],……) [ASC | DESC] INVISIBLE;
6、切换索引可见状态
#切换为隐藏索引
ALTER TABLE 表名 ALTER INDEX 索引名 INVISIBLE;
#切换成非隐藏索引
ALTER TABLE 表名 ALTER INDEX 索引名 VISIBLE;
7、当索引被隐藏时,它的内容仍然是和正常索引实时更新
(1)如果一个索引需要长期被隐藏,那么可以将其删除,因为索引的存在会影响插入、更新、删除的性能
(2)通过设置隐藏索引的可见性,可以查看索引对调优的帮助
8、使隐藏索引对查询优化器可见
(1)在 MySQL 8.x 版本中,为索引提供了一种新的测试方式,可以通过查询优化器的一个开关(use_invisible_indexes)来打开某个设置,使隐藏索引对查询优化器可见
(2)如果 use_invisible_indexes 设置为 off(默认),优化器会忽略隐藏索引;如果设置为 on,即使隐藏索引不可见,优化器在生成执行计划时仍会考虑使用隐藏索引
(3)查看查询优化器的开关设置
SELECT @@optimizer_switch \G
(4)设置查询优化器对隐藏索引的可见性
#可见
SET session optimizer_switch="use_invisible_indexes=on";
#不可见
SET session optimizer_switch="use_invisible_indexes=on";
适合创建索引的字段
1、字段数值有唯一性限制
(1)索引本身可以起到约束的作用,唯一索引、主键索引都可以起到唯一性约束
(2)如果某个字段是唯一,可以直接创建唯一性索引,或主键索引,可以更快速地通过该索引来确定某条记录
(3)业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引
(4)可以忽略唯一索引影响插入性能,但明显提高查找速度
2、频繁作为 WHERE 查询条件的字段
(1)数据量大的情况下,创建普通索引,可以大幅提升数据查询效率
3、经常 GROUP BY 和 ORDER BY 的列
(1)如果待排序的列有多个,可以在这些列上建立组合索引
4、UPDATE、DELETE 的 WHERE 条件列
(1)对数据按照某个条件进行查询后,再进行 UPDATE 或 DELETE 操作,如果对 WHERE 字段创建索引,就能大幅提升性能
(2)因为需要先根据 WHERE 条件列检索的记录,然后再对它进行更新或删除
(3)如果进行更新时,更新的字段是非索引字段,提升的效率会更明显,因为非索引字段更新不需要对索引进行维护
5、DISTINCT 字段
6、多表 JOIN 连接操作时,创建索引注意事项
(1)连接表的数量不要超过 3 张,每增加一张表,相当于增加一次嵌套的循环,数量级增长会非常快,严重影响查询效率
(2)对 WHERE 条件创建索引
(3)对用于连接的字段创建索引,并且该字段在多张表中的类型必须一致
7、使用列的类型小的创建索引
(1)类型大小:类型表示的数据范围的大小
(2)数据类型越小,在查询时进行的比较操作越快
(3)数据类型越小,索引占用的存储空间就越少,在一个数据页内就可以放下更多的记录,从而减少磁盘 I/O 带来的性能损耗,即可以把更多的数据页缓存在内存中,从而加快读写效率
(4)更加适用表的主键,不仅是聚簇索引中会存储主键值,其他所有的二级索引的节点处都会存储一份记录的主键值,如果主键使用更小的数据类型,即节省更多的存储空间和更高效 I/O
8、使用字符串前缀创建索引
(1)假设字符串很长,那存储一个字符串就需要占用很大的存储空间
(2)B+ 树索引中的记录存储该列的完整字符串,更费时,而且字符串越长,在索引中占用的存储空间越大
(3)B+ 树索引中索引列存储的字符串很长,则在字符串比较时,占用更多的时间
(4)前缀索引:通过截取字段的前面一部分内容建立索引
(5)在查找记录时,不能精确定位到记录的位置,但能定位到相应前缀所在位置,然后根据前缀相同的记录的主键值,回表查询完整的字符串值
(6)节约空间,减少字符串的比较时间,大体解决排序问题
(7)计算不同长度下的区分度的公式,索引长度即截取前缀长度,结果越接近于 1,区分度更好
SELECT COUNT(DISTINCT LEFT(列名, 索引长度)) / COUNT(*) AS 区分度
FROM 表名;
(8)在 VARCHAR 字段上建立索引时,必须指定索引长度,不必对全字段建立索引,根据实际文本区分度决定索引长度
(9)索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90% 以上
(10)索引列前缀对排序的影响:前缀长度与 LIMIT 长度相同,且恰好存在多个相同前缀,则 ORDER BY 不能得到正确结果
9、区分度高(散列性高)的字段
(1)列的基数:某一列中不重复数据的个数
(2)在记录行数一定的情况下,列的基数越大,该列中的值越分散;列的基数越小,该列中的值越集中
(3)列的基数直接影响是否能有效利用索引
(4)以列的基数大的列建立索引
(5)区分度公式,结果越接近 1 越好,一般超过 33% 算较高效索引
SELECT COUNT(DISTINCT LEFT(列名, 索引长度)) / COUNT(*) AS 区分度
FROM 表名;
(6)联合索引把区分度高(散列性高)的列放在前面
10、使用最频繁的列放到联合索引的左侧
(1)可以较少的建立一些索引
(2)由于最左前缀原则,可以增加联合索引的使用率
11、在多个字段都要创建索引的情况下,联合索引优于单值索引
限制索引的数目
1、需要限制每张表上的索引数量,建议单张表索引数量不超过 6 个
2、每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大
3、索引会影响 INSERT、DELETE、UPDATE 等语句性能,因为表中的数据更改的同时,索引也会进行调整和更新,造成负担
4、优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,会增加 MySQL 优化器生成执行计划时间,降低查询性能
不适合创建索引的情况
1、WHERE、GROUP BY、ORDER BY 条件中用不到的字段不需要创建索引
2、数据量小的表不使用索引
(1)在数据表中的数据行数不到 1000 行,不需要创建索引
(2)查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果
3、有大量重复数据的列不使用索引
(1)数据重复度高于 10% 时,不需要对这个字段使用索引
(2)数据重复度过高,若建立索引,不会提高查询效率,反严重降低数据更新速度
4、避免对经常更新的表创建过多的索引
(1)更新数据时,也需要更新索引,如果索引太多,在更新索引时,也会造成负担,从而影响效率
(2)索引中的列尽可能少,提高查询速度,同时降低更新表的速度
5、不使用无序的值作为索引
(1)身份证、UUID(在索引比较时需要转为 ASCII,并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等
6、删除不再使用或很少使用的索引,减少索引对更新操作的影响
7、不要定义冗余或重复的索引
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战