大家好，我是小菜，一个盼望在互联网工作做到蔡不菜的小菜。可柔可刚，点赞则柔，白嫖则刚！
死鬼~看完记住给我来个三连哦！

MySQL再发一弹，不要再说不会了！

本文首要介绍 Mysql开发和面试中所必知的
本文较长，分为上下篇（可收藏，勿吃尘）
如有需求S @ s 9，能够参看
如有帮忙，不忘 点J L 7 v = Q C赞 ❥

【相关文章】

  • MySQL第一弹

一、查询截取分析

1）慢查询日志

  • MySQL 的慢查询日志是MySQL供应的一种日志记载，它用来记载在MySQL中响应时间逾越阀值的语句，具体指工作时间逾越long_quj V U 3 M Zerc } B ^ v #y_time值的SQL，则会被记载到慢查询日志中。
  • 具体指工作时间逾越long_query_time值的SQL，则会被记9 w j ~ 6 (载到慢查询日志中。long_query_time的默许值为10，意思是工作10秒以上的语句。
  • 我们能够查看哪些SQL超出了我们的最大& G K i S c J Y忍受时间值，比如一条SQL实行逾越5秒钟，我们就算慢SQL，希望能收集逾越5` & 1 Z , a b秒的sql，能够结合之前explain进行全面分析。

初步运用：
默许情况下，MySQL数据库没有敞开慢查询日志，需求我们手动– 1 / K来设置这个参数。
经过show variables like ‘%slow_query_log’ 查看是否敞开了慢查询日志

MySQL再发一弹，不要再说不会了！
在这儿刺进图片描绘

设置办法：

#以下办法只对其时数据库有用，MySQL重启后失效
setglobalslow_query_log=1;
setgloba{ } u i + e C ? ullong_query_time=1.0;
#首要从头联接或许新开一个会话才能看到批改值
setsessionlong_query_time=1.0;

永久收效就得批改 my.cnf

slow_query_log=1

#指定生成方位，假定没有指定默许生成host_name-slow.log
slow_queryx 5 # d_lX $ Q %og_file=/var/lib/mysql/cbuc_slow.log

敞开后假定long_que1 J d ( 7 M Rry_time没有指定，默许为10秒，那么假定工作时间正好等于long_query_tie的情况，并不会8 7 * 8 d ]被记载下来，也就是说在mysql源码里面# z + ? 0 t W L的判别是大于long_q5 0 @ ! $ Puery_time,而非大于等于
实验：

#手动制作一条慢SQL
selectsleep(9)

盯梢日志文件 ： tail -50f cbuc_slow.log

MySQL再发一弹，不要再说不会了！

查询其时系统Z U J O c i y中有多少条慢查询：

showglobalstatuslike'%Slow_queries%'

【配备小结】
在 my.ini或许my.cnf配备文件下的配备

show_query_log=1;
show_query_log_file=/var/lib/mh j r 5 +ysql/N w @ X (  c Hcbuc_slow.log
long_query_ti O Y & lme=3;
log_output=FILE

日志分析东西mysqldumpslow
查看mysqldumpslow的帮忙信息:

  1. s：是标明依照何种办法排序；
  2. c：访问次数
  3. l：确认时间
  4. r：回来记载
  5. t：查询行数
  6. al：平均确认时间
  7. ar：平均回来记载数
  8. at：平均查询时间
  9. t：即为回来前面多少条的q , + u N 4 I E h数据
  10. g：后边调配一个正则e 4 B I + +匹配形式，巨细写不灵敏

【r H N c运用参看】
1、 得到回来L r ? 1 h X G R记载集最多的10个SS P { E g ` i Q :QL

mysqldumpslow-* U K C u )s-t10/var/lib/mysql/cbuc_slow.+ ^ & [log

2、 得到访问次数最多的10个SQL

mysqldumpslow-s-c-t10/vE 4 ; 9 + nar/lib/mysql/cbuc_slow.log

3、 得到依照时间排序的前10条里面含有左联接的查询语句

mysqldumpslow-s-t-t10-g"leftjoin"/var/lib/mysql/cbuc_slow.log

4、 其他建议在运用这些指令是结合 | 和 more运用, 不然] + c z有C ` j ? ? H & 0或许呈现爆屏的情况

mysqldumpslow-sr-t10/var/lib/mysql/] ? { 8 o a 5 Lcbuc_slow.log|more

2）Show Profile

  • 是mysql供应能够用来分析其时会话中语句实行的资源耗费情况，能够用于SQL的调优的测量
  • 默许情况下，参数处于关闭状况,并保存最近15次的工作效果

【分析进程】

  • 查看是否支撑
#默许是关闭，运用前需求敞开
showvariableslike'profiling';
MySQL再发一弹，不要再说不会了！
  • 敞开
setprofiling=1;
MySQL再发一弹，不要再说不会了！
  • 测验
#工作两个SQL查看
select*fromtbl_empaleftjointbl_deptbona.deptId=b.id
select*fromtbl_emparightjointbl_deptbona.deptId=b.id

查看效果 ：

MySQL再发一弹，不要再说不会了！

MySQL再发一弹，不要再说不会了！

参数说明：

  • ALL：闪现全部的开支信息
  • BLOCK IO ：闪现块} I $ IO 相关开支
  • CONTEXT SWITCHES ：上下文切换相关开支
  • CL p ~PU ：闪现! 0 / CPU相关开支信息
  • IPC ：闪现发送和接纳相关开支信息
  • MEMORY ：闪现内存相关开支信7 0 y息
  • PAGE FAULTS ：闪现页面差错相关开支信息
  • SOUR# 3 & iCE ：闪现和Source_function,Sourceg i ] x_file,Source_line 相关的开支信息
  • SWAPS ：闪现交流次数相关开支的信息

3）大局查询y 0 , + $ Q ~ ( y日志

  • 配备启用
    在 mysql 的my.cnf或my.ini中设置
#敞开
general_log=1

#记载日K i x % c志文件的途径
general_log_file=/path/logfile

#输出格式
log_output=FILE
  • 编码启用
    指w C W ~ _令：set global general_log = 1;
    大局日志能够存放在日志文件文件中，也能够存放在MySQL系统表中。存放在日志中功用会更好一些，存储到表中：
    set global log_output = 'TABLE'
    此后，你所编写的sql 语句，将会记载到mysql 库里的 general_log 表，能够用下面的指令查看
    sD : q . _elect * from mysql.general_log

二、Mysql锁机制

1）概述

锁是核算机调和多个进程或线程并发访问某一资源的机制。P A J
在数据库中，除传统的核算资源（如CPU、RAM、I/O等）的争用以外，数据也是一4 ^ p * g + /种供许多用户同享的资源。怎样保证数据并发访问的共同性，有用性是全部数据库有必要处理的一个问题，锁^ L % f p u j冲突也是影响数据库并发访问功用y a y Y h的一个重要因素。从这个视点来说，锁对数据库而言显的特别重要，也更加杂乱。] s ^ , 5 S

【案例了解】
一件产品这个时分只需T & S一件库存，可是一重用A、B两个人要下单，那么是A下单成功还是B下单成功。
这种时分就要运用到事务，我们要先从库存表中取出物品数量，然后生成订单，付款成功后生成付款信息，再更新产品数量。这个流程中，我们需求运用到锁对q 5 z 7 K B s U T有限的资源进; , ; K %行保护，处理隔绝和并发问题。
【锁的分类】

  • 从数据操作的类型划分 （读/写锁）
  1. 读锁（同享锁）： 针对同一份数据，多个读操作能够一起进行而不会相互影响。
  2. 写锁（排W m y _ f ) _ n它锁）p ! j n p !： 其时写操作没有结束前，它会阻断其他! I X u写锁和读锁。
  • 从数据操作的颗粒度划分
  1. 表锁
  2. 行锁

2）三级锁

【表锁】
特b h g & : 2 ( { p点：（偏读）

倾向MyISAM存储引擎，开支小，加锁快；无死锁；确认粒度大；发生锁冲突的概率高，并发度最低。

  • 手动加锁：
locktable<table_name1><read/write>,<table_name2><read/write>
  • 查看表+ $ { E上加过的锁：
showd Y ?opentables;
MySQL再发一弹，不要再说不会了！
  • 开释表锁
unlocktables;

读锁说明：
新建两个session会话，session1 和sessionk ( R 1 g L2
此刻在session1中对mylock表进行read 确认，情况如下：

  1. session1能够查询该表的信息，session2也能够查询该表的记载
  2. session1中不能查询其他没有确认的表，session2能够查询和更新其它没有确认的表
  3. session1刺进或更新确认的表都会提示差错，session2刺进或更h + c Y h ; :新确认的表会一直等候。
  4. 当session1q . p w开释锁后，session2之前刺进或更新实行结束。

写锁说明：
同样新建两个session会话，session1 和session2
此刻在session1中对mylock表^ 6 } u E ,进行write 确认，情况如下：

  1. sL q ` a x Eession1 对确认表的查询+更新+刺进操作都能够实行，session2 对确认表的查询 被阻塞，需求等候锁的开释。可是假定session2之前有数据缓存，则能够读出缓存数据，一旦数据发生改动，缓存将失效，– : B = b |操作将被阻塞。

