数据库知识详解

数据库知识详解

Q：我看你简历上写到了熟悉MySQL数据库以及索引的相关知识，我们就从索引开始，索引有哪些数据结构？

A：Hash、B+

Q：为什么哈希表、完全平衡二叉树、B树、B+树都可以优化查询，为何Mysql独独喜欢B+树？

A：
哈希表的特点就是可以快速的精确查询，但是不支持范围查询。

例如下sql

select * from student where name = '张三';

可以直接对‘张三’按哈希算法算出来一个数组下标，然后可以直接从数据中取出数据并拿到所对应那一行数据的地址，进而查询那一行数据， 那么如果现在执行下面的sql语句：

select * from student where name > '张三';

hash表示则无能为力，如果做成了索引，那速度也是很慢的，要全表扫描。

二叉树的新增和结构如图：

二叉树的性质就是左节点比根节点小，右节点比根节点大。
二叉树存在的缺点：在插入特殊数值的情况下，可能会形成一条链表，例如顺序插入5,4,3,2,1。此时就形成了“歪脖子”树

二叉树是有序的，所以是支持范围查询的。

但是他的时间复杂度是O(log(N))，为了维持这个时间复杂度，更新的时间复杂度也得是O(log(N))，那就得保持这棵树是完全平衡二叉树了。

平衡二叉树有以下性质：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。为保证二叉树的高度平衡，在对二叉树进行增删的时候都会进行高度判断，通过左旋和右旋保证二叉树的平衡。
平衡二叉树的缺点：如果数据多了，树高会很高，查询的成本就会随着树高的增加而增加。

同理来看看B树的结构：

可以发现同样的元素，B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素。

B树其实就已经是一个不错的数据结构，用来做索引效果还是不错的。

再看一下B+的结构：

我们可以发现同样的元素，B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。

Q：那么B+树到底有什么优势呢？

其实很简单，我们看一下上面的数据结构，最开始的Hash不支持范围查询，二叉树树高很高，只有B树跟B+有的一比。

B树一个节点可以存储多个元素，相对于完全平衡二叉树整体的树高降低了，磁盘IO效率提高了。

而B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。

提高了的原因也无非是会有指针指向下一个节点的叶子节点。

小结：到这里可以总结出来，Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。

Q：那么，一个B+树的节点中到底存多少个元素最合适你有了解过么？

A：
B+树中一个节点为一页或页的倍数最为合适。

因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费。

如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费。

所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

Q：你提到了页的概念，能跟我简单说一下么？

A：
首先Mysql的基本存储结构是页(记录都存在页里边)：

• 各个数据页可以组成一个双向链表
• 而每个数据页中的记录又可以组成一个单向链表（每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录）
• 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写 select * from user where username='丙丙’这样没有进行任何优化的sql语句，默认会这样做：

• 定位到记录所在的页（需要遍历双向链表，找到所在的页）
• 从所在的页丙中查找相应的记录（由于不是根据主键查询，只能遍历所在页的单链表了）

很明显，在数据量很大的情况下这样查找会很慢！看起来跟回表有点点像。

Q：哦？回表你聊一下。

回表大概就是我们有个主键为ID的索引，和一个普通name字段的索引，我们在普通字段上搜索：

select * from table where name = '丙丙'

执行的流程是先查询到name索引上的“丙丙”，然后找到他的id是2，最后去主键索引，找到id为2对应的值。

回到主键索引树搜索的过程，就是回表。不过也有方法避免回表，那就是覆盖索引。

Q：哦？那你再跟我聊一下覆盖索引呗？

这个其实比较好理解，刚才我们是 select * ，查询所有的，我们如果只查询ID那，其实在Name字段的索引上就已经有了，那就不需要回表了。

覆盖索引可以减少树的搜索次数，提升性能，他也是我们在实际开发过程中经常用来优化查询效率的手段。

很多联合索引的建立，就是为了支持覆盖索引，特定的业务能极大的提升效率。

Q：索引的最左匹配原则知道么？

A：
最左匹配原则：

• 索引可以简单如一个列 (a)，也可以复杂如多个列 (a,b,c,d)，即联合索引。
• 如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。
• 因此，列的排列顺序决定了可命中索引的列数。

例子：如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)

Q：数据库优化有了解吗？和我讲讲呗？

