signed

QiShunwang

“诚信为本、客户至上”

MySQL进阶

2020/12/28 20:29:40   来源:

数据库三大设计范式

  • 第一范式 1NF:数据表的所有字段都是不可拆分的原子值。

    比如:地址可以拆分为 国家、省份、市区、街道、门牌号等。不应该写到一起。方便统计。

  • 第二范式 2NF:满足 1NF 前提下,主键外的每一列都必须完全依赖于主键。如果不完全依赖,只可能是联合主键的情况。

    create table myorder(
    	product_id int,
        customer_id int,
        product_name varchar(20),
        customer_name varchar(20),
        primary key(product_id, customer_id)
    );
    

    除主键外的其他列,只依赖于主键的部分字段。pName之和pId有关,cName之和cId有关。

    拆表。

    create table myorder(
        order_id int primary key,
    	product_id int,
        customer_id int,
    );
    
    create table product(
    	id int primary key,
        name varchar(20),
    );
    
    create table customer(
        id int primary key,
        name varchar(20),
    );
    
  • 第三范式 3NF:满足 2NF 前提下,除主键列外其他列之间不允许有传递依赖关系。

    create table myorder(
        order_id int primary key,
    	product_id int,
        customer_id int,
        customer_phone varchar(15)
    );
    

    cPhone和cID是唯一相关的,产生了冗余。

事务管理

**事务定义:**一个最小的不可分割的工作单元。能够保证一个业务的完整性。

存储引擎(表类型?):在mysql的数据用不同的技术存储在文件(内存)中。

  • show engines查看。
  • 最多是innodb,myisam,memory等。innodb支持事务,myisam,memory不支持。

基本术语:

  • 事务(transaction)指一组 SQL 语句;
  • 回退(rollback)指撤销指定 SQL 语句的过程;
  • 提交(commit)指将未存储的 SQL 语句结果写入数据库表;
  • 保留点(savepoint)指事务处理中设置的临时占位符(placeholder),你可以对它发布回退(与回退整个事务处理不同)。

事务四大特征ACID

A:原子性atomicity:事务是最小的单位,不可分割。同一事务的sql语句必须保证同时成功或同时失败。

C:一致性consistency:数据库在事务执行前后都保持一致性状态,事务开始和结束之间的中间状态不会被其他事务看到。事务的修改必须满足相关的数据规则,保证数据完整性。事务结束后,所有内部数据结构(B树索引和双向链表)必须是正确的。所有事务对同一个数据的读取结果都是相同的。

I:隔离性isolation:保证事务执行能不被其他并发操作影响独立运行,即事务内部的操作和使用的数据对并发的其他事务是隔离的。

D:持久性durability:事务一旦结束提交,会永久的改变数据库,即使出现系统故障也能保持。

事务隔离性:隔离级别越高,性能越差。

  1. 更新丢失,a、b同时修改一张表,只保留了最后一次结果。在一个事务未提交前,另一个事务不能访问同一文件。
  2. read uncommitted:读未提交的——脏读。b读到a 已修改但未提交的数据。违反一致性
  3. read committed:读已提交的——不可重复读。b读到a 已修改并提交的数据。导致b认为前后不一致。违反隔离性。
  4. repeatable read:可以重复读——幻读。默认级别。b读到a 提交的新增数据。a插入数据,导致b两次读到的数据不同。b读不到a已经提交的数据,导致插入操作出错。违反隔离性。
  5. serializable:串行化。当表被事务a操作时,其他事务b的写操作会卡住(会等待超时),进入排队状态。当事务a结束后,事务b的写操作才会执行。效率很低。

查询隔离级别:8.0用transaction_isolation; 5.0用tx_isolation

  • 系统级别:select @@global.transaction_isolation;
  • 会话级别:select @@transaction_isolation;
  • 修改系统:set global transaction isolation level read uncommitted;
  • 修改会话:set session transaction isolation level read uncommitted;

