数据库性能优化实际案例

1. 同时网上考试时数据库性能优化：

1. 同时考试，考的同样的试卷，那么试卷中从数据库取的数据多次使用，需要缓存。

2.同时考试，那么考试答题卡表是是不是建立全局临时表，答完不用truncate，也不会导致表争用。

3.连接数够不够用，session等待时间够不够长

于是分析学生参与考试的整个过程：

1. 打开应用：学生使用平板电脑打开app，并进入考试列表页面（原生），这里后台读取操作是查询本课程目前的考试
2. 开始考试：当某一考试开始时间到达，考生可以点击开始答题按钮进入考试，此时，会将整个试卷的内容都拉取下来，并存储到安卓本地的DB中（这里读取的并发很大）
3. 做题：学生开始答题，当学生每点击下一题时，会将本题的答案保存到后台，成功后会跳转到下一题（写入的并发压力大）
4. 提交：学生答完所有题目后，会在最后一题点击提交按钮，保存最后一题，并自动计算得分

所以读取的问题锁定在第2步，也就是拉取试卷的过程，那么首先考虑可否使用缓存，而不是每次都去DB存储读取，因为试卷一旦建立，数据是固定的，而不会去更新，契合缓存使用的场景。

innodb_buffer_pool_size=8M
innodb_buffer_pool_instances=8
innodb_log_file_size=48M

调整后参数如下（关于这几个参数，请参考后续的原理解析）：

innodb_buffer_pool_size=5G

磁盘读的压力问题解决。

10.29 查看日志，发现主键重复问题（UUID），此问题是代码问题。
10.30 添加后台自动提交的功能（防止学生通过平板锁屏后的倒计时暂停作弊），发现DB连接无法获取的异常，将my.ini中max_connections由默认的156改为800解决
10.31 发现日志中偶尔存在死锁问题，系统抛出MySQLTransactionRollbackException，代码逻辑问题。
11.6 1200+人的考试，顺利完成，整个过程服务器压力不大，最后学校满意读很高。
12.19 期末考试第一场出现主键重复错误（MySQLIntegrityConstraintViolationException），调整写参数innodb_flush_log_at_trx_commit=0
12.20 一切正常（未出现重复主键错误，也就是说出现重复主键错误和写并发高有关，但为何并发写高时会出现此错误，需要查看随机数生成机理）

 

日志相关
1. innodb_log_buffer_size 当存在较大数据量的事务提交的时候，修改此参数会降低磁盘写
2. innodb_flush_logs_at_trx_commit （默认为1，对写性能要求高而对数据不是特别敏感时可以改为0或者2） 是不是每次commit刷redo

SQL查询的优化方法
> 提前终止查询（比如使用limit避免扫描全表）
> 不需要去重，则使用union all，否则中间的临时表会因为加上distinct而导致效率低下
> 如果是经常删除插入的表，可能存在大量的空洞，导致虽然表的数量量不大，但占用空间依然很大、查询效率地下，用下面的方法可清理空间
  alter table t engine=InnoDB （相当于recreate）
  optimize table t （相当于recreate+analyze）
  truntace table t (相当于drop+create)
> 充分利用索引（下边的索引会讲到）
充分利用MySQL影响性能的参数

这里也有必要提下MySQL的日志机制（分为redoLog和binLog），以下是SQL更新的过程

>项目中没有调整innodb_flush_logs_at_trx_commit默认值1（表示每次事务提交都会持久化到磁盘中），因为我们的数据用于学生考试，对精确性有一定的要求。

>如果磁盘写压力比较大，而平板端提交等待时间长的话（比如最后提交试卷），应该调大innodb_log_buffer_size，这个参数其实只是对于单次commit比较大的数据有用，表示在真正commit前如果达到了这个参数的峰值，会先写入磁盘中，极大降低单次提交的效率。这个参数在版本更新中屡次增大，在5.7.6之后默认为16M，足以应对绝大多数情况。

>项目增大innodb_log_file_size（默认48M），是为了减少磁盘的IO，这个参数控制的是redoLog的文件大小
所以如果这个参数过小，就会经常降低磁盘IO，推荐设置为innodb_buffer_pool_size的1/4
充分利用索引
因为当时项目中已经解决磁盘读的问题，所以没有在索引上进行优化。实际上，索引优化是非常值得学习的手段（特别是执行慢的代码段）
而对于执行慢的的SQL，可以使用慢查询方法来寻找，方法如下：
```
慢查询：查询时间长于long_query_time参数的设置（默认10秒），查询方法：show variables like 'long_query_time';
查看慢查询日志（slow_query_log）：show variables like 'slow_query%'; （默认不开启）
慢日志开启方法：my.cnf中设置slow_query_log =1 以及slow_query_log_file的路径
```
然后通过mysqldumpslow来找到执行慢的SQL

下面总结几条建立索引的一般规则
> 尽量使用自增主键索引，因为非主键索需要二次查询
> 利用覆盖索引来避免回表
> 利用前缀索来避免索引的字段过长
> 使用短字段建立索引
> 避免使用使索引失效的语句，比如
不等于（可转换为大于和小于）、
is null和is not null（如果失效可设置列默认值）、
or（如果失效使用使用union解决）、
in（如果失效使用between和exists替换）
now()函数、用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表、

学会监控MySQL的运行状态（找到慢查询）

show global status; // 显示所有状态，下面列出几个跟性能关系比较大的status

show status like 'Threads%'; // 显示的Threads_connected可用来标示并发数
show status like '%conn%'  // 显示的Connections是所有尝试连接的数量
show processlist 显示正在执行的MySQL连接，记录数与上面的Threads-connected相等 > 对应的配置项可以在配置文件中修改（windows的my.ini，linux的my.cnf），比如max_connections等

查看执行计划 （explain或desc SQL）
比如上面的执行计划结果是使用了SQL： explain SELECT * FROM t where c=10;
表t中，c为非主键索引，下面逐个字段做简单解释
> id是执行计划的顺序标示，如果有自查询，则id越大越先执行
> select_type：查询类型
simple：无子查询和union
primary：包含子查询的查询语句的最外层或union的第一句
subquery：子查询
derived：临时表
union：union中的第二个及以后的查询
union result：union的结果
> table：表名（可能为临时表的derived）
> type：访问类型（索引是否被使用需要用这个字段判断），以下是性能从低到高的类型
all：全表扫描
index：按照索引顺序进行全表扫描
range：范围扫描
ref：非唯一索引的匹配
const：唯一索引的匹配（本例）
system：查询表只有一行
null：不需要扫描表
> possible keys：与查询相关，可能使用的索引
> key：真正使用的索引（优化器的选择）
> key_len：使用的索引长度
> ref：显示某列或const被选择
> rows：预估查询行数
> Extra：补充信息，对于分析性能帮忙比较大，不再详列
关于事务，尽量避免死锁的可能、减少锁数据的时间
需要了解以下内容
> MySQL默认都是行锁
> 行锁都是对索引的锁
> 不要把查询语句放在事务开启和提交之间
> 默认锁级别为REPEATABLE READ（可重读），可通过更改TRANSACTION ISOLATION LEVEL，下面的四种级别的锁，就不再赘述了
SERIALIZABLE（序列化）、REPEATABLE READ（可重读）、READ COMMITTED（提交后读）、READ UNCOMMITTED（未提交读）