3. 查询截取分析
- 分析
- 分析，至少跑一天，看看生产的慢 SQL 情况
- 开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
- explain+慢SQL分析
- Show Profile
- 运维经理 or DBA 进行SQL 数据库服务器的参数调优
- 总结
- 慢查询的开启并捕获
- explain+慢SQL分析
- show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
- SQL数据库服务器的参数调优
3.1 查询优化
-
永远小表驱动大表
- 优化原则：小表驱动大表，即小的数据集驱动大的数据集。
-
对于以下两种情况：for(int i = 0 to 5;...){for(int j = 1000;){} }for(int i = 0 to 1000;...){for(int j = 5;){} }在程序中都一样，但是在 mysql 中要选择上面的，因为 i=0 to 5 只建立了5次连接；第二种连接了 1000 次 - in 与 exists：
-
select * from A where id in (select id from B) # 等价于： for select id from Bfor select * from A where A.id = B.id # 当B 表数据集小于 A 表的数据集时，用 in 优于 exists -
select * from A where exists (select 1 from B where B.id = A.id) # 等价于： for select * from Afor select * from B where B.id = A.id # 当A 表数据集小于 B 表的数据集时，用 exists 优于 in -
提示：# exists公式 select ...from table where exists(subquery) # 将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE) 来决定主查询的数据结果是否得以保留。- EXISTS(subquery) 只返回 TRUE 或 FALSE，因此子查询中的 SELECT *也可以是 SELECT 1 或其他（select ‘X’ 也可以），官方说法是实际执行时会忽略SELECT 清单，因此没有区别
- EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
- EXISTS 子查询往往也可以用条件表达式、其他子查询或者 JOIN 来替代，何种最优需要具体问题具体分析
-
-
类似嵌套循环Nested Loop
-
order by关键字优化
- ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
- 对于建立了复合索引的age,birth字段：
explain select * from tbl where age > 20 order by age # 不会产生 filesort，range 没有导致索引失效，因为order by 是在 where 筛选之后进行排序，并且根据索引天然有序explain select * from tbl where age > 20 order by age,birth # 不会产生 filesortexplain select * from tbl where age > 20 order by birth # 产生 filesort；原因：排序顺序和索引相同或者是索引的前缀就可以，否则会产生 filesort，此处和 range 导致失效无关explain select * from tbl where age > 20 order by age,birth # 产生 filesort；原因：和上面相同，排序顺序和索引不相同explain select * from tbl where birth > value order by birth # 分析：索引失效，并且·产生 filesortexplain select * from tbl where birth > value order by age # 不会产生 filesortexplain select * from abl order by age asc , birth desc # 产生 filesort：原因：默认升序，如果需要降序且两个顺序不同就会产生 filesort # 如果都是降序也可以使用索引 - order by满足两情况，会使用Index方式排序：
- ORDER BY 语句使用索引最左前列
- 使用Where子句与Order BY子句条件列组合满足索引最左前列
- 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
- 如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序
- 双路排序：MysQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，
读取行指针和orderby列，对他们进行排序，然后扫描己经排序好的列表，按照列表中的值重新从列表中读
从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。 - 要对磁盘进行了两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。
- 单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。
- 单路存在问题（单路失效）：sort_buffer小导致一次无法取完所有数据，每次取一个 buffer 的大小，排完再取
- 单路优化策略：
- 增大sort buffer size参数的设置
- 增大max length for_sort_data参数的设置
- order by 不要加 select * ：因为更容易把 buffer 缓冲区占满
- 双路排序：MysQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，
- 小总结：为排序使用索引
- MySql两种排序方式：文件排序或扫描有序索引排序
- MySql能为排序与查询使用相同的察引
- 哪些情况可以使用索引排序：
- order by 能使用索引最左前缀
- ORDER BY
- ORDER BY a,b
- ORDER BY a, b. c
- ORDER BY a DESC. b DESC, C DESC
- 如果WHERE使用索引的最左前煲定义为常量，则order by能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY C
- WHERE a = const ORDER BY b.c
- WHERE a = const AND b > const ORDER BY b. c
- order by 能使用索引最左前缀
- 不能使用索引排序
- ORDER BY a ASC, b DESC. C DESC /小排序不一致/
- WHERE g = const ORDER BY b, c /丢失a索引/
- WHERE a = const ORDER BYC /丢失b案引/
- WHEREa = const ORDER BY a.d /d不是索引的一部分/
- WHERE a in (…) ORDER BY b, c /对于排家来说，多个相等条件也是范围查询/
-
GROUP BY关键字优化（几乎和 order by 一致）
- group by实质是先排序后进行分组，遵照索引建的最佳左前缀
- 当无法使用索引列，增大max_length_ for_sort _ data参数的设置+增大sort buffer_size参数的设置
- where高于having，能写在where限定的条件就不要去having限定了。
3.2 慢查询日志
- 是什么
- MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值 的SQL，则会被记录到慢查询日志中。
- 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
- 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。
- 默认情况下，MysQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
- 当然，如果不是调优需要的话，—般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。馆
查询日志支持将日志记录写入文件
- 怎么用：
- 查看：
默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置 slow _query_log的值来开启mysql> show variables like '%slow_query_log%'; +---------------------+------------------------------------------+ | Variable_name | Value | +---------------------+------------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /usr/local/mysql/data/Neptune-2-slow.log | +---------------------+------------------------------------------+ 2 rows in set (0.00 sec) - 开启：
使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效,如果MySQL重启后则会失效。mysql>$ set global slow_query_log=1; Query OK, 0 rows affected (0.00 sec)
如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
修改my.cnf文件，[mysqld]下增加或修改参数 slow_query_log 和slow_query_Jog_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件slow_query_log =1 slow_query_log_file=/var/lib/mysql/atguigu-slow.logmysql> show variables like '%slow_query_log%'; +---------------------+------------------------------------------+ | Variable_name | Value | +---------------------+------------------------------------------+ | slow_query_log | ON | | slow_query_log_file | /usr/local/mysql/data/Neptune-2-slow.log | +---------------------+------------------------------------------+ 2 rows in set (0.00 sec) - 慢查询筛选标准：
- 这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：
也可以在 my.cnf修改。mysql> SHOW VARIABLES LIKE 'long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
10 指的是大于 10 秒的查询，不包括 10 秒 - 设置慢的阈值时间：
需要重新连接或新开一个会话才能看到修改值。$ set global long_query_time=3;mysql> SHOW VARIABLES LIKE 'long_query_time%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 3.000000 | +-----------------+----------+ 1 row in set (0.00 sec)
- 这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：
- mysqldumpslow 工具：（mysql 高级 47 节）
- 查看：
3.3 批量数据脚本
3.4 Show Profile
3.5 全局查询日志
4. MySql 锁机制
4.1 概述
- 定义：
- 分类：
- 从对数据操作的类型分
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
- 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
- 从对数据操作的粒度分
- 表锁：
- 行锁：
- 从对数据操作的类型分
4.2 三锁
- 表锁（偏读）
- 特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
- 案例分析：
- 建表：用 myISAM 作为数据库引擎
- 增加表锁：
# 手动增加表锁 lock table tableName read/write, tableName read/write - 读锁：
session_1 Session_2 对mylock 表加读锁 不加任何锁 当前 session 可以查询表记录 其他 session 也可以查询该表记录（读共享） 当前 session 不能查询其他没有锁的表 其他 session 可以查询或更新未锁定的表 当前 session 中插入或者更新锁定的表都会提示错误 其他 session 插入或者更新锁定表会一直等待获得锁 释放锁 session2 获得锁，插入操作完成 - 写锁：
session_1 Session_2 对 mylock 加写锁 不加锁 可以查询自己加锁的表 可以查询未加锁的表 可以修改自己加锁的表 可以修改未加锁的表 不能读取其他未加锁的表，会报错 查询被加写锁的表会被阻塞 释放锁 session2 更新成功 - 总结：
锁类型 可否兼容 读锁 写锁 读锁 是 是 否 写锁 是 否 否
- 表锁定分析：
- 可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定：
- SQL: show status like 'table%：
- 这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：
- Table _locks_immediate： 产生表级锁定的次数，表示可以立即获取锁的查询次数， 每立即获取锁值加1
- Table locks waited：出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况；
- 此外，Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞
- 行锁（偏写）：
- 特点：
- 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁：锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
- InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION)：二是采用了行级锁
- 行锁支持事务：
- 复习事务：事务的 4 大特性：ACID 原子性、一致性、隔离性、持久性
- 原子性(Atomicity)：原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。
- 一致性(Consistent)：一致性是指事务执行前后，数据从一个 合法性状态变换到另外一个 合法性状态。这种状态是 语义上的而不是语法上的，跟具体的业务有关。例如银行转账一边扣 100，另一边要加 100
- 隔离性 (Isolation)：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
- 持久性(Durable)：事务完成之后，它对数据的修改是永久性的，即使出现故障也可以保持
- 并发事务问题：
- 更新数据丢失：多个事务同时更改同一行数据，造成只有最后一个修改进行了覆盖，其他修改丢失
- 脏读：读取事务修改过程中的数据，读未提交
- 不可重复读：读取的过程中其他事务提交了修改，两次读取结果不一样（读已提交）
- 幻读：在可重复读的隔离级别下，读取了其他事务提交的 添加的新数据，两次读取的数据总量不同
- 幻读和不可重复读区别：幻读在于 insert 和 delete 操作；不可重复读在于 update
- 数据库隔离性：
隔离级别 读数据一致性 脏读 不可重复读 幻读 未提交读 最低级别，只能保证不读取物理上损坏的数据 是 是 是 已提交读 语句级 否 是 是 可重复读 事务级 否 否 是 可序列化 最高级别，事务级 否 否 否 mysql 默认可重复读
- 复习事务：事务的 4 大特性：ACID 原子性、一致性、隔离性、持久性
- 案例分析：
- 建表：使用 innodb 引擎
- 操作：
- 关闭自动提交事务：
set autocommit = 0; - Session1修改表数据，session1 可以读取到修改，但是 session2 读取不到未提交的数据
- Session1 执行 commit 操作，session2 依然读取不到，session2 也执行 commit 后，就可以读取到更新操作
- 关闭自动提交事务：
- 行锁冲突演示：修改同一行
session1 session2 关闭自动提交事务 关闭自动提交事务 更新但是不提交，没有手写 commit 尝试更新，但是被阻塞 提交更新 解除阻塞，更新正常 commit 执行提交 - 行锁不冲突演示，修改不同行数据
session1 session2 关闭自动提交事务 关闭自动提交事务 修改第 4 行，修改成功 修改第九行，修改成功 - 索引失效行锁变表锁：表格里两行属性a(int)、b(varchar)，都分别建立了索引
session1 session2 Update tbl set a = 41 where b = 4000
这里 varchar 故意没有加引号，会导致索引失效update tbl set b = ‘9002’ where a = 9;
发现阻塞了，因为索引失效行锁变成表锁commit提交 完成修改，等待很久 commit 解释：在某些情况下，如果MySQL无法确定应该锁定哪些行，它可能会退化为表级锁定（table-level locking）。其中一种可能导致行级锁定退化为表级锁定的情况是索引失效。当执行一个数据修改操作时，如果MySQL可以通过索引精确地确定哪些行将被影响，那么它可以只锁定这些行。但是，如果索引无法使用，那么MySQL可能无法确定哪些行将被影响，因此它可能需要锁定整个表以保证操作的正确性。
- 间隙锁危害：
session1 session2 update tbl set b=‘0629’ where a>1 and a<6;
更新成功insert into tbl values(2,‘2000’)
更新被阻塞commit 更新成功 - 什么是间隙锁：
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的素引项加锁：对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。 - 解释：在刚才的案例里面，插入的 a=2在(1,6)这个范围里，所以更新失败，要保证数据的一致性和完整性。
- 危害：因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
- 什么是间隙锁：
- 面试题：如何锁定一行
# 打个起点begin;# 选择要锁定的行select * from tbl where a=8 for update;# 开始做自己的修改...# 完成之后 commitcommit; - 行锁总结：
- Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
- 但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比My/SAM高甚至可能会更差。
- 分析行锁：
Innodb_row_lock_time_avg：等待平均时长mysql> show status like 'innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | +-------------------------------+-------+ 5 rows in set (0.01 sec)
Innodb_row_lock_waits：等待总次数
Innodb_row_lock_time：等待总时长 - 优化建议：
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
- 合理设计索引，尽量缩小锁的范围
- 尽可能较少检索条件，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度
- 尽可能低级别事务隔离
- 特点：
- 页锁（介于两者之间，知道即可）
开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
5. 主从复制
- 复制的基本原理
- slave会从master读取binlog来进行数据同步
- 三步骤+原理图
- master将改变记录到二进制日志 (binary log）。这些记录过程叫做二进制日志事件，binary log events:
- slave将master的binary log events拷贝到它的中继日志 (relay log)；
- slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的
- 复制的基本原则：
- 每个slave只有一个master
- 每个slave只能有一个唯一的服务器ID
- 手个master可以有多个salve
- 复制的最大问题：延时
- 一主一从常见配置：
