MySQL-102
[TOC]

进阶 MySQL 的目的

  • DBA
  • Mysql 的执行效率非常高,而且提供了非常多丰富的功能,例如各种字符串函数,触发器,字段的约束等功能。在实际应用中,我们可以将统一的计算迁移到 MySQL 上,这样就避免了在切换不同的框架时写重复的代码。这篇文章的思想 Simplify: move code into database functions 和我的思想不谋而合。这也是为什么要深入学习 Mysql 的一个原因。
  • 个人能力的提升

Fixed problems

root 用户无法远程访问
根本原因在于,没有为 "root"@"%" 配置密码
root 用户在 MySQL 的 user 表中,有多种,这是 mysql.user 表中的数据:
Host
User
localhost
root
%
root
如果我们想要使用 root 用户远程访问数据库,必须先去数据库里看看到底有几个 root 用户配置:
1
use mysql;
2
select * from user where user="root";
Copied!
Access denied for user 没有权限
1
mysqldump --databases checkRandomDiff dashboard dashboard_debug diff liuli12 rpmp > dump.sql
2
# 在 dump 的时候提示 access denied for user
3
解决:为该命令指定用户
4
mysqldump -uroot -p123456 --databases checkRandomDiff dashboard dashboard_debug diff liuli12 rpmp > dump.sql
Copied!

DBA 相关

权限控制

官方文档
https://dev.mysql.com/doc/refman/5.7/en/grant.html
权限相关查询
1
-- 查询可以使用 root 用户访问数据库的 host
2
use mysql;
3
SELECT host FROM user WHERE user = 'root';
Copied!

Grant 授权语法

1
-- Demo
2
GRANT
3
SELECT -- 权限
4
ON
5
spider_db.* -- 数据库.表 这里表示数据库的所有表
6
TO
7
'wanshuo'@'%' -- '用户名'@'host', % 表示并没有指定任何 host,用户可以从任何 host 上访问
8
IDENTIFIED BY
9
"ws6226067";
10
flush privileges;
11
/*
12
详解
13
1. 如果 'wanshuo'@'%' 这个用户配置在 mysql.user 中已经存在,那么 IDENTIFIED BY 就不需要加入,如果加入了,那么配置的密码就会覆盖原始的密码,grant 就是单纯的为 'wanshuo'@'%'赋予权限。
14
2. 如果 'wanshuo'@'%' 这个用户配置在 mysql.user 中不存在,那么 grant 的作用是创建用户,并赋予权限。IDENTIFIED BY 起到配置密码的作用
15
16
identified by 参考:https://stackoverflow.com/questions/31111847/identified-by-password-in-mysql
17
*/
Copied!

Grant demo

1
-- 把查询 student 表的权限授权给 u1
2
grant select on table student to u1;
3
4
-- 把对 student 表和 course 表的所有权限赋给 u1,u2
5
grant all privileges on table student, course to u1, u2;
6
7
-- 把对 student 表的查询权限赋给所有用户
8
grant select on table student to public;
9
10
-- 把查询 student 表 和 修改学号的权限赋给 u1
11
grant select, update(sno) on student to u1;
12
13
-- 把对 student 表的插入权限赋给 u1,并允许 u1 再将此权限授予其他用户
14
grant insert on student to u1 with grant option;
Copied!

Revoke 收回授权

1
revoke
2
insert
3
on
4
spider_db.*
5
from
6
user;
Copied!

创建用户,授予权限,删除用户的 Demo

1
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
2
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
3
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
4
ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
5
6
DROP USER 'jeffrey'@'localhost';
Copied!

远程访问

1
grant all privileges on *.* to 'root'@'%' identified by '密码' with grant option;
2
flush privileges;
Copied!

其他

打开/关闭/重启 Mysql,查看运行状态

