MySQL学习手册-高级

DB 评论

MySQL 架构

配置文件

二进制日志log-bin

主从复制

错误日志log-error

默认是关闭的,记录严重的警告和错误信息,每次启动和关闭的详细信息

默认关闭的原因是为了高效

查询日志 log

  • 默认关闭
  • 记录查询的语句
  • 开启会降低 mysql 的整体性能,因为记录日志也是需要消耗系统资源的

数据文件

linux 中,使用ls -1F|grep ^d查看当前系统中所有库,貌似不适用于 docker 安装的 mysql

默认路径: var/lib/mysql

  • frm 文件: 存放表结构
  • myd 文件: 存放表数据
  • myi 文件: 存放表索引

逻辑架构

  • 连接层
  • 服务层
  • 引擎层

和其他数据库相比,它的架构可以在不同场景中应用并发挥良好作用.主要体现在存储引擎的架构上.

插件式存储引擎架构将查询处理和其他的系统人物以及数据的存储提取相分离.

这种架构可以根据业务的需求和实际需要选择合适的存储引擎

连接层

最上层是一些客户端和连接服务,包含本地 sock 通信和大多数基于 c/s 工具实现的类似于 tcp/ip 的通信.

主要完成一些类似于连接处理,授权认证及相关的安全方案.

在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程.

同样在该层上可以实现基于 SSL 的安全连接.

服务器也会为安全接入的每个客户端验证它所具有的操作权限.

服务层

第二层架构主要完成大多核心服务功能,如 sql 接口,并完成缓存的查询, SQL 的分析和优化及部分内置函数的执行.所有跨存储引擎的功能,如过程,函数等.

在该层,服务器会解析查询并创建相应的内部解析树,并对其完成响应的优化如确定查询表的顺序,是否利用索引等,最后生成响应的执行操作.

如果是 select 操作,服务器还会查询内部的缓存.

如果缓存空间足够大,这样在解决大量读操作的环境中能够更好的提升系统的性能

引擎层

存储引擎层,存储引擎真正的负责了 MySQL 中数据的存储和提取,服务器通过 API 与存储引擎.

不同的存储引擎具有的功能不同,根据实际需要选取,如 MyISAM 和 InnoDB

存储层

主要是数据存储在运行于裸设备的文件系统之上,完成与存储引擎的交互

存储引擎

查看存储引擎: show engines;

对比 MyISAM 和 InnoDB:

对比项 MyISAM InnoDB
主外键 不支持 支持
事务 不支持 支持
行表锁 表锁,即使操作一条记录也会锁住整个表,不适合高并发操作 行锁,操作时只锁一行,不会其他行有影响,适合高并发操作
缓存 只缓存索引,不缓存数据 不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响
表空间 小 大
关注点 性能 事务
默认安装 Y Y

索引优化分析

sql 性能下降原因分析

性能下降 sql 慢,执行时间长,等待时间长

可能的原因:

  • 语句写的烂
  • 索引失效
    • 单值
      • 创建: create index idx_user_name on user(userName)
    • 复合
      • 创建: create index idx_user_name_email on user(name,email)
  • 关联查询太多 join
    • 设计缺陷或不得已的需求
  • 服务器调优及各个参数设置
    • 缓冲,线程数等

sql 执行顺序

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. LIMIT

七种 JOIN

见基础篇

