mysql 索引及其原理

阅读量：6139 次

发布时间：2019-06-21

本文共 4701 字，大约阅读时间需要 15 分钟。

mysql 索引

KEY与INDEX的区别：

KEY is something on the logical level, describes your table and database design.

INDEX is something on the physical level, helps improve access time for table operations.

KEY是关系模型理论中的一部份，比如有主键（Primary Key），外键（Foreign Key）等，用于数据完整性检查与唯一性约束等。

而Index则处于实现层面，比如可以对表个的任意列建立索引，那么当建立索引的列处于Select语句中的Where条件中时，就可以得到快速的数据定位，从而快速检索。至于Unique Index，则只是属于Index中的一种而已，建立了Unique Index表示此列数据不可重复。

注意：对于MySQL而言，术语"Index"和"Key"经常是混用的。

如何使用索引

简而言之，INDEX是为实现基于数据列的快速检索（fast retrieval）而设计的。虽然索引可以加快数据检索操作，但会使数据修改操作变慢。每当修改数据记录，索引就必须刷新一次。为了在某种程度上弥补这一缺陷，许多SQL命令都有一个DELAY_KEY_WRITE项，这个选项的作用是暂时制止MySQL在每插入一条新行和每修改一条现有行之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。另外，索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。

Mysql使用索引的方式有以下几种：

1、在SELECT操作中，把与WHERE子句中所给出的条件相匹配的数据行尽快找出来；

2、使用MIN()或MAX()函数的查询，如果数据列带索引，那么它的最小值和最大值能被迅速找到而不用逐行检查；

3、使用索引也可以迅速完成ORDER BY子句和GROUP BY子句的分类和分组操作；

4、对那些仅需要使用带索引的数据列的场合，可以直接从索引文件读取到数据，甚至不用去读数据文件；

5、在运行联接多个数据表的查询时，索引也可以发挥很大作用；

综上，最适合有索引的数据列是那些出现在WHERE子句/ORDER BY子句/GROUP BY子句的字段，以及在联接子句中出现的字段，例如：

SELECT    col_a                                --> not a candidate   FROM    tbl1 INNER JOIN tbl2    ON tbl1.col_b = tbl2.col_c           -->candidates   WHERE    col_d = expr;                        ---> a candidate

挑选索引的思路：

1、考虑数据列的维度势，数据列的维度（cardinality）等于它所容纳的非重复值的个数。

比如说，假设有一张包含1000万数据的银行客户信息表，年龄这个字段的取值在1-100平均分布，那么对年龄字段建立索引可以很很快捷的找到30岁的客户群（大概包含10万行数据）；但如果对性别建立索引，并查询所有男性客户群，这大概有500万行数据，索引的意义就不大了，还不如直接全表扫描；

2、对短小的值进行索引。

短小的值可以让索引的“体积”更小，一方面减少了磁盘I/O，另一方面可以让键缓存里面可以容纳更多的键值。

另外，如果对字符串数据列建立索引，尽可能给出前缀长度。

3、对复合索引要特别注意字段顺序。

当创建一个包含n个数据列的复合索引时，在查询使用时，最好将条件顺序按找索引的顺序，这样效率最高。

例如一个数据表的复合索引是province+city+zip，即省份+城市+邮编的组合，该索引能够用来查询下面的数据列的组合：

province=a and city=b and zip=c

province=a and city=b

province=a

如果搜索的是一个给定的city+zip，索引就不能使用了。

4、不要建立过多索引。

每一个多出的索引都要占据额外的磁盘空间，而且都会影响写入操作的性能。

例如：如果想对已经存在了一个复合索引的数据表的某个数据列新建索引时，需要先看下该字段是否是复合索引最左边的数据列，如果是这样的话，就没有必要了。

5、选择合适的索引类型

InnoDB/MyISAM存储引擎通常使用“B树”索引结构，MEMORY存储引擎默认使用散列索引。

散列索引在使用<>、!= 操作符进行的精确匹配比较操作速度极快，但不擅长范围比较操作。

B树索引在使用<、<=、=、>、>=、<>、!= 和 BETWEEN 操作符进行的精确比较操作或范围比较操作里都很有效率。

6、利用“慢查询”日志。

如果在“慢查询”日志里面经常看到某个查询命令，应该尝试改写它以加快其运行速度。

MySQL索引操作的语法

MySQL 可以创建好几种索引，如下所示：

1、唯一索引，这种索引不允许索引项（包括单列索引和复合索引）本身出现重复的值。

2、普通索引，允许索引值出现重复；

3、FULLTEXT索引，只适用于MyISAM数据表，用来进行全文检索。

4、SPATIAL索引，只适用于MyISAM数据表和空间（spatial）数据类型；

5、HASH索引，MEMORY数据表的默认索引类型，利用散列索引进行精确值查询的速度非常快。不过如果打算用一个MEMORY数据表进行范围比较（如id<100），散列索引的性能就比较差了，这种情况下可以改用BTREE索引，例如：

CREATE TABLE namelist {　　id INT NOT NULL,　　name CHAR(100),　　INDEX USING BTREE(id)}ENGINE=MEMORY;

可以在使用CREATE TABLE 语句创建新数据表时创建索引，例如：

CREATE TABLE tbl_name{　　... column definitions ...　　INDEX index_name(index_columns),　　UNIQUE index_name(index_columns),　　PRIMARY KEY(index_columns),　　FULLTEXT index_name(index_columns),　　SPATIAL index_name(index_columns),}

也可以用ALTER TABLE或CREATE INDEX语句给现有数据表添加索引，例如：

CREATE INDEX index_name ON tbl_name (index_columns);CREATE UNIQUE INDEX index_name ON tbl_name (index_columns);CREATE FULLTEXT INDEX index_name ON tbl_name (index_columns);CREATE SPATIAL INDEX index_name ON tbl_name (index_columns);

ALTER TABLE tbl_name ADD INDEX index_name(index_columns);ALTER TABLE tbl_name ADD UNIQUE index_name(index_columns);ALTER TABLE tbl_name ADD PRIMARY KEY (index_columns);ALTER TABLE tbl_name ADD FULLTEXT index_name(index_columns);ALTER TABLE tbl_name ADD SPATIAL index_name(index_columns);

在ALTER语句中，索引本身的名字"index_name"是可选的，如果没有给出，MySQL将根据第一个带索引的数据列给它挑选一个名字。

删除索引：

DROP INDEX `index_name` ON tbl_name;ALTER TABLE tbl_name DROP INDEX index_name;ALTER TABLE tbl_name DROP PRIMARY KEY;

如果想限制某个索引只包含独一无二的值，可以使用PRIMARY KEY或UNIQUE索引，这两种索引的区别是：

1、每个数据表只能有一个PRIMARY KEY，而UNIQUE索引可以有多个；

2、PRIMARY KEY不允许包含NULL值，而UNIQUE索引可以；

可以只对某个字符串类型字段的一个前缀进行索引，例如：

CREATE TABLE address_list {　　name CHAR(30) NOT NULL,　　address BINARY(60) NOT NULL,　　INDEX(name(10)),　　INDEX(address(15))}

它对CHAR字段的前10个字符和BINARY字段的前15个字节编制了索引。

注意：

1、BLOB或TEXT字段只能创建前缀型索引；

2、索引项本身的长度等于构成索引的各个字段的长度总和，如果这个长度超过了索引项本身所能容纳的最大字节数，可以通过前缀索引来缩短长度；

聚集索引和非聚集索引

聚集索引（clustered index）中键值的逻辑顺序决定了表中相应行的物理顺序。

非聚集索引（nonclustered index）中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。　　

当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法，是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应