Mysql视频学习总结

刺骨的言语ヽ痛彻心扉 2023-02-17 15:57 13阅读 0赞

### Mysql视频学习总结 ###

*  1.索引优化与分析
 *   *  1.1.索引简介
     *  1.2.性能分析
     *   *  1.2.1.Mysql Query Optimizer
         *  1.2.2.Mysql常见的瓶颈：
         *  1.2.3.explain
     *  1.3.索引优化
     *   *  1.3.1.索引分析
         *  1.3.2.索引失效（避免）
         *   *  1.3.2.1一般建议：
 *  2.查询截取分析
 *   *  2.2.查询优化
     *  2.2.慢查询日志
     *  2.3.批量数据脚本
     *  2.4.Show Profile
     *  2.5.全局查询日志（测试环境）
 *  3.mysql 的锁
 *  4.主从复制

# 1.索引优化与分析 #

## 1.1.索引简介 ##

**索引结构**： BTree索引、Hash索引、full-text全文索引、R-Tree索引。

应该建立索引：

1.  主键自动建立唯一索引
2.  频繁作为查询条件的字段
3.  查询中与其他表关联的字段，外键关系建立索引
4.  频繁更新的字段不适合建立索引
5.  Where条件用不到的字段不适合建立索引
6.  单键/组合索引选择问题
7.  查询中排序字段，排序字段通过索引访问会大大提高排序速度
8.  查询中统计或者分组字段

不应该应该建立索引：

1.  表记录太少
2.  经常增删改的表
3.  数据重复且分布平均的表字段

## 1.2.性能分析 ##

### 1.2.1.Mysql Query Optimizer ###

### 1.2.2.Mysql常见的瓶颈： ###

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

### 1.2.3.explain ###

语法：

explain select ...

使用extended可以在原来查询计划上提供一些优化的信息  
**语法**:explain extended select …,然后通过show warnings来查看相应的优化信息，测试时发现一个警告，含义EXTENDED将被移除\[本地环境mysql5.7\]  
尝试不使用EXTENDED参数，执行show warning明显，发现确实是可以使用的并消除了这个警告

虽然不同版本的存储引擎执行计划不完全相同，但基本信息一致

id: 1  
     select_type: SIMPLE  
           table: student  
      partitions: NULL  
            type: ALL   
    ossible_keys: NULL  
             key: NULL  
         key_len: NULL  
             ref: NULL  
            rows: 2 
        filtered: 100.00    
           Extra: NULL

下面进行解释：  
**id**:由一组数字组成，表示子查询执行的顺序。  
id相同 执行顺序自上而下

explain SELECT 
    	i.cust_id AS '客户ID',  
    	i.cust_name AS '客户姓名',  
    	IF (l.cust_lmt < '1000',1000,  l.cust_lmt) AS '额度'    
    FROM    
    	cust_inf i  
    	JOIN cust_lmt l ON i.cust_id = l.cust_id ;

**id不同**，id越大优先级越高，越先被执行

**id不同和id相同同时存在**，优先根据id排序，然后在id相同的内部，自左向右进行执行。

explain	SELECT * FROM student union SELECT * FROM student ;

id为null时表示是一个结果集，不需要使用他查询，常出现包含union等查询语句中

**select\_type**  
每个子查询的查询类型，常见如下表

<table> 
 <thead> 
  <tr> 
   <th>select_type</th> 
   <th>description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIMPLE</td> 
   <td>不包含任何子查询或union等查询</td> 
  </tr> 
  <tr> 
   <td>PRIMARY</td> 
   <td>包含子查询，最外层查询显示为PRIMARY</td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>在select或where子句中</td> 
  </tr> 
  <tr> 
   <td>DERIVED</td> 
   <td>from字句中包含的子查询被标记为DRIVED（衍生表）mysql会递归执行这些子查询，把结果放到临时表中</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>出现在union后的查询语句中</td> 
  </tr> 
  <tr> 
   <td>UNION RESULT</td> 
   <td>从UNION中获取结果集</td> 
  </tr> 
 </tbody> 
</table>

**table**  
查询涉及到的数据表  
如果查询使用别名，就显示别名，如果不涉及对数据表的操作，显示为null，如果是尖括号括起来的就标识这个是临时表，后边的N就是执行计划中的id,表示结果  
来自于这个查询产生。如果是尖括号括来<union M,N>，与类似，也是一个临时表，标识这个结果来自于union查询的id为M,N的结果集。

**type**  
访问类型：  
ALL 扫描全表数据  
index 遍历索引  
range 索引范围内查找  
index\_subquery在子查询中使用ref  
unique\_subquery在子查询中使用eq\_ref  
ref\_or\_null 对Null进行优化的ref  
fulltext 使用全文索引  
ref 使用非唯一索引查找数据  
eq\_ref 在join查询中使用PRIMARY KEY or UNIQUE NOT NULL索引关联  
const使用主键或者唯一索引，且匹配结果只有一条记录  
system const链接类型的特例，查询的表为系统表