事务一致性:

  • 强一致性:读操作可以立即读到提交的更新操作。
  • 弱一致性:提交的更新操作,不一定立即会被读操作读到,此种情况会存在一个不一致窗口,指的是读操作可以读到最新值的一段时间。
  • 最终一致性:是弱一致性的特例。事务更新一份数据,最终一致性保证在没有其他事务更新同样的值的话,最终所有的事务都会读到之前事务更新的最新值。如果没有错误发生,不一致窗口的大小依赖于:通信延迟,系统负载等。
  • 其他一致性变体还有:
    • 单调一致性:如果一个进程已经读到一个值,那么后续不会读到更早的值。
    • 会话一致性:保证客户端和服务器交互的会话过程中,读操作可以读到更新操作后的最新值。

事务原子性

  • 实现事务的原子性,要支持回滚操作,在某个操作失败后,回滚到事务执行之前的状态。

  • 回滚实际上是一个比较高层抽象的概念,大多数DB在实现事务时,是在事务操作的数据快照上进行的(比如,MVCC),并不修改实际的数据,如果有错并不会提交,所以很自然的支持回滚。

  • 在其他支持简单事务的系统中,不会在快照上更新,而直接操作实际数据。可以先预演一边所有要执行的操作,如果失败则这些操作不会被执行,通过这种方式很简单的实现了原子性。

自动提交:
show variables like '%autocommit%';
set autocommit=0; 

START TRANSACTION
# sql语句。只针对增删改查,create、drop不行。
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

注意:

  • 不能回退 SELECT 语句,回退 SELECT 语句也没意义;
  • 不能回退 CREATE 和 DROP 语句。
  • truncate不能回滚,delete可以。
  • MySQL 的事务提交默认是隐式提交,每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时,会关闭隐式提交;当 COMMIT 或 ROLLBACK 语句执行后,事务会自动关闭,重新恢复隐式提交。
  • 设置 autocommit 为 0 可以取消自动提交;autocommit 标记是针对每个连接而不是针对服务器的。
  • 如果没有设置保留点,ROLLBACK 会回退到 START TRANSACTION 语句处;如果设置了保留点,并且在 ROLLBACK 中指定该保留点,则会回退到该保留点。

案例

比如a给b转账100:

update user set money=money-100 where name='a';
update user set money=money+100 where name='b';

要求两个语句必须同时成功或者同时失败。

  1. mysql默认开启事务(自动提交)select @@autocommit; – 1
  2. 开始事务时,执行sql语句后立即生效,不能回滚。 rollback;–无效
  3. 关闭事务:set autocommit=0;
  4. 关闭事务时,执行sql语句后,需要手动提交才不能回滚。commit;
  5. 在没有commit前,rollback会回滚所有语句。所以转账时,可以两条语句都执行完毕后再commit。

**手动开启事务:**在最开始,输入begin; 或者 start transaction;此后可以rollback;

逻辑架构

应用程序——连接池——缓存(读信息)和缓冲(写信息)——读不到信息,到sql接口——解析器——优化器,生成执行计划——存储引擎——返回客户端,并在缓存存一份。

MySQL执行

启动缓存:

  • 修改配置文件:vim /etc/my.cnf
  • 新增 query_cache_type=1 (修改字符为:character_set_server=utf8)
  • esc —— :wq
  • 重启mysql:systemctl restart mysqld
  • 检查一下是否active:systemctl status mysqld
  • 开启profile:
    • show variables like ‘%profiling%’
    • set profiling=1
  • 执行语句 select。。。
  • 查看profiles
    • show profiles; ——显示语句id,耗时,命令
    • show profile cpu.block io for query 语句id; ——status。两次查看可以看到第二次查询时,直接在缓存中查找。(时间变短)
    • 想要一样,必须p(sql语句)、v(查询结果)键值对完全一致。

存储引擎

show engines; ——最常用 InnoDB 和 MyISAM

