Java八股文-MySQL学习记录1

如何定位慢查询

方案一：开源工具

调试工具：Arthas

运维工具：Prometheus 、Skywalking

方案二：MySQL自带慢日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

slow_query_log=1

long_query_time=2

如何分析慢查询

直接在select语句之前加上关键字 explain

• possible_key 当前sql可能会使用到的索引
• key 当前sql实际命中的索引
• key_len 索引占用的大小
• Extra 额外的优化建议

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

1. system：查询系统中的表
2. const：根据主键查询
3. eq_ref：主键索引查询或唯一索引查询
4. ref：索引查询
5. range：范围查询
6. index：索引树扫描
7. all：全盘扫描

6和7这种全盘扫描或全索引扫描，效率就比较低了

存储引擎-InnoDB

B树与B+树

B树

B+树

而且B+树的非叶子节点不存数据，则可以比B树存更多的数据。这使得B+树更加矮胖

聚簇索引

聚簇索引不需要回表，一般就是我们所说的主键索引。

比如我们有 id name age三列。 查name,先用二级索引搜到id,再回表。 因为只有聚簇索引下面才有完整的数据信息。

覆盖索引

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

MySQL超大分页处理

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

原SQL： select * from tb_sku limit 9000000,10

升级后SQL:

先分页查询数据的id字段（这一步走的是覆盖索引），确定了id之后，再用子查询来过滤，只查询这个id列表里的数据（聚簇索引）

索引创建原则

1). 数据量较大，且查询比较频繁的表

2). 常作为查询条件、排序、分组的字段

3). 字段内容区分度高

4). 内容较长，使用前缀索引

5). 尽量联合索引

6). 要控制索引的数量

7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它

索引失效

用explain快速看一看是否失效

写sql里面的where后面的and是随意的，建立复合索引的时候要谨慎，因为顺序关系到索引树的顺序

1.最左前缀法则，针对的是复合索引.指的是查询从索引的最左前列开始，并且不跳过索引中的列。

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

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

4.字符串不加单引号，造成索引失效。(因为没有对字符串加单引号， MySQL的查询优化器，会自动的进行类型转换)

5.以%开头的Like模糊查询，索引失效。

SQL优化的经验

表的涉及优化，使用索引、sql语句的编写、主从复制，读写分离，还有一个是如果量比较大的话，可以考虑分库分表。

sql语句的简单优化：（详细优化请看我之前的简历介绍文章）

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

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

③尽量用union all代替union union会多一次过滤，效率低[union会再对结果取一次distinct的并集]

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

⑤Join优化 能用innerjoin 就不用left join right join，如必须使用 一定要以小表为驱动，

内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序

事务

1. 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
2. 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
3. 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
4. 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题，隔离级别

并发事务问题：脏读、不可重复读、幻读

隔离级别：读未提交、读已提交、可重复读、串行化

redolog 和 undolog

• redo log: 记录的是数据页的物理变化，服务宕机可用来同步数据
• undo log ：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据
• redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

redo log

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

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

undo log

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

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

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

事务的隔离性是如何保证的呢

锁：排他锁（如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁）

mvcc : 多版本并发控制

MVCC

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

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

隐藏字段

undolog版本链

readview

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

对于RC，读已提交。每次都可以生成新的readview,则不满足可重复读。有新的事务提交了就能读取到新的数据

公式里面的trx_id是undolog版本链里的trx_id，从上到下一个个去对比

)

对于RR，可重复读，只生成一个readview,可能后面会读到一些过期数据

主从同步

分库分表

主从解决的是读写的压力，分库分表解决的是存储的压力

垂直分库

垂直分表

光辉税月项目中，把文件内容存到MongoDB里，就有点垂直分表的意思

水平分库

水平分表

水平分库分表可能需要一些中间件来支持

sharding-sphere 、 mycat

什么是BufferPool

第一次从磁盘中取出来放到BufferPool,第二次查询就会快一些。一个缓存页对应一个控制块。

BufferPool默认大小是128M，以Page页为单位，Page页默认大小是16k

mysql中有个hashTable,key是表空间号＋数据页号，value是对应的控制块。然后再从控制块找缓存页。

InnoDB如何管理Page页

Page有三种状态， free page,clean page, dirty page.

free list: 空闲缓冲区。存的是控制块

flush list:管理dirty page,表示需要刷新到磁盘的缓冲区。 其他结构与free list相似

lru list: 表示正在使用的缓冲区，管理clean page 和 dirty page.会划分两个区域，冷热数据。

mysql改进lru算法

普通lru算法的优点：最近使用的数据都在表头，可以快速找到热点数据

缺点：如果发生全表扫描，有很大概率将真正的热数据淘汰掉。

mysql存在预读机制，很多预读的页会放到LRU的链表头，尾部的缓冲页很容易被淘汰。

第一次进冷数据区，在LRU链表中存在的时间超过1秒才会到热数据区。

自适应哈希索引

自适应哈希索引是InnoDB的三大特性之一，还有两个是Buffer Pool和双写缓冲区

1、自适应即我们不需要自己处理，当InnoDB引擎根据查询统计发现某一查询满足hash索引的数据结构特点，就会给其建立一个hash索引;

2、hash索引底层的数据结构是散列表(Hash表)，其数据特点就是比较适合在内存中使用，自适应Hash索引存在于InnoDB架构中的缓存中(不存在于磁盘架构中)，见下面的InnoDB架构图。

3、自适应hash索引只适合搜索等值的查询，如select *from table where index_col=’xxx’，而对于其他查找类型，如范围查找，是不能使用的;