Python面试汇总（三）——数据库

1、数据库事务

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

一个数据库事务通常包含对数据库进行读或写的一个操作序列。它的存在包含有以下两个目的：

1、为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
2、当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

特性

并非任意的对数据库的操作序列都是数据库事务。事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
持久性（Durability）：一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。

2、关系型数据库和非关系型数据库

1) 数据存储方式不同。

关系型和非关系型数据库的主要差异是数据存储的方式。关系型数据天然就是表格式的，因此存储在数据表的行和列中。数据表可以彼此关联协作存储，也很容易提取数据。

与其相反，非关系型数据不适合存储在数据表的行和列中，而是大块组合在一起。非关系型数据通常存储在数据集中，就像文档、键值对或者图结构。你的数据及其特性是选择数据存储和提取方式的首要影响因素。

2) 扩展方式不同。

SQL和NoSQL数据库最大的差别可能是在扩展方式上，要支持日益增长的需求当然要扩展。

要支持更多并发量，SQL数据库是纵向扩展，也就是说提高处理能力，使用速度更快速的计算机，这样处理相同的数据集就更快了。因为数据存储在关系表中，操作的性能瓶颈可能涉及很多个表，这都需要通过提高计算机性能来客服。虽然SQL数据库有很大扩展空间，但最终肯定会达到纵向扩展的上限。

而NoSQL数据库是横向扩展的。非关系型数据存储天然就是分布式的，NoSQL数据库的扩展可以通过给资源池添加更多普通的数据库服务器(节点)来分担负载。

3) 对事务性的支持不同。

如果数据操作需要高事务性或者复杂数据查询需要控制执行计划，那么传统的SQL数据库从性能和稳定性方面考虑是你的最佳选择。SQL数据库支持对事务原子性细粒度控制，并且易于回滚事务。

虽然NoSQL数据库也可以使用事务操作，但稳定性方面没法和关系型数据库比较，所以它们真正闪亮的价值是在操作的扩展性和大数据量处理方面。

一、关系型数据库

mysql、sqlserver、sqlite

关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织
优点：
1、易于维护：都是使用表结构，格式一致；
2、使用方便：SQL语言通用，可用于复杂查询；
3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
缺点：
1、读写性能比较差，尤其是海量数据的高效率读写；
2、固定的表结构，灵活度稍欠；
3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

二、非关系型数据库

mangodb、redis、hbase

非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。
优点：
1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
3、高扩展性；
4、成本低：nosql数据库部署简单，基本都是开源软件。

缺点：
1、不提供sql支持，学习和使用成本较高；
2、无事务处理；
3、数据结构相对复杂，复杂查询方面稍欠。

非关系型数据库的分类和比较：
1、文档型——mangodb
2、key-value型——redis
3、列式数据库——hbase
4、图形数据库

MYSQL知识点

1、mysql的引擎

可在mysql中的conf中修改默认引擎，或可以mysql中修改数据表的引擎

存储引擎	MyISAM	InnoDB	Memory	Merge
存储限制	无	64TB	无	无
锁机制	表锁	行锁	表锁	表锁
事务支持	X	√	X	X
外键约束	X	√	X	X
B树索引	√	√	√	√
哈希索引	X	X	√	X
全文索引	√	X	X	X

2、mysql的索引

概念综述

索引是帮助MySql高效获取数据的数据结构，使用索引可以不用扫描全表来定位查询的数据，而是通过索引表找到该行数据对应的物理地址然后访问相应的数据，在数据非常庞大的时候，索引可以加快查询速度。

索引的原理

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引等等。

索引的分类

常见的5中索引类型：主键索引、唯一索引、普通索引、全文索引、组合索引

（以下命令实例中，引号内为需要设定的值）

（1）主键索引：即主键，该索引在添加主键时自动创建，不允许重复，不允许为空

修改命令：ALTER TABLE 'table_name' ADD PRIMARY KEY ('columnname')

（2）唯一索引：用来建立索引的列的值必须是唯一的，允许为空

创建命令：create unique index 'index_name'  on 'table_name' ('column(length)')

修改命令：ALTER TABLE 'table_name' ADD INEDX 'index_name' ('column(length)')

删除命令：DROP INDEX  'index_name' on 'table_name' 

（3）普通索引：用表中的普通列构建索引，没有限制

创建命令：create index 'index_name'  on 'table_name' ('column(length)')

修改命令：ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column(length)')

（4）全文索引：用大文本对象的列构建的索引

创建命令：ALTER TABLE 'table_name' ADD FULLTEXT INDEX 'index_name' ('column(length)')；

