一，索引基础
1，索引的类型 ：B-Tree索引
B-Tree索引意味着所有的值都是按顺序存放的，并且每一个叶子页到根到距离相同。B-Tree索引之所以能加快数据的速度，是因为存储引擎不再需要根据全表扫描来获取需要的数据，取而代之的是从索引的根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针指向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层节点。
接下来来看一个复合索引的例子 假如我们要一个表定义如下：
CREATE TABLE People {
last_name varchar(20) not null,
first_name varchar(20) not null,
dob date not null,
gender enum('m','f') not null,
key(last_name, first_name, dob)
};
对于表中每一行数据，索引中包含了last_name,firtst_name,dob的值，如下图所示
索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。所以最后两条数据，两个人姓名都一样，于是按照出生日期来排序。
注意：
1，B-Tree适用于全键值(和索引所有列进行匹配)，匹配最左前缀，匹配列前缀，匹配范围值，精确匹配某一列并范围匹配另外一列，值访问索引的查询。
2，使用限制：(1)如果不是按照索引的最左列开始查找，那么无法使用索引。(2)不能跳过索引的列，例如不能使用last_name,dob的索引，跳过first name。(3)如果查询中某个列有范围查询那么其右边所有列都无法使用索引优化查询。例如 WHERE last_name = ‘Allen’ AND first_name LIKE ‘J%’ AND dob = ‘1996-12-11’，那么只能使用到前两列的索引。
到这里我我们发现，索引列的顺序非常重要，在优化性能时候，可能要使用相同的列担顺序不同的索引来满足不同类型的查询
二，索引的优点
1，索引大大减少了服务器需要扫描的数据量
2，索引可以帮助服务器避免排序和临时表
3，索引可以将随机I/O变为顺序I/O
三，高性能索引策略
1，独立的列
如果我们查询中的列不是独立的，那么我们将无法使用索引，独立的列指的是索引列既不能是表达式的一部分，也不能是函数的参数。
例如：select id from student where id+1=9;
还有：select id from student where to_days(CURRNET_DATE)-to_days(registerDate) <= 10;
2,前缀索引和索引选择性
有时候需要索引很长的字符列，这会让索引变得大而慢，我们通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但是会降低索引的选择性。
一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT或者VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
所以现在的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能过长。
下面举个小例子： 1，我们首先可以从3个前缀字母开始查询:
SELECT COUNT(*) AS cnt LEFT(city,3)AS pref from.....
然后我们可以慢慢增加前缀长度，直到这个前缀的选择性接近完整列的选择性。
3，多列索引
对于多列索引，有些人有这样的误区：为每个列创建独立的索引，或者按照错误的顺序创建多列索引
先看一个错误的例子：
CREATE TABLE{
c1 INT,
c2 INT,
c3 INT,
KEY (c1),
KEY (c2),
KEY (c3)
};
在多个列上建立独立的单列索引大部分情况下并不能提高mysql的查询性能，尽管5.0以上的版本有个index merge的策略。但是在更早的版本只能使用其中的一个单列索引。
再看一个例子：
SELECT film_id,actor_id FROM film_actor WHERE actor_id = 1 OR film_id = 1;
在老的Mysql版本中，此时会进行全表扫描，除非改写成以下：
SELECT film_id,actor_id FROM film_actor WHERE actor_id = 1
UNION ALL
SELECT film_id,actor_id FROM film_actor WHERE film_id = 1
但是在5.0和更新的版本，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。但是我只能说，索引合并策略有时候是一种优化的结果，但是实际上更多时候说明表上的索引相当糟糕。
4，选择合适的索引列顺序
在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY,GROUP BY,DISTINCT等。
这里有一个经验法则：将选择性最高的列放到索引最前列。但是这这是通常情况，最好还是结合具体表进行分析。
聚簇索引和非聚簇索引的对比：
(1)聚簇索引(clustered index)不是一个单独的索引类型，而是一个数据存储方式。具体细节依赖于其实现方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行，聚集索引决定数据在磁盘上的物理排序。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页。因为无法同时把数据行存放在两个不同的地方。所以一个表只能有一个聚簇索引，一般用primary key来约束。
(2)聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。 聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。
(3)InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键(MyISAM可以没有)，如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
(4)辅助索引中，叶结点的data域存放的是对应记录的主键的key。对于建立辅助索引的表需要先根据辅助索引找到相应的主键，再根据主键在聚集索引中找到相应的记录集。
非聚集索引 (non-clustered index)
(1)非聚集索引就是普通索引了，它并不决定数据在磁盘上的物理排序，索引上只包含被建立索引的数据，以及一个行定位符row-locator，这个行定位符，可以理解为一个聚集索引物理排序的指针，通过这个指针，可以找到行数据。
(2)主键索引中，叶节点的data域存放的是数据记录的地址，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。(MYISAM采用此种索引方式)。
区别 (1)聚集索引表里数据物理存储顺序和主键索引的顺序一致，所以如果新增数据是离散的，会导致数据块趋于离散，而不是趋于顺序。而非聚集索引表数据写入的顺序是按写入时间顺序存储的。
(2)聚簇索引索引的叶节点就是数据节点；而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
5、联合索引
多个字段上建立的索引，能够加速复合查询条件的检索
举例，登录业务需求：
select uid, login_time from t_user where login_name=? and passwd=?
可以建立(login_name, passwd)的联合索引。
联合索引能够满足最左侧查询需求，例如(a, b, c)三列的联合索引，能够加速a | (a, b) | (a, b, c) 三组查询需求。
这也就是为何不建立(passwd, login_name)这样联合索引的原因，业务上几乎没有passwd的单条件查询需求，而有很多login_name的单条件查询需求。
提问：
select uid, login_time from t_user where passwd=? and login_name=?
能否命中(login_name, passwd)这个联合索引？
回答：可以，最左侧查询需求，并不是指SQL语句的写法必须满足索引的顺序(这是很多朋友的误解)
6、索引覆盖
被查询的列，数据能从索引中取得，而不用通过行定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。
举例，登录业务需求：
select uid, login_time from t_user where
login_name=? and passwd=?
可以建立(login_name, passwd, login_time)的联合索引，由于login_time字段的数据已经被用来建立在索引中了，被查询的uid和login_time就不用去row上获取数据了，从而加速查询。
末了多说一句，登录这个业务场景，login_name具备唯一性，建这个单列索引就好。
敲黑板：对于高并发工作负载，InnoDB中按主键顺序插入可能会造成明显的争用，主键的上界会成为热点，所有插入的数据都在这里，所以并发插入可能导致间隙锁。另一个则是 AUTO_INCREMENT 的锁机制，这个可以修改 innodb_autoinc_lock_mode 来解决
Refer：
[1] mysql高性能索引的探索
495
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
