MySql学习第三部分（深入浅出MySql）_深入浅出mysql第三版匹配全值-CSDN博客

本文链接：https://blog.csdn.net/qq_34822710/article/details/111827568

一、 sql优化

1.定位慢sql

show processlist查询当前MySql在进行的线程，实时查看SQL的执行情况
show variables like '%slow_query_log%' 查看慢日志开启状态
set global slow_query_log = 1; 或者在my.cnf配置文件配置slow_query_log = 1
show variables like 'long_query_time%' 查看慢日志记录的时间阈值，sql执行时长达到该值则记录到慢日志
set global long_query_time = 4；设置慢日志记录的时间阈值
show variables like 'slow_query_log_file';查看慢日志存储位置

2.分析慢sql

(1) explain

通过EXPLAIN分析：explain sql；
EXPLAN结果各字段解析：select_type表示SELECT类型：SIMPLE（单表查询）、PRIMARY（主键查询）、UNION（UNION第二个或者后面的查询语句）、SUBQUERY（子查询第一个SELECT）；table；type：ALL（全表），index（索引），range（索引范围），ref（非唯一索引或唯一索引的前缀查询），eq_ref（唯一索引的前缀查询），const/system（单表最多有一个匹配行），NULL（不需要访问表或索引）

(2) profile

select @@have_profiling 查看是否支持MySql
set profiling = 1；设置profile
查看proflie：show profiles; show profilefor {all | cpu | block io | context switch | page faults} query id;

（3）trace

打开trace：set optimizer_trace="enabled=on",END_MARKERS_IN_JSON=on;
设置trace大小：set optimizer_trace_max_mem_size = 1000000;
检查跟踪文件：select * from information_schema.optimizer_trace;

3.sql优化措施

（1）索引

索引分类：B-Tree索引、HASH索引（memory引擎支持）、R-Tree索引（空间索引，主要用于地理空间类型）、Full-text（全文索引）、前缀索引（order by和group by无法使用）
B-Tree索引原理：构造平衡树，能根据键值提供一行或者一个行集的快速访问。
索引适用场景：匹配全值、匹配值的范围查询、匹配最左前缀（联合索引）、索引字段查询、匹配列前缀、索引字段匹配精确其他部分范围匹配、如果列名是索引情况使用is null也会使用索引
索引不适合场景：%开头的模糊查询、隐式转换类型、非最左原则（联合索引）、索引匹配范围大、or前的列有索引，后面没有索引
定期分析和检查表：分析表：ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name].. 检查表: CHECK TABLE tbl_name [,tbl_name] .. [option] .. option = {QUICK | FAST|MEDIUM|EXTENDED | CHANGED}
定期优化表：OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name] ..

索引支持索引表
索引	MylSAM引擎	InnoDB引擎	Memory引擎
B-Tree索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-Tree索引	支持	不支持	不支持
Full-text索引	支持	暂不支持	不支持

（2）常用的sql优化

大批量插入数据：ALTER TABLE tbl_name DISABLE KEYS； loading the data； ALTER TABLE tbl_name ENABLE KEYS(MylSAM) ；按顺序导入（InnoDB）；
优化insert：批量插入比多次单次插入
优化order by：尽量用索引字段排序，适当设置max_length_for_sort_data和sort_buffer_size，少用select *
优化嵌套查询：使用关联查询
or查询：复合索引不可用
优化分页查询：1.索引分页处理；2.用id记录分页数据游标

4.SQL技巧

（1）正则表达式

语法：regexp

正则表达式匹配
序列	说明
^	在字符串的开始处进行匹配
$	在字符串的末尾进行匹配
.	匹配任意单个字符，包括换行符
[...]	匹配括号内的任意字符
[^...]	匹配不是括号内的任意字符
a*	匹配零个或者多个a
a+	匹配1个或者多个a
a?	匹配零个或者1个a
a1\|a2	匹配a1或者a2
a(m)	匹配m个a
a(m,)	匹配m个或者更多a
a(m, n)	匹配m到n个a
a(, n)	匹配少于n个a，包括0和n
(...)	将模式元素组成单一元素

（2）随机行