主要记住两表相连一表独有要怎么做

  • select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null;

    1
    2
    3
    4
    5
    6
    7
    8
    9

    全连接 mysql 不支持的情况下怎么实现?

    * 使用 union

    * ```mysql
      select * from tbl_emp a left join tbl_dept b on a.deptId=b.id 
      union 
      select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;

两表各自的独有怎么实现?

  • select * from tbl_emp a left join tbl_dept b on a.deptId=b.Id where b.id is null 
union 
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89

    ## 索引

    ### 简介

    #### 是什么

    **索引 (Index) 是帮助 MySQL 高效获取数据的数据结构.** 目的: 提高查找效率

    所以,索引是一种"**排好序的快速查找数据结构**"

    索引会影响 where 的查找和 order by 的排序

    我们平时说的索引,如果没有特别指明, 都是指 B 树(多路搜索树,不一定是二叉树) 结构组织的索引

    #### 优势劣势

    * 优势
      * 类似大学图书馆建书目索引, 提高数据检索的效率, 降低数据库的 IO 成本
      * 通过所以对数据进行排序, 降低数据排序的成本, 降低了 CPU 的消耗
    * 劣势
      * 索引也是一张表, 该表保存了主键与索引字段, 并指向实体表的记录, 所以索引列也是要占用空间的
      * 虽然索引大大提高了查询速度, 同时会降低更新表的速度, 如对表进行 INSERT, UPDATE 和 DELETE. 因为更新表时, MySQL 不仅要保存数据, 还要保存一下索引文件每次更新添加了索引列的字段, 都会调整因为更新所带来的键值变化后的索引信息
      * 索引只是提高效率的一个因素, 如果 MySQL 有大数据量的表, 就需要花时间研究建立最优秀的索引

    #### 分类

    * 单值索引
      * 即一个索引只包含单个列, 一个表可以由多个单列索引
      * 最多不要超过 5 个.
        * MySQL 5.1 之前,一条查询语句只会用到一个索引
        * MySQL 5.1 之后, 支持索引合并. 索引合并是利用表上的多个单列索引来定位指定行，其原理是将对每个索引的扫描结果做运算，总共有：交集、并集以及他们的组合，但是索引合并并非是一种合适的选择，因为在做索引合并时可能会消耗大量的CPU和内存资源，一般用到索引合并的情况也从侧面反映了该表的索引需要优化，可以尝试建立联合索引
    * 唯一索引
      * 索引列的值必须唯一, 但必须有空值
    * 复合索引
      * 即一个索引包含多个列

    #### 结构

    * BTree 索引
    * Hash 索引
    * full-text 全文索引
    * R-Tree 索引

    #### BTree

    重点,详见数据结构笔记

    一些平衡树只在叶子节点中存储值，而且叶子节点和内部节点使用不同的结构。B树在每一个节点中都存储值，所有的节点有着相同的结构。然而，因为叶子节点没有子节点，所以可以通过使用专门的结构来提高B树的性能。

    > B+树也是重中之重

    #### 需要创建索引的情况

    1. 主键自动建立唯一索引
    2. 频繁作为查询条件的字段应该创建索引
    3. 查询与其他表关联的字段, 外键关系建立索引
    4. 频繁更新的字段不适合创建索引. 因为每次更新不单单是更新了记录还会更新索引
    5. where 条件里用不到的字段不创建索引
    6. 单键/组合索引的选择问题: 高并发下倾向于创建组合索引
    7. 查询中排序的字段, 排序字段若通过索引去访问将大大提高排序速度
    8. 查询中统计或者分组字段

    #### 不需要创建索引的情况

    1. 表记录太少 (300 万以上MySQL性能下降)
    2. 经常增删改的表. 因为提高查询速度但是却降低了更新表的速度(更新表要更新索引)
    3. 数据重复且分布平均的表字段. 没有意义

    ### 性能分析

    #### MySQL Query Optimizer

    MySQL 中有专门负责优化 select 语句的优化器模块

    * 通过计算分析系统中收集到的统计信息,为客户端请求的 query 提供它认为最优的执行计划(它认为最优的不一定是 DBA 认为最优的, 这部分最耗费时间)

    #### MySQL 常见瓶颈

    * CPU: CPU 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
    * IO: 磁盘 I/O 瓶颈发生在装入数据远大于内存容量的时候
    * 服务器硬件的性能瓶颈: top,free,iostat 和 vmstat 来查看系统的性能状态

    #### EXPLAIN 关键字

    使用 explain 关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理 SQL 语句的. 分析你的查询语句或是表结构的性能瓶颈

    ```mysql
    explain select * from tbl_emp;
可以解析:
  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询
