关于数据库索引创建与优化，首先需要明确的一点是，Mysql是一种插件式的数据存储服务，MySQL只提供了一个server，而其实际存储数据的工作是各个插件来实现的，这种形式的数据存储方式会导致一个问题就是MySQL如果要对数据进行过滤，那么就得把可能符合条件的数据从存储引擎中拉到MySQL server中，然后遍历这些数据以获取最终的数据，这会产生一个很大的问题就是，如果目标数据量非常大，那么从存储引擎中拉取的数据量将会非常大。数据库索引的设计其实最终目的就是为了在MySQL从存储引擎拉取数据之前先通过索引进行一部分的过滤，减少需要扫描的结果集大小，从而提升查询的效率。
对于索引，从存储数据的形式上来讲，主要有两种，一种是聚簇索引，另一种是二级索引，这两种索引都是通过B+树来组织的。对于聚簇索引，这里可以这么理解，MySQL在将数据存储到磁盘时，数据在磁盘是有一个顺序的，那么这里的顺序就是使用聚簇索引来组织的，MySQL一般使用主键id作为聚簇索引。也就是说磁盘上的数据存储的数据就是主键id的顺序，而索引B+树的叶节点上存储了当前数据在磁盘的地址值。对于二级索引，数据库中所有除了聚簇索引以外的索引都是二级索引，二级索引与聚簇索引最大的区别就是二级索引的叶节点存储的是聚簇索引的主键id值。在使用二级索引进行查询的时候，首先通过查询字段定位到二级索引的一部分叶节点，获取对应的主键id，然后通过id在聚簇索引中查询对应的数据。
常用的二级索引有单列索引，联合索引，字符串前缀索引和hash索引。
这里单列索引就是简单的为某个列建一个索引，单列索引主要需要注意的问题是字段的选择性，如果一个字段的选择性比较差，那么以该字段建立索引其实意义不大，因为根据索引过滤之后，MySQL还是需要在磁盘上拉取大量的数据。不过单列索引有一个好处就是可以进行索引合并扫描，比如查询条件是A or B，这个时候为A和B两列分别建立一个单列索引，MySQL就会用到这两个索引，然后将过滤后的结果进行合并，再到磁盘上拉取数据。
对于联合索引，这是使用最多的一个索引，其优点主要有四个：①如果查询条件是等值条件，那么只要这些等值条件与联合索引的前缀相匹配，那么就可以使用到索引进行过滤；②如果查询条件既有等值条件，也有非等值条件，那么如果联合索引有覆盖非等值条件的字段，MySQL就可以使用到索引覆盖扫描，也就是对于非等值条件，虽然不能使用B+树排序的特性，但是也可以直接从索引中进行数据扫描，而不用回到磁盘进行数据拉取；③如果查询条件中有group by，order by，或者distinct等条件，这个时候如果group by，order by或distinct后的字段是联合索引的最后一个字段，那么MySQL就可以不用再对数据进行这之类的处理了，因为在索引中数据就天然的已经聚合，并且排序了；④如果查询字段不多，那么建立一个联合索引，将查询条件和字段都放到索引里，这样所需要的数据就都在索引里了，从而可以省略回磁盘读取数据的过程了。在使用联合索引的时候，有一个问题需要注意就是，联合索引是有一个最左前缀匹配原则的，比如条件的字段是A和B，而联合索引是(A，B，C)，由于索引的组织顺序是按照先由A排序，当A相等时再按照B排序，B相等时再按照C排序，这样我们如果只使用A和B两个字段进行查询，也是能够使用到该联合索引的，这里如果使用A和C字段进行查询也能使用该索引，但只能首先根据A字段进行过滤，然后在过滤后的索引中全量扫描C字段，这样虽然效率没有使用前缀索引高，但是能够使用索引覆盖扫描，而不用回磁盘读取数据。
对于字符串前缀索引，就是对于某些字符串字段比较长，并且前缀选择性比较高的情况来创建的一个索引，MySQL是内在支持这种索引的，原理就是取该字段的某一个前缀来组成一个索引。这种索引存在两个问题：①前缀索引只能进行等值条件查询，无法像联合索引一样对group by，distinct和order by等进行支持；②前缀长度的选择需要通过计算字段的选择性来决定，也就是分别取前缀为2，3，4，5等长度，看其选择性与全字段建索引的选择性是否一致。
对于hash索引，MySQL内部是不支持的，不过我们可以通过冗余字段的方式来创建hash索引，比如对于一个很长的字符串字段，我们经常需要对该字段进行单点查询，那么这个时候直接为该字段建立前缀索引可能会导致索引片厚度太大，我们就可以新建一个列，用于存储这个字符串字段值得hash值，因为都是整型值，我们这个时候就可以为这个新建的字段创建索引了。那么查询的时候我们首先根据查询条件按照同一种hash方式将查询的数据映射为一个hash值，然后通过新建的hash字段定位到这个hash值所对应的数据记录，然后逐条比较其与我们需要的结果是否一致。
网友评论