mysql面试题笔记

MySQL存储引擎

Innodb引擎：

Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。适用于更新操作频繁，或者要保证数据的完整性，并发量高，支持事务和外键的场景。比如OA自动化办公系统。（默认索引实现为：B+树索引）

• InnoDB的行锁是建立在索引的基础之上的，行锁锁的是索引，不是数据，所以提高并发写的能力要在查询字段添加索引

MyISAM引擎(原本MySQL的默认引擎)：

不提供事务的支持，也不支持行级锁和外键。适用于管理非事务表，它提供高速存储和检索， 以及全文搜索能力的场景。比如博客系统、新闻门户网站。（MyISAM存储引擎有全文索引）。

	Innodb	MyISAM
存储结构	每张表都保存在同一个数据文件中	每张表被存放在三个文件：表定义文件、数据文件、索引文件
数据和索引存储方式	数据和索引是集中存储的，查询时做到覆盖索引会非常高效	数据和索引是分开存储的，索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据
记录存储顺序	按主键大小有序插入	按记录插入顺序保存
索引	聚簇索引	非聚簇索引
索引的实现方式	B+树索引，Innodb 是索引组织表	B+树索引，myisam 是堆表
全文索引	不支持	支持
哈希索引	支持	不支持
外键	支持	不支持
事务	支持	不支持
锁粒度（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）	行级锁定、表级锁定，锁定力度越小并发能力越高	表级锁定
SELECT		MyISAM更优
select count(*)		myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
INSERT、UPDATE、DELETE	InnoDB更优

索引

聚簇索引：

将数据与索引放到了一块，索引结构的叶子节点存储了行数据，找到索引也就找到了数据

聚簇索引的缺点：

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列作为主键。（自增主键的优点）

• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

• 通过辅助索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

回表查询：

对于普通索引，如 name 字段，则需要根据 name 字段的索引树（非聚簇索引）找到叶子节点对应的主键，然后再通过主键去主键索引树查询一遍，才可以得到要找的记录，这就叫回表查询（如上图）。

联合索引（最左匹配原则）：

对于多个字段的联合索引，如 index(a,b,c) 联合索引，则相当于创建了 a 单列索引，(a,b)联合索引，和(a,b,c)联合索引（但并不是建立了多个索引树）

锁：

行级锁 

对当前操作的行进行加锁。行级锁能减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。（INNODB支持）

特点：对当前操作的行记录加锁，发生锁冲突的概率最低，并发度也最高；加锁开销大，加锁慢；会出现死锁；

页级锁 

一次锁定相邻的一组记录。

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

表级锁 

对当前操作的整张表加锁。MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点：锁定粒度大，对当前操作的整张表加锁，发出锁冲突的概率最高，并发度最低；加锁开销小，加锁快；不会出现死锁；