各字段解释
  • id: select 查询的序列号,包含一组数字, 表示查询中执行 select 子句或操作的顺序
    • id 相同, 执行顺序由上至下
    • 如果是子查询, id 的序列递增, id 值越大优先级越高, 越先被执行
    • id 如果相同,可以认为是一组,从上往下顺序执行; 在所有组中, id 值越大,优先级越高,越先执行
      • 衍生 = derived
  • select_type: 查询的类型, 主要用于区别普通查询,联合查询,子查询等的复杂查询
    • 分类
      • SIMPLE: 简单的 select. 不包含子查询或 union
      • PRIMARY: 查询中若包含复杂的子部分, 最外层查询被标记
      • SUBQUERY: 子查询
      • DERIVED: 在 from 列表中包含的子查询被标记为 derived(衍生). MySQL 会递归执行这些子查询, 把结果放在临时表中
      • UNION: 若第二个 select 出现在 union 之后,则被标记为 union; 若 union 包含在 from 子句的子查询中, 外层 select 将被标记为 derived
      • UNION RESULT: 从 union 表中获取的 result
  • table: 属于哪张表
  • type: 访问类型
    • 分类: ALL/index/range/ref/eq_ref/const,system/NULL
      • system: 表只有一行记录, const 类型的特例,平时不会出现
      • const: 表示通过索引一次就找到了, const 用于比较 primary key 或者 unique 索引. 因为只匹配一行数据所以很快
      • eq_ref: 唯一性索引扫描, 对于每个索引键, 表中只有一条记录与之匹配. 常见于主键或唯一索引扫描
      • ref: 非唯一性索引扫描, 返回匹配某个单独值的所有行. 可能会找到多个符合条件的行
      • range: 只检索给定范围的行, 使用一个索引来选择行. key 列显示使用了哪个索引
        • 一般是在 where 中出现了 between, <, >, in等的查询
        • 范围索引扫描比全表扫描要好, 因为不需要扫描全部索引
      • index: full index scan. index 与 all 区别为 index 类型只遍历索引树.
      • all: full table scan. 遍历全表找到匹配的行
    • 从好到差依次是: system>const>eq_ref>ref>range>index>ALL
    • 一般来说, 得保证查询至少达到 range 级别, 最好能达到 ref
  • possible_keys: 显示可能应用在这张表中的索引, 一个或多个.
  • key: 实际使用的索引. 如果为 NULL, 则没有使用索引. 查询中若使用了覆盖索引, 则该索引仅出现在key 列表中
  • key_len: 表示索引中使用的字节数, 可通过该列计算查询中使用的索引的长度.
    • 在不损失精确性的情况下, 长度越短越好
    • key_len 显示的值为索引字段的最大可能长度, 并非实际使用长度, 即 key_len 是根据表定义计算可得, 不是通过表内检索出的
  • ref: 显示索引的哪一列被使用了, 如果可能的话, 是一个常数. 哪些列或常量被用于查找索引列上的值.
  • rows: 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
  • extra: 包含不适合在其他列显示但十分重要的额外信息
    • Using filesort
    • Using temporary
    • Using index
    • …
覆盖索引( Covering Index)

  • 理解方式一: select 的数据列只用从索引中就能够取得, 不必读取数据行, MySQL 可以利用索引返回 select 列表中的字段,而不必根据索引再次读取数据文件, 换句话说查询列要被所创建的索引覆盖.

  • 理解方式二: 索引是找到行的一个方法, 但是一般数据库也能使用索引找到一个列的数据, 因此它不必读取整个行. 毕竟索引叶子结点存储了它们索引的数据; 当能通过读取索引就可以得到想要的数据, 那就不需要读取行了. 一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引

注意:

  • 如果要使用覆盖索引, 一定要注意 select 列表中值选出需要的列, 不可 select *, 因为如果将所有字段一起做索引会导致索引文件过大, 查询性能下降.

索引优化

单表优化

1
select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;

这种情况下建立:

1
create index idx_article_ccv on article(category_id,comments,views);

这样其实是不好的. 因为 comments>1 这个条件会使索引失效

应该仅仅在 category_id 和 views 上建立索引

1
create index idx_article_cv on article(category_id,views);

两表优化

  • 左连接的特性,就是左边表全部都会有. 所以根据左连接的特性, LEFT JOIN 条件用于确定如何从右表搜索行, 左边一定都有. 所以右边是我们的关键点, 一定要建立索引.
  • 同理, 右连接RIGHT JOIN 的条件用于确定如何从左表搜索行, 右边一定都有. 所以左边是关键点, 一定要建立索引.

三表优化

1
select * from class left join book on class.card=book.card left join phone on book.card = phone.card;

类似上述的两表优化, 将右边的两个表都加上索引

1
2
alter table `phone` add index z(card);
alter table `book` add index y(card);

结果可以发现后两行的 type 都是 ref 并且总 rows 优化很好,效果也不错.

因此索引最好设置在需要经常查询的字段中

结论

join 语句的优化:

  • 尽可能减少 Join 语句中的 NestedLoop 的循环总次数: “永远用小结果集驱动大的结果集”.
  • 优先优化 NestedLoop 的内层循环
  • 保证 Join 语句中 被驱动表上 Join 条件字段 已经被索引
  • 当无法保证被驱动表的 Join 字段被索引且内存资源充足的情况下, 不要太吝啬 JoinBuffer 的设置.

索引失效(应该避免)

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table staffs( 
  id int primary key auto_increment, 
  NAME varchar(24) not null default "" comment 'xingming',
  age int not null default 0 comment 'nianling',
  pos varchar(20) not null default "" comment 'zhiwei', 
  add_time timestamp not null default current_timestamp comment 'ruzhishijian' 
)charset utf8 comment 'yuangongjilubiao';