注意： 目前全文索引只应用于MyISAM和InnoDB

    全文索引只支持英文，不支持中文

（5）组合索引：用多个列组合构建索引，这多个列中的值不允许为空

创建命令：ALTER TABLE 'table_name' ADD INDEX 'index_name' ('col1','col2','col3')；

注意： 组合索引遵循最左前缀原则，把最常用的作为检索或排序的列放到最左，依次递减；

    当使用组合索引时很容易因为索引key过大，导致效率降低，所以可以采用只使用组合字段的前几位作为索引拼接，命令更新为('col1(3)','col2(4)','col3(3)')：即分别选择3个、4个、3个字段做为索引字段进行拼接。

具体请参照博文：https://blog.csdn.net/justry_deng/article/details/81458470

索引的优缺点

（1）优点

• • 减少I/O次数，加快检索速度
  • 减少存储引擎需要扫描的数据量，加快检索速度
  • 根据索引的分组和排序，可以加快分组排序

（2）缺点

• • 占用空间大，一般索引表是数据表空间的1.5倍
  • 数据量越大，索引表的维护和创建成本越大
  • 降低数据表修改操作的效率

索引的使用场景

（1）建议使用场景

• • 主键自动建立唯一索引
  • 经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引
  • 作为排序的列要建立索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 高并发条件下倾向组合索引
  • 用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

（2）不建议使用场景

• • 经常增删改的列不要建立索引
  • 有大量重复的列不建立索引

  • 表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

3、mysql的事务

概念综述

事务是数据库关系系统执行过程中的一个逻辑单位，其是在引擎层实现的功能，InnoDB支持事务，但MyISAM不支持事务。事务具有原子性、一致性、隔离性、持久性四大特点

事务四大特性（ACID）原子性、一致性、隔离性、持久性

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离性详解

可参考这篇博客：http://blog.itpub.net/31559358/viewspace-2221931/

【1】在介绍个理性以前先对事务操作可能出现对问题进行介绍：

（1）脏读：简单来说，就是一个事务在处理过程中读取了另外一个事务未提交的数据。这种未提交的数据我们称之为脏数据。依据脏数据所做的操作肯能是不正确的

（2）不可重复度：是指一个事务范围内，多次查询某个数据，却得到不同的结果。在第一个事务中的两次读取数据之间，由于第二个事务的修改，第一个事务两次读到的数据可能就是不一样的。

（3）幻读：是事务非独立执行时发生的一种现象。例如事务 T1 对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务 T2 又对这个表中插入了一行数据项为“1”的数据，并且提交给数据库。

而操作事务 T1 的用户如果再查看刚刚修改的数据，会发现数据怎么还是 1？其实这行是从事务 T2 中添加的，就好像产生幻觉一样，这就是发生了幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

【2】事务的隔离级别

（1）Serializable（串行化）：完全隔离，但效率很低

（2）Repeatable red（可重复读，mysql默认级别）：可避免脏读、不可重复读、但可能有幻读

（3）Read committed（读已提交）：可以避免脏读

（4）Read uncommitted（读未提交）：无法避免

* 读未提交：另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据脏读
*不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。
*可重复读：在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象
*串行化：最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样。

读未提交（Read uncommitted），就是一个事务能够看到其他事务尚未提交的修改，允许脏读出现.
读已提交（Read committed），事务能够看到的数据都是其他事务已经提交的修改，也就是保证不会看到任何中间性状态，当然脏读也不会出现。
可重复读（Repeatable reads），保证同一个事务中多次读取的数据是一致的，这是 MySQL InnoDB 引擎的默认隔离级别，
串行化（Serializable），并发事务之间是串行化的，通常意味着读取需要获取共享读锁，更新需要获取排他写锁，如果 SQL 使用 WHERE 语句，还会获取区间锁（MySQL 以 GAP 锁形式实现，可重复读级别中默认也会使用），这是最高的隔离级别。

事务的实现

【1】成功提交事务

• • start transaction;      #手动开启事务
  • DML语句;                 #数据库读增、删、改操作
  • commit;                    #commit之后就可以开播底层数据库中读数据

执行外以上步骤后，数据读修改更新到mysql中

【2】失败提交事务

• • start transaction;      #手动开启事务
  • DML语句;                 #数据库读增、删、改操作
  • rollback;                     #rollback之后，修改读数据不会更新到数据库中

执行外以上步骤后，数据读修改不会更新到mysql中

Mysql事务与Redis事务的区别

Mysql事务具有原子性，Redis的事务不具有原子性，该部分的区别主要体现在提交事务失败时

（1）Mysql命令

