Java面试_数据库篇_优化,事务,Mysql

优化

在Mysql的配置文件(/etc/my.cnf)中配置:

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒, SQL语句执行时间超过2秒, 就会视为慢查询, 记录慢查询日志
long_query_time=2

explain select * from ap_user where id = 1
在这里插入图片描述

Extra: 额外的优化建议

Extra	含义
Using where; Using Index	查找使用了索引, 需要的数据都在索引列中能找到, 不需要回表查询数据
Using index condition	查找使用了索引, 但是需要回表查询数据

使用MySQL自带的分析工具 EXPLAIN(DESC)

什么是索引
- 索引（index）是帮助Mysql高效获取数据的数据结构（有序）
- 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
索引的底层数据结构
- Mysql的InnoDB引擎采用的B+树的数据结构来存储索引
- 阶数更多，路径更短
- 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据
- B+树便于扫库和区间查询，叶子节点间是双向链表

聚簇索引（聚集索引）：数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个
非聚簇索引（二级索引）：数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个
回表查询：通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表
extend: 聚集索引选取规则
- 如果存在主键，主键索引就是聚集索引
- 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

什么是覆盖索引
- 覆盖索引：指查询使用了索引，返回的列，必须在索引中全部能够找到
- 如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *
超大分页优化
- 当数据量比较大时，limit分页查询，需要对数据进行排序，效率低
- 解决方案：覆盖索引+子查询

表的设计优化
- 设置合适的数值(tinyint int bigint), 根据实际情况选择
- 设置合适的字符串类型(char varchar), char定长效率高, varchar可变长度, 效率稍低
SQL语句优化
- select语句务必指明字段名称（避免直接使用 select *）
- sql语句要避免造成索引失效的写法
- 尽量用union all代替union，union会多一次过滤，效率低
- 避免在where子句中对字段进行表达式操作
- Join优化，能用inner join就不用left join，right join，如果需要使用则要以小表为驱动。内连接会对两个表进行优化，优先把小表放到外边，大表放到里边。left join或right join不会重新调整顺序。
主从复制，读写分离，不让数据的写入影响都操作
分库分表

缓冲池（buffer pool）：主内存中的一个区域，里面可以换成磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓冲），以一定频率刷新到磁盘，从而减少磁盘io，加快处理速度。
数据页（page）：是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。页中存储的是行数据。
redo log：记录的是数据页的物理变化，服务宕机可用来同步数据
undo log：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据
redo log保证了事务的持久性，undo log保证了事务的原子性和一致性
参考回答

MVCC是Mysql中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突。
实现原理如下：

隐藏字段
- trx_id(事务id)，记录每一次操作的事务id，是自增的。
- roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址。
undo log
- 回滚日志，存储老版本数据
- 版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表。
readView解决的是一个事务查询选择版本的问题
- 根据readView的匹配规则和当前的一些事务id判断该访问哪个版本的数据。
- 不同的隔离级别快照读是不一样的，最终的访问的结果不一样
  - RC：每一次执行快照读时生成ReadView
  - RR：仅在事务中第一次执行快照读时生成ReadView，后续复用。