insert into staffs(NAME,age,pos,add_time) values
('z3',22,'manager',NOW()),
('July',23,'dev',NOW()),
('2000',23,'dev',NOW());

alter table staffs add index idx_staffs_nameAgePos(name,age,pos);

规则 (5.x 版本, 8.x 可能很多不符合)

  1. 全值匹配我最爱
  2. 最佳左前缀法则
    1. 如果索引了多列, 要遵循最左前缀法则. 指的是查询从索引的最左前列开始并且不跳过索引中的列.
  3. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
  4. 存储引擎不能使用索引中范围条件右边的列口
  5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列–致))，减少select *
    1. 这样 extra 中能出现 using index, 性能好
  6. mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
  7. is null ,is not null也无法使用索引
  8. like以通配符开头(‘%abc..’)mysql索引失效会变成全表扫描的操作
  9. 字符串不加单引号索引失效(重罪)
    1. MySQL 底层会有隐形的类型转换, 见 3
  10. 少用or,用它来连接时会索引失效

小总结

假设 index(a,b,c)

where语句 索引是否被使用
where a = 3 Y, 使用到 a
where a = 3 and b = 5 Y, 使用到a,b
where a = 3 and b = 5 and c = 4 Y, 使用到 a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 N
where a = 3 and c = 5 Y, 使用到a, 因为 b 中间断了
where a = 3 and b > 4 and c = 5 Y, 使用到 a 和 b, c 不能用在范围之后,b 断了
where a = 3 and b like ‘kk%’ and c = 4 Y, a 能用,b 能用,c 不能用
where a = 3 and b like ‘%kk’ and c = 4 Y, 只用到 a
where a = 3 and b like ‘%kk%’ and c = 4 Y, 只用到 a
where a = 3 and b like ‘k%kk%’ and c = 4 Y, 使用到a,b,c

一般性建议

  • 对于单键索引, 尽量选择针对挡圈 query 过滤性更好的索引
  • 在选择组合索引的时候, 当前 query 中过滤性最好的字段在索引字段顺序中, 位置越靠前越好
  • 在选择组合索引的时候, 尽量选择可以能够包含当前 query 中的 where 字句中更多字段的索引
  • 尽可能通过分析统计信息和调整 query 的写法来达到选择合适索引的目的

优化总结口诀

全值匹配我最爱, 最左前缀要遵守;

带头大哥不能死, 中间兄弟不能断;

索引列上少计算, 范围之后全失效;

LIKE 百分写最右, 覆盖索引不写星;

不等空值还有 or, 索引失效要少用;

VAR 引号不可丢, SQL 高级也不难!

查询截取分析

查询优化

永远小表驱动大表

优化规则: 即小的数据集驱动大的数据集

原理:

1
2
3
4
select * from A where id in (select id from B)
等价于
for select id from B
for select * from A where A.id = B.id

当 B 表的数据集必须小于 A 表的数据集时, 用 in 优于 exists

1
2
3
4
select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select * from A
for select * from B where B.id = A.id

当 A 表的数据集小于 B 表的数据时,用 exist 优于 in

注意: A 和 B 的 id 字段应该建立索引

  • EXISTS
    • select ... from table where exists (subquery)
    • 该语法可以理解为: 将主查询的数据,放到子查询中做条件验证, 根据验证结果(TRUE/FALSE) 来决定主查询的数据结果是否得以保留
  • 提示
    • EXISTS(subquery) 只返回 TRUE 或 FALSE, 因此子查询中的 select * 也可以是 SELECT 1 或其他, 官方说法是实际执行时会忽略 select 清单, 因此没有区别
    • EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比, 如果但有效率问题,可进行实际检验以确定是否有效率问题
    • EXISTS 子查询往往也可以用条件表达式, 其他子查询或者 JOIN 来替代, 何种最优需要具体问题具体分析
