MySQL

事务隔离级别、锁、索引的数据结构、聚簇索引和辅助索引、最左匹配原则、查询优化( explain 等命令)

推荐文章

http://hedengcheng.com/?p=771

https://tech.meituan.com/2014/06/30/mysql-index.html

http://hbasefly.com/2017/08/19/mysql-transaction/

常见问题

  • Mysql(innondb 下同) 有哪几种事务隔离级别?
  • 不同事务隔离级别分别会加哪些锁?
  • mysql 的行锁、表锁、间隙锁、意向锁分别是做什么的?
  • 说说什么是最左匹配?
  • 如何优化慢查询?
  • mysql 索引为什么用的是 b+ tree 而不是 b tree、红黑树
  • 分库分表如何选择分表键
  • 分库分表的情况下,查询时一般是如何做排序的?

语法级别

union

union、union all

union 相当于加法, 合并两个查询结果并且排除掉重复的列

union all 则包含重复的列

join

left join、right join, inner join(join)

求集合 A、B 的 A, B, A & B

limit

limit m,n

从第 m + 1 条记录开始, 查出 n 条记录

反转行列

id attribute value
1 attr1 11
1 attr2 22
1 attr3 33

将上表行转列

id attr1 attr2 attr3
1 11 22 33
SELECT id,
    MAX(CASE WHEN attribute='attr1' THEN value END) AS attr1,
    MAX(CASE WHEN attribute='attr2' THEN value END) AS attr2,
    MAX(CASE WHEN attribute='attr3' THEN value END) AS attr3
FROM t
GROUP BY id;

having, group by

orderby

创建索引

CREATE INDEX indexName ON mytable(username(length)); 

函数

参见 mysql-functions

聚合函数

COUNT(col) 函数, col 为 null 时不统计在内

AVG(expression)

SUM(expression)

MAX(expression), MIN(expression)

慢查询

查询优化神器 - explain 命令

关于 explain 命令相信大家并不陌生,具体用法和字段含义可以参考官网 explain-output,这里需要强调 rows 是核心指标,绝大部分 rows 小的语句执行一定很快(有例外,下面会讲到)。所以优化语句基本上都是在优化rows。

id 列
为数字或者为no`
 `id 列相同时由上到下执行`
 `id 不同时,由大到小
select_type 列

子查询 simple 不包含子查询或者 UNION 查询 primary 查询中如果包含任何子查询,最外层的标记为 primary subquery 子查询 dependent subquery (依赖关系 子查询)

UNION查询 union union 查询的第二条或者之后的 dependent union (依赖关系 子查询 ,union 语句作为子查询,union 查询的第二条或者之后的) union result 产生的结果集 derived 出现在 from 子句中的子查询

table 列
输出表的名称`
 ` 由ID为M,N union后产生的结果集`
 `/ 由ID为N的查询产生的结果集
partitions 列
查分区表
type 列 (性能从高到低)

system (性能高) const 连接类型,当查询表只有一行时 const 表中值有且只有一行匹配,或者利用 where 查询某个常量的值,主键或唯一索引查询是效率最高的方式 eq_ref(常出现在join查询 ) 唯一索引或主键索引查找,对每个索引键,表中只有一条记录与之匹配 ref 非唯一索引查找,返回匹配某个单独值的所有行 ref_or_null 类似于ref 增加了null值的查询 index_merge 索引合并 range 索引范围素描(between , > , <) index full index scan 全索引扫描 all (性能最低) 全表扫描

key 列
(possible_keys)可能会被使用到的索引`
 `(keys)会被使用到的索引`
 `(key_len) 实际使用索引的最大长度 (字节)
ref 列
列出哪些列被用于索引查找
rows 列
预估扫描行数
filtered 列
返回行数与扫描行数的百分比(越高 就 性能越高)
extra 列
distinct 找到第一个值后立刻停止找同值的动作`
 `not exists 使用not exists优化 使用不存在于某个条件的查询`
 `using filesort 常见使用order by 或者 group by 查找`
 `using index 使用了覆盖索引(直接通过索引获取数据,不访问表)`
 `using temporary使用了临时表`
 `using where 使用了where`
 `select tables optimized away 操作已经优化到不能再优化了(MySQL根本没有遍历表或索引就返回数据了)。

慢查询优化基本步骤

0.先运行看看是否真的很慢,注意设置 SQL_NO_CACHE

1.where 条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的 where 都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高

2.explain 查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)

3.order by limit 形式的 sql 语句让排序的表优先查

4.了解业务方使用场景

5.加索引时参照建索引的几大原则

6.观察结果,不符合预期继续从0分析

版本

共同

查看所有的事务信息

SELECT * FROM information_schema.INNODB_TRX;

mysql 终端使用系统命令

system ls -lh /home/root/code/

8.0 之前

几乎所有系统配置都在 information_schema 架构下

查看所有的事务持有的锁

select * from information_schema.innodb_locks; 

查看等待锁的事务

select * from information_schema.innodb_lock_waits; 

8.0

部分系统配置被移动到 performance_schema 架构

查看所有的事务持有的锁

select * from performance_schema.data_locks;

查看等待锁的事务

select * from performance_schema.data_lock_waits;

InnoDB 存储引擎

文件

  • 参数文件:告诉 MySQL 启动时去哪里查找数据库文件,并指定初始化参数
  • 日志文件:包括错误日志、二进制日志、慢查询日志、查询日志文件等
  • socket 文件:当用 Unix 套接字进行连接时需要的文件
  • pid 文件:MySQL 进程的 PID 文件
  • 存储引擎文件

参数文件

