前言(可以跳过直接看正文)
索引的基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。
索引的原理很简单，就是把无序的数据变成有序的。读取数据时，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据。如下图，MySQL索引默认结构为：
MySQL索引改良后的结构为：
索引设计的原则
- 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
- 基数较小的类，索引效果较差，没有必要在此列建立索引
- 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
- 不要过度索引，索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可
创建索引的原则
索引虽好，但也不是无限制的使用，最好符合一下几个原则：
- 最左前缀匹配原则
- 较频繁作为查询条件的字段才去创建索引，更新频繁字段不适合创建索引
- 若是不能有效区分数据的列不适合做索引列，如性别，男女未知，最多也就三种，区分度实在太低，尽量的扩展索引，不要新建索引
- 定义有外键的数据列一定要建立索引
- 对于那些查询中很少涉及的列
- 对于定义为text、image和bit的数据类型的列不要建立索引
正文
使用索引查询一定能提高查询的性能吗？
通常，通过索引查询数据比全表扫描要快。合理的索引设计，可以大大减少慢SQL，但是我们也必须注意到它的代价。
索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:
- 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
- 基于非唯一性索引的检索
怎样查看索引是否有高选择性？
通过Show Index结果中的列Cardinality来观察。非常关键，表示所以中不重复记录的预估值，需要注意的是Cardinality是一个预估值，而不是一个准确值基本上用户也不可能得到一个准确的值，在实际应用中，Cardinality/n_row_in_table应尽可能的接近1，如果非常小,那用户需要考虑是否还有必要创建这个索引。
在InnoDB存储引擎中，Cardinality统计信息的更新发生在两个操作中：insert和update。InnoDB存储引擎内部对更新Cardinality信息的策略为:
表中1/16的数据已发生了改变
stat_modified_counter>2000 000 000
用一条SQL查看低效的索引
根据Cardinality/n_row_in_table的大小，我们可以通过以下SQL查找某个schema（或者整个数据库实例）中，低效的索引，如下：
SELECT TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
COLUMN_NAME,
CARDINALITY,
TABLE_ROWS,
index_rate
FROM (SELECT a. TABLE_SCHEMA,
a.TABLE_NAME,
a.INDEX_NAME,
a.COLUMN_NAME,
a.CARDINALITY,
b.TABLE_ROWS,
LEFT(IF(b. TABLE_ROWS = 0 || b. TABLE_ROWS IS NULL,
0.00,a.CARDINALITY/b.TABLE_ROWS),4) AS index_rate
FROM (SELECT TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
COLUMN_NAME,
CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA='xxxx' -- xxxx为schema的名字
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS a JOIN information_schema.TABLES AS b
ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
and table_rows > 100000) c
where CARDINALITY < 100
order by 5 asc,6 desc
limit 10;
说明：低效的索引虽然大多时候不会被SQL执行计划选中，但是，不仅仅占用宝贵数据库空间，而且在INSERT，DELETE，UPDATE时需要对索引进行维护，降低性能，所以，低效的索引必须清理掉。
