总结一下面试中经常被问到的理论知识。现在发现理论知识很重要,，很惭愧的发现理论知识掌握的并不扎实，现在整理出来。
1.什么是聚集索引：
聚集索引又叫聚簇索引，聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。mysql中innodb存储引擎是聚集索引组织表，myisam是堆组织表。
2.什么是覆盖索引：
select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
3.sync_binlog 和innodb_flush_log_at_trx_commit的作用以及不同值有什么区别
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
innodb_flush_log_at_trx_commit =0 每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；
log buffer 会 每秒写入到日志文件并刷写（flush）到磁盘。但每次事务提交不会有任何影响，也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下，MySQL性能最好，但如果 mysqld 进程崩溃，通常会导致最后 1s 的日志丢失。
innodb_flush_log_at_trx_commit =1 每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上；
当取值为 1 时，每次事务提交时，log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢。
N=2 每个事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度；
4.innodb和myisam索引的实现方式和区别
myisam使用b+tree索引结构，叶节点的data域存放的是数据记录的地址。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
innodb：也使用B+Tree作为索引结构，在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
innodb的辅助索引引用主键作为data域，辅助索引搜索需要检索两遍索引。首先检索辅助索引获得主键，然后用主键到主索引中检索获得的记录。
两者的区别：innodb的数据文件本身就是索引文件。myisam索引文件和数据文件是分离的。
innodb的辅助索引data域存储相应记录主键的值而不是地址。
5.binlog的几种格式 以及各自的优缺点
statement：记录的修改数据的sql，减少了binlog的日志量，节约了io，提高性能。
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).
Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。
优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题
缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。
3.Mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。
6.事务的特性，隔离级别
1.原子性（atomicity）：一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
2.一致性（consistency）：数据库总数从一个一致性的状态转换到另一个一致性的状态。
3.隔离性（isolation）：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
4.持久性（durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。
隔离级别：
1.read-uncommit：未提交的事务可以被其他事务看到，很少应用于生产环境，缺点：脏读
2.read-commit：已提交的事务可以被其他事务看到 缺点：不可重复读：在同一事务中执行同一语句可能看到不一样的结果
3.repeatable-read：mysql默认的隔离级别，同一事务的多个实例在并发读取数据时，会看到同样的数据行。缺点：幻读：当用户读取某一范围的数据行时，另一事务又在该范围内插入了新行，当用户在读取该范围的数据行时，会发现有新的幻影行。 （innodb和falcon存储引擎通过多版本并发控制解决了该问题）
4.serializable(可串行化）：通过强制事务排序，使之不可能相互冲突，解决了幻读，缺点：会导致大量的超时和锁竞争。
7.mysql 5.7 半同步复制做了什么新改进
mysql 5.5/5.6如果开启半同步，会存在数据不一致的风险，
有这么一个场景，客户端提交了一个事务，master把binlog发送给slave，在发送的期间，网络出现波动，此时Binlog Dump线程发送就会卡住，要等待slave把binlog写到本地的relay-log里，然后给master一个反馈，等待的时间以rpl_semi_sync_master_timeout参数为准，默认为10秒。在这等待的10秒钟里，在其他会话里，查看刚才的事务是可以看见的，此时一旦master发生宕机，由于binlog没有发送给slave，前端app切到slave查看，就会发现刚才已提交的事务不见了。
为了解决这个问题，5.7有增强半同步复制。
5.7
rpl_semi_sync_master_wait_point = AFTER_SYNC
client-->MySQL SQL Parse-->Storage Involve-->write binary log-->wait ACK-->storage commit-->client(OK)
主库把每一个事务写道二进制日志并保存到磁盘上，并发给从库，主库等待从库写到自己的relay-log日志里的确认信息。主库收到确认后再将事务写到存储引擎里，然后将结果返回给客户端。
5.7之前：
rpl_semi_sync_master_wait_point = AFTER_COMMIT
client-->MySQL SQL Parse-->Storage Involve-->write binary log-->storage commit-->wait ACK-->client(OK)
主库把每一个事务写到二进制日志并保存到磁盘上，且发送给从库，并把事务写到存储引擎里，主库在等待从库写到自己的relay-log里确认信息。在接收到确认信息后，主库把相应结果返回给客户端。
8.MySQL下count(*)比count(id)慢的原因
在不加WHERE限制条件的情况下，COUNT(*)与COUNT(COL)基本可以认为是等价的；
但是在有WHERE限制条件的情况下，COUNT(*)会比COUNT(COL)快非常多，
9.索引的种类
全文索引，hash索引，b-tree ，rtree
10.mysql都是有哪些类型的存储引擎
myisam，innodb，memory，merge，archive等，用show engines可以查看。
1.什么是聚集索引：
聚集索引又叫聚簇索引，聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。mysql中innodb存储引擎是聚集索引组织表，myisam是堆组织表。
2.什么是覆盖索引：
select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
3.sync_binlog 和innodb_flush_log_at_trx_commit的作用以及不同值有什么区别
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
innodb_flush_log_at_trx_commit =0 每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；
log buffer 会 每秒写入到日志文件并刷写（flush）到磁盘。但每次事务提交不会有任何影响，也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下，MySQL性能最好，但如果 mysqld 进程崩溃，通常会导致最后 1s 的日志丢失。
innodb_flush_log_at_trx_commit =1 每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上；
当取值为 1 时，每次事务提交时，log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢。
N=2 每个事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度；
4.innodb和myisam索引的实现方式和区别
myisam使用b+tree索引结构，叶节点的data域存放的是数据记录的地址。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
innodb：也使用B+Tree作为索引结构，在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
innodb的辅助索引引用主键作为data域，辅助索引搜索需要检索两遍索引。首先检索辅助索引获得主键，然后用主键到主索引中检索获得的记录。
两者的区别：innodb的数据文件本身就是索引文件。myisam索引文件和数据文件是分离的。
innodb的辅助索引data域存储相应记录主键的值而不是地址。
5.binlog的几种格式 以及各自的优缺点
statement：记录的修改数据的sql，减少了binlog的日志量，节约了io，提高性能。
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).
Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。
优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题
缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。
3.Mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。
6.事务的特性，隔离级别
1.原子性（atomicity）：一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
2.一致性（consistency）：数据库总数从一个一致性的状态转换到另一个一致性的状态。
3.隔离性（isolation）：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
4.持久性（durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。
隔离级别：
1.read-uncommit：未提交的事务可以被其他事务看到，很少应用于生产环境，缺点：脏读
2.read-commit：已提交的事务可以被其他事务看到 缺点：不可重复读：在同一事务中执行同一语句可能看到不一样的结果
3.repeatable-read：mysql默认的隔离级别，同一事务的多个实例在并发读取数据时，会看到同样的数据行。缺点：幻读：当用户读取某一范围的数据行时，另一事务又在该范围内插入了新行，当用户在读取该范围的数据行时，会发现有新的幻影行。 （innodb和falcon存储引擎通过多版本并发控制解决了该问题）
4.serializable(可串行化）：通过强制事务排序，使之不可能相互冲突，解决了幻读，缺点：会导致大量的超时和锁竞争。
7.mysql 5.7 半同步复制做了什么新改进
mysql 5.5/5.6如果开启半同步，会存在数据不一致的风险，
有这么一个场景，客户端提交了一个事务，master把binlog发送给slave，在发送的期间，网络出现波动，此时Binlog Dump线程发送就会卡住，要等待slave把binlog写到本地的relay-log里，然后给master一个反馈，等待的时间以rpl_semi_sync_master_timeout参数为准，默认为10秒。在这等待的10秒钟里，在其他会话里，查看刚才的事务是可以看见的，此时一旦master发生宕机，由于binlog没有发送给slave，前端app切到slave查看，就会发现刚才已提交的事务不见了。
为了解决这个问题，5.7有增强半同步复制。
5.7
rpl_semi_sync_master_wait_point = AFTER_SYNC
client-->MySQL SQL Parse-->Storage Involve-->write binary log-->wait ACK-->storage commit-->client(OK)
主库把每一个事务写道二进制日志并保存到磁盘上，并发给从库，主库等待从库写到自己的relay-log日志里的确认信息。主库收到确认后再将事务写到存储引擎里，然后将结果返回给客户端。
5.7之前：
rpl_semi_sync_master_wait_point = AFTER_COMMIT
client-->MySQL SQL Parse-->Storage Involve-->write binary log-->storage commit-->wait ACK-->client(OK)
主库把每一个事务写到二进制日志并保存到磁盘上，且发送给从库，并把事务写到存储引擎里，主库在等待从库写到自己的relay-log里确认信息。在接收到确认信息后，主库把相应结果返回给客户端。
8.MySQL下count(*)比count(id)慢的原因
在不加WHERE限制条件的情况下，COUNT(*)与COUNT(COL)基本可以认为是等价的；
但是在有WHERE限制条件的情况下，COUNT(*)会比COUNT(COL)快非常多，
9.索引的种类
全文索引，hash索引，b-tree ，rtree
10.mysql都是有哪些类型的存储引擎
myisam，innodb，memory，merge，archive等，用show engines可以查看。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30162734/viewspace-2143451/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30162734/viewspace-2143451/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