MySQL 实例会按照一定顺序查找配置文件,用户可以通过

mysql --help | grep my.cnf

来寻找即可。

innodb_buffer_pool_size

innodb 引擎很重要的参数,默认大小 128M。MySQL 8.0 以下可以在 information_schema 架构下通过下面的命令查看

mysql> use information_schema;
Database changed
mysql> SELECT * FROM
    -> GLOBAL_VARIABLES
    -> WHERE VARIABLE_NAME LIKE 'innodb_buffer%'\G;

MySQL 8.0 可以在 performance_schema 架构下使用相同的命令

参数分动态参数和静态参数

set | [global | session] system_var_name= expr

日志文件

错误日志
mysql> show VARIABLES LIKE 'log_error'\G;
*************************** 1. row ***************************
Variable_name: log_error
        Value: E:\Dev\mysql-8.0.11-winx64\data\DESKTOP-7S7P9JO.err
1 row in set, 1 warning (0.05 sec)

可以用 tail 命令查看

C:\Users\zjp>tail -n 50 E:\Dev\mysql-8.0.11-winx64\data\DESKTOP-7S7P9JO.err
2019-12-07T20:26:00.279406Z 0 [System] [MY-013105] [Server] E:\Dev\mysql-8.0.11-winx64\bin\mysqld.exe: 
Normal shutdown.
2019-12-07T20:26:24.398545Z 0 [System] [MY-010910] [Server] E:\Dev\mysql-8.0.11-winx64\bin\mysqld.exe: 
Shutdown complete (mysqld 8.0.11)  MySQL Community Server - GPL.
2019-12-07T23:08:58.699077Z 0 [System] [MY-010116] [Server] E:\Dev\mysql-8.0.11-winx64\bin\mysqld.exe
(mysqld 8.0.11) starting as process 5604
2019-12-07T23:10:00.666296Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
慢查询日志

MySQL 默认是没有开启慢查询日志的, 可以使用以下命令查看

mysql> show VARIABLES LIKE 'slow_query_log'\G;
*************************** 1. row ***************************
Variable_name: slow_query_log
        Value: OFF
1 row in set, 1 warning (0.01 sec)

查看 long_query_time 设置

mysql> show VARIABLES LIKE 'long_query_time'\G;
*************************** 1. row ***************************
Variable_name: long_query_time
        Value: 10.000000
1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

5.6 以下查看日志文件

mysql> show VARIABLES LIKE 'log_slow_queries'\G;

5.6 以上查看日志文件

mysql> show VARIABLES LIKE 'slow_query_log_file'\G;
*************************** 1. row ***************************
Variable_name: slow_query_log_file
        Value: E:\Dev\mysql-8.0.11-winx64\data\DESKTOP-7S7P9JO-slow.log
1 row in set, 1 warning (0.01 sec)
查询日志

查询日志针对所有的 sql 请求,默认文件名:主机名.log

二进制日志
mysql> show master status\G;
*************************** 1. row ***************************
             File: binlog.000148
         Position: 155
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysql> show binlog events in 'binlog.000148'\G;
*************************** 1. row ***************************
   Log_name: binlog.000148
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 124
       Info: Server ver: 8.0.11, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000148
        Pos: 124
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 155
       Info:
2 rows in set (0.07 sec)

可以通过配置参数 log-bin [=name] 可以启动二进制日志。如果不指定 name, 默认二进制文件名是主机名,后缀名为二进制日志的序列号,所在路径为数据库所在目录(datadir)。

mysql> show variables like 'datadir'\G;
*************************** 1. row ***************************
Variable_name: datadir
        Value: E:\Dev\mysql-8.0.11-winx64\data\
1 row in set, 1 warning (0.00 sec)

二进制日志可以用于恢复、复制、审计

套接字文件

使用 Unix 系统下的本地连接 MySQL 会使用 Unix 域套接字连接,这种方式需要一个 socket 文件,一般放在 /tmp 下, 名为 mysql.sock

查看 socket 文件

mysql> show variables like 'socket'\G;
*************************** 1. row ***************************
Variable_name: socket
        Value: /tmp/mysql.sock
1 row in set, 1 warning (0.01 sec)

windows 下

mysql> show variables like 'socket'\G;
*************************** 1. row ***************************
Variable_name: socket
        Value: MySQL
pid 文件
mysql> show variables like 'pid_file'\G;
*************************** 1. row ***************************
Variable_name: pid_file
        Value: E:\Dev\mysql-8.0.11-winx64\data\DESKTOP-7S7P9JO.pid
1 row in set, 1 warning (0.01 sec)
表结构文件

InnoDB 存储引擎文件

表空间文件

innodb 默认会有一个大小 10M, 名为 ibdata1 的文件,可以通过参数 innodb_data_file_path 控制

重做日志文件

innodb 默认在数据目录会有 ib_logfile0、ib_logfile1 两个文件

B+ 树索引

聚集索引

InnoDB 存储引擎表是索引组织表,即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗 B+ 树,同时叶子结点存放的即为整张表的行记录数据。

辅助索引

对于辅助索引(非聚集索引),叶子节点并不包含行记录的全部数据。叶子结点中的索引行除了包含键值外,还包含了一个书签,该书签用来告诉 InnoDB 存储引擎哪里可以找到索引相对应的数据。相当于要查两遍,去聚集索引中拿到记录的行物理地址。

索引联合

索引联合符合最左匹配原则

索引覆盖

InnoDB 支持索引覆盖,索引覆盖指的是从辅助索引查询不需要再去聚集索引中查询一遍。

分库分表

MyCat

发表评论

评论内容
 

评论列表, 共 0 条评论