分类:

  • InnoDB :mysql的默认事务型引擎,用来处理大量短期事务。优先使用。
  • MyISAM:提供大量特性,如全文索引、压缩、空间函数GIS等。不支持事务和行级锁。崩溃后无法安全恢复
  • Archive:档案存储引擎支持insert和select操作。适合日志和数据采集类应用。比MyISAM表小75%,比InnoDB 表小83%。
  • Blackhole:没有实现任何存储机制,它会丢弃所有插入数据,不做任何操作。服务器会记录Blackhole表的日志,可以用于复制数据到备库,或者简单地记录到日志。这种应用方式会有很多问题。
  • CSV:可以作为普通的csv文件作为MySQL表来处理,不支持索引。可以作为数据交换机制。用于报表系统,文本。
  • Memory:用于快速访问数据,且数据不会修改,不在意重启数据丢失。
  • Federated:访问其他MySQL数据库的一个代理,很多问题,默认禁用。

InnoDB 和 MyISAM的区别:

MyISAMInnoDB
外键不支持支持
事务不支持支持
行表锁表锁,即使操作一条记录也会锁住整个表,不适合高并发行锁,操作时只锁住某一行,不对其他行有影响,适合高并发(会出现死锁)
缓存只缓存索引,不缓存真实数据缓存索引和真实数据,对内存要求高,内存大小对性能有决定性影响
关注节省资源、消耗少、简单业务并发写、事务、更大资源
默认安装
默认使用
系统表

详细版:

MyISAMInnodb
存储结构每张表被存放在三个文件:frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB
存储空间MyISAM可被压缩,存储空间较小InnoDB的表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复由于MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了
文件格式数据和索引是分别存储的,数据.MYD,索引.MYI数据和索引是集中存储的,.ibd
记录存储顺序按记录插入顺序保存按主键大小有序插入
外键不支持支持
事务不支持支持
锁支持(锁是避免资源争用的一个机制,MySQL锁对用户几乎是透明的)表级锁定行级锁定、表级锁定,锁定力度小并发能力高
SELECTMyISAM更优
INSERT、UPDATE、DELETEInnoDB更优
select count(*)myisam更快,因为myisam内部维护了一个计数器,可以直接调取。
索引的实现方式B+树索引,myisam 是堆表B+树索引,Innodb 是索引组织表
哈希索引不支持支持
全文索引支持不支持

MyISAM索引与InnoDB索引的区别?

  • InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引。
  • InnoDB的主键索引的叶子节点存储着行数据,因此主键索引非常高效。
  • MyISAM索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据。
  • InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据,因此查询时做到覆盖索引会非常高效。

InnoDB引擎的4大特性

  • 插入缓冲(insert buffer)
  • 二次写(double write)
  • 自适应哈希索引(ahi)
  • 预读(read ahead)

存储引擎选择

如果没有特别的需求,使用默认的Innodb即可。

MyISAM:以读写插入为主的应用程序,比如博客系统、新闻门户网站。

Innodb:更新(删除)操作频率也高,或者要保证数据的完整性;并发量高,支持事务和外键。比如OA自动化办公系统。

索引优化

性能下降

  • 数据太多——分库分表
  • 关联太多表,太多join——sql优化
  • 没有充分利用索引
  • 服务器调优及各个参数设置——调整my.cnf

索引(Index):

  • 帮助MySQL高效获取数据的数据结构。 可以简单理解为排好序的快速查找数据结构
  • 索引本身也很大,不可能全部存到内存中,往往以索引文件的形式存到磁盘上
  • **优点:**提高数据检索效率,降低IO成本。索引对数据进行排序,降低排序成本,CPU消耗。
  • **缺点:**提高了查询速度,降低更新、插入、删除的速度(需要同步更新索引信息)。索引也是一张表,保存了主键与索引字段,并指向实体表的记录,占用空间。