语法：ORDER BY RAND()
说明：随机排序

（3）GROUP BY 的 WITH ROLLUP

语法：GROUP BY ...WITH ROLLUP
说明：检索本组类的整体聚合信息，注意不能和order by一起用

（4） BIT GROUP FUNCTIONS 统计

语法：BIT_OR（column），BIT_AND（column）
说明：适用于二进制存储的数据需要按业务进行处理统计的

（5）数据库名和表名大小写

说明：在UNIX中对大小写比较敏感，建议统一用小写

（6）使用外键需要注意的问题

InnoDB支持对外部关键字约束条件的检查，但是其他存储引擎，使用references tal_name（column）只是提醒作用

二、优化数据库对象

1.优化表的数据类型

分析：SELECT * FROM tbl_name PROCEDURE ANALYSE()
表拆分：1.垂直拆分：主码和常用列一张表、主码和其他列另一张表，需要联合查询；2.水平拆分：一列或者多列数据的值把数据放到两个独立表中（按数据拆分）
逆规范化：降低表连接：增加冗余列、增加派生列、重新组表、分割表；可以采用触发器来维护数据的完整性
使用中间表提高统计查询速度：

三、锁问题

1.简介

归类：表级锁：开销小、加锁快；行级锁：开销大、加锁慢；页面锁：介于表级和行级之间

2.MylSAM表级锁

查看锁：show status like "table%"
锁模式：表共享读锁：读锁不会阻塞读锁，但是会阻塞写锁；表独占写锁：阻塞其他锁
自动加锁：select 操作加读锁，update、delete和insert更新动作增加写锁
并发插入：concurrent_insert 为0表示禁用，为1表示表中没有空洞，允许读数据同时并发表尾插入数据，为2表示支持表尾插入数据
MylSAM的锁调度：默认写锁优先，可以通过low-priority-updates设置读优先，也可以通过max_write_lock_count设置读锁达到阈值，降低写锁优先
注意：使用LOCK TABLES显式加锁的时候，同一个表在SQL中出现多少次就要通过与SQL语句中相同的别名锁定多少次

3.InnoDB锁问题

事务：由一组SQL语句组成的逻辑处理单元，特性为原子性、一致性、隔离性、持久性
并发事务的问题：更新丢失、脏读、不可重复读、幻读
事务隔离级别：未提交读、已提交读、可重复读、可序列化
查看锁：show status like 'innodb_row_lock%';
查看锁等待：select * from innodb_locks;
锁冲突情况：设置：CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB; 查看：show engine innodb_status;
行锁模式：1.共享锁（S）：允许事务读，阻塞其他事务获得相同数据集的排他锁；2.排他锁（X）：允许事务更新，阻塞其他事务获得相同数据集的排他锁和共享锁；3.意向共享锁（IS）：加共享锁前先请求意向锁；4.意向排他锁（IX）：加排他锁前先请求意向锁
行锁实现方式：Record lock：对索引项加锁；Gap lock ：对索引项之间，第一条记录前或者最后一条记录后加锁；Next-key lock：记录以及前面的间隙加锁
Next-Key锁：使用范围条件检索数据，并请求共享或者排他锁时，InnoDB会给符合条件的已有数据的索引项加索，对于键值在范围内但是不存在的记录也会加锁。
恢复和复制的需要，对InnoDB锁机制的影响：复制模式：基于SQL语句的复制SBR，基于行数据的复制RBR，混合复制模式，使用全局事务ID的复制
表锁的适用场景：事务大量更新数据，并且表比较大；事务涉及多个表
避免死锁方法：按相同的顺序访问多张表；批量处理数据，先对数据排序；更新记录时应该直接排他锁，而不应先申请共享锁；
注意：如果不通过索引条件查询，InnoDB会锁住表中的所有数据；相同索引键会出现锁冲突；不同事务可以使用不同的索引锁定不同的行；分析锁冲突时，检查SQL的执行计划，以确认是否使用了索引

隔离级别比较
	读数据一致性	脏读	不可重复读	幻读
未提交读	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交读	语句级	否	是	是
可重复读	事务级	否	否	是
可序列化	事务级	否	否	否