[面试]MySQL几个常见问题

1. SQL优化步骤

• 通过show status命令了解各种SQL的执行频率
• 定位执行效率较低的SQL语句
• 通过Explain分析效率低的sql的执行计划
• 通过show profile分析SQL
• 通过trace分析优化器如何选择执行计划

2. 死锁和死锁检测

概念：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

出现死锁以后，两种策略：

• 进入等待，直到超时，这个超时时间可以通过参数innodb_lock_wait_timeout来设置，在innodb中默认值是50s。
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行，将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

正常情况下采用第二种策略：主动死锁检测。但是需要注意一些特殊场景。

每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别的线程锁住，如此循环，最后判断是否出现了死锁。

每个新的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是O(n)的操作，假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万量级，虽然最终检测结构可能是没有死锁，但是这期间要消耗大量的CPU资源，这时就会看到CPU利用率很高，但是每秒执行不了几个事务。

怎么解决由热点行更新导致的性能问题？

• 简单粗暴的方式是通过业务层面保证不会出现死锁，可以临时关闭死锁检测。这有一定的风险，由于业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没有问题了，这是业务无损的。而关闭死锁检测意味着可能会出现大量的超时，这是业务有损的。
• 控制并发度，这个并发控制要在数据库服务端，如果有中间件，可以考虑在中间件实现。如果有实力修改MySQL源码，也可以在MySQL里实现。思路：对于相同行的更新，在进入引擎之前排队，这样InnoDB内部就不会有大量的死锁检测工作了。
• 笨办法，将一行改成逻辑上的多行来减少锁冲突。

3. 读写分离解决方案？

方案一：

• 使用mysql-proxy代理
• 优点：直接实现读写分离和负载均衡，不用修改代码，master和salve用一样的账号
• 缺点：降低性能，不支持事务。

方案二：

• 使用AbstractRoutingDataSource + aop + annotation在dao层决定数据源。
• 如果采用mybatis，可以将读写分离放在ORM层，比如mybatis可以通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，所有的select都访问salve库，这样对于dao层都是透明。plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。不过这样依然有一个问题，也就是不支持事务，所以我们还需要重写一下DataSourceTransactionManager， 将read-only的事务扔进读库， 其余的有读有写的扔进写库。

方案三：

• 使用AbstractRoutingDataSource+aop+annotation在service层决定数据源，可以支持事务。
• 缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理。

4. MySQL数据库cpu飙升到500%，应如何处理？

• show processlist，看看正在跑的session情况，是否有特别消耗资源的sql在执行，找出消耗资源高的sql，看看执行计划是否准确，index是否缺失，或者实在数据量太大造成的。
• 如果每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

5. 如何避免全表扫描？

• 应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描。
• 应尽量避免在where子句中使用!=或者<>操作符
• 应尽量避免在where子句中使用or来连接条件
• in和not in也要慎用
• 应尽量避免在where子句中对字段进行表达式操作