mysql索引【第一篇】

 

索引的作用：提高查询效率，类似一本书的目录，可以让我们快速找到需要的内容。

索引的常见模型：

最多用到的三种，哈希表、有序数组、搜索树

哈希表：以key-value形式存储数据，将key通过内部函数计算得到一个值，放置于一个确定的位置，再将value放在数组的这个位置。由于多个key经过哈希函数计算可能会得出同一个值，这时候哈希表会拉一个链表出来。查询的时候会先将key值计算出来，通过计算后的确定位置，再去这个位置的链表中遍历所需要的内容。由于插入数据的值不是递增的，所以哈希表等值查询的效率特别高，而在范围查询中他会将整个表都扫描一遍得出结果，效率低下。应用场景：等值查询场景，比如memecache、nosql引擎

有序数组：由于是有序排列，通过二分法，范围查询和等值查询的性能很优秀。适用于静态查询表，如果插入一个中位数+1的数值，会导致中位数以后的数值都向后移动，所以写入的成本太高，只适用于静态存储引擎。

搜索树：二叉树、多叉树

二叉树特点：每个节点的左子节点小于父节点，父节点又小于右子节点。二叉树的搜索效率索然最高，但是大多数的数据库存储并不适用二叉树，因为索引不止存在内存中，还要写到磁盘上。比如：一颗100W节点的平衡二叉树，层高20，每次查询可能需要访问20个数据块，在机械硬盘时代，磁盘随机读取一个数据块可能需要10ms寻址地址，这样会导致查询效率很慢。

多叉树：每个节点有多个子节点，子节点从左到右递增。为了让查询尽量少的读磁盘，就要使用N叉树，N取决于数据块的大小。

    比如：以innodb的一个整形字段为例，N差不多是1200，当这棵树是4层高，数据量为1200的三次方，差不多上几十亿了，这时候通过索引查询只需要访问三次磁盘。再加上第二次又有很大概率存在内存中，访问磁盘的平均数就更少了。

 

InnoDB的索引模型：  B+树

优点：B+树可以很好的配合磁盘的读写特性，减少单次查询磁盘的访问次数。

这里有个问题： 为什么普通索引会回表呢？

原因：  select * from  A  where user_id=100    [其中user_id为普通索引，id为自增主键]

查询过程：  先去A表中根据user_id索引找到user_id=100，然后根据叶子节点中存储的主键值（比如：id=20），再去id主键索引中找出所需的信息（也就是*），去主键寻找整条数据过程称为回表。

主键索引：叶子节点存储的是整行数据      非主键索引：叶子节点的内容都为主键索引的值，也称为二级索引

基于非主键索引查询需要多扫描一颗主键索引树，在应用中尽量使用主键查询。

 

涉及到一个索引维护的问题：

B+树为了维护索引的有序性，比如A  B字段都是业务字段，A字段为主键索引，在插入一条中位数+1的值，会将A中位数以后的数据都往后挪一位，如果当前数据页满了的话，会新申请一个数据页出来，将部分数据挪过去，这个过程称为页分裂。当相邻的俩个页删除了数据，利用率很低后，会将数据页做一个合并，也就是页分裂的逆过程。不管是页分裂还是也合并，都会占用一定的数据库性能资源和磁盘存储空间。这时候引出一个建表规范：必须要有自增ID主键，以递增的方式追加数据，避免了页分裂和页合并。

为什么不用业务字段做自增主键：

1>身份证码字段为例，二级索引叶子节点会占用约20个字节，而int的暂用4个字节，从存储空间方面考量，不建议
2>自增主键是有序递增，数据写入成本太高