网站建设是不是无形资产,优化大师免费下载,微信营销和微博营销的区别,关于营销型网站建设的建议文章目录概述MySQL 索引类型MySQL 索引方法BTREE 方法HASH 方法主键构成的索引结构主键索引的优点主键索引的缺点依赖顺序插入更新代价高索引使用指南索引树回顾索引树排序规则最左前缀法则最左前缀法则的产生依据最左前缀法则延申字段书写顺序不影响最左前缀法则最左前缀法则总…
文章目录概述MySQL 索引类型MySQL 索引方法BTREE 方法HASH 方法主键构成的索引结构主键索引的优点主键索引的缺点依赖顺序插入更新代价高索引使用指南索引树回顾索引树排序规则最左前缀法则最左前缀法则的产生依据最左前缀法则延申字段书写顺序不影响最左前缀法则最左前缀法则总结索引字段匹配时不要进行操作大于小于符号范围查询对于索引后面字段的影响其他的规则综合评估索引是否使用值辨别度低的字段不使用索引通配符开头的模糊查询不使用索引不等于操作不使用索引概述
在日常开发过程中为 MySQL 表中的字段建立索引是我们常用的性能优化手段。使用开发工具添加索引也很方便使用次数多了闭着眼睛都能完成操作。 但是细心一点的小伙伴会发现索引并不是那么简单
除了普通索引或者唯一索引还有哪些类型的索引它们的作用分别是什么呢BTREEHASH 索引方法的适用场景分别是什么呢为什么字段上已经建立了索引但是实际执行包含该字段的 SQL 时却没有走索引索引有哪些使用规则呢
下面请跟随着这篇文章一起得到这些问题对应的答案。
MySQL 索引类型
MySQL 的索引类型主要包括
NORMAL普通索引普通索引使用方法没有做特殊限制因此应用范围比较广UNIQUE唯一索引在普通索引的基础上增加了唯一约束功能。唯一约束表示建立在这种索引上的字段值将会在表中唯一一个或多个字段联合唯一FULLTEXT全文索引必须建立在**字符或文本类型如 varchar**的列上可以实现在大文本中搜索指定关键词的功能 注意使用全文索引时必须使用 match 和 against 关键字进行操作 SPATIAL空间索引必须建立在空间数据类型如 geometry且非空的列上可以实现空间数据查询的功能 上面就是 MySQL 所有的索引类型了在实际使用中需要结合实际情况需要来选择合适的索引类型。
MySQL 索引方法
MySQL 的索引方法主要包括 BTREE 和 HASH。 顾名思义BTREE 方法就是通过构建 B 树的方法来组织索引结构而 HASH 方法就是通过构建哈希表的方法来组织索引结构。
BTREE 方法
BTREE 方法是通过构建 B 树的方法来组织索引结构的。例如有这样一张表
CREATE TABLE user (id bigint NOT NULL AUTO_INCREMENT COMMENT 主键,name varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT 名称,age smallint NOT NULL COMMENT 年龄,register_date date NOT NULL COMMENT 注册日期,sex tinyint NOT NULL COMMENT 性别,address varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT 地址,phone varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 电话,PRIMARY KEY (id),KEY idx_name_age_registerdate (name,age,register_date) USING BTREE
) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_bin;可以看到在 user 表中有一个由 name、age、register_date 三个字段联合组成的普通索引。现在假设表中有一些这样的数据
那么由 BTREE 方法组织的这个索引的结构示意图只为示意请不要较真名字的真实字典序和实际的 B 树插入逻辑图为
可以看到这就是一颗 B 树且索引关键字之间的字段值与其在数中的实际顺序的对应关系一目了然这张图在后面讲解索引规则的时候也还会用到。
HASH 方法
HASH 方法下面简称哈希索引是通过构建哈希表的方法来组织索引结构的解决哈希冲突的方法为链地址法。 既然哈希索引是基于哈希表实现的那么它就继承了哈希表的所有优点和缺点。那么我们可以得出
哈希索引只在等值查询时才有效无法在范围查询时生效也不能应用于排序哈希索引不支持部分索引列匹配查找通俗地说就是如果想要用哈希索引就要用全而不是只用一部分字段当哈希碰撞的情况很严重时对哈希索引的维护和查询操作性能都会降低
主键构成的索引结构
InnoDB 存储引擎主键字段构成的索引结构是在 UNIQUE 索引类型 BTREE 索引方法基础上将实际数据存储在了叶子节点上。这样的索引结构我们称为主键索引或者聚簇索引。 聚簇这个词的含义是指为了提高某个属性或属性组的查询速度把这个或这些索引上具有相同值的元组几种存放在连续的物理块。 在这里是指索引与数据保存在同一个结构中因此这里的聚簇指的就是索引与数据的聚簇。 请注意主键索引或者说聚簇索引并不是一种索引类型而是一种将索引与数据保存在一个结构中的存储方式。 使用 InnoDB 作为存储引擎的表都是基于主键索引来构建的所以这就是为什么在使用 InnoDB 存储引擎时一定要定义一个主键的原因。 如果没有定义主键那么 InnoDB 会选择一个 UNIQUE 索引类型的索引作为主键索引如果没有InnoDB 会定义一个隐式的主键来作为主键索引。 总之使用 InnoDB 作为存储引擎的表一定会有一个主键如果开发者没有定义那么 MySQL 将会自己指定或者创建一个隐式列来作为主键。 这一小节的标题是主键构成的索引结构其实这个标题的名字可能不太好理解可能换成主键索引结构是大家普遍能接受的说法。 但是我并没有这样做因为主键索引是一个很突兀的概念它不是一种索引类型也不是一种索引方法而是一种索引与数据共存的存储方式。
主键索引的优点
主键索引的优点通常是与非主键索引进行对比得出的。最主要的一点就是在查询数据行时主键索引在大多数场景下比非主键索引高效。 因为如果使用了主键索引那么在 B 树中搜索到对应的叶子节点后可以直接获得该索引值对应的数据行。 而如果使用的是非主键索引那么在 B 树中搜索到对应的叶子节点后还需要通过叶子节点上存储的数据指针或者主键值再进行一次寻址或者根据主键在主键索引中再进行一次搜索才能得到真正的数据行。 总的来说就是在通常情况下主键索引会比非主键索引少一次查找数据行的过程。
主键索引的缺点
凡事都有两面性主键索引也不例外。
依赖顺序插入
主键索引的插入需要依赖顺序插入否则会发生页分裂而严重影响性能。在往 InnoDB 主键索引中插入数据时有以下三个规则
根据 B 树的性质主键值最终都会写入到叶子节点中而 InnoDB 将会把叶子节点写入到数据页中InnoDB 页与页之间的顺序是由主键的大小顺序决定的新创建的页中的主键值应该比之前创建页中的主键值都大且页中每条新写入的主键值应该比已经写入的主键值都大InnoDB 默认会将新写入的数据放在最新创建的页中 所以结合上面的规则可以得出InnoDB 隐式地约定了最新写入的主键需要比之前写入的所有主键值都要大。 那么如果新写入的数据的主键值不是当前最大的主键值那么 InnoDB 为了维护上面的规则将会在之前创建的页中寻找这个主键值应该存放的位置。那么如果旧页已经满了的情况下将会把这一页中的所有数据拆分为两个页或者合并临近的未满的页中这种拆分-合并页的现象就称之为页分裂。 在发生页分裂时InnoDB 将会把当前表锁住等页分裂完成后再释放锁所以页分裂是一种比较影响性能的现象。
更新代价高
当我们需要更新主键值时就可能涉及到主键值存放位置的变动就有可能出现页的拆分和合并即有可能发生页分裂。所以主键的更新代价是比较高的在日常开发中一般也会禁止更新主键值。
索引使用指南
在实际开发过程中主键索引的用法相对固定除主键索引外用的比较多的是由 BTREE 方法构建的索引所以我们接下来的讨论内容是由 BTREE 方法构建的非主键索引。在此章节中出现的索引如无特殊说明都指代由 BTREE 方法构建的非主键索引。
索引树回顾
让我们再回顾一下索引的数据存储示意图
基于上面这张图我们来梳理一下索引的使用规则
索引树排序规则
在介绍索引的使用规则之前我们先了解一下索引树的排序规则。索引树的排序规则主要指的是索引树节点的关键字的排序规则而在这里关键字代表的就是索引字段值那么索引树的排序规则实际上指的就是索引字段值的排序规则。 上面是由 name、age、register_date 三个字段组成的索引树那么这颗索引树的排序规则是什么样的呢 例如我们现在有如下四条数据
那么排序的规则为
首先按照 name 进行排序由于**“小美”** “小美” “小美” “美美”则有 当 name 相同时再按照 age 进行排序由于 22 48 48则有 当 name 和 age 都相等时再按照 register_date 进行排序由于 2018-07-10 2018-10-01则有 整合上面的所有顺序则最后的顺序为 由上面的流程我们可以得知索引字段值的顺序正是索引字段依次比较的结果。有了这个概念那么理解后面的索引使用规则就会容易得多。
最左前缀法则
最左前缀法则可能是大家最耳熟能详的索引使用规则了规则大家也都很清楚就是使用同一个索引的多个索引字段作为查询条件时必须从索引字段顺序的左边开始依次且不能跳过字段进行值匹配如果从左边开始有哪个字段没有出现在查询条件中那么从这个字段开始后面的字段查询都不能使用索引。
最左前缀法则的产生依据
为什么会有最左前缀法则它产生的依据是什么呢例如我想跳过 name 字段直接使用 age 字段作为索引树的查询条件这样为什么不能使用索引呢
我们知道如果数据集有序那么我们只要使用二分查找就能轻松把查询性能提升几个数量级反之如果在无序的数据集中进行查询那就只能把所有数据都遍历一遍了。利用索引树中的数据有序的性质才叫真正地使用索引。 那么我们仔细观察一下在跳过 name 字段后剩下两个字段还有序吗很显然排除掉 name 字段后的剩下两个字段值并不是有序的即整个索引树在排除掉 name 字段后并不是一个有序的数据集所以索引就不能提升查询性能了。
最左前缀法则延申
最左前缀法则其实不仅仅在索引字段之间有效在同一个字段的前缀查询中也是生效的即字段的前缀查询也满足最左前缀法则也可以使用索引。当然前提是得满足字段间的最左前缀 例如在本文讨论的索引结构中我们使用如下的几个 SQL 也是满足最左前缀法则的
-- 单独对 name 进行前缀查询
select * from user where name like 小%;-- 对于 name 进行等值查询对 age 进行前缀查询
select * from user where name 小美 and age like 2%;-- 对于 name 和 age 进行等值查询对 register_date 进行前缀查询
select * from user where name 小美 and age 22 and register_date like 2015%;但是在如下几个 SQL 中是不满足最左前缀法则的即不能或只能使用一部分使用索引
-- 对于 name 进行后缀查询完全不能使用索引
select * from user where name like %美;-- 对于 name 进行等值查询但是 age 进行后缀查询那么只能用到 name 的部分
select * from user where name 小美 and age like %2;-- 对于 name 和 age 进行等值查询但是 register_date 进行后缀查询那么只能用到 name age 的部分
select * from user where name 小美 and age 22 and register_date like %05;当然这里的最左前缀是建立在索引前面的字段是进行的等值查询的前提下进行讨论的。索引前面的字段不是等值查询的情况将在后面的章节中进行讨论。
字段书写顺序不影响最左前缀法则
在我初学 MySQL 的时候有人曾经问过我下面的这条 SQL 会走索引吗还是以本文中的索引为例
-- 查询条件
select * from user where age 22 and name 小美 and register_date 2015-03-05由于当时对于 MySQL 知识的一知半解我回答了一个不能现在想想仍为当时的自己感到尴尬。因为当时虽然知道最左前缀匹配但是却不知道 MySQL 的架构中有分析器和优化器的存在也就不知道其实查询条件中的字段书写顺序其实并不会影响最左前缀法则。 写到这里回忆起了这件事情所以把它写下来也算是记录一下在变强这条道路上踩过的一个脚印吧
最左前缀法则总结
最左前缀法则是索引最重要的使用规则之一也是制定高性能索引策略的基础。在日常开发过程中我们也要遵守最左前缀法则来尽量符合索引的使用条件。 这里需要划重点利用索引树中数据有序的性质才叫真正地使用索引。这是判断索引能不能生效的关键条件这个结论也会在后面的章节中反复用到。
索引字段匹配时不要进行操作
再使用索引字段进行匹配时不要在索引字段上进行操作。操作包含了一切需要计算才能得到结果的动作包括表达式计算、函数、类型转换等。原因也很简单因为包含了计算那么每条数据的索引字段值都需要计算才能得到结果那么就只能每条数据都遍历一遍了。 例如下面的操作是不能或只能使用一部分使用索引的
-- 在等值匹配 name age 时age 上进行了一个数学运算此时只能使用索引的 name 部分
select * from user where name 小美 and age -1 21;-- 在等值匹配 name 时name 上进行了一个字符串拼接的操作此时是不能走索引的
select * from user where CONCAT(name, 01) 小美01
-- 要想改写包含操作的 SQL 来实现对应的功能有两种做法
把操作从索引字段侧转移到常量侧在第一个 SQL 中把 -1 操作转移到右侧来即 age 211建立函数索引在第二个 SQL 中先执行创建函数索引的语句添加函数索引
-- 在 name 列上增加一个函数索引索引的字段值为 concat(name, 01)
ALTER TABLE user ADD INDEX idx_name_concat_01 ((concat(name, 01))) USING BTREE;再执行第二个 SQL就可以使用上面创建的函数索引了。
大于小于符号范围查询对于索引后面字段的影响
前面我们在讨论最左前缀法则时特意提到讨论的前提是建立在索引前面的字段是进行的等值查询下的。那么这一部分中将会讨论建立在索引前面的字段进行范围查询时对后面的索引部分的影响。 在使用索引前面的字段进行大于、小于符号的范围查询时该字段后面的索引部分将不会被使用。
以这个叶子节点举例假设我有以下 SQL 语句
-- name 进行等值查询age 进行大于符号的范围查询register_date 使用等值查询时age 后面的索引部分将不会被使用
select * from user where name 王小美 and age 25 and register_date 2014-04-25-- name 进行等值查询age 进行小于符号的范围查询register_date 使用等值查询时age 后面的索引部分将不会被使用
select * from user where name 王小美 and age 30 and register_date 2014-04-25上面两个 SQL索引并没有使用完全原因就是 age 字段使用了大于、小于符号的范围查询。如果将大于、小于符号改成 between...and... 就可以使用索引了
-- 使用 between...and... 可以使用索引
select * from user where name 王小美 and age between 1 and 30 and register_date 2014-04-25其他的规则
综合评估索引是否使用
划重点MySQL 其实并不会按照死板的规则来约定走或者不走索引而是要根据检索比例表数据量等因素综合评估是否走索引常见的需要综合评估的查询操作有 、、、、in、or。 例如下面两个 SQL
-- 这个查询条件从最左前缀法则来看应该是会走索引的但是实际情况是它走的全表扫描
-- 原因就是这个查询条件对于过滤数据几乎没有帮助因为表中所有的数据都符合条件
-- 所以在这种情况下由于使用索引还会带来回表操作的开销所以性能还不如直接全表扫描
select * from user where name 0 ;-- 这个查询条件是会走索引的因为符合这个查询条件的数据比较少检索比例比较低
select * from user where name 王 ;所以日常开发过程中在对 SQL 进行性能分析时不能一味地套用各种规则而是要根据实际情况如符合条件的数据量大小来综合评估索引的使用情况。
值辨别度低的字段不使用索引
在一个数据列中如果数据值的取值范围非常有限的情况下重复度就会很高这也就造成了只使用这一列作为数据的辨别条件时数据与数据之间的辨别度很低这样的列就称为值辨别度低的字段。 这样的列在日常开发中也非常常见例如用户表中的性别字段一般来说只有 2 个或者 3 个取值那么性别字段就可以称为值辨别度低的字段。在这样的字段上建立索引一般来说是得不偿失的因为在大多数情况下来说MySQL 都不会选择使用该字段对应的索引。 原因也很简单这样的字段的筛选条件过于简单在表数据量大的情况下符合条件的数据量通常也会很大例如性别字段中男女两种取值的数据一般各占 50%那么与其使用索引还会带来回表操作的开销不如直接全表扫描还能获得更高的性能。 is null 或者 is not null 查询条件也是一样的道理一般来说也不会使用索引。 但是这也不绝对因为如果在某个取值的数据在表中占比很少假设在某个系统中女性用户只占 1%且这个字段符合使用索引的条件时还是会使用索引的。
通配符开头的模糊查询不使用索引
当使用通配符开头的模糊查询时例如 name like %小美 或者 name like %小% 这样的条件时是不会使用索引的。原因很简单违反了最左前缀法则。
不等于操作不使用索引
像常见的 !、、not in、not like、not exists 操作都不会使用索引。个人猜测原因主要是
MySQL 认为符合不等于条件的数据量比较大索引树中如果使用不等于条件那么将无法使用索引树有序的性质
