事务是什么？

事务是指满足ACID特性的一组操作，可以通过commit提交一个事务，通过rollback来回滚一个事务

数据库事务有哪些特性？

• 原子性（Atomicity）

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。
回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

• 一致性（Consistency ）

举例：转账前后，总金额一致， 数据是满足完整性约束的，数据在事务的前后，业务整体一致

• 隔离性（Isolation）

一个事务所做的修改在最终提交以前，对其它事务是不可见的。

• 持久性（Durability）

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。使用重做日志来保证持久性。

非关系型数据库和关系型数据库的区别？

关系型数据库：

指采用了关系模型来组织数据的数据库。简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

优点：

容易理解，二维表的结构非常贴近现实世界，二维表格，容易理解。
使用方便，通用的sql语句使得操作关系型数据库非常方便。
易于维护，数据库的ACID属性，大大降低了数据冗余和数据不一致的概率。

缺点：

海量数据的读写效率。对于网站的并发量高，往往达到每秒上万次的请求，对于传统关系型数据库来说，硬盘I/o是一个很大的挑战。
高扩展性和可用性。在基于web的结构中，数据库是最难以横向拓展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库没有办法像web Server那样简单的通过添加更多的硬件和服务节点来拓展性能和负载能力。

非关系型数据库

主要指那些非关系型的、分布式的，且一般不保证ACID的数据存储系统，主要代表MongoDB，Redis、CouchDB。

NoSQL提出了另一种理念，以键值来存储，且结构不稳定，每一个元组都可以有不一样的字段，这种就不会局限于固定的结构，可以减少一些时间和空间的开销。使用这种方式，为了获取用户的不同信息，不需要像关系型数据库中，需要进行多表查询。仅仅需要根据key来取出对应的value值即可。

分类：

面向高性能并发读写的key-value数据库：
主要特点是具有极高的并发读写性能，例如Redis、Tokyo Cabint等。

面向海量数据访问的面向文档数据库：
特点是，可以在海量的数据库快速的查询数据。例如MongoDB以及CouchDB.

面向可拓展的分布式数据库：
解决的主要问题是传统数据库的扩展性上的缺陷。

缺点：

但是由于Nosql约束少，所以也不能够像sql那样提供where字段属性的查询。因此适合存储较为简单的数据。

对比：

1. 存储上：关系型数据库以数据表的形式存储。非关系型采用key-value形式。

2. 事务：SQL中如果多张表需要同批次被更新，即如果其中一张表更新失败的话，其他表也不会更新成功。这种场景可以通过事务来控制，可以在所有命令完成之后，再统一提交事务。在Nosql中没有事务这个概念，每一个数据集都是原子级别的。

3. 数据表 VS 数据集：关系型是表格型的，存储在数据表的行和列中。彼此关联，容易提取。而非关系型是大块存储在一起。

4. 预定义结构 VS 动态结构：

   在sql中，必须定义好地段和表结构之后，才能够添加数据，例如定义表的主键、索引、外键等。表结构可以在定义之后更新，但是如果有比较大的结构变更，就会变的比较复杂。

   在Nosql数据库中，数据可以在任何时候任何地方添加。不需要预先定义。

5. 存储规范 VS 存储代码：

关系型数据库为了规范性，把数据分配成为最小的逻辑表来存储避免重复，获得精简的空间利用。但是多个表之间的关系限制，多表管理就有点复杂。

当然精简的存储可以节约宝贵的数据存储，但是现在随着社会的发展，磁盘上付出的代价是微不足知道的。

非关系型是平面数据集合中，数据经常可以重复，单个数据库很少被分开，而是存储成为一个整体，这种整块读取数据效率更高。

6. 纵向拓展 VS 横向拓展：

为了支持更多的并发量，SQL数据采用纵向扩展，提高处理能力，通过提高计算机性能来提高处理能力。

NoSql通过横向拓展，非关系型数据库天然是分布式的，所以可以通过集群来实现负载均衡。

数据库的隔离级别有哪些？有什么区别？

• 未提交读（READ UNCOMMITTED）
  事务中的修改，即使没有提交，对其它事务也是可见的

• 提交读（READ COMMITTED）

一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。

• 可重复读（REPEATABLE READ）

保证在同一个事务中多次读取同样数据的结果是一样的。

• 可串行化（SERIALIZABLE）

强制事务串行执行。需要加锁实现，而其它隔离级别通常不需要。

数据库的隔离级别

如何加快数据库查询速度？

处理百万级以上的数据提高查询速度的方法：

1. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

3. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

selectid from t where num=0

4. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10
union all
select id from t where num=20

