MYSQL-索引

时间  2019-11-06 标签 mysql 索引

概述

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

索引的本质

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

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

索引的存储分类

索引是在MYSQL的存储引擎层中实现的，而不是在服务层实现的。因此每种存储引擎的索引都不必定彻底相同，也不是全部的存储引擎都支持全部的索引类型。MYSQL目前提供了一下4种索引。sql

  • 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三个经常使用引擎类型比较缓存

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

B-TREE索引类型

  • 普通索引
    这是最基本的索引类型，并且它没有惟一性之类的限制。普通索引能够经过如下几种方式建立：
    （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索引

索引的设置语法

一 设置索引

在执行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)

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索引。

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

三 查看索引

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）的比值：
    Index Selectivity = Cardinality / #T
    显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。例如，上文用到的employees.titles表，若是title字段常常被单独查询，是否须要建索引，咱们看一下它的选择性：

    SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;

    +-------------+
| 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 所占用的空间也会不断增长，因此索引还会带来存储空间资源消耗的增长。

引用

联系我们 沪ICP备2021010478号-7