索引的代价和选择
空间代价
每个建立的索引树，每个树节点都是一个数据页，每个页需要16kb的空间，而数据量很大的表，索引树将会占用较大的空间
时间代价
每次对数据做增删改的时候，都需要去修改各个B+树索引，因为不管页面之间的次序跟本页内的用户记录都是以索引值进行排序的，而增删改操作都有可能会对这个排序产生破坏，所以需要专门进行维护，根据情况进行记录的移位、页分裂、页回收
结论
所以当一个表的索引的键越多，就会占用越多的空间，并且增删改时的效率就会越差
B+树索引的适用条件
CREATE TABLE person_info(
id INT NOT NULL auto_increment,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);
-- 创建一个表，该表有两个索引，一个主键的聚簇索引，另外一个 name，birthday和phone_number的联合索引
全值匹配
如果搜索的值与索引的列完全一致时，这个情况称之为全值匹配
比如：
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239'
这个时候，索引建立依赖的三个列都在 where 语句中，而且我们之前了解到，二级索引树的联合索引其实是有次序的，会先比较最前面的列，相同时再比较第二个列，依次类推，但是 where 条件并不影响，因为 MySQL 的优化器会完成这一个优化，让次序不对也会尽可能按索引建立的次序来完成查询
匹配左边的列
建立了联合索引，我们也可以不包含所有的索引列进行查询
比如：
-- 会使用索引
SELECT * FROM person_info WHERE name = 'Ashburn';
-- 会使用索引
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';
-- 不会使用索引
SELECT * FROM person_info WHERE birthday = '1990-09-27' AND phone_number = '15123983239';
以上几种情况是建立二级索引树的方式所决定的，因为建立索引树时，会进行排序，排序规则就是最前比较第一个索引列，相同时再比较第二个索引列，所以当不是全部索引列进行查找时，需要以所以创建列从左到右的顺序连续的列才会使用到联合索引，比如上面例子第三个，因为没有 name，所以索引树根本不能提供帮助，所以只能走全表遍历
匹配列前缀
单个列创建的索引或者联合索引，都是先用同一个字段进行大小的比较，比较规则由字符集和排序规则确定，而字符串的比大小一般都是按字符依次比较大小，最终完成整个字符串大小的比较，所以进行匹配时也可以不是完整的字符串或者最前面的一部分也可以使用索引
比如：
-- 会使用索引
SELECT * FROM person_info WHERE name LIKE 'Ash%';
-- 不会使用索引
SELECT * FROM person_info WHERE name = '%burn';
值得注意的是，因为建立二级索引的时候，是按字符串从前往后依次比较，所以有前缀进行匹配可以使用索引，但是如果字符串前缀是通配的话，就不能使用索引而只能走全表搜索，例如上面例子的第二个 sql就不会使用索引
匹配范围值
根据索引树的创建规则，所有的元素都是按索引列的值进行排序的，所以这非常方便我们查找某个范围内的记录
查询过程就是，先找到左区间值，再找到又区间值，然后将区间内的记录都可以遍历出来，如果是二级索引，就通过查出来的主键值再回表查询完整的记录
如果是联合索引，这样只有最左侧的索引列的范围比较才可以使用索引
比如：
-- 会使用索引
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
-- 不会使用索引
SELECT * FROM person_info WHERE birthday > '1980-01-01';
精准匹配某一列并范围搜索另外一列
对联合索引而言，如果是先精准匹配某一列，那这一列右边的一列就可以进行范围匹配
比如：
-- 会使用索引
SELECT * FROM person_info WHERE name = 'Asa' AND birthday > '1980-01-01';
-- 不会使用索引
SELECT * FROM person_info WHERE birthday > '1980-01-01';
-- 以下情况，name 将使用联合索引精准匹配，birthday将使用联合索引范围匹配，而 phone_number 因为查出的记录，phone_number的次序并不确定，所以只能对结果进行遍历时进行筛选
SELECT * FROM person_info WHERE name = 'Asa' AND birthday > '1980-01-01' AND phone_number > '18200000000';
-- 以下情况的所有筛选条件都会走索引过
SELECT * FROM person_info WHERE name = 'Asa' AND birthday = '1980-01-01' AND phone_number > '18200000000';
用于排序
我们对已经查询出来的记录，可以使用 ORDER BY 语句进行排序，而排序一般都必须将数据全部加载至内存中，才能进行，如果结果集过大，还需要额外利用磁盘空间存放中间结果，在 MySQL 中在内存或者磁盘中完成排序的过程称之为 filesort ，但是如果排序是利用到了 索引，就可以省去了内存或磁盘中排序的过程
比如：
-- 会使用联合索引，通过联合索引进行排序则直接按索引顺序访问即可，但是 ORDER BY 必须按着索引列定义的顺序才行
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;
-- filesort + 使用索引
SELECT * FROM person_info ORDER BY birthday, name, phone_number LIMIT 10;
-- 会使用联合索引，如果左边的列是常量，那边右边的列也可以用于排序
SELECT * FROM person_info WHERE name = 'Asa' ORDER BY birthday, phone_number LIMIT 10;
-- filesort
SELECT * FROM person_info ORDER BY birthday LIMIT 10;
EXPLAIN 测试与预期不符
上面在测试时，使用 EXPLAIN 来查看执行计划的时候发现，SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10; 依然显示的是使用 filesort 而不是 using index，这意味索引并没有生效，后面回表的代价中会专门再说明这个问题
ASC 及 DESC 混用将不会使用索引
如果要使用联合索引，那么每个字段的排序要么都是 ASC 要么都是 DESC，因为实际索引是按联合索引的每个字段，按从小到大的顺序进行排序的，那么要么正序访问，要么倒序访问，如果排序方式混合使用，那么索引的排序就不对，就需要重新排序
WHERE 子句出现非排序使用到的索引列
因为 WHERE 子句有了非排序使用到的索引列，那么 InnoDB 就会先搜索符合 WHERE 子句的记录，然后再进行排序，比如： SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10; 就不会使用索引，但是像：``SELECT * FROM person_info WHERE name = ‘Asa’ ORDER BY name LIMIT 10;虽然也有 WHERE 子句，但是可以索引本身也是通过 name，birthday，phone_number三个字段排序的，这个规则类似于之前提到的匹配左边的列`
排序列包含非同一索引的列
如果排序时，排序字段并非同一索引的列，这个时候依然不会使用索引，因为索引树就是按固定的列进行排序的，所以排序列有一个对应不上，就无法使用，比如：SELECT * FROM person_info ORDER BY name, country LIMIT 10; 就不会使用索引
排序列使用了复杂的表达式
如果要用索引进行排序操作，那么必须保证索引列是单独列的存在，而不是修饰过的，比如: SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10; 就不会使用索引
用于分组
有时候为了方便统计，会将表中的数据进行分组，比如以下的分组查询
-- 使用索引
SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number
分组跟排序本质差别不大，都是根据指定的列进行大小的比较，只是比较之后的行为不一样，所以 GROUP BY 的限制跟 ORDER BY 的限制都差不多，指定的列名必须跟索引的顺序一致，也必须从左边的列进行分组之类的等等
回表的代价
结合之前我们创建的表，一个通过二级索引查询的语句，会先通过二级索引将符合条件的用户记录取出，此时二级索引保存的仅仅是建索引保存的列及主键值，所以查询数据如果还需要其他的记录数据，就需要回表，通过主键去聚簇索引中查询完整的用户记录
这里需要说明下，二级索引因为是排序的，所以通过二级索引条件进行搜索的记录一般都是在一起的几个数据页，所以需要顺序访问就能快速读取到数据，这里称之为顺序IO，而与之相反的是，查询出的中间结果的主键值，在聚簇索引中的位置，可能极其分散，所以需要一个个的获取数据，这时这就称之为随机IO
顺序IO 往往会比随机IO 性能高几个数量级，所以当通过二级索引查询大量记录时，如果需要大量回表操作，那么二级索引的效率就会非常的低，甚至不如直接去聚簇索引中遍历进行搜索，而 MySQL 的优化器会为我们做这个判断，当判断二级索引效率可能不如直接全表排序的，那么就算可以用索引也会直接进行全表的排序搜索
比如之前的例子：
-- 这个例子按之前的分析，应该是会使用索引的，但是使用 EXPLAIN 查看执行计划却发现是 filesort
-- 1. 如果表本身记录很少，容易让 MySQL 优化器判断，回表甚至不如直接排序搜索，而直接用 filesort
-- 2. 如果本身表记录非常多，但是 SQL 查询的结果也非常大，那么 MySQL 优化器也会判断直接使用 filesort可能效率更高
SELECT * FROM person_info ORDER BY name, birthday, phone_number;
-- 对于上面的第2种情况，我们加上 LIMIT 时，就会更容易让 MySQL 优化器判断使用索引效率更高，那么用 EXPLAIN 查看时就会显示 using index
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;
覆盖索引
使用二级索引时，如果完全不需要回表，显然是效率最高的，所以当我们查询的列在二级索引中全部都能提供时，那么将不需要再回表查询完整的用户记录，此时这种查询方式称之为 覆盖索引
比如：
-- 查询完整的记录，所以需要回表
SELECT * FROM person_info ORDER BY name, birthday, phone_number;
-- 查询的数据在二级索引中都能提供，所以不需要回表，覆盖索引
-- 覆盖索引时，就算没有 LIMIT 也不会再使用 filesort，因为之前优化器就是担心回表的性能问题，而覆盖所有完全不需要回表
SELECT id, name, birthday, phone_number FROM person_info ORDER BY name, birthday, phone_number;
所以，如果业务需要查询出索引以外的列，那还是以业务为重，但是不鼓励全部都直接使用 SELECT * 作为查询列表，最好吧需要查询的列依次标明
如何挑选索引
只为用于搜索，排序和分组的列创建索引
如下的查询：
SELECT birthday, country FROM person_info WHERE name = 'Asa';
只为出现在 WHERE 、ORDER BY 和 GROUP BY 的列创建索引，像上面的 sql 我们就只需要对 name 创建索引即可，而不需要对 birthday 和 country 字段创建索引
考虑到列的基数
列的基数是指某一列不重复数据的个数，也就是说列的基数越大，列的值越分散，基数越小，列值越集中。所以列的基数越小，就代表索引的效率越低，因为相同索引值的记录，实际是以主键进行排序的，所以本质上是没有排序的，而查询出来的记录又要回表，所以性能损耗更大，结论是：最好是为那些基数大的列建立索引，基数小的列效果可能不好
索引的列类型尽量小
数据列的类型占用空间并不一样，而且不同的类型，比较的方法也是不一样的，所以一般尽量选占用小的类型，这样比较速度会更快(CPU开销)，而占用小的类型意味着一页能存放更多的记录，所以查询时会减少访问的数据页，从而减小磁盘IO
索引字符串值的前缀
按上个总结的，我们尽量选取占用小的类型，但是业务需要一定是比较的大的类型，比如 varchar，编码是utf8，那么每个字符都将占用1-3字节，所以如果字符串很长，那意味着索引的每条记录都占用比较大的空间，而且字符串是按字符每个字符进行比较，所以比较的速度也很慢，所以字符串越长，索引的效率越低，所以索引的设计者提出这样一个解决方案：只对字符串的前缀进行建立索引，这样虽然不能精确确定记录，但是能定位到相应前缀所在的位置，然后在回表时再对比，所以建立索引可以参考如下方式：
CREATE TABLE person_info(
id INT NOT NULL auto_increment,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);
-- 这里创建索引时，就只用了name字段的前10位进行创建索引
索引前缀对排序的影响
因为只有将索引列的一部分创建了索引，所以索引的次序并不是索引列的真实次序，所以需要排序的时候，以前缀创建的索引并不能生效，只能走 filesort
让索引列在表达式中单独出现
-- 假设表中有一整数列为 my_col
WHERE my_col * 2 < 4
WHERE my_col < 4/2
上面这两种写法看着效果相同，但是前者不会使用索引而后者会，所以要保证索引列在比较表达式中要单独存在，而不是表达式或者函数调用的方式出现，后者不会使用到索引
主键插入顺序
InnoDB 会默认帮我们创建一个聚簇索引，而所有的数据都是聚簇索引的一个叶子节点，这时根据 b+树的规律，如果插入的顺序忽左忽右，就会导致前面的数据会移位，如果数据页满了就会导致页面分裂，而随机大小的值可能导致树有很多的碎片空间产生，而递增的主键值则会一直将数据添加到最后面，然后不端添加新的数据页即可，显然后者的效率更高，所以建议：让主键具有 AUTO_INCREMENT，让储存引擎自己生成主键而不是手动插入
冗余和重复索引
CREATE TABLE person_info (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
KEY idx_name (name(10))
);
这里，通过 idx_name_birthday_phone_number 索引即可对 name 列完成搜索，所以根本不需要再创建一个索引 idx_name ，多一个索引反正会增删改时的维护成本
像下面这样：
CREATE TABLE repeat_index_demo (
c1 INT PRIMARY KEY,
c2 INT,
UNIQUE uidx_c1 (c1),
INDEX idx_c1 (c1)
);
C1 列本身就是主键了，又为 c1 列增加了一个唯一索引和一个普通的索引，这些都是重复的，需要避免