正确的创建和使用索引是实现高性能查询的基础。
因为每次查索引本身也是一次IO，较小的索引可以有更好的性能。
使用索引的操作符 <,<=,=,>,>=,between, IN
like：‘name%’使用索引，’%name%’不适用索引
扫描的记录数超过30%会进行全表扫描
如果查询中的列不是独立的，则mysql就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。
- 示例一：索引列不能是表达式的一部分
SELECT item_code FROM cc_item WHERE item_code + 1 = '282005';
- 示例二：索引列不能是函数的参数
SELECT ... WHERE TO_DAYS(CURRENT_DATE()) - TO_DAYS(date_col) <= '10';
有时候需要索引很长的字符列，这会让索引变的大且慢，一个策略是模拟哈希索引。
通常还可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性。索引的选择性越高则查询效率越高，因为选择性高的索引可以让mysql在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为mysql不允许索引这些列的完整长度。
诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。
计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。
mysql> SELECT COUNT(DISTINCT last_name)/COUNT(*) FROM people;
+------------------------------------+
| COUNT(DISTINCT last_name)/COUNT(*) |
+------------------------------------+
| 0.8462 |
+------------------------------------+
通常来说，示例中如果前缀的选择性能够接近于0.846，基本上就可用了。可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用。
mysql> SELECT COUNT(DISTINCT LEFT(last_name, 2))/COUNT(*) AS sel2,
-> COUNT(DISTINCT LEFT(last_name, 3))/COUNT(*) AS sel3,
-> COUNT(DISTINCT LEFT(last_name, 4))/COUNT(*) AS sel4,
-> COUNT(DISTINCT LEFT(last_name, 5))/COUNT(*) AS sel5,
-> COUNT(DISTINCT LEFT(last_name, 6))/COUNT(*) AS sel6,
-> COUNT(DISTINCT LEFT(last_name, 7))/COUNT(*) AS sel7,
-> COUNT(DISTINCT LEFT(last_name, 8))/COUNT(*) AS sel8,
-> COUNT(DISTINCT LEFT(last_name, 9))/COUNT(*) AS sel9
-> FROM people;
+--------+--------+--------+--------+--------+--------+--------+--------+
| sel2 | sel3 | sel4 | sel5 | sel6 | sel7 | sel8 | sel9 |
+--------+--------+--------+--------+--------+--------+--------+--------+
| 0.4615 | 0.4615 | 0.7692 | 0.7692 | 0.7692 | 0.7692 | 0.7692 | 0.8462 |
+--------+--------+--------+--------+--------+--------+--------+--------+
只看平均选择性是不够的，也有例外的情况，需要考虑最坏情况下的选择性。如果数据分布很不均匀，可能就会有陷阱。
前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：mysql无法使用前缀索引做GROUP BY和ORDER BY，也无法使用前缀索引做覆盖扫描。
一个常见的错误是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。为每个列创建独立索引的策略，一般是听从“把WHERE条件里面的列都建上索引”这种错误建议。
在多个列上建立独立的索引大部分情况下并不能提高mysql的查询性能。mysql 5.0和更新版本引入了一种叫索引合并（index merge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并，这种算法有三个变种：
- OR条件的联合（union）
- AND条件的相交（intersection）
- 组合前两种情况的联合及相交
例如，字段last_name、first_name上各有一个单列索引：
mysql> EXPLAIN SELECT last_name,first_name FROM people WHERE last_name = 'yanzuojing' OR first_name = 'h'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: people
type: ALL
possible_keys: last_name,first_name
key: NULL
key_len: NULL
ref: NULL
rows: 13
Extra: Using where
索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕：
- 当有多个AND条件，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引
- 当有多个OR条件，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上
- 更重要的是，优化器不会把这些计算到查询成本中，优化器只关心随机页面读取。这会使得查询的成本被低估，导致该执行计划还不如直接走全表扫描
正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。
在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。
至于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的，这时候索引的作用只是用于优化WHERE条件的查找。然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。
SELECT * FROM people WHERE last_name = 'yanzuojing' AND first_name = 'h';
上述示例是创建一个(last_name, first_name)索引还是应该颠倒顺序？
根据前述经验法则，应该将last_name放到前面，因为对应值的last_name数量更小，但这样查询的结果非常依赖于选定的具体值。如果没有类似的具体查询来运行，最好还是按经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体查询：
mysql> SELECT COUNT(DISTINCT last_name)/COUNT(*) AS l_select,
-> COUNT(DISTINCT first_name)/COUNT(*) AS f_select,
-> COUNT(*)
-> FROM people;
+----------+----------+----------+
| l_select | f_select | COUNT(*) |
+----------+----------+----------+
| 0.8462 | 0.9231 | 13 |
+----------+----------+----------+
当表有聚簇索引时，它的数据行实际上存放在索引的叶子叶中。“聚簇”表示数据行和相邻的键值紧凑的存储在一起。
InnoDB通过主键聚集数据，即上图中被索引的列就是主键列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。
聚集的数据的优点：
- 可以把相关数据保存在一起
- 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快
聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细的考虑聚簇索引，尤其是将表的存储引擎从InnoDB改成其它引擎时。聚簇索引的缺点：
- 聚簇数据最大限度的提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序也就没那么重要了，聚簇索引也就没什么优势了
- 插入速度严重依赖于插入顺序
- 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行时，可能面临页分裂（page split）的问题
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
- 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列
- 二级索引访问需要两次索引查找，而不是一次
二级索引叶子结点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子结点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B-Tree查找而不是一次，对于InnoDB，自适应哈希索引能够减少这样的重复工作。
聚簇索引的每一个叶子结点都包含了主键值、事务ID、用于事务和多版本控制(MVVC)的回滚指针以及所有的剩余列。
如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。
最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用，它使得聚簇索引的插入变的完全随机，使数据没有任何聚集特性。使用InnoDB时应该尽可能的按主键顺序插入数据，并且尽可能的使用单调增加的聚簇键的值来插入新行。
如果一个索引包含（或者覆盖）所有需要查询的字段的值，则称之为覆盖索引，优点如下：
- 索引条目通常远小于数据行大小，所以如果只需要读取索引，mysql就会极大的减少数据访问量
- 因为索引是按照列值顺序存储（至少在单个页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多
- 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子结点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询
不是所有类型的索引都可以成为覆盖索引，覆盖索引必须要存储索引列的值，
而哈希索引、空间索引和全文索引等都不存储索引列的值，所以mysql只能使用B-Tree索引做覆盖索引。
InnoDB的二级索引的叶子结点都包含了主键的值，这意味着InnoDB的二级索引可以有效的利用这些“额外”的主键列来覆盖查询。
mysql有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描。如果EXPLAIN中的type列的值为index，则表明mysql使用了索引扫描来做排序。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取的速度要比顺序的全表扫描慢。
有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候，示例如下，
KEY last_name (first_name,last_name) USING BTREE：
SELECT dob,address FROM people WHERE first_name = 'm' ORDER BY last_name\G
如果创建了索引(A, B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。
mysql的唯一限制和主键限制都是通过索引实现。
大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变的太大，从而影响其他使用该索引的查询的性能。
一般来说，增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。
索引可以让查询锁定更少的行。如果查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处：
- 首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销
- 其次，锁定超过需要的行会增加锁争用并减少并发性
在mysql 5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁。
InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）。这消除了使用覆盖索引的可能性，并且使得SELECT ... FOR UPDATE 比 LOCK IN SHARE MODE或非锁定查询要慢的多。