5. 下面的查询也将导致全表扫描：(不能前置百分号)

select id from t where name like ‘%abc%’

若要提高效率，可以考虑全文检索。

6. in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between就不要用 in 了：

select id from t where num between 1 and 3

7. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

8. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’–name以abc开头的id

select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

--应改为:
select id from t where name like ‘abc%’

select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

9. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

10. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。

11. 不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)

12. 很多时候用 exists 代替 in 是一个好的选择：

selectnum from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

13. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

14. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

15. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

16. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

17. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

18. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

19. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

20. 避免频繁创建和删除临时表，以减少系统表资源的消耗。

21. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使 用导出表。

22. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

23. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

24. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

25. 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

26. 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

27. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

28. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

29. 尽量避免大事务操作，提高系统并发能力。

聚集索引和非聚集索引的区别？（主键索引和非主键索引）

1. 聚集索引（聚簇索引）（主键索引）：

数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

主索引的叶子节点 data 域记录着完整的数据记录。

聚簇索引

2. 非聚集（unclustered）索引（非主键索引）:

该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找

什么时候不该使用索引？

对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；

对于中到大型的表，索引就非常有效；

但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术 。

索引底层的数据结构是什么？B+树的结构是怎么样的？

B+Tree

• 首先B+Tree是从最早的平衡二叉树演化过来的。
• 每个节点中的键值是有序的
• 平衡二叉树每个节点的出度为2，m阶B+Tree的出度最大为m。m的大小取决于磁盘页的大小
• B+Tree的非叶子节点存储键值信息，即表中记录的主键，以及子节点的指针信息。
• 数据记录都存放在叶子节点中。
• 每个节点占用存储引擎的一个页。
• 所有叶子节点之间都有一个链指针。因此对B+Tree可以进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
• B+Tree相对于平衡二叉树而言，增大了节点的出度，减少了树的高度，缩减了查询时间。而且B+Tree在一个节点中存储了多个键值，相比于平衡二叉树减少了节点的个数。
• 数据库引擎在把磁盘数据读入到内存时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

b加树与b树的区别是什么？

• b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”；
• b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）；
• 对于范围查找和元素遍历来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历
• B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
• B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低
• B+树的查询效率更加稳定
• 增删文件（节点）时，效率更高

索引优化方法有哪些？

独立的列

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5; 在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

多列索引

1. 在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把actor_id 和 film_id 设置为多列索引。

2. SELECT film_id, actor_ id FROM sakila.film_actor WHERE actor_id = 1 AND film_id = 1;

索引列的顺序

1. 让选择性最强的索引列放在前面。

2. 索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

3. 例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

前缀索引

1. 对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

2. 前缀长度的选取需要根据索引选择性来确定

覆盖索引

1. 覆盖索引会直接在索引表中进行查询而不会访问原始数据。加快查询效率

2. 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。

3. 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

4. 所有需要查询的字段信息都包含在了索引里面

5. 查询字段中包含了索引列

优点：

简单说就是，select 到 from 之间查询的列 <=使用的 索引列+主键

数据库的四种连接方式是什么？

1. 内连接。 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行

2. 左连接。 左表的挨个信息去查询,查不到则将右边控制为null进行显示

3. 右连接。 右表的挨个信息去查询,查不到则将左边控制为null进行显示

4. 完全连接。 交叉联接返回左表中的所有行，左表中的每一行与右表中匹配的所有行组合。交叉联接也称作笛卡尔积。

数据库并发一致性问题有哪些？

丢失修改

T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

读脏数据
T1修改了一个数据，T2随后读取了这个数据。如果T1撤销这次修改，那么T2读取的数据是脏数据。

不可重复读

T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

幻影读
T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

可串行化调度如何实现？

通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。

事务遵循两段锁协议是保证可串行化调度的充分条件。

两段锁协议

加锁和解锁分为两个阶段进行。

关系型数据库设计三范式是什么？

第一范式：属性不可分

第二范式：非主属性完全函数依赖于主属性

第三范式：非主属性不传递函数依赖于主属性

数据库存储引擎有哪些？

1. InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ 间隙锁（Next-Key Locking）防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取

2. MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

3. 比较

事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
外键：InnoDB 支持外键。
备份：InnoDB 支持在线热备份。
崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
其它特性：MyISAM 支持压缩表和空间数据索引。

4. Memory引擎

用来存储只读数据，读取速度要比MyISAM更快

对(a,b,c)加索引，查询ab、ac、bc有没有用？

mysql中关于关联索引的问题——对a,b,c三个字段建立联合索引，那么查询时使用其中的2个作为查询条件，是否还会走索引？

