MYSQL 高级

2021-10-27 05:26:08

SQL查询流程:

1. 通过客户端/服务器通信协议与 MySQL 建立连接
2. 查询缓存，这是 MySQL 的一个可优化查询的地方，如果开启了 Query Cache 且在查询缓存过程中查询到完全相同的 SQL 语句，则将查询结果直接返回给客户端；如果没有开启Query Cache 或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成解析树。
3. 预处理器生成新的解析树。
4. 查询优化器生成执行计划。
5. 查询执行引擎执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的 API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由MySQL Server 过滤后将查询结果缓存并返回给客户端。若开启了 Query Cache，这时也会将SQL 语句和结果完整地保存到 Query Cache 中，以后若有相同的 SQL 语句执行则直接返回结果。

MySQL物理文件:

日志文件:

error log 错误日志排错 /var/log/mysqld.log【默认开启】
bin log 二进制日志备份增量备份 DDL DML DCL
Relay log 中继日志复制接收 replication master
slow log 慢查询日志调优查询时间超过指定值

-- 查看错误日志文件路径
show variables like ‘log_error‘;
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| log_error | /var/log/mysqld.log |
+---------------+---------------------+

-- 慢查询日志文件路径
show variables like ‘slow_query_log_file‘;
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
-- bin log 日志文件 需要在 my.cnf 中配置
log-bin=/var/log/mysql-bin/bin.log
server-id=2
-- 查看 relay log 相关参数
show variables like ‘%relay%‘

配置文件&数据文件:

配置文件 my.cnf:

　　在 my.cnf 文件中可以进行一些参数设置, 对数据库进行调优。

[client] #客户端设置，即客户端默认的连接参数
port = 3307 #默认连接端口
socket = /data/mysqldata/3307/mysql.sock #用于本地连接的socket套接字
default-character-set = utf8mb4 #编码
[mysqld] #服务端基本设置
port = 3307 MySQL监听端口
socket = /data/mysqldata/3307/mysql.sock #为MySQL客户端程序和服务器之间的本地通讯指定一
个套接字文件
pid-file = /data/mysqldata/3307/mysql.pid #pid文件所在目录
basedir = /usr/local/mysql-5.7.11 #使用该目录作为根目录（安装目录）
datadir = /data/mysqldata/3307/data #数据文件存放的目录
tmpdir = /data/mysqldata/3307/tmp #MySQL存放临时文件的目录
character_set_server = utf8mb4 #服务端默认编码（数据库级别）

-- 查看数据文件的位置
show variables like ‘%dir%‘;
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| datadir | /var/lib/mysql/ |
+-----------------------------------------+----------------------------

1、.frm文件
不论是什么存储引擎，每一个表都会有一个以表名命名的.frm文件，与表相关的元数据（meta)信息都存放在
此文件中，包括表结构的定义信息等。
2、.MYD文件
myisam存储引擎专用，存放myisam表的数据（data)。每一个myisam表都会有一个.MYD文件与之呼应，同
样存放在所属数据库的目录下
3、.MYI文件
也是myisam存储引擎专用，存放myisam表的索引相关信息。每一个myisam表对应一个.MYI文件，其存放的
位置和.frm及.MYD一样
4、.ibd文件
存放innoDB的数据文件（包括索引）。
5. db.opt文件 此文件在每一个自建的库里都会有，记录这个库的默认使用的字符集和校验规。

MySQL查询和慢查询日志分析:

　　等待时间长:

1.锁表导致查询一直处于等待状态，后续我们从MySQL锁的机制去分析SQL执行的原理

　　执行时间长:

1.查询语句写的烂
2.索引失效
3.关联查询太多join
4.服务器调优及各个参数的设置

需要遵守的优化原则:

第一条: 只返回需要的结果

　　　　一定要为查询语句指定 WHERE 条件，过滤掉不需要的数据行

　　　　避免使用 select * from , 因为它表示查询表中的所有字段

第二条: 确保查询使用了正确的索引

经常出现在 WHERE 条件中的字段建立索引,可以避免全表扫描；

将 ORDER BY 排序的字段加入到索引中，可以避免额外的排序操作；

多表连接查询的关联字段建立索引，可以提高连接查询的性能；

将 GROUP BY 分组操作字段加入到索引中，可以利用索引完成分组。

第三条: 避免让索引失效

在 WHERE 子句中对索引字段进行表达式运算或者使用函数都会导致索引失效

使用 LIKE 匹配时，如果通配符出现在左侧无法使用索引

如果 WHERE 条件中的字段上创建了索引，尽量设置为 NOT NULL

SQL的执行顺序:

　　我们写的sql:

sql的执行顺序:

MYSQL的7种join:

慢查询日志分析:

　 MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句。

　默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。

如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件和数据库表。

SHOW VARIABLES LIKE "%query%" ;

slow_query_log：是否开启慢查询日志， 1 表示开启， 0 表示关闭。
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。
long_query_time： 慢查询阈值，当查询时间多于设定的阈值时，记录日志。

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的

mysql> SHOW VARIABLES LIKE ‘%slow_query_log%‘;
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

可以通过设置slow_query_log的值来开启