• • start transaction;      #手动开启事务
  • DML语句;                 #数据库读增、删、改操作
  • rollback;                     #rollback之后，修改读数据不会更新到数据库中

如果已经执行了2条语句，第3条语句失败，执行rollback，前两条语句的影响也随之消失，即事务原子性：要么全部成功，要么全部失败

（2）Redis命令

• • multi;                        #手动开启事务
  • DML语句;                 #数据库读增、删、改操作
  • discard;                    #结束事务，并清除commands队列

如果已经执行了2条语句，第3条语句失败，执行discard，前两条语句的影响不会消失，即事务不具有原子性

4、Mysql的主从复制原理

应用场景

（1）主从服务器实现读写分离，从服务器实现负载均衡：主服务器负责数据新增、删除和修改，承担部分或不承担数据查询工作，从服务其只负责数据查询工作

（2）从服务器作为主服务器的实时数据备份：在主服务器宕机的时候实现快速恢复数据

（3）把多个从服务器根据业务重要性进行拆分访问

复制类型

（1）基于语句的复制：在Master上执行的SQL语句复制到Slave上重新执行一遍，该方式是Mysql的默认复制方式，效率高；一旦发现没有精确复制时，会自动选择基于行的复制

（2）基于行的复制：把改变的内容复制过去，二不是将Master中的命令重新执行一遍，Mysql5.0以后的版本开始支持

（3）混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的复制无法精确复制时，就采用基于行的复制

解决问题

（1）数据分布

（2）负载均衡

（3）数据备份

（4）高可用性与容错行

（5）实现读写分离，缓解数据库压力

实现过程

（1）Master服务其将操作语句存储到二进制的binlog日志中，然后授予Slave远程连接的权限（Master一定要开启binlog的二进制的日志功能，在my.cnf文件中对该参数进行设计，当该参数为'0'时，数据库的性能最好，但不会对操作进行备份；当该参数为‘1’时，每发生一次改变，都会将相关操作记录到binlog文件中，数据安全性达到最高，但数据库的性能最差）

（2）Slave服务器在一定时间间隔内对Master对binlog文件进行探测，如果发生改变，就开启一个IO线程请求Master的binlog文件，Master会开启一个IO线程将binlog文件的内容同步到Slave服务器上的中继日志relaylog中

（3）Slave开启SQL线程，对中继日志中的sql命令进行重放，完成主从同步

注意事项

（1）binlog文件中只记录更改数据库的操作，对于select和show的查询语句不与记录

（2）实现主从复制必须开启my.cnf文件中的binlog功能

（3）Master服务器开启一个IO线程，Slave开启一个IO线程，开启一个SQL线程

【参考部署】https://www.cnblogs.com/brianzhu/p/10154446.html

5、Mysql的数据备份

【1】数据备份基础操作

（1）进入mysql的bin目录：/home/map/mysql/bin

（2）官方命令格式：./mysqldump --no-defaults -h 'ip' -P 'port'  -u'user' -proot -database 'db_name' > test.sql

  执行以上命令会报错：mysqldump: unknown option '-b'

（3）修改命令：./mysqldump --no-defaults -h 'ip' -P 'port'  -u'user' -proot 'db_name' > test.sql

其他备份数据命令见连接：https://www.cnblogs.com/Cherie/p/3309456.html

（4）将test.sql迁移到另外一台机器上执行，即可以完成数据备份，如果是海量数据，需要考虑备份过程的性能问题，可以进行如下调整

• • 将已有的索引删除，完成数据备份后，再将索引添加到新的数据库内
  • 将InnoDB修改为MyISAM，因为数据量大的情况下，MyISAM的插入速度明显高于InnoDB
  • 保证insert语句是多值写法

mysql常问问题

1、MySQL常见的存储引擎InnoDB、MyISAM的区别？

1）事务：MyISAM不支持，InnoDB支持
2）锁级别： MyISAM 表级锁，InnoDB 行级锁及外键约束
（MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。什么意思呢，就是说对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。
InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。当锁定的场景中不涉及Innodb的时候，InnoDB是检测不到的。只能依靠锁定超时来解决。）
3）MyISAM存储表的总行数；InnoDB不存储总行数；
4）MyISAM采用非聚集索引，B+树叶子存储指向数据文件的指针。InnoDB主键索引采用聚集索引，B+树叶子存储数据

适用场景：
MyISAM适合： 插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择， 没有事务。适合读。
InnoDB适合： 可靠性要求比较高，或者要求事务； 表更新和查询都相当的频繁， 大量的INSERT或UPDATE。适合写。 

主要 MyISAM 与 InnoDB 两个引擎，其主要区别如下：

