数据库面试题总结

1．MySQL数据库引擎种类

   （MySQL-5.5.5开始,InnoDB作为默认存储引擎）之前是MyISAM，更早是ISAM你能用的数据库引擎取决于mysql在安装的时候是如何被编译的。要添加一个新的引擎，就必须重新编译MYSQL。在缺省情况下，MYSQL支持三个引擎：ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEYDB（BDB），也常常可以使用。 
　　ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。 
　　MYISAM是MYSQL的ISAM扩展格式和缺省的数据库引擎(5.5之前)。除了提供ISAM里所没有的索引和字段管理的大 量功能，MYISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZETABLE命令，来恢复被更新机制所浪费的空间。MYISAM还有一些有用的扩展，例如用来修复数据库文件的 MYISAMCHK工具和用来恢复浪费空间的MYISAMPACK工具。 MYISAM强调了快速读取操作，这可能就是为什么MYSQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。 
　　HEAP允许只驻留在内存里的临时表格。驻留在内存使得HEAP比ISAM和MYISAM的速度都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间，HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，用完表格后要删除表格。 
　　INNODB和BERKLEYDB（BDB）数据库引擎都是造就MYSQL灵活性的技术的直接产品，这项技术就是MySql++ API。在使用MySql的时候，你所面对的每一个挑战几乎都源于ISAM和MYIASM数据库引擎不支持事务处理也不支持外来键。尽管要比ISAM和MYISAM引擎慢很多，但是INNODB和BDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。

2. MySQL锁类型

根据锁的类型分，可以分为共享锁，排他锁，意向共享锁和意向排他锁。 
　　根据锁的粒度分，又可以分为行锁，表锁。 
　　对于mysql而言，事务机制更多是靠底层的存储引擎来实现，因此，mysql层面只有表锁，而支持事务的innodb存 储引擎则实现了行锁(记录锁（在行相应的索引记录上的锁）)，gap锁（是在索引记录间歇上的锁），next-key锁（是记录锁和在此索引记录之前的gap上的锁的结合）。Mysql的记录锁实质是索引记录的锁，因为innodb是索引组织表；gap锁是索引记录间隙的锁，这种锁只在RR隔离级别下有效；next-key锁是记录锁加上记录之前gap锁的组合。mysql通过gap锁和next-key锁实现RR隔离级别。 
　　说明：对于更新操作(读不上锁)，只有走索引才可能上行锁；否则会对聚簇索引的每一行上写锁，实际等同于对表上写锁。 
　　若多个物理记录对应同一个索引，若同时访问，也会出现锁冲突； 
　　当表有多个索引时，不同事务可以用不同的索引锁住不同的行，另外innodb会同时用行锁对数据记录(聚簇索引)加 锁。 
　　MVCC(多版本并发控制)并发控制机制下，任何操作都不会阻塞读操作，读操作也不会阻塞任何操作，只因为读不上锁。 
　　共享锁：由读表操作加上的锁，加锁后其他用户只能获取该表或行的共享锁，不能获取排它锁，也就是说只能读不能写 
　　排它锁：由写表操作加上的锁，加锁后其他用户不能获取该表或行的任何锁，典型是mysql事务中的更新操作 
　　意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。 
　　意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

3.理解数据库事务四大特性的四种隔离级别

数据库中事务的四大特性（ACID），并且将会详细地说明事务的隔离级别。

如果一个数据库声称支持事务的操作，那么该数据库必须要具备以下四个特性：

3.1数据库中事务的四大特性

⑴ 原子性（Atomicity）

　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

⑵ 一致性（Consistency）

　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

　　拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

⑶ 隔离性（Isolation）

　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

　　即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

　　关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

⑷ 持久性（Durability）

　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

　　例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

3.2数据库事务的常见问题

在讨论数据库的隔离级别的同时重点来说明下事务的隔离性，当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，在介绍数据库提供的各种隔离级别之前，我们先看看如果不考虑事务的隔离性，会发生的几种问题：

(1)脏读

　　脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

　　当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下

    updateaccount set money=money+100 where name=’B’; (此时A通知B)

 

    updateaccount set money=money - 100 where name=’A’;

　　当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

(2)不可重复读

　　不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

　　例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

　　不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据A和B依次查询就可能不同，A和B就可能打起来了……

(3)虚读(幻读)

