MySql

0、参考文章

• https://www.cnblogs.com/kismetv/p/10331633.html

1、索引失效的情况

• 组合索引不遵循最左匹配原则
• 组合索引的前面索引使用范围（>, < ,like）会导致后面索引失效
• 在索引上做计算操作(计算、函数、隐式类型转换)
• is null 和 is not null不会使用索引
• mysql使用全表扫描比使用索引快的时候，index也会失效
• like语句，以%开头的查询语句

2、数据库存储引擎有哪些

show engines可以查看

• inniDB
• MyIsam
• memory 内存性质的引擎
• CSV
• performance_schema

3、innoDB和myIsam的区别

	innoDB	MyIsam
是否支持事务	是	否
是否支持外键	是	否
行锁	支持	不支持
表锁	支持	支持
索引	聚促索引和非聚促索引均有	只有非聚促索引
存储文件	frm、ibd	frm、myi、myd
具体行数	不保存表的具体行数	用变量保存(查询不能带条件)

4、mysql创建索引的优缺点

• 优点：加快查询的速度
• 缺点：
  • 增、删、改的速度都会变慢
  • 维护索引需要耗费数据库资源，索引需要占用磁盘空间

5、索引分类

• innoDB：
  • 主键索引：使用主键默认创建的索引，索引不能为空，建表时自动创建
  • 单值索引：除主键以外其他列创建的索引，又叫普通索引，一个表可以有多个单值索引
  • 唯一索引：索引列的值必须唯一，允许有空值，但是只能有一个
  • 复合索引：一个索引中包含多个列，左前缀匹配原则，在查询过程中会动态调整查询字段的顺序，以便利用索引
• MyIsam：
  在上面这四种之上，多了个Full Text， 只能建在 string类型的字段

6、数据库语句

• show index from 表名： 查看表的所有索引
• create index index_name on 表名(列名)： 给某个表的某个列创建一个名为 index_name的索引
• create table 表名(列名B 列类型 primary key，列名 列类型， key(列名A))： 创建一个数据库表，并在列名为A的列创建普通索引, 列B为主键， 索引名字为列名，没办法手工指定
• create table 表名(列名B 列类型 primary key，列名 列类型， unique(列名A))： 创建一个数据库表，并在列名为A的列创建唯一索引, 列B为主键
• create table 表名(列名B 列类型 primary key，列名 列类型， unique(列名A, 列名C))： 创建一个数据库表，并在列名为A和C的列创建复合索引, 列B为主键
• show engine innoDB status\G 查看锁的状态，执行这个前先把 lock output打开

7、细小知识

• innoDB 每一页大小默认是 16 KB
• B+Tree最顶层的树根是常驻内存的，三层时存储的数据量大约为10亿条
• 查找某一条记录时最多只需要 1-3次磁盘IO，对应是B+tree是 2-4层
• MySql的存储引擎： InnoDB(frm+ibd), 底层B+Tree实现、MyIsam(frm+MYD+MYI)，底层B+Tree实现， Memory底层hash实现。

8、B+树和B树的区别

• B+树只在叶子节点存储数据，B树在非叶子节点也会存储数据
• B+树在叶子节点末尾有一个指针指向下一个叶子节点

9、聚簇索引和非聚簇索引

• 聚簇索引：将数据存储和索引放在一起，索引结构的叶子节点保存了行数据
  • 一个表只能有一个聚簇索引
  • 在innoDB中，如果定义了主键，以主键作为聚簇索引；没定义主键，mysql将第一个组成列都 not null的唯一索引作为聚簇索引；如果innoDB表没有主键，且没有适合的唯一索引，mySql将自动创建一个隐藏的名字为GEN_CLUST_INDEX 的聚簇索引
  • innoDB中的辅助索引的叶子节点为什么存主键，而不是数据的地址：
    • 当行数据发生变化时，索引树的节点也需要分裂变化，我们只需要维护聚簇索引就可以了，可以避免对辅助索引的维护
• 非聚簇索引：数据和索引分开存储，索引中的叶子节点指向了数据对应的位置
  • myIsam使用的是非聚簇索引，它的两个B+树看起来一样， 主键索引节点存储了主键，辅助索引存储了辅助键，表数据存储在独立的地方， 两棵树的叶子节点都使用同一个地址指向真正的表数据，两棵索引树是独立的，通过辅助索引查找时无需访问主键的索引树