索引结构:平衡树(二叉树在极端情况下变成链表,平衡树会旋转)

  • BTree:
  • B+Tree:发生的IO次数更低。
  • 聚簇索引(数据行和相邻键值聚簇的存储在一起)和非聚簇索引

B树

索引分类:

  • 单值:一个索引只包含单个列,可以有多个
  • 唯一:索引列的值必须唯一,允许空值。可以有多个null
  • 主键:设定为主键,数据库会自动建立索引,InnoDB为聚簇索引
  • 复合:一个索引包括多个列

索引语法:

  • 查看:show index from 表名;
  • 创建:create [unique] index [索引名] on 表名(字段名);
  • 修改:alter table 表名 add unique 索引名(列);
  • 删除:drop index [索引名] on 表名

什么时候用索引:

  • 主键自动建立唯一索引
  • 频繁查询的字段应该创建索引
  • 查询与其他表关联的字段应该创建索引,外键
  • 组合索引比单键索引性价比高。MySQL只会选择一个索引,组合索引才能都用起来。
  • 排序字段应该建立索引
  • 统计或者分组字段

不用索引

  • 记录很少
  • 经常删改
  • where中用不到的字段
  • 过滤性不好的字段。如性别

explain

模拟优化器执行SQL语句,分析查询语句或表结构的性能瓶颈。

  • 功能:
    • 表的读取顺序
    • 哪些索引可用
    • 数据读取操作的操作类型
    • 哪些索引被引用
    • 表之间的引用
    • 每张表多少行被物理查询
  • 怎么做
    • explain sql语句(select…)
    • 总:id一个最好。type,查看是否为all或者range。key_len,索引长度,越长越好。rows:越短越好。extra,order、group、union等。
    • id:id一样从上到下,id不一样(子查询),id越大优先级越高,先执行。每一个id号表示一次独立的查询,一个sql的查询次数越少越好。
    • select_type:
      • simple:简单查询,不包含子查询或union
      • primary:主要查询,查询中包含复杂的子部份,最外层标记为primary
      • derived:衍生查询,在from中包含的子查询标记为derived,MySQL会递归执行这些子查询,并把结果存到临时表中。
      • subquery:子查询,在select或where中包含子查询,用=连接。
      • dependent subquery:依赖子查询,在select或where中包含子查询,用in连接。
      • uncacheable subquery:不可用缓存的子查询,sql一模一样就可以用缓存,若sql不可能一样,则标记为uncacheable 。如包含系统变量
      • union:联合查询,在第二个select出现在union后,标记为union。若union包含在from子句的子查询中,外出标记为derived
      • union result:联合查询结果,从union表获取的结果。
    • table:显示表名 或(临时表)
    • partitions:代表分区表的命中情况,非分区表,该项为null
    • type:访问类型排列。显示查询使用的类型,system>const>eq_ref>ref>range>index>all。最差应该达到range,最好是到ref。
      • system:表中只有一行记录(等于系统表),一般不会出现
      • const:通过索引一次就找到,用于比较primary key或unique索引。(索引值为常量,id=1)
      • eq_ref:唯一性扫描索引,对每一个索引键,表中只有一条记录与之匹配。(索引值为主键或唯一键)
      • ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质上是一种索引访问,但可能会找到多个符合条件的行,属于查找和扫描的混合体(用=连接,且不是主键或唯一键)
      • range:范围查询,只检测给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引。一般在where中出现between、<、>、in等查询。
      • index:全索引扫描,只遍历索引树。通常索引文件比数据文件小,所以index比all快。index和all都是读全表,但是index是从索引读取,all从硬盘读取。一般是使用了 覆盖索引 或者 利用索引进行了排序分组。
      • all:全表扫描,效率极低,必须建立索引
      • index_merge:查询需要多个索引组合。(用到or连接)
      • ref_or_null:某个字段需要关联条件,也需要null的情况。(or连接,且其中一个为null)
      • index_subquery:子查询用到索引,不需要关联扫描
      • unique_subquery:子查询中唯一索引。(主键或唯一键)
    • possible_keys:显示可能应用在这张表中的索引,一个或多个 。查询涉及到的字段若存在索引,则将给索引列出。但是不一定被查询使用。
    • key:实际使用的索引。如果为null,没有用索引。查询中若使用了覆盖索引,仅显示在key列表中,possible_keys没有。
    • key_len:索引长度。where后面的筛选条件命中索引的长度,越小越好。如,int长度4字节,加上null,5字节。varchar(20)长度20字节,gbk*2,utf8*3,动态字符最后加2字节。允许为空的字符加1字节。
    • ref:显示索引的哪一列被使用。哪些列或常量被用于查找索引列上的值。(没多少意义)
    • rows:MySQL认为执行查询时必须检查的行数,越小越好。模拟值。
    • filtered:存储引擎返回的数据中server层过滤后,剩下满足查询记录数量的比例。百分比。
    • extra:包含不适合在其他列中显示但十分重要的额外信息。(order by、group by)
      • using filesort:文件排序,order by没用上索引。
      • using temporary:新建临时表保存中间结果。group by没用上索引。group by包含order by,更慢。
      • using index:使用了覆盖索引,避免访问了表的数据行,效率不错。同时出现using where表明索引被用来执行索引键值的查找。没有出现using whereusing where表明索引只是用来读取数据而非利用索引执行查找。
      • using where:使用了where过滤
      • using join buffer:union没用上索引。关联字段增加索引(on后面)。
      • impossible where:逻辑错误。
      • select tables optimized away:没有group by的情况下,基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作。查询执行计划生成的阶段即完成优化。
      • distinct:优化distinct操作,在找到第一匹配的元组后,停止再找相同值的操作。