　　幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

　　幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

 

3.3数据库四种隔离级别：

3.3.1Serializable (串行化)

可避免脏读、不可重复读、幻读的发生。Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

3.3.2Repeatable read (可重复读)

可避免脏读、不可重复读的发生。重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。重复读可以解决不可重复读问题。，不可重复读对应的是修改，即UPDATE操作。

3.3.3 Read committed (读已提交)

可避免脏读的发生。读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。如果在一个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

3.3.4Read uncommitted (读未提交)

最低级别，任何情况都无法保证。读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

　以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。

3.4 日志文件

错误日志：记录出错信息，也记录一些警告信息或者正确的信息 
慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。 
二进制日志：记录对数据库执行更改的所有操作 
查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。 

3.5事务是如何通过日志来实现的

　在Innodb存储引擎中，事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现 的，当开始一个事务的时候，会记录该事务的lsn(logsequence number)号; 当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方 式”， innodb通过此方式来保证事务的完整性。也就意味着磁盘上存储的数据页和内存缓冲池上面的页是不同步的，是先写入redo log，然后写入data file，因此是一种异步的方式。 

事务日志包括：重做日志redo和回滚日志undo

Redo记录的是已经全部完成的事务，就是执行了commit的事务，记录文件是ib_logfile0 ib_logfile1

Undo记录的是已部分完成并且写入硬盘的未完成的事务，默认情况下回滚日志是记录下表空间中的（共享表空间或者独享表空间）

一般情况下，mysql在崩溃之后，重启服务，innodb通过回滚日志undo将所有已完成并写入磁盘的未完成事务进行rollback，然后redo中的事务全部重新执行一遍即可恢复数据，但是随着redo的量增加，每次从redo的第一条开始恢复就会浪费长的时间，所以引入了checkpoint机制

Dirty page：脏页 什么意思呢？

一般业务运行过程中，当业务需要对某张的某行数据进行修改的时候，innodb会先将该数据从磁盘读取到缓存中去，然后在缓存中对这条数据进行修改，这样缓存中的数据就和磁盘的数据不一致了，这个时候缓存中的数据就称为dirty page，只有当脏页统一刷新到磁盘中才会是clean page

Checkpoint：如果在某个时间点，脏页的数据被刷新到了磁盘，系统就把这个刷新的时间点记录到redo log的结尾位置，在进行恢复数据的时候，checkpoint时间点之前的数据就不需要进行恢复了，可以缩短时间 

Innodb_log_buffer_size 重做日志缓存大小

Innodb_log_file_size redo log文件大小  文件越大 数据恢复的时间越长

Innodb_log_file_group redo log文件数量 默认是2个 ib_logfile0 ib_logfile1
隔离性：通过锁实现 原子性、一致性和持久性是通过redo和undo来完成的。

 

 

4. mysql中myisam与innodb的区别，以及索引的实现方式

4.1   myisam与innodb的区别

1.  InnoDB 不支持FULLTEXT 类型的索引。
2.  InnoDB 中不保存表的具体行数，也就是说，执行 select count(*) fromtable 时，InnoDB 要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。注意的是，当 count(*)语句包含where 条件时，两种表的操作是一样的。
3.  对于 AUTO_INCREMENT 类型的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中，可以和其他字段一起建立联合索引。
4.  DELETE FROM table 时，InnoDB 不会重新建立表，而是一行一行的删除。
5.  LOAD TABLE FROM MASTER 操作对 InnoDB 是不起作用的，解决方法是首先把 InnoDB 表改成 MyISAM
表，导入数据后再改成 InnoDB 表，但是对于使用的额外的InnoDB 特性（例如外键）的表不适用。

6,myisam不支持事务，innodb支持事务。Myisam是表级锁，innodb是行级锁。

Myisam常用于select较多的数据库，innnodb常用于更新较多的数据。

 

4.2   innodb引擎的4大特性

1，支持事务安全,2，数据多版本读取,3，锁定机制的实现,4，实现外键

4.3  索引方式

4.3.1区别

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

4.3.2InnoDB 与 MyISAM 结构上的区别

1.InnoDB的主键索引 ，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，所以必须有主键，如果没有显示定义，自动为生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形

