- 视频地址: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 c
key(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.sql
mysqlbinlog --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(有序)/时间范围 分库分表,扩容不需要迁移数据。但有热点问题,比如只有近期数据查询比较频繁。
两种方案结合:该内容暂无法显示 - 知乎
分库按范围、库中再取模分表。避免单表热点,不需要扩容迁移。
扩容后: