Mysql数据库索引

       数据库索引一直是数据库学习的一个重点。以下以这几个模块对最近的学习进行一个总结。

     （ps:学习的资料来自掘金小册《MySQL 是怎样运行的：从根儿上理解 MySQL》，下文仅仅是个人学习的简单总结，我非常推荐这本，同时作者的微信群也有非常好的解答服务，在此打一波广告）

---

•     InnoDB引擎的数据存储以及数据查找

     在Mysql的架构中，存储引擎是以类似插件的形式存在的，可以根据自己使用的需求进行更换。比较常见的就是MyIsam和InnoDB了，由于InnoDB支持事务，所以更为常用。

     InnoDB的基本存储单位是以一页（16kb）进行存储的，页中的数据（记录）是以链表的形式进行存储，而页与页之间是双向链表。那平时在没有索引的情况下即全表扫描，这个过程就是先从第一页开始，一条一条记录查找，找到符合条件的就放在结果集中，然后再查找下一页，这种形式肯定是非常慢的。所以索引的产生就是为了解决这个问题，希望能够更快地查找。

   此图为一页中数据的存储方式，是以链表的形式进行存储的（图中记录的蓝色部分存储的是记录头信息，保存关于这条记录的相关属性信息，橙色部分才是记录的真实数据，其中橙色的第一列表示的是记录的主键信息），该链表是以主键的大小进行排序的。

在一页中，mysql会对数据进行分组，每组也就4-8条，图中的槽就是页目录，每个槽对应一个组（类似与书中的目录，对应书中某个单元的信息），槽可存放对应组的最大的id号。由于页中的id是按照顺序从小到大进行排列的，所以在某一页中的查找可以使用二分法进行。具体的步骤分为两步：

1.利用二分法查到该条记录所在的槽

2.在槽中进行遍历，找到符合条件的记录（注：因为每组的条数很少，所以就算遍历，也不会影响效率）

（是通过每条记录的额外信息中的next_record(相当于链表中的指针)来找到下一条记录的）

刚刚介绍了单页中的数据查找，页与页之间是通过页头存储的上下页的编号，使之成为双向链表，具体的示意图如下：

•   B+树索引的构造

      刚刚在一页中的查找是对业内的每条记录建立目录项（槽）进行二分查找，来提高二分查找的效率。而索引就是对页建立目录。此时需要保证的前提就是记录的排序，不仅要在一页内有序，在所有页中也应该要有序。（数据页号可能在存储空间上是不连续的）如上图所示：我们已经建立了满足条件的模型，接下来就是建立目录项，如下图

目录项的存储与数据页其实差不多，所以也可存储成数据页，不同数据页之间有标志位进行分类。还有一个重大原有就是上图所示的方案有问题：1.数据库中的数据量大的时候，需要非常大的连续空间来存储这些目录项    2.对数据增删的时候，目录项跟着增删，牵一发而动全身。所以存储成类似数据页的格式。如下：

以上就是根据主键建立的索引（聚簇索引，就算在Mysql表中没有建立自己的主键，Mysql中也会默认以一个row_id来建立主键，row_id是记录格式中的某一行，）

1.索引的结构是树,树的叶子节点存储的真实的数据，非叶子节点存的是索引（目录页）.聚簇索引也是Mysql中的InnoDB存储引擎存储数据的方式，数据即索引，索引即数据。

如果是在我们指定的列上建立索引呢，这种索引叫做二级索引，其叶子节点不是存储具体的数据（如果是具体的数据，多建立几个索引，空间要浪费多少），而是存储索引值+id

二级索引的查找需要回表，何为“回表”，就是根据二级索引查到值之后，该值实际上是记录的主键值，而不是真是的记录，需要根据id，回到聚簇索引再查找一次，找到真是的记录。

•   索引的使用技巧

以前看过很多博客，讲如何用索引才不会失效，我顶多就是把规则记住，现在知道了索引的内部结构之后，再来一条条分析这些规则与索引结构上的关系。

1.最左匹配原则：即在联合索引的情况下，要有左边开始使用连续的索引列才能用的到索引

先创建一张表：

create table student(

id int not null auto_increment,

name varchar(10)  not null,

age int(3) not null,

country varchar(10) not null,

primary key(id),

key index_name_age_country(name, age, country)

);

此表创建了联合索引，索引是有顺序的，索引的结构是先按照name进行排序，name相同的情况下，在按照age进行排序，age相同再按照country进行排序。假如前面的没有排好，你去直接用后面的那个字段进行查找，那还能有序吗？显然不能

select * from student s where s.name = 'EEE' and age = 11;

这条语句是可以，因为他按照了索引顺序。 假如直接用 where age = 11 作为条件，还能用到索引吗?

2.匹配最前列：类似与最左匹配原则，因为字符串也是有顺序排列的，按照每个字符。

所以如果like '%a',像这样的 左模糊是用不到索引的。

3.group by 时候用联合索引时的字段顺序，会用到索引，其实以上道理都一样

4.尽量使用覆盖索引，避免回表操作

• 如何挑选索引

   1.考虑列的基数

       也就是考虑某列的不重复的数值所占比，占比越大，值越分散，排序的效果就越好。极端情况，某一列的所有值都一样，那建立个索引，该列排序不排序有什么影响吗？没有。类似的，面试题目中，性别列不应该建立索引的原因就是基数太小。

假如100万的数据，性别建索引，男女数目五五开，或者六四开，查某个性别，获得结果四五十万，然后根据id，去聚簇索引再查（也就是回表）。这样的操作还不如直接全表扫描，建立索引意义不大，而且MySQL也会根据回表的数量来决策是否全表扫。

  2.索引列的类型尽量小

 数据类型越小，在查询时进行的比较操作越快

数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

3.给字符串字段加索引时的技巧

   3.1第一种方式是使用倒序存储，比如存储身份证，前面的数字可能区分度不大，末尾的数字区分度较大，截取末尾的几位，这样也满足字段小的原则，查询速度也会加快

  3.2第二种方式 创建字符串前缀索引

     好处是：节约了空间，也减少字符串的比较时间

    坏处是：如果使用order by ，那么由于这个字符串索引不完整，无法利用到索引进行排序。