MySQL 索引

索引介绍

在MySQL中，索引是高效获取数据的最重要的数据结构，通常在表数据越来越多情况下获取数据的效率开始下降，而索引或者叫做键可以有效提升效率。

理解索引工作的方式最好的办法就是把索引比喻成书的目录，当需要查看特定的章节时通过查看目录的方式往往要比查看整个书的内容要有效很多。

当索引包含多个字段时，索引字段的顺序就非常重要，因为MySQL是从左开始匹配使用索引，意味着如果没有最左边字段时，语句是用不了索引。

使用索引的优势在于： 
大大减少服务器需要扫描的数据量 
帮助服务器避免排序和临时表 
可以将随机IO变成顺序IO

 

索引在带来上述优势的同时，也有缺点在； 
在创建索引和维护索引时会耗费时间，而且随着数据量的增加而增加 
索引文件会占用物理空间 
当对表的数据进行Insert，update，delete操作时，索引也要动态维护，这就降低了DML的执行效率

B-Tree索引

B-tree索引 
顾名思义，B-tree索引使用B-tree的数据结构存储数据，不同的存储引擎以不同的方式使用B-Tree索引，比如MyISAM使用前缀压缩技术使得索引空间更小，而InnoDB则按照原数据格式存储，且MyISAM索引在索引中记录了对应数据的物理位置，而InnoDB则在索引中记录了对应的主键数值。

 下图展示InnoDB的B-tree索引结构

 

B-Tree索引驱使存储引擎不再通过全表扫描获取数据，而是从索引的根节点开始查找，在根节点和中间节点都存放了指向下层节点的指针，通过比较节点页的值和要查找值可以找到合适的指针进入下层子节点，直到最下层的叶子节点，最终的结果就是要么找到对应的值，要么找不到对应的值。整个B-tree树的深度和表的大小直接相关。

B-Tree对索引列是顺序组织存储的，所以也很适合查找范围数据。

如下图

 

 使用B-Tree索引适用于全键值、键值范围或者键前缀查找

1. 全键值匹配：和索引中的所有列都进行匹配，比如查找姓名为zhang san，出生于1982-1-1的人
2. 匹配最左前缀：和索引中的最左边的列进行匹配，比如查找所有姓为zhang的人
3. 匹配列前缀：匹配索引最左边列的开头部分，比如查找所有以z开头的姓名的人
4. 匹配范围值：匹配索引列的范围区域值，比如查找姓在li和wang之间的人
5. 精确匹配左边列并范围匹配右边的列：比如查找所有姓为Zhang，且名字以K开头的人
6. 只访问索引的查询：查询结果完全可以通过索引获得，也叫做覆盖索引，比如查找所有姓为zhang的人的姓名

创建测试数据

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)
);

insert into people values('zhang','san','1982-1-1','m');
insert into people values('li','si','1985-3-2','m');
insert into people values('wang','wu','1988-6-15','f');

　　

 

 

 

B-Tree索引也有一定的限制：

如果查询条件不是按照索引最左列开始查找，则无法使用索引，比如如果查找名字为san的人、查找某个特定生日的人、查看姓氏以某字母结尾的人都无法使用此索引

如果查询条件跳过了索引的中间列，则查询使用索引仅使用最左边的列，比如查找姓为zhang且在某个特定日期出生的人

如果查询的某列有范围查找，则其右边的列无法使用索引优化查找，如查询姓为zhang，名字以s开头且生日为1982-1-1的人，则查询只能使用前两列。

 

 

哈希索引

MySQL中只有Memory引擎支持哈希索引，但Memory引擎也支持B-Tree索引。哈希索引是基于哈希表实现， 只有精确匹配索引中的所有列的查询才有效。对每一行数据，会将所对应的索引列计算出一个哈希码， 在索引中存放此哈希码和对应数据行的指针。当存在多行数据的哈希码相同时，索引会以链表的方式存放多个记录指针到同一个哈希条目中。也有不同的索引列值却有相同哈希码的情况，叫哈希冲突，当存在哈希冲突时， 
存储引擎必须遍历链表中所有行指针，于对应的行数据比较，直到找到所有符合条件的行。

创建一些测试表和数据

CREATE TABLE testhash (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
KEY USING HASH(fname)
) ENGINE=MEMORY;

insert into  testhash values
("Arjen", "Lentz"), 
("Baron",  "Schwartz"), 
("Peter",  "Zaitsev"), 
("Vadim",  "Tkachenko");

