Mysql索引总结-博客-最代码

ajwensome 2019-01-03 17:39:24

Mysql索引总结

索引原理

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。

如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

索引的目的在于提高查询效率，这个过程可以类比查字典：假如我们需要查询 mysql 这个单词，我们通常会先在字母表里找到字母 m，这样我们就定位到了 m 开头的单词在词典的位置，然后根据定位到的位置从下往下找到 y 字母，最后找到剩下的 sql。索引就相当于字典里的字母表。如果不使用字母表，想要找到mysql 这个单词，我们就需要把字典从头开始翻一遍，直到翻到m 开头的单词在词典的位置，这个过程重复而浪费时间。

索引本质上是独立的索引文件，里面按照特定的顺序（一般采用B+树结构）记录了数据字段（A,B）和实际数据存储位置在没有索引的时候，如果要查询某个字段=值，则需要遍历所有实际数据，然后和字段对比，也就是全表扫描然而在拥有索引的时候，则不需要查询原始数据，只需要查询索引文件，分别查询出A和B匹配的记录，然后计算他们的交集，最后再根据索引中记录的实际位置去读取数据，避免了全表扫描。

索引特点

提高数据检索效率，大大减少存储引擎需要扫描的数据量，把磁盘 IO 的随机读取变成顺序读取
索引需要占用磁盘空间，每一个索引都会新建一个索引文件，保存在磁盘上
由于索引是以文件的形式保存在磁盘上，每次利用索引去查询的时候，都需要将索引文件读取到内存中，需要进行磁盘 IO 操作，对性能影响很大，磁盘 IO 的次数是衡量索引效率的重要指标
mysql默认的存储引擎是 B-Tree 树，数据的增删改都需要去维护 B-Tree，需要消耗 CPU 资源

索引的存储分类

索引是在MYSQL的存储引擎层中实现的，而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。MYSQL目前提供了一下4种索引 :

B-Tree 索引：最常见的索引类型，大部分引擎都支持B树索引。
HASH 索引：只有Memory引擎支持，使用场景简单。
R-Tree 索引(空间索引)：空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。
Full-text (全文索引)：全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。全文索引是一种特殊类型的索引，通过建立倒排索引，快速匹配文档的方式。它查找的是文本中的关键字，而不是直接比较索引中的值。全文索引跟其它几种索引类型的匹配方式完全不一样。它有许多需要注意的细节，比如停用词、词干和复数、布尔搜索等。另外，只有字段类型为 char、varchar 和 text 的字段才能设置全文索引

Mysql目前不支持函数索引，但是能对列的前面某一部分进行索引，例如标题title字段，可以只取title的前10个字符进行索引，这个特性可以大大缩小索引文件的大小，但前缀索引也有缺点，在排序Order By和分组Group By 操作的时候无法使用。用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。语法：create index idx_title on film (title(10))

索引	MyISAM引擎	InnoDB引擎	Memory引擎
B-Tree 索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-Tree 索引	支持	不支持	不支持
Full-text 索引	不支持	暂不支持	不支持

B-Tree 索引类型：

普通索引 ：这是最基本的索引类型，而且它没有唯一性之类的限制
UNIQUE 索引：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique
主键索引：主键是一种唯一性索引，但它必须指定为“PRIMARY KEY 。主键是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引，并且一个表只能有一个主键。主键相当于聚合索引，是查找最快的索引。注：不能用CREATE INDEX语句创建PRIMARY KEY索引

聚集索引与非聚集索引

InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
聚集索引：可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了。非聚集索引：把一个很大的范围，转换成一个小的地图。你需要在这个小地图中找你要寻找的信息的位置。然后通过这个位置，再去找你所需要的记录。
简单理解就是聚集索引物理顺序存储，非聚集索引随机存储这里的存储是索引到的数据行，而非索引段本身

不同存储引擎支持的索引类型

索引类型	InnoDB	MyISAM	Memory
PRIMARY KEY	支持	支持	支持
INDEX	支持	支持	支持
Unique	支持	支持	支持
FULLTEXT	支持	支持	不支持
SPATIAL	支持	支持	不支持

索引使用原则

较频繁的作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合创建索引

当然，并不是存在更新的字段就适合创建索引，从判定策略的用语上也可以看出，是"非常频繁"的字段。到底什么样的更新频率应该算是"非常频繁"呢？每秒？每分钟？还是每小时呢？说实话，还真难定义。很多时候是通过比较同一时间段内被更新的次数和利用该字段作为条件的查询次数来判断的，如果通过该字段的查询并不是很多，可能几个小时或是更长才会执行一次，更新反而比查询更频繁，那这样的字段肯定不适合创建索引。反之，如果我们通过该字段的查询比较频繁，但更新并不是特别多，比如查询几十次或更多才可能会产生一次更新，那我个人觉得更新所带来的附加成本也是可以接受的。

4 . 不会出现在 WHERE 子句中的字段不该创建索引

性能优化过程中，选择在哪个列上创建索引是最非常重要的。可以考虑使用索引的主要有 两种类型的列：在where子句中出现的列，在join子句中出现的列，而不是在SELECT关键字后选择列表的列

索引列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同的值，很容易区分行，而用来记录性别的列，只有"M"和"F",则对此进行索引没有多大用处，因此不管搜索哪个值，都会得出大约一半的行

使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度

例如，有一个CHAR(200)列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前10个或者20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘IO较少，较短的值比较起来更快。更为重要的是，对于较短的键值，所以高速缓存中的快能容纳更多的键值，因此，MYSQL也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多快的可能性

5.不建议使用索引的情况

表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了 .至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值。怎样叫太小？一半说来，同值的数据超过表的百分之15，那就没必要建索引了。

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的

MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形) 。 <>，not in ，！=，WHERE index=1 OR A=10 ，对列进行函数运算的情况（如 where md5(password) = “xxxx”）

存了数值的字符串类型字段（如手机号），查询时记得不要丢掉值的引号，否则无法用到该字段相关索引，反之则没关系也即 select * from test where mobile = 13711112222; 可是无法用到mobile字段的索引的哦（如果mobile是char 或 varchar类型的话）

不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越

6. 最左前缀原理

MySQL 对联合索引的顺序是敏感的。例如我们建立了一个 (a,b,c,d) 顺序的索引，假如现在需要执行这样一个 a = 1 and b = 2 and c > 3 and d = 4 WHERE 查询语句，MySQL 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止索引的匹配，因此 d 字段是没有办法使用索引的。

另外，查询条件用到了索引中列的精确匹配，但是中间某个条件未提供，此时也是没有办法使用索引的。例如我们有一个 (a,b,c) 顺序的索引，假如现在需要执行一个 a = 1 and c = 2 的 WHERE 查询，因为 b 未提供，所以查询只用到了索引的第一列，而后面的 c 虽然也在索引中，但是由于 b 不存在而无法和左前缀连接，因此 c 字段也是没有办法使用索引的。

有一点需要注意，在精确匹配（= 和 IN）中索引的顺序可以是任意的，MySQL 的查询优化器会帮用户优化成索引可以识别的形式。比如对于 (a,b,c) 顺序的索引来说，a = 1 and b = 2 and c = 3 和 a = 1 and c = 3 and b = 2、c = 3 and a = 1 and b = 2 等 SQL 语句效果都是一样的。

7.尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可

参考链接

打赏

0个评论