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 用户配置：
use mysql;
select * from user where user="root";
Access denied for user 没有权限
mysqldump --databases checkRandomDiff dashboard dashboard_debug diff liuli12 rpmp > dump.sql
# 在 dump 的时候提示 access denied for user
解决：为该命令指定用户
mysqldump -uroot -p123456  --databases checkRandomDiff dashboard dashboard_debug diff liuli12 rpmp > dump.sql
DBA 相关
权限控制
官方文档
https://dev.mysql.com/doc/refman/5.7/en/grant.html
权限相关查询
-- 查询可以使用 root 用户访问数据库的 host
use mysql;
SELECT host FROM user WHERE user = 'root';
Grant 授权语法
-- Demo
GRANT 
	SELECT -- 权限
ON 
	spider_db.* -- 数据库.表 这里表示数据库的所有表
TO 
	'wanshuo'@'%' -- '用户名'@'host', % 表示并没有指定任何 host，用户可以从任何 host 上访问
IDENTIFIED BY 
	"xxxxxx"; 
flush privileges;
/*
详解
1. 如果 'wanshuo'@'%' 这个用户配置在 mysql.user 中已经存在，那么 IDENTIFIED BY 就不需要加入，如果加入了，那么配置的密码就会覆盖原始的密码，grant 就是单纯的为 'wanshuo'@'%'赋予权限。
2. 如果 'wanshuo'@'%' 这个用户配置在 mysql.user 中不存在，那么 grant 的作用是创建用户，并赋予权限。IDENTIFIED BY 起到配置密码的作用
​
identified by 参考：https://stackoverflow.com/questions/31111847/identified-by-password-in-mysql
*/
Grant demo
-- 把查询 student 表的权限授权给 u1
grant select on table student to u1; 
​
-- 把对 student 表和 course 表的所有权限赋给 u1,u2
grant all privileges on table student, course to u1, u2; 
​
-- 把对 student 表的查询权限赋给所有用户
grant select on table student to public; 
​
-- 把查询 student 表 和 修改学号的权限赋给 u1
grant select, update(sno) on student to u1;
​
-- 把对 student 表的插入权限赋给 u1，并允许 u1 再将此权限授予其他用户
grant insert on student to u1 with grant option;
Revoke 收回授权
revoke 
	insert 
on
	spider_db.*
from
	user;
创建用户，授予权限，删除用户的 Demo
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
​
DROP USER 'jeffrey'@'localhost';
远程访问
grant all privileges on *.* to 'root'@'%' identified by '密码' with grant option;
flush privileges;  
其他
打开/关闭/重启 Mysql，查看运行状态
sudo /etc/init.d/mysql start # open
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql restart
sudo /etc/init.d/mysql status
# windows 平台
net stop mysql
net start mysql
# 没有重启的命令
mysqladmin 命令
-- 设置用户, mysqladmin
mysqladmin -u xxx password xxx
重置密码
1. 在配置文件中 [mysqld] 下添加 skip-grant-tables，保存退出
2. 重启 mysql，执行以下命令：
   mysql
   mysql > use mysql;
   mysql > update user set password=password('root') where user='root';   
   mysql > flush privileges;
   mysql > exit;
3. 停止 mysql 服务， 在配置文件中 [mysqld] 下删除 skip-grant-tables，保存退出
时区
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
-- 如果输出 08:00:00，则说明是中国时区
Engine
查看 mysql 支持的引擎
show engines;
概述
MySQL 5.7 的存储引擎默认是 InnoDB，值得一提的是，mysql 支持内存数据库。
查看 MySQL 版本
-- 在 bash 中
mysql --help | findstr Distrib
-- 在 MySQL 中
select version();
数据库专有名词
qps
query per second
是指一台服务器每秒能够接受的查询次数。
只读实例与主备实例
详细介绍：https://www.alibabacloud.com/help/zh/doc-detail/26136.htm
应用场景
在对数据库有少量写请求，但有大量读请求的应用场景下，单个实例可能无法承受读取压力，甚至对业务产生影响。为了实现读取能力的弹性扩展，分担数据库压力，您可以创建一个或多个只读实例，利用只读实例满足大量的数据库读取需求，增加应用的吞吐量。
拓扑图
只读实例与主备实例
Mysql 内置函数
注意，所有的功能性的内置函数，都可以直接通过 select 进行调用。例如：
select CurDate();
select Upper("nihao");
时间相关
Mysql 的日期格式，必须是 2019-01-01
time_to_sec, timediff
select 
	cid, (1-sum(TIME_TO_SEC(TIMEDIFF(meddle_end,meddle_start)))/2592000) as sec 