mysql> SELECT * FROM testhash;
+--------+-----------+
| fname | lname |
+--------+-----------+
| Arjen | Lentz |
| Baron | Schwartz |
| Peter | Zaitsev |
| Vadim | Tkachenko |
+--------+-----------+

　　

虽然InnoDB表创建索引时指定hash索引也能创建成功，但底层创建的索引依旧是btree索引

 

create table TIhash(fname varchar(10),lname varchar(10),key using hash(fname));

　　

看看表结构

 

 看看底层用的索引

哈希索引的限制：

1. 只存储哈希值和行指针，不存储字段值，所以最后的查询会真正查询行数据，无法使用覆盖索引
2. 索引数据并不是按照索引值顺序存储的，所以排序操作无法使用索引
3. 由于哈希索引是用索引列的全部内容计算哈希码，所以如果查询只有部分索引字段时是无法使用索引的
4. 哈希索引只支持等值比较查询，比如=，IN操作等，不支持范围查询

 例： 如下
explain select * from TIhash where fname=’a’;

 

explain select * from TIhash where fname>’a’;

 

explain select * from TIhash where fname in (‘a’,’b’);

 

 

全文索引

Fulltext索引是创建在char, varchar, text文本字段上的加速查询和DML操作的索引。 它不是直接比较索引中的值，而是查找文本中的关键词，所以全文索引更类似于搜索引擎而不是简单的where条件匹配

 

Fulltext索引的使用时通常用match()…against语句 
创建表的适合添加全文索引

 

CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);

　　

修改表结构添加全文索引 
ALTER TABLE article ADD FULLTEXT index_content(content)

直接创建索引 
CREATE FULLTEXT INDEX index_content ON article(content)

 

全文搜索的语法： MATCH(col1,col2,…) AGAINST (expr[search_modifier])。其中 

MATCH中的内容为已建立FULLTEXT索引并要从中查找数据的列， AGAINST中的expr为要查找的文本内容， search_modifier为可选搜索类型。 search_modifier的可能取值有： 

IN NATURAL LANGUAGEMODE、 IN NATURAL LANGUAGE MODE WITH QUERY 
EXPANSION、 IN BOOLEAN MODE、 WITH QUERY EXPANSION。 search_modifier 
的每个取值代表一种类型的全文搜索，分别为自然语言全文搜索、带查询扩展的自然语 
言全文搜索、布尔全文搜索、查询扩展全文搜索（默认使用IN NATURAL LANGUAGE 
MODE） 。

插入测试数据

Insert into article values(1,'ac','abcd',1), (2,'bd','bcde',1),
(3,'ab','defg',1), (4,'be','degh',1);

　　

使用全文索引方法如下

SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘xxx’, ‘sss’, ‘ddd’)

　　

索引创建

 

mysql> help create index
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

　　

Index_col_name可以包含一个字段，也可以包含多个字段（逗号隔开），如果包含多个字段，则表明此索引是复合索引

Unique index代表索引中的值不能有重复

Fulltext index只能创建在innodb和myisam存储引擎的char,varchar和text字段上

Index可以创建在包含NULL值的字段上

Key_block_size=value是在myisam存储引擎的表上指定索引键的block大小

创建索引的是三种途径包括：

1. 直接创建索引 
   CREATE INDEX index_name ON table(column(length))

2. 修改表结构的方式添加索引 
   ALTER TABLE table_name ADD INDEX index_name ON (column(length))

3. 创建表的时候同时创建索引

CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
);

　　

删除索引

DROP INDEX index_name ON table

例如：

create index idx_st_sname on students(sname); ##创建普通索引
create index idx_st_union on students(sname,sex); ##创建复合索引
create unique index idx_st_sid on students(sid); ##创建唯一索引

　　

mysql 存储引擎支持的索引类型

 

 

存储引擎	索引类型
InnoDB	BTREE
MyISAM	BTREE
MEMORY	HASH,BTREE
NDB	HASH,BTREE

 

 

 

 

  Create index注释 

Comment ‘string’ 代表可以为索引添加最长1024字符的注释

CREATE TABLE t1 (id INT);
CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

　　

创建唯一索引

与普通索引类似，不同的就是在索引功能的基础上又增加了对数据的唯一性要求，但和主键索引不同的是允许空值，如果是多列索引，则列的组合在每行都必须唯一，否则数据插入失败。其创建的方法和普通索引类似：