性能从好到差依次为：system，const，eq\_ref，ref，fulltext，ref\_or\_null，unique\_subquery，index\_subquery，range，index\_merge，index，ALL,除了ALL之外，其他type类型都可以使用到索引，除了index\_merge之外，其他的type类型只可以用到一个索引。  
所以通过执行查询计划查下某张表的查询type为ALL，可以考虑添加索引，或者更换查询方式，使用索引进行查询。  
**system**：表中只有一行记录（等于系统表）,这就是const类型的特例，平时不会出现。  
**const**：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如 将主键放到where列表中，mysql就能将查询转换成一个常量。  
**eq\_ref**:唯一性索引扫描，对于每个索引键表中只有一条记录与之匹配，常见于主键或者唯一索引扫描。  
**ref**：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，他返回匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以应该属于查找和扫描的混合体。  
**range**：只检索给定范围的行，使用一个索引来选择行。key显示使用了那个索引，一般where条件语句中出现between、<>、in等的查询。这种范围扫描比全表扫描要好，因为只需要扫描开始索引于某一点结束于另外一点，不用全表扫描。  
**index**：Full Index Scan，index与all的区别，index 只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说all和index虽然都是读全表的但是index是从索引中读取，而all是从硬盘中读取）。  
**all**： Full table scan，遍历全表找到匹配的行

**possible\_keys**  
可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来，当该列为NULL时就要考虑当前SQL是否需要优化了。

**key**  
显示MySQL在查询中使用的索引，若没有引用索引，显示为NULL  
提示：查询中若使用了覆盖索引（覆盖索引：索引的数据覆盖了需要查询的所有数据），则该列仅出现在key列表中  
select\_type为index\_merge时，这里可能出现两个以上的索引，其他的select\_type这里只会出现一个。

**key\_length**  
索引长度char()、varchar()索引长度的计算公式

(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)

其他类型索引长度的计算公式：

