MYSQL 高级

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查询，或者说等值查询。

　　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);