本文深入探讨MySQL性能优化的关键策略，涵盖SQL语句优化、索引管理、存储引擎选择、事务处理、日志管理、安全措施及大规模数据操作技巧。解析慢查询日志，提升查询效率，确保数据库稳定运行。
- 视频地址：SQL优化（MySQL版；不适合初学者，需有数据库基础）_哔哩哔哩_bilibili，http://www.icoolxue.com/album/show/80
Mysql优化方面的面试题_晏霖/胖虎的博客-优快云博客_mysql优化笔试题
字段请使用utf8mb4字符集(字段级别配置)
3.对mysql优化时一个综合性的技术，主要包括（硬件、配置、设计、执行、规模）
SQL级
a: 表的设计合理化(符合3NF)
b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
e: 存储过程 [模块化编程，预编译，可以提高速度]
j: 推荐不要使用关联表过多的SQL
[由于开发人员水平不齐，简单SQL更易优化命中索引，也方便后期使用分库分表框架，且将部分聚合工作分摊给业务代码，业务代码做集群/分布式比数据库更加简单]
服务器级
c: 分库分表(水平分割、垂直分割)
d: 主从复制，读写分离 [写: update/delete/add]
f: 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小，默认为100，max_connection=1000 ]
部分数据库连接池会与mysql保持长连接不放，所以分布式/集群时注意保证数据库有充足的链接
g: mysql服务器硬件升级，比如固态硬盘SSD
h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)
I: 将历史数据转移
4. 3NF
1NF: 即表的列的具有原子性,不可再分解
2NF: 表中的记录是唯一的, 就满足2NF
3NF: 即表中不要有冗余数据
反3NF : 但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据，减少多表关联。
5 show status/variables like 'name' 检查配置
show status like 'uptime' ; （mysql数据库启动了多长时间）
show stauts like 'com_select' show stauts like 'com_insert' ...类推 update delete（显示数据库的查询，更新，添加，删除的次数）
show [session|global] status like .... 如果你不写 [session|global] 默认是session 会话，指取出当前窗口的执行，如果你想看所有(从mysql 启动到现在，则应该 global)
show status like 'connections';//显示到mysql数据库的连接数
show status like 'slow_queries';//显示慢查询次数 默认10s以上是慢查询 推荐缩小
show global variables like 'long_query_time'; //可以显示当前慢查询时间
set global slow_query_log='ON' //（临时开启慢查询日志，mysql重启时失效）
set long_query_time=1 ;//可以修改慢查询时间s（重启失效）
set global max_connections=500 ;//修改最大链接数
show variables like 'slow_query%' ：slow_query_log是否开启慢查询日志，1表示开启，0表示关闭。
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据<br>库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。
6 慢查询日志
记录超过long_query_time时间的sql语句
在默认情况下，我们的mysql不会记录慢查询，需启动mysql时候，指定记录慢查询日志 启动指令 bin\mysqld.exe - -safe-mode - -slow-query-log [mysql5.5 可以在my.ini指定]（安全模式启动，数据库将操作写入日志，以备恢复）；bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]
推荐临时开启：set global slow_query_log =1 （mysql重启时失效）
先关闭mysql服务,再启动, 如果启用了慢查询日志，默认把这个文件放在my.ini 文件中记录的位置
#Path to the database root
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"
mysqldumpslow 慢日志分析工具
命令：
-s 按照那种方式排序
c：访问计数
l：锁定时间
r:返回记录
al：平均锁定时间
ar：平均访问记录数
at：平均查询时间
-t 是top n的意思，返回多少条数据。
-g 可以跟上正则匹配模式，大小写不敏感。举例：
得到返回记录最多的20个sql:：mysqldumpslow -s r -t 20 /database/mysql/mysql_slow.log（日志路径）
得到平均访问次数最多的20条sql：mysqldumpslow -s ar -t 20 sqlslow.log
得到平均访问次数最多,并且里面含有ttt字符的20条sql：mysqldumpslow -s ar -t 20 -g "ttt" sqldlow.log
mysql 开启慢查询及其用mysqldumpslow做日志分析_keyboard专栏-优快云博客
MYSQL优化浅谈，工具及优化点介绍，mysqldumpslow，pt-query-digest，explain等 - moss_tan_jun - 博客园（包含win/linux下使用）
执行结果格式
命令解析:排序（-s）按执行次数（c）倒序（-a）
C:\Program Files\MySQL\MySQL Server 5.5\bin>perl mysqldumpslow.pl -s c -a c:\mysql\log\mysqlslowquery.log
Reading mysql slow query log from c:\mysql\log\mysqlslowquery.log
执行次数 Count: 3 执行时间Time=0.27s (0s) 锁定时间Lock=1.67s (1s) 发送行数Rows=1.0 (1), 执行地址root[root]@localhost
内容:select count(*) from lm_d_plan
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.22-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)SQL解析顺序
影响索引的最左命中。一定是解析顺序在前的字段，排在索引左边。
解析顺序 ： from..on..join..where..group by..having..seletc distinct..order by limit
Mysql多表连接查询的执行细节（一）_点滴之积-优快云博客_mysql多表连接查询
解析过程简述：
1、确定主驱动表(第一个被处理)
有A,B,C三个表做join查询。Mysql先要通过where条件推断结果集，没有命中索引或没有条件则按照全表计算，从小到大依次解析表。中间MySQL还会有一些缓存、join顺序、回表次数等条件优化，同行数的看where能否命中索引等。
假设推断后，A=10行，B=5行，C=15行 ，假定优化后解析顺序 ：B>A>C。确定主驱动表=B。
Explain中顺序最优先的表为主驱动表
2、开始解析执行，索引生效
B表：作为主驱动表，执行where条件取得中间结果1。此时where条件判定索引命中。
A表：B join A，则先通过On条件关联（on尝试命中索引），再通过where条件过滤（where尝试命中索引）。取得中间结果2
C表：A join C , 同A
可以看到索引顺序，除主驱动表外都是先on再where。外层表每循环一行都递进到内层表最终放入结果集
下面会有具体测试案例。
Nested-Loop 嵌套循环
for rowB in B表 filtered by where{
for rowA in A表 associated by on条件 filtered by where{
for rowC in C表 associated by on条件 filtered by where{
put into net-buffer then send to client;
放入结果集
}
}
}
7 索引 (主键索引/唯一索引/全文索引/普通索引)
primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个主键索引；
PRIMARY KEY 约束：唯一标识数据库表中的每条记录；主键必须包含唯一的值；主键列不能包含 NULL 值；每个表都应该有一个主键，并且每个表只能有一个主键。（PRIMARY KEY 拥有自动定义的 UNIQUE 约束）
primary key(name,age) #复合主键
unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个唯一索引；
UNIQUE 约束：唯一标识数据库表中的每条记录。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。（每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束）
unique index(`userId` ,`taskId`,`date`) #复合唯一索引
唯一索引注意 null 是可以重复的。例如：unique index(`userId` ,`phone`），可以重复插入不报错：（'A00001',null）。
因此在创建属于唯一索引的列时，最好指定字段值不能为空，在已有值为NULL的情况下，创建的字段不允许为空，且默认值为空字符。如果已经创建了默认值为NULL的字段，则先将其update为空字符，然后再修改为NOT NULL DEFAULT ‘’。
foreign key也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；
索引的代价：占用磁盘空间，dml操作变慢
适合建索引列:
a:字段在where,on,order,group经常使用
b: 该字段的内容多样化，唯一性高(容易缩小范围)
c: 字段内容不是频繁变化.
使用索引的注意事项:
a.单表查询一次只能命中一个索引。影响：查找，排序
多表查询时通过EXPLAN可以看到各个表的子查询命中的索引情况和执行顺序
b.unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复
c.复合索引从左边按顺序开始匹配，注意sql解析顺序，避免跨列引起索引失效。
把可能索引失效的列尽可能放在KEY最后
使用最频繁、唯一性高的，解析顺序靠前的字段 放在最左。
创建key(a,b,c)后，不用再创建（a,b）,(a)索引
d.BTREE全覆盖索引，查询时不用回表 。只要select的列全部包含在某一个索引中，就可以索引覆盖。
例：表A（c1,c2,c3） key(c1,c2) 。 select c2 from A 即可索引覆盖 Using index
i. join on 条件字段索引，= 左右哪个字段循环频率高，建立索引 。
定位驱动表： 左外联左表驱动，右外联右表驱动，内联自动小表驱动。内联可以通过表自身大小和EXPLAN解析select扫描的行数来判断那个表为驱动表。
一般情况：被驱动表on条件一定要加索引(条件允许也可两表都建立索引)。因为被驱动表一般是主表循环的内嵌套循环，执行频率高。
且where条件中尽量能够过滤一些行将驱动表变得小一点
l.主键自带索引，会自动加到复合索引的最右侧
J.TEXT，长字符串，若需要查询则可以建立前缀索引。建立前先观察不同长度前缀的区分度，确定前缀长度
索引失效总结: 索引命中不连续，显/隐式类型转换，列计算，null值判断，负向(否)条件，in (条件太多)，or，like ‘%a’，范围查询右边key（自身也可能失效），group/order by 。某些情况是概率事件，不一定百分百触发。
e.对于使用like的查询，查询如果是 ‘%aaa’ 不会使用到索引 (索引失效)
f.某列是范围查询，其索引右边列无法使用索引。(索引失效，多范围条件注意)
例如：key(a,b,c) => where a=1 and b>0 and c<0 => 只会用到key(a,b)或key(a) 有不确定性
g.如果条件中有or,要求or包含的所有字段都必须建立索引，其中一个有索引也无效 (索引失效)
h.避免在where条件中进行 null值判断、in (条件太多)、负向条件(!=、<>、not in、not like、not exists ) 这会使索引失效，所以把这些列放在复合索引最后位置，或是优化为其他形式（例如：用left join + id=null 代替not in ）
j.不要在索引字段做操作（计算，函数，类型转换等），会引起索引失效。例如：where a+1=1
k.显式/隐式类型转换，会引起索引失效 例如：where name=1
H.可以使用全覆盖索引挽救索引失效
例如：select * from user where name like '%a%'，其必然索引失效。
可以建立key（name,id），改变SQL为： select name,id from user where name like '%a%' 这样虽然索引失效，但是可以挽救为use index。
L.group/order by 字段在同一个表，字段顺序与索引相同。所有字段排序同升同降。否则可能出现索引失效、临时表、文件排序...
8.sql语句的优化
避免索引失效
select：
a.可以使用关联来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。（5.5及以下）
b.确保关联查询 on/using 条件命中索引，被驱动表/大表 必须建立索引
c.left join 尽可能小表左驱动大表，join相当于for循环嵌套，次数少的循环放在最外层。inner join mysql会自动优化。也可以尝试用straight join 代替inner join，强制驱动表。条件 “=” 小表字段在左边
d.避免使用select *，减少IO负担。（经测试，字段多时间消耗有可能成倍增长）
e.发现扫描行数远大于结果，则考虑优化查询：使用覆盖索引/修改表结构/复杂查询分割多个小查询
f.尽可能使用索引覆盖扫描select
g.select ..from table where in/exists (子查询)，主查询结果ROW大用IN反之用exist
h.left join where id != null 可以代替 not in () 或是先插入临时表再delete。
join、between 可以代替 in ()。in中元素不要太多，否则必须被代替。
i.合理运用临时表，比如有一张全年表，现在只需要一个月数据，且这个数据后边要反复查询/处理，则先插入临时表，以后就不用处理全年表啦。
j.避免使用过多的join 关联表。于Mysql来说是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置
在Mysql中，对于同一个SQL 多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，
所占用的内存也就越大。如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性同时对于关联操作来说，会产生临时表操作，影响查询效率
Mysql最多允许关联61个表，建议不超过5个
k.拆分大sql变为小sql。大SQL:逻辑上比较复杂，需要占用大量CPU进行计算的SQL。MySQL 一个SQL只能使用一个CPU进行计算。SQL拆分后可以通过并行执行来提高处理效率
L.不会有重复值时使用UNION ALL 。UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作， UNION ALL 不会再对结果集进行去重操作
M.force index 强制使用某个索引。有时一条SQL能够命中多个索引，Mysql选择的并不是最优索引，需要人为指定命中。一般某一列有单独索引和联合索引时，手动指定单独索引key_len会小，性能会提高很多。
group：
a.在使用group by 分组查询是默认分组后，还会排序，可能会降低速度。
在group by 后面增加 order by null 就可以防止排序.
b.group by/order by 只涉及一个表的字段，才能使用索引。group by 可以拆分成子查询，从而使其只涉及一个表。
c.用join关联条件做group by条件，效率更高
d.尽量不要group by 不同表字段，这将强制使用临时表和 filesort
limit：
a.尽可能使用索引覆盖扫描select
b.order by 命中索引
c.当只需要一条结果时手动limit 1，可以有效减少扫描量。
d.大表分页优化。例如：表有主键ID自增，每页有10条数据，想显示第二页数据，传统思路limit 11,10。
方法一：select id from x limit 11,10。通过全覆盖索引不回表查找当前页的10个ID，再join查出页面信息。
方法二：where id>10 limit 10。
order by :
a.避免使用select *
b.选择单路（4之后默认）/双路排序方法，设置max_length_for_sort_data扩大排序缓冲区
c.复合索引，按照解析顺序不要跨列
d,排序字段，同升同降 order by a desc,b desc
e.尽量不要order by 不同表字段，这将强制使用临时表和 filesort
优化举例：
例1：
order by 举例对比：有索引 key（a,b,c,d）
where +order by 从左命中不要跨列
select a,b,c,d from table where a=1 and d=1 and c=1 and b=1
(using index全覆盖查询， 实际用到 key（a,b,c,d）)
select a,b,c,d from table where a=1 and b=1 and d=1 order by c
(using index,using where 回表查询 ，因为从左命中d跨列导致d无法用到索引 ，实际使用 key（a,b）)
select a,b,c,d from table where a=1 and d=1 order by c
(using index,using where,using fliesort 回表查询+文件排序：因为从左命中orderby跨列 key（a）)
select a,b,c,d from table where a=1 and d=1 order by b,c
(using index,using where ：对比上个例子没有using fliesort，因为where +orderby没有索引跨列，但是由key_len可知道，实际使用只有 key（a），只是避免了文件排序)
例2：
Sql解析顺序影响索引命中，in()可能出现索引失效
select b from table where a=1 and c in (1,2) order by ckey（a,c,b） 效果最好，受Sql解析顺序影响，b放在where条件之后
索引中将c放在a之后，即使c in ()索引失效, a 索引还是有效
例3：
多表JOIN 解析对比索引命中。请先阅读上面的"sql解析顺序"
表user_zhw： 列( userid,zhwid,beizhu ) union_key(userid,zhwid) 当前 7 rows
表zhwdoc： 列(id ,name) KEY(id) 当前 3 rows
select * from user_zhw
join zhwdoc on zhwdoc.id=user_zhw.zhwid
where zhwdoc.name ='prouser'
and user_zhw.userid=1只执行前两行：解析
同ID由上至下执行，sql解析顺序规则(见上面)，先锁定结果可能最少的表，因为只执行前两行无where，但是zhwdoc总行数少所以其为主驱动表。zhwdoc先执行where过滤因为没where所有ALL扫描。之后on条件关联user_zhw表没有命中索引ALL扫描。扫描行数3+7*3。
执行前三句：解析
同ID由上至下执行。通过where条件确定zhwdoc结果集较小作为主驱动表。
zhwdoc先执行where过滤，没有命中索引ALL扫描。之后on条件关联user_zhw表没有命中索引ALL扫描。扫描行数3+7*3。
全部执行：解析
同ID由上至下执行。 通过where条件确定user_zhw命中索引其结果集较小作为主驱动表。
user_zhw先执行where过滤且命中部分索引(len)。之后on条件关联zhwdoc表索引命中。扫描行数1+1*1。
例4： 误区
表user_zhw： 列( userid,zhwid,beizhu ) union_key(userid,zhwid) 当前 7 rows
select userid,zhwid from user_zhw where zhwid=1 
上面where条件应该无法命中索引，但是解释结果命中了全部索引union_key长度，但是看扫描行数为全表扫描。 此处命中的索引是因为Using index，既索引全覆盖，select字段不用回表查询。where条件并没有命中，所以还是全表扫描。多表join时也要特别注意，不要只看key列，一定要以rows为准。但是也说明在where无法命中情况下 ，全覆盖索引能稍微弥补
小米SOAR
小米的SQL优化改写框架。
SOAR优化规则:小米soar mysql优化规则_一个路过的小码农~的博客-优快云博客
9.EXPLAN 执行计划
a.EXPLAN是近似结果，可能与真相相差甚远，且只能解释select
b.id,type,key,row,extra几个字段比较关键
id：表示执行优先级，由大到小执行，id相同时由上到下执行
type：ALL/index/range/ref/eq_ref/const/system/null 效率由低到高，一般以range/ref为目标
ALL：按行顺序全表扫描。一般百万以上出现ALL则必须优化
index：按索引顺序全表扫描，避免了排序。缺点是承担按索引顺序读取的开销
range：范围扫描。有范围的索引扫描。（between,><,in,or 且命中索引）
ref：索引访问/查找。返回所有匹配当个索引列的结果。
例如：select name from T where name="a" (name命中索引)
key：实际使用索引(where\on\select)，只能命中一个。5.0后有索引合并
多表查询时通过EXPLAN (id)可以看到各个表的子查询命中的索引情况和执行顺序
key_len：判断使用索引前几个字段，例如 KEY(A,B,C) 使用到（A,B,C）和（A,B）长度不同
rows：最重要的依据（直观检验），预估该表扫描到的行数。这里注意在mysql里边JOIN是嵌套链接，所以，需要把所有rows相乘就会得到查询数据行关联扫描的次数。rows代表此表单次join嵌套需要扫描行数。
filtered：实际显示行数占扫描rows的比 。实际显示的行数 = rows * filtered / 100
extra：
using index :使用覆盖索引(不回表)
using where :可能部分受益于索引(需回表)
using temporary :需优化，排序使用临时表（group by 未命中索引）
using fliesort :需优化，文件排序 IO消耗（order by 未命中索引，where +orderby 从左命中不要跨列）
using join buffer : 需优化。
select_type:
示查询中每个select子句的类型
(1) SIMPLE(简单SELECT,不使用UNION或子查询等)
(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果)
(6) SUBQUERY(子查询中的第一个SELECT)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
Show Profile指令
MySQL高级知识（十一）——Show Profile - developer_chan - 博客园
# set profiling =on; 开启监控，非调优环境请关闭 。
show profile cpu,block io;
查看单sql语句执行各个阶段的耗时、CPU、IO使用情况。
show processlist指令
查看客户端链接信息，重点关注有没有大量Sleep状态的客户端将链接占满。
10 存储引擎(针对单表)
myisam,请一定记住要定时进行碎片整理
innoDB vs myisam：MyISAM与InnoDB 的区别（9个不同点）_张花生的博客-优快云博客_innodb和myisam的区别
InnoDB 支持事物+行级锁+支持外键+支持MVCC多版本快照实现隔离性，不支持全文索引，B+聚集索引。
myisam 查询速度更快、全文索引、B+非聚集索引。(适合只读表)
聚簇索引与非聚簇索引
聚簇索引和非聚簇索引(通俗易懂 言简意赅) - 创天创世纪 - 博客园
聚簇索引InnoDB ：B+树、主键索引保存数据、非主键索引保存主键
非聚簇索引Myisam ：B+树、索引保存数据只保存地址指针
隔离性
11 锁
1 加行锁(命中索引)：
互斥锁X,其他线程不能再在此行上任何锁，其他线程可读不可写，直到当前线程提交事务。insert /update /select ...for update 。
共享锁S，所有可读不可写，共享锁可以叠加，之前的所有共享锁都解锁才能上互斥锁。select ...lock in share mode。
普通的select只要不尝试加锁，就不产生竞争或阻塞。
commit 时解除锁。 无论通过哪个索引命中此行，只要此行被锁住，其他线程都不能更新此行
特别注意： update table set name='hh' where id=3 and age>1000; id是主键。这句即使没有行满足where条件也会锁住id=3这一行。
2 没有命中索引，行锁会转为表锁
坑1：update table set name='sw' where name=1 ,假设有KEY(name),但是name=1发生类型转换，导致索引失效，执 行update产生表锁
表锁要等待所有行锁+表锁解锁
3 间隙锁：mysql为范围内不存在的行加锁
例如：update table set name='hh' where id<=3; 有key(id)。即使当前表中还没有id=2的行，mysql也会锁住id in (1,2,3) 三行内容。在update未提交之前，读/写/插入id=3的数据，就会阻塞
4 乐观锁（版本号 or 时间戳 or 临界条件）
提高数据库并发能力，避免超买超减等问题。本质上是CAS，要保证比较和赋值是原子性操作
比如： 在事务A中使用 update spkc set shl=shl-1 where id=1 and shl>0，判断受影响rows决定是否扣减成功是否向下执行。事务A执行update会上锁（注意命中索引，否则会上表锁），其他事务需要等待事务A提交再执行update。
Mysql 事务隔离性_shanchahua123456的博客-优快云博客
5 避免死锁，加速事务提交
https://825635381.iteye.com/blog/2339434
5.1 以固定的顺序访问表和行，这样就避免了交叉等待锁的情形
5.2 大事务拆小、缩短事务提交时间、避免事务中有复杂逻辑/远程调用。
一定要保证事务方法不要无休止的不提交事务。比如方法中等待http/远程响应，或复杂逻辑等情况导致不能提交。
5.3 在同一个事务中，尽可能做到一次锁定所需要的所有资源。
5.4 为表添加合理的索引，命中索引上行锁。避免表锁，不容易死锁
5.5 避免gap锁
5.6 并发插入出现duplicate key重复键异常时，当前事务会默认加上S锁。这时当前事务再去申请X锁，就会死锁。
可以查看死锁日志
查看锁表
方法一 慢查询日志
方法二 MYSQL中执行
MYSQL 表锁情况查看_好记忆也需烂博客-优快云博客_mysql查看表锁情况
-- 查看那些表锁到了
show OPEN TABLES where In_use > 0;
--In_use列表示有多少线程正在使用某张表，Name_locked表示表名是否被锁
-- 查看进程号
show processlist;
--删除进程
kill 1085850；
--查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
--查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 12 日志
mysql日志的种类，一般来说，日志有五种，分别为：
错误日志：-log-err (记录启动，运行，停止mysql时出现的信息)
二进制日志：-log-bin （记录所有更改数据的语句，还用于复制，同步，恢复数据库用）
查询日志：-log （全部记录建立的客户端连接和执行的语句，量大，不建议生产中使用）
慢查询日志: -log-slow-queries （记录所有执行超过long_query_time秒的所有查询）
查询当前日志记录的状况：
mysql>show variables like 'log_%';（是否启用了日志）
mysql> show master status;（怎样知道当前的日志）
mysql> show master logs;（显示二进制日志的数目）
Binlog
mysql查看binlog日志内容_天涯的浪子-优快云博客_查看binlog
Binlog会记录除了show select以外的所有语句。
开启log：
编辑 vi /etc/my.cnf
加入以下
server_id=1
log_bin = mysql-bin
binlog_format = Mixed
expire_logs_days = 30
查询log：
show variables like 'log_%'; （查看日志开启和保存路径）
show global variables like "binlog%";(查看模式)
show binary logs;(显示现有的binlog文件名)
show binlog events in 'mysql-bin.000002';（显示mysql-bin.000002）
binlog记录格式：
binlog_format=Statement \ Row \ Mixed
Statement：记录sql语句 Row：记录行号 Mixed：前两种的混合
通过上边指令锁定日志文件路径
在linux中mysql执行(解析时间较长,注意缩短datetime)
docker需要进入运行的mysql容器执行
利用binlog通过时间区间 生成.sql脚本
mysqlbinlog --start-datetime="2017-08-14 16:30:29" --stop-datetime="2017-08-14 17:30:31" --database=test mysql-bin.000893 >1.sqlmysqlbinlog --start-datetime='2019-01-01 00:00:00' --stop-datetime='2019-06-18 23:01:01' -d 库名 /var/lib/mysql/binlog.000003
若打印的SQL语句为乱码，则需要base64解析 --base64-output=decode-rows
mysqlbinlog --base64-output=decode-rows -v --start-datetime='2019-01-01 00:00:00' --stop-datetime='2019-06-18 23:01:01' -d 库名 /var/lib/mysql/binlog.000003
执行SQL:update user set beactive='n' where id=1 （user 表有三个字段）
可以看到update-log显示了所有字段的新旧状态。
13 安全
1 防止sql注入，所以最好使用预编译SQL执行 #{}
例如：注入 "or 1=1 " 作为条件欺骗
2 字符尽量使用单引号。尤其是sql_mode='ANSI_QUOTES'时，双引号会当识别符处理，引起条件丢失/赋值错误
14 大量数据操作
读10万数据
1 以JAVA为例，多线程+where条件分批并行读，每个任务读2两万，最后将list汇总到一个list中。注意OOM
2 mybatis 执行jdbc的流式读取
写insert
过大数据的（100万）批量写操作要分批多次操作
0.使用批量insert减少IO
1.大批量操作可能会导致严重的主从延迟
2. binlog日志为row格式时会产生大量的日志
大批量写操作会产生大量日志，特别是对于row格式二进制数据而言，由于在row格式中会记录每一行数据的修改，我们一次修改的数据越多，
产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因
3. 避免产生大事务操作
大批量修改数据，在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对MySQL的性能产生非常大的影响
特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批
4.对于大表的修改使用pt-online-schema-change
1.原理： 会在原表的结构上建造一个新表 复制数据
2.避免延迟，修改时锁表
5.禁止super权限滥用
6.数据账号连接最小15 Mysql缓存机制
mysql 缓存机制_qzqanzc的博客-优快云博客_mysql 缓存
mysql自身也有缓存机制，可以很大提高重复查询的执行效率
表碎片整理
OPTIMIZE TABLE table_name 会锁住整张表进行整理，且时间可能很长分区表
主键、索引、时间段等等作为分区条件...设置表内部分区，缩小查询扫描的范围。且select要将分区条件加入where/on条件。
失效：NULL值、对分区条件做列函数处理、以索引做分区条件但是未命中此索引.... 等都会造成全表扫描。
可以通过EXPLAN查看select扫描的分区。
MySQL高级特性一：分区表_yongqi_wang的博客-优快云博客_mysql 分区表
MySQL性能优化(六)：分区_monday的博客-优快云博客_mysql 优化 分区
分库分表
Id/Hash取模方案：分布均匀没有热点问题，但扩容后取模基数改变，需要迁移数据。
避免扩容迁移：记录下取模生成ID时的表数和库数，方便复现取模运算。或直接记录保存的目标表/库ID。例如：fid=唯一id(10位) + 当前分表数(两位) +当前分库数(两位) ，查询时可以通过ID中原分表数再次取模定位，扩容后也不影响，不用迁移原数据。例如：雪花算法中可以保存ID生成时的 时间戳+数据中心ID+机器ID。
Range方案：Id(有序)/时间范围 分库分表，扩容不需要迁移数据。但有热点问题，比如只有近期数据查询比较频繁。
两种方案结合：该内容暂无法显示 - 知乎
分库按范围、库中再取模分表。避免单表热点，不需要扩容迁移。
扩容后：
扫一扫
1698
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
