背景
MySQL数据库作为数据持久化的存储系统，在实际业务中应用广泛。在应用也经常会因为SQL遇到各种各样的瓶颈。最常用的MySQL引擎是innodb，索引类型是B-Tree索引，增删改查等操作最经常遇到的问题是“查”，查询又以索引为重点（没索引不是病，慢起来太要命）。踩过O2O优惠券、摇一摇周边两个业务的一些坑，当谈到SQL优化时，想分享下innodb下B-Tree索引的一些理解与实践。
接下来的内容，安排如下：
- 介绍索引的工作原理；
- 引用实例具体介绍索引；
- 如何使用explain排查线上问题；
- 实际碰到的问题汇总；
索引如何工作
当查询时，MySQL的查询优化器会使用统计数据预估使用各个索引的代价（COST），与不使用索引的代价（COST）比较。MySQL会选择代价最低的方式执行查询。MySQL如何使用索引，可以用下面的伪代码来说明：
min_cost = INIT_VALUE min_cost_index = NONE for(index in all_indexs): if (index match WHERE_CLAUSE): cur_cost = COST(index) if(cur_cost < min_cost): min_cost = cur_cost min_cost_index = index
INIT_VALUE：不使用索引时的代价
all_indexs：查询表上所有的索引COST：基本是由“估计需要扫描的行数”（rows）来确定
WHERE_CLAUSE：查询SQL中的WHERE子句
大致的意思：MySQL会遍历该查询相关的表（table）的每一条索引，然后判断该索引能否被本次查询使用（possible_keys）。当索引可以使用时，MySQL预估使用该索引进行查询的cost，然后选择预估代价最低的代价的方式（key）执行查询。
索引匹配(match)
怎样判断索引是否匹配（match）SQL查询？
1、索引的左前缀规则；索引中的列由左向右逐一匹配，如果中间某一列不能使用索引则后序列不在查询中不再被使用。
例如，如果有一个3列索引(str_col1,col2,col3)，其中str_col1为字符串，则对(str_col1)、(str_col1,col2)和(str_col1,col2,col3)上的查询进行了索引。
如果列不构成索引最左面的前缀，MySQL不能使用索引。假定有下面显示的SELECT语句。
SELECT * FROM tbl_name WHERE str_col1=val1; SELECT * FROM tbl_name WHERE str_col1=val1 AND col2=val2; SELECT * FROM tbl_name WHERE col2=val2; SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
如果 (str_col1，col2，col3)有一个索引，只有前2个查询使用索引。第3个和第4个查询确实包括索引的列，但(col2)和(col2，col3)不是 (col1，col2，col3)的最左边的前缀。
2、where语句中列的表达式为=、>、>=、<、<=、BETWEEN、ISNULL或者LIKE ’pattern’（其中’pattern’不以通配符开始）
3、每个AND组作为表达式匹配索引。
SELECT * FROM tbl_name WHERE (str_col1=val1 OR col4 =val4) AND col2=val2;
因为str_col1=val1 OR col4 =val4作为一组，col4不匹配索引中的列，所以查询不匹配索引。
4、如果表达式中存在类型转换或者列上有复杂函数则与该列不匹配索引中的列。
SELECT * FROM tbl_name WHERE str_col1=1; SELECT * FROM tbl_name WHERE SUBSTRING(str_col1,1,8) = ‘title’;
第1个查询，因为1是整数、str_col1是字符串，所以不匹配索引；第2个查询str_col1有复杂函数，同样不匹配索引。
索引的COST
MySQL如何计算索引的COST？
索引的cost基本是由“估计需要扫描的行数”（rows）来确定。数据来源于information_schema，在MySQL启动的时候读入内存，运行时只使用内存值，存储引擎会动态更新这些值。
我们可以通过explain看下“估计需要扫描的函数”，可以通过optimizer_trace查询适用每一条SQL的具体的cost值。explain也是线上排查问题的利器，后面会重点介绍。
索引实例分析
索引的字段究竟是怎么从where语句中提取，并被MySQL使用呢，下面将以一个实例分析这个过程。内容全文为摘取何登成的文章《SQL中的where条件，在数据库中提取与应用浅析》，并做了部分删改。
我们创建一张测试表，一个索引索引，然后插入几条记录。(注意：下面的实例，使用的表的结构不是InnoDB引擎所采用的聚簇索引表。图例仅为说明，原理适用innodb)
create table t1 (a int primary key, b int, c int, d int, e varchar(20)); create index idx_t1_bcd on t1(b, c, d); insert into t1 values (4,3,1,1,’d’); insert into t1 values (1,1,1,1,’a’); insert into t1 values (8,8,8,8,’h’): insert into t1 values (2,2,2,2,’b’); insert into t1 values (5,2,3,5,’e’); insert into t1 values (3,3,2,2,’c’); insert into t1 values (7,4,5,5,’g’); insert into t1 values (6,6,4,4,’f’);
