MySQL-102

Search…
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
	"xxxxxx"; 
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.CURRENT_TIMESTAMP insert 时插入当前时间
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!
​Overview of Partitioning in MySQL​
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​
​Partitioning Types and Partition Management​
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() 操作中。当 type 是 range 时, 那么 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 根据查询优化器自己选择的。