索引的创建与设计原则

索引分类

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、不要定义冗余或重复的索引

posted @   半条咸鱼  阅读(370)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示