MYSQL-索引

2019-08-21 10:50:53 浏览数 (1)

概述

用来加快查询的技术很多，其中最重要的是索引。通常索引能够快速提高查询速度。如果不适用索引，MYSQL必须从第一条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。但也不全是这样。本文讨论索引是什么以及如何使用索引来改善性能，以及索引可能降低性能的情况。

索引的本质

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

数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

索引的存储分类

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

  • B-Tree 索引：最常见的索引类型，大部分引擎都支持B树索引。
  • HASH 索引：只有Memory引擎支持，使用场景简单。
  • R-Tree 索引(空间索引)：空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。
  • Full-text (全文索引)：全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。

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

MyISAM、InnoDB引擎、Memory三个常用引擎类型比较

B-TREE索引类型

  • INDEX普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建： （1）创建索引: CREATE INDEX 索引名 ON 表名(列名1，列名2,...); （2）修改表: ALTER TABLE 表名ADD INDEX 索引名 (列名1，列名2,...); （3）创建表时指定索引：CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1，列名 2,...) );

  • UNIQUE唯一索引

表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique： （1）创建索引：CREATE UNIQUE INDEX 索引名 ON 表名(列的列表); （2）修改表：ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表); （3）创建表时指定索引：CREATE TABLE 表名( [...], UNIQUE 索引名 (列的列表) );

  • PRIMARY KEY主键索引

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。 （1）主键一般在创建表的时候指定：“CREATE TABLE 表名( [...], PRIMARY KEY (列的列表) ); ”。 （2）但是，我们也可以通过修改表的方式加入主键：“ALTER TABLE 表名ADD PRIMARY KEY (列的列表); ”。 每个表只能有一个主键。 （主键相当于聚合索引，是查找最快的索引）

注：不能用CREATE INDEX语句创建PRIMARY KEY索引

  • fulltext index 全文索引

上述三种索引都是针对列的值发挥作用,但全文索引,可以针对值中的某个单词,比如一篇文章中的某个词,然而并没有什么卵用,因为只有myisam以及英文支持,并且效率让人不敢恭维,但是可以用coreseek和xunsearch等第三方应用来完成这个需求

索引的设置语法

一 设置索引

在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

1．ALTER TABLE - ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

  • ALTER TABLE table_name ADD INDEX index_name (column_list)
  • ALTER TABLE table_name ADD UNIQUE (column_list)
  • ALTER TABLE table_name ADD PRIMARY KEY (column_list)
  • ALTER TABLE table_name ADD FULLTEXT KEY (column_list)

2．CREATE INDEX - CREATE INDEX可对表增加普通索引或UNIQUE索引。

  • CREATE INDEX index_name ON table_name (column_list)
  • CREATE UNIQUE INDEX index_name ON table_name (column_list)

二 删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

  • DROP INDEX index_name ON talbe_name
  • ALTER TABLE table_name DROP INDEX index_name
  • ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中的索引index_name。 第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

三 查看索引

代码语言：javascript复制
mysql> show index from tblname;
mysql> show keys from tblname;
  • Table：表的名称
  • Non_unique：如果索引不能包括重复词，则为0。如果可以，则为1
  • Key_name：索引的名称
  • Seq_in_index：索引中的列序列号，从1开始
  • Column_name：列名称
  • Collation：列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
  • Cardinality：索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
  • Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
  • Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。
  • Null：如果列含有NULL，则含有YES。如果没有，则该列含有NO。
  • Index_type：用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
  • Comment：更多评注。

索引选择性

一 索引选择原则

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

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

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

二 索引选择原则细述

  • 性能优化过程中，选择在哪个列上创建索引是最非常重要的。可以考虑使用索引的主要有 两种类型的列：在where子句中出现的列，在join子句中出现的列，而不是在SELECT关键字后选择列表的列
  • 索引列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同的值，很容易区分行，而用来记录性别的列，只有"M"和"F",则对此进行索引没有多大用处，因此不管搜索哪个值，都会得出大约一半的行,（ 见索引选择性注意事项对选择性解释；）
  • 使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度

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

  • 利用最左前缀

