面试：MYSQL（SQL优化、MYSQL事务）-易微帮

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志如果要开启慢查询日志，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log

2、定位到慢查询时，如何优化

可以采用EXPLAIN 或者DESC命令获取MySQL如何执行SELECT语句的信息

possible_key当前sql可能会使用到的索引

key当前sql实际命中的索引

key_len索引占用的大小

Extra额外的优化建议

type这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all

system:查询系统中的表

const:根据主键查询

eq_ref:主键索引查询或唯一索引查询

ref:索引查询

range:范围查询

index:索引树扫描(若为这两个，则说明SQL语句需要优化了)

all:全盘扫描

3、什么是索引

索引 (index）是帮助MysQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树)，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

（1）底层结构

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

B树和B+树的对比：

磁盘读写代价B+树更低;

查询效率B+树更加稳定;

B+树便于扫库和区间查询；

4、聚簇索引（聚集索引）与非聚簇索引（二级索引）

聚集索引存储的是id对应的一行数据，二级索引存储的是属性值对应的id数据。

（1）聚集索引选取规则：

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一(UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

（2）回表查询

通过二级索引查出的id，前往聚集索引重新查到那一行的数据；

5、覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

**（1）通过id可以查到 * ，所以它是聚集索引**

（2）这个sql使用二级索引查询，查到了arm和2，而需要的也是这两个，所以为聚集索引。

（3）这条sql通过二级索引只查到了arm和id，没有查到gender，需要进行回表查询才能获得，所以不是覆盖索引。

（4）Mysql超大分页处理

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

因为，当在进行分页查询时，如果执行limit 9000000,10，此时需要MySQL排序前9000010记录，仅仅返回9000000 - 9000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路:一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

6、索引创建原则

针对于数据量较大，且查询比较频繁的表建立索引。单表超过10万数据（增加用户体验)

针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。

尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间避免回表，提高查询效率。

要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

7、什么情况下索引会失效

（1）违反了最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

假设我创建了三个索引，name、status、address。但是我的sql语句里判断了status和address而跳过了name，这样就会违反最左前缀法则，从而导致索引失效。

（2）范围查询右边的列，不能使用索引。

在这个sql中status使用了范围查询>,导致它右边的索引失效了。

（3）不要在索引列上进行运算操作，索引将失效。

（4）字符串不加单引号，造成索引失效。

由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

（5）以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

8、sql优化的经验

（1）表的设计优化

比如设置合适的数值(tinyint int bigint)，要根据实际情况选择

比如设置合适的字符串类型(char和varchar) char定长效率高,varchar可变长度，效率稍低

（2）sql语句优化

SELECT语句务必指明字段名称。（避免直接使用select* )

SQL语句要避免造成索引失效的写法。

尽量用union all代替union，union会多一次过滤，效率低。（union all不去重）

避免在where子句中对字段进行表达式操作。

Join优化能用innerjoin就不用left join right join，如必须使用一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join或 right join，不会重新调整顺序。

（3）主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询的效率。

二、MYSQL事务

事务是一组操作的集合,它是一个不可分割的工作单位，事务会把所有的操作作为一-个整体- -起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

1、事务的特性

原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

一致性(Consistency)︰事务完成时，必须使所有的数据都保持一致状态。

隔离性(lsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下行。

持久性(Durability) ︰事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

2、并发事务问题

脏读：一个事务读到另外一个事务还没有提交的数据。

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了"幻影”。

3、事务的隔离级别

注意:事务隔离级别越高，数据越安全，但是性能越低。

4、undo logo和redo logo的区别

首先我们需要了解缓冲池和数据页。

缓冲池(buffer pool) :主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据〈若缓冲池没有数据，则从磁盘加载并缓存)，以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

数据页（page):是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。页中存储的是行数据。

（1）redo logo

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件（(redo log file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。

（2）undo logo

回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚和MVCC(多版本并发控制）undo log和redo log记录物理日志不一样，它是逻辑日志。

可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然。

当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

undo log可以实现事务的一致性和原子性

5、MVCC

全称Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突。

MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView。

记录中的隐藏字段

DB_TRX_ID：最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。

DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。

DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

undo logo版本链

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

readview

ReadView(读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的) id。

m_ids：当前活跃的事务ID集合
min_trx_id：最小活跃事务ID
max_trx_id：预分配事务ID，当前最大事务ID+1(因为事务ID是自增的)
creator_trx_id：ReadView创建者的事务ID

1、当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记灵进行加锁。

对于我们日常的操作，如:select ..lock in share mode(共享锁)，select .. for update、update、insert、delete(排他锁)都是一种当前读。

2、快照读

简单的select (不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

Read Committed:每次select，都生成一个快照读。

Repeatable Read:开启事务后第一个select语句才是快照读的地方。