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

 

 

 

 

    

 

posted @ 2021-06-01 23:53  wangheng1409  阅读(491)  评论(0编辑  收藏  举报