【2019秋冬】【面经】MySQL

索引

索引的优缺点
优点：
1.创建唯一索引，保证每一行数据的唯一性
2.加快数据库的检索速度，加速数据表库之间的连接

缺点：
1.创建和维护索引需要耗费时间，随数据量的增加而增加
2.索引需要占用物理空间
3.数据进行变化时，索引也需要维护

索引失效条件
1.条件中有or
2.like查询以%开头
3.列类型是字符串要用引号
4.索引列参与计算或函数
5.全表扫描比索引快

索引适用场合
1.为经常出现在order by ，group by ，distinct后的字段添加索引
2.在union等集合操作的结果集字段上建立索引
3.在经常用作查询选择字段和表连接属性上建立索引
4.对于数据很少被更新的表，如果用户经常只查询几个字段，在这几个字段上建立索引，把表扫描简化为字段扫描

索引如何优化查询效率
索引通过采用B+树，提高查询效率，将无序的数据变为相对有序的数据

索引为什么使用B+树
1.B+树更有利于对数据库的扫描
B树只需要遍历叶子节点就可以对全部关键字进行扫描
2.磁盘读写代价低
B+树非叶子节点没有指向具体数据，因此内部结构更小，可以容纳的关键字数量多，读写次数降低
3.查询效率稳定
所有关键字查询都需要从根走到叶子节点，查询每一个数据的效率都差不多

介绍一下B+树和B树
B+树：
根节点要么没有子树，要么至少两个子树；
所有叶子节点包含全部关键字及对应文件地址；
叶子节点都在同一层；
相邻叶子节点按顺序排列；
叶子节点中的关键字按大小顺序排列；

B树：
多路搜索树；
内部节点也包含关键字和文件指针；
内部节点关键字个数是指向儿子节点的指针个数减一；

B+树与B树区别
1.B+树内部节点中，关键字个数与其子树个数相同
B树子树个数比关键字个数多一个
2.B+树指向文件的关键字和指针都在叶子节点中
B树有些指向文件的关键字在内部节点中
3.B+树在内部节点搜索到了想要的关键字也会继续向下搜索，找到对应的指针后停止

索引分类
组合索引 将多个字段建立到一个索引中，列值的组合要唯一
聚集索引 数据行的物理顺序与列值的逻辑顺序相同；一个表只能有一个
非聚集索引：
唯一索引（unique） 不可以出现相同的值，可以有NULL
普通索引（index） 允许出现相同值
主键索引（primary key） 不允许出现相同值
全文索引（fulltext） 效率低

如何创建/添加索引
创建非聚集索引

alter table 表名 add 索引类型（index....）[索引名]（字段名）

创建多列索引/组合索引

alter table 表名 add index 索引名（'column1','column2',....）

InnoDB与MyISAM区别
1.MyISAM是非事务安全型；InnoDB是事务安全型
2.MyISAM是表级锁；InnoDB是行级锁
3.MyISAM支持全文索引；InnoDB不支持
4.MyISAM相对简单，适用于小型应用
5.MyISAM表是文件形式，在跨平台中更方便
6.InnoDB比MyISAM更安全

MyISAM更适合读大量表，InnoDB适合写大量表；
如果需要支持事务，并发读取频率高，InnoDB更适合，因为MyISAM锁粒度大，查询等待时间久；
如果不需要支持事务，数据量大，MyISAM更适合，因为MyISAM支持压缩，减少磁盘占用

事务

什么是事务
事务是逻辑上的一组操作，要么不执行，要么完全执行

事务隔离级别
读取未提交
最低级，可以读还没修改的数据，脏读，幻读，不可重复读
读取已提交
允许读取并发事务已经提交的数据，幻读，不可重复读
可重复读
InnoDB默认支持的级别，幻读
可串行化
最高级别，所有事务依次执行

事务特性ACID
原子性 要么执行要么不执行
一致性 多个事务对同一数据读取结果相同
隔离性 各个并发事务之间互不影响
持久性 事务提交后改变是持久的

并发事务带来的问题

脏读 一个事务修改了数据，还没提交就别别的事务使用了

幻读 一个事务读取了几行，另一个事务插入或删除了几行，导致第一个事务读到了意料之外的数据

不可重复读 一个事务读取了几行，另一个事务修改了几行，导致第一个事务读的数据前后不一致

丢失修改 一个事务修改了数据，另一个事物同时也修改了，导致第一个事务修改的值丢失了

不可重复读与幻读的区别
不可重复读重点是修改
幻读重点是增加或删除

数据库锁

详细见MySQL技术内幕 第六章 锁

隔离级别越低，请求的锁越少
可重读使用的是 Next-Key Lock锁，已经达到了普通SQL的要求

共享锁 S锁 读取行数据
排他锁 X锁 修改行数据
意向共享锁 IS锁 读取表数据
意向排他锁 IX锁 修改表数据

Record Lock：给当前行上锁
Gap Lock：给一个范围上锁，但不锁定当前行
Next-Key Lock：前两个锁的结合，给一个范围上锁，包括当前行，左开右闭（ ]
previous-key Lock：同Next-Key Lock ，左闭右开[ )

当查询的索引包含唯一属性时（比如主键），Next-Key Lock会自动转化为Record Lock，就不锁定范围，只锁定当前行，方便其他事务进行操作，不用等待
当给非聚集索引上锁时，使用Next-Key Lock，对其前一个值做范围上锁，同时，InnoDB存储引擎还会自动给后一个值的范围加Gap Lock 防止幻读

Gap锁如何解决幻读的
Gap锁在索引记录的间隙上加锁，禁止插入，避免了幻读

其他

DDL DML DCL DQL
DDL 管理数据库对象 create alter drop
DML 增删改查
DCL 控制，给权限
DQL 查询语句

explain
explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。
explain命令可以查看优化器如何决定执行查询的主要方法

优化MySQL
不要用double，text，时间用timestamp，只有日期用date