对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，该字段及其后面的索引字段都无法被使用索引(即使用的索引字段仅为前面的部分字段)

mysql索引相关操作的语句介绍一下？

ALTER TABLE：ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

CREATE INDEX：CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

删除索引：

DROP INDEX index_name ON table_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

查看索引

mysql> show index from tblname;

mysql> show keys from tblname;

explain+select可以查看是否使用索引

force index可以强制使用某个索引

where和having的异同？

用的地方不一样

where可以用于select、update、delete和insert into values(select * from table where ..)语句中。

having只能用于select语句中

执行的顺序不一样

where的搜索条件是在执行语句进行分组之前应用

having的搜索条件是在分组条件后执行的

where 早于 group by 早于 having

即如果where和having一起用时，where会先执行，having后执行

子句有区别

having子句可以用集合函数（sum、count、avg、max和min），而where子句不可以

总结：

WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。

GROUP BY 子句用来分组 WHERE 子句的输出。

HAVING 子句用来从分组的结果中筛选行

sql select语句的执行顺序是什么？

1. from 子句组装来自不同数据源的数据；

2. where 子句基于指定的条件对记录行进行筛选；

3. group by 子句将数据划分为多个分组；

4. 使用聚集函数进行计算；

5. 使用 having 子句筛选分组；

6. 计算所有的表达式；

7. select 的字段；

8. 使用 order by 对结果集进行排序。

mysql有几种锁？怎么实现？

表级锁定：

• 表独占写锁（Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

• 表共享读锁（Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

• MySQL各存储引擎中最大颗粒度的锁定机制

• 现逻辑非常简单，带来的系统负面影响最小，获取锁和释放锁的速度很快，可以很好的避免困扰我们的死锁问题，但是系统资源争用的概率最高，并发能力差。

• 主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎使用

• MyISAM的表级锁定完全是由MySQL提供的表级锁定实现：

• MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁

行级锁定：

• InnoDB的行级锁定同样分为两种类型，共享锁和排他锁。

• 在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

• 意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。

锁之间的兼容性

• 事务可以通过以下语句显示给记录集加共享锁或排他锁。

InnoDB行锁实现方式

间隙锁（Next-Key锁）：

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；

为了满足其恢复和复制的需要。

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。

对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

目的：

锁定对象的颗粒度很小，发生锁定资源争用的概率也最小，能够给予程序尽可能大的并发能力。但是由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。

此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎。

行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的

页级锁定：

特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。

另外，页级锁定和行级锁定一样，会发生死锁。

使用页级锁定的主要是BerkeleyDB存储引擎。

总结：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

mysql mvcc是什么？如何实现？

mvcc是指多版本并发控制 ，是MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

版本号：

• 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增

• 事务版本号：事务开始时的系统版本号。

隐藏的列 ：

• 创建版本号：指示创建一个数据行的快照时的系统版本号；

• 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

• MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

Undo日志是什么？如何实现？

• MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。

• 当对某个记录进行更新时，会将当前记录写入 undo log 中，并更新当前记录中 DB_ROLL_PTR 字段值，使其指向刚才的 undo log record，然后更新当前记录相关字段值，同时更新 DB_TRX_ID 字段，记录执行更新操作的事务 ID。

实现过程：

将当前事务版本号作为数据行快照的删除版本号。

将当前事务版本号作为更新前的数据行快照的删除版本号，并将当前事务版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。

将当前事务版本号作为数据行快照的创建版本号。

多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在最近的一个有效快照。(可以通过回滚指针找到历史版本)（当然，只是活跃的事务，如果当前记录没有相关事务在操作，则会清理 undo log，就不能拿到历史版本数据了）

事务所要读取的数据行快照的创建版本号必须小于该事务的事务版本号。

事务读取的数据行的删除版本号必须为未指定或者大于当前事务的版本号

满足上述要求的记录才能被事务查出来

查询：将当前事务版本号作为数据行快照的创建版本号。

插入：将当前事务版本号作为数据行快照的创建版本号。

更新：将当前事务版本号作为更新前的数据行快照的删除版本号，并将当前事务版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。

删除：将当前事务版本号作为数据行快照的删除版本号

回滚实现：

利用undo 日志，当需要进行回滚的时候，使用回滚指针来反过来执行指令就能将数据库回滚。

mysql不同存储引擎的特点及它们各自的应用场景是什么？

MyISAM

MyISAM 引擎读取速度较快，占用资源相对较少，不支持事务，不支持外键约束，但支持全文索引；

使用表级锁，读写互相阻塞，也就是说读数据的时候你就不能写数据，写数据的时候你就不能读数据；