A：
1、我认为数据库查询优化，最主要的是优化sql，在实现同一目标的前提下，sqlA查询花了10s，sqlB花了1s，这就是最大的优化，通常需要不断的sql积累以及对业务的了解才能写出更优秀的sql；
2、在sql一定的前提下（无法再继续优化），那么可以通过加索引的方式提升查询效率，当然索引也不是随便加的，索引相当于一本书的目录，会占用磁盘，且索引在提升查询的同时会降低增、删、改的效率。建立索引时通常会遵循这几个规则：

• 最左前缀匹配原则：MySQL会一直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停止匹配。
• 尽量选择区分度高的列作为索引：区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。
• 索引列不能参与计算，尽量保持列“干净”：比如， FROM_UNIXTIME(create_time)=‘2016-06-06’ 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ：create_time=UNIX_TIMESTAMP(‘2016-06-06’)。
• 尽可能的扩展索引，不要新建立索引：比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。

PS：这里引入索引设计中非常著名的一个准则：三星索引。
如果一个查询满足三星索引中三颗星的所有索引条件，理论上可以认为我们设计的索引是最好的索引。什么是三星索引？

1、第一颗星：WHERE 后面参与查询的列可以组成了单列索引或联合索引；
2、第二颗星：避免排序，即如果 SQL 语句中出现 order by colulmn，那么取出的结果集就已经是按照 column 排序好的，不需要再生成临时表；
3、第三颗星：SELECT 对应的列应该尽量是索引列，即尽量避免回表查询。

Q：建立索引的时候，某列唯一的情况下，唯一索引和普通索引，怎么选则？

A：使用普通索引。

Mysql维护了一个修改缓冲区，叫Change Buffer。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。

除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

那么，什么条件下可以使用change buffer呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。

要判断表中是否存在这个数据，而这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

change buffer用的是buffer pool里的内存，因此不能无限增大，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置，这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一，change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer的使用场景？

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好，这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价，所以，对于这种业务模式来说，change buffer反而起到了副作用。

Q：有个很长的字段，想用它做索引，我们怎么去优化他呢？

A：MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

我们都知道只要区分度过高，都可以，那我们可以采用倒序，或者删减字符串这样的情况去建立我们自己的区分度，不过大家需要注意的是，调用函数也是一次开销哟。

就比如本来是www.aobing@qq,com 其实前面的www.基本上是没任何区分度的，所有人的邮箱都是这么开头的，你一搜一大堆出来，放在索引还浪费内存，你可以substring()函数截取掉前面的，然后建立索引。

我们所有人的身份证都是区域开头的，同区域的人很多，那怎么做良好的区分呢？REVERSE（）函数翻转一下，区分度可能就高了。

Q: 你说你会数据库优化？简单说一样。（这个比上面更专业）

A：
1、首先，我认为数据库查询优化，最主要的是优化sql的业务，在实现同一目标的前提下，sqlA查询花了10s，sqlB花了1s，这就是最大的优化，通常需要不断的sql积累以及对业务的了解才能写出更优秀的sql；
2、在sql一定的前提下（无法再继续优化），那么可以通过加索引的方式提升查询效率，当然索引也不是随便加的，索引相当于一本书的目录，会占用磁盘，且索引在提升查询的同时会降低增、删、改的效率。
3、我建立索引时通常会考虑这几个点：
3.1、首先就是覆盖索引，因为覆盖索引可以减少回表的次数；然后再mysql5.6之后又对覆盖索引做了进一步的优化，可以支持一个索引下推的功能，将索引所覆盖的字段进一步的进行筛选，尽量减少回表次数；同时覆盖索引和最左匹配原则配合，减少一些索引的维护。
3.2、还有一点就是，如果我们是一个写多读少的服务，并且我们的业务可以保证唯一，我们可以用普通索引来代替唯一索引，因为普通索引是可以用到Change Buffer的，在内存种没有这个数据页的的时候，Change Buffer可以将我们的一些写操作缓存下来，在我们读操作，将数据页读进内存的时候进行一个merge的操作，这样的话可以提高我们写入的速度，还有一个内存的命中率。这个的话我认为是在创建索引的时候可以考虑的一些点。
3.3、还有一个就是，如果我这个索引走不上，我们应该考虑那些方面。第一，是不是我们的sql写的有问题，比如对索引字段进行了一些函数操作，或者就是在连接查询的时候，两个表的编码不一样，也有可能是由于隐式类型的转换。考虑了我们自己我sql问题后，我们可以考虑是否是索引统计的信息有问题，如果索引信息有问题，我们可以使用analyze table重新统计索引信息。