创建唯一索引 
CREATE UNIQUE INDEX indexName ON table(column(length)) 
length 字符数

 修改表结构 
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

创建表的时候直接指定索引

CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE indexName (title(length))
);

　　

索引的删除

DROP INDEX `index_name` ON `table_name`
ALTER TABLE `table_name` DROP INDEX `index_name`

　　

 这两句都是等价的,都是删除掉table_name中的索引index_name;

ALTER TABLE table_name DROP PRIMARY KEY 
– 删除主键索引,注意主键索引只能用这种方式删除

MySQL索引查看

通过执行show create table table_name查看基本索引信息

mysql> show create table students;
| students | CREATE TABLE `students` (
`sid` int(11) NOT NULL,
`sname` varchar(10) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
PRIMARY KEY (`sid`),
KEY `idx_s_1` (`dept_id`),
KEY `idx_union_1` (`gender`,`dept_id`)
) ENGINE=InnoDB DEFAULT  CHARSET=utf8 COLLATE=utf8_unicode_ci 

　　

通过执行show index from table_name\G;命令查看索引的信息

 

 1.Table 
表的名称。 
2.Non_unique 
如果索引不能包括重复词，则为0。如果可以，则为1。 
3.Key_name 
索引的名称。 
4.Seq_in_index 
索引中的列序列号，从1开始。

例如联合索引 左边第一个序列号是1 ， 第二个是2

 create index idx_union on course (course_name,teacher_id);

5.Column_name 
列名称。 

6.Collation 
列以什么方式存储在索引中。在MySQL中，有值‘A’ （升序）或NULL（无分类）。 

 7.Cardinality 
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE table_name或myisamchk （shell命令）可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，mysql使用该索引的机会就越大 

8.Sub_part 
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。 

 

9.Packed 
指示关键字如何被压缩。如果没有被压缩，则为NULL。

10.Null 
如果列含有NULL，则含有YES。如果没有，则该列含有NO。

 

11.Index_type 
索引类型（BTREE, FULLTEXT, HASH, RTREE）。 

12.Comment 
索引注释

 

MySQL聚簇索引和辅助索引

每个InnoDB表都会有一个特殊的索引，叫聚簇索引，索引中包含了所有的行数据。 聚簇索引和主键是一个意思的两种叫法。

当显示定义一个主键时，则InnoDB就把它作为聚簇索引，当表中没有代表唯一的一个或一组字段时，可以增加一个auto-increment字段作为主键 

当没有定义主键时，则MySQL会寻找是否有非NULL的唯一索引，如果有就把第一个唯一索引作为聚簇索引 当没有定义主键时，则MySQL会寻找是否有非NULL的唯一索引，如果有就把第一个唯一索引作为聚簇索引 

 

当没有主键或合适的唯一索引时，InnoDB内部会创建一个虚构的聚簇索引，其中包含row ID。 

聚簇索引的优势：

当SQL语句通过聚簇索引访问表数据时，由于通过索引能直接定位并访问表数据，所以性能很高。 

相关数据会保存在一起，比如表是包含用户的邮件信息，通过用户ID创建聚簇索引，则查询一个用户的所有邮件只需要读取少量的数据页。 

使用覆盖索引扫描的查询可以直接使用页节点上的主键值

聚簇索引的叶子节点包含了行的全部数据，而节点页只包含了索引列，比如下图索引列

 

所有非聚簇索引都叫做辅助索引，在InnoDB里，辅助索引的每一行包含了对应的主键值和辅助索引值，索引对辅助索引的SQL执行是先定位对应的主键值，然后再到聚簇索引中查找对应的行数据。

针对同一个表分别使用MyISAM和InnoDB存储引擎创建主键索引和普通索引，看看索引的数据结构不同

CREATE TABLE layout_test (
col1 int NOT NULL,
col2 int NOT NULL,
PRIMARY KEY(col1),
KEY(col2)
);

　　

针对上表，主键上随机插入1~10000的随机整数，col2上随机插入1到100的随机整数。

MyISAM索引数据分布

MyISAM存储引擎对定长的行数据采用行号，行号从0开始递增，按照数据插入的顺序存储在磁盘上

 

主键数据分布情况如下：

 

 

 辅助索引的数据分布和主键一致，情况如下：

