博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址:http://blog.chinaunix.net/uid-31396856-id-5793386.html

我们在书写SQL语句的时候，总是希望sql语句使用索引进行高效查询；我们在优化SQL的时候，也希望使用索引降低IO提高效率，那么MySQL是怎样使用索引的呢，MySQL遵从B+Tree的最左前缀原理。
一、索引的最左匹配原理。
 当B+Tree的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，(name,age,sex),相当于(name)单列搜素，(name,age)组合索引以及(name,age,sex)组合索引。
二、 key_len介绍
常通过执行计划中 key_len列来衡量索引的利用情况，key_len 用于表示本次查询中，所选择的索引长度有多少字节，通常我们可借此判断联合索引有多少列被选择了。key_len 大小的计算规则是：
一般地，key_len 等于索引列类型字节长度，例如int类型为4 bytes，bigint为8 bytes；
如果是字符串类型，还需要同时考虑字符集因素，例如：CHAR(30) UTF8则key_len至少是90 bytes；
若该列类型定义时允许NULL，其key_len还需要再加 1 bytes；
若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引也被视为动态列类型），其key_len还需要再加2bytes;
三、MySQL索引类型
 可以按照索引的列数，把MySQL索引分为单列索引和多列索引。MySQL中的索引可以以一定顺序引用多个列，就是联合索引。联合索引属于多列索引，也可以把单列索引看到列数为一的特殊的多列索引。
四、索引的使用
以employees.titles表为例，我们来下面先查看的建表语句
(root@localhost:)[employees]> show create table employees.titles\G
*************************** 1. row ***************************
       Table: titles
Create Table: CREATE TABLE `titles` (
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`title`,`from_date`),
  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
表employees.titles上都有哪些索引：
(root@localhost:)[employees]> show index from employees.titles;


1、全列匹配
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

进行精确匹配的时候(=或者in)，使用索引，并且都是常量。而且不受条件顺序的影响


2、最左前缀匹配

条件为联合索引的第一列，SQL使用到联合索引。可以看出(`emp_no`,`title`,`from_date`)其实等于单列索引(`emp_no`)

3、查询条件用到了索引中列的精确匹配，但是没有部分索引列条件
(root@localhost:)[employees]> 
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';

因为条件中没有索引的第二列，因此只用到索引的第一列。
考虑到使用索引的效率，并且结合实际情况title只有7个值，因此可以改写为如下SQL语句提高性能。
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001'
    ->        AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
    ->        AND from_date='1986-06-26';

可以看到完全使用到索引，思考提高了多少性能呢？

4、查询条件中没有用到索引的第一列
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';

不满足最左前缀，无法使用到索引。oracle有index skip scan的说法，但是性能也很差。

5、匹配索引某列的前缀字符串
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';

可以看到用到索了引，如果通配符％不出现在开头，则可以用到索引。

6、范围查询
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';

再看，
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles
    ->        WHERE emp_no < 10010
    ->        AND title='Senior Engineer'
    ->        AND from_date BETWEEN '1986-01-01' AND '1986-12-31';

对联合索引进行范围扫描的时候，无法使用到接下来的索引列。
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles
    ->        WHERE emp_no BETWEEN '10001' AND '10010'
    ->        AND title='Senior Engineer'
    ->        AND from_date BETWEEN '1986-01-01' AND '1986-12-31';


可以看到BETWEEN '10001' AND '10010'，这是因为BETWEEN ... AND ... 的作用相当于“IN”，IN是进行精确过滤匹配。

7、查询条件中含有函数或表达式
表达式情况下：
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';

看函数的情况下
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';


8、覆盖索引
条件和select查询的数据都在索引里，直接从索引中取得结果，不需要做回表操作。
(root@localhost:)[employees]> EXPLAIN SELECT emp_no,from_date,title FROM employees.titles WHERE emp_no>10001;

可以看到执行计划中表示的sql执行中Using index 。
(root@localhost:)[employees]> EXPLAIN SELECT emp_no,from_date,title,to_date FROM employees.titles WHERE emp_no>10001;

显然，无法直接从索引获取to_date的数据。
以上列举了MySQL使用索引对SQL执行计划的影响，合理的使用好索引会大大的提高sql的性能。

---The End