一、InnoDB 支持事务，MyISAM 不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而 MyISAM就不可以了；

二、MyISAM 适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用；

三、InnoDB 支持外键，MyISAM 不支持；

四、MyISAM 是默认引擎，InnoDB 需要指定；

五、InnoDB 不支持 FULLTEXT 类型的索引；

六、InnoDB 中不保存表的行数，如 select count(*) from table 时，InnoDB；需要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。注意的是，当 count(*)语句包含 where 条件时 MyISAM 也需要扫描整个表；

七、对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM表中可以和其他字段一起建立联合索引；

八、清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表；

九、InnoDB 支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

2、数据库三范式，根据某个场景设计数据表？优缺点

1）所有字段值都是不可分解的原子值。
2）在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
3）数据表中的每一列数据都和主键直接相关，而不能间接相关。

第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。

第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说
在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。

第三范式(确保每列都和主键列直接相关,而不是间接相关)
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

优点：可以尽量得减少数据冗余 缺点:对于查询需要多个表进行关联，更难进行索引优化
反范式化: 优点:可以减少表得关联 缺点:数据冗余以及数据异常
 

3、SQL怎么优化

SQL优化是一个分析，优化，再分析，再优化的过程。站在执行计划的角度来说，我们这个过程，就是在不断的减少rows的数量。

1.建索引
2.减少表之间的关联
3.优化 sql，尽量让 sql 很快定位数据，不要让sql 做全表查询，应该走索引,把数据 量大的表排在前面
4.简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据
5.尽量用PreparedStatement 来查询，不要用 Statement

不要在列上使用函数和进行运算
不要在列上使用函数，这将导致索引失效而进行全表扫描。
尽量避免使用 != 或 not in或 <> 等否定操作符
尽量避免使用 or 来连接条件
多个单列索引并不是最佳选择，复合索引的最左前缀原则
查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。
索引不会包含有NULL值的列
当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。
like 语句的索引失效问题

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析

4、如何做 MySQL 的性能优化？

*为搜索字段创建索引。
*避免使用 select *，列出需要查询的字段。
*垂直分割分表。
*选择正确的存储引擎。

1、慢查询
2、索引
3、拆分表
实践中如何优化MySQL

1. 优化索引、SQL 语句、分析慢查询；

2. 设计表的时候严格根据数据库的设计范式来设计数据库；（数据库表结构优化）

3. 使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能

节约磁盘IO；

4. 优化硬件；采用SSD，使用磁盘队列技术(RAID0,RAID1,RDID5)等；

5. 采用MySQL 内部自带的表分区技术，把数据分层不同的文件，能够提高磁

盘的读取效率；

6. 垂直分表；把一些不经常读的数据放在一张表里，节约磁盘I/O；

7. 主从分离读写；采用主从复制把数据库的读操作和写入操作分离开来；

8. 分库分表分机器（数据量特别大），主要的的原理就是数据路由；

9. 选择合适的表引擎，参数上的优化；

10. 进行架构级别的缓存，静态化和分布式；

11. 不采用全文索引；

12. 采用更快的存储方式，例如 NoSQL存储经常访问的数据

5、数据库悲观锁和乐观锁的原理和应用场景？

悲观锁，先获取锁，再进行业务操作，一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据。
当数据库执行select … for update时会获取被select中的数据行的行锁，select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

乐观锁则与，先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过。Java 并发包中的 AtomicFieldUpdater 类似，也是利用 CAS 机制，并不会对数据加锁，而是通过对比数据的时间戳或者版本号，来实现乐观锁需要的版本判断。

6、索引是什么？MySQL为什么使用B+树，而不是使用其他？B+树的特点

索引是帮助MySQL高效获取数据的数据结构。索引：排好序的快速查找数据结构！索引会影响where后面的查找，和order by 后面的排序。
B+Tree索引（平衡多路查找树）
是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。B-Tree需要获取所有节点，相比之下B+Tree效率更高。B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问;
在这里插入图片描述
hash：虽然可以快速定位，但是没有顺序，IO复杂度高。
二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
红黑树：树的高度随着数据量增加而增加，IO代价高。

为什么说B±tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？
B+tree的磁盘读写代价更低，B+tree的查询效率更加稳定
数据库索引采用B+树而不是B树的主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。
B+树的特点：
（1）所有关键字都出现在叶子结点的链表中(稠密索引)，且链表中的关键字恰好是有序的;
（2）不可能在非叶子结点命中;
（3）非叶子结点相当于是叶子结点的索引(稀疏索引)，叶子结点相当于是存储(关键字)数据的数据层;