每个叶子节点的对应的是行号和对应的键值 
所以MyISAM中的主键索引和其他索引在结构上没有什么不同。 例行号0 ，值是99，数据按照顺序插入在磁盘上。

InnoDB索引数据分布

因为InnoDB支持聚簇索引，所以使用了不同的方式存储同样的数据。 在InnoDB中，聚簇索引相当于表，不像MyISAM一样需要独立的行存储。 

 聚簇索引数据分布情况如下：

聚簇索引的每个叶子节点包含了主键值，事务ID，用于事务和多版本并发控制的回滚指针以及其他剩余的列。

另外，InnoDB的二级索引也和MyISAM不同，其叶子节点中存储的不是行指针，而是主键值，从而当出现行移动或者数据页分裂时无需更新二级索引中的该值，

而当出现行移动或者数据页分裂时无需更新二级索引中的该值，

 

 

MyISAM和InnoDB的索引数据对比情况如下：

 

聚簇 ：二级（辅助） 索引键值对应主键的键值 ， 
非聚簇 ： 键值对应的行号， 指向关系

InnoDB索引物理结构

InnoDB索引的物理结构是B-tree结构，所有的索引数据都存放在B-tree的叶节点上。默认的索引页的大小为16KB。 
当新数据插入到聚簇索引时，InnoDB会保留1/16的索引页空闲空间作为将来的数据插入和修改所用。

在创建和重建和B-tree索引时，InnoDB会执行bulk加载将数据加载到索引页中。当索引页的数据占据的空间低于设置的merge_threshod，默认是50%时，则InnoDB会执行索引页的合并并将当前索引页释放。

在数据库实例之间复制数据文件和日志文件要确保使用相同的页大小，否则无法使用。

 

索引使用策略

如何创建高效的索引

1. 索引建立在经常搜索的列上 
   在慢查询日志看看经常使用一些查询语句，在经常使用的列上创建索引，才能保证索引会被重复使用，从而发挥索引提升语句执行性能的优势，反之如果建立在很少使用的列上则索引带来的劣势要大于优势 
   对于经常使用的列常见于where条件中，表连接中的关联字段，以及排序字段等。
2. 使用独立的列 
   独立的列是指索引列不能是表达式的一部分，也不能是函数的一部分 
   比如以下的两个例子就用不了索引 

准备测试数据：

create table students_1 (
sid int(11)  auto_increment not null,
sname varchar(64) default null,
gender int(11) default null,
dept_id int(11) default null,
birthday datetime default null,
primary key (sid),
key inx_name (sname) 
) engine=innodb;
 insert into students_1 values(1,'a',1,1,now()),(2,'b',1,1,now());

　　

正常走主键索引

 

 等号左边是一个表达式 查到的结果是跟sid=1 一样，但是不会走索引，表达式最好放等号右边才会走索引

避免使用函数 

在索引字段上尽量避免使用函数，否则该索引不会被使用。

 

 

3. 前缀索引 
   当要增加索引的字段是很长的字符列时，索引会变得很大和很慢，所以要考虑在列开始的部分字符串上创建索引，这里要求索引的选择性，即不重复的索引值和表的记录总数对比的比例约接近1越好，因为选择性越高意味着在查询时能筛选掉的数据量就越多。

对于BLOB，TEXT或者长度很长的varchar字段，要选择合适的前缀字段长度建立前缀索引。 
但前缀索引的缺点是无法用在order by和group by情况下，且也无法执行覆盖扫描。 
评估前缀索引是否足够好，可以通过以下方法对比：

找区别度 ， 插入数据建立前缀索引测试：

DROP INDEX inx_name  on students_1;
delete from students_1;
insert into students_1 values (1,'aaaabbcbdaaavvsdfasdfgasdfasdfasdfasdfasdfgasdfgadsf',1,1,now()),
(2,'aaacbbcbdaaavvsdfasdfgasadfasdfasdfsadfsdfgasdfgadsf',1,1,now()),
(3,'aaaebbcbdaaavvsdfasdfasdfaasdfasdfsdfgasdfgasdfgadsf',1,1,now()),
(4,'aaagbbcbdaaavvsdfasdfgasdasdfasdfasdfasdffgasdfgadsf',1,1,now());

　　

例如 ： left(sanme,3) # 是看前3个字符的区别度

