总结一下面试中经常被问到的理论知识。现在发现理论知识很重要,,很惭愧的发现理论知识掌握的并不扎实,现在整理出来。
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/