覆盖索引:covering index

  • select的数据列只用从索引中取得,不必读取数据行。MySQL可以利用索引返回select列表中的字段,不必根据索引再次读取数据文件。查询列被索引覆盖。
  • 注意:
    • 要用覆盖索引,select列表中不要用*,而只取需要的列。
    • 如果将所有字段一起做成索引,会导致索引文件过大,查询性能下降。

单表使用索引和常见索引失效

1、改变顺序不影响用到索引,优化器会优化。但是缺少了索引开头的字段,后面的就用不了。
2、最佳左前缀法则。索引建立平衡树是分层的。先建立age树,后建立deptid树。deptid树的建立是在age相同的情况下的。
3、函数或计算索引失效。如 name like '张%'能用到, left(name, 1)='张'用不到索引
4、范围查询后面的字段失效。如:where name='abc' and deptid>4 and age=35; # age失效。建立索引时范围查询应该放到后面。如日期、时间、能比较的字段等。
5、不等于索引失效。如:where name<>'abc'。
6、 is not null索引失效。 is null可以用。
7、模糊查询首字符未知,索引失效,索引创建根据字符顺序的。如: like '%abc%'; 可以用覆盖索引优化。 like是范围,但是不会导致后面的失效。
8、自动或手动类型转换索引失效。如:name=123,如果 name是 varchar,匹配 int失效。
9、用 or之后索引失效。

# 没有索引,全表扫描all
explain select sql_no_cache * from emp where emp.age=30;  # sql_no_cache不走cache
create index idx_age on emp(age);  # 为age创建索引,type变为ref,key_len,rows变化。

explain select sql_no_cache * from emp where emp.age=30 and drptid=4;  # 增加条件
create index idx_age_deptid on emp(age, deptid);  # 联合索引,ref

一般性建议:

  • 单键索引尽量选择当前query过滤性更好的索引。性别过滤性不好,学号、身份证、手机号好。跟业务不相关的自增最好。
  • 选择组合索引时,当前query中过滤性最好的字段在索引字段顺序越靠前越好
  • 选择组合索引时,尽量选择可以包含当前query中where字句中更多字段的索引
  • 选择组合索引时,如果某个字段可能出现范围查询,应该放到最后
  • 避免索引失效

