面试复习-Mysql数据库

1.事务

事务是指满足ACID特性的一组操作。

• 原子性（Atomicity）：事务是不可分割的最小单元，事务内的语句，要么全部执行成功，要么全部执行失败。
• 一致性（Consistency）：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的。
• 隔离性（Isolation）：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
• 持久性（Durability）：一旦事务提交，则其所做的修改将会永远保存在数据库中。
  MYSQL默认采用自动提交模式。

2.并发一致性

• 丢失修改
  T1和T2两个事务都对一个数据进行修改，T1先修改，T2后修改，则T2的修改会覆盖T1。
• 读脏数据
  T1修改一个数据，T2随后读取这个数据。如果T1撤销了这次修改，则T2读到的是脏数据。
• 不可重复读
  T2读取一个数据，T1对该数据做了修改，此时T2再次读取这个数据会和第一次读取的结果不同。
• 幻影读
  T1读取某个范围的数据，T2在这个范围内插入了新的数据，T1再次读取和第一次的结果不同。

3.多版本并发控制（MVCC）

InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间。存储的不是实际的时间值，而是系统的版本号。MVCC只要可重复读和提交读两个隔离级别下工作。
在可重复读的隔离级别下，MVCC的操作：

• SELECT

1. InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是事务开始前已经存在的，要么是事务自身插入或者修改过的。
2. 行的删除版本号要么未定义，要么大于当前事务的版本号，这可以保证事务读取到的行，在事务开始前未被删除。

• INSERT
  为插入的每一行保存当前系统版本号作为行版本号。
• DELETE
  为删除的每一行保存当前系统版本号为删除版本号。
• UPDATE
  修改后的数据行保存当前系统版本号为创建版本号，同时保存当前系统版本号作为原来的行的删除版本号。

4.事务的隔离级别

• 未提交读：事务中的修改，即使没有提交，对其他事务也是可见的。
  问题：丢失修改、读脏数据、不可重复度、幻影读
• 提交读：一个事务只能读取已经提交的事务所做的修改。
  解决：读脏数据。
• 可重复读：保证在同一个事务中多次读取同样数据的结果是一样的。
  解决：读脏数据、不可重复读

为什么不能解决幻读：
快照读：使用MVCC读取的是快照中的数据，可以减少加锁带来的开销
当前读：读取的是最新的数据，需要加锁。
当执行select操作时innodb默认会执行快照读，会记录下这次select后的结果，之后select的时候就会返回这次快照的数据。当A事务执行第一次select的时候，没有任何问题，此时事务B insert 了一条数据然后commit，这时候A再次执行select，那么返回的数据中会有B添加的那条数据，因为快照已经生成了。
对于会对数据修改的操作都是采取当前读的模式。

• 可串行化：强制事务串行执行。
  解决：读脏数据、不可重复读、幻影读

5.范式

• 第一范式：属性不可分
• 第二范式：每个非主属性完全函数依赖于键码
• 第三范式：非主属性不传递函数依赖于键码。
• 范式化的好处：

1. 范式化的更新操作通常比反范式化要快
2. 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
3. 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。

• 范式化的缺点：
  通常需要关联，代价昂贵 ，也可能使一些索引策略无效。

6.索引

• B+Tree索引
  B+Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始搜索。B+Tree是有序的，所以适用于排序和分组。
  B+Tree索引适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于根据最左前缀查找。B+Tree索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。
• 限制

1. 如果不是按照索引的最左列开始查找，则无法开始索引。
2. 不能跳过索引中的列。
3. 如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查找。

• 哈希索引
  基于哈希表实现，只有精确匹配所有列的查询才有效。只有Memory引擎显式支持哈希索引。哈希索引无法用于排序和分组。InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。
• 空间数据索引
  MyISAM表支持空间索引，可以用作地理数据存储。这类索引无须前缀查询，会从所有维度索引数据。
• 全文索引
  全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。
• 索引的优点
  1.大大减少了服务器需要扫描的数据量
  2.可以帮助服务器避免排序和临时表
  3.可以将随机I/O变为顺序I/O

7.高性能的索引策略

• 独立的列
  索引列不能是表达式的一部分，也不能是函数的参数。
• 前缀索引和索引选择性
  当需要索引很长的字符列时，会让索引变得大且慢，通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这也会降低索引的选择性。对于BLOB\TEXT\或者很长的VARCHAR类型的列，必须使用前缀索引。前缀的长度需要根据索引选择性来确定。（索引的选择性：不重复的索引值和数据表的记录总数的比值）
• 多列索引
  在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好
• 选择合适的索引列顺序
  让选择性强的索引列放在前面
• 覆盖索引
  如果一个索引包含所有需要查询的字段的值，就称为覆盖索引。如果索引的叶子节点中已经包含要查询的数据，那么就不再需要回表查询了。
  优点：
  1.索引条目通常远小于数据行大小，Mysql会极大地减少数据访问量。
  2.因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
  3.对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。
