mysql优化类面试题（六）

骑猪看日落 2022-12-22 09:06 176阅读 0赞

**一、mysql的乐观锁，悲观锁，共享锁，排它锁以及对应的sql语句  **https://www.cnblogs.com/protected/p/6526857.html

**乐观锁：**乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做（**一般是回滚事务**）

**悲观锁：**悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）

**共享锁（S）**：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

SELECT \* FROM table\_name WHERE ... LOCK IN SHARE MODE。

**排他锁（X)**：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

SELECT \* FROM table\_name WHERE ... FOR UPDATE。

**二、数据库的隔离级别 **[https://blog.csdn.net/weixin\_39651041/article/details/79980202][https_blog.csdn.net_weixin_39651041_article_details_79980202]

**读未提交（Read Uncommitted）**：只处理更新丢失。如果一个事务已经开始写数据，则不允许其他事务同时进行写操作，但允许其他事务读此行数据。可通过“排他写锁”实现。

**读提交（Read Committed）：**处理更新丢失、脏读。读取数据的事务允许其他事务继续访问改行数据，但是未提交的写事务将会禁止其他事务访问改行。可通过“瞬间共享读锁”和“排他写锁”实现。

**可重复读取（Repeatable Read）：**处理更新丢失、脏读和不可重复读取。读取数据的事务将会禁止写事务，但允许读事务，写事务则禁止任何其他事务。可通过“共享读锁”和“排他写锁”实现。

**序列化（Serializable）：**提供严格的事务隔离。要求失去序列化执行，事务只能一个接一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

**三、mysql索引失效？**  
1、全值匹配  
2、最佳左前缀法则（带头索引不能死，中间索引不能断）  
3、不要在索引上做任何操作（计算、函数、自动/手动类型转换），不然会导致索引失效而转向全表扫描  
4、mysql存储引擎不能继续使用索引中范围条件（bettween、<、>、in等）右边的列  
5、尽量使用覆盖索引（只查询索引的列（索引列和查询列一致）），减少select \*\*\*  
6、索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描  
7、索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描  
8、索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描  
9、索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描  
10、索引字段使用 or 时，会导致索引失效而转向全表扫描

**四、慢查询优化的基本步骤**  
1、先运行看看是否真的很慢，注意设置SQL\_NO\_CACHE  
2、where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高  
3、explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）  
4、order by limit 形式的sql语句让排序的表优先查  
5、了解业务方使用场景  
6、加索引时参照建索引的几大原则  
7、观察结果，不符合预期继续从0分析

**五、数据库优化的思路**

**1、SQL语句优化**

1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null。可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

3）很多时候用 exists 代替 in 是一个好的选择

4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

**2、索引优化**

看上文索引

**3、数据库结构优化**

1）范式优化： 比如消除冗余（节省空间。。）

2）反范式优化：比如适当加冗余等（减少join）

3）拆分表： 垂直拆分和水平拆分

4、**服务器硬件优化**

这个么多花钱咯！

**六、MySQL 反应慢的排查思路**

**（一）导致 MySQL 慢可能的因素有**

1、计算资源不足

2、系统层面未进行基本的优化，或不同进程间资源抢占

3、MySQL 配置不科学

4、垃圾 SQL 满天飞

**（二）查看系统层面负载手段**

1、top 查看整体负载情况，快速确认哪个进程系负载高

2、free 查看内存情况，是否有内存泄露和用了 swap 等风险

3、vmstat/sar 查看当前系统瓶颈到底在哪，如 CPU、IO、网络等

4、终极神器 perf top 查看 cpu 消耗在哪些系统调用函数

**（三）查看 MySQL 的整体情况**

1、观察 show processlist 输出中是否有临时表、排序、大量逻辑读、锁等待等状态

2、观察 show engine innodb status 输出中是否有大事务、长事务、锁等待等状态

**（四）干掉垃圾 SQL，常用手段**

1、用 explain、desc 观察执行计划

2、用 profiling 定位 sql 执行的瓶颈

3、用 pt-query-digest 分析慢 sql

**（五）几个窍门**

1、mysqld 进程消耗 CPU 长时间超过 90%的话，99.9%是因为没用好索引

2、cpu 的%sys 高的话，大概率是 swap 或中断 不 均衡导致，也可能是有多个索引且超高

并发写入( 更 新)，或者有很严重的锁等待事件

3、最 ⼤ 的瓶颈通常是在磁盘 I/O 上，因此尽 量 用高速磁盘设备

4、如果物 理 磁盘无法再升级，则通过增加内存提升性能容 量

5、遇到无法诊断的问题时，试试 perf top 来观测跟踪

6、SQL 执 行 慢，有时未必是效率低，也可能是因为锁等待，甚至是磁盘满了

**七、慢日志管理**

慢日志  
            - 执行时间 > 10  
            - 未命中索引  
            - 日志文件路径  
              
        配置：  
            - 内存  
                show variables like '%query%';  
                show variables like '%queries%';  
                set global 变量名 = 值  
            - 配置文件  
                mysqld --defaults-file='E:\\wupeiqi\\mysql-5.7.16-winx64\\mysql-5.7.16-winx64\\my-default.ini'  
                  
                my.conf内容：  
                    slow\_query\_log = ON  
                    slow\_query\_log\_file = D:/....  
                      
                注意：修改配置文件之后，需要重启服务  
                  
                MySQL日志管理  
    ========================================================  
    错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息  
    二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作  
    查询日志: 记录查询的信息  
    慢查询日志: 记录执行时间超过指定时间的操作  
    中继日志： 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放  
    通用日志： 审计哪个账号、在哪个时段、做了哪些事件  
    事务日志或称redo日志： 记录Innodb事务相关的如事务执行时间、检查点等  
    ========================================================

### 日志命令： ###

一、bin-log  
1. 启用  
    \# vim /etc/my.cnf  
    \[mysqld\]  
    log-bin\[=dir\\\[filename\]\]  
    \# service mysqld restart

2. 暂停

//仅当前会话      
        SET SQL\_LOG\_BIN=0;  
       SET SQL\_LOG\_BIN=1;

3. 查看  
查看全部：

\# mysqlbinlog mysql.000002

按时间：

\# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"  
    \# mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"  
    \# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54"

按字节数：

\# mysqlbinlog mysql.000002 --start-position=260  
    \# mysqlbinlog mysql.000002 --stop-position=260  
    \# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930

4. 截断bin-log（产生新的bin-log文件）

a. 重启mysql服务器  
    b. \# mysql -uroot -p123 -e 'flush logs'

5. 删除bin-log文件

\# mysql -uroot -p123 -e 'reset master'

二、查询日志  
启用通用查询日志

\# vim /etc/my.cnf  
    \[mysqld\]  
    log\[=dir\\\[filename\]\]  
    \# service mysqld restart

三、慢查询日志  
启用慢查询日志

\# vim /etc/my.cnf  
    \[mysqld\]  
    log-slow-queries\[=dir\\\[filename\]\]  
    long\_query\_time=n  
    \# service mysqld restart

MySQL 5.6:

slow-query-log=1  
    slow-query-log-file=slow.log  
    long\_query\_time=3

查看慢查询日志  
测试:

BENCHMARK(count,expr)  
    SELECT BENCHMARK(50000000,2\*3);