MYSQL5.5/5.7优化摘要

本文深入探讨MySQL性能优化的关键策略,涵盖SQL语句优化、索引管理、存储引擎选择、事务处理、日志管理、安全措施及大规模数据操作技巧。解析慢查询日志,提升查询效率,确保数据库稳定运行。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

  1. 视频地址:SQL优化(MySQL版;不适合初学者,需有数据库基础)_哔哩哔哩_bilibili,http://www.icoolxue.com/album/show/80
  2. 文档:韩顺平mysql优化笔记.doc_免费高速下载|百度网盘-分享无限制 

          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 myisamMyISAM与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 避免死锁,加速事务提交

             mysql死锁问题分析 - zhanlijun - 博客园

             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

MySQL 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自身也有缓存机制,可以很大提高重复查询的执行效率

表碎片整理

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(有序)/时间范围 分库分表,扩容不需要迁移数据。但有热点问题,比如只有近期数据查询比较频繁。

两种方案结合:该内容暂无法显示 - 知乎

分库按范围、库中再取模分表。避免单表热点,不需要扩容迁移。

扩容后:

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值