关联查询优化

  • 关联查询分为:驱动表、被驱动表。驱动表必须全表扫描,建立索引无效。被驱动表建立索引有效。
  • 驱动表和被驱动表会换顺序,在inner join中,会根据是否有索引更换,优化查询,left join就没办法了。
  • straight_join指定驱动表和被驱动表,不可以换,以免大表中有主键,导致mysql选择大表作为被驱动表。使用时,必须明确表的数量级关系不会变。
  • 小表为驱动表,大表为被驱动表。提高效率。
  • 虚拟表(子查询的结果)不能建立索引,不能放到被驱动表上。
  • 能直接关联尽量直接关联,不要用子查询。子查询多趟查询。

子查询优化:进行不要用not in或not exists。

  • 一个表有,另一个表没有,用关联查询代替
# 查询非ceo信息,emp表有信息和id,dept有ceo的id。
select * from emp a 
where a.id not in (
	select b.ceo from dept b
    where b.ceo is not null
);
# 优化:不用子查询,is not null改为is null
select * from emp a
left join dept b
on a.id=b.ceo
where b.id is null

# 小表驱动大表,in 和 exists
select * from A where id in ( select id from B)# 当A表大于B表时,in好
select * from A where exists( select 1 from B where A.id=B.id) # A表小于B表时,exists好
1、 exists(subquery) 返回 True或 False,子查询列表可以为1或其他常数。代码会忽略select清单。
2、 exists子查询往往可以用条件表达式、其他子查询、join代替。
3、 exists子查询实际执行可能会被优化,需要实际检验确定效率问题。

排序分组优化

index(age,birth)  # 查看是否using filesort
1、where age>20 order by age;  # 没有
2、where age>20 order by age,bitrh;   # 没有
3、where age>20 order by bitrh;  # 有
4、where age>20 order by bitrh,age;    # 有
# 结论:范围查询会破坏索引的排序功能

order by用到索引的条件:

  • 必须有过滤条件,where或者limit。
  • 排序字段的顺序必须和索引一致,因为order by调整顺序的结果不一样,不能更换顺序。
  • 升降序必须只选一个。都升序或都降序。

group by和order by几乎一样。但是group by即使没有过滤条件,也可以直接用索引。

filesort有两种算法

  • 双路排序:MySQL4.1之前,扫描两次磁盘。从磁盘读行指针和order列,在buffer对他们排序,然后扫描已经排序好的列表。按照列表的值重新从磁盘中读取数据输出。
  • 单路排序:从磁盘读取需要的所有列,在buffer对order列排序,扫描排序后的列表输出。避免二次读取数据,把随机IO变成顺序IO,效率高。但是会占用更多空间,而且一旦超出sort_buffer容量,会导致多次IO,性能更差。
    • 增加sort_buffer_size
    • 增加max_length_for_sort_data

提高order by的速度:

  • select * 只query需要的字段。
    • query字段大小总和小于max_length_for_sort_data,且排序字段不是text|blob类型时,才用单路排序,否则用多路排序。
    • 两种算法都可能超出sort_buffer容量,超出后,会创建tmp文件进行合并排序,导致多次I/O,但单路排序风险更大,需要提高sort_buffer_size。
  • 尝试提高sort_buffer_size。这个参数针对每个进程的1M-8M之间调整。
  • 尝试提高max_length_for_sort_data。会增加用改进算法的概率。如果太高,数据总容量超出sort_buffer_size的概率增大,导致高磁盘I/O活动和低处理器效率。1024-8192之间调整。

**覆盖索引:**不要用select *,写出需要的具体字段。

# 组内排序:
找emp员工表中年龄第二大的人。
set @rank=0;
set @last_deptid=0;
select a.deptid, a.name, a.age
from (
	select t.*, if(@last_deptid=deptid, @rank:=@rank+1, @rank:=1) as rk,
    	@last_deptid:=deptid as last_deptid
    from emp t
    order by deptid, age desc
) a
where a.rk=2;

