目录

  • 第一章MySQL架构介绍
  • MySQL简介
  • MySQLLinux版的安装
  • MySQL配置文件
  • MySQL逻辑架构介绍
  • MySQL存储引擎
  • 第二章索引优化分析
  • 索引简介
  • 性能分析
  • 索引优化
  • 第三章查询截取分析
  • 查询优化
  • 慢查询日志
  • 批量数据脚本
  • Show Profile
  • 全局查询日志
  • 第四章MySQL锁机制
  • 第五章主从复制


第一章MySQL架构介绍

MySQL简介

MySQLLinux版的安装

MySQL配置文件

MySQL逻辑架构介绍

  • 总体概览
    插件式的存储引擎架构将查询处理和其他的系统任务以及数据存储提取相分离。
    1.连接层
    2.服务层
    3.引擎层
    4.存储层
  • 查询说明

MySQL存储引擎

  • 常用InnoDB和MyISAM

第二章索引优化分析

  • 性能下降SQL慢、执行时间长 、等待时间长：查询语句写的差；索引失效（单值、复合）；关联查询join太多（设计缺陷或不得已的需求）；服务器调优及各个参数设置（缓冲、线程数等）
  • 常见通用的join查询
    SQL执行加载顺序：from→on join→where→group by→having→select→distinct→order by→limit

索引简介

  • 在数据之外，数据库还维护着满足特定查找算法的数据结构，以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引；
    在没有特别指明的情况下，索引都是指B树（多路搜索树）结构组织的索引；
    索引本身也很大，往往以索引文件的形式存储在磁盘上；
  • 优势：提高数据检索效率，降低数据库IO成本；降低排序成本和CPU消耗；
  • 劣势：占用空间；降低了更新表的速度；消耗时间建立高效的索引；
  • 索引分类：
    ①单值索引：一个索引只包含单列，一个表可以有多个单值索引；
    ② 唯一索引：索引列的值必须唯一，但允许有空值；
    ③ 复合索引：一个索引包含多个列；
  • 操作：
    ①创建 create【unique】index 索引名 on 表名（列名（length））；alter 表名 add 【unique】index 索引名on 列名（length）；
    ②删除：drop index 索引名on 表名；
    ③查看：show index from 表名
  • MySQL索引结构：BTree索引、Hash索引；full-text全文索引；R-Tree索引
  • 哪些情况需要创建索引：主键自动建立唯一索引；频繁作为查询条件的字段；查询中与其他表关联的字段，外键关系建立索引；在高并发下倾向创建组合索引；查询中统计或分组字段
  • 哪些情况不要创建索引：表记录太少；经常增删改的表；数据重复且分布平均的表字段；频繁更新的字段不适合创建索引；

性能分析

  • MySQL Query Optimizer：优化select语句的优化模块
  • MySQL常见瓶颈
    ①CPU：CPU饱和一般发生在数据装入内存或从磁盘上读取数据时；
    ②IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候；
    ③服务器硬件性能瓶颈：top、free、iostat和vmstat来查看系统的性能状态；
  • 关键字EXPLAIN
    ①是什么：模拟优化器执行SQL查询语句
    ②能干嘛：表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少行被优化器查询；
    ③怎么用：explain + SQL语句；
    执行计划包含的信息：id、select_type、table、type、possible_keys、key、key_len、ref、rows、extra；
  • id：
    ①select查询的序列号，包含一组数字，表示查询中执行select子句或操作的表顺序；
    ②三种情况：
    id相同：从上到下；
    id不同：如果是子查询，id越大优先级越高；
    id相同/不同同时存在：id相同，可以认为是一组，从上往下执行；在所有组中，id越大优先级越高；
  • select_type：
    ①simple：查询中不包含子查询或union；
    ②primary：查询中若包含任何的子查询，最外层的查询为primary
    ③subquery：在select或where列表中包含了子查询；
    ④derived：在from列表中包含的子查询被标记为derived（衍生），放在临时表中；
    ⑤union：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select被标记为derived；
    ⑥union result：从union表获取结果的select
    -table： 显示这一行的数据是关于哪张表的；
  • type：显示查询使用了何种类型
    ① 从好到差排列为：system>const>eq_ref>ref>range>index>all
    ②一般来说，得保证查询至少达到range级别，最好能达到ref
    ③system：表只有一行记录；
    ④const：通过一次索引就找到了，用于比较primary_key或者unique索引；
    ⑤eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或唯一索引扫描；
    ⑥ref：非唯一性索引扫描，返回匹配某个单独值的所有行；
    ⑦range：只检索给定范围的行，使用一个索引来选择行；
    ⑧index：full index scan，index与all区别为index类型只遍历索引树
  • possible_keys：显示可能应用在这张表的索引，一个或多个；
  • key：实际使用的索引，如果为null。则没有使用索引；查询中若使用了覆盖索引，则该索引仅出现在key列表中；
  • key_len：表示索引中使用的字节数，可以通过该列计算查询中使用的索引长度；显示为索引字段的最大可能长度，并非实际使用长度；
  • ref：显示索引的哪一列被使用了，如果可能的话，显示一个常数。
  • rows：根据表统计信息及索引选用情况，大致估算出找到所需要读取的行数；越小越好；
  • extra：包含不适合在其他列显示但十分重要的额外信息；
    ①using filesort：MySQL无法利用索引完成的排序操作称为“文件内排序”；
    ②using temporary：使用了临时表保存中间结果，常见于排序group by和order by
    ③using index：表示相应的select操作中使用了覆盖索引（covering index），避免访问表的数据行；如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有，表明索引用来读取数据而非查找动作；
    覆盖索引：select的数据列只从索引中就能获取，不用读取数据行
    ④using where：表明使用了where过滤
    ⑤using join buffer：表明使用了连接缓存
    ⑥impossible where：where子句的值总是false，不能用来获取任何元组
    ⑦select tables optimized away：在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化count(*)操作。查询执行计划生成的阶段即完成优化；
    ⑧distinct：优化distinct操作；

