设计索引的原则（查询优化最重要的，优先权最高的优化就是建立索引）

书摘from 《深入浅出mysql》

设计索引的原则（查询优化最重要的，优先权最高的优化就是建立索引）

1搜索的索引列，不一定的要选择的列列，换句话说：最适合索引的列是出现在where子句中的列，或连接子句中指定的列，而不是出现在select关键字后的选择列表中的列

2使用唯一索引，越特殊越单一容易区分越好，比如：存放生日日期的列具有不同的值，很容易区分各行，而用来记录性别的列，只包含M或者F则对此列进行索引没有多大的用处，应为不管搜索那个值，都会得出大约一半的行

3使用短索引，如果对字符串进行列索引，应该指定一个前缀长度，只要有可能，就应该这样做，例如：一个char(200)如果在前20或者前10字符内，多数值是唯一的，那么不需要对整个列索引

较小的索引涉及较少的磁盘Io，较短的值比较起来更快，更为重要的较短的键值，索引高速缓存中的块能容纳更多的键值（innodb_pool_siez）因此myql也可以在内存中容纳更多的值，这样就增加了找到行而不用读取索引中较多块的可能性

4利用最左前缀，在创建一个n列的索引时，实际是创键了mysq可利用的n个索引，多列索引可以起N个索引的作用，应为可利用索引中最左边的列集来匹配行，这样的列几称为最左前缀。

5不要过度索引，本身也要占用额外的磁盘空间，在修改表的内容时，索引必须进行更新，有时可能要重构，很少利用或者从不用的索引干掉他，只保持所需的索引有利于查询优化


实例一：
现在我们想查出满足以下条件的用户id：
mysql>SELECT ｀uid｀ FROM people WHERE lname｀='Liu'  AND ｀fname｀='Zhiqun' AND ｀age｀=26
因为我们不想扫描整表，故考虑用索引。


1单列索引：
ALTER TABLE people ADD INDEX lname (lname);
将lname列建索引，这样就把范围限制在lname='Liu'的结果集1上，之后扫描结果集1，产生满足fname='Zhiqun'的结果集2，再扫描结果集2，找到 age=26的结果集3，即最终结果。


由 于建立了lname列的索引，与执行表的完全扫描相比，效率提高了很多，但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引，再创建fname或者age 列的索引，但是，不论在哪个列上创建索引搜索效率仍旧相似。


2.多列索引：
ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);
为了提高搜索效率，我们需要考虑运用多列索引,由于索引文件以B－Tree格式保存，所以我们不用扫描任何记录，即可得到最终结果。


注：在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。


3.最左前缀：顾名思义，就是最左优先，上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。


注：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。


实例二：
假设数据 表T (a,b,c) rowid 为物理位置
rowid a b c
(1) 1 1 1
(2) 2 1 13
(3) 2 2 14
(4) 1 3 3
(5) 2 3 12
(6) 1 2 5
(7) 2 3 9
(8) 1 2 2
(9) 1 3 6
(10) 2 2 11
(11) 2 2 8
(12) 1 1 7
(13) 2 3 15
(14) 1 1 4
(15) 2 1 10


当你创建一个索引 create index xxx on t(a,b), 则索引文件逻辑上等同于如下
a b rowid
1 1 1
1 1 12
1 1 14
1 2 6
1 2 8
1 3 4
1 3 9
2 1 2
2 1 15
2 2 3
2 2 10
2 2 11
2 3 5
2 3 7
2 3 13


当select * from T where a=1 and b=3 的时候， 数据库系统可以直接从索引文件中直接二分法找到A=1的记录，然后再B=3的记录。
但如果你 where b=3 则需要遍历这个索引表的全部！


实例三：
如果查询语句使用索引（通常是where条件匹配索引)就会利用树的结构加快查找，索引会按值查找到要查找的行在表中位置，不需回表查询数据的就是聚簇索引(索引和数据存放在一起)。通常是需要回表再查数据，需要消耗额外的磁盘IO。所以有些时候（如按顺序读取数据）全表扫描会比使用索引快的原因就在于此。


查询条件只有一个字段时，在该字段建立索引即可，可优化的地方是对于text blob字段使用前缀索引。


当查询条件有多个字段时，单列索引和多列索引有很大的区别。如果使用多列索引，where条件中字段的顺序非常重要，需要满足最左前缀列。最左前缀：查询条件中的所有字段需要从左边起按顺序出现在多列索引中，查询条件的字段数要小于等于多列索引的字段数，中间字段不能存在范围查询的字段(<,like等)，这样的sql可以使用该多列索引。 mysql多列索引适合的场景


全字段匹配
匹配部分最左前缀
匹配第一列
匹配第一列范围查询(可用用like a%,但不能使用like %b)
精确匹配某一列和和范围匹配另外一列
order by操作中出现的字段同样适用于按值查找的规则，where+order by中出现的字段需可以建立满足如上五种规则的多列索引。


使用多列需要按照最左索引列查找；不能跳过中间列；如果某一列是范围查询，那么其右边所有列无法使用索引。