MySQL面试
MySQL面试
事务
逻辑上的一组操作，要么全部执行，要么都不执行。
事务的4大特性(ACID):
- A:atomic原子性
事务是最小的执行单位，不允许分割，要么全部成功，要么全部失败
- C:consistency一致性
执行事务前后，数据保持一致，多个事务对统一数据的读取应该是相同的
- I:Isolation独立性
并发访问数据库时，多个事务之间应该是独立的
- D:determined持久性
事务提交后造成的数据影响是永久的
并发事务带来的问题：
- 脏读：dirty read
一个事务修改来了数据未提交，另一个数据读取了未提交的数据。
- 丢失修改：lost to modify
两个事务都修改了数据之后，第一个数据修改就会丢失。如数据A=10,事务1：A=A-1;
事务2：A=A-1，最后结果发现为19；
- 不可重复读：Unrepeatable Read
A事务在多次修改数据之间，B事务读取了数据，但是B在A事务执行之间读取的时候，可能会发现读取同一个数据的时候两次读到的不一样；
- 幻读：Phantom Read
幻读与不可重复读相同，但是是一个事务多次插入或者删除数据，另一个事务读取的时候会发现多了或者少了数据。
事务的隔离级别
- Read-Uncommitted(读未提交)
最低的隔离级别，允许读取尚未提交的事务，会导致脏读、幻读、不可重复读。
- Read-Committed(读已提交)
允许并发事务读取已经提交的数据，可以防止脏读，单有可能幻读、不可重复读。
- Repeatable-Read(可重复读)
mysql的默认隔离级别，对同一字段的多次读取都是相同的，除非数据是被本身事务修改。可以防止脏读与不可重复读，但有可能会有幻读。
- Serializable(可串行化)
最高的隔离级别，完全按照ACID，所有事务依次执行。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read-Uncommitted(读未提交) | √ | √ | √ |
| Read-Committed(读已提交) | × | √ | √ |
| Repeatable-Read(可重复读) | × | × | √ |
| Serializable(可串行化) | × | × | × |
需要注意：InnoDB引擎在Repeatable-Read级别下使用的Next-Key Lock，所以可以在这个级别下防止幻读，起到了Serializable的效果。
InnoDB在分布式事务的情况下采用的是Serializable隔离级别。
数据库的索引
索引做了什么：将无需的数据变成相对的有序的数据。
最左前缀匹配原则
MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：
select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引
如何添加索引
- PRIMARY KEY（主键索引）
主键是索引的一种，是唯一索引的特殊类型。创建主键的时候，数据库默认会为主键创建一个唯一索引。
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
- UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
- INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
- FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
- 多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
数据库引擎
MyISAM和InnoDB区别
MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。
两者的对比：
- 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
- 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
- 是否支持外键： MyISAM不支持，而InnoDB支持。
- 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在
READ COMMITTED和REPEATABLE READ两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。
表级锁和行级锁对比：
- 表级锁： Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
- 行级锁： Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
InnoDB存储引擎的锁的算法有三种：
- Record lock：单个行记录上的锁
- Gap lock：间隙锁，锁定一个范围，不包括记录本身
- Next-key lock：record+gap 锁定一个范围，包含记录本身