慢查询日志

使用

  • 文件在 /var/lib/mysql/主机名-slow.log
  • MySQL提供的日志记录,用来记录在MySQL中响应时间超过阈值的语句,具体指运行时间超过long_quey_time值的sql。long_quey_time默认为10,指10s。
  • 默认关闭:show variables like ‘%slow_query_log%’;
  • 开启:set global slow_query_log=1;
    • 修改配置文件永久生效。my.cnf 的 [mysqld]下增加 slow_query_log=1 和 slow_query_log_file=/var/lib/mysql/主机名-slow.log
  • 阈值时间:show variables like ‘long_quey_time%’;
  • 修改阈值:set long_quey_time=0.1;
  • 查看记录:cat /var/lib/mysql/主机名-slow.log
  • 查询当前有多少慢查询记录:show global status like ‘%Slow_queries%’

日志分析工具

  • mysqldumpslow。 # 用来进行筛选,分析等。

  • mysqldumpslow --help # 查看帮助

  • mysqldumpslow -s c -t 3 -a 日志文件 # 计算c个数、t时间,t显示top3,a显示数字和字符

  • s:何种方式访问,c:访问次数,I:锁定时间,r:返回记录,t:查询时间,al:平均锁定时间,ar:平均返回记录数,at:平均查询时间,t:返回前面多少条数据,g:搭配正则表达式,大小写不敏感等。

  • 可以配合管道和more

show profile

  • mysql提供的分析会话中语句执行的资源消耗情况。用于sql调优的测量。
  • 默认关闭,并保存最近15次运行结果。show variables like ‘%profiling%’;
  • 开启功能:set profiling=on;
  • 查看结果:show profiles; # 展示query_id,耗时、sql代码
  • 诊断sql:show profile cpu, block io for query query_id; # sql的完整生命周期和过程
    • converting HEAP to MyISAM查询结果太大,内存不够用,往磁盘搬了。
    • Creating tmp table 创建临时表
      • 拷贝数据到临时表
      • 用完再删除
    • Copying to tmp table on disk 把内存的临时表复制到磁盘。
    • locked

全局查询日志

  • 启用:set global general_log=1; set global log_output=‘TABLE’;
  • 查看:select * from mysql.general_log;# mysql 系统表
  • 永远不要在生产环境打开

进程

  • 展示进程列表:show processlist;
  • 杀掉进程:kill 进程id

数据锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。

数据库中,除了传统计算资源(cpu、ram、io等)争用外,数据也是一种用户共享资源。保证数据并发访问一致性、有效性是数据库必须解决的问题。

分类

  • 数据操作类型
    • 读锁(共享锁 Shared)S锁:对同一份数据,多个读操作可以同时进行、互不影响。
    • 写锁(互斥锁 Exclusive)X锁:当前写操作没完成前,阻断其他写锁和读锁。
  • 数据操作粒度:行锁、表锁