1
sudo /etc/init.d/mysql start # open
2
sudo /etc/init.d/mysql stop
3
sudo /etc/init.d/mysql restart
4
sudo /etc/init.d/mysql status
Copied!
1
# windows 平台
2
net stop mysql
3
net start mysql
4
# 没有重启的命令
Copied!

mysqladmin 命令

1
-- 设置用户, mysqladmin
2
mysqladmin -u xxx password xxx
Copied!

重置密码

1
1. 在配置文件中 [mysqld] 下添加 skip-grant-tables,保存退出
2
2. 重启 mysql,执行以下命令:
3
mysql
4
mysql > use mysql;
5
mysql > update user set password=password('root') where user='root';
6
mysql > flush privileges;
7
mysql > exit;
8
3. 停止 mysql 服务, 在配置文件中 [mysqld] 下删除 skip-grant-tables,保存退出
Copied!

时区

1
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
2
-- 如果输出 08:00:00,则说明是中国时区
Copied!

Engine

查看 mysql 支持的引擎
1
show engines;
Copied!
概述
MySQL 5.7 的存储引擎默认是 InnoDB,值得一提的是,mysql 支持内存数据库。

查看 MySQL 版本

1
-- 在 bash 中
2
mysql --help | findstr Distrib
3
-- 在 MySQL 中
4
select version();
Copied!

数据库专有名词

qps

query per second
是指一台服务器每秒能够接受的查询次数。

只读实例与主备实例

详细介绍:https://www.alibabacloud.com/help/zh/doc-detail/26136.htm
应用场景
在对数据库有少量写请求,但有大量读请求的应用场景下,单个实例可能无法承受读取压力,甚至对业务产生影响。为了实现读取能力的弹性扩展,分担数据库压力,您可以创建一个或多个只读实例,利用只读实例满足大量的数据库读取需求,增加应用的吞吐量。
拓扑图
只读实例与主备实例

Mysql 内置函数

注意,所有的功能性的内置函数,都可以直接通过 select 进行调用。例如:
1
select CurDate();
2
select Upper("nihao");
Copied!

时间相关

Mysql 的日期格式,必须是 2019-01-01

time_to_sec, timediff

1
select
2
cid, (1-sum(TIME_TO_SEC(TIMEDIFF(meddle_end,meddle_start)))/2592000) as sec
3
from
4
run_case_meddlecase
5
where
6
meddle_start > "2018-09-01" and meddle_start < "2018-10-01"
7
and TIMEDIFF(meddle_end,meddle_start) <= "168:00:00"
8
group by
9
cid
10
order by
11
sec
Copied!

Date(), Time(), Year()

如果只需要比较一部分日期,例如只针对日期/时间/年份/月份进行查询,则需要用到一些日期的处理函数
函数
解释
Date()
返回一个日期的日期部分
Time()
返回一个日期的时间部分
Year()
返回一个日期的年份
Now()
返回当前日期和时间
CurDate()
返回当前日期
CurDate()
返回当前时间
Demos
1
select weibo_content from senti_weibo_realtime_weibo where weibo_content like "%[微笑]%" and Date(post_time ) = "2019-04-22";
2
3
select CurDate();
Copied!

concat() ,round()

作用
concat(str1, str2)连接字符串,其输入可以为 整型
round(num, 2) 对 num 四舍五入保留两位
Demo
1
select table_name, concat(round((data_length + index_length)/1024/1024, 2), 'M') as size_M from information_schema.tables where table_schema = "se_monitor" and table_name = "run_case_deploycoveragedetail";
Copied!

to_days()

Given a date date, returns a day number (the number of days since year 0). 常用于对数据库表按照时间进行分区时的时间函数。
Demo
1
SELECT TO_DAYS("2017-06-20 09:34:00");
Copied!

数据备份:dump 与 load

https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html
数据库备份工具
Navicat

mysqldump

