MySQL索引
MySQL 正确建立索引
在实际项目场景中,当SQL查询性能较慢时,我们常常会有一个疑惑:表中哪个字段建立一个索引能带来最大的性能收益呢?一般来说,判断字段是否要添加的索引的依据,是看这个字段是否被经常当做查询条件使用。 但也不能光依靠这一个依据来判断,比如用户表中的性别字段,就会经常被用做查询条件,但如果对性别字段建立一个索引,那对查询的性能提升并不大,因为性别就两个值:男/女,那对其建立索引,索引文件中就只会有两个索引节点。这种情况下,为性别建立一个索引,带来的性能收益显然不是太大。 ### 索引查询时的回表问题 什么叫做回表呢?意思就是指一条SQL语句在MySQL内部,要经过两次查询过程才能获取到数据。  在上图用户表中,基于ID字段先建立了一个主键索引,然后又基于name字段建立了一个普通索引,此时MySQL默认会选用主键索引作为聚簇索引,将表数据和主键索引存在同一个文件中,也就是主键索引的每个索引节点,都直接对应着行数据。而基于name字段建立的索引,其索引节点存放的则是指向聚簇索引的ID值。 在这种情况下,假设有一条下述SQL,其内部查询过程是啥样的呢? ```sql SELECT * FROM `zz_user` WHERE name = "子子"; ``` 首先会走name字段的索引,然后找到对应的ID值,然后再基于查询到的ID值,再走ID字段的主键索引,最终得到一整条行数据并返回。 在这个案例中,一条查询SQL经历了两次查询才获取到数据,这个过程则被称之为回表。 回表动作会导致额外的查询开销,因此尽量可以基于主键做查询,如果实在需要使用非主键字段查询,那么尽量要写明查询的结果字段,而并非使用*。 实际情况中建立联合索引,利用索引覆盖特性,从而避免使用辅助索引,这样也能够消除回表动作。 ### 建立索引时需要遵守的原则 当建立索引仅考虑一个字段是否被经常用于查询是不够的,往往一个合适的索引需要更为细致与长远的思考,例如使用多个字段建立是否会更好?创建其他类型的索引性能是否会更佳?下面我们就一起来看看建立索引时,需要遵守的一些原则: - ①经常频繁用作查询条件的字段应酌情考虑为其创建索引。 - ②表的主外键或连表字段,必须建立索引,因为能很大程度提升连表查询的性能。 - ③建立索引的字段,一般值的区分性要足够高,这样才能提高索引的检索效率。 - ④建立索引的字段,值不应该过长,如果较长的字段要建立索引,可以选择前缀索引。 - ⑤建立联合索引,应当遵循最左前缀原则,将多个字段之间按优先级顺序组合。 - ⑥经常根据范围取值、排序、分组的字段应建立索引,因为索引有序,能加快排序时间。 - ⑦对于唯一索引,如果确认不会利用该字段排序,那可以将结构改为Hash结构。 - ⑧尽量使用联合索引代替单值索引,联合索引比多个单值索引查询效率要高。 同时,除开上述一些建立索引的原则外,在建立索引时还需有些注意点: - ❶值经常会增删改的字段,不合适建立索引,因为每次改变后需维护索引结构。 - ❷一个字段存在大量的重复值时,不适合建立索引,比如之前举例的性别字段。 - ❸索引不能参与计算,因此经常带函数查询的字段,并不适合建立索引。 - ❹一张表中的索引数量并不是越多越好,一般控制在3,最多不能超过5。 - ❺建立联合索引时,一定要考虑优先级,查询频率最高的字段应当放首位。 - ❻当表的数据较少,不应当建立索引,因为数据量不大时,维护索引反而开销更大。 - ❼索引的字段值无序时,不推荐建立索引,因为会造成页分裂,尤其是主键索引。 ### 联合索引的最左前缀原则 >⑧尽量使用联合索引代替单值索引,联合索引比多个单值索引查询效率要高。 基于X、Y、Z字段建立了一个联合索引,实际上也相当于建立了三个索引:X、X、Y、X、Y、Z,因此只要查询中使用了这三组字段,都可以让联合索引生效。 >但如若查询中这三个字段不以AND形式出现,而是单独作为查询条件出现,那单值索引性能会好一些,但三个不同的索引,维护的代价也会高一些。 其实联合索引的最左前缀原则,道理很简单的,就是组成联合索引的多个列,越靠左边优先级越高,同时也只有SQL查询条件中,包含了最左的字段,才能使用联合索引。 >❺建立联合索引时,一定要考虑优先级,查询频率最高的字段应当放首位。 因为将查询频率越高的字段放首位,就代表着查询时命中索引的几率越大。同时,MySQL的最左前缀原则,才匹配到范围查询时会停止匹配,比如>、<、between、like这类范围条件,并不会继续使用联合索引。举个栗子: ```sql SELECT * FROM tb WHERE X="..." AND Y > "..." AND Z="..."; ``` 当执行时,虽然上述SQL使用到X、Y、Z作为查询条件,但由于Y字段是>范围查询,因此这里只能使用X索引,而不能使用X、Y或X、Y、Z索引。 >SQL是否走索引查询跟where后的条件顺序无关,因为MySQL优化器会优化,对SQL查询条件进行重排序。 比如下面这个例子,如果建立了联合索引user(name,age),都会利用到索引 ```sql select * from user where name = '子子' and age = 18; select * from user where age = 18 and name = '子子'; ```
顶部
收展
底部
[TOC]
目录
MySQL索引机制概述
MySQL的索引分类
MySQL索引的创建使用方式
MySQL各索引的优劣分析
MySQL索引失效
MySQL 正确建立索引
MySQL 正确使用索引
相关推荐
MySQL教程
MySQL命令
MySQL事务
MySQL锁机制
MySQL版本特性