【小结】：
MyISAM在实行查询语句的前，会自动给触及的全部表加P = I读锁，在实行增删改操作前，会自动给触及的表加写锁。

锁类型 他人可读 他人可写
读锁 是 否
写锁 否 否

1、 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读恳求，但会阻塞对同一表的写a ) X %恳求，只需当读锁开释后，才会实行其他进程的写操作。~ % ` L M x
2、 对MyISN , S ,AM表的写操作（加写锁），会阻塞其他线程U g a + U对同一表的读和写操作，! X 3 N ^ Y r I只用当写锁开释后，才会实行其他进程的读E D % h写操作。
总结：读锁会阻塞写，可是不会阻塞读。而写锁则会把读和写都阻塞C 6 H 7 y 6 U # S

【行锁】
特色：) n S D M ( : ; ?（偏读）

倾向InnoDB存储引擎，开支大，加锁慢；会呈现死锁；确认粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB与MyISAM的最大不同有两点：

  • 支撑事务（TRANSACTION）
  • 采用了行级锁

事务m [ B温习：
事务6 n Q W O 4是由一组SQL语句组成的逻辑处理单元，事务: O V S m q H 0 |具有以下4个特色，通常简称为事务的ACID特色0 ; P 2 P ? ? .。

  • 原子性（Atomicity）： 事务是一个原子操作的单元，其对数据的批改，要么全部实行，要么全都不实行。
  • 共同性（ConsistenY ` + L f Yt）：在事务初步和O H F W T 8 x结束时分，数据都有必要坚持共同状况。这意味E s $着全部相关的数据o g l K M规矩都有必要应用于事务的批改，以坚持数据的完整性D f ;；事务结束时，全部内C 2 k t F S S *部的数据结构（如B树索引或双向链表）也都有必要是正确的。
  • 隔绝性（IsolatioX 3 q @ pn）：数据库系统供应一定的隔绝机制，保证事务在不受外部并发操作影响的“独立”环境实行。这意味着事务处理进程中的中间状况& t n l _ :对外部是; 6 L J H k % 4不可见的，反之亦然。
  • 持久性（Durable）：事务结束之后，它关于数据的批改是永久性的，即便呈现系统毛病也能够坚持。

