本文详细讲解了MySQL中的聚簇索引、非聚簇索引、MVCC多版本并发控制、事务隔离级别、InnoDB引擎的B+树优势、MyISAM与InnoDB的区别、MySQL引擎类型、日志系统及其用途、高效创建索引、EXPLAIN工具、SQL执行顺序、IN和EXISTS的区别以及事务实现。还讨论了主从同步延迟的解决策略。
目录
1，什么是聚簇索引和非聚簇索引？
聚簇索引非叶子节点只有索引，没有数据，叶子节点记录了完整的数据行数据，一般是以主键作为键值而构建的B+树索引。聚簇索引能够提高查询性能，缺点是当表的数据发生大范围变化时，需要重新组织索引结构。
非聚簇索引将索引和数据行分开存储的，索引叶子节点存储着指向数据行的指针。需要二次磁盘IO查询数据行。
一个表最多只能有一个聚簇索引，可用有多个非聚簇索引。聚簇索引和非聚簇索引主要区别在于是否存储数据，非聚簇索引是根据索引查询出主键，然后再到非聚簇索引中查询数据行。
2，数据库mvcc详解？
mvcc是多版本控制，实现数据库的并发控制，提高数据的并发效率，主要体现在读已提交和可重复读两种隔离级别下。
mvcc实现是通过隐藏字段，undo版本链，快照。
隐藏字段：在他的聚簇索引记录中系统会加入事务ID和回滚指针，事务ID记录操作的事务，回滚指针在回滚的情况，数据的恢复。
undo版本链：每次处理数据前，都会把旧数据放入undo日志中，由于回滚指针的存在，undo的数据就形成了一个undo链表，链表头就是当前数据的回滚指针。
快照：当事务开启的时候，会生成当前系统的一个快照，在读已提交隔离级别下，每次select都会生成快照；在可重复读隔离级别下，select第一次会生成快照，后面的select读已经生成的快照。
具体实现：在底层查询的时候，只查询隐藏字段中事务ID小于等于当前的数据，后面的时候修改也不会看到，解决了脏读问题；在可重复读级别下，事务开始的时候快照已经生成，在此读取还是读的开始的快照，即便有修改，就解决了不可重复读的问题；加入间隙锁，解决了幻读问题(这个不属于mvcc的范畴)。
3，请你简单说一下mysql的事务隔离级别？
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 | 是 | 是 | 是 |
| 读已提交 | 否 | 是 | 是 |
| 可重复读 | 否 | 否 | 是 |
| 串行化 | 否 | 否 | 否 |
脏读：在一个事务中能读到另外一个事务还未提交的数据；
不可重复读：同样的条件读取同一行记录，两次读到的内容不一样(其他的事务修改了这个数据);
幻读：同样的条件，读取到的记录行数不一样(其他事务插入了或者删除了符合本事务的记录）；
mysql默认的隔离级别是可重复读，会有幻读的情况。
4，innodb使用B+树的好处？
B+树特点是业子节点存储数据或者指针，且形成一个有序的列表，非叶子节点只存储索引范围，这么做的好处是非叶子节点能够存储更多的索引信息。
减少IO次数，提高查询效率；
叶子节点存储了数据的指针，可以直接进行查询和排序；
叶子节点只存储索引信息，不存储数据，可以存储更多的索引信息，提高索引的翻盖范围；
叶子节点形成一个有序列表，可以高效范围查询。
5，innodb和myisam的区别？
innodb内部使用的是B+树，myisam使用的是B树；
innodb支持外键，myisam不支持外键；
innodb是聚簇索引，myisam不非聚簇索引；
innodb支持事务，myisam不支持事务；
innodb支持行级锁，mysiam仅支持表级锁；
6，mysql的引擎有哪些？
innodb：支持事务，行级锁，崩溃恢复和高并发性能，是mysql5.5之后默认的引擎。
myisam：较低的开销和较高的效率，是mysql5.5之前默认的引擎。
memory：数据都在内存中，提供了很快的读写性能，但是断电或者重启数据会丢失，一般用于临时表，作为缓存使用。
archive：用于高压缩比，快速插入的需求，适用于数据的归档。
csv：在存储数据时，以逗号作为数据项分隔符。
merge：用来管理多个表的合集，比如分表后想总的查询。
7，mysql的log都有哪些？有什么用处？
有错误日志，慢查询日志，二进制日志，redo日志，undo日志，其中重要的有二进制日志，redo日志，undo日志。
二进制日志(binlog)：用来同步数据，保证数据一致性和数据备份，主从同步就是依赖二进制日志，他的格式有基于行，基于sql，混合模式。
redo日志：他能让mysql拥有崩溃恢复能力。数据在buffer中，如果修改，修改buffer中数据(数据还没有写入磁盘)，同时生成redo日志，redo日志会以一定的策略写入磁盘(写入策略有：每次事务不刷盘，每次事务都刷盘，每次事务提交都把redo log buffer写入页缓存)。
undo日志：回滚日志，用于事务在回滚中将数据变成以前的样子。
8，mysql的binlog格式有哪些？
statement格式：binlog记录的是执行的sql，这个格式高效，但是有些情况会导致数据不一致的情况，比如更新的字段值是函数返回的，CURDATE()，CURRENT_USER();
row格式：binlog记录的是变动的数据行，能够保证数据的一致性，但是效率低下，比如一条sql语句更新了1W条数据，row格式就会记录1W条记录，而statement格式只会记录一条sql语句。
mixed格式：如果statement格式不会存在数据一直性的情况，就用statement格式；如果用statement格式存在数据不一致的情况，就用row格式。兼顾了效率和数据一致性。
9，怎么高效的创建索引？
只为搜索，排序，分组的列创建索引；
创建索引的列区分度要比较高；
创建联合索引的时候，查询条件最频繁的列放到最前面；
10，mysql的explain的详解？
explain可以查看读表顺序，扫描数据行数据量，是否用到索引，用的索引是什么，表关系等；
| 序号 | 列 | 备注说明 |
|---|---|---|
| 1 | id | 序列号，sql从大到小的执行执行，如果id相同，执行顺序由上到下 |
| 2 | select_type | 查询类型，simple，primary，subquery，dependent subquery，uncacheable subquery，union，union result，dependent union |
| 3 | table | 所访问的表 |
| 4 | partitions | 匹配的分区 |
| 5 | type | 访问的类型，system(单行)>const(唯一键读取)>eq_ref(唯一键联表)>ref(非唯一性索引扫描)>ref_or_null>index_merge(索引合并)>range(索引范围)>index(全索引扫描)>ALL |
| 6 | possible_keys | 可能用到的索引 |
| 7 | key | 实际用到的索引 |
| 8 | key_len | 索引长度 |
| 9 | ref | 索引的哪列被使用 |
| 10 | rows | 读取的记录行数 |
| 11 | filtered | 过滤的行数 |
| 12 | extra | 额外信息，Using filesort(文件排序)，Using temporary(临时表)，using index(索引覆盖)，using where(where过滤) |
11，mysql的排查方法？
开启慢查询日志，查看慢日志多少，以及都有哪些；
使用explain分析sql，查看sql是否高效，索引创建是否合理；
查看mysql进程占用内存，cpu情况，然后根据进程找到对应sql；
show processlist查看正在执行的sql，锁等待等情况；
12，索引覆盖是什么？
指查询语句所需要的数据，通过索引字段全部能满足，不需要回表查询数据。在innodb的非聚簇索引中，索引查询出主键，然后通过主键在回表查询数据，索引覆盖不需要后面的回表查询。
13，mysql执行sql的顺序？
from(定位表) -> on(关联条件) -> join(关联表) -> where(条件) -> group by(分组) -> having(分组条件) -> select(返回列) -> distinct(排重) -> order by(排序) -> limit(返回数量)
14，mysql中in和exist的区别？
in：先查询in里的sql，查询的结果存入临时表中，然后查询外成sql结果集遍历，和临时表的每个数据比较。
exists：关心记录是否存在，先查询外sql，然后遍历，把条件在带入子句。
//in子句过程，例如以下sql语句如下
select * from a where id in(select uid from b)
//执行过程
list = select uid from b //先查询子句的结果，放入变量
foreach (list as binfo) {
select * from a where id = binfo.uid
}
//--------------------------------
//exist子句过程
select * from a where id exist(select * from b where b.uid=a.id)
list = select * from a
for(list as ainfo){
select * from b where b.uid = ainfo.id //这个判断是否操作，只要有一个记录，就返回true，不用查询全部列表
}如果查询的两个表大小相当，那么用 in 和 exists 差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
15，mysql中各种锁详解？
使用方式上看，有共享锁(读锁)，排它锁(写锁)。
使用范围上看，有行锁，表锁。
记录锁：数据是存在的，然后锁住。
间隙锁：记录不存在，所在的ID锁住。
意向锁：由于行锁和表锁可以共存的，加行锁前需要看看表锁是否存在。
思想上，有乐观锁，悲观锁。乐观锁不是数据库实现功能，是一种编程思想和技巧。
16，Innodb为什么要用自增id作为主键？
用自增id作为主键，会顺序添加，当一页满了后，开辟新的页，不会存在分页的情况，如果用uuid做为主键，由于插入没有顺序性，插入的页满了，就会存在分页的情况，从而影响效率。
17，mysql主从同步延迟长怎么解决？
查看binlog是否用了row格式，因为row格式生成的数据量大，可以改成更高效的mixed格式；
查看主从之间网络是否够快，网络的问题会影响binlog数据的传输效率；
查看从数据库机器的配置是否比较小，或者磁盘比较慢，这种情况需要更换更好的硬件；
查看从服务器是否有其他的耗资源的任务，服务器资源被消耗会影响数据同步的效率；
查看从服务器同步过程是否有报错，如果设置不合理，从同步遇到错误会停止；
查看从库配置，比如innodb_flush_log_at_trx_commit，sync_binlog，innodb_buffer_pool_size设置合理的值，可以减少延迟时间。
一般数据库同步设置的都是最终一致性，延迟肯定存在的，如果想无延迟，可以设置数据库同步为强一致，但是这种效率低。
18，innodb如何实现事务的？
事务是通过buffer pool(存放数据的buffer)，log buffer(采访日志的buffer)，redo log，undo log实现的。
一条更新sql过来后，根据条件在buffer中寻找该数据，如果没有找到，在从磁盘中读到buffer中，然后在buffer中修改该数据，生成redo日志，并把redo日志存入log buffer中，如果回滚，需要undo日志，把数据恢复到以前的状态，如果提交，则把redo日志持久化，后续在写入磁盘。
------------------------------------------推荐阅读----------------------------------------------------------------
扫一扫
3094
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