CREATE TABLE `student` (
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `name` varchar(128) NOT NULL DEFAULT '',
     `age` int(11),
     PRIMARY KEY (`id`),
     UNIQUE KEY `idx` (`name`),
     KEY `idx_age` (`age`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

name索引长度为：编码为utf8mb4，列长为128,不允许为NULL,字段类型为varchar(128)。key\_length=128\*4+0+2=514;  
同理,age索引长度：int类型占4位，允许为null，索引长度为5。

**ref**  
表示上述表的连接匹配条件，即那些列或常量被用于查找索引列的值  
如果是使用常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这类会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

**rows**  
返回估算的结果集数目，注意这并不是一个准确值。

**extra**  
extra的信息非常丰富，常见的有：  
**Using filesort**使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。  
**Using temporary**使用了临时表，mysql对查询的结果集排序时使用的临时表，常见于排序的order by和分组group by  
。尽量优化。  
**Using index**使用覆盖索引  
Using where使用了where子句过滤结果集  
Using join buff使用连接缓存  
impossible where表示where子句永远为false  
select tables optimized away  
distinct

## 1.3.索引优化 ##

### 1.3.1.索引分析 ###

1.  单表索引  
    涉及多字段查询的时候，根据where顺序建立索引，当中间有<,>,between等的时候会使ref变成range，并产生Using filesort，需要跳过范围查询。
2.  双表索引  
    对于左外连接，查询的结果是左表的全部，因此即使在左表建立索引，仍然会扫描整个表，在右表建立索引，会使用索引，加快查询速度。右外连接，反之亦然。
3.  三表查询  
    如何使用inner join，mysql会自动使用“Using join buff”，但是左/右外连接需要建立与双表一样需要建立两个索引

总结

1.  尽可能减少join语句中的nestedLoop的循环总次数，“小结果集驱动大结果集”
2.  优先优化nestedLoop的内层循环：
3.  保证join语句中被驱动的字段被索引
4.  当无法保证被驱动表的join条件被索引且内存资源充足的情况下，不要吝啬JoinBuffer的设置。

### 1.3.2.索引失效（避免） ###

1.  全值匹配我最爱（不会失效）
2.  最佳左前缀法则：如果查询中索引了多列，要遵守此法则，指查询中从索引的最左列开始并且不能跳过索引中的列。
3.  不在索引列做任何操作（计算、函数、（自动/手动）类型转换）,会导致索引失效，进而全表扫描
4.  存储引擎不能使用索引中范围范围条件右边的列
5.  尽量使用覆盖索引（只访问索引列的查询（索引列和查询列一致）），减少select \*
6.  在使用不等于（!=或者<>）无法使用索引会导致全表扫描
7.  is null和is not null也无法使用索引
8.  like以通配符开头(’%abc’),索引会失效转变成全表扫描。使用覆盖索引可以避免%abc%的全表扫描。
9.  字符串不加单引号会导致失效。
10. 少用or，用它连接也会导致索引失效。

注意：定值、范围还是排序，一般order by是给个范围  
group by基本上都需要排序，会有临时表产生。

#### 1.3.2.1一般建议： ####

1.  对于单键索引，尽量选择针对当前query过滤性更好的索引。
2.  在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中最靠前最好
3.  在选择组合索引的时候，尽量选择可以包含到当前query的where中更多字段的索引
4.  尽可能通过分析统计信息调整query写法来达到选着合适索引的目的

# 2.查询截取分析 #

## 2.2.查询优化 ##

永远小表驱动大表  
order by子句，尽量使用index方式排序（效率高），避免使用fileSort方式排序  
order by满足两种情况，会使用index排序：

1.  order by 语句使用索引最左前列
2.  使用Where子句与order by子句组合满足最左前列

filesort有两种排序算法：双路排序和单路排序  
双路排序：mysql4.1前使用，两次扫描磁盘，得到最终数据，读取行指针和order列，对他们进行排序。然后扫描已经排好序的列表，然后按照列表中的值重新读取数据。从磁盘中读取排序字段，在buffer中进行排序，再从磁盘读取其他字段。

单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，效率更高一些，避免了二次读取数据，并且把随机I/O变成了顺序I/O，但是会使用更多的内存空间。同时，单路还有一个问题是当一次数据总大小超过sort\_buffer的容量，导致每次只能读取sort\_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort\_buffer容量大小，从而多次I/O

**提高order by的速度的方式**

1.  order by只查询需要的字段，只要使用sort by就尽量禁用select \*
2.  尝试提高sort\_buffer\_size
3.  尝试提高max\_length\_for\_sort\_data

**提高group by的速度的方式**

1.  group by实际上是先排序后进行分组，遵照索引的最佳左前缀
2.  当无法使用索引列的时候，增加max\_length\_for\_sort\_data参数的设置+增大sort\_buffer\_size参数的设置
3.  where高于having，能写到where的条件，就不要写到having

## 2.2.慢查询日志 ##

默认不开启

-- 查询是否开启
    SHOW VARIABLES LIKE 'slow_query_log%';
    -- 开启 [只对当前数据库生效，重启失效]
    set global slow_query_log = 1;
    -- 如果需要永久开启，my.cnf添加
    slow_query_log=1
    slow_query_log_file=D:\Applications\mysql-5.7.23-winx64\data\hostname-slow.log 
    -- 查看慢查询时长，默认10s
    SHOW VARIABLES LIKE 'long_query_time%';
    -- 查看慢查询条数
    show global status like '%slow_queries%';

日志分析工具mysqldumpslow

## 2.3.批量数据脚本 ##

利用存储过程编写生成随机字符串函数，编写存储过程批量插入数据

## 2.4.Show Profile ##

-- 查看是否开启
     show variables like 'profiling';
     -- 开启
     set profiling = 1;
     -- 运行sql
     -- 查看结果
     show profiles;
     -- 查看cpu io等信息
     show profile cpu,block io for query 2;

**需要注意的点（必须优化）**  
converting HEAP to MyISAM查询结果太大了，内存不够用，都向磁盘上搬  
Creating tmp table创建临时表 （拷贝数据到临时表，用完再删除）  
Copying to tmp table on disk把内存中的临时表复制到磁盘，危险！！  
locked

## 2.5.全局查询日志（测试环境） ##

--命令启用
    set global general_log= 1
    --将结果记录到表中
    set global log_output = 'TABLE';
    查看记录
    select * from mysql.general_log;

# 3.mysql 的锁 #

**表锁（偏读）**

手动添加表锁

-- 查看表上的锁
    show open tables;
    -- 手动加锁
    lock table 表名字 read（write），表名字2read（write）;
    -- 释放锁
    unlock tables;

分析表锁定  
可以通过table\_locks\_waited和table\_locks\_immediate状态变化来分析系统上的表锁定.  
table\_locks\_immediate:产生表级锁的次数，表示可以立即获取锁的次数，每次立即获取锁值+1;  
table\_locks\_waited:出现表级锁争用而发生等待的次数（不能立即获取锁，次数+1），值高说明存在严重的锁争用

show status like 'table%';

myisam中读写锁，写锁优先，这也是myisam不适合做以写为主的表引擎。因为写锁后，其他线程不能进行任何操作，大量的更新会使查询很难得到锁，进而导致永久阻塞。

**行级锁（偏写）**  
无索引会导致行锁变成表锁  
间隙锁的危害：当我们使用范围条而不是相等条件检索数据，并请求共享锁或排它锁时，InnoDB会给符合条件的已有数据记录索引添加锁；对于键值在查询范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个间隙进行加锁，这个锁机制就是间隙锁（Next-key锁）

分析行锁定

-- 查看行锁
    show status like 'innodb_row_lock%';
    -- 结果解释
    Innodb_row_lock_current_waits:当前正在等待锁的数量；
    Innodb_row_lock_time:从系统启动到现在锁定总时间长度；
    Innodb_row_lock_time_avg：每次等待所花平均时间；
    Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间长度；
    Innodb_row_lock_waits:系统启动到现在总共等待的次数；

行级锁建议

1.  尽可能所有数据检索都使用索引来完成，避免无索引行锁升级表锁
2.  合理设计索引，减少锁的范围
3.  尽可能少的检索条件，避免间隙锁
4.  尽量控制事务大小，减少锁资源量和时间长度
5.  尽可能低级别事务级别

# 4.主从复制 #

1.  主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。
2.  从服务器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。
3.  从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。