MySQL索引创建原则总结
关联博文：
MySQL索引基础入门详解
MySQL索引中的数据结构详解
MySQL中的(非)聚簇索引与索引覆盖详解
MySQL中最左匹配相关实践总结
MySQL调优之大表处理探索那些事
MySQL调优之索引在什么情况下会失效？
MySQL索引创建原则总结
认真学习MySQL中的索引条件下推（ICP）
① 适合创建索引的场景
- 字段的数值有唯一性的限制，索引本身可以起到约束作用比如唯一索引、主键索引；
- 频繁作为 WHERE 查询条件的字段，那么可以使用索引；
- 定义有外键的数据列一定要建立索引(虽然实际业务通常不建议采用外键)；
- UPDATE、DELETE 的 WHERE 条件列可以根据情况创建索引，如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显。因为非索引字段更新不需要对索引进行维护。
- 查询中排序的字段、统计或者分组的字段根据情况创建索引，比如经常 GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储或检索，因此当我们使用group by对数据进行分组查询，或者使用order by 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。
- DISTINCT 字段需要创建索引,会提升查询效率。因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。
- 使用列的类型小的创建索引(索引文件小，一个数据页可以存放多个键值，减少磁盘IO);
- 使用字符串前缀创建索引(索引文件小，一个数据页可以存放多个键值，减少磁盘IO);
- 区分度高(散列性高)的列适合作为索引；
- 使用最频繁的列放到联合索引的左侧，这样也可以较少的建立一些索引。同时，由于“最左前缀原则”，可以增加联合索引的使用率。
- 最左前缀原则，组合索引非常重要的原则。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。比如
a=1 and b=2 and c>3 and d=4，如果建立(a,b,c,d)顺序的索引，d是用不到索引的；如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整（优化器会优化调整）。 - 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
- 在多个字段都要创建索引的情况下，联合索引优于单值索引。
索引字段越小越好： 数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。
关于数据页、磁盘IO等可以参考博文认真学习InnoDB的数据存储结构和MySQL索引中的数据结构详解
使用列的类型小的创建索引。
我们这里所说的类型大小指的就是该类型表示的数据范围的大小。
我们再定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型。比如我们能使用int就不要使用bigint，能使用mediumint就不要使用int，这是因为：
- 数据类型越小，在查询时进行的比较操作越快；
- 数据类型越小，索引占用的存储空间就越少。在一个数据页内就可以存放更多的记录，从而减少磁盘IO带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
这个建议对表的主键来说更加适用。因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的IO。
使用字符串前缀创建索引。
假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题：
- B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
- 如果B+树索引中索引列存储的字符串很长，那么做字符串比较时会占用更多时间。
我们可以通过截取字符串的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置。然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。
例如，text和blob类型的字段，进行全文检索会很浪费时间。如果只检索字段前面的若干字符，这样可以提高检索速度。
这里的问题是如果定义前缀的长度。如果截取的多了达不到节省索引存储空间的目的，截取的少了重复内容太多，字段的散列度（选择性）会降低。怎么计算不同的长度的选择性呢？
先看一下字段在全部数据中的选择度：
#创建表
create table shop(address varchar(120) not null);
# 创建前缀索引
alter table shop add index (address(12));
# 全表的选择性
select count(distinct address) / count(*) from shop
通过不同长度计算，与全表的选择性对比，公式如下：
count(distinct left(列名,索引长度)) / count(*)
例如：
select count(distinct left(address,10)) / count(*) as sub10,--截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub15,--截取前15个字符的选择度
count(distinct left(address,20)) / count(*) as sub20,--截取前20个字符的选择度
count(distinct left(address,25)) / count(*) as sub25 --截取前25个字符的选择度
from shop
这个结果越接近于1，表示效果越好。不过这里需要考虑另外一个问题：索引列前缀对排序的影响。
如果使用了索引列前缀，比如只把address列的前12个字符放到了二级索引中，对于下面这个查询语句可能就有点尴尬：
select * from shop order by address limit 12;
因为二级索引中不包含完整的address列信息，所以无法对前12个字符相同，后边的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只能使用文件排序。
区分度高（散列性高）的列适合作为索引。
列的基数指的是某一列中不重复数据的个数。比如 说某个列包含值2,5,8，2,5，8,2，5,8，虽然有9条记录，但该列的基数却是3。也就是说在记录行数一定的情况下，列的基数越大，该列中的值越分散。列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最后为列的基数大的列建立索引，为基数太小列建立索引效果可能不好。
可以使用公式 select count(distinct a)/count(*) from t1 计算区分度，越接近1越好。一般超过33%就算是比较高效的索引了。
拓展：联合索引把区分度高（散列性高）的列放在前面。
② 不适合创建索引的场景
- 在where中（包括group by 、order by）使用不到的字段，通常不要设置索引。索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。
- 更新频繁的字段或者表不适合创建过多索引
第一层含义：频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引。如果索引太多，在更新索引的时候也会造成负担从而影响效率。
第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能的少。此时，虽然提高了查询速度，同时却会降低更新表的速度。 - 数据量小的表最好不要使用索引。因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
- 不建议用无序的值作为索引。例如身份证、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）、MD5、HASH、无序长字符串等等。
如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入、数据移动，导致产生很多的内存碎片，进而造成插入性能的下降。频繁的
移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。分页操作也会导致磁盘IO次数增加，降低性能。 - 不能有效区分数据的列（有大量重复数据的列）不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)。
- 删除不再使用或者很少使用的索引。
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
- 对于定义为text、image和blob的数据类型的列不要建立索引。
- 不要定义冗余或重复的索引。
不要定义冗余或重复的索引。
冗余索引
有时候有意或者无意的就对同一个列创建了多个索引，比如index(a,b,c)相当于index(a)、index(a,b)、index(a,b,c)。
如下建表语句：
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,
primary key (id),
key idx_name_b_p_n(name(10),birthday,phone_number),
key idx_name(name(10))
);
我们知道，通过idx_name_b_p_n索引就可以对 name 列进行快速搜索，再创建一个专门针对 name 列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。
重复索引
另一种情况，我们可能会对某个列重复建立索引，比如下面：
create table repeat_index_demo(
col1 int primary key,
col2 int,
unique uk_idx_c1(col1),
index idx_c1(col1)
)
我们看到 col1既是主键，又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。
③ 限制索引的数目
在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因：
- 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
- 索引会影响 insert delete update等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
- 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划。如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。
最后给出网上流传的索引优化口诀：
全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
var引号不可丢，SQL高级也不难！