2.InnoDB的辅助索引（Secondary Index， 也就是非主键索引）也会包含主键列，比如名字建立索引，内部节点 会包含名字，叶子节点会包含该名字对应的主键的值，如果主键定义的比较大，其他索引也将很大

3.MyISAM引擎使用B+Tree作为索引结构，索引文件叶节点的data域存放的是数据记录的地址，指向数据文件中对应的值，每个节点只有该索引列的值

4.MyISAM主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，辅助索引可以重复，

  （由于MyISAM辅助索引在叶子节点上存储的是数据记录的地址，和主键索引一样，所以相对于B+的InnoDB可通过辅助索引

        快速找到所有的数据，而不需要再遍历一边主键索引，所以适用于OLAP）

 InnoDB索引和MyISAM索引的区别：

一是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。

二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

下面主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式：

4.3.3 MyISAM索引实现：

1）主键索引：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

2）辅助索引（Secondary key）

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

4.3.4 InnoDB索引实现

然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同.

1）主键索引：

         MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

2）. InnoDB的辅助索引

       InnoDB的所有辅助索引都引用主键作为data域。

   InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引（Secondary Index， 也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

  文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

      不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

4.3.5 InnoDB索引和MyISAM索引的区别：

一是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。

二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

5. MySQL几种备份方式（重点）

5.1　备份方式

1、逻辑备份：使用mysql自带的mysqldump工具进行备份。备份成sql文件形式。 
　　　　优点：最大好处是能够与正在运行的mysql自动协同工作，在运行期间可以确保备份是当时的点，它会自动将对应操作的表锁定，不允许其他用户修改(只能访问)。可能会阻止修改操作。sql文件通用方便移植。 
　　　　缺点：备份的速度比较慢。如果是数据量很多的时候。就很耗时间。如果数据库服务器处在提供给用户服务状态，在这段长时间操作过程中，意味着要锁定表(一般是读锁定，只能读不能写入数据)。那么服务就会影响的。 
2、物理备份：直接拷贝mysql的数据目录。 
　　直接拷贝只适用于myisam类型的表。这种类型的表是与机器独立的。但实际情况是，你设计数据库的时候不可能全部使用myisam类型表。你也不可能因为myisam类型表与机器独立，方便移植，于是就选择这种表，这并不是选择它的理由。 
　　　　缺点：你不能去操作正在运行的mysql服务器(在拷贝的过程中有用户通过应用程序访问更新数据，这样就无法备份当时的数据)可能无法移植到其他机器上去。 
3、双机热备份。 
mysql数据库没有增量备份的机制。当数据量太大的时候备份是一个很大的问题。还好mysql数据库提供了一种主从备份的机制(也就是双机热备) 
　　　　优点：适合数据量大的时候。现在明白了。大的互联网公司对于mysql数据备份，都是采用热机备份。搭建多台数据库服务器，进行主从复制。

5.2数据库不能停机，请问如何备份?如何进行全备份和增量备份? 
　　完全备份：完整备份一般一段时间进行一次，且在网站访问量最小的时候，这样常借助批处理文件定时备份。主要是写一个批处理文件在里面写上处理程序的绝对路径然后把要处理的东西写在后面，即完全备份数据库。 
　　增量备份：对ddl和dml语句进行二进制备份。且5.0无法增量备份，5.1后可以。如果要实现增量备份需要在my.ini文件中配置备份路径即可，重启mysql服务器，增量备份就启动了。也可以使用逻辑备份和双机热备份。

5.3  mysql是怎么备份的（重点）

  5.3.1备份的目的 
　　　　做灾难恢复：对损坏的数据进行恢复和还原 
　　　　需求改变：因需求改变而需要把数据还原到改变以前 
　　　　测试：测试新功能是否可用 

  5.3.2 备份需要考虑的问题 

　可以容忍丢失多长时间的数据； 
　　　　恢复数据要在多长时间内完； 
　　　　恢复的时候是否需要持续提供服务； 
　　　　恢复的对象，是整个库，多个表，还是单个库，单个表。

   5.3.3备份的类型 
