索引的创建与设计原则
1.索引的声明与使用
1.1索引的分类
- 从功能逻辑上说,索引主要有四种,分别为普通索引、唯一索引、主键索引、全文索引。
- 按照物理实现方式,索引可分为2种:聚簇索引和非聚簇索引。
- 按照作用字段个数进行划分,分为单列索引和联合索引。
1.2创建索引(表已创建)
索引可以在创建表的时候一起创建索引,这种方式这里就不记录,如果感兴趣,可自行百度。
①方式一
ALTER TABLE 表名 ADD INDEX 索引名(字段);
②方式二
CREATE INDEX 索引名 ON 表名(字段)
1.3查看索引
SHOW INDEX FROM 表名;
1.4删除索引
①方式一
ALTER TABLE 表名 DROP INDEX 索引名
②方式二
DROP INDEX 索引名 ON 表名;
2.MySQL8.0新特性
1.1降序索引
MySQL5.7及以前添加的降序索引是通过反向扫描的方式,并没有真正的创建,MySQL8.0真正创建了降序索引。
1.2隐藏索引
MySQL8.0可以让索引隐藏起来失去作用,如果删除索引后发现问题,还可以马上回复,节约了重新创建浪费的大量时间。要注意的是隐藏索引同样会消耗插入、更新、删除的性能,所以如果长期不使用就将隐藏索引删除掉。
3.索引的设计原则
3.1哪些情况适合创建索引
1. 字段的数值有唯一性的限制
2. 频繁的作为WHERE查询条件的字段
3. 经常GROUP BY 和 ORDER BY 的列
4. UPDATE、DELETE的WHERE条件列
5. DISTINCT字段需要创建索引
6. 多表JOIN连接操作时,创建索引注意事项
首先,连接表的数量尽量不要超过3张,每增加一张表就相当于增加了一次嵌套的循环,数量级增长会非常快,严重映像查询的效率。
其次,对于WHERE 条件创建索引,因为WHERE才是对数据条件的过滤,如果在数据量非常大的情况下,没有WHERE条件过滤是非常可怕的。
最后,对于连接的字段创建索引,并且该字段在多张表中的类型必须一致,不一致会自动使用类型转换函数,无法使用索引。
7. 使用列的类型小的创建索引
数据大小指的是该类型表示的数据范围的大小,TINYINT、MEDIUMINT、INT、BIGINT占用内存空间依次增大。
- 数据类型越小,查询进行比较操作越快。
- 数据类型越小,索引占用的存储空间越少,在一个数据页就可以放下更多的记录,从而减少磁盘的I/O带来的性能损耗,也就意味着把更多的数据页存到内存中,从而加快读写效率。
8. 使用字符串前缀创建索引
ALibaba 强制 在varchar字段建立索引时,必须指定索引长度,没必要对全字段建立索引,根据文本区分决定索引长度。
- B+树索引把整个的字符串保存起来,更费时,而且字符串越长占用空间越大。
- 如果B+树索引中,索引列存储字符串越长,那么在做字符串比较时会占用更多时间。
可以通过截取字段一部分内容建立索引,这个叫做前缀索引,这样也可以找到对应的字符串。
对于截取多少的问题,有一个公式count(distinct left(列名, 索引长度)) / count(*)
,这个值越接近1越好,当然如果几个索引长度所对应的值都差不多大,取索引长度比较小的。
因为索引前缀是对截取的部分字段作为索引,所以无法对整个字段使用索引排序。
9. 区分度高(散列度高)的列适合作为索引
散列度公式count(distinct 字段名)/count(*)
10. 使用频繁的列放到联合索引的左侧
由于最左前缀原则,,可以增加联合索引的使用率
11. 在多个字段都要创建索引的情况下,联合索引优于单值索引
3.2限制索引的数目
索引不是越多越好,建议一张表最多不超过6个,原因:
①索引占用磁盘空间,索引越多磁盘空间越大。
②索引影响INSERT、DELETE、UPDATE等语句性能,表中数据更改的同时,索引也要更改,会造成负担。
③优化器在选择如何优化查询时,会根据统一信息,对每个可以用到的索引进行评估,如果有多个索引可以用于查询,会增加SQL优化器生成执行计划的时间,降低查询性能。