MySQL进阶，秒变大神（三）

最近闲来无事，整理了一波Mysql的资料，欢迎大家来围观！！！

16、什么情况下应不建或少建索引？

对于那些在查询中很少使用或者参考的列不应该创建索引。（既然这些列很少使用到，因此有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。）白嫖资料
对于那些只有很少数据值的列也不应该增加索引。（由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。）
对于那些定义为text, image和bit数据类型的列不应该增加索引。（这是因为，这些列的数据量要么相当大，要么取值很少。）
当修改性能远远大于检索性能时，不应该创建索引。（这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。）

17、什么是mysql联合索引？

联合索引是指对表上的多个列做索引。在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

最左前缀匹配原则：

最左优先，在检索数据时从联合索引的最左边开始匹配。

对列col1、列col2和列col3建一个联合索引：KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 test_col1_col2_col3 相当于建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

（1）

SELECT * FROM test WHERE col1="1" AND clo2="2" AND clo4=|"4"

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

（2）索引的字段可以是任意顺序的，如：

白嫖资料

　SELECT * FROM test WHERE col1=“1” AND clo2=“2”
　　SELECT * FROM test WHERE col2=“2” AND clo1=“1”

这两个查询语句都会用到索引(col1,col2)，mysql创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段col1的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段col2进行排序。其实就相当于实现了类似 order by col1 col2这样一种排序规则。

有人会疑惑第二个查询语句不符合最左前缀匹配：首先可以肯定是两个查询语句都保函索引(col1,col2)中的col1、col2两个字段，只是顺序不一样，查询条件一样，最后所查询的结果肯定是一样的。既然结果是一样的，到底以何种顺序的查询方式最好呢？此时我们可以借助mysql查询优化器explain，explain会纠正sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

（3）如果只查询col2：SELECT * FROM test WHERE col2=2;

第一个col字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个字段col2进行条件判断是用不到索引的。当然是col2字段的索引数据也是有序的情况下才能使用咯，什么时候才是有序的呢？在col1字段是等值匹配的情况下，cid才是有序的。这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。（而且第一个索引必须是等值匹配）。

为什么要使用联合索引？
减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

白嫖资料
　　从本质上来说，联合索引还是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。

对于查询 SELECT * FROM TABLE WHERE a=xxx and b=xxx，显然可以使用(a,b)这个联合索引。对于单个的a列查询 SELECT * FROM TABLE WHERE a=xxx 也是可以使用(a,b)索引。但对于b列的查询 SELECT * FROM TABLE WHERE b=xxx 不可以使用这颗B+树索引。因为叶节点上的b值为1,2,1,4,1,2，显然不是排序的，因此对于b列的查询使用不到(a,b)的索引。

联合索引的第二个好处是，可以对第二个键值进行排序。例如，在很多情况下我们都需要查询某个用户的购物情况，并按照时间排序，去除最近3次的购买记录，这是使用联合索引可以避免多一次的排序操作，因为索引本身在叶节点已经排序了。

【注】：对于相同的第一个键值的数据，第二个键值是排好序的。

对于单个列a的查询往往使用单个键的索引，因为其叶节点包含单个键值，能存放的记录更多。

18、说一说 B+树索引、哈希索引？

Hash索引和B+树索引的特点：

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位;

B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问。

Hash索引与B+树索引区别？

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
从示意图中也能看到，如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
哈希索引也不支持多列联合索引的最左匹配规则；
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

19、B树和B+树的区别？

B+树是一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶结点指针进行连接。
白嫖资料
（平衡二叉树AVL：首先符合二叉查找树的定义（左结点的值比根节点小，右结点的值比根结点大），其次必须满足任何节点的左右两个子树的高度最大差为1。）

B树 ：每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为nul，叶子结点不包含任何关键字信息。
B+树：所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非终端结点可以看成是索引部分。
20、为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？
（1）B+的磁盘读写代价更低

B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

（2）B+tree的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

21、聚集索引和非聚集索引区别?

数据库中的B+索引可以分为聚集索引和辅助聚集索引。不管是聚集索引还是非聚集的索引，其内部都是B+树的，即高度平衡的，叶节点存放着所有的数据，聚集索引与非聚集索引不同的是，叶节点存放的是否是一整行的信息。

聚集索引(clustered index)：
　　聚集索引就是按照每张表的主键构造一颗B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的叶节点成为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。

聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。

非聚集索引(nonclustered index)（也叫辅助索引）：
　　对于辅助索引(非聚集索引)，叶级别不包含行的全部数据。聚集索引键来告诉InnoDB存储引擎，哪里可以找到与索引相对应的行数据。辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

非聚集索引指定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。

根本区别：

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。
白嫖资料

22、说一说drop、delete与truncate的区别？

drop直接删掉表。
truncate删除表中数据，再插入时自增长id又从1开始。
delete只删除表中数据，可以加where字句，也不包括索引。
（1）truncate和delete只删除数据，而drop则删除整个表（结构和数据）。

（2）delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作，如果有相应的trigger（触发器），执行的时候将被触发。truncate table则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（3）执行速度：drop> truncate >delete

（4）delete语句是dml，这个操作会放到rollback segement中，事务提交之后才生效。如果有相应的trigger（触发器），执行的时候将被触发；

truncate、drop是ddl，操作立即生效，原数据不放到rollback segment中，不能回滚，操作不触发trigger。

（5）当表被truncate后，这个表和索引所占用的空间会恢复到初始大小， delete操作不会减少表或索引所占用的空间。

（5）应用范围：truncate只能对table；delete可以是table和view

如果直接删除一个表drop，对数据量很大的表，这个过程会占用比较长的时间，如果先truncat后drop table：1、可以降低操作失败的风险；2、可以降低数据字典锁占用的时间，降低系统开销。

23、drop、delete与truncate分别在什么场景之下使用？

不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate

最后，祝大家早日学有所成，拿到满意offer，快速升职加薪，走上人生巅峰。 可以的话请给我一个三连支持一下我哟，我们下期再见

白嫖资料