order by 关键字优化
  1. order by 子句, 尽量使用 Index 方式排序, 避免使用 FileSort 方式排序
  2. 尽可能在索引列上完成排序操作, 遵照索引建的最佳左前缀
  3. 如果不在索引列上, filesort 有两种算法: mysql 就要启用双路排序和单路排序
    1. 双路排序
      1. MySQL 4.1 之前是使用双路排序, 字面意思就是两次扫描磁盘, 最终得到数据, 读取行指针和 orderby 列, 对他们进行排序, 然后扫描已经排序好的列表, 按照列表中的值重新从列表中读取对应数据输出
      2. 从磁盘取排序字段, 在 buffer 进行排序, 再取其他字段
      3. I/O 非常耗时
    2. 单路排序
      1. 从磁盘读取查询需要的所有列, 按照 orderby 列在 buffer 对它们进行排序, 然后扫描排序后的列表进行输出, 它的效率更快一些, 避免了第二次读取数据.
      2. 把随机 IO 变成了顺序 IO, 但是它会使用更多的空间, 因为它把每一行都保存在内存中了
    3. 注意
      1. 在 sort_buffer 中,单路排序比双路排序要占用很多空间, 有可能取出的数据总大小超出了 sort_buffer 的容量, 导致每次只能取 sort_buffer 容量大小的数据, 进行排序(创建 tmp 文件, 多路合并), 排完再取 sort_buffer 大小, 再排序… 从而多次 IO
      2. 本来想省一次 IO 操作, 从而导致了大量的 IO 操作, 反而得不偿失.
    4. 优化策略
      1. 不要用 select *
        1. 当 query 的字段的大小总和小于 max_length_for_sort_data 而且排序字段不是 text|blob 类型时, 会采用改进后的单路排序, 否则使用老算法双路排序
      2. 增大 sort_buffer_size 的设置
        1. 不管哪种算法, 提高这个参数都会提高效率,
        2. 根据系统能力提高, 这个参数针对进程
      3. 增大 max_length_for_sort_data 参数的设置
        1. 提高这个参数, 会增加用改进算法的概率
  4. select * order by 无论后面怎么写, 都会是 filesort. 只要是 select 中包含了不在索引的列都会如此
总结

为排序使用索引

  • MySQL两种排序方式: filesort 和 index
  • MySQL能为排序与查询使用相同的索引

KEY a_b_c(a,b,c)

order by 能使用索引最左前缀

  • order by a
  • order by a,b
  • order by a,b,c
  • order by a desc, b desc, c desc

如果 where 使用索引的最左前缀定义为常量, 则 order by 能使用索引

  • where a = const order by b,c
  • where a = const and b = const order by c
  • where a = const order by b,c
  • where a = const and b > const order by b,c (本来 b 断了,但是后面连上了)

不能使用索引进行排序

  • order by a asc, b desc, c desc // 排序不一致
  • where g = const order by b,c // 丢失 a
  • where a = const order by c // 丢失 b
  • where a = const order by a,d // d不是索引
  • where a in (…) order by b,c // 对于排序来说,多个相等条件也是范围查询
group by 关键字优化
  1. group by 实质是先排序后进行分组, 遵照索引建的最佳左前缀
  2. 当无法使用索引列, 增大 max_length_for_sort_data 参数的设置+增大 sort_buffer_size 参数的设置
  3. where 高于 having, 能写在 where 限定的条件就不要去 having 限定了

慢查询日志

  • MySQL 的慢查询日志是 MySQL 提供的一种日志记录, 它用来记录在 MySQL 中响应时间超过阈值的语句, 具体指运行时间超过 long_query_time 值的 SQL, 则会被记录到慢查询日志中
  • 默认情况下, MySQL 数据库没有开启慢查询日志, 需要手动设置
  • 如果不是调优需要的话, 一般不建议启动该参数, 因为会有一定的性能影响
  • 支持将日志写入文件
开启
1
2
show variables like '%low_query_log%';
set global slow_query_log=1;#开启,但是只对当前数据库生效, 如果 MySQL 重启后将会失效

如果要永久生效, 必须修改配置文件 my.cnf

在[mysqld]下增加参数

1
2
slow_query_log=1
slow_query_log_file=/var/lib/mysql/yiqing.log

查看多久算慢

1
show variables like 'long_query_time%';

设置慢的阈值时间

1
set global long_query_time=3;

设置后是看不出变化的, 需要重新连接或新开一个会话才能看到修改值

1
2
show variables like 'long_query_time%';
show global variables like 'long_query_time';
使用mysqldumpslow 查询
1
2
mysqldumpslow -s r -t 10 /var/lib/mysql/yiqing-slow.log
...

批量数据脚本

