1、 文中以应用为主，对于索引的实现算法没有深入探讨，待有一定积累后再补充。
2、 所有示例使用MySQL5.6演示，也主要针对MySQL默认的InnoDB存储引擎（InnoDB支持事务，其他不同的MySQL存储引擎差别很大，也有相应的不同用途，后面会写文章作介绍）；示例中单独的看每个SQL的执行时间没有意义，主要看对比结果（如有索引和无索引的情况、索引选择性不同的情况）。
3、 文中使用的演示数据库是employees，可以从这里获取：
https://dev.mysql.com/doc/employee/en/employees-installation.html，链接里面是导入Linux环境的方法，导入windows环境的方法类似：
F:\Mysql\employees_db-full-1.0.6\employees_db>mysql -u root --port=5209 -p -t < employees.sql
Enterpassword: ********
+-----------------------------+
|INFO |
+-----------------------------+
|CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
|INFO |
+------------------------+
|storage engine: InnoDB |
+------------------------+
+---------------------+
|INFO |
+---------------------+
|LOADING departments |
+---------------------+
+-------------------+
|INFO |
+-------------------+
|LOADING employees |
+-------------------+
+------------------+
|INFO |
+------------------+
|LOADING dept_emp |
+------------------+
+----------------------+
|INFO |
+----------------------+
|LOADING dept_manager |
+----------------------+
+----------------+
|INFO |
+----------------+
|LOADING titles |
+----------------+
+------------------+
|INFO |
+------------------+
|LOADING salaries |
+------------------+
索引概念
数据库索引本质上是一种数据结构，是数据库存储引擎用于快速查找数据的一种数据结构，我们可以把索引想象成书的目录，书的目录可以帮助我们快速定位到我们要找的章节，索引则可以帮助我们快速找到我们存储的数据。
索引分类
按实现算法分类
1、B+树
B+Tree由B-Tree演化而来，B+Tree的叶子页（Page）类似于一个链表（与B-Tree的区别也在此，B-Tree的叶子节点没有指向下一节点的指针），如下图，B+Tree更适合用着范围扫描。
2、Hash索引
目前InnoDB还不支持Hash索引，Memory存储引擎支持。Hash索引对于等值查询效率很高；在Hash冲突很大的场景下，开销也会很大，这时采用以链表的方式组织具有相同Hash值的数据，以此来解决Hash冲突时值得存储问题，Hash索引可以用下图简单表示：
3、空间索引（R-Tree）
多应用于GIS（地理信息系统），空间数据是多维的，空间索引从所有维度来索引数据，很多数据库都有专门的支持这样的应用，如MyISAM存储引擎、Oracle Spatial、PostgreSQL的PostGIS。
4、全文索引
MySQL5.6的InnoDB开始支持全文索引，也有其他专门支持全文检索的存储引擎，更多的时候是通过上层应用实现全文检索（如开源的Apache Lucene，一个java实现的全文检索引擎）。
5、T-Tree索引
MySQL NDB Cluster内存存储引擎使用T-Tree索引，T-Tree由平衡二叉树（这里取的是平衡特性）和B-Tree发展而来，其节点不存放数据（这与B-Tree不同），只存放指向数据的指针，因此索引较小，适合内存应用。
二、按应用特点分类
根据应用特点，可以分为：主键（主码）、唯一索引、普通索引、多列索引等；对于MySQL的InnoDB引擎，B+Tree实现可分为：聚族索引（聚集索引，索引查找表）和非聚族索引（二级索引、辅助索引）
基本语法：
（1）主键：primary key
（2）唯一索引：unique，可创建多个
（3）单列索引：key，可以创建多个
（4）多列索引：key（column1，column2，……，columnX），可以创建多个
主键与唯一索引的区别：共同点是对应的字段值都是唯一的，不同点：
（1）一个表只允许有一个主键，而唯一索引可以创建多个
（2）主键不能为NULL，唯一索引可以（NULL!=NULL）
（3）Auto_Increment列必须是主键的一部分
唯一索引特点：
（1）可提供数据完整性
（2）优化器可以避免额外的索引扫描，因为是唯一值，当数据库查询到值时，就可以直接返回，不需要再继续扫描
索引使用&优化
聚族索引
MySQL的InnoDB存储引擎内部即是使用B+Tree组织数据的，可以理解为：聚族索引就是表，当在InnoDB上创建一个表（Create table T），往T插入数据，InnoDB以B+Tree组织表的数据，每个Page作为树的叶子节点存储表数据，查询数据时，通过索引找到的是数据所在的Page，再从Page里面找到具体的数据。
数据是以默认16Kb大小的页（Page）组织的，5.6版本增加了配置项‘innodb_page_size’，可以设置为4Kb、8Kb、16Kb等。
既然表就是聚族索引，那么索引列是什么呢？InnoDB以主键作为索引列，如果创建表时没有定义主键，InnoDB会自动生成一个主键（内部的，对用户不可见）。
用一个图例来说明吧，创建了一个表（create table t (col1 int primary key, col2 int） engine=innodb;主键取值1~10000，col2随机取值1~100，InnoDB内部组织数据可能会是下图的样子：
二级索引（辅助索引）
二级索引仅存储索引列和主键列，通过主键列来获取其他非索引列的数据；就是说通过二级索引查询数据需要经过两跳，先通过二级索引查询到主键，再通过主键查询其他列的数据。
索引选择性
在一个表有多个索引的情况下，查询优化器是以什么为依据来选择使用那个索引呢？其中一个重要的依据是‘索引选择性’，一个字段的选择性大小等于一个字段非重复值得数量/总记录数，有2种方法来计算索引的选择性，方法1就是通过基数（cardinality）来估计，explain出的是一个估计值，如下：
mysql> show index from employees\G
***************************1. row ***************************
Table: employees
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: emp_no
Collation: A
Cardinality: 300584
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set(0.03 sec)
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
***************************2. row ***************************
Table: employees
Non_unique: 1
Key_name: emp_names
Seq_in_index: 1
Column_name: first_name
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
***************************3. row ***************************
Table: employees
Non_unique: 1
Key_name: emp_names
Seq_in_index: 2
Column_name: last_name
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
3 rows in set(0.00 sec)
这里可以看到主键的索引选择性为1，主键和唯一索引的选择性都应该是1，通常情况下选择性越大越好，因为越大越容易查询到需要的数据。下面看一个普通索引的选择性，使用方法2，直接通过SQL计算：
mysql>select count(distinct first_name)/count(*) as first_name_c,count(distinctlast_name)/count(*) as last_name_c from employees;
+--------------+-------------+
|first_name_c | last_name_c |
+--------------+-------------+
| 0.0042 | 0.0055 |
+--------------+-------------+
多列索引
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 299113
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from employees where first_name like 'G%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: range
possible_keys: idx_name
key: idx_name
key_len: 44
ref: NULL
rows: 32574
Extra: Using index condition
1 row in set (0.09 sec)
mysql> explain select * from employees where first_name like 'G%' and last_name='A'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: range
possible_keys: idx_name
key: idx_name
key_len: 94
ref: NULL
rows: 32574
Extra: Using index condition
1 row in set (0.00 sec)
索引提示
MySQL支持Index hint（索引提示），可以显示的告诉查询优化器使用指定的索引；大多数的情况下，使用优化器自己选择的索引就好，不建议在SQL中强制指定索引。select xx from t ignore index(x) ...;
select xx from t force index(x) ...;
覆盖（索引）查询
如果从二级索引中就可以获取全部的查询数据，即select列、where条件涉及列、order by列和group by列都包含在二级索引中，我们就称这个查询叫覆盖查询。在理解聚族索引后，我们已经明白，查询中涉及的非二级索引列需要再经过一次聚族索引才能得到。一个覆盖查询只需二级索引即可完成，可以减少IO，通常情况下覆盖查询的性能也会好一些，这也告诉我们‘select *’这样的查询为什么不好。看下面的示例，第1个查询是覆盖查询，在Extra中有‘Using index’的信息；第2个查询不是覆盖查询，因为查询列包含非idx_name索引中的列hire_date。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: range
possible_keys: idx_name
key: idx_name
key_len: 44
ref: NULL
rows: 32574
Extra: Using where; Using index; Using filesort
1 row in set (0.00 sec)
mysql> explain select first_name,last_name,<span style="color: rgb(255, 0, 0);">hire_date </span>from employees where first_name like 'G%' order by last_name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: range
possible_keys: idx_name
key: idx_name
key_len: 44
ref: NULL
rows: 32574
Extra: Using index condition; Using filesort
1 row in set (0.00 sec)
多表连接查询
索引可以提高连接查询的效率，这里不详细介绍连接查询，大家只需要了解索引可以提高多表连接查询性能，看下面一个简单地示例（前一个查询无索引，后一个带索引），表index_t1和index_t2都只有一个integer类型的列，分别有20w（1~200000）、10w（1~100000）数据：mysql> select count(1) from index_t1 t1 innerjoin index_t2 t2 on t1.a=t2.b;
+----------+
| count(1) |
+----------+
| 100000|
+----------+
1 row in set (29 min 50.90 sec)
select count(1) from index_t1 t1 inner joinindex_t2 t2 on t1.a=t2.b;
+----------+
| count(1) |
+----------+
| 100000|
+----------+
1row in set (0.14sec)
索引排序
从聚族索引我们知道，InnoDB 组织好的数据本身是按照主键排好序的，因此我们在查询中可以利用索引排序，达到提高查询效率的目的。自适应Hash索引（Adaptive hash index）
虽然MySQL5.5的InnoDB存储引擎目前还不支持Hash索引，但其内部已经有利用Hash索引来优化查询了。当InnoDB发现某些索引值被使用得非常频繁时，它会在内存中基于B+Tree创建一个Hash索引，这是一个完全自动的内部行为，用户可以通过‘innodb_adaptive_hash_index’配置项来开关该功能。
MRR （Multi-Range-Read）
MySQL5.6开始支持MRR，单从名字很难理解MRR是啥。我们分析下查询过程吧，通过前面我们知道使用二级索引查询时需要两跳，二级索引已经有序，但其对应的主键不一定有序，没有MRR时，通过二级索引查一条就通过主键取一条，整个过程是随机IO（一条一条从主键取数据不连续）；有MRR后，先把二级索引和主键一起查询出来，再排序，最后通过排好序的主键取其他需要的数据（连续IO）。可以通过下面的命令查看MRR优化是否开启；其中‘mrr_cost_based’表示MRR优化是否自动根据查询成本来启用，如果关闭，就表示一定使用MRR。
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,
engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,
batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,
subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)
set @@optimizer_switch='mrr=on,mrr_cost_based=on';
mysql> explain select * from salaries where (from_date between '1986-01-01' and '1995-01-01') and (salary between 38000 and 40000)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: salaries
type: range
possible_keys: idx_salaries
key: idx_salaries
key_len: 4
ref: NULL
rows: 210740
Extra: Using index condition; <span style="color: rgb(255, 0, 0);">Using MRR</span>
1 row in set (0.02 sec)
索引合并 （Index merge）
执行查询时，MySQL一般只选择一个索引，但在一些特殊情况下，MySQL会选择多个，最常见的索引合并就是2个索引结果取并集，当查询条件中对2个有较高基数的索引执行OR时，会出现；如下，在employees表上分别创建了idx_name(first_name, last_name)，idx_birth(birth_date)，执行下面的查询时，MySQL就会做索引并集合并。*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: index_merge
possible_keys: idx_name,idx_birth
key: idx_name,idx_birth
key_len: 44,3
ref: NULL
rows: 310
Extra: Using sort_union(idx_name,idx_birth);Using where
1 row in set (0.00 sec)
查看优化开启状态和设置方法类似于MRR。
ICP （Index-Condition-Pushdown）
我们知道MySQL服务和存储引擎是通过API联系的，这样的模式才使MySQL可以替换存储引擎、拥有丰富的存储引擎成为可能；同样，这也给内部实现时带来一些限制，没有ICP时，查询中的where条件过滤都是在MySQL服务端处理的，即存储引擎获取到数据，全部返回给MySQL服务，MySQL服务再做过滤，这样会导致存储引擎返回很多冗余数据；有了ICP后，过滤处理下移，在存储引擎查询出数据做过滤，再将过滤的数据返回给MySQL服务，看下面的示例，Extra里面出现了‘Using index condition’就表示使用了ICP：*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: salaries
type: range
possible_keys: idx_salaries
key: idx_salaries
key_len: 4
ref: NULL
rows: 23378
Extra: <span style="color: rgb(255, 0, 0);">Using index condition;</span> Using MRR
1 row in set (0.00 sec)
查看优化开启状态和设置方法类似于MRR。
索引使用注意
索引失效
下面的一些情况会导致索引失效：1、查询中使用 like通配
2、查询中对索引列使用内置函数
3、需遵循最左匹配原则，如：有index（C1，C2），如果查询中只使用C2是无法利用到这个索引的
索引膨胀
索引固然能够提高查询效率，创建索引时是会占用存储空间的，看下面的示例：((data_length+index_length)/1024/1024,2) total_s
,round((data_length)/1024/1024,2) data_s,round((index_length)/1024/1024,2) index_s from
INFORMATION_SCHEMA.TABLES where TABLE_NAME='employees'\G
*************************** 1. row ***************************
table_name: employees
engine: InnoDB
row_format: Compact
table_rows: 299920
avg_row_length: 50
total_s: 14.52
data_s: 14.52
index_s: 0.00
1 row in set (0.00 sec)
mysql> create index emp_names on employees(first_name, last_name);
Query OK, 0 rows affected (5.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select table_name,engine,row_format,table_rows,avg_row_length,round
((data_length+index_length)/1024/1024,2) total_s,
round((data_length)/1024/1024,2) data_s,round((index_length)/1024/1024,2) index_s from
INFORMATION_SCHEMA.TABLES where TABLE_NAME='employees'\G
*************************** 1. row ***************************
table_name: employees
engine: InnoDB
row_format: Compact
table_rows: 299698
avg_row_length: 50
total_s: 22.03
data_s: 14.52
index_s: 7.52
1 row in set (0.00 sec)
小结
索引优点：1、 减少数据库扫描数据量，以提高性能
2、 帮助排序和避免使用临时表，以提高性能
3、 索引可以将随机IO转化为顺序IO，以提高性能
缺点：
1、索引需要占用磁盘空间，如果表的数据量非常大，创建索引引起的空间膨胀会比较严重，进而备份恢复的时间会增加
2、索引可以提高查询性能，但是反过来会降低更新、插入效率
索引的创建、使用并没有完全固定的标准，上面提到的只是一些实践经验，在实际环境中，不同类型的应用、不同版本的MySQL都会有差别，具体应用具体分析，具体问题具体分析解决；优化的过程需要充分了解数据库本身，同时自己具备丰富的手段和方法。
---------------------------------------------------------
参考
1、http://dev.mysql.com/doc/refman/5.6/en/ （官方文档是最好的学习资料）2、《High performance MySQL 3rd Edition》
3、《Effective MySQL》
4、《MySQL技术内幕 SQL编程》
2727
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
