MySQL查询的深入探寻

一.mysql的数据存储

        为什么mysql存储的数据量如此庞大，它还能保持快速的查询呢，为什么我们普通的链表在添加到千万级别的数据流量后就会慢的要死，具体差距在哪里？

        其中最大的差距，就是我们使用的数据结构有很大的区别，我们首先认识几种常见的数据结构吧。

     1.二叉树

       

        如果将数据库的索引用二叉树排序，大概就就是上图的样子，虽然大部分情况查询速度是比平常的链表结构快了很多，但是，加入我们是一次排序，从从小到大一次排序，则形成的二叉树可能是下图所示的样子，那如果想要查询最后一个数据的话，它查询的次数与链表没有任何的差别。

 

     2.红黑树

        如果是红黑树，他的样子则为下图所示。它查询的次数相比于二叉树确实是减少了几次，但是他的树高还是很高。

        假如是是百万级别的数据量，那树的高度也是无法估量的，加入真的查询起来，他的查速度也是大的惊人。

 

     3.hash表

        相对于前边的两种树，hash表查询的结构就好了很多，如下图所示。

 他的特点如下：

 •对索引的key进行一次hash计算就可以定位出数据存储的位置
 • 很多时候Hash索引要比B+ 树索引更高效
 • 仅能满足“=”，“IN”，不支持范围查询
  • hash冲突问题

        由于存储，查询都是相对于后边的树结构还是又部分缺陷，所以还是没能成为我们数据库的查询最终结构。

---

 

     4.B-Tree

        B树的结构就已经非常进阶我们数据库使用的存储数据结构了，如下图所示。

他的特点如下：

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列

         他的已经非常接近了，这种查询时，会将索引和元数据一起加载到内存中，占用的内存实在是太大，而且如果索引想要进行范围查询，需要重新从树顶到枝叶一层蹭查询，所以最终也的结果也不是它。

        5.B+Tree（B-Tree变种）

        这个数据结构时我们InnoDB表最终定使用的数据机构。他的样子如下图所示。

他的特点如下：

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能

         相对于其他的数据结构，B+树可以说是聚集了，树的层数低，查询数据多且可以通过最后的数据间的地址，快速查询想要的范围结果。最终顶板。

二.数据库搜索引擎的存储格式

1.非聚集索引

        MyISAM索引文件和数据文件是分离的(非聚集)，如下图.

        他的主要特点是将数据和索引分开存放，每当查询数据时，会先通过索引树查询树，然后跟根据索引树的地址进行回表操作，再次对存储数据的表进行二次查询，得出结果后，将其返回给我们服务端。

 

2.聚集索引

InnoDB索引实现(聚集)：如下图。

他的特点如下：

• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引-叶节点包含了完整的数据记录
• 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

        因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形
• 为什么非主键索引结构叶子节点存储的是主键值？

        一致性和节省存储空间

         上图为主键索引，标准的聚集索引的样貌。下图则是二级索引，他的结构将相当于是非聚集索引，查询方式也是非聚集索引的样子，需要根据id回标查询。

 

 三.联合索引

        联合索引听着很有可能会跟聚集索引有类似的地方，但是这两个完全就不是一个东西。联合索引，其实就是将一个表中的多个字段合并做做成一个索引，然后根据他们他么的向后顺序进行一个二级索引的树形排列，其样子如下图所示。

         他有一个很重要的原则——最左前缀原则:（MySQL 5.6之后才引入的）

        简单的来说有一点想我们就比如是有两个百位数作比较，光对比百位，可得更大的数。如果抛弃百位直接比较十位，则无法比较两数指定的大小。

        153和267比较，我们一下就能知道267数值更大，但是如果让我们只通通过这两个数的十位和个位进行比较，我们则无法对比出他的大小。

        就相当于我们创建联合索引时，将name当成百位，age当成十位，postion当成个位，我们可以通过name比较出后边的顺序大小，但是无法通过age比较两个数据在索引树上的前后位置。所以当我们使用like%查询时，他一定会走索引，当查到第一个符合条件的数据之后，后边的所有数据也就默认成为符合条件的数据，这就时索引的最左前缀原则了。

 四.索引设计原则

1、代码先行，索引后上

不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？

这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。

2、联合索引尽量覆盖条件

比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。

3、不要在小基数字段上建立索引

索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。

如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。

一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。

4、长字符串我们可以采用前缀索引

尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。

当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。

对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。

此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。

但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。

5、where与order by冲突时优先where

在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?

一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。

因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。

6、基于慢sql查询做优化

可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。

关于慢sql查询不清楚的可以参考这篇文章：mysql慢查询_八点半的Bruce丶D的博客-CSDN博客_mysql查询