三 索引选择注意事项

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。

一般两种情况下不建议建索引：

1、表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了;

至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

2、索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：

代码语言：javascript复制
Index Selectivity = Cardinality / #T

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B Tree的性质决定的。例如，上文用到的employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

代码语言：javascript复制
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
代码语言：javascript复制
 ------------- 
| Selectivity |
 ------------- 
|      0.0000 |
 -------------

title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。

3、MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)。

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

四 索引的弊端

索引的益处已经清楚了，但是我们不能只看到这些益处，并认为索引是解决查询优化的圣经，只要发现 查询运行不够快就将 WHERE 子句中的条件全部放在索引中。

确实，索引能够极大地提高数据检索效率，也能够改善排序分组操作的性能，但有不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。假设在Table ta 中的Column ca 创建了索引 idx_ta_ca，那么任何更新 Column ca 的操作，MySQL在更新表中 Column ca的同时，都须要更新Column ca 的索引数据，调整因为更新带来键值变化的索引信息。而如果没有对 Column ca 进行索引，MySQL要做的仅仅是更新表中 Column ca 的信息。这样，最明显的资源消耗就是增加了更新所带来的 IO 量和调整索引所致的计算量。此外，Column ca 的索引idx_ta_ca须要占用存储空间，而且随着 Table ta 数据量的增加，idx_ta_ca 所占用的空间也会不断增加，所以索引还会带来存储空间资源消耗的增加。

B-Tree 索引

当人们谈论索引的时候，如果没有特别指明类型，那么多半说的是 B-Tree 索引，它使用 B-Tree 数据结构来存储数据。我们使用术语“B-Tree”，是因为 MySQL 在 CREATE TABLE 和其他语句中也使用该关键字。不过，底层的存储引擎也可能使用不同的存储结构。InnoDB使用的是B Tree。

假如有如下数据表：

代码语言：javascript复制
CREATE TABLE People (
  last_name  varchar(50)    not null,
  first_name varchar(50)    not null,
  dob        date           not null,
  gender     enum('m', 'f') not null,
  key(last_name, first_name, dob)
);

一 B-Tree 索引对如下类型的查询有效

  • 全值匹配

全值匹配指的是和索引中的所有列进行匹配，例如上表的索引可用于查找姓名为 Cuba Allen 、出生于 1960-01-01 的人。

  • 匹配最左前缀

上表中的索引可用于查找所有姓为 Allen 的人，即只使用索引的第一列。

  • 匹配列前缀

只匹配某一列的值的开头部分。例如上表的索引可用于查找所有以 J 开头的姓的人。这里也只使用了索引的第一列。

  • 匹配范围值

例如上表中的索引可用于查找姓在 Allen 和 Barrymore 之间的人。这里也只使用了索引的第一列。

  • 精确匹配某一列并范围匹配另外一列

上表的索引也可用于查找所有姓为 Allen ，并且名字是字母 K 开头（比如 Kim 、 Karl 等）的人。即第一列 last_name 全匹配，第二列 first_name 范围匹配。

  • 只访问索引的查询

B-Tree 通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。

二 B-Tree 索引的一些限制

  • 如果不是按照索引的最左列开始查找，则无法使用索引。例如上表的索引无法用于查找名字为 Bill 的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。
  • 不能跳过索引中列。也就是说，上表的索引无法用于查找姓氏为 Smith 并且在某个特定日期出生的人。如果不指定名（first_name），则 MySQL 只能使用索引的第一列。
  • 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询 WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23' ，这个查询只能使用索引的前两列，因为这里 LIKE 是一个范围条件（但是服务器可以把其余列用于其他目的）。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

引用

  • 美团-MySQL索引原理及慢查询优化
  • MySQL索引背后的数据结构及算法原理
  • 索引的利弊与如何判定，是否需要索引
  • MySQL 多列索引优化小记
全文检索 云推荐引擎 数据库 云数据库SQLServer sql

0 人点赞