1、根据是否需要数据库离线 
　　　　　　冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行； 
　　　　　　温备（warm backup）： 服务在线，但仅支持读请求，不允许写请求； 
　　　　　　热备（hot backup）：备份的同时，业务不受影响。 
　　　　注： 
　　　　　　1、这种类型的备份，取决于业务的需求，而不是备份工具 
　　　　　　2、MyISAM不支持热备，InnoDB支持热备，但是需要专门的工具 
2、根据要备份的数据集合的范围 
　　　　　　完全备份：full backup，备份全部字符集。 
　　　　　　增量备份:incremental backup 上次完全备份或增量备份以来改变了的数据，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。 
　　　　　　差异备份：differential backup 上次完全备份以来改变了的数据。 
　　　　　　建议的恢复策略： 
　　　　　　　　完全+增量+二进制日志 
　　　　　　　　完全+差异+二进制日志 
3、根据备份数据或文件 
　　　　　　物理备份：直接备份数据文件 
　　　　　　优点：备份和恢复操作都比较简单，能够跨mysql的版本，恢复速度快，属于文件系统级别的 
　　　　　　建议：不要假设备份一定可用，要测试mysql>check tables；检测表是否可用 
　　　　　　逻辑备份: 备份表中的数据和代码 
　　　　　　优点：恢复简单、备份的结果为ASCII文件，可以编辑与存储引擎无关可以通过网络备份和恢复 
　　　　　　缺点：备份或恢复都需要mysql服务器进程参与备份结果占据更多的空间，浮点数可能会丢失精度 还原之后，缩影需要重建  
　　　　1、 数据； 
　　　　2、配置文件； 
　　　　3、代码：存储过程、存储函数、触发器 
　　　　4、os相关的配置文件 
　　　　5、复制相关的配置 
　　　　6、二进制日志 

5.3.4备份和恢复的实现 

1、利用select into outfile实现数据的备份与还原。 
　　　　2、利用mysqldump工具对数据进行备份和还原 
　　　　3、利用lvm快照实现几乎热备的数据备份与恢复 
　　　　4、基于Xtrabackup做备份恢复。 
　　　　优势： 
　　　　　　1、快速可靠的进行完全备份 
　　　　　　2、在备份的过程中不会影响到事务 
　　　　　　3、支持数据流、网络传输、压缩，所以它可以有效的节约磁盘资源和网络带宽。 
　　　　　　4、可以自动备份校验数据的可用性。

6.什么情况下使用索引？

表的主关键字 
　　自动建立唯一索引 

表的字段唯一约束 直接条件查询的字段 查询中与其它表关联的字段 查询中排序的字段   查询中统计或分组统计的字段 

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

表记录太少 

经常插入、删除、修改的表 

数据重复且分布平均的表字段 

经常和主字段一块查询但主字段索引值比较多的表字段

7.千万级MySQL数据库建立索引的事项及提高性能的手段

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t wherenum is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0 
3.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。 
4.应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10or num=20可以这样查询：select id from t wherenum=10 union all select id from t where num=20 
5.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t wherenum in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3 
6.避免使用通配符。下面的查询也将导致全表扫描：select id from t wherename like ‘李%’若要提高效率，可以考虑全文检索。 
7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t wherenum=@num可以改为强制查询使用索引：select id from twith(index(索引名)) where num=@num 
8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t wherenum/2=100应改为:select id from t wherenum=100*2 
9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t wheresubstring(name,1,3)=’abc’ ，name以abc开头的id应改为:select id from t wherename like ‘abc%’ 
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。 
12.不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #tfrom t where 1=0 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(…) 
13.很多时候用 exists 代替 in 是一个好的选择：select num from a where numin(select num from b)用下面的语句替换：select num from a whereexists(select 1 from b where num=a.num) 
14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。 
15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。 
16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。 
17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。 
18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。 
19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。 
20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。 
21.避免频繁创建和删除临时表，以减少系统表资源的消耗。 
22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。 
23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。 
24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。 
25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。 
26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。 
27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。 
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。 
29.尽量避免大事务操作，提高系统并发能力。 
30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

8 .MySQL调优?（重点）