往表里插入 1000W 条数据

  1. 建表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    # 新建库
    create database bigData;
    use bigData;

    #1 dept
    create table dept(
    	id int unsigned primary key auto_increment,
      deptno mediumint unsigned not null default 0,
      dname varchar(20) not null default "",
      loc varchar(13) not null default ""
    )engine=innodb default charset=GBK;

    #2 emp
    create table emp(
    	id int unsigned primary key auto_increment,
      empno mediumint unsigned not null default 0,
      ename varchar(20) not null default "",
      job varchar(9) not null default "",
      mgr mediumint unsigned not null default 0,
      hiredate date not null,
      sal decimal(7,2) not null,
      comm decimal(7,2) not null,
      deptno mediumint unsigned not null default 0
    )engine=innodb default charset=GBK;

  2. 设置参数 log_bin_trust_function_creators

  3. 创建函数,保证每条数据都不同

    1. 随机产生字符串

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      DELIMITER $$
      CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
      BEGIN
      	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
      	DECLARE return_str VARCHAR(255) DEFAULT '';
      	DECLARE i INT DEFAULT 0;
      	WHILE i < n DO
      	SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
      	SET i = i + 1;
      	END WHILE;
      	RETURN return_str;
      END $$

    2. 随机产生部门编号

      1
      2
      3
      4
      5
      6
      7
      8
      DELIMITER $$
      CREATE FUNCTION rand_num()
      RETURNS INT(5)
      BEGIN
      	DECLARE i INT DEFAULT 0;
      	SET i = FLOOR(100+RAND()*10);
      RETURN i;
      END $$

  4. 创建存储过程

    1. 创建往 emp 表中插入数据的存储过程

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      DELIMITER $$
      CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
      BEGIN
      DECLARE i INT DEFAULT 0;
      #set autocommit =0 fE autocommiti Ï FXO
      SET autocommit = 0;
      REPEAT
      SET i = i + 1;
      INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES ((START+i),rand_string(6),'SALESMAN' ,0001 ,CURDATE(),2000,400,rand_num());
      UNTIL i = max_num 
      END REPEAT;
      COMMIT;
      END $$

    2. 创建往 dept 表中插入数据的存储过程

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      DELIMITER $$
      CREATE PROCEDURE insert_dept(IN START INT(10), IN max_num INT(10))
      BEGIN
      DECLARE i INT DEFAULT 0;
      SET autocommit = 0;
      REPEAT
      SET i=i+1;
      INSERT INTO dept(deptno ,dname, loc ) VALUES((START+i) ,rand_string(10),rand_string(8));
      UNTIL i = max_num
      END REPEAT;
      COMMIT ;
      END $$

  5. 调用存储过程

    1
    2
    3
    4
    5
    6
    # dept 10 条
    DELIMITER ;
    CALL insert_dept(100,10);
    # emp 添加 50w 条
    DELIMITER ;
    CALL insert_emp(100001,500000);

Show Profile

简介

是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况. 可以用于 SQL 的调优的测量

默认情况下, 参数处于关闭状态, 并保存最近 15 次的运行结果

分析步骤

  1. 查看当前的 MySQL 版本是否支持

    1
    show variables like 'profiling';

  2. 开启

    1
    set profiling=on;

  3. 运行 SQL

    1
    select * from tbl_emp group by id%10 limit 150000;

  4. 查看结果

    1
    2
    3
    4
    5
    6
    7
    show profiles;
    +----------+------------+-----------------------------------------------+
    | Query_ID | Duration   | Query                                         |
    +----------+------------+-----------------------------------------------+
    |        1 | 0.00404850 | show variables like 'profiling'               |
    |        2 | 0.54576350 | select * from emp group by id%10 limit 150000 |
    +----------+------------+-----------------------------------------------+

  5. 诊断 SQL

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    show profile cpu, block io for query [Query_ID];
    mysql> show profile cpu, block io for query 2;

    +----------------------+----------+----------+------------+--------------+---------------+
    | Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
    +----------------------+----------+----------+------------+--------------+---------------+
    | starting             | 0.000208 | 0.000209 |   0.000000 |            0 |             0 |
    | checking permissions | 0.000051 | 0.000060 |   0.000000 |            0 |             0 |
    | Opening tables       | 0.000060 | 0.000050 |   0.000000 |            0 |             0 |
    | init                 | 0.000047 | 0.000047 |   0.000000 |            0 |             0 |
    | System lock          | 0.000035 | 0.000033 |   0.000000 |            0 |             0 |
    | optimizing           | 0.000018 | 0.000019 |   0.000000 |            0 |             0 |
    | statistics           | 0.000035 | 0.000035 |   0.000000 |            0 |             0 |
    | preparing            | 0.000055 | 0.000093 |   0.000000 |            0 |             0 |
    | Creating tmp table   | 0.000119 | 0.000062 |   0.000000 |            0 |             0 |
    | Sorting result       | 0.000031 | 0.000030 |   0.000000 |            0 |             0 |
    | executing            | 0.000010 | 0.000009 |   0.000000 |            0 |             0 |
    | Sending data         | 0.544337 | 0.542910 |   0.000000 |            0 |             0 |
    | Creating sort index  | 0.000060 | 0.000058 |   0.000000 |            0 |             0 |
    | end                  | 0.000013 | 0.000013 |   0.000000 |            0 |             0 |
    | query end            | 0.000015 | 0.000015 |   0.000000 |            0 |             0 |
    | removing tmp table   | 0.000012 | 0.000012 |   0.000000 |            0 |             0 |
    | query end            | 0.000102 | 0.000102 |   0.000000 |            0 |             0 |
    | closing tables       | 0.000020 | 0.000019 |   0.000000 |            0 |             0 |
    | freeing items        | 0.000500 | 0.000049 |   0.000000 |            0 |             0 |
    | cleaning up          | 0.000039 | 0.000038 |   0.000000 |            0 |             0 |
    +----------------------+----------+----------+------------+--------------+---------------+

  6. 要注意的结论

    1. converting HEAP to MyISAM 查询结果太大, 内存都不够用了, 只能使用磁盘了
    2. Creating tmp table 创建临时表
    3. Copying to tmp table on disk 把内存中的临时表复制到磁盘, 危险!!!
    4. locked