1
# 将远程库 bq 中的 svmstate 表存储到 backup.sql
2
mysqldump -h 10.67.32.24 -uroot -pIQd7VOksI8KtHeZ8Hy0U -P 9306 bq svmstate > backup.sql
3
4
# 备份多个数据库,注意,必须要加 --databases 参数,该参数可以指定命令行中所有的 name 都是数据库的名称,如果不加 --databases,则会默认 db1 为数据库的名称,db2,db3 为表名
5
mysqldump --databases db1 db2 db3 > dump.sql
6
7
# 加 --databases 和 不加 --databases 的区别
8
##########################################
9
# Use of --databases with the mysqldump command line causes the dump file to include CREATE
10
# DATABASE and USE statements that create the database if it does exist and make it the # default database for the reloaded data.
11
# Alternatively, you can omit --databases from the mysqldump command. Then you will need to # create the database on Server 2 (if necessary) and specify it as the default database # when you reload the dump file.
12
##########################################
13
14
# mysqldump 会锁住表,加上 --single-transaction 参数可以在不加锁的情况下进行备份
15
16
# 常见的两种 备份 和 加载 方式
17
mysqldump --databases db1 > dump.sql
18
mysql < dump.sql
19
20
mysqldump db1 > dump.sql
21
mysqladmin create db1
22
23
# dump 所有的数据库
24
mysqldump --all-databases > dump.sql
25
26
# 备份指定的数据库,加入 --databases,就会备份备份建表语句
27
mysqldump --databases db1 db2 db3 > dump.sql
28
29
# 备份数据库中除某些表之外的部分表
30
mysqldump -uroot -p123456 diff --ignore-table=diff.getdiffrate_difftraceinfo --ignore-table =diff.test > /home/work/mysql_backup/dump.sql
31
32
# dump 表中的一部分数据,加入 where 条件
33
mysqldump -uroot -p123456 diff getdiffrate_difftraceinfo --where="id > 1 ORDER BY ID DESC LIMIT 100" > /home/work/mysql_backup/getdiffrate_difftraceinfo_partial_data.sql
34
# 注意,where 字句中一定要加入一个条件,例如 id > 1,否则不符合语法要求。
35
36
# 只 dump 表结构 -d 参数
37
mysqldump -d 数据库名 -uroot -p > xxx.sql
38
39
# 只 dump 数据 -t 参数
40
mysqldump -h 10.91.162.162 -umonitor -pmonitor123 -P 3306 se_monitor run_case_detail_20180201 -t --single-transaction --where="id > 1000 limit 10" > /home/work/mysql_dump/sql/run_case_detail_20180201.sql
Copied!

load

1
# 将本地的 sql 文件导入远程的数据库中,这里这是备份了数据库中的一张表,所以需要指定数据库
2
mysql -h 10.64.26.153 -P 8091 -u dasou_wangye --password=6fb943b0-d74d-4d26-8aa6-217848bd04ea se_monitor < /home/work/mysql_dump/sql/run_case_20180801.sql
3
4
# 将本地的包含多个库的备份文件导入数据库中,不需要指定数据库
5
./mysql -h localhost -P 3306 -uroot -p123456 < /home/work/mysql_backup/dump_except_diff.sql
Copied!
注意
对于 id 自增的数据表,不管其增加新的数据,然后删除,还是删除旧的数据,然后更新,在使用 MySQL dump 命令的时候,都不会导致 id 中断。只要 dump 的 sql 文件中,没有冲突的 id,就能 load 成功。

dump.sql 详解

