MySQL面试题

SQL优化

SQL优化的几种方法

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如果索引是整形，那么可以在索引上设置默认值 0，确保表中列没有 null 值。
应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。
in 和 not in 也要慎用，否则会导致全表扫描。
like ‘%abc%’ 也会导致全表扫描。
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
很多时候用 exists 代替 in 是一个好的选择。
并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段 sex，male、female 几乎各一半，那么即使在 sex 上建了索引也对查询效率起不了作用。
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间,其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
任何地方都不要使用 select * from ，用具体的字段列表代替 “*”，不要返回用不到的任何字段。

SQL优化的步骤

通过 show status 命令了解各种 SQL 的执行频率
```
show [session|global] status
```

定位执行效率较低的 SQL 语句

修改配置，会在 data 目录下生成 log-slow.txt 慢查询日志

[mysqld]
#记录sql执行超过2秒的SQL语句
long_query_time = 2
#指定记录的文件名
log-slow-queries=log-slow.txt
#记录没有使用索引的SQL查询 
log-queries-not-using-indexes

通过 EXPLAIN 分析低效 SQL 的执行计划
1. 可以看到是否使用了索引
2. 扫描了表的行数

MySQL锁

Mysql锁类型

MySQL有三种锁的级别：页级、表级、行级。
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

死锁产生的情况

表级锁不会产生死锁，所以解决死锁主要还是针对于最常用的 InnoDB。
死锁的关键在于：两个(或以上)的 Session 加锁的顺序不一致。

查看死锁的语句

show engine innode status

mysql解除正在死锁的状态

运行命令 show processlist ;
查看 Command 为 Sleep 的 Id，执行 kill id 即可。

如何避免死锁

按同一顺序访问对象。
- 如两个并发事务，第一个事务要先获取A表的锁，再获得B表的锁，那么其他事务都保持一致。
避免事务中的用户交互。
保持事务简短并在一个批处理中。
使用低隔离级别。
使用绑定连接。

死锁举例

在 for 循环中执行 Select * from xxx where id=‘随机id’ for update，那么下面的情况就会发生死锁：

Session1
select * from t3 where id=9 for update;
Session2
select * from t3 where id<20 for update;
Session1
insert into t3 values(7,'ae','a',now());

Session2 在等待 Session1 的 id=9 的锁，session2 又持了 1 到 8 的锁（注意 9 到 19 的范围并没有被 session2 锁住），最后，session1 在插入新行时又得等待 session2，故死锁发生了。

MySQL事务

Mysql事务隔离级别

设置执行语句：

set tx_isolation='READ-UNCOMMITTED';
select @@tx_isolation;

Read Uncommitted(读取未提交内容)

所有事务都可以看到其他未提交事务的执行结果。
本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。
该级别引发的问题是——脏读(Dirty Read)：读取到了未提交的数据。
事务 A 第一次执行查询，查到了数据 A，这时候 A 事务还没结束，B 修改了数据，但并未提交事务，这时候 A 再次查询发现数据被修改了。

Read Committed(读取提交内容)

这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。
它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。
这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read)
- 不可重复读意味着我们在同一个事务中执行完全相同的 select 语句时可能看到不一样的结果
- 导致这种情况的原因可能有
  - 有一个交叉的事务有新的 commit，导致了数据的改变;
  - 一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的 commit
事务 A 第一次执行查询，查到了数据 A，这时候 A 事务还没结束，B 修改了数据，并已提交事务，这时候 A 再次查询发现数据被修改了，此时 B 再次修改数据，那么 A 再次查询，数据再次被修改。

Repeatable Read(可重读)

这是 MySQL 的默认事务隔离级别。
它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
此级别可能出现的问题——幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的 “幻影” 行。
InnoDB 和 Falcon 存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了该问题。

Serializable(可串行化)

这是最高的隔离级别。
它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。
在这个级别，可能导致大量的超时现象和锁竞争。