mysql> set global slow_query_log=1;
mysql> SHOW VARIABLES LIKE ‘%slow_query_log%‘;
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

　　使用 set global slow_query_log=1 开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。

　　如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

-- 编辑配置
vim /etc/my.cnf
-- 添加如下内容
slow_query_log =1
slow_query_log_file=/var/lib/mysql/lagou-slow.log
-- 重启MySQL
service mysqld restart
mysql> SHOW VARIABLES LIKE ‘%slow_query_log%‘;
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/lagou-slow.log |
+---------------------+-------------------------------+

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？

这个是由参数 long_query_time 控制，默认情况下long_query_time的值为10秒

mysql> show variables like ‘long_query_time‘;
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

注意：使用命令 set global long_query_time=1 修改后，需要重新连接或新开一个会话才能看到修改值。

mysql> set global long_query_time=1;

mysql> show variables like ‘long_query_time‘;
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

log_output 参数是指定日志的存储方式。 log_output=‘FILE‘ 表示将日志存入文件，默认值是‘FILE‘。

log_output=‘TABLE‘ 表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log 表中。

mysql> SHOW VARIABLES LIKE ‘%log_output%‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+

系统变量 log-queries-not-using-indexes ：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。

mysql> show variables like ‘log_queries_not_using_indexes‘;
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |


mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘log_queries_not_using_indexes‘;

+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)

MySQL存储引擎:

InnoDB(推荐):

　　优点:

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别
支持多版本并发控制的行级锁，由于锁粒度小，写操作和更新操作并发高、速度快。
支持自增长列。
支持外键。
适合于大容量数据库系统，支持自动灾难恢复。

缺点:

它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表

应用场景 :

当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率
更新密集的表, InnoDB存储引擎特别适合处理多重并发的更新请求

MyISAM:

优点:

MyISAM存储引擎在查询大量数据时非常迅速，这是它最突出的优点
另外进行大批量插入操作时执行速度也比较快。

缺点:

MyISAM表没有提供对数据库事务的支持。
不支持行级锁和外键。
不适合用于经常UPDATE（更新）的表，效率低。

应用场景:

以读为主的业务，例如：图片信息数据库，博客数据库，商品库等业务。
对数据一致性要求不是非常高的业务（不支持事务）
硬件资源比较差的机器可以用 MyiSAM （占用资源少）

MySQL索引优化:

普通索引:

CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

唯一索引:

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

主键索引:

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);

复合索引:

　　用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引注意事项:

1. 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
2. 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

全文索引:

　　查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如:

SELECT * FROM users3 WHERE MATCH(NAME) AGAINST(‘aabb‘);
-- * 表示通配符,只能在词的后面
SELECT * FROM users3 WHERE MATCH(NAME) AGAINST(‘aa*‘ IN BOOLEAN MODE);

全文索引使用注意事项：

全文索引必须在字符串、文本字段上建立。
全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84； myisam：4-84）

创建索引的原则:

在经常需要搜索的列上创建索引，可以加快搜索的速度；
在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
group by字段

索引原理:

　　hash 结构:

　　　　Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据 key查找value值，也就是单个key查询，或者说等值查询。

　　B+Tree结构

　　　　非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值叶子节点包含了所有的索引值和data数据叶子节点用指针连接，提高区间的访问性能

EXPLAIN性能分析:

id:

　　id相同，执行顺序由上至下

　　id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

select_type:

　　simple : 简单的select查询，查询中不包含子查询或者UNION

　　primary : 查询中若包含任何复杂的子部分，最外层查询被标记

　　subquery : 在select或where列表中包含了子查询

union : 如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived

type介绍:

　　type显示的是连接类型，是较为重要的一个指标。

　　下面给出各种连接类型,按照从最佳类型到最坏类型进行排序:

　　　　system > const > eq_ref > ref > range > index > ALL

system : 表仅有一行 (等于系统表)。这是const连接类型的一个特例,很少出现。

const : 表示通过索引一次就找到了, const用于比较 primary key 或者 unique 索引. 因为只匹配一行数据,所以如果将主键放在 where条件中, MySQL就能将该查询转换为一个常量

eq_ref : 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配. 常见与主键或唯一索引扫描

ref : 非唯一性索引扫描, 返回匹配某个单独值的所有行, 本质上也是一种索引访问, 它返回所有匹配某个单独值的行, 这是比较常见连接类型.

range : 只检索给定范围的行,使用一个索引来选择行。

index : 出现index 是 SQL 使用了索引, 但是没有通过索引进行过滤,一般是使用了索引进行排序分组

ALL : 对于每个来自于先前的表的行组合,进行完整的表扫描。

possible_keys:显示可能应用到这张表上的索引, 一个或者多个. 查询涉及到的字段上若存在索引, 则该索引将被列出, 但不一定被查询实际使用

key :实际使用的索引

key_len介绍:

表示索引中使用的字节数, 可以通过该列计算查询中使用索引的长度.

CREATE TABLE T1(
a INT PRIMARY KEY,
b INT NOT NULL,
c INT DEFAULT NULL,
d CHAR(10) NOT NULL
);

EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;

ALTER TABLE T1 ADD INDEX idx_b(b);

ALTER TABLE T1 ADD INDEX idx_d(d);