全局查询日志

永远不要在生产环境开启这个功能

  1. 配置启用

    在 mysql 的 my.cnf 中,设置

    1
    2
    3
    4
    5
    6
    # 开启
    general_log=1
    # 记录日志文件的路径
    general_log_file=/path/logfile
    # 输出格式
    log_output=FILE

  2. 编码启用

    1
    2
    3
    4
    5
    set global general_log=1;
    set global log_output='TABLE';
    # 此后,编写的 slq 语句,会记录到 mysql 库里的 general_log 表.
    # 查看
    select * from mysql.general_log;

MySQL锁机制

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

在数据库中, 除了传统的计算资源(CPU, RAM, IO等)的争用外, 数据也是一种供多个用户共享的资源. 如何保证数据并发访问的一致性, 有效性是所有数据库必须解决的一个问题, 锁冲突也是影响数据库并发访问性能的一个重要因素. 从这个角度来说, 锁对于数据库而言显得尤其重要, 也更加复杂.

锁的分类

对数据操作的类型(读/写)分

  • 读锁(共享锁): 针对同一份数据, 多个读操作可以同时进行而不会互相影响
  • 写锁(排它锁): 当前写操作没有完成前, 它会阻断其他写锁和读锁

从对数据操作的力度分

  • 表锁
  • 行锁

三锁

开销,加锁速度,死锁,粒度,并发性能只能就具体应用的特点来说哪种锁更合适

表锁(偏读)

特点
  • 偏向 MyISAM 存储引擎,开销小,加锁快
  • 无死锁
  • 锁定粒度大
  • 发生锁冲突的概率最高, 并发度最低
案例

  1. 建表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    create table mylock(
      id int not null primary key auto_increment,
      name varchar(20)
    )engine myisam;

    insert into mylock(name) values('a');
    insert into mylock(name) values('b');
    insert into mylock(name) values('c');
    insert into mylock(name) values('d');
    insert into mylock(name) values('e');

  2. 手动增加表锁

    1
    lock table [tablename] read(write), [tablename2] read(write),...;

  3. 查看表上加过的锁

    1
    show open tables;

  4. 释放表锁

    1
    unlock tables;
结论

MyISAM 在执行查询语句 select 前, 会自动给涉及的所有表加读锁, 在执行增删改操作前, 会自动给涉及的表加写锁.

MyISAM 的表级锁有两种模式:

  • 表共享读锁(Table Read Lock)
  • 表独占写锁(Table WriteLock)

两种情况

  • 对 MyISAM 表的读操作(加读锁), 不会阻塞其他进程对同一表的读请求, 但会阻塞对同一表的写请求. 只有当读锁释放后, 才会执行其他进程的写操作
  • 对 MyISAM 表的写操作(加写锁), 会阻塞其他进程对同一表的读和写操作, 只有当写锁释放后, 才会执行其他进程的读写操作

简而言之,就是读锁会阻塞写, 但是不会阻塞读. 而写锁则会把读和写都阻塞.

MyISAM 的读写锁调度是写优先, 这也表明 MyISAM 不适合做写为主的表的引擎. 因为一旦加了写锁, 其他线程读写都会被阻塞

行锁(偏写)

特点
  • 偏向 InnoDB 存储引擎, 开销大, 加锁慢
  • 会出现死锁
  • 锁定力度最小, 发生锁冲突的概率最低, 并发度也最高

InnoDB 与 MyISAM 最大不同有两点: 一是支持事务; 二是采用了行级锁