并发事务处理带来的问题：

  • 更新丢D ` W c 2 M 2 6掉（Losi Q Z R C b $t Update）
    当两个或多个事务挑选同一行，然后依据开始选定的值更新该行是，因为每个事务都不知道其他事务的存在，就会发生丢掉更新的问题1 H _ ` — 最终的更新覆盖了由其$ , B ] r 5 9 E f他事务所做的更新。
  • 脏读（Dirty Reads）
    事务A读取到了事务B已批改但尚未提交的数据，还在这个数4 c z m * 3 2 n据基础上做了操作。此刻，假定B事务回滚，A读取的数据无N [ x % z效，D | I P H S ~不契合共同性要求。
  • 不可重复读（Non-Repeatablee [ t ReadsC / } 8 3）
    一个事务规划内两个相同的查询) [ p b _ 9 U ~却回来了不同数据。
  • 幻读（Phantom Reads）
    一个事务按相同的查询从头读取曾经检索过的数据，却发现其他事务刺进了满足其查询条件的新数据，这种现象就称为“幻读”。也就是说事务A读取到了事务B提交的新增数据，不契合隔绝性。

事务隔绝等级：

#查看事务的隔绝等级
showvariablelike'T , X F F xtx_isolate'
隔绝等级 读数据共同性 脏读 不可重复读 幻读
未提交读（Read uncommitted） 最低等级，只能保证不读取物理上损坏[ X M r 5 y | S ,的数据 是 是 是
已提交读（Read committed） 语句级 否 是 是
可重复读（Repeatable read） 事务级 否 否 是
可序列化（Serializable） 最高等级，事务级 否 否 否

写锁（排他锁）：
加上排它锁后，其他事务不能再对A加任何类型的锁。已获取到排它锁@ ^ G R r e的事务既能读数据，又能批改数据。

#经过这段加锁，mysql会对查询S , F . * 0 C &效果中的每行都加排他锁
select...for& 9 X [update;

空地锁：
当我们用规划条件而不是相等条件检索数据，并恳求同享或排他锁时，InnoDB会给契合条件的已有数据记载的索引项加锁；关于键值在条件规划内但并不存在的记载，叫做“空地（GAP）”
InnoDB也会对这个“空地”加锁，这种锁机制就是所谓的空地锁（GAP Lock）
损| ~ ~ H o 8 ! n O害：
因为Query实行进程中经过规划查找的话，他会确认整个规划内全部h i : ; R , W &的索引键值，即便这个键值G + C H T并不存在，空地锁有一个比较丧命的弱点，就是当确认一个规划键值之后，即便某些不存在的键值也会被无辜的确认，而形成在确认的时分无法刺进确认键值规划内的任何数据。在某些场( ? & : j 3 b = R景下这或许会对功用形成很大的危害
优化建议：

  1. 尽或许让给全部数据检索都经过索引j L C J y # D来结束，防止无索引行锁晋级为表锁。
  2. 尽或许较少检索条件，防止空地锁。
  3. 尽量控制事务巨细，削减确认资源量和时间长度。
  4. 锁住某行后，尽量不要去调其他f W $ e | E行或表，赶忙处理被锁住的行然后开释掉锁。
  5. 触及相同表的事务，关于调用表的次序尽量x X c ] @ U $ x U坚持共同。
  6. 在事务环境答应的情况下，尽或许低等级事务隔绝。
    【页锁】
    开支和加锁时间介于表锁和行锁之L F 1 R间，会出M ] 1现死锁i { Y j ^ | C ? 6；确认粒度介于表@ r – f Q 9 r锁和行H 1 e /锁之间，并发度一般。

三、主从拷贝

1）拷贝的基本原理

slave 会从 master 读取binlog来进行数据同步

【三个进程】

  • master将改动记载到二进制日志（binary log）。这些记载进程叫做二进制日志时间，binaS ; $ Gry log events
  • slave将master的binary log events拷贝到它的中继日志中（relay log）
  • slave重做中继日志中的事件，将改动应用到自己的数据库中，mysql X , _ ll拷贝是异步的且串行化的。
    MySQL再发一弹，不要再说不会了！

2）拷贝的基本原则

  • 每个slave 只需p _ h 1 S ) X ! 1一个master
  • 每个slave只能有一个仅有的服务器ID
  • 每个master能够有多个slave

拷贝的最大问题： 推迟

32 = = & @ I E M）主从常见配备

mysql 版别共同且后台以服务工作，主从配备都在[mysqld]结点下，都是小写
【主机批改my_ 9 |.ini配备文件】

  • [有必要] 主服务器仅有ID
server-id=1
  • [有必要] 启用二进v a – D 7 v制文件
log-bin=自己本地的途径/data/mysqlbin
log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
  • [可选] 启用差错日志
log-err=自Z } b {  C己本地的途径/data/m9 H m B . T q Z 0ysqlerr
log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr
  • [可选] 根目录
basedir& ; [="自己本地途径"
bau l b osedir=D:/devSoft/MySQLServer5.5/
  • [可选]j ^ z ! p 暂时目录
tmpdir="自己本地途径"
tmpdK H 2 ) D b 3 V vir=D:/devSoft/MySQLServer5.5/
  • [可选] 数据目{ * ` k U P 4 3 +录
datadir="自己本地途9 ) | h * | m ;径"
datadir=D:/devSoft/MySQLSer Z O * *  3 @ 6rver5.5/data/
  • read-only = 0
    主机读写都b y L @能够
  • [可选] 设置不要拷贝的数据库
bp 9 1 w E - ainlog-io 3 t ( C x M s ;gnore-db=mysql
  • [可选] 设置需求拷贝的数据库
binlog-do-db=需求拷贝的数据库的名字

【从机批改my.inK % ` # I : mi配备文件】

  • [有必要] 从服务器仅有ID

  • [可选] 启用二进制文件

    【批改后，主从机都需求重启后台mysql服务】
    【主从机都需求关闭防火墙` O q】
    【在wif b @ndows主机上建立账户并授权slave】

  • 进程1：

GRANTREPLICATIONSLAVEON*.*TO'zhangsan'@'从机的数据库IP'INDETIFIEDBY'123456'
  • 进程2：
flushprivileges;
  • 查看master状况
showmasterstatus;
#记载File和Position的值
MySQL再发一弹，不要再说不会了！
  • 实行完以上进程便不要再操作，防止主服务器状况值发生改动

【在Linux从机上配备需求} N 5 D f v W D拷贝的主机】

  • 进程_ O ! ) x1
changemastertomaster_host='主机IP'，
m, z )aster_user='zhangsan',master_password='123456',
master_log_file='file名字',
master_log_pos=position数字
MySQL再发一弹，不要再说不会了！
  • 进程2：
    启动从服务器拷贝功` } 0 P h 7 p用
startslave;
  • 进程3：
showslavestatus
#下面两个参数b P J 9 ; C ! 4 h都是Yes，便说明主从配备成功
Slave_IOe = l ) b D ; ^ A_Running：Yes
Slave_r S ASQL_running:Yes1 W j a g ?

【主机新建库，新建表，insert记载，从机便会拷贝】
【中止从服务拷贝功用】V G - $ G % T

stopslave;
MySQL再发一弹，不要再说不会了！
看完不赞，都是坏蛋

本文较长，能看到这儿的都是好样的，成长之路学无止境
今天的你多尽力一点，明天的你就能少说一句求人的话！
很久很久之前，有个传说，听说：
看完不赞，都是坏蛋