MyISAM 引擎只能缓存索引，而不能缓存数据。

适用场景：

不需要事务支持的业务

适用于读数据比较多的业务，不适用于读写频繁的业务

并发相对较低、数据修改相对较少的业务

硬件资源比较差的机器可以考虑使用 MyISAM 引擎

InnoDB

事务型数据库的首选引擎，支持事务安全表，支持行锁定和外键

具有提交、回滚和崩溃恢复能力的事务安全存储引擎，能处理巨大数据量，性能及效率高，完全支持外键完整性约束;

具有非常高效的缓存特性，能缓存索引也能缓存数据，对硬件要求比较高;

使用场景：

需要事务支持的业务、高并发的业务

数据更新较为频繁的场景，比如 BBS、SNS、微博等

数据一致性要求较高的业务，比如充值转账、银行卡转账

Innodb是如何实现事务的? TODO

为什么用 B+ 树做索引而不用哈希表做索引?

哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。

如果我们要进行范围查找，例如查找ID为100 ~ 400的人，哈希表同样不支持，只能遍历全表。

索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。

哈希表中存储记录行的物理位置

哈希索引在进行等值查询的时候速度较快，但是无法进行范围查询，不支持使用索引排序，不支持模糊查询等

适用哈希表作为索引的时候，需要将整个哈希表加载进内存中，一次性占用的内存空间较大，但是B+树可以按照节点来进行加载，按序加载，不用一次性占用较大的内存

为什么索引不使用红黑树？

在大规模数据存储的时候，红黑树由于树的深度过大，查询效率较低而且磁盘IO读写比较频繁，进而导致效率较低

通过B+树，可以有效地提高查询效率和磁盘读写效率，查询效率更加稳定

为什么建议使用主键自增的索引？

如果我们的主键是自增的，每次插入的 ID 都会比前面的大，那么我们每次只需要在后面插入就行， 不需要对叶子节点进行移动或者页分裂等操作，这样可以提高性能。也就是为什么建议使用主键自增的索引。

redo log是什么？作用和实现介绍一下？

重做日志

用来记录数据页上进行了什么修改

在 MySQL 中，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就采用了日志（redo log）来提升更新效率

而日志和磁盘配合的整个过程，其实就是 MySQL 里的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

在同一个事务中，每当数据库进行修改数据操作时，将修改结果更新到内存后，会在redo log添加一行记录记录“需要在哪个数据页上做什么修改”，并将该记录状态置为prepare，等到commit提交事务后，会将此次事务中在redo log添加的记录的状态都置为commit状态，之后在适当的时候（如系统空闲时）将修改落盘时，会将redo log中状态为commit的记录的修改都写入磁盘

redolog采用循环写的方式记录，当写到结尾时，会回到开头循环写日志

redolog的大小是固定的，在mysql中可以通过修改配置参数innodb_log_files_in_group和innodb_log_file_size配置日志文件数量和每个日志文件大小

参数

- `innodb_log_file_size`：指定每个redo日志大小，默认值48MB

- `innodb_log_files_in_group`：指定日志文件组中redo日志文件数量，默认为2

- `innodb_log_group_home_dir`：指定日志文件组所在路劲，默认值`./`，指mysql的数据目录datadir

- `innodb_mirrored_log_groups`：指定日志镜像文件组的数量，默认为1，此功能属于未实现的功能，在5.6版本中废弃，在5.7版本中删除了。

redo log也是需要写入磁盘的，但是它是顺序IO，比起直接将内存的脏页写到磁盘的随机IO要快很多

重做日志

write pos表示日志当前记录的位置，当ib_logfile_4写满后，会从ib_logfile_1从头开始记录；

check point表示将日志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日志上的相关记录擦除掉，即write pos->checkpoint之间的部分是redo log空着的部分，用于记录新的记录，checkpoint->write pos之间是redo log待落盘的数据修改记录。

当writepos追上checkpoint时，得先停下记录，先推动checkpoint向前移动，空出位置记录新的日志。

有了redo log，当数据库发生宕机重启后，可通过redo log将未落盘的数据恢复，即保证已经提交的事务记录不会丢失。

特点

• redo log的大小是固定的，日志上的记录修改落盘后，日志会被覆盖掉，无法用于数据回滚/数据恢复等操作。

• redo log是innodb引擎层实现的，并不是所有引擎都有。

• 重做日志都是以512字节进行存储的，称之为重做日志块，与磁盘扇区大小一致，这意味着重做日志的写入可以保证原子性，不需要doublewrite技术

innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

binlog是什么？作用和实现介绍一下？