简单复习数据库并发问题
  • 更新丢失 Lost Update: 两个或多个事务选择同一行,每个事务不知道其他事务存在, 最后的更新会覆盖其他事务的更新操作
  • 脏读 Dirty Reads: 事务 A 读到了事务 B 已修改但未提交的数据. 如果事务 B 回滚,A 读取的数据无效, 不符合一致性要求
  • 不可重复读 Non-Repeatable Reads: 事务 A 读到了 B 已提交的修改数据, 不符合隔离性
  • 幻读Phantom Reads: 事务 A 读到了事务 B 提交的新增数据, 不符合隔离性; 和脏读有点类似, 脏读是修改,幻读是新增数据
数据库隔离级别

上面的脏读,不可重复读,幻读,其实都是数据库一致性问题,必须由数据库提供一定的事务隔离机制来解决

级别 读数据一致性 脏读 不可重复读 幻读
未提交读(read uncommitted) 最低级别,只能保证不读取物理上损坏的数据 是 是 是
已提交读(read committed) 语句级 否 是 是
可重复读(repeatable read) 事务级 否 否 是
可序列化(serializable) 最高级别,事务级 否 否 否

事务隔离约严格, 并发副作用越小, 付出的代价就越大, 因为事务隔离实质上就是使事务在一定程度上”串行化”进行, 这显然与”并发”是矛盾的.

不同的应用对于读一致性和数据库隔离程度的要求也是不同的

查看数据库的事务隔离级别: show variables like 'tx_isolation';

MySQL默认级别是可重复读(REPEATABLE READ)

无索引行锁升级为表锁

比如使用了InnoDB 自己底层做了类型转换, 使得索引失效, 会导致行锁变成表锁.

间隙锁危害
  1. 什么是间隙锁
    1. 当我们用范围条件而不是相等条件检索数据, 并请求共享或排他锁时, InnoDB 会给符合条件的已有数据记录的索引加锁; 对于键值在条件范围内但不存在的记录, 叫做”间隙(GAP)”
    2. InnoDB 也会对这个”间隙”加锁, 这种锁机制就是所谓的间隙锁(Next-Key 锁).
  2. 危害
    1. 因为 query 执行过程中通过范围查找的话,它会锁定整个范围内所有的索引键值, 即使这个键值不存在.
    2. 间隙锁有一个比较知名的弱点, 就是那些不存在的键值也会被无辜的锁定, 而造成在锁定的时候无法插入锁定键值范围内的任何数据. 在某些场景下可能会对性能造成很大的危害
面试题常考: 如何锁定一行?
1
select [fields] from [table_name] where [xxx] for update;

使用 select xxx for update 锁定某一行后, 其它的操作将会被阻塞, 直到锁定行的会话提交 commit

总结

InnoDB 存储引擎由于实现了行级锁定, 虽然在锁定机智的实现方面带来的性能损耗可能比表级锁定会要更高一点, 但是在整体并发处理能力方面要远远优于 MyISAM 的表级锁定的. 当系统并发量较高的时候, InnoDB 的整体性能和 MyISAM 相比就会有比较明显的优势

行锁分析

检查状态分析行锁争夺情况

1
show status like 'innodb_row_lock%';

重要的三个变量

  • Innodb_row_lock_current_waits: 当前正在等待锁定的数量
  • Innodb_row_lock_time_avg: 从系统启动到现在锁定总时间长度
  • Innodb_row_lock_waits: 从系统启动到现在总共等待的次数
优化建议
  • 尽可能让所有数据检索都用索引来完成, 避免无索引行锁升级为表锁
  • 合理设计索引, 尽量缩小锁的范围
  • 尽可能较少检索条件, 避免间隙锁
  • 尽量控制事务大小, 减少锁定资源量和时间长度
  • 尽可能低级别事务隔离

页锁

开销和加锁时间介于表锁和行锁之间

会出现死锁

锁定力度介于表锁和行锁之间,并发度一般

只需了解一下

主从复制

基本原理

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

三步骤

  1. master 将该表记录到二进制日志 (binary log). 这些记录过程叫做二进制日志事件, binary log events;
  2. slave 将 master 的 binary log events 拷贝到它的中继日志 (relay log);
  3. slava 重做中继日志中的事件, 将改变应用到自己的数据库中. MySQL 复制是异步的且串行化的

基本规则

  • 每个 slave 只有一个 master
  • 每个 slave 只能有一个唯一的服务器 ID
  • 每个 master 可以有多个 slave

最大问题

延时

一主一从常见配置

[详见另一篇 blog ]

YiqingWeb Developer

一位打工人