高性能MySQL笔记-索引设计规范

1.单张表索引不要太多，建议不超过5个
2.独立的列。指的是查询时索引列不能是表达式的一部分也不能是函数的参数
3.前缀索引。有时候索引列很长，这回让索引变的大且慢，一种做法是模拟哈希索引（InnoDB用的是B-Tree（技术上说是B+Tree）），一种做法是用前缀索引
3.1 模拟哈希索引跟真正的哈希索引是不一样的，前者还是用的B-Tree存，只不过里面存的不再是原来的索引值而是其hash值，查的时候也要用列的hash值查，一般不要用MD5()和SHA1()作为hash函数，因为这两个函数计算出来的hash值是非常长的字符串。
3.2 前缀索引就是只存索引的前缀，前缀不能太短也不能太长。太短的话选择性会太低(所谓选择性就是不重复的索引值和数据表记录总数的比值），太长的话就跟没用一样（毕竟就是为了减少空间和时间开销而用的前缀索引），创建前缀索引方法：ALTER TABLE table_name ADD KEY（index_clumn_name(pre_length)）
4. 多列索引（联合索引）
4.1 避免在WHERE用OR，这回导致索引失效引起全表扫描，可以用UNION ALL或UNION代替（最好是UNION ALL）
4.2 在MySQL高版本中，有索引合并策略：查询能够同时使用这两个单列索引进行扫描，并将结果合并，这种算法有三个变种：OR条件的联合（union），AND条件的相交（intersection），组合前两种情况的联合及相交。索引合并策略可通过EXPLIAN 的 Extra 列看到。索引合并策略有时是一种优化的的结果，但实际上更多时候说明了表上的索引建得很糟糕
4.3 将选择性最高的列放在联合索引前面
4.4 将字段长度小的列放在联合索引前面，因为字段长度小，则一页中存的数据量越多，IO性能越好
5.聚簇索引：聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针
5.1 因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引，不过覆盖索引可以模拟多个聚簇索引的情况
5.2 InnoDB选择主键作为聚簇索引，如果没有定义主键会选择第一个唯一非空索引作为聚簇索引，如果还没有就隐式定义一个主键（性能不好）作为聚簇索引
5.3 不要用UUID，MD5,HAS字符串列作为主键，因为无法保证顺序，也就是之后新建的列可能主键值小于之前创建的，这样更新聚簇索引速度慢
5.4 不要用更新频繁的列作为逐渐，也不要用联合主键
5.5 建议用自增ID作为主键
6. 对于频繁的查询优先使用覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就叫覆盖索引
6.1 覆盖索引避免了二次查询（二次查询就是，先找到聚簇索引（通常是主键），然后根据聚簇索引找到数据行）
7. 避免建立重复索引和冗余索引
7.1 重复索引，如：PRIMARY KEY(id),index(id),unique(ID)
7.2 冗余索引，如：index(a,b,c),indec(a,b),index(a)
8. 常见索引列建议
8.1 SELECT，UPDATE，DELETE语句中的WHERE从句中的列
8.2 包含在ORDER BY，GROUP BY，DISTINCT中的字段
8.3 多表join的关联列
9. 尽量避免使用外键，这里指的是不要用InnoDB本身的外键约束，而是要在业务段维护表关联
9.1 MySQL会自动在外键上建立索引（这条跟“避免使用外键”没有关系，只是顺带记一下外键的特点，并没有暗示不要在关联列上建索引，反而应该在关联列上建索引）
9.2 外键-->参照完整性-->保证数据库一致性
10. 索引的最左前缀匹配原则
10.1 假设有一个联合索引（A，B，C，D）就相当于也有（A，B，C），（A，B），（A）这几个索引
10.2 接10.1：那WHERE中怎么匹配上某个索引呢？可以这样看：从左到右读取WHERE中字段直到第一个范围查询（>,<,<=,>=,BETWEEN），后面的都不用索引了，然后通过最左匹配原则尽可能选个长的联合索引。