dump.sql 文件其实是一个纯文本文件,其中包含了数据库表的创建信息,还有要插入的数据。
1
-- MySQL dump 10.13 Distrib 5.5.30, for Linux (x86_64)
2
--
3
-- Host: 10.67.32.24 Database: bq
4
-- ------------------------------------------------------
5
-- Server version 5.1.49-log
6
7
/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
8
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
9
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
10
/*!40101 SET NAMES utf8 */;
11
/*!40103 SET @[email protected]@TIME_ZONE */;
12
/*!40103 SET TIME_ZONE='+00:00' */;
13
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;
17
18
--
19
-- Table structure for table `svmstate`
20
--
21
22
DROP TABLE IF EXISTS `svmstate`; # 会覆盖之前存储的同名的表
23
/*!40101 SET @saved_cs_client = @@character_set_client */;
24
/*!40101 SET character_set_client = utf8 */;
25
CREATE TABLE `svmstate` (
26
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID标识',
27
`module` varchar(255) NOT NULL COMMENT '类型',
28
`flag` varchar(255) NOT NULL COMMENT '状态',
29
`last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后更新时间',
30
PRIMARY KEY (`id`)
31
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
32
/*!40101 SET character_set_client = @saved_cs_client */;
33
34
--
35
-- Dumping data for table `svmstate`
36
--
37
38
LOCK TABLES `svmstate` WRITE; # 在写入的时候,会锁住 写入的数据表
39
/*!40000 ALTER TABLE `svmstate` DISABLE KEYS */;
40
INSERT INTO `svmstate` VALUES (1,'feedback','1','2018-02-02 05:32:59'),(3,'onlineproblem','1','2018-03-22 09:06:45'),(4,'bugs','1','2018-03-22 09:32:37');
41
/*!40000 ALTER TABLE `svmstate` ENABLE KEYS */;
42
UNLOCK TABLES;
43
/*!40103 SET [email protected]_TIME_ZONE */;
44
45
/*!40101 SET [email protected]_SQL_MODE */;
46
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
47
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
48
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
49
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
50
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
51
/*!40111 SET [email protected]_SQL_NOTES */;
52
53
-- Dump completed on 2018-10-15 20:29:56
Copied!

innodb_force_recovery 进入安全模式备份数据

Forcing InnoDB Recovery 模式
在 my.cnf 中加入 innodb_force_recovery = 1,进入强制恢复模式。
进行该操作只是权宜之计,用来启动 InnoDB 引擎并 dump 数据。
开启 Forcing Recovery 模式后,InnoDB 会禁止对数据库的 增删改 操作,当 innodb_force_recovery > 3 后,数据库会进入只读模式,并且到了 4 就会有永久损坏数据的危险。
1 <= innodb_force_recovery <= 6,数字越高,风险越大。innodb_force_recovery 的默认值为 0。
已经实践确认,进入安全模式后,确实不能进行增删改操作,但是能在 innodb_force_recovery <= 3 时进行新建表,和删除表的操作。如果进行 insert 操作,会报: ERROR 1030 (HY000): Got error -1 from storage engine 的错误。

其他知识点

Mysql 自带的四个数据库

mysql 数据库

存储 mysql 的配置信息,例如 mysql 的用户,访问权限等。

performance schema 和 sys 数据库

存储服务器性能参数,暂时不需要了解。

information schema 数据库

abstract
information_schema 数据库中存储着 MySQL 下的所有数据库的配置信息。包括用户、权限、数据库大小等信息。
information_schema 数据库中每一张表,都存储着一种配置,下面对于常用到的表进行解释。
information schema 数据库中的表实际上都是只读视图。
TABLES 表
字段
含义
TABLE_SCHEMA
database name
TABLE_NAME
table name
DATA_LENGTH
数据量大小,单位 byte
INDEX_LENGTH
索引大小,单位 byte
TABLE_ROWS
表的行数
Demo
1
* 查看各个库的大小
2
select table_schema, sum(data_length+index_length)/1024/1024 as total_mb, \
3
sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb, \
4
count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc;
5
6
* 查看某张表的大小
7
select table_name, (data_length + index_length)/1024/1024 as size_M from information_schema.tables where table_schema = "se_monitor" and table_name = "run_case_deploycoveragedetail";
8
9
# 查看某张表有多少行
10
select table_rows from tables where table_schema = "diff" and table_name = "getdiffrate_difftraceinfo";
Copied!

正则表达式

primary key / unique key

相同点
  • 两者都用来标记一条独一无二的数据
不同点
  • primary key 是 not null, 而 unique key 可以是 null
  • primary key 只能有一个,而 unique 可以定义多个

索引

为什么要建立索引

数据库为了提高查询效率,通常会为某些字段加入索引。当我们查询某个字段的时候,如果这个字段没有添加索引,那么查询速度就会非常慢,因为数据库要从第一行开始查找。
数据库会默认为 Primary Key 建立索引,但是这远远不够,如果我们对某张表的某个字段查询比较频繁,我们最好给这个字段加入索引,进而提高查询效率。

索引的缺点

  • 索引的缺点,就是 B+ 树的缺点,索引方便了查询,但是不利于数据的增删改
  • 简历索引和维护索引需要耗费很多计算资源和内存空间

索引的类型

索引名
作用
index, key
普通索引,加快查询速度( key 的功能多于 index,默认用key)
unique
唯一索引,该值在字段下唯一,可以为 null
primary key
主索引,主键
foreign key
外索引,外键
Demo
1
create table test_table(
2
id int(11) auto_increment,
3
name varchar(8) not null,
4
mother varchar(8) not null,
5
primary key (id), -- 主键
6
key mother (mother), -- 加索引
7
foreign key (name) -- 加外键
8
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; -- id 从 1 开始增长
Copied!

索引的实现

Mysql InnoDB 引擎采用 B+ 树(多叉平衡树)建立索引,所有数据存储到 B+ 树中。之所以采用 B+ 树,首先如果采用传统的二叉查找树,那么很容易导致树的高度过高,一层就是一次查询,进而导致查询效率不高。那么为了优化二叉查找树,我们考虑采用 AVL(二叉平衡树) 才建立索引,但是问题在于,当数据量很大时,即使是平衡树,树也会达到很深的深度。所以我们采用 B+ 树,B+ 树既是平衡树,又因为一个节点存储多值,所以树的深度会变浅,从而查询次数比较少。
优化的准则:降低查找树的深度,减少磁盘访问次数。
优化的过程
1
二叉排序树 ——> AVL 树 ——> B树 ——> B+树
Copied!
Demo:
1
create table T(
2
id int primary key, -- 主键建立主索引
3
k int not null,
4
name varchar(16),
5
index (k) -- k 建立普通索引
6
)engine=InnoDB;
Copied!
如果我们有五组数据要存储到表中:(id, k), (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6) ,那么这两棵树存储结构为:
主键索引与非主键索引
主键索引叶子节点存储的是整行的数据,而非主键索引叶子节点存储的是主键,所以如果我们想要查找主键,只需要搜索一次 B+ 树,而如果我们想要对其他非主键索引的字段进行查找,则需要查找两次 B+ 树,第一次是查找字段所在 B+ 树,找到主键ID,然后根据主键 ID 去主键索引树中查找。
由此我们也可以看到,普通索引的叶子节点,存储的是主键ID,那么主键的存储空间越小,那么普通索引的叶子节点就越小,普通索引占用的空间也越小。这就是为什么,大多数的数据库表,主键设置为主键自增长,而且用整型来存储,这样即方便比较,存储的体积也小。举个例子,如果我们设置了某一个字段为主键,例如身份证,那么其他非主键索引树的叶子节点,就存储的是身份证号,身份证号比起自增长 ID 来说,占用了更多的空间。而且每一个非主键索引树,都会附带上身份证号,导致索引树的体积变大。

索引失效的情况

一个宗旨:对带所有的字段进行查找,尽量不要设计非索引字段的查找。
  • where 子句
    • 进行 null 值的判断: is null
    • != < > 的使用
    • or 如果包含了一个非索引的字段,则也会索引失效,进行全表扫描
    • in 的使用
    • 函数操作 substring(name) = "wansho"
    • like 模糊查询

Mysql 优化

查看 Mysql 线程

Mysql 是多用户多线程的,有时候一个线程任务速度慢,可能会拖慢所有任务。
查看当前线程的命令:
1
show processlist;
Copied!
kill 某个线程的命令:
1
kill id;
Copied!

delayed 关键字 延迟执行

delayed 常用于延迟执行 insert。有时候 MySQL 事务繁忙,例如某张表正在被读任务占用,我们就可以延迟对这张表的写入。delayed insert 语句实际上进入了一个队列,当读操作结束的时候,delayed 队列中的 insert 语句才会执行。常用于缓解数据库的压力。
Demo:
1
insert delayed into senti_weibo_conf values(7, "hehe", "1", "2019-09-09 00:00:00", "s");
Copied!

其他优化技巧

  • Mysql Script (存储过程)的执行速度,快于一条一条单独执行的速度。
  • 不要用 select *,用到什么字段,select 什么字段
  • 使用 delayed 关键字延迟命令的执行,防止数据库压力过大
  • 用 union 替代 or
  • 避免在线上环境使用 like

数据类型

浮点型

alter table spider_realtime_weibo add sentiment_score float(5,2) ;
给spider_realtime_weibo 加入一个浮点型字段,其可以表示两位小数,包括小数点最长可达 5 位。
上述描述同样适用于 double 类型
注意:float 型必须指定小数点位数,否则存入数据库时还是整数

对于 NULL 值的判断

1
is null
2
select crawl_time, sentiment_score from spider_realtime_weibo where sentiment_score is null;
Copied!

datetime 与 timestamp

timestamp
timestamp 只占用 4 个字节,可以表示的时间范围有上限和下线,这是一个坑。
两个属性:
  1. 1.
    CURRENT_TIMESTAMP insert 时插入当前时间
  2. 2.
    ON UPDATE CURRENT_TIMESTAMP update 时更新为当前时间
常用组合:
1
last_modify_time timestamp not null default current_timestamp on update current_timestamp comment '最后更新时间' -- 创建新记录和修改现有记录的时候都对这个数据列刷新
Copied!
datetime
datetime 的时间没有上线限制,可以在 timestamp 失效时,替代 timestamp
实际问题
往数据库中插入时间 > '2038-01-19 3:14:7' 的时间时,会报错
1
INSERT INTO `run_case_deploycoveragedetail` VALUES ('3380211', 'search', '', 'dict', 'online', '', '', '', '[email protected]:[email protected]_log_dict_31', 'misdata:[email protected]_log_dict_31', 'beehive_jobplatform', '', '1', 'lichenggang,chaijunjun', '', '', 'http://10.46.63.64:8888/jobplatform/data?task_id=1539834449_1', 'CompactionKvDictZdictBuild', '1539834449_1', 'Y', 'day', '2018-10-18 11:47:29', '2038-01-19 3:14:8', '2018-10-18 00:00:00', '2018-10-19 00:00:00', '2018-10-19 08:01:35');
2
[Err] 1292 - invalid time format: '{2038 1 19 3 14 8 0}'
Copied!
原因
mysql 的 timestamp 的时间范围为 1970-01-01 00:00:00 到 2038-01-19 3:14:07,timestamp 占 4 个字节。
具体解释:http://blog.itpub.net/7728585/viewspace-2077609/

Mysql Script 存储过程

实现类似 Linux Script 的功能,包含了 if else while 变量等特性,入门参考 MySQL 必知必会。

Mysql 触发器

触发器是应对 增删改 的操作触发执行的 mysql 语句或 MySQL script。

Mysql Partition 拆表

Partition 的目的

将大表拆成小表,降低增删改查的成本。

查看是否支持 Partition

1
-- 查看结果中是否有 Name 为 partition 的 plugin,并且其状态是否为 active,如果是 active,则支持 partition
2
SHOW PLUGINS;
Copied!
Partition 可以将我们的一张表切分成多块,分不到各个地方。事实上,被切分出来的不同的表块,是作为不同的表存储在其他位置。至于一条数据要被切分到哪一个小表,是根据 partition function 进行区分的。
水平切分
This is known as horizontal partitioning—that is, different rows of a table may be assigned to different physical partitions. MySQL 5.7 does not support vertical partitioning, in which different columns of a table are assigned to different physical partitions. There are no plans at this time to introduce vertical partitioning into MySQL.
目前 Mysql5.7 只支持水平切分,水平切分的最小单位是 data-row。
注意
  • 切分出来的没一张表,其 database engine 都应该是一样的
  • partition 描述应该放到建表语句的最后
  • 被切分的键,应该隶属于 primary key 或者 unique key,参考:Partitioning Keys, Primary Keys, and Unique Keys

range partition

1
-- 创建带有分区的表
2
CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) (
3
PARTITION p0 VALUES LESS THAN (1990),
4
PARTITION p1 VALUES LESS THAN (1995),
5
PARTITION p2 VALUES LESS THAN (2000),
6
PARTITION p3 VALUES LESS THAN (2005),
7
PARTITION p4 VALUES LESS THAN (2010),
8
PARTITION p5 VALUES LESS THAN (2015)
9
);
10
11
-- 正常插入数据
12
INSERT INTO tr VALUES
13
(1, 'desk organiser', '2003-10-15'),
14
(2, 'alarm clock', '1997-11-05'),
15
(3, 'chair', '2009-03-10'),
16
(4, 'bookcase', '1989-01-10'),
17
(5, 'exercise bike', '2014-05-09'),
18
(6, 'sofa', '1987-06-05'),
19
(7, 'espresso maker', '2011-11-22'),
20
(8, 'aquarium', '1992-08-04'),
21
(9, 'study desk', '2006-09-16'),
22
(10, 'lava lamp', '1998-12-25');
23
24
-- 从某个分区获取数据
25
SELECT * FROM tr PARTITION (p2);
26
27
-- 删除某个分区,注意:在删除某个分区后,该分区的数据也会对应的被删除
28
ALTER TABLE tr DROP PARTITION p2;
29
30
-- 对于在创建时,没有设置 partition 的 table,alter 初次设置其 partition 的命令
31
ALTER TABLE tr
32
PARTITION BY HASH(id)
33
PARTITIONS 8;
34
-- 同上
35
ALTER TABLE senti_weibo_realtime_weibo PARTITION BY RANGE( to_days(post_time) ) (
36
37
PARTITION p0 VALUES LESS THAN (to_days("2016-01-01")),
38
PARTITION p1 VALUES LESS THAN (to_days("2017-01-01")),
39
PARTITION p2 VALUES LESS THAN (to_days("2018-01-01")),
40
PARTITION p3 VALUES LESS THAN (to_days("2018-03-01")),
41
PARTITION p4 VALUES LESS THAN (to_days("2018-06-01")),
42
PARTITION p5 VALUES LESS THAN (to_days("2018-08-01")),
43
PARTITION p6 VALUES LESS THAN (to_days("2018-10-01")),
44
PARTITION p7 VALUES LESS THAN (to_days("2018-12-01")),
45
PARTITION p8 VALUES LESS THAN (to_days("2019-01-01")),
46
PARTITION p9 VALUES LESS THAN (to_days("2019-03-01")),
47
PARTITION p10 VALUES LESS THAN (to_days("2019-05-01")),
48
PARTITION p11 VALUES LESS THAN (to_days("2019-07-01")),
49
PARTITION p12 VALUES LESS THAN (to_days("2019-09-01")),
50
PARTITION p13 VALUES LESS THAN (to_days("2019-11-01")),
51
PARTITION p14 VALUES LESS THAN (to_days("2020-01-01")),
52
PARTITION p15 VALUES LESS THAN (to_days("2020-03-01")),
53
PARTITION p16 VALUES LESS THAN (to_days("2020-05-01")),
54
PARTITION p17 VALUES LESS THAN (to_days("2020-07-01")),
55
PARTITION p18 VALUES LESS THAN (to_days("2020-09-01")),
56
PARTITION p19 VALUES LESS THAN MAXVALUE
57
);
Copied!

Partition Pruning/Selection

Partition Pruning

Partition Pruning Mysql 语句修剪:对包含分区的表的 sql 查询语句进行修剪优化。Partition Pruning 是 Mysql 自带的一个 Optimizer,其可以对 partitioned table 进行 sql 语句的自动优化,提高 sql 效率
Mysql 中有一个 optimizer,可以对分区表的 sql 查询进行优化,其优化的 case 主要有两种:
  • partition_column = constant
  • partition_column IN (constant1, constant2, ..., constantN)
对于 case1, optimizer 会直接根据所给的值,判断该条数据在哪一个分区,然后只扫描这一个分区,除了 = 外,optimizer 还可以对 <, >, <=, >=, and <>between 进行优化;
对于 case2, optimizer 会根据 list 中所给值匹配到一个 partition_list,然后扫描。
Partition Pruning 可以对 select update delete 进行优化,不能对 insert 进行优化。
Partition Pruning 还可以对在 partition table 时采用 YEAR()TO_DAYS()TO_SECONDS()作为划分函数的 table 进行优化

Partition Selection

Partition selection 是明确指定分区的 select 语法,其作用类似于 Partition pruning,但区别在于其明确指定了从哪一个分区进行 select。

MySQL 执行计划 explain

explain 命令用来剖析 sql 的执行。
1
explain select * from users where name = "wanshuo";
Copied!
image-20211124090417979
1
explain select * from users where id = "08e83a509fbe4c3e9010c7c5da984db0";
Copied!
image-20211124093408525
1
explain select * from blog_browse_history where chapter_id > 2700;
Copied!
image-20211124094340040
1
explain select * from blog_browse_history where chapter_id > 270;
2
-- 并没有走索引,查询优化器根据索引数据的分布情况,判断是走索引查询,还是全表扫描,这里是走全表扫描了,所以并不是配置了索引,就一定走索引
Copied!
image-20211124095113735
  • id: 自增序号
  • select_type: 查询类型,simple 代表直接对表的简单查询
  • table: 查询哪张表
  • partitions: 坐落分区,数据分区存储的时候使用
  • type: 索引检索类型
    • const 表示针对主键或唯一索引的等值查询扫描,最多只返回一行数据
    • system 表示表中只有一条数据,这个类型是特殊的 const 类型
    • all 表示全表扫描,性能最差的查询,我们的查询不应该出现全表扫描
    • index 表示全索引扫描,和 all 类似,不过 all 是全表扫描,而 index 是扫描所有的索引,包括主键索引和普通索引。index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。当是这种情况时, Extra 字段 会显示 Using index
    • range 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。当 typerange 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个
    • ref, eq_ref 通常表示多表 join 查询
  • possible_keys: 与当前查询相关备选的索引,primary 代表主键
  • key: 代表当前实际使用的索引
  • key_len: 代表单个索引值的长度
  • ref: 显示使用哪个列或者常数与 key 一起从表中选择行
  • rows: 本次查询扫描的行数,注意,这个值可能与最终结果不一致,扫描行数越少越好
  • filter: 过滤器
  • extra: 扩展条件的详细信息。
    • using index condition 表示通过索引筛选满足的 chapter_id
    • using MRR 表示默认是按照主键索引升序排序
    • using where 表示直接扫描数据行进行筛选
    • using filesort 表示使用文件排序,非常低效

MySQL 执行计划 explain 多表

多表查询类似于嵌套 for 循环,最外层的 for 循环尤为关键,最外层的 for 循环表也被叫做驱动表。
驱动表选哪一个,是MySQL 根据查询优化器自己选择的。
Last modified 4d ago