MySQL高阶知识

网上学习整理，侵权请告知删除

MySQL中文文档：https://www.mysqlzh.com/

存储引擎

MyISAM和InnoDB区别

常见通用的Join查询

SQL的执行顺序

机读的顺序是从from开始

七种Join链接

内连接

左链接

右链接

全连接

mysql不支持FULL OUTER JOIN 语法
可以用union来代替

索引

索引介绍

索引（index）是帮助MySQL高效获取数据的一种数据结构
本质：索引是一种数据结构
索引的目的在于提高查询效率，可以类比字典
可以理解为排好序的快速查找数据结构
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据。

索引示例：

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
我们平常说的索引，如果没有特别指明，都是B树(多路搜索树，并不一定是二叉的)结构组织的索引，其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引。除了B+树索引之外还有哈希索引（hash index）等

索引优势

1、类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
2、通过索引列队数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引劣势

1、实际上索引也是一张表，该表保存了主键与索引字段，并指向实体引表的记录，所以索引列也是要占用空间的。
2、索引大大提高了查询数据，同时却会降低更新表的速度，如果对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因更新所带来的键值变化后的所有信息。

索引分类

单值索引： 一个索引只包含的单个列，一个表可以有多个单值索引。
唯一索引： 所有列的值必须唯一，但允许有空值。
复合索引： 一个索引包含多个列。

索引结构

BTree索引

创建索引的条件

哪些条件适合创建索引：

哪些条件不适合创建索引：

条件二的原因:

性能分析

mysql常见瓶颈

Explain

Explain是什么：

Explain能干嘛：

Explain怎么用：

1、Explain + SQL语句

2、执行计划包含的信息

id：

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

三种情况

• id相同
  

• id不同
  

• id相同和不同，两种情况同时存在
  
  
  DERIVED:衍生
derived2就是id为2查询后的衍生表

select_type:

主要用于区别普通查询、联合查询、子查询等的复杂查询。

类型

类型解释

table:

显示这行数据是关于哪张表的

type:

访问类型排列
显示查询使用了何种类型
最好到最差依次是：
system > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref

possible_keys:

显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则改索引将被列出，但不一定被查询实际应用

key:

实际使用的索引，如果为null，则没有使用索引。
查询中若使用了覆盖索引，则该索引仅出现在key列表中

key_len:

ref:

显示索引的那一列被使用了，可能的话最好是一个常数。哪些列或常数被用于查找索引列上的值。

rows:

根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数。

Extra:

包含不适合在其他列中显示但十分重要的额外信息。
1、Using filesort（读取数据慢）

2、Using temparory（读取数据慢）

3、Using index





4、Using where
表明使用了where过滤

5、Using Join buffer
使用了连接缓存

6、impossible where
where子句的值总是false，不能用来获取任何元组

7、select tables optimized away

8、distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

索引优化

单表查询优化
查询category_id为1且comments大于1，views查看最多的article


第一次创建索引优化


删除索引

重新创建索引

双表查询优化

给book添加索引

优化结果

删除book表的索引

给class表添加索引

优化结果

三表查询优化

未优化的结果

给phone表和book表添加索引

优化的结果

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

Join 优化
1、尽可能减少join语句中的嵌套循环的循环总次数；用小结果集驱动大的结果集
2、优先优化嵌套循环的内层循环
3、保证join语句中被驱动表上join条件字段已经被索引
4、当无法保证被驱动表的join条件字段被索引，且内存资源充足的条件下，不要太吝惜JoinBuffer的设置

避免索引失效

创建复合索引

索引失效案例

第二条
说明：如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列

案例：

第三条说明：

案例

第四条
说明：查询语句中索引使用了范围查询，则范围查询条件右边的索引全部失效。
索引使用了范围查询会进行排序，导致索引只使用了部分，

第五条：

案例

第六条：

案例

第七条：

案例

第八条：
说明：like百分号只写右边，否者索引失效
如果使用 %字符串% 查询，最好使用覆盖索引查询（查询的列是索引列），like查询也属于范围查询但是右边的索引不会失效
"%"代表匹配0个或多个任意字符。

案例

第九条：
说明：字符串类型条件查询不使用单引号，会导致mysql自动进行类型转换，从而导致索引失效。

第十条：

案例

总结

一般性建议

查询截取分析

优化步骤：
1、慢查询的开启并捕获。
2、explain + 慢SQL分析
3、show profile查询SQL在MySQL服务器里面执行细节和生命周期情况
4、SQL数据库服务器的参数调优

查询优化

永远小表驱动大表

ORDER BY关键字优化

案例：



order by 子句尽量使用index方式排序，避免使用filesort方式排序
MySQL支持两种方式的排序，FileSort和Index,Index效率高，它指MySQL扫描索引本身完成排序。FileSort效率较低。

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
Order By 满足两种情况，会使用index方式排序：

优化策略


总结

GROUP BY 关键字优化

group by实质是先排序后分组，遵循索引建的最佳左前缀

当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置

where高于having，能写在where的限定条件就不要到having限定

慢查询日志

作用

怎么使用

说明：

日志开启：


设置慢的阙值时间:

set global long_query_time = 时间值（单位秒）；

设置阙值后查看：

记录慢SQL并后续分析：

查询当前系统中有多少条慢查询记录

SHOW global status like '%Slow_queries%';

日志分析工具mysqldumpslow

查看mysqldumpslow帮助信息

linux使用命令 mysqldumpslow --help

常用命令：

Show Profile

介绍

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

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

分析步骤

1、是否支持，看看当前的mysql版本是否支持

show variables like ‘profiling’;
或
show variables like ‘profiling%’;

2、开启功能，默认是关闭，使用前开启

set profiling=on

3、运行SQL

执行SQL语句

4、查看结果，show profiles

5、诊断SQL，show profile cpu，block io for query 上一步前面的问题SQL数字号码（就是Query_ID）

show profile cpu，block io for query 3;

6、日常开发需要注意的结论

全局查询日志

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

配置启用

编码启用

select* from mysql.general_log

MySQL锁机制

概述

案例

锁的分类

从对数据操作的类型（读\写）分

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

从对数据操作的粒度分

表锁和行锁

三锁

表锁(偏读)

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

案例：

加读锁




加写锁

结论

简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞

表锁分析

行锁(偏写)

特点

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突概率最低，并发度也最高。
InnoDB与MyISAM的最大不同有两点：
一是支持事务（TRANSACTION）
二是采用了行级锁

案例

建表SQL

行锁基本演示

无索引行锁变表锁

间隙锁危害

总结

行锁分析

show status like ‘innodb_row_lock%’;

优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
合理设计索引，尽量缩小锁的范围。
尽可能较少索引条件，避免间隙锁。
尽量控制事务大小，减少锁定资源量和时间长度。
尽可能低级别事务隔离。

页锁

开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

主从复制

复制的基本原理

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

三步骤+原理图

复制的基本原则

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

复制的最大问题

延迟

一主一从常见配置

mysql版本一致且后台已服务运行

主从都配置在**[mysqld]**结点下，都是小写

主机修改my.ini配置文件


从机修改my.cnf配置文件

因修改过配置文件，请主机+从机都重启后台mysql服务

主机从机都关闭防火墙

linux关闭防火墙命令：service iptables stop

在windows主机上建立账户并授权slave

flush privileges

在Linux从机上配置需要复制的主机

可能遇到的问题