特点

binlog记录了数据库表结构和表数据变更

binlog是server层实现的，意味着所有引擎都可以使用binlog日志

binlog通过追加的方式写入的，可通过配置参数max_binlog_size设置每个binlog文件的大小，当文件大小大于给定值后，日志会发生滚动，之后的日志记录到新的文件上。

binlog有两种记录模式，statement格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

备注: 每个事务 binlog 的末尾，会记录一个 XID event，标志着事务是否提交成功，也就是说，recovery 过程中，binlog 最后一个 XID event 之后的内容都应该被 放弃。

sync_binlog：设置为1，表示每次事务的binlog都直接持久化到磁盘（注意是这里指的是binlog日志本身落盘），保证mysql重启后binlog记录是完整的。

show variables like 'sync_binlog';

对于事务引擎，每次事务提交的时候，都会写binlog

对于非事务引擎，每条SQL语句都会写到binlog

作用

• 数据复制：数据库的数据出问题以后，可以通过binlog来对数据进行恢复

• 数据恢复：在主服务器和从服务器间进行数据复制，保持数据的一致性

区别：

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

redo log 是物理日志，记录的是在某个数据页上做了什么修改；binlog 是逻辑日志，记录的是这个语句的原始逻辑。

redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。追加写是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

redo log事务开始的时候，就开始记录每次的变更信息，而binlog是在事务提交的时候才记录。

MySQL需要保证redo log和binlog的数据是一致的，如果不一致，主从服务器间的数据就会不一致了

录入格式：

statement：综合了上述两者，没有函数时，使用statement，有函数时使用row，但是对于系统变量仍然会出现主从不一致

row：用来记录发生改变的行以及对应的改变，但是存在效率问题，可能一条SQL能设置完的数据，需要一条一条遍历整个表才能做完

mixed：用来记录执行的语句，该模式的话对于函数无法保证数据一致性，如now函数

有了对这两个日志的概念性理解后，再来看执行器和 InnoDB 引擎在执行这个 update 语句时的内部流程。

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3. 引擎将这行新数据更新到内存（InnoDB Buffer Pool）中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

MyISAM索引与InnoDB索引的区别？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。

• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。

• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。

• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

MyISAM的读取性能为什么更高？

MyISAM是非聚集索引，索引跟数据是分开来的，在进行查询操作的时候，可以先加载索引文件，然后再根据从索引文件中得到的文件偏移来定位数据的位置

由于它使用的是非聚集索引，因此索引大小要比innoDB的索引文件要小，IO次数要少

InnoDB是事务型引擎，在默认的可重复读的情况下，它会通过MVCC来维护隔离级别

在MVCC机制下，读取数据会判断数据行的版本号是否在当前事务版本号之前，如果是则可以使用，否则需要在undo log中寻找符合条件的数据

MyISAM不支持事务，因此不用进行此操作，所以能够更快一些

索引失效的情况介绍一下？

1. 多列索引中，应该遵守最佳左前缀法则，即过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

2. 不要对索引列进行任何操作，否则会导致索引失效

3. 存储引擎不能使用索引中范围条件右边的列

4. mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

5. is not null 也无法使用索引,但是is null是可以使用索引的

6. like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

7. 字符串不加单引号索引失效

8. 隐式转换的影响, 当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。下面的案例中，date_str 是字符串，然而匹配的是整数类型，从而发生隐式转换。

建立索引的原则有哪些？

1. 尽量选择针对当前query过滤性更好的索引

2. 当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好

3. 尽量选择可以能够包含当前query中的where字句中更多字段的索引

4. 如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面

5. 更新频繁字段不适合创建索引

6. 尽量的扩展索引，不要新建索引

7. 外键数据列一定建立索引

8. 索引尽量非空

百万级数据如何删除？

所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）

然后删除其中无用数据（此过程需要不到两分钟）

删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。

与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

前缀索引的作用？

目的：利用前缀索引来减少索引的大小

语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。

利用select count(*)/count(distinct left(password,prefixLen));得到前缀的区分度，进而确定前缀的合适长度

MySQL中InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将使用表锁，并发将无从谈起

InnoDB引擎的锁有哪几种？

Record lock：单个行记录上的锁

Gap lock：间隙锁，锁定一个范围，不包括记录本身

Next-key lock：record+gap 锁定一个范围，包含记录本身

数据库的死锁解决方法有哪些？

解决方法

尽可能一次锁定全部所需资源

提升锁的粒度，升级为表级锁

SQL 约束有哪几种？

非空约束

唯一约束

主键约束

外键约束

CHECK: 用于控制字段的值范围