I 硬件配置优化 
Ø CPU选择：多核的CPU，主频高的CPU 
Ø 内存：更大的内存 
Ø 磁盘选择：更快的转速、RAID、阵列卡， 
Ø 网络环境选择：尽量部署在局域网、SCI、光缆、千兆网、双网线提供冗余、0.0.0.0多端口绑定监听 
II操作系统级优化 
Ø 使用64位的操作系统，更好的使用大内存。 
Ø 设置noatime,nodiratime 
Ø 优化内核参数 
Ø 加大文件描述符限制 
Ø 文件系统选择 xfs 
III Mysql设计优化 
III.1 存储引擎的选择 
Ø Myisam：数据库并发不大，读多写少,而且都能很好的用到索引，sql语句比较简单的应用，TB数据仓库 
Ø Innodb：并发访问大，写操作比较多，有外键、事务等需求的应用，系统内存较大。 
III.2 命名规则 
Ø 多数开发语言命名规则：比如MyAdress 
Ø 多数开源思想命名规则：my_address 
Ø 避免随便命名 
III.3 字段类型选择 
　　　　字段类型的选择的一般原则： 
Ø 根据需求选择合适的字段类型，在满足需求的情况下字段类型尽可能小。 
Ø 只分配满足需求的最小字符数，不要太慷慨。原因：更小的字段类型更小的字符数占用更少的内存，占用更少的磁盘空间，占用更少的磁盘IO，以及占用更少的带宽。 
　　　　对于varchar和char的选择要根据引擎和具体情况的不同来选择，主要依据如下原则: 
1.如果列数据项的大小一致或者相差不大，则使用char。 
2.如果列数据项的大小差异相当大，则使用varchar。 
3.对于MyISAM表，尽量使用Char，对于那些经常需要修改而容易形成碎片的myisam和isam数据表就更是如此，它的缺点就是占用磁盘空间。 
4.对于InnoDB表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分（所有数据行共用一个表头部分，这个标头部分存放着指向各有关数据列的指针），所以使用char类型不见得会比使用varchar类型好。事实上，因为char类型通常要比varchar类型占用更多的空间，所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利。 
5.表中只要存在一个varchar类型的字段，那么所有的char字段都会自动变成varchar类型，因此建议定长和变长的数据分开。 
III.4 编码选择 
　　　　　　单字节 latin1 
　　　　　　多字节 utf8(汉字占3个字节，英文字母占用一个字节)如果含有中文字符的话最好都统一采用utf8类型，避免乱码的情况发生。 
III.5 主键选择原则 
　　　　注：这里说的主键设计主要是针对INNODB引擎 
1.能唯一的表示行。 
2.显式的定义一个数值类型自增字段的主键，这个字段可以仅用于做主键，不做其他用途。 
3.MySQL主键应该是单列的，以便提高连接和筛选操作的效率。 
4.主键字段类型尽可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT。 
5.尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能。 
6.MySQL主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。 
7.MySQL主键应当有计算机自动生成。 
8.主键字段放在数据表的第一顺序。 
　　　　推荐采用数值类型做主键并采用auto_increment属性让其自动增长。 
III.6 其他需要注意的地方 
Ø NULL OR NOT NULL 
　　　　　　尽可能设置每个字段为NOT NULL，除非有特殊的需求，原因如下： 
1.使用含有NULL列做索引的话会占用更多的磁盘空间，因为索引NULL列需要而外的空间来保存。 
2.进行比较的时候，程序会更复杂。 
3.含有NULL的列比较特殊，SQL难优化，如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。 
Ø 索引 
　　　　　　　　索引的优点：极大地加速了查询，减少扫描和锁定的数据行数。 
　　　　　　　　索引的缺点：占用磁盘空间，减慢了数据更新速度，增加了磁盘IO。 
　　　　　　添加索引有如下原则： 
1 选择唯一性索引。 
2.为经常需要排序、分组和联合操作的字段建立索引。 
3.为常作为查询条件的字段建立索引。 
4.限制索引的数据，索引不是越多越好。 
5.尽量使用数据量少的索引，对于大字段可以考虑前缀索引。 
6.删除不再使用或者很少使用的索引。 
7.结合核心SQL优先考虑覆盖索引。 
8.忌用字符串做主键。 
Ø 反范式设计 
　　　　　　适当的使用冗余的反范式设计，以空间换时间有的时候会很高效。 
IV Mysql软件优化 
Ø 开启mysql复制，实现读写分离、负载均衡，将读的负载分摊到多个从服务器上，提高服务器的处理能力。 
Ø 使用推荐的GA版本，提升性能 
Ø 利用分区新功能进行大数据的数据拆分 
V Mysql配置优化 
　　注意：全局参数一经设置，随服务器启动预占用资源。 
Ø key_buffer_size参数 
mysql索引缓冲，如果是采用myisam的话要重点设置这个参数，根据（key_reads/key_read_requests）判断 
Ø innodb_buffer_pool_size参数 
INNODB 数据、索引、日志缓冲最重要的引擎参数，根据（hit riatos和FILE I/O）判断 
Ø wait_time_out参数 
　　　　线程连接的超时时间，尽量不要设置很大，推荐10s 
Ø max_connections参数 
　　　　服务器允许的最大连接数，尽量不要设置太大，因为设置太大的话容易导致内存溢出 
Ø thread_concurrency参数 
　　　　线程并发利用数量，(cpu+disk)*2,根据(os中显示的请求队列和tickets)判断 
Ø sort_buffer_size参数 
　　　　获得更快的–ORDER BY,GROUP BY,SELECTDISTINCT,UNION DISTINCT 
Ø read_rnd_buffer_size参数 
　　　　当根据键进行分类操作时获得更快的–ORDER BY 
Ø join_buffer_size参数 
join连接使用全表扫描连接的缓冲大小，根据select_full_join判断 
Ø read_buffer_size参数 
　　　　全表扫描时为查询预留的缓冲大小，根据select_scan判断 
Ø tmp_table_size参数 
　　　　临时内存表的设置，如果超过设置就会转化成磁盘表，根据参数(created_tmp_disk_tables)判断 
Ø innodb_log_file_size参数(默认5M) 
　　　　记录INNODB引擎的redo log文件，设置较大的值意味着较长的恢复时间。 
Ø innodb_flush_method参数(默认fdatasync) 
Linux系统可以使用O_DIRECT处理数据文件，避免OS级别的cache，O_DIRECT模式提高数据文件和日志文件的IO提交性能 
Ø innodb_flush_log_at_trx_commit(默认1) 
1.0表示每秒进行一次log写入cache，并flush log到磁盘。 
2.1表示在每次事务提交后执行log写入cache，并flush log到磁盘。 
3.2表示在每次事务提交后，执行log数据写入到cache，每秒执行一次flush log到磁盘。 
VI Mysql语句级优化 
1.性能查的读语句，在innodb中统计行数,建议另外弄一张统计表，采用myisam，定期做统计.一般的对统计的数据不会要求太精准的情况下适用。 
2.尽量不要在数据库中做运算。 
3.避免负向查询和%前缀模糊查询。 
4.不在索引列做运算或者使用函数。 
5.不要在生产环境程序中使用select * from 的形式查询数据。只查询需要使用的列。 
6.查询尽可能使用limit减少返回的行数，减少数据传输时间和带宽浪费。 
7.where子句尽可能对查询列使用函数，因为对查询列使用函数用不到索引。 
8.避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’。 
9.所有的SQL关键词用大写，养成良好的习惯，避免SQL语句重复编译造成系统资源的浪费。 
10.联表查询的时候，记得把小结果集放在前面，遵循小结果集驱动大结果集的原则。 
11.开启慢查询，定期用explain优化慢查询中的SQL语句。

