MySQL索引
MySQL各索引的优劣分析
有句古话曾说过:“凡事有利必有弊”,MySQL的索引机制也不例外,引入索引机制后,能够给数据库带来的优势很明显: - ①整个数据库中,数据表的查询速度直线提升,数据量越大时效果越明显。 - ②通过创建唯一索引,可以确保数据表中的数据唯一性,无需额外建立唯一约束。 - ③在使用分组和排序时,同样可以显著减少SQL查询的分组和排序的时间。 - ④连表查询时,基于主外键字段上建立索引,可以带来十分明显的性能提升。 - ⑤索引默认是B+Tree有序结构,基于索引字段做范围查询时,效率会明显提高。 - ⑥从MySQL整体架构而言,减少了查询SQL的执行时间,提高了数据库整体吞吐量。 同时也会带来一系列弊端,如: - ①建立索引会生成本地磁盘文件,需要额外的空间存储索引数据,磁盘占用率会变高。 - ②写入数据时,需要额外维护索引结构,增、删、改数据时,都需要额外操作索引。 - ③写入数据时维护索引需要额外的时间开销,执行写SQL时效率会降低,性能会下降。 当然,但对数据库整体来说,索引带来的优势会大于劣势。不过也正由于索引存在弊端,因此索引不是越多越好,合理建立索引才是最佳选择。 ### 主键索引存在的陷阱 主键一般都是使用自增ID,但这是为什么呢?有人可能会回答自增ID不会重复,确保了主键唯一性。这样也确实没错,但不会重复的又不仅仅只有自增ID,比如我使用随机的UUID也不会重复,为何不使用UUID呢?这是由于索引存在一个陷阱! 主键索引是聚簇索引,表数据和索引数据在一块、索引结构是有序的,那再反推前面给出的疑惑,为何不使用UUID呢?因为UUID是无序的,如果使用UUID作为主键,那么每当插入一条新数据,都有可能破坏原本的树结构。 但使用自增ID就不会有这个问题,所有新插入的数据都会放到最后。 ### 联合索引存在的矛盾 为了多条件查询时的效率更高,一般都会同时对多个字段建立联合索引,但之前也聊到过,联合索引存在一个致命的问题,比如在用户表中,通过id、name、age三个字段建立一个联合索引,此时来了一条查询SQL,如下: ```sql SELECT * FROM `zz_user` WHERE name = "竹子" AND age = "18"; ``` 而这条SQL语句是无法使用联合索引的,为什么呢?因为查询条件中,未包含联合索引的第一个字段,想要使用联合索引,那么查询条件中必须包含索引的第一个字段,如下: ```sql SELECT * FROM `zz_user` WHERE name = "竹子" AND id = 6; ``` 上面这条SQL才是能命中多列索引的语句,因此在建立索引时也需要考虑这个问题,确保建立出的联合索引能够命中率够高。 ### 前缀索引存在的弊端 前缀索引的特点是短小精悍,我们可以利用一个字段的前N个字符创建索引,以这种形式创建的索引也被称之为前缀索引,相较于使用一个完整字段创建索引,前缀索引能够更加节省存储空间,当数据越多时,带来的优势越明显。 不过前缀索引虽然带来了节省空间的好处,但也正由于其索引节点中,未存储一个字段的完整值,所以MySQL也无法通过前缀索引来完成ORDER BY、GROUP BY等分组排序工作,同时也无法完成覆盖扫描等操作。 ### 全文索引存在的硬伤 之前做模糊查询时,通常都会使用like%语法,不过这种方式虽然能够实现效果,但随着表越来越大,数据越来越多时,其性能会出现明显下降,而全文索引的推出则能够完美解决该问题,可以利用全文索引代替like%语法实现模糊查询,它的性能会比like%快上N倍。 全文索引虽然可以实现模糊查询,但也存在一系列硬伤: - ①由于全文索引是基于分词实现的,所以对一个字段建立全文索引后,MySQL会对该字段做分词处理,这些分词结果也会被存储在全文索引中,因此全文索引的文件会额外的大! - ②由于全文索引对每个字段值都会做分词,因此当修改字段值后,分词是需要时间的,所以修改字段数据后不会立马自动更新全文索引,此时需要咱们写存储过程,并调用它手动更新全文索引中的数据。 - ③除开上述两点外,全文索引最大的硬伤在于对中文支持不够友好,类似于英文可以直接通过符号、空格来分词,但中文呢?一个词语来形容就是博大精深,无法精准的对一段文字做分词,因此全文索引在检索中文时,存在些许精准度问题。 因此如果你项目规模较大,通常再引入ElasticSearch、Solr、MeiliSearch等搜索引擎是一个更佳的选择。 ### 唯一索引存在的快慢问题 唯一索引有个很大的好处,就是查询数据时会比普通索引效率更高,因为基于普通索引的字段查询数据,例如: ```sql SELECT * FROM TABLE_XX WHERE COLUMN_XX = "XX"; ``` 假设COLUMN_XX字段上建立了一个普通索引,此时基于这个字段查询数据时,当查询到一条COLUMN_XX = "XX"的数据后,此时会继续走完整个索引树,因为可能会存在多条字段值相同的数据。 但如果COLUMN_XX字段上建立的是唯一索引,当找到一条数据后就会立马停下检索,因此本身建立唯一索引的字段值就具备唯一性。 因此唯一索引查询数据时,会比普通索引快上一截,但插入数据时就不同了,因为要确保数据不重复,所以插入前会检查一遍表中是否存在相同的数据。但普通索引则不需要考虑这个问题,因此普通索引的数据插入会快一些。 ### 哈希索引的致命问题 哈希索引,也就是数据结构为Hash类型的索引,不过估计大家接触的比较少,毕竟创建索引时都默认用的B+树结构。但要比起查询速度,哈希索引绝对是MySQL中当之无愧的魁首!因为采用哈希结构的索引,会以哈希表的形式存储索引字段值,当基于该字段查询数据时,只需要经过一次哈希计算就可获取到数据。 >但哈希结构的致命问题在于无序,也就是无法基于哈希索引的字段做排序、分组等工作。 因此如果你确定一个表中,不会做排序这类的工作,那可以适当选用哈希结构作为索引的数据结构,它会给你带来意想不到的性能收益~
顶部
收展
底部
[TOC]
目录
MySQL索引机制概述
MySQL的索引分类
MySQL索引的创建使用方式
MySQL各索引的优劣分析
MySQL索引失效
MySQL 正确建立索引
MySQL 正确使用索引
相关推荐
MySQL教程
MySQL命令
MySQL事务
MySQL锁机制
MySQL版本特性