• 聚簇索引的优势：
  • 访问同一页的不同记录时，已在内存中加载完成， 在内存中即可完成，不需要再次IO操作
• 聚簇索引需要注意的地方：
  • 主键最好不要用uuid
  • 建议使用int类型的自增，聚簇索引数据的存放顺序和索引的顺序是一致的， 主键值占用的存储空间越大，辅助索引保存的主键索引占用的空间也会变大

10、什么情况下无法利用索引

• 查询语句中使用多列索引
  多列索引是在表的多个字段上创建索引，即复合索引，只有查询条件中使用了这些字段中的第一个字段，索引才会使用
• 查询语句中使用OR关键字
  查询语句只有OR关键字时，OR前后2个条件都是索引，那么查询中将使用索引，OR前后有一个条件不是索引，将不使用索引

11、数据库事务的四大特征

• Atomicity：原子性，事务中的所有操作都作为一个整体，像原子一样，要么全部成功，要么全部失败
• Consistency：一致性，事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。一致性状态是指，系统的状态满足数据的完整性约束,系统的状态反应数据库本应描述的现实世界的真实状态
• Isolation：隔离性，并发执行的事务不会相互影响，和串行执行对数据库的影响一致。
• Durability：持久性，事务一旦提交，其对数据库的更新是持久的。任何事务或系统故障都不会导致数据丢失。

12、数据库锁相关

• 按锁机制分为：
  • 共享锁（S）：允许一个事务读取一行，阻止其他事务获取相同数据集的排他锁
  • 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排他锁
• 按锁粒度分为：
  • 表锁：myisam和memory采用的是这种锁
    • 开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突的概率大，并发度低
    • 适用于以查询为主，并发用户少的场景
    • muIsam在执行查询语句前，会自动的给涉及的表加读锁，在更新前，会自动的给涉及的表加写锁，一般不需要用户感知
  • 行锁：inniDB既支持行级锁，也支持表级锁，默认情况下采用的是行级锁
    • 锁定粒度小，最大程度知识并发，可能发生死锁
      由于隔离级别在读未提交和串行化2种级别下，一个是没锁，一个是全加锁，所以下面的所有的知识点全部是基于读已提交和可重复读级别来说的
• 在InnoDb中
  • 对于 update、delete、insert语句，innoDB会自动的给涉及的数据集加排他锁X
  • 对于普通的select语句，innoDB不会加任何锁
  • SELECT * FROM table_name WHERE … LOCK IN SHARE MODE 给涉及的数据添加共享锁，其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
  • 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

13、如何理解事务的ACID特性

• automic:原子性，在一个事务中的所有操作，要么全部成功，要么全部失败。它的实现主要是靠undolog来实现的：
  • undolog是一个逻辑日志，用来记录sql执行的相关信息
  • 当事务对数据进行修改的时候，innoDb会生成对应的undolog，当事务执行失败或者回滚的时候，可以利用undolog中的信息将数据恢复到之前的样子，其实就是执行相反的数据库操作
• consisitent: 一致性
• Isolation：隔离性， 指事务内部的操作与其他事务是隔离的，mvCC + 锁保证
• 持久性：持久性是指数据写入磁盘，主要是靠redolog保证是的
  • 在读写数据库时会先从buffer pool中读写，读的时候先读buffer pool，里面没有再从磁盘中读取，写的时候先写到buffer pool中，buffer pool修改的数据会定期刷新到磁盘中，这一过程称为脏刷
  • 在写入buffer pool数据前会在redolog中记录这次操作，当事务提交时会调用fsync对数据进行刷盘，如果MySql宕机，可以重新读取redolog对数据进行恢复
  • 为什么redolog比buffer pool中修改的数据写入磁盘快：redolog是追加的形式写入，是顺序IO；buffer pool中的数据持久化到磁盘是随机IO

14、谈谈对 MVCC 的理解

15、幻读怎么解决

16、sql join的原理

17、mysql一条数据是如何保存到数据库的

客户端 -> 连接器(校验用户名、密码、准备连接)->分析器（转换成语法树）->优化器（CBO，RBO）->执行器 ->存储引擎