SELECT COUNT(DISTINCT LEFT(sname, 1))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(sname, 2))/COUNT(*) AS sel4,
COUNT(DISTINCT LEFT(sname, 3))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(sname, 4))/COUNT(*) AS sel6,
COUNT(DISTINCT LEFT(sname, 5))/COUNT(*) AS sel7
FROM students_1;

　　

所以在字符长度为4 的创建前缀索引即可

mysql> create index inx_s_4 on students_1(sname(4));

　

 会走索引

 

 

 前缀索引无法用在分组的情况下

 

 

4. 多列索引 
   通常一般的索引是创建在单独的一个列上，但索引也可以创建在多个列上，这就要求对列的前后顺序的选择。 
   索引的错误使用方法是在所有限制条件语句涉及的字段上都建立单独的索引，而是应该判断多个列的组合是否经常出现而且组合之后的数据筛选范围要优于单独的索引使用。 如果答案是肯定的，那就可以在多个列上创建多列索引。比如：

create index ind_union on students(sname, dept_id, teacher_id);

　　

5. 覆盖索引 
   当出现语句的执行过程所需要的数据完全可以通过索引来获得时，这样的索引叫做覆盖引，常常出现在多列索引的情况，对于经常出现的SQL语句，可以通过创建覆盖索引而使得语句执行不需要扫描表数据，从而加快语句的执行效率。 
   当发起一个覆盖索引的查询时，在explain语句的Extra字段中可以看到“Using index” 的信息，代表发生了在索引身上的覆盖查询。

mysql> create index idx_union_1 on students_1 (gender,dept_id);

　　覆盖索引是非常有用的工具，能够极大的提高性能，其主要优势在于： 

a. 索引大小通常远小于数据行大小，所以如果只需要读取索引，那MySQL会极大地减少数据访问量 
b. 因为索引是按照列值顺序存储的，所以对IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少很多。 
c. InnoDB的二级索引能够覆盖查询的话，就可以避免对主键索引的二次查询

由于覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。

索引列顺序选择

当创建的索引包含多个字段时，字段在索引中的顺序就非常重要，正确的顺序依赖于使用索引的查询语句。此要求仅限于B-Tree索引，其他类型的索引则不关注索引列顺序。

 

通常的做法是基于全局基数和选择性来决定字段顺序，某个列的选择性高就意味着更能首先过滤掉大部分无用的数据，所以就将此列作为索引的第一列

 

Customer_id 列的选择性(过滤条件)更高，所以应该将其作为索引列的第一列。

索引统计信息

MySQL查询优化器通过两个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。如果存储引擎向优化器提供的扫描行数信息不准确，或执行计划太复杂以致于无法准确获取匹配的行数，那优化器会使用索引统计信息来估算扫描行数。 如果表没有统计信息，或者统计信息不准确，优化器很可能做出错误的决定。可以通过analyze table命令来重新生成统计信息.

 

可以通过执行show index from命令来查看索引的基数cardinality（存储引擎估算索引列有多少不同的值）等信息：

 

 cardinality 越高 ： 走索引的概率也就越高，基数越低走索引的越低

 还可以通过查看information_schema.statistics表来查看索引的信息。

InnoDB存储引擎通过抽样的方式计算统计信息，首先随机读取少量的索引页面，以此为样本计算索引的统计信息，通过参数innodb_stats_sample_pages参数来设置样本页的数量。

mysql> show variables like '%innodb_stats_sample_pages%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_stats_sample_pages | 8     |
+---------------------------+-------+

　　

InnoDB会在表首次打开，或者执行analyze table，或者表的大小变化超过1/16时，都会计算索引的统计信息。 
如果希望持久化索引的统计信息，则可以在5.6或更高版本上使用 
innodb_analyze_is_persistent参数控制。

索引碎片处理

B-Tree索引随着表数据的修改变化，包括插入、修改和删除动作而导致碎片化，这会降低查询的效率。碎片化的索引数据可能会以无序的方式存储在磁盘上，而如果叶子节点在物理分布上是顺序且紧密的，那查询性能会更好。 
对于支持optimize table命令的存储引擎来说，可以通过此命令来重新整理表数据，或者通过导出再导入的方式重置数据。

对索引可以用删除再重建的方式，也可以使用rebuild的方式。

对于不支持optimize table命令的存储引擎，可以用alter table指定表修改为当前的引 
擎来重建表 
mysql > alter table table_name engine=<原来的存储引擎>