• 使用索引扫描来做排序
• 压缩索引
  MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。

8.查询优化

查询性能低下的最基本原因是访问的数据太多。

• 是否向数据库请求了不需要的数据
  有些查询请求超过实际需要的数据，而这些多余的数据会被应用程序丢弃。这会给Mysql服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的cpu和内存资源。所以我们需要：
  1.只返回必要的列：最好不要用select * 进行查询
  2.只返回必要的行：使用limit来限制返回的数据
  3.缓存重复查询的数据

• 是否在扫描额外的记录
  可以使用下面的方法优化：
  1.使用索引覆盖扫描，把所有需要用到的列都放在索引中，这样存储引擎无须回表获取对应行就可以返回结果
  2.改变库表结构
  3.重构查询方式

• 重构查询方式

1. 切分大查询
   一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询
2. 分解大连接查询
   对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。这样可以让缓存的效率更高，执行单个查询可以减少锁的竞争，在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展，查询本身的效率也可能会有所提升，可以减少冗余的数据。

9.存储引擎

• InnoDB
  是MySQL默认的存储引擎。实现了四个标准的隔离级别，默认是可重复读，通过MVCC和Next-Key Locking防止幻
  影读。支持真正的在线热备份，其他存储引擎不支持在线热备份。
  Record Locks：锁定一个记录上的索引，而不是记录本身。
  Gap Locks：锁定索引之间的间隙，而不是索引本身。
  Next-key locks: InnoDB存储引擎的一种锁实现，是Record Locks和Gap Locks的结合。不仅锁定索引，也锁定索引之间的间隙。
• MyISAM
  设计简单，数据以紧密格式存储。提供了大量的特性，包括压缩表、空间数据索引等。不支持事务，不支持行级锁，只支持表级锁，可以手工或者自动执行检查和修复操作，但可能导致一些数据丢失，而且修复操作非常慢。

10.主从复制

• binlog线程：负责将主服务器上的数据更改写入到二进制日志中
• I/O线程：负责从主服务器上读取二进制日志，并写入从服务器的中继日志
• SQL线程：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放

11.SQL

一条SQL语句的执行过程：

• 连接器查看当前用户权限，若拥有权限，建立连接。如果客户端太长时间没有动静，连接器会自动断开连接
• 分析器分析SQL语句，包括语法分析（语法是否正确），词法分析（识别SQL语句）。
• 优化器优化查询，决定使用的索引等。
• 执行器开始执行语句。

1. 创建表

create table tb (
  # 非空，自增
  id int not null auto_increment,
  name varchar(255) null,
  state int not null default 0,
  primary key (`id`)
);

2. 修改表

#添加列
alter table tb add col char(20);

#删除列
alter table tb drop column col;

#删除表
drop table tb;

3. 插入

insert into tb(name,state) values(val1,val2);

#插入检索出来的数据
insert into tb(name,state) select name,state from tb1;

#将一个表的内容插入到一个新表
create table tb2 as select * from tb;

4. 更新

update tb set name=val where id=1;

5. 删除

delete from tb where id=2;

#清空表
truncate table tb;

6. DISTINCT／LIMIT

#相同值只会出现一次
select distinct name,state from tb;

#限制返回的行数
select name,state from tb limit 5;

#从第2行开始返回，返回的行数为5
select name,state from tb limit 1,5;

7. 排序

#默认为升序ASC，DESC为降序
select * from tb order by state desc;

8. 通配符

# %匹配>=0个任意字符
# _匹配==1个任意字符
# 【】可以匹配集合内的字符
# ^表示否定

#表示不以a和b开头的任意文本
select * from tb where name like '[^ab]'；

9. CONCAT

#CONCAT()用于连接两个字段，使用TRIM()可以去除首尾空格
select concat(trim(name),':',trim(state)) as nameState from tb;

10. 分组

#group by出现在where之后，出现在order by之前
select col , count(*) as num from tb group by col;

11. 子查询

select * from tb where name in ( select name from tb2 );

select name, (select count(*) from tb2 where tb2.id = tb.id) as num from tb order by name;

12. 连接

• 内连接

select a.val , b.val from t1 as a inner join t2 as b on a.key = b.key;

• 自连接：内连接的一种，只是连接的表是自身

select a.name from tb as a inner join tb as b on a.state=b.state and b.name="lug";

• 自然连接：将自动连接所有的同名列

select a.val, b.val from tb1 as a natural join tb2 as b;

• 外连接：保留了没有关联的行。分为左外连接，右外连接和全外连接。

select a.val, b.val from tb1 left join tb2 on tb1.id=tb2.id;

13. 组合查询

#使用UNION来组合两个查询
select col from tb1 where col=1 union selct col from tb2 where col=0;