from 
	run_case_meddlecase
where 
	meddle_start > "2018-09-01" and meddle_start < "2018-10-01"
	and TIMEDIFF(meddle_end,meddle_start) <= "168:00:00"
group by 
	cid
order by 
	sec
Date(), Time(), Year()
如果只需要比较一部分日期，例如只针对日期/时间/年份/月份进行查询，则需要用到一些日期的处理函数
函数
解释
Date()
返回一个日期的日期部分
Time()
返回一个日期的时间部分
Year()
返回一个日期的年份
Now()
返回当前日期和时间
CurDate()
返回当前日期
CurDate()
返回当前时间
Demos
select weibo_content from senti_weibo_realtime_weibo where weibo_content like "%[微笑]%" and Date(post_time ) = "2019-04-22";
​
select CurDate();
concat() ,round()
作用
concat(str1, str2)连接字符串，其输入可以为 整型
round(num, 2) 对 num 四舍五入保留两位
Demo
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"; 
to_days()
Given a date date, returns a day number (the number of days since year 0). 常用于对数据库表按照时间进行分区时的时间函数。
Demo
 SELECT TO_DAYS("2017-06-20 09:34:00");
数据备份：dump 与 load
https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html
数据库备份工具
Navicat
mysqldump
# 将远程库 bq 中的 svmstate 表存储到 backup.sql
mysqldump -h 10.67.32.24 -uroot -pIQd7VOksI8KtHeZ8Hy0U -P 9306 bq svmstate > backup.sql
​
# 备份多个数据库，注意，必须要加 --databases 参数，该参数可以指定命令行中所有的 name 都是数据库的名称，如果不加 --databases，则会默认 db1 为数据库的名称，db2，db3 为表名
mysqldump --databases db1 db2 db3 > dump.sql
​
# 加 --databases 和 不加 --databases 的区别
##########################################
# Use of --databases with the mysqldump command line causes the dump file to include CREATE
# DATABASE and USE statements that create the database if it does exist and make it the    # default database for the reloaded data.
# 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.
##########################################
​
# mysqldump 会锁住表，加上  --single-transaction 参数可以在不加锁的情况下进行备份
​
# 常见的两种 备份 和 加载 方式
mysqldump --databases db1 > dump.sql
mysql < dump.sql
​
mysqldump db1 > dump.sql
mysqladmin create db1
​
# dump 所有的数据库
mysqldump --all-databases > dump.sql
​
# 备份指定的数据库，加入 --databases，就会备份备份建表语句
mysqldump --databases db1 db2 db3 > dump.sql
​
# 备份数据库中除某些表之外的部分表
mysqldump -uroot -p123456 diff --ignore-table=diff.getdiffrate_difftraceinfo --ignore-table =diff.test > /home/work/mysql_backup/dump.sql
​
# dump 表中的一部分数据，加入 where 条件
mysqldump -uroot -p123456 diff getdiffrate_difftraceinfo --where="id > 1 ORDER BY ID DESC LIMIT 100" > /home/work/mysql_backup/getdiffrate_difftraceinfo_partial_data.sql
# 注意，where 字句中一定要加入一个条件，例如 id > 1，否则不符合语法要求。
​
# 只 dump 表结构 -d 参数
mysqldump -d 数据库名 -uroot -p > xxx.sql
​
mysql -h 106.54.116.243 -P 3306 -u root -pxxxxxx
​
# 只 dump 数据 -t 参数
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
load
# 将本地的 sql 文件导入远程的数据库中，这里这是备份了数据库中的一张表，所以需要指定数据库
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
​
# 将本地的包含多个库的备份文件导入数据库中，不需要指定数据库
./mysql -h localhost -P 3306  -uroot -p123456 < /home/work/mysql_backup/dump_except_diff.sql
注意
对于 id 自增的数据表，不管其增加新的数据，然后删除，还是删除旧的数据，然后更新，在使用 MySQL dump 命令的时候，都不会导致 id 中断。只要 dump 的 sql 文件中，没有冲突的 id，就能 load 成功。
dump.sql 详解
dump.sql 文件其实是一个纯文本文件，其中包含了数据库表的创建信息，还有要插入的数据。
-- MySQL dump 10.13  Distrib 5.5.30, for Linux (x86_64)
--
-- Host: 10.67.32.24    Database: bq
-- ------------------------------------------------------
-- Server version	5.1.49-log
​
/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;
​
--
-- Table structure for table `svmstate`
--
​
DROP TABLE IF EXISTS `svmstate`; # 会覆盖之前存储的同名的表
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `svmstate` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID标识',
  `module` varchar(255) NOT NULL COMMENT '类型',
  `flag` varchar(255) NOT NULL COMMENT '状态',
  `last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
​
--
-- Dumping data for table `svmstate`
--
​
LOCK TABLES `svmstate` WRITE; # 在写入的时候，会锁住 写入的数据表
/*!40000 ALTER TABLE `svmstate` DISABLE KEYS */;
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');
/*!40000 ALTER TABLE `svmstate` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET [email protected]_TIME_ZONE */;
​
/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;
​
-- Dump completed on 2018-10-15 20:29:56
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
* 查看各个库的大小
select table_schema, sum(data_length+index_length)/1024/1024 as total_mb, \
sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb, \
count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc;
​
* 查看某张表的大小
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"; 
​
# 查看某张表有多少行
select table_rows  from tables where table_schema = "diff" and table_name = "getdiffrate_difftraceinfo";
正则表达式
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
create table test_table(
	id int(11) auto_increment,
	name varchar(8) not null,
	mother varchar(8) not null,
	primary key (id), -- 主键
	key mother (mother), -- 加索引
	foreign key (name) -- 加外键
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; -- id 从 1 开始增长
索引的实现
Mysql InnoDB 引擎采用 B+ 树（多叉平衡树）建立索引，所有数据存储到 B+ 树中。之所以采用 B+ 树，首先如果采用传统的二叉查找树，那么很容易导致树的高度过高，一层就是一次查询，进而导致查询效率不高。那么为了优化二叉查找树，我们考虑采用 AVL(二叉平衡树) 才建立索引，但是问题在于，当数据量很大时，即使是平衡树，树也会达到很深的深度。所以我们采用 B+ 树，B+ 树既是平衡树，又因为一个节点存储多值，所以树的深度会变浅，从而查询次数比较少。
优化的准则：降低查找树的深度，减少磁盘访问次数。
优化的过程：
二叉排序树 ——> AVL 树 ——> B树 ——> B+树
Demo:
create table T(
    id int primary key, -- 主键建立主索引
    k int not null, 
    name varchar(16),
	index (k) -- k 建立普通索引
)engine=InnoDB;
如果我们有五组数据要存储到表中：（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 是多用户多线程的，有时候一个线程任务速度慢，可能会拖慢所有任务。
查看当前线程的命令：
show processlist;
kill 某个线程的命令:
kill id;
delayed 关键字 延迟执行
delayed 常用于延迟执行 insert。有时候 MySQL 事务繁忙，例如某张表正在被读任务占用，我们就可以延迟对这张表的写入。delayed insert 语句实际上进入了一个队列，当读操作结束的时候，delayed 队列中的 insert 语句才会执行。常用于缓解数据库的压力。
Demo:
insert delayed into senti_weibo_conf values(7, "hehe", "1", "2019-09-09 00:00:00", "s");
其他优化技巧
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 值的判断
is null
select crawl_time, sentiment_score  from spider_realtime_weibo where sentiment_score is null;
datetime 与 timestamp
timestamp
timestamp 只占用 4 个字节，可以表示的时间范围有上限和下线，这是一个坑。
两个属性：
1.CURRENT_TIMESTAMP insert 时插入当前时间
2.ON UPDATE CURRENT_TIMESTAMP update 时更新为当前时间
常用组合：
last_modify_time timestamp not null default current_timestamp on update current_timestamp comment '最后更新时间'  -- 创建新记录和修改现有记录的时候都对这个数据列刷新
datetime
datetime 的时间没有上线限制，可以在 timestamp 失效时，替代 timestamp
实际问题
往数据库中插入时间 > '2038-01-19 3:14:7' 的时间时，会报错
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');
[Err] 1292 - invalid time format: '{2038 1 19 3 14 8 0}'
原因
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
-- 查看结果中是否有 Name 为 partition 的 plugin，并且其状态是否为 active，如果是 active，则支持 partition
SHOW PLUGINS;
​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
-- 创建带有分区的表
CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (2000),
        PARTITION p3 VALUES LESS THAN (2005),
        PARTITION p4 VALUES LESS THAN (2010),
        PARTITION p5 VALUES LESS THAN (2015)
);
​
-- 正常插入数据
INSERT INTO tr VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'alarm clock', '1997-11-05'),
(3, 'chair', '2009-03-10'),
(4, 'bookcase', '1989-01-10'),
(5, 'exercise bike', '2014-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'espresso maker', '2011-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '2006-09-16'),
(10, 'lava lamp', '1998-12-25');
​
-- 从某个分区获取数据
SELECT * FROM tr PARTITION (p2);
​
-- 删除某个分区，注意：在删除某个分区后，该分区的数据也会对应的被删除
ALTER TABLE tr DROP PARTITION p2;
​
-- 对于在创建时，没有设置 partition 的 table，alter 初次设置其 partition 的命令
ALTER TABLE tr
    PARTITION BY HASH(id)
    PARTITIONS 8;
-- 同上
ALTER TABLE senti_weibo_realtime_weibo PARTITION BY RANGE( to_days(post_time) ) (
​
	PARTITION p0 VALUES LESS THAN (to_days("2016-01-01")),
	PARTITION p1 VALUES LESS THAN (to_days("2017-01-01")),
	PARTITION p2 VALUES LESS THAN (to_days("2018-01-01")),
	PARTITION p3 VALUES LESS THAN (to_days("2018-03-01")),
	PARTITION p4 VALUES LESS THAN (to_days("2018-06-01")),
	PARTITION p5 VALUES LESS THAN (to_days("2018-08-01")),
	PARTITION p6 VALUES LESS THAN (to_days("2018-10-01")),
	PARTITION p7 VALUES LESS THAN (to_days("2018-12-01")),
	PARTITION p8 VALUES LESS THAN (to_days("2019-01-01")),
	PARTITION p9 VALUES LESS THAN (to_days("2019-03-01")),
	PARTITION p10 VALUES LESS THAN (to_days("2019-05-01")),
	PARTITION p11 VALUES LESS THAN (to_days("2019-07-01")),
	PARTITION p12 VALUES LESS THAN (to_days("2019-09-01")),
	PARTITION p13 VALUES LESS THAN (to_days("2019-11-01")),
	PARTITION p14 VALUES LESS THAN (to_days("2020-01-01")),
	PARTITION p15 VALUES LESS THAN (to_days("2020-03-01")),
	PARTITION p16 VALUES LESS THAN (to_days("2020-05-01")),
	PARTITION p17 VALUES LESS THAN (to_days("2020-07-01")),
	PARTITION p18 VALUES LESS THAN (to_days("2020-09-01")),
	PARTITION p19 VALUES LESS THAN MAXVALUE
);
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 的执行。
explain select * from users where name = "wanshuo";
image-20211124090417979
explain select * from users where id = "08e83a509fbe4c3e9010c7c5da984db0";
image-20211124093408525
explain select * from blog_browse_history where chapter_id > 2700;
image-20211124094340040
explain select * from blog_browse_history where chapter_id > 270;
-- 并没有走索引，查询优化器根据索引数据的分布情况，判断是走索引查询，还是全表扫描，这里是走全表扫描了，所以并不是配置了索引，就一定走索引
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 根据查询优化器自己选择的。
MySQL 保留字
mysql 使用反引号来括住表名和字段名，以区分保留字和普通字符。注意，只有 MySQL 才有反引号这个怪东西。
CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `nickname` varchar(20) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `in_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
不能将字段名命名为如下列举的名字：
order