7、创建索引时需要注意什么？

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；

取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；

索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。
唯一、不为空、经常被查询的字段 的字段适合建索引

8、CHAR和VARCHAR的区别？

1）char的长度是不可变的，用空格填充到指定长度大小，而varchar的长度是可变的。
2）char的存取数度还是要比varchar要快得多
3）char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。
 

9、MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？

a. 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
b. 选择合适的表字段数据类型和存储引擎，适当的添加索引。
c. mysql库主从读写分离。
d. 找规律分表，减少单表中的数据量提高查询速度。
e。添加缓存机制，比如memcached，apc等。
f. 不经常改动的页面，生成静态页面。
g. 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

10、各种索引的概念：索引，主键，唯一索引，联合索引，索引分类

索引分类： Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引.{按聚集分类：聚集索引和非聚集索引}
1）索引（ 普通索引）：这是最基本的MySQL数据库索引，它没有任何限制
2）唯一索引：唯一索引可以保证数据记录的唯一性，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。唯一索引允许空值（ 索引列的所有值都只能出现一次，即必须唯一）。它与前面的普通索引类似，不同的就是：MySQL数据库索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
3）主键：也简称主键。它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。
4）外键：表的外键是另一表的主键,
5）组合索引：索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。
CREATE INDEX index_name ON table_name (column_list)
全文索引： 全文索引的索引类型为FULLTEXT， 可以在VARCHAR或者TEXT类型的列上创建。
单列索引与多列索引
建立索引的使用场景

在最频繁使用的、用以缩小查询范围的字段,需要排序的字段上建立索引。
不宜：
1）对于查询中很少涉及的列或者重复值比较多的列
2）对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

11、说一说drop、delete与truncate的区别

drop、delete、truncate都表示删除，但是三者有一些差别：
Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除。会触发这个表上所有的delete触发器
Truncate删除表中的所有数据（结构保留），这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小；
Drop命令从数据库中删除表（不保留结构），所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

12、什么叫视图？游标是什么？

视图是一种虚拟的表，通常是有一个表或者多个表的行或列的子集，具有和物理表相同的功能
游标是对查询出来的结果集作为一个单元来有效的处理。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。
什么是存储过程？用什么来调用？

存储过程是一个预编译的 SQL 语句， 使用存储过程比单纯 SQL 语句执行要快。
调用： 1）可以用一个命令对象来调用存储过程。 2）可以供外部程序调用，比如： java 程序

13、内连接、自连接、外连接（左、右、全）、交叉连接的区别

内连接：只有两个元素表相匹配的才能在结果集中显示。
外连接：

左外连接:   左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
右外连接:   右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
全外连接：连接的表中不匹配的数据全部会显示出来。
交叉连接： 笛卡尔效应，显示的结果是链接表数的乘积。
详见博文：https://blog.csdn.net/plg17/article/details/78758593

14、MVCC

全称是Multi-Version Concurrent Control，即多版本并发控制，在MVCC协议下，每个读操作会看到一个一致性的snapshot，并且可以实现非阻塞的读。MVCC允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务ID，在同一个时间点，不同的事务看到的数据是不同的。

       MVCC是为了解决什么问题?

    大多数的MYSQL事务型存储引擎,如,InnoDB，Falcon以及PBXT都不使用一种简单的行锁机制.事实上,他们都和MVCC–多版本并发控制来一起使用.
    大家都应该知道,锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销. 

MySQL的innodb引擎是如何实现MVCC的

innodb会为每一行添加两个字段，分别表示该行创建的版本和删除的版本，填入的是事务的版本号，这个版本号随着事务的创建不断递增。在repeated read的隔离级别（事务的隔离级别请看这篇文章）下，具体各种数据库操作的实现：

• select：满足以下两个条件innodb会返回该行数据：
  • 该行的创建版本号小于等于当前版本号，用于保证在select操作之前所有的操作已经执行落地。
  • 该行的删除版本号大于当前版本或者为空。删除版本号大于当前版本意味着有一个并发事务将该行删除了。
• insert：将新插入的行的创建版本号设置为当前系统的版本号。
• delete：将要删除的行的删除版本号设置为当前系统的版本号。
• update：不执行原地update，而是转换成insert + delete。将旧行的删除版本号设置为当前版本号，并将新行insert同时设置创建版本号为当前版本号。

其中，写操作（insert、delete和update）执行时，需要将系统版本号递增。

​ 由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。

通过MVCC很好的实现了事务的隔离性，可以达到repeated read级别，要实现serializable还必须加锁。

详见博文：https://blog.csdn.net/whoamiyang/article/details/51901888?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task