写在前面：要想了解索引的底层以及优化索引的底层还得学习不同搜索引擎下对于数据的处理，后续我会出一些相关的文章介绍，比如什么是B+tree，什么是聚簇索引什么是二级索引、联合索引等，以及不同搜索引擎下（ MyISAM 与 InnoDB）数据是如何相关联的，他们之间是通过单项链表还是双向链表来简历关系的。当然了下面的学习对这些东西虽然有关联但是现在目前不学习这些的话也没有太大的影响。在后续的文章中我会介绍的。
1.字段的数值有唯一性的限制
索引本身可以起到约束的作用，比如唯一索引，主键索引都是可以起到唯一性的约束，因此我们的数据表中如果某个字段是唯一性的就可以直接创建唯一性索引或者主键索引。这样可以更快速地通过索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名的现象，从而降低查询速度。
业务上具有唯一性的字段即使是组合字段也必须建成唯一索引。
说明：不要以为唯一性索引影响了insert速度，这个速度损耗可以忽略不计，但提高查找速度是明显的。
2.频繁作为where查询条件的字段
某个字段在select语句中的where条件中经常被使用到，那么就需要给这个字段创建索引。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
3.经常group by和order by的列
索引就是让数据按照某种顺序进行存储或检索，因此当我们使用group by对数据进行分组查询或使用order by对数据进行排序的时候就需要对分组或排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。
如果同时有group by和order by的情况下，我们可以创建一个联合索引。将group by的字段放在前面也就是左侧，order by的字段放在后面
4.update delete 的where条件列
当我们对某条数据进行update或delete操作的时候，是否也需要对where的天剑列创建索引呢？
答案是肯定的。如果where字段创建了索引就能大幅提升效率，原理是因为我们需要先根据where条件检索出这条记录，然后再对其进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更加明显，这是因为非索引字段更新不需要对索引进行维护。
5.distinct 字段需要创建索引
有时候我们需要对某个字段进行去重，使用distinct，那么对这个字段创建索引也会提升查询效率。
6.多表join连接操作时创建索引注意事项
首先，连接表的数量尽量不要超过三张表，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
其次，对where条件创建索引，因为where才是对数据条件的过滤。如果在数据量非常大的情况下，没有where条件过滤是非常可怕的。
最后，对用于连接的字段创建索引，并且该字段在多张表中类型必须保持一致。
7.使用列的类型小的创建索引
我们这里所说的类型大小指的就是该类型表示的数据范围的大小。
我们在定义表结构的时候要显示的指定列表类型，以整形为例有tinyint mediumint int bigint 等，他们占用的存储空间一次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许情况下，尽量让索引列使用较小的类型。比如我们能使用int就不要使用bigint，能使用mediumint就不要使用int。这是因为：
①数据类型越小，在查询时候进行的操作比较快。
②数据类型越小，索引占用的存储空间就也少（这个后续会在文章讲述，比如聚簇索引的叶子节点以及非叶子节点是如何存放数据的）。在一个数据页内就可以放下更多的记录，从而减少磁盘的I/O带来的性能的损耗，也就意味着可以把更多的数据页缓存到内存中，从而加快读写效率。
这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他二级索引节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O.
8.使用字符串前缀创建索引
假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要这个字符串建立索引时那就意味着对应的B+tree中有两个问题：
①B+tree索引中的记录需要把该列的完整字符串存起来，更费时。而且字符串越长在索引中占用的存储空间也就越大。
②如果B+tree索引中索引列存储的字符串很长那么在做字符串比较时会占用很多的时间。
我们可以通过截取字段前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确定位位置但是能定位到相应的前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间又减少字符串的比较时间，还答题能解决排序的问题。
例如，text和blog类型的字段进行全文检索会很浪费时间，如果只检索字段前面的如果字符这样可以提高检索速度。
引申另一个问题：索引列前缀对排序的影响
如果使用了索引列前缀比方只把某个列的前12个字符放到二级索引中那么查询可能有点儿尴尬了。
因为二级索引中不包含完整的类信息，所以无法对前12个字符相同后边的字符不同的记录进行排序。也就是使用索引列前缀的方式无法支持使用索引排序只能使用文件排序。
拓展：Alibaba《Java开发手册》
【强制】在varchar字段上建立索引时必须指定索引长度没必要对全字段建立索引，根据实际文本区分度决定索引长度
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引区分度会高达90%以上
9.区分度高（散列型高）的列适合作为索引
列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2,3,8,2,5,8,2,5,8虽然有9条记录但该列的基数却是3.也就是说在记录行数一定的情况下列的基数越大该列中的值越分散；列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列建立索引引起的效果可能不好。
拓展：联合索引把区分度高（散列型）的列放在前面
10.使用最频繁的列放到联合索引的左侧
这样也可以较少的建立一些索引。同时由于最左前缀原则，可以增减联合索引的使用率
11.在多个字段都要创建索引的情况下联合索引优于单值索引
限制索引的数目
在实际工作中我们也需要注意平衡。索引的数目不是越多越好。我们需要限制每张表的索引数量，建议单张表索引数量不要超过6个。原因：
①每个索引都需要占用磁盘空间，索引越多需要的磁盘空间就越大。
②索引会影响insert delete update 等语句的性能，因为表中的数据更改的同时索引也会进行调整和更新会造成负担。
③优化器在选择如果优化查询时会根据统一信息，对每一个可以用到的索引来进行评估，以生成一个最好的执行计划，如果同事有很多索引都可以使用查询，会增加mysql优化器生成执行计划时间时间，降低查询性能。
260
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