索引优化

  • 索引分析
  • 索引失效（应该避免）
    ①全值匹配
    ②最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引最左前列开始并不跳过索引中的列；
    ③不在索引列上做任何操作（计算表 、函数、类型转换），会导致索引失效而转向全表扫描；
    ④存储引擎不能够使用索引中范围条件右边的列；
    ⑤尽量使用覆盖索引（只访问索引的查询），减少select*
    ⑥mysql在使用不等于的时候无法使用索引，会导致全表扫描；
    ⑦is null，is not null也无法使用索引
    ⑧like以通配字符开头（%abc），mysql索引失效而转向全表扫描；推荐用覆盖索引解决该问题
    ⑨字符串不加单引号索引失效；
    ⑩少用or，用它来连接时索引会失效；

第三章查询截取分析

查询优化

  • 小表驱动大表，即小的数据集驱动大的数据集。类似嵌套循环Nested Loop；
    当B表的数据集必须小于A表的数据集时，用in优于exists，反之则用exists优；
select * from A where id in (select id from B);
  • Order by关键字优化
    ①尽量使用index方式排序，避免使用filesort方式排序；
    ②尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
    ③如果不在索引列上，filesort有两种算法，mysql就要启动双路排序和双路排序
    优化策略是增大sort_buffer_size或max_length_for_sort_data参数的设置
  • Group by关键字优化
    ① group by实质是先排序后进行分组，遵照索引建的最佳左前缀；
    ②无法使用索引列时，增大sort_buffer_size或max_length_for_sort_data参数的设置；
    ③where高于having，能够用where解决的不用having；

慢查询日志

  • 是MySQL提供的一种日记记录，用来记录在MySQL中响应时间超过阀值的语句；
  • 默认情况下，MySQL数据库没有开启慢查询日志，如果不是调优需要，一般不建议开启；
  • 通过设置set global slow_query_log = 1开启；
  • 查看当前多少秒算慢 show variables like '‘long_query_time%’；
  • 设置慢的阙值时间：set global long_query_time = 3；

批量数据脚本

  1. 建表（员工表）
  2. set global log_bin_trust_function_creators
  3. 创建函数，保证每条数据都不同
// 随机产生字符串
delimiter $$ 
create function rand_string(n int) returns varchar(255)
begin
declare char_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
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 $$
//随机产生部门编号
delimiter $$ 
create function rand_num returns int(5)
begin
declare i int default 0;
set i = floor(100 + rand()*10)l
return i;
end $$
  1. 创建存储过程
delimiter $$
create procedure insert_emp(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 emp(empno, ename, job, mgr) values ((start + i), rand_string(6), 'salesman',001)
until i = max_num
end repeat;
commit;
end $$
  1. 调用存储过程
delimiter ;
call inert_dept(100,10);

Show Profile

  • 是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，用于SQL的调优的测量；
  • 默认情况下，参数处于关闭状态，并保存最近15次的运行结果
  • 分析步骤
    ①是否支持，当前版本的SQL是否支持
    ②开启功能，默认关闭
    set profiling = on;
    ③运行SQL
    ④查看结果，show profiles
    ⑤诊断SQL，show profile cpu, block io for query上一步前面的问题SQL数字号码(即ID)
    ⑥日常开发需要注意的结论
    converting heap to MyISAM 查询结果太大，内存不够用了往磁盘上搬了；
    creating tmp table创建临时表：拷贝数据到临时表/用完再删除
    copying to tmp table on disk把内存中临时表复制到磁盘，危险！！！
    locked

全局查询日志

永远不要在生产环境开启

配置启用
编码启用

第四章MySQL锁机制

  • 定义：锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源，如CPU、RAM的争用以外，数据也是一种供多用户享用的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的问题；
  • 锁的分类
    ①从对数据操作的类型分：读锁（共享锁）、写锁（排它锁）
    ②从对数据操作的粒度分：表锁、行锁
  • 三锁：表锁、行锁、页锁

第五章主从复制

  • 复制的基本原理
    ①master将改变记录到二进制日志（binary log）；
    ②slave将master的binary log events拷贝到它的中继日志（relay log）
    ③slave重做中继日志中的事件，将改变应用到自己的数据库中
    MYSQL复制是异步且串行化的
  • 复制的基本原则
    ①每个slave只有一个master
    ②每个slave只能有一个唯一的服务器ID
    ③每个master可以有多个slave
  • 复制的最大问题：延时