三锁:(开销、加锁速度、死锁、粒度、并发性能)

  • 引擎

    • InnoDB实现行级锁定,性能损耗比表级锁定高一些。但是在高并发情况下,行级锁定就有明显优势。
    • MyISAM在查询时,会自动给涉及的表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。
  • cap:强一致性、高可用性、分区容错性。p必须保证,ca只能选一个

  • 表锁:偏读

    • 特点:偏向MyISAM存储引擎,开销小,加锁快,无死锁,粒度大,发生锁冲突概率最高,并发度最低
    • 建表:create table 表名 (字段名 数据类型 键)engine myisam;# 指定存储引擎
    • 增加表锁:lock table 表名 read/write,表名2 read/write,…;
      • 读锁:本会话,可读被锁表,不能读其他表。不能写操作。其他会话,可读被锁表,可读其他表,写操作进入阻塞状态。
      • 写锁:本会话,可读被锁表,不能读其他表,可写被锁表。其他会话,读写操作都会进入阻塞状态。
    • 查看表锁:show open tables; # in_use 为1表示锁,0表示m
    • 删除表锁:unlock tables;
    • MyISAM在查询时,会自动给涉及的表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。
    • 分析表锁定:show status like ‘table%’;
      • table_locks_immediate:立即释放表锁次数。
      • table_locks_waited:需要等待的表锁数,表示表级锁的争用情况。
      • MyISAM的读写锁调度是写优先,不适合作为主表的引擎。写锁后,其他线程不能操作,如果有大量更新操作会使查询阻塞。
      • 如果Table_locks_immediate / Table_locks_waited > 5000,最好采用InnoDB引擎,
        因为InnoDB是行锁而MyISAM是表锁,对于高并发写入的应用InnoDB效果会好些。
  • 行锁:偏写

    • 特点:偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁,粒度小,发生锁冲突概率最低,并发度最高。
    • InnoDB对比MyISAM最大不同:InnoDB支持事务(transaction),并采用行级锁。
    • 建表:create table 表名 (字段名 数据类型 键)engine innodb;# 指定存储引擎
  • 关闭自动提交:set autocommit=0;

    • 更新某一行但不提交,其他会话该行被锁定,进入阻塞。

    • 无索引导致行锁升级为表锁。比如,本会话执行时,出现了索引失效(函数、类型转换等),导致整张表都被锁起来了。

    • 间隙锁:当使用范围条件检索时,范围内已有数据的索引项加锁,范围内不存在的记录叫做“间隙GAP”。innodb也会对这个间隙加锁,称之为间隙锁Next-Key锁。其他会话操作间隙锁也会进入阻塞。

    • 单独一行加锁。

      • select … for update。# 锁定一行,其他会话不能读写。
      • select … lock in share mode; # 锁定一行,其他会话不能读。
    • 分析表锁定:show status like ‘innodb_row_lock%’;

      • innodb_row_lock_current_waits:当前锁定数量
      • innodb_row_lock_waits:总共等待次数
      • innodb_row_lock_time:总锁定时间
      • innodb_row_lock_time_avg:平均等待时间
      • innodb_row_lock_time_max:最长的一次等待时间
    • 优化建议:

      • 数据检索尽可能用索引完成,避免行锁升级为表锁
      • 尽量缩小锁的范围
      • 尽量少的检索条件,避免间隙锁
      • 尽量控制事务大小,减小锁定的资源和时间
      • 尽量低事务隔离级别
  • 页锁:

    • 开销和加锁时间、粒度、并发度在表锁和行锁中间,会出现死锁。

封锁协议

1. 三级封锁协议

一级封锁协议:事务 T 要修改数据 A 时必须加 X 锁,直到 T 结束才释放锁。可以解决丢失修改问题,因为不能同时有两个事务对同一个数据进行修改,那么事务的修改就不会被覆盖。

二级封锁协议:在一级的基础上,要求读取数据 A 时必须加 S 锁,读取完马上释放 S 锁。可以解决脏读问题,因为如果一个事务在对数据 A 进行修改,根据 1 级封锁协议,会加 X 锁,那么就不能再加 S 锁了,也就是不会读入数据。

三级封锁协议:在二级的基础上,要求读取数据 A 时必须加 S 锁,直到事务结束了才能释放 S 锁。可以解决不可重复读的问题,因为读 A 时,其它事务不能对 A 加 X 锁,从而避免了在读的期间数据发生改变。

2. 两段锁协议

加锁和解锁分为两个阶段进行。

可串行化调度是指,通过并发控制,使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰,不会出现并发一致性问题。

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议,它是可串行化调度。

lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)

但不是必要条件,例如以下操作不满足两段锁协议,但它还是可串行化调度。

lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)

多版本并发控制

Multi-Version Concurrency Control, MVCC,是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式,用于实现读已提交和可重复读这两种隔离级别。而读未提交隔离级别总是读取最新的数据行,要