9. MySQL数据库同步怎样实现

1、安装配置，两台服务器，分别安装好MySQL。采用单向同步的方式，就是Master的数据是主的数据，然后slave主动去Master哪儿同步数据回来。两台服务器的配置一样，把关键的配置文件拷贝一下，两台服务器做相同的拷贝配置文件操作。 
2、配置Master服务器，要考虑我们需要同步那个数据库，使用那个用户同步，我们这里为了简单起见，就使用root用户进行同步，并且只需要同步数据库abc。 
3、配置Slave服务器，我们的slave服务器主要是主动去Master服务器同步数据回来。 
4、测试安装，首先查看一下slave的主机日志：检查是否连接正常，在Master查看信息，查看Master状态：查看Master下MySQL进程信息：在slave上查看信息：查看slave状态：查看slave下MySQL进程信息：再在Master的abc库里建立表结构并且插入数据，然后检查slave有没有同步这些数据，就能够检查出是否设置成功。

10.MySQL Binlog格式介绍      

mysql binlog日志有三种格式，分别为Statement,MiXED,以及ROW！

10.1 Statement：

每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数，last_insert_id()，以及user-definedfunctions(udf)会出现问题).

10.2.Row:

不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行altertable之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

10.3.Mixedlevel

 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以rowlevel来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。