数据库介绍
现如今，我们可以看到很多的数据库软件 ，比如mysql，oracle，pgsql等很多，这里呢，我们就不一一列举出来。
但实际上他们本身更多的是一个数据库管理系统，简称DBMS（database manager system），而我们说的数据库更多的应该是database（DB）
从电脑诞生开始，数据的存放就是一个问题。早期的数据比较少，因此都是基于文件系统进行存储，我们将数据存储于文件中，读取时将整个文件加载进内存。
但是呢，之后伴随着业务的增长，数据量发生了很大程度的增长，单个文件存储数据无论是对于我们的查询还是修改都变得非常困难，很有可能我们需要遍历整个文件的所有内容才能找到需要的内容。
对于上面这种情况，我们就需要一种新的数据存储方式，数据库也就应运而生。由于数据库本身设计出来就是为了存储小而多的数据，因此对于一些大的文件并不适合存储在数据库中。
当我们的数据存储在数据库之后呢，为了使用数据库里面的数据，我们就必须要一个专门能够管理数据库的软件，通常而言这个软件就是DBMS，也就是数据库管理系统，像我们平时所说的mysql，oracle，pgsql都是数据库管理系统。
现在的数据库根据我们的需要总体上可以分成两大类，一类是关系型数据库，另一类是非关系型数据库。
非关系型数据库是为了补足关系型数据库的缺点而诞生的，因为通常而言，磁盘的读取速度是远远低于内存的，那么从磁盘加载数据本身就会浪费大量的时间，那么使用内存存储数据之后，数据的加载速度就会变得非常之快。
大多数的非关系型数据库都是将数据存储在内存的，这样就可以快速的加载数据，通常我们用非关系型数据库进行缓存，另外就是存储一些需要快速加载的，但又不是非常重要的数据。
真正重要的数据都是存放在关系型数据库的。
我们通过这个网站https://hellogithub.com/report/db-engines/ 进行查看一些数据库的使用量。
mysql历史
- 1979年：TcX公司 Monty Widenius使用BASIC设计了一个报表工具Unireg
- 1985 年，瑞典的几位志同道合小伙子（以David Axmark 为首） 成立了一家公司，这就是MySQL AB 的前身。
- 1996年：发布MySQL1.0，Solaris版本，Linux版本
- 1999年：MySQL AB公司，瑞典
- 2003年：MySQL 5.0版本，提供视图、存储过程等功能
- 2008年：Sun 10亿美元收购MySQL
- 2009年：Oracle 75亿美元收购Sun
- 2009年：Monty成立MariaDB
Mysql.5.5.18. Oracle 对MySQL版本重新进行了划分，分成了社区版和企业版
MySQL 的三大主要分支
- mysql
- mariadb
- percona Server
官方网址
官方文档
- https://dev.mysql.com/doc/
- https://mariadb.com/kb/en/
- https://www.percona.com/software/mysql-database/percona-server
mysql安装
数据库的安装主要有这么两种方式，我们将会在centos7和centos8主机上面分别进行这两种安装，安装的版本以mysql5.7和mysql8.0
- yum安装
- 二进制安装
yum安装
yum安装都需要配置官方的yum仓库然后使用yum进行安装。
前往官方下载yum仓库
https://dev.mysql.com/downloads/repo/yum/
根据自己使用的系统版本选择合适的仓库包下载，下载之后将其上传到系统，使用yum将起进行安装。
当然也可以将下载的链接复制后执行下面的命令
yum install -y 复制的地址例如下面这个
Centos7：
yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm这个链接不一定可用，但至少现在是可以使用的。
mysql5.7安装
Centos7
下载yum的组件
yum install -y yum-utils启动mysql5.7的仓库
yum-config-manager --enable mysql57-community
yum-config-manager --disable mysql80-community清除一下yum缓存
yum clean all首先可以查看一下可以安装的具体版本
yum search mysql-community-server --showduplicates | grep 5.7然后选择一个版本号进行安装，我这里安装的是5.7.35
yum install -y mysql-community-server-5.7.35centos8
centos8的仓库的中没有mysql5.7，因此必须使用7的仓库才能安装5.7版本的mysql
在使用mysql库之前，我们需要关闭appstream中的mysql
dnf module reset mysql
dnf module disable mysql安装7的mysql仓库
yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm启动mysql5.7的仓库
dnf config-manager --enable mysql57-community安装mysql前，查看可以安装的具体版本
dnf search mysql-community-server --showduplicates | grep 5.7选择一个版本进行安装，我这里安装的是5.7.35
yum install -y mysql-community-server-5.7.35mysql8.0安装
centos7
查看一下可以安装的具体版本
yum search mysql-community-server --showduplicates | grep 8.0`然后选择一个版本号进行安装，我这里安装的是8.0.26
yum install -y mysql-community-server-8.0.26centos8
cnetos8仓库中默认就有mysql8.0，不需要前面的仓库配置，直接执行以下命令
yum install -y mysql-server二进制安装
不同平台的mysql二进制安装区别不是特别大，因此我们这里只演示centos7的二进制安装
这是mysql的官方下载链接：https://downloads.mysql.com/archives/community/
这是mysql5.7二进制安装的官方文档：https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html
这是mysql8.0二进制安装的官方文档：https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html
mysql5.7
创建普通用户，准备相对应的目录
useradd -r mysql
mkdir -p /data/mysql_5.7
chown -R mysql.mysql /data/mysql_5.7准备相应的二进制包，解压至相应的目录，我这里解压到/data下
tar -xvf mysql-5.7.34-el7-x86_64.tar -C /data/
cd /data/
tar -xvf mysql-5.7.34-el7-x86_64.tar.gz进入对应的目录中，构建数据库系统
cd mysql-5.7.34-el7-x86_64/bin
echo "PATH=${PATH}:/data/mysql-5.7.34-el7-x86_64/bin" >> /etc/profile
source /etc/profile
mysqld --initialize --datadir=/data/mysql_5.7 --user=mysql创建文件夹存放日志，pid，套接字，锁
cd /data/mysql-5.7.34-el7-x86_64
mkdir conf修改配置文件
vim /etc/my.cnf #如果没有这个文件就创建一下
[mysqld]
user = mysql
bind-address=0.0.0.0
port = 3306
basedir=/data/mysql-5.7.34-el7-x86_64
datadir=/data/mysql_5.7
pid-file=/data/mysql-5.7.34-el7-x86_64/conf/mysql.pid
log-error=/data/mysql-5.7.34-el7-x86_64/conf/mysql.log
socket=/data/mysql-5.7.34-el7-x86_64/conf/mysql.socket
[mysqld_safe]
user = mysql
bind-address=0.0.0.0
port=3306
log-error=/data/mysql-5.7.34-el7-x86_64/conf/mysql.log #这个文件需要预先进行创建
pid-file=/data/mysql-5.7.34-el7-x86_64/conf/mysql.pid
[client]
socket=/data/mysql-5.7.34-el7-x86_64/conf/mysql.socket
# include all files from the config directory
# !includedir /etc/my.cnf.dvim /data/mysql-5.7.34-el7-x86_64/support-files/mysql.server #将其中的basedir和datadir换成相应的路径，设置pid路径
basedir=/data/mysql-5.7.34-el7-x86_64
datadir=/data/mysql_5.7
#pid文件路径
mysqld_pid_file_path=/data/mysql-5.7.34-el7-x86_64/conf/mysql.pid创建mysql.log文件并且修改属主属组
touch /data/mysql-5.7.34-el7-x86_64/conf/mysql.log
chown -R mysql.mysql /data/mysql-5.7.34-el7-x86_64/conf/启动数据库
/data/mysql-5.7.34-el7-x86_64/support-files/mysql.server start使用mysql进行连接
/data/mysql-5.7.34-el7-x86_64/bin/mysql -p创建相对应的service文件
mysql创建services文件的文档
#可以使用mysqld --verbose --help命令来查看mysqld后面可以跟随的参数
vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
TimeoutSec=0
PIDFile=/data/mysql-5.7.34-el7-x86_64/conf/mysql.pid
ExecStart=/data/mysql-5.7.34-el7-x86_64/bin/mysqld --daemonize --pid-file=/data/mysql-5.7.34-el7-x86_64/conf/mysql.pid --basedir=/data/mysql-5.7.34-el7-x86_64 --datadir=/data/mysql_5.7重新载入systemd文件
systemctl daemon-reload使用systemctl启动mysql
systemctl start mysqld注意事项
mysql5.7之后的版本基于安全考虑，为root用户默认了一个初始密码，我们可以使用以下命令进行查看
grep "temporary password" /var/log/mysqld.log
使用该密码即可进行登陆，但在登陆之后需要修改密码。
修改密码之后可以使用show global variables like '%validate_password%'查看密码策略
学习期间可以使用命令将密码验证删除掉
删除密码验证插件
uninstall plugin validate_password;安装密码验证插件
install plugin validate_password soname 'validate_password.so';
建议：在安装之后执行
mysql_secure_installation -p'临时密码'
mysql客户端连接时，localhost使用的是套接字进行连接，而若是指名地址，则是会使用TCP进行连接
某些版本可能会进行域名反向解析，将IP地址反向解析为主机名然后进行连接，可以使用服务器参数配置进行调整，skip_name_resolve=1
获取mysqld的可用选项列表：
- 可以使用
mysqld --print-defaults来查看当前mysqld启动时使用的参数 - 使用
mysqld --verbose --help查看可用选项列表以及当前的值
- 可以使用
mysql基础说明
数据库基本概念了解
- 数据库：database
- 表：table，行：row 字段：column
- 约束：constraint，表中的数据要遵守的限制
- 主键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；必须提供数据，即NOT NULL，一个表只能有一个
- 惟一键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；允许为NULL，一个表可以存在多个
- 外键：一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据
- 检查：字段值在一定范围内
- 索引：将表中的一个或多个字段中的数据复制一份另存，并且按特定次序排序存储
数据库对象和命名
数据库的组件(对象)
数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
命名规则
- 必须以字母开头，可包括数字和三个特殊字符（# _ $）
- 不要使用MySQL的保留字
SQL语言规范
- 在数据库系统中，SQL语句不区分大小写，建议用大写
- SQL语句可单行或多行书写，以“;”结尾
- 关键词不能跨多行或简写
- 用空格和缩进来提高语句的可读性
- 子句通常位于独立行，便于编辑，提高可读性
Mysql默认数据库
- mysql 数据库
- 是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息
- performance_schema 数据库
- MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表
- information_schema 数据库
- MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）
- sys 数据库
- MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况
mysql客户端命令格式以及选项
mysql [OPTIONS] [database]
注意mysql所有选项后面的参数都不能有空格，要将选项和参数连在 一起，当然可以使用引号。
常见选项：
-u, --user=name ： 指名用户
-p, --password[=name] ： 指名密码
-P, --port=# ：指名端口
-h, --host=name ：指名要连接到的主机
-e, --execute=name ：执行命令，然后退出
-V, --version ：查看版本信息，然后退出
-S, --socket=/path/to/file : 指名套接字文件mysql配置格式以及路径
mysql的配置都是以配置块的方式进行配置，通过在配置块设置相应的配置选项来设置配置
配置块的格式：
[配置块的名字]
配置参数1
配置参数2通常来说有以下配置块：
- [mysqld] ：mysql服务器配置
- [mysqld_safe] ：mysql_safe配置
- [client] ：mysql客户端配置
mysql相应目录以及配置文件
mysql的配置文件：/etc/my.cnf和/etc/my.cnf.d/*
mysql的目录文件：/var/lib/mysql
mysql配置相关
mysql的配置可以分为这么三类，命令行参数，系统变量，服务器选项
- 命令行参数 #通常来说是位于mysql命令之后是连接mysql的server时进行设置，限于本次连接有效
- 系统变量 #可以再度进行细分，通常是服务启动时内部设置好的，可以进行修改
- 服务器选项 #配置在对应的文件之下， 如果更改需要重启服务器才能生效
各种参数详解：https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html
sql语句以及数据类型
注：在MySQL中我们通常使用大写代表命令，小写代表我们在使用中要变化的参数。中括号表示的该参数是可选的，尖括号意味意味有多个选项，连续的三个点表示它前面的参数可以是多个。
数据类型
选择合适的数据类型对于获得高性能至关重要，三大原则：
- 在允许的范围内尽可能地选择较小的的数据类型
- 简单就好，简单数据类型的操作通常需要更少的CPU周期
- 尽量避免NULL，包含为NULL的列，对MySQL更难优化
常见的数据类型：
整数型
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)
BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真
#上述数据类型，如果加修饰符unsigned后，则最大值翻倍
浮点型
- float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
- double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位
定点数
在数据库中存放的是精确值,存为十进制
- decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
字符串
char(n) 固定长度，最多255个字符,注意不是字节
varchar(n) 可变长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符
BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
内建类型：ENUM枚举, SET集合
注意
- char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符 串末尾不能有空格，varchar不限于此
- char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1 个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
- char类型的字符串检索速度要比varchar类型的快
- varchar和text：
- varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。
- text类型不能有默认值
- varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text
二进制BLOB
BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob以二进制方式存储，不分大小写 BLOB存储的数据只能整体读出 TEXT可以指定字符集，BLOB不用指定字符集
日期时间类型
date 日期 '2008-12-2'
time 时间 '12:25:36'
datetime 日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间
YEAR(2), YEAR(4)：年份
timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间
修饰符 | 约束条件
- 适用所有类型的修饰符：
- NULL 数据列可包含NULL值，默认值
- NOT NULL 数据列不允许包含NULL值，*为必填选项
- DEFAULT 默认值
- PRIMARY KEY 主键，所有记录中此字段的值不能重复，且不能为NULL
- UNIQUE KEY 唯一键，所有记录中此字段的值不能重复，但可以为NULL
- CHARACTER SET name 指定一个字符集
- 适用数值型的修饰符：
- AUTO_INCREMENT 自动递增，适用于整数类型
- UNSIGNED 无符号
数据库相关的sql语句
创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ...
创建选项：
CHARACTER SET [=] charset_name #设置字符集
COLLATE [=] collation_name #设置排序规则
ENCRYPTION [=] {'Y' | 'N'} #是否加密
说明 ：
db_name #数据库名
charset_name #字符集名
collation_name #排序规则名修改数据库
ALTER {DATABASE | SCHEMA} [db_name] alter_option ...
alter_option #修改选项
CHARACTER SET [=] charset_name
COLLATE [=] collation_name
[DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
READ ONLY [=] {DEFAULT | 0 | 1} #是否只读删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name查看数据库
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]
说明：
pattern #模式
expr #表达式
https://dev.mysql.com/doc/refman/8.0/en/expressions.html查看数据库创建命令
SHOW CREATE DATABASE db_name进入数据库
USE db_name查看当前所在数据库
SELECT DATABASE()表相关的sql语句
创建表
- 直接创建
CREATE [TEMPORARY] TABLE tbl_name ( create_definition , ... ) [table_options]
create_definition #创建定义
col_name column_definition #字段选项
column_definition #常用字段选项
data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
table_options:
table_option [[,] table_option] ...
table_option { #表选项
AUTO_INCREMENT [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| ENGINE [=] engine_name)
}- 通过查询其他表进行创建，新的表会直接插入查询出来的结果
CREATE [TEMPORARY] TABLE tbl_name [(create_definition,...)] [table_options] query_expression
query_expression #查询表达式
SELECT ... (Some valid select or union statement)- 通过复制已有的表的结构创建，不复制表中的数据
CREATE [TEMPORARY] TABLE tbl_name { LIKE old_tbl_name | ( LIKE old_tbl_name ) }删除表
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...查看表
SHOW TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
DESC tbl_name查看表创建命令
SHOW CREATE TABLE tbl_name修改表
ALTER TABLE tbl_name [alter_option [, alter_option] ...]
alter_option: {
table_options
| ADD [COLUMN] col_name column_definition [ FIRST | AFTER col_name]
| ADD [COLUMN] ( col_name column_definition , ... )
| ALTER [COLUMN] col_name {SET DEFAULT {literal | (expr)} | DROP DEFAULT}
| ALTER INDEX index_name {VISIBLE | INVISIBLE}
| CHANGE [COLUMN] old_col_name new_col_name column_definition [ FIRST | AFTER col_name ]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DROP [COLUMN] col_name
| DROP {INDEX | KEY} index_name
| DROP PRIMARY KEY
| MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
| ORDER BY col_name [, col_name] ...
| RENAME COLUMN old_col_name TO new_col_name
| RENAME {INDEX | KEY} old_index_name TO new_index_name
| RENAME [TO | AS] new_tbl_name
}数据相关的sql语句
添加数据
- 添加多行数据
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ...
| VALUES row_constructor_list }
#注意如果前面指定了col_name,后面就只需要配置指定的col_name对应的值
#但如果前面没有指明col_name,后面就需要配置每一个col_name所对应的值
row_constructor_list:
ROW(value_list)[, ROW(value_list)][, ...]
value_list:
value [, value] ...
assignment:
col_name = [row_alias.]value- 添加单行数据
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name
SET assignment_list
assignment_list:
assignment [, assignment] ...
assignment:
col_name = [row_alias.]value- 从其他表导入数据
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[(col_name [, col_name] ...)]
{SELECT ... | TABLE table_name}删除数据
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
where_condition #过滤条件
过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
BETWEEN min_num AND max_num
IN (element1, element2, ...)
IS NULL
IS NOT NULL
DISTINCT 去除重复行，范例：SELECT DISTINCT gender FROM students;
LIKE:
% 任意长度的任意字符
_ 任意单个字符
RLIKE：正则表达式，索引失效，不建议使用
REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：NOT，AND，OR，XOR修改数据
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
assignment:
col_name = value查看数据
单表查询
SELECT [ALL | DISTINCT | DISTINCTROW ]
select_expr [, select_expr] ...
[FROM table_references]
[WHERE where_condition]
[GROUP BY col_name ... ]
[HAVING where_condition]
[ORDER BY col_name [ASC | DESC] ]
[ LIMIT [offset,] row_count ]
select_expr {
col_name
}
table_reference: {
table_factor
| joined_table
}
table_factor: {
tbl_name [[AS] alias]
| ( table_references )
}
joined_table: {
table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
| table_reference NATURAL [INNER | {LEFT|RIGHT} ] JOIN table_factor
}
GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
常见聚合函数：avg(), max(), min(), count(), sum()
HAVING: 对分组聚合运算后的结果指定过滤条件
一旦分组group by ，select语句后只跟分组的字段和聚合函数
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制多表查询
#为了方便大家理解，我们这里使用两个表来给进行演示，一个表是student，另一个是teacher
student
| id | name | age | class | teacher_id |
|---|---|---|---|---|
| 1 | 张三 | 12 | 1 | 1 |
| 2 | 李四 | 25 | 1 | 3 |
| 3 | 王五 | 18 | 2 | 2 |
| 4 | 赵六 | 35 | 2 | 2 |
| 5 | 李林 | 28 | null | null |
teacher
| id | name | age | class |
|---|---|---|---|
| 1 | 小明 | 20 | 1 |
| 2 | 小红 | 40 | 2 |
| 3 | 小刚 | 35 | 3 |
| 4 | 小芳 | null | null |
子查询
#在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
常用在WHERE子句中的子查询
- 用于比较表达式中的子查询；子查询仅能返回单个值
SELECT Name,Age FROM student WHERE Age>(SELECT avg(age) FROM teacher);- 用于IN中的子查询：子查询应该单独查询并返回一个或多个值重新构成列表
SELECT Name,Age FROM student WHERE Age IN (SELECT Age FROM teacher);
用于FROM子句中的子查询
# SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
SELECT * FROM (SELECT id,class,name FROM teacher) as a ;
内连接
FROM tb1 INNER JOIN tb2 ON tb1.col=tb2.col
SELECT * FROM student INNER JOIN teacher ON student.teacher_id=teacher.id;
外连接
- left join #左插入
FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
SELECT * FROM student LEFT JOIN teacher ON student.teacher_id=teacher.id;- right join #右插入
FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
SELECT * FROM student RIGHT JOIN teacher ON student.teacher_id=teacher.id;union #联合查询
FROM tb1 UNION tb2 ON tb1.col=tb2.col
SELECT id,name,class,age FROM student UNION SELECT id,name,age,class FROM teacher;交叉查询
- cross join #笛卡尔乘积,
SELECT *FROM student CROSS JOIN teacher;在 MySQL 中JOIN，CROSS JOIN、 和INNER JOIN是句法等价物（它们可以相互替换）。在标准 SQL 中，它们不是等价的。
服务器配置和状态
变量相关的sql语句
这是官网对变量的整体说明：https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html
MySQL中的可变的变量有两种变量：系统内置变量和用户自定义变量，当然还有一种是不可修改的状态变量
服务器状态变量：分全局和会话两种，可以使用
SHOW GLOBAL STATUS和SHOW [SESSION] STATUS来进行查看- 状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改
- https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html
系统变量：MySQL数据库中内置的变量，可用@@var_name引用
- 这是系统变量的说明：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
- 这里呢，我们列一些常见的系统变量
- max_connections：最大连接数，这个需要提前设置服务器参数LimitNOFILE=65535
- innodb_page_size：innodb页面大小，一般来说也是改为65536
- character_set_results：向客户端返回查询结果时的字符集，这个配置是系统变量却不是服务器参数
用户自定义变量分为以下两种
普通变量：在当前会话中有效，可用@var_name引用
局部变量：在函数或存储过程内才有效，需要用DECLARE 声明，之后直接用 var_name引用自定义函数中定义局部变量语法
自定义函数中定义局部变量语法
DECLARE var_name [, var_name] ... type [DEFAULT value]说明：局部变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一行定义
设置变量
SET variable = expr [, variable = expr] ...
variable: {
user_var_name
| local_var_name
| {GLOBAL | @@GLOBAL.} system_var_name
| [ SESSION | @@SESSION. | @@ ] system_var_name
}查看系统变量
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
like_or_where: {
LIKE 'pattern'
| WHERE expr
}
SELECT @@system_var_name查看用户自定义变量
SELECT @variable用户相关sql语句
%：类似于linux通配符中的*，意味任意长度的任意字符
_ ：类似于linux通配符中的？，意味一个长度的任意字符
创建用户
Syntax:
CREATE USER [IF NOT EXISTS]
user [auth_option] [, user [auth_option]] ...
user {
'user_name'[@'host_name']
}
#主机名可省略，但不指名特定主机就意味着所有主机
example : 'me' 等价于'me'@'%'.
#如果没有特殊符号可以不加引号，但有特殊符号时一定要加引号
#用户名和主机名部分（如果引用）必须单独引用，也就是说，写 'me'@'localhost'，不是 'me@localhost'，后者实际上相当于'me@localhost'@'%'
auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED BY RANDOM PASSWORD
}用户重命名
RENAME USER old_user_name TO new_user_name;
删除用户
DROP USER 'USERNAME'@'HOST'
查看用户
SELECT * FROM mysql.user
修改密码
ALTER USER [IF EXISTS] user [auth_option] [, user [auth_option]] ...
ALTER USER [IF EXISTS] USER() user_func_auth_option
user_func_auth_option: {
IDENTIFIED BY 'auth_string'
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
}
ALTER USER user IDENTIFIED BY 'auth_string' #MySQL 8 版本修改密码
SET PASSWORD FOR 'user'@'host' = PASSWORD('password') #MySQL 8 版本不支持此方法忘记root密码的解决办法
启动mysqld进程时，为其使用如下选项：
[mysqld] skip-grant-tables skip-networking重启mysqld，使用UPDATE命令修改管理员密码
#MySQL8.0 update mysql.user set authentication_string='111111' where user='root' and host='localhost'; 旧版本可以使用 update mysql.user set password=password('111111') where user='root';关闭mysqld进程，移除上述两个选项，重启mysqld
权限管理与授权sql语句
权限分类：
- Global Level # 全局级别
- Database Level # 数据库级别
- Table Level #表级别
- Column Level #字段级别
- Routine Level # 常规级别
全局级别：
- CREATE USER
- SHUTDOWN
- REPLICATION SLAVE
- REPLICATION CLIENT
字段级别
- SELECT(col1,col2,...)
- UPDATE(col1,col2,...)
- INSERT(col1,col2,...)
所有权限
- ALL PRIVILEGES 或 ALL
授权
GRANT
priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] priv_level TO user_or_role [, user_or_role] ...
object_type: {
TABLE
| FUNCTION
| PROCEDURE
}
priv_level: {
*
| *.*
| db_name.*
| db_name.tbl_name
}
user_or_role: {
user (see https://dev.mysql.com/doc/refman/8.0/en/account-names.html)
| role (see https://dev.mysql.com/doc/refman/8.0/en/role-names.html)
}取消授权
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user_or_role [, user_or_role] ...额外的一些sql语句
字符集和排序
早期MySQL版本默认为latin1，从MySQL8.0开始默认字符集已经为 utf8mb4
查看支持所有字符集：
SHOW CHARACTER SET查看当前字符集的使用情况
SHOW VARIABLES LIKE 'character%'查询当前表中各个字段的字符集
SHOW FULL COLUMNS FROM tbl_name;设置字符集
[mysqld] character-set-server=utf8mb4 #临时设置 SET GLOBAL character_set_server=utf8mb4 #持久化设置 SET PERSIST character_set_server=utf8mb4查看支持所有排序规则：
bashSHOW COLLATION查看当前使用的排序规则
SHOW GLOBAL VARIABLES LIKE '%COLLATION%'
8和5.7区别
- 添加了nosql，不过使用较少
- 窗口函数
- 隐藏索引
- 通过role进行授权
- 系统表使用的默认存储引擎为innodb #升级时需额外注意
- 默认字符集utf8mb4
- 设置持久化 set persist，将设置保存到数据目录下的mysqld_auto.cnf，下一次数据库启动时会自动加载
- 以前，通过使用CREATE TEMPORARY TABLE不存在的数据库的名称限定表名，可以使用 它在不存在的数据库中创建表。这不再被允许。
- 对json的支持增强
mysql特性
VIEW 视图
视图view：虚拟表，保存有实表的查询结果，相当于别名
CREATE VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]查看视图定义：
SHOW CREATE VIEW view_name #只能看视图定义
SHOW CREATE TABLE view_name # 可以查看表和视图删除视图：
DROP VIEW [IF EXISTS]
view_name [, view_name] ...注意：视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表限制
流控制语句
标签
标签被允许 BEGIN ... END块和对LOOP， REPEAT和 WHILE语句。这些语句的标签使用遵循以下规则：
begin_label后面必须跟一个冒号。- *
begin_label*可以不给end_label。如果end_label存在，它必须与 相同begin_label。 - *
end_label*不能没有begin_label. - 同一嵌套级别的标签必须是不同的。
- 标签最长可达 16 个字符。
要引用标记结构中的标签，请使用 ITERATEor LEAVE语句。以下示例使用这些语句继续迭代或终止循环：
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN ITERATE label1; END IF;
LEAVE label1;
END LOOP label1;
ENDIF语句
IF search_condition THEN
statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IFLOOP 语句
LOOP实现一个简单的循环结构，允许重复执行语句列表，该列表由一个或多个语句组成，每个语句以分号 ( ;) 语句分隔符终止。循环中的语句会重复执行，直到循环终止。通常，这是通过LEAVE语句完成的 。在存储的函数中，RETURN也可以使用，它完全退出函数。
[begin_label:] LOOP
statement_list
END LOOP [end_label]REPEAT 语句
语句中的语句列表 REPEAT会重复，直到*search_condition*表达式为真。因此， REPEAT总是至少进入循环一次。 *statement_list*由一个或多个语句组成，每个语句以分号 ( ;) 语句分隔符结束。
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]WHILE 语句
WHILE只要*search_condition*表达式为真 ，语句中的语句列表就会重复 。 *statement_list*由一个或多个 SQL 语句组成，每个语句以分号 ( ;) 语句分隔符终止。
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]FUNCTION 函数
函数：分为系统内置函数和自定义函数
| 名称 | 描述 |
|---|---|
AVG() | 返回参数的平均值 |
COUNT() | 返回返回的行数的计数 |
MAX() | 返回最大值 |
MIN() | 返回最小值 |
SUM() | 返回总和 |
自定义函数：user-defined function UDF，保存在mysql.proc表中
CREATE [AGGREGATE] FUNCTION function_name( parameter_name type, [parameter_name type , ... ] )
RETURNS {STRING|INTEGER|REAL}
routine_body
CREATE [DEFINER = user] FUNCTION sp_name ([func_parameter[,...]])
RETURNS type [characteristic ...]
routine_body
parameter_name：参数名说明：
- 参数可以有多个,也可以没有参数
- 无论有无参数，小括号（）是必须的
- 必须有且只有一个返回值
查看函数列表：
SHOW FUNCTION STATUS;查看函数定义
SHOW CREATE FUNCTION function_name删除UDF
DROP FUNCTION function_name调用自定义函数语法
SELECT function_name(parameter_value,...)
parameter_value：参数值#无参UDF
CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";
#有参数UDF
DELIMITER //
CREATE FUNCTION deleteById(id SMALLINT UNSIGNED) RETURNS VARCHAR(20)
BEGIN
DELETE FROM students WHERE stuid = id;
RETURN (SELECT COUNT(*) FROM students);
END//
DELIMITER ;PROCEDURE 存储过程
存储过程：多表SQL的语句的集合，可以独立执行，存储过程保存在mysql.proc表中
存储过程优势：
存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程，提高了运行速度，同时降低网络数据传输量
存储过程与自定义函数的区别
- 存储过程实现的过程要复杂一些,而函数的针对性较强
- 存储过程可以有多个返回值,而自定义函数只有一个返回值
- 存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用
- 无参数的存储过程执行过程中可以不加(),函数必须加 ( )
创建存储过程
CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])
routime_body
proc_parameter : [IN|OUT|INOUT] parameter_name type说明：其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；
param_name 表示参数名称；type表示参数的类型
查看存储过程列表
SHOW PROCEDURE STATUS;查看存储过程定义
SHOW CREATE PROCEDURE sp_name调用存储过程
CALL sp_name ([ proc_parameter [,proc_parameter ...]])说明:当无参时,可以省略"()",当有参数时,不可省略"()”
存储过程修改
ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改存储过程,方法就是删除重建
删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name
#创建含参存储过程:包含IN参数和OUT参数
delimiter //
CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
BEGIN
DELETE FROM students WHERE stuid >= id;
SELECT row_count() into num;
END//
delimiter ;
call deleteById(20,@Line);
SELECT @Line;
#说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行
数值的用户变量@Line,select @Line;输出被影响行数
#row_count() 系统内置函数，用于存放前一条SQL修改过的表的记录数流程控制
存储过程和函数中可以使用流程控制来控制语句的执行
- IF：用来进行条件判断。根据是否满足条件，执行不同语句
- CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断
- LOOP：重复执行特定的语句，实现一个简单的循环
- LEAVE：用于跳出循环控制，相当于SHELL中break
- ITERATE：跳出本次循环，然后直接进入下一次循环，相当于SHELL中continue
- REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句
- WHILE：有条件控制的循环语句
TRIGGER 触发器
触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行
创建触发器
CREATE [DEFINER = user]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name说明：
- trigger_name：触发器的名称
- trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
- trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
- tbl_name：该触发器作用在表名
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;//
mysql> delimiter ;查看触发器#在当前数据库对应的目录下，可以查看到新生成的相关文件：
trigger_name.TRN,table_name.TRG
SHOW TRIGGERS;
#查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发器信息。
USE information_schema;
SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert';删除触发器
DROP TRIGGER trigger_name;Event 事件
Event 事件介绍
event类似于我们linux中的crontab，定时执行某些任务，不同于crontab最低只能到分钟执行，event最低可以到秒级执行。
MySQL事件调度器event_scheduler负责调用事件， ON是默认 event_scheduler值。#不同版本可能不太一样
这个调度器不停的监视一个事件是否要进行调用， 要创建事件，必须打开调度器
开启调度器 #以下四种方法都可以
SET GLOBAL event_scheduler = ON;
SET @@GLOBAL.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@GLOBAL.event_scheduler = 1;开启事件调度功能后,自启动一个event_scheduler线程
show processlist;管理事件
create event 语句创建一个事件。每个事件由两个主要部分组成，第一部分是事件调度（eventschedule），表示事件何时启动以及按什么频率启动，第二部分是事件动作（event action ），这是事件启动时执行的代码，事件的动作包含一条SQL语句，它可能是一个简单地insert或者update语句，也可以使一个存储过程或者 benin...end语句块，这两种情况允许我们执行多条SQL
一个事件可以是活动（打开）的或停止（关闭）的，活动意味着事件调度器检查事件动作是否必须调用，停止意味着事件的声明存储在目录中，但调度器不会检查它是否应该调用。在一个事件创建之后，它立即变为活动的，一个活动的事件可以执行一次或者多次
CREATE [DEFINER = user] EVENT [IF NOT EXISTS]
event_name ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
DO event_body;
schedule: {
AT timestamp [+ INTERVAL interval] ...
| EVERY interval [STARTS timestamp [+ INTERVAL interval] ...] [ENDS timestamp [+ INTERVAL interval] ...]
}
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}说明：
event_name ：创建的event名字，必须是唯一确定的
schedule: 决定event的执行时间和频率（注意时间一定要是将来的时间，过去的时间会出错），有两种形式 AT和EVERY
[ON COMPLETION [NOT] PRESERVE]： 可选项，默认是ON ，意为计划任务执行完毕后是否删除该计划任务，ON为不删除
[ENABLE | DISABLE] ：计划任务是否启动可以用alter修改
event_body: 需要执行的sql语句
示例：
每秒事件
CREATE EVENT event_every_second ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO events_list VALUES('event_now', now());
每分钟事件
create event testdb.event_every_minute on schedule every
1 minute do insert into events_list values('event_now', now());查看Event
SHOW EVENTS [{FROM | IN} schema_name] [LIKE 'pattern' | WHERE expr]注意：事件执行完即释放，如立即执行事件，执行完后，事件便自动删除，多次调用事件或等待执行事件,才可以用上述命令查看到。
event事件是存放在mysql.event表中
修改Event
ALTER [DEFINER = user] EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[DO event_body]删除Event
DROP EVENT [IF EXISTS] event_namemysql架构与性能优化
存储引擎
mysql的存储引擎种类非常之多，大概有上百种之多，每一种存储引目前主流的存储引擎主要有上面图片中的那些，不过自8.0开始，Myisam引擎也开始被放弃使用，而是使用更加安全的innodb。
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力,此种技术称为存储擎,MySQL 支持多种存储引擎其中目前应用最广泛的是InnoDB和MyISAM两种
mysql中使用最多的两种存储引擎就是InnoDB和MyISAM两种。在8.0之前MySQL中的mysql数据库就是使用的是MyISAM存储引擎，但是在8.0之后，MySQL默认的存储引擎也换成了innodb。
网站：https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/storage-engines.html
#包含了各种索引的说明
| 特点 | MyISAM | Memory | InnoDB | Archive | NDB |
|---|---|---|---|---|---|
| B树索引 | Yes | Yes | Yes | No | No |
| 备份/时间点恢复 | Yes | Yes | Yes | Yes | Yes |
| 集群数据库支持 | No | No | No | No | Yes |
| 聚集索引 | No | No | Yes | No | No |
| 压缩数据 | Yes | No | Yes | Yes | No |
| 数据缓存 | No | N/A | Yes | No | Yes |
| 加密数据 | Yes | Yes | Yes | Yes | Yes |
| 外键支持 | No | No | Yes | No | Yes |
| 全文检索索引 | Yes | No | Yes | No | No |
| 地理空间数据类型支持 | Yes | No | Yes | Yes | Yes |
| 地理空间索引支持 | Yes | No | Yes | No | No |
| 哈希索引 | No | Yes | No | No | Yes |
| 索引缓存 | Yes | N/A | Yes | No | Yes |
| 锁定级别 | Table | Table | Row | Row | Row |
| MVCC(高版本并发控制) | No | No | Yes | No | No |
| 复制支持 | Yes | Limited | Yes | Yes | Yes |
| 存储限制 | 256TB | RAM | 64TB | None | 384EB |
| T树索引 | No | No | No | No | Yes |
各种引擎简介
- InnoDB：MySQL 8.0 中的默认存储引擎。
InnoDB是 MySQL 的事务安全（符合 ACID）存储引擎，具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁定）和 Oracle 风格的一致非锁定读取提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的 I/O。为了维护数据完整性，InnoDB还支持FOREIGN KEY引用完整性约束。 - MyISAM：这些表占用空间很小。 表级锁定限制了读/写工作负载的性能，因此它通常用于 Web 和数据仓库配置中的只读或以读取为主的工作负载。
- Memory：将所有数据存储在 RAM 中，以便在需要快速查找非关键数据的环境中进行快速访问。这种引擎以前称为
HEAP引擎。它的用例正在减少；InnoDB其缓冲池内存区域提供了一种通用且持久的方式来将大部分或所有数据保存在内存中，并NDBCLUSTER为庞大的分布式数据集提供快速的键值查找。 - CSV：它的表格实际上是具有逗号分隔值的文本文件。CSV 表允许您以 CSV 格式导入或转储数据，以便与读写相同格式的脚本和应用程序交换数据。由于 CSV 表未编入索引，因此您通常
InnoDB在正常操作期间将数据保存在表中，并且仅在导入或导出阶段使用 CSV 表。 - Archive：这些紧凑的、未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。
- Blackhole：Blackhole 存储引擎接受但不存储数据，类似于linux中的
/dev/null设备。查询总是返回一个空集。这些表可用于复制配置，其中 DML 语句被发送到副本服务器，但源服务器不保留自己的数据副本。 - NDB：（也称为
NDBCLUSTER（也称为NDBCLUSTER）：这种集群数据库引擎特别适用于需要尽可能高的正常运行时间和可用性的应用程序。 - Merge：使 MySQL DBA 或开发人员能够对一系列相同的
MyISAM表进行逻辑分组并将它们作为一个对象引用。适用于 VLDB 环境，例如数据仓库。 - Federated：提供链接单独的 MySQL 服务器以从多个物理服务器创建一个逻辑数据库的能力。非常适合分布式或数据集市环境。
在mysql8.0之中我们使用innodb引擎作为默认引擎，5.7之前的版本中的mysql数据库使用MyIsam存储引擎
MyIsam的文件
- tbl_name.frm 表格式定义，8.0已经删除
- tbl_name.MYD 数据文件
- tbl_name.MYI 索引文件
Innodb的文件
- tb_name.ibd 数据文件(存储数据和索引)
- tb_name.frm 表格式定义
存储引擎管理
查看mysql支持的存储引擎
SHOW ENGINES查看当前默认的存储引擎
SHOW VARIABLES LIKE '%storage_engine%'设置默认的存储引擎
vim /etc/my.cnf
[mysqld]
default_storage_engine= InnoDB设置表的存储引擎
CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;索引
索引简介
是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现
索引优点以及缺点
优点：
- 索引可以降低服务需要扫描的数据量，减少了IO次数
- 索引可以帮助服务器避免排序和使用临时表
- 索引可以帮助将随机I/O转为顺序I/O
缺点
- 占用额外空间，影响插入速度
索引类型
- B+ TREE、HASH、R TREE、FULL TEXT
- 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
- 主键索引、二级（辅助）索引
- 稠密索引、稀疏索引：是否索引了每一个数据项
- 简单索引、组合索引
- 左前缀索引：取前面的字符做索引
- 覆盖索引：从索引中即可取出要查询的数据，性能高
索引结构
索引通过某些特殊的数学结构可以在最快的时间内查找到我们需要的内容，因此索引在MySQL中很重要，很重要，很重要
- 二叉树
- 红黑树
- B-Tree
- B+Tree
可以使用B+Tree索引的查询类型
- 全值匹配：精确所有索引列
- 匹配最左前缀：即只使用索引的第一列
- 匹配列前缀：只匹配一列值开头部分
- 匹配范围值
- 精确匹配某一列并范围匹配另一列
- 只访问索引的查询
B+Tree索引的限制：
- 如不从最左列开始，则无法使用索引
- 不能跳过索引中的列
管理索引
创建索引
CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)] ,... );
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]);
help CREATE INDEX;删除索引：
DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);查看索引
SHOW INDEXES FROM [db_name.]tbl_name;优化表空间
OPTIMIZE TABLE tb_name;查看索引的使用
SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;EXPLAIN 工具
explain工具是用来进行索引优化，分析索引的有效性,获取查询执行计划信息
syntax:
EXPLAIN explainable_stmt
explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}| Column | JSON Name | Meaning |
|---|---|---|
id | select_id | 执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置 |
select_type | None | 搜索类型，简单查询：SIMPLE，复杂查询：PRIMARY，DERIVED（用于FROM中的子查询） |
table | table_name | 访问引用哪个表（引用某个查询，如“derived3”） |
type | access_type | 关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式 |
possible_keys | possible_keys | 查询可能会用到的索引 |
key | key | 显示mysql决定采用哪个索引来优化查询 |
key_len | key_length | 显示mysql在索引里使用的字节数 |
ref | ref | 根据索引返回表中匹配某单个值的所有行 |
rows | rows | 为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数 |
filtered | filtered | 按表条件过滤的行百分比 |
Extra | None | 额外信息 |
TYPE类型
掌握以下10种常见的即可
NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL| 类型 | 说明 |
|---|---|
| All | 最坏的情况,全表扫描 |
| index | 和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多 |
| range | 范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range |
| ref | 一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。 |
| eq_ref | 最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 （高效） |
| const | 当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效） |
| system | 这是const连接类型的一种特例，表仅有一行满足条件。 |
| Null | 意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效） |
并发控制
锁机制
锁：
- 读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务） ，多个读互不阻塞
- 写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写
S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容
锁级别（锁粒度）
- Myisam：表级锁
- Innodb：行级锁
加锁
LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias]lock_type] ...
lock_type:{ READ | WRITE }解锁
UNLOCK TABLES事务
事务特性
ACID特性：
- A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
- C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
- I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
- D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中
管理事务
显式启动事务：
BEGIN
BEGIN WORK
START TRANSACTION结束事务：
#提交
COMMIT
#回滚
ROLLBACK事务的四种级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 加读锁 |
|---|---|---|---|---|
| 读未提交(READ UNCOMMITTED) | yes | yes | yes | no |
| 读已提交(READ COMMITTED) | no | yes | yes | no |
| 可重复读(REPEATABLE READ) | no | no | yes | no |
| 序列化(SERIALIZABLE) | no | no | no | yes |
MVCC和事务的隔离级别：
MVCC（多版本并发控制机制）只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁
设定事务运行级别
5.7之前的版本使用服务器变量tx_isolation指定，默认为REPEATABLE-READ，可在GLOBAL和SESSION级进行设置
8.0使用服务器变量transaction_isolation指定
show global variables like "tx_isolation" #5.7查看事务运行级别
show global variables like "transaction_isolation" #8.0查看事务运行级别
set global tx_isolation="REPEATABLE-READ" #5.7设定事务运行级别
set global transaction_isolation="REPEATABLE-READ" #8.0设定事务运行级别事务的提交类型
- 0：每秒提交事务到文件磁盘
- 1：一次事务一次提交 #安全
- 2：每秒提交事务到系统缓存，由系统写入磁盘 #常用
设定事务的提交类型
SET GLOBAL innodb_flush_log_at_trx_commit = 2日志
事务日志
redo log：实现 WAL（Write Ahead Log) ,数据更新前先记录redo log
undo log：保存与执行的操作相反的操作,用于实现rollback
注意：事务型存储引擎自行管理和使用，建议和数据文件分开存放
查看日志相关的变量
SHOW VARIABLES LIKE '%innodb_log%'二进制日志
特性：
- 记录导致数据改变或潜在导致数据改变的SQL语句
- 记录已提交的日志
- 不依赖于存储引擎类型
功能：通过“重放”日志文件中的事件来生成数据副本 注意：建议二进制日志和数据文件分开存放
二进制日志记录三种格式
- 基于“语句”记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
- 基于“行”记录：row，记录数据，日志量较大，更加安全，建议使用的格式 #8.0默认模式
- 混合模式：mixed, 让系统自行判定该基于哪种方式进行
二进制相关的变量
sql_log_bin=ON|OFF：#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=/PATH/BIN_LOG_FILE：#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，默认ROW
max_binlog_size=1073741824：#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0：#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：#二进制日志可以自动删除的天数。 默认为0，即不自动删除二进制日志文件的构成
有两类文件
1.日志文件：binlog.文件名后缀，二进制格式,如： binlog.000001,binlog.000002
2.索引文件：binlog.index，文本格式,记录当前已有的二进制日志文件列表查看二进制日志
SHOW {BINARY | MASTER} LOGS查看正在使用中的二进制日志文件
SHOW MASTER STATUS清除指定二进制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }删除所有二进制日志，index文件重新记数
RESET MASTER [TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行刷新新的二进制日志
FLUSH LOGS;mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志
mysqlbinlog [OPTIONS] log_file…
OPTIONS：选项
--start-position=# 指定开始位置
--stop-position=#
--start-datetime= #时间格式：YYYY-MM-DD hh:mm:ss
--stop-datetime=
--base64-output[=name]
-v -vvv通用日志
通用日志：记录对数据库的通用操作，包括:错误的SQL语句
通用日志可以保存在：file（默认值）或 table（mysql.general_log表）
查看通用日志相关参数
SHOW GLOBAL VARIABLES LIKE '%general%'通用日志相关设置
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE错误日志
错误日志内容
- mysqld运行中产生的错误信息
- mysqld启动和关闭过程中输出的事件信息
- event scheduler运行一个event时产生的日志信息
- 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
显示错误日志路径
SHOW GLOBAL VARIABLES LIKE 'log_error'慢查询日志
慢查询日志：记录执行查询时长超出指定时长的操作
慢查询相关变量
slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
slow_query_log_file=HOSTNAME-slow.log #慢查询日志文件
long_query_time=N #慢查询的阀值，单位秒,默认为10s
log_queries_not_using_indexes=ON #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录慢查询分析工具 mysqldumpslow
备份
备份类型
全备份：备份整个数据库
增量备份：仅备份从最近一次完全备份或增量备份以来变化的数据，备份较快，还原复杂
冷备：读、写操作均不可进行，数据库停止服务
温备：读操作可执行；但写操作不可执行
热备：读、写操作均可执行
备份内容
- 数据
- 二进制日志、InnoDB的事务日志
- 用户帐号，权限设置，程序代码（存储过程、函数、触发器、事件调度器）
- 服务器的配置文件
常用的备份工具
- cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
- LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
- mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
- xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份 #(重要)
- MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
- mysqlbackup：热备份， MySQL企业版提供，需要付费
mysqldump
mysqldump [options] [db_name [tbl_name ...]]
mysqldump [OPTIONS] database [tables] #支持指定数据库和指定多表的备份，但数据库本身定义不备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份，包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS] #备份所有数据库，包含数据库本身定义也会备份这是官方的站点：https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
OPTION
-A, --all-databases #备份所有数据库，含create database
-B, --databases db_name… #指定备份的数据库，包括create database语句
-E, --events：#备份相关的所有event scheduler
-R, --routines：#备份所有存储过程和自定义函数
--triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集
--master-data[=#]： #此选项须启用二进制日志
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用
#2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用
#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact #去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data #只备份表结构,不备份数据,即只备份create table
-t, --no-create-info #只备份数据,不备份表结构,即不备份create table
-n,--no-create-db #不备份create database，可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force #忽略SQL错误，继续执行
--hex-blob #使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick #不缓存查询，直接输出，加快备份速度实际环境中的备份命令
#innodb
mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8mb4 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql
#MyISAM
mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges --triggers --default-character-set=utf8mb4 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sqlxtrabackup
说明：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
xtrabackup 安装
centos7的epel源有此安装包
yum install -y percona-xtrabackup#配置yum源
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
#启用存储库
percona-release enable-only tools release
#安装
yum install percona-xtrabackup-80xtrabackup备份
Usage: [ xtrabackup [--defaults-file=#] --backup
| xtrabackup [--defaults-file=#] --prepare ] [OPTIONS]
--user：#该选项表示备份账号
--password：#该选项表示备份的密码
--host：#该选项表示备份数据库的地址
--databases：#该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；
如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。
如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表
--defaults-file：#该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
--incremental：#该选项表示创建一个增量备份，需要指定--incremental-basedir
--incremental-basedir：#该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用
--incremental-dir：#该选项表示还原时增量备份的目录
--include=name：#指定表名，格式：databasename.tablename备份流程演示
备份
1.安装xtrabackup包
yum -y install percona-xtrabackup-80
2.在原主机做完全备份到/backup
mkdir /backup
xtrabackup -uroot --backup --target-dir=/backup/base
还原
1.预准备：确保数据一致，提交完成的事务，回滚未完成的事务
xtrabackup --prepare --target-dir=/backup/base
2.复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
xtrabackup --copy-back --target-dir=/backup/base
3.还原属性
chown -R mysql:mysql /data/mysql
4.启动服务
systemctl start mysqld备份
1.创建备份文件夹
mkdir /backup/
2.创建全备份
xtrabackup --backup --target-dir=/backup/base
3.创建第一次增量备份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
4.创建第二次增量备份
xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
还原
1.预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2.合并第1次增量备份到完全备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
3.合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
xtrabackup --prepare --target-dir=/backup/base --incrementaldir=/backup/inc2
4.复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
xtrabackup --copy-back --target-dir=/backup/base
5.还原属性
chown -R mysql:mysql /var/lib/mysql
6.启动服务
systemctl start mysqldMySQL架构
主从
主从同步过程
- 主服务器将自己的操作记录写入二进制文件
- 从服务器读取主服务器的二进制文件，并将其写入自己的中继日志
- 从服务器将中继日志内的操作执行一下
主从复制相关线程
主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
主从实现
本次的主从基于mysql8.0进行构建，版本和安装方式不同的话，配置文件的路径有可能不相同
配置：
- 两台机器同时安装mysql，确保安装的相同的版本
- 在主服务器的/etc/my.cnf.d/mysql-server.cnf进行修改
[mysqld]
server-id = 18
log-bin- 修改从服务器的配置文件
[mysqld]
server-id = 28
read-only = 1- 启动主服务器，从服务器，连接主服务器上的mysql，查看二进制日志的位置，然后创建一个具有复制权限的用户
SHOW MASTER STATUS;
CREATE USER muser@'%' identified by '111111';
GRANT replication slave on *.* to muser@'%';- 连接从服务器，确定连接主服务器
CHANGE MASTER TO MASTER_HOST='10.0.0.18',
MASTER_USER='muser',
MASTER_PASSWORD='111111',
MASTER_PORT=3306,
MASTER_LOG_FILE='thinkmo-bin.000002',
MASTER_LOG_POS=156;- 在从服务器启动slave进程
start slave;- 查看slave进程运行状态
show slave status;在从节点清除信息
#清除信息之前请先停止slave
RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和PASSWORD 等复制错误解决方法
#系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors = ALL级联
Slave1服务器的配置
[mysqld]
server-id = 1
log_bin
log_replica_updates
read_only注意：从 MySQL 8.0.26 开始，使用 log_replica_updates 代替 log_slave_updates ，该版本已弃用。在 MySQL 8.0.26 之前的版本中，使用 log_slave_updates.
log_replica_updates 指定从服务器将复制源服务器接收的更新记录到从服务器自己的二进制日志中。主服务器和主从架构中主服务器配置是一致的，从服务器的配置也是一致的，只有中间的中继服务器配置不一致。
双主架构
主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2半同步复制
默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失
- 异步：主服务器将二进制日志发送后不验证从库是否接受
- 同步：主服务器将二进制日志发送后等到所有从服务器回复确认收到消息后才会给用户回复
- 半同步：主服务器将二进制日志发送后有一台从服务器回复确认收到，主服务器就会给用户回复
半同步复制配置
[mysqld]
server-id=8
log-bin
plugin-load-add = semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000 #设置3s内无法同步，也将返回成功信息给客户端mysql中间件
高可用
可用级别：
99% 99.9% 99.99% 99.999%
业界里面用MHA最多，PXC、ORC其次
MHA：Master High Availability，对主节点进行监控，可实现自动故障转移至其它从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，出于机器成本的考虑，淘宝进行了改造，目前淘宝TMHA已经支持一主一从
Galera Cluster：wsrep(MySQL extended with the Write Set Replication) 通过wsrep协议在全局实现复制；任何一节点都可读写，不需要主从复制，实现多主读写
GR（Group Replication）：MySQL官方提供的组复制技术(MySQL 5.7.17引入的技术)，基于原生复制技术Paxos算法，实现了多主更新，复制组由多个server成员构成，组中的每个server可独立地执行事务，但所有读写事务只在冲突检测成功后才会提交
MHA工作原理
- 从宕机崩溃的master保存二进制日志事件（binlog events）
- 识别含有最新更新的slave
- 应用差异的中继日志（relay log）到其他的slave
- 应用从master保存的二进制日志事件（binlog events）
- 提升一个slave为新的master
- 使其他的slave连接新的master进行复制
注意：
为了尽可能的减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL的半同步复制
MHA软件
MHA软件由两部分组成，Manager工具包和Node工具包
Manager工具包主要包括以下几个工具：
masterha_check_ssh 检查MHA的SSH配置状况
masterha_check_repl 检查MySQL复制状况
masterha_manger 启动MHA
masterha_check_status 检测当前MHA运行状态
masterha_master_monitor 检测master是否宕机
masterha_master_switch 故障转移（自动或手动）
masterha_conf_host 添加或删除配置的server信息
masterha_stop --conf=app1.cnf 停止MHA
masterha_secondary_check 两个或多个网络线路检查MySQL主服务器的可用Node工具包：这些工具通常由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具：
save_binary_logs #保存和复制master的二进制日志
apply_diff_relay_logs #识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog #去除不必要的ROLLBACK事件（MHA已不再使用此工具）
purge_relay_logs #清除中继日志（不会阻塞SQL线程）MHA自定义扩展：
secondary_check_script #通过多条网络路由检测master的可用性
master_ip_ailover_script #更新Application使用的masterip
shutdown_script #强制关闭master节点
report_script #发送报告
init_conf_load_script #加载初始配置参数
master_ip_online_change_script #更新master节点ip地址- global配置，为各application提供默认配置，默认文件路径 /etc/masterha_default.cnf
- application配置：为每个主从复制集群
MHA构建实战
本次构建是基于Centos7上Mysql5.7进行构建
10.0.0.17 MHA的manager节点
10.0.0.27 MHA的node节点，主从复制的主服务器
10.0.0.37 MHA的node节点，主从复制的从服务器
10.0.0.47 MHA的node节点，主从复制的从服务器构建一主两从的主从复制集群
安装mysql
yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm yum install -y yum-utils yum-config-manager --enable mysql57-community yum-config-manager --disable mysql80-community yum install -y mysql-community-server启动mysql，更换初始密码
systemctl start mysqld #获取初始密码 grep 'password' /var/log/mysqld.log mysql -p'初始密码' 修改连接密码 alter user root@localhost identified by 'Xxthinkmo1.@';修改配置文件
#master server-id=27 log-bin bind-address=0.0.0.0 validate_password_policy=0 validate_password_length=1 #slave1 server-id=37 log-bin bind-address=0.0.0.0 validate_password_policy=0 validate_password_length=1 #slave2 server-id=47 log-bin bind-address=0.0.0.0 validate_password_policy=0 validate_password_length=1启动mysql server，并且更改密码
systemctl restart mysqld mysql -p'Xxthinkmo1.@' -e 'alter user root@localhost identified by "111111"; '在主服务器查看二进制日志，创建复制用户
mysql -p'111111' show master status; create user muser@'%' identified by '111111'; grant replication slave on *.* to muser;在从服务器上构建主从
change master to master_host='10.0.0.27', master_user='muser', master_password='111111', master_log_file='master-bin.000001', master_log_pos=398; #启动主从 start slave; #查看主从状态 show slave status\G;
在三个node节点生成公钥信息，并且将公钥信息发送到管理节点
#此操作在manager，master，slave1，slave2都要进行 ssh-keygen ssh-copy-id 10.0.0.17 #在manager节点使用scp完成各个节点的认证文件同步 scp .ssh/authorized_keys 10.0.0.27:/root/.ssh/authorized_keys scp .ssh/authorized_keys 10.0.0.37:/root/.ssh/authorized_keys scp .ssh/authorized_keys 10.0.0.47:/root/.ssh/authorized_keys安装相应的rpm包
#manager节点安装manager包和node包 yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm #一主两从一共三个node节点安装node包 yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm在管理节点创建manager需要的配置文件
#创建配置文件的文件夹 mkdir /etc/mha mkdir /var/log/mha #创建配置文件 vim /etc/mha/mha-manager.conf [server default] user=mha password=111111 manager_log=/var/log/mha/mha.log manager_workdir=/var/log/mha/ master_binlog_dir=/var/lib/mysql ssh_user=root repl_user=muser repl_password=111111 [server1] hostname=10.0.0.27 port=3306 [server2] hostname=10.0.0.37 port=3306 [server3] hostname=10.0.0.47 port=3306在数据库之中为MHA创建用户
#直接在主库中操作，数据可以同步至从库 grant all privileges on *.* to mha@'%' identified by '111111';通过Manager自带脚本masterha_check_ssh检查各节点免密登录是否正常
masterha_check_ssh --conf=/etc/mha/mha-manager.conf通过masterha_check_repl脚本检查主从复制是否正常
masterha_check_repl --conf=/etc/mha/mha-manager.conf启动MHA
masterha_manager --conf=/etc/mha/mha-manager.conf --remove_dead_master_conf
MHA构架完成
额外设置VIP漂移
在[server default]配置块下面添加
master_ip_failover_script=/usr/local/bin/master_ip_failover
#构建VIP漂移文件，脚本可以从manager源码包samples/script目录中复制，然后修改一些VIP配置等信息，这里已经是配置完成的
vim /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '10.0.0.100/24';#设置Virtual IP
my $gateway = '10.0.0.2';#网关Gateway IP
my $interface = 'eth0'; #指定VIP所在网卡
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
#添加执行权限
chmod +x /usr/local/bin/master_ip_failover分库分表
mycat介绍
在整个IT系统架构中，数据库是非常重要，通常又是访问压力较大的一个服务，除了在程序开发的本身做优化，如：SQL语句优化、代码优化，数据库的处理本身优化也是非常重要的。主从、热备、分表分库等都是系统发展迟早会遇到的技术问题问题。Mycat是一个广受好评的数据库中间件，已经在很多产品上进行使用了。
Mycat是一个开源的分布式数据库系统，是一个实现了MySQL协议的服务器，前端用户可以把它看作是一个数据库代理（类似于Mysql Proxy），用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里。Mycat发展到目前的版本，已经不是一个单纯的MySQL代理了，它的后端可以支持MySQL、SQLServer、Oracle、DB2、PostgreSQL等主流数据库，也支持MongoDB这种新型NoSQL方式的存储，未来还会支持更多类型的存储。而在最终用户看来，无论是那种存储方式，在MyCat里，都是一个传统的数据库表，支持标准的SQL语句进行数据的操作，这样一来，对前端业务系统来说，可以大幅降低开发难度，提升开发速度。这是mycat的官方站点http://www.mycat.org.cn/
Mycat 可以简单概括为
- 一个彻底开源的，面向企业应用开发的大数据库集群
- 支持事务、ACID、可以替代MySQL的加强版数据库
- 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
- 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
- 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
- 一个新颖的数据库中间件产品
mycat使用环境
Mycat适用的场景很丰富，以下是几个典型的应用场景
- 单纯的读写分离，此时配置最为简单，支持读写分离，主从切换
- 分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片
- 多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化
- 报表系统，借助于Mycat的分表能力，处理大规模报表的统计
- 替代Hbase，分析大数据
- 作为海量数据实时查询的一种简单有效方案，比如100亿条频繁查询的记录需要在3秒内查询出来结果，除了基于主键的查询，还可能存在范围查询或其他属性查询，此时Mycat可能是最简单有效的选择
Mycat分⽚策略
- ⽔平分⽚：⼀个表格的数据分割到多个节点上，按照⾏分隔。
- 垂直分⽚：⼀个数据库中多个表格A，B，C，A存储到节点1上，B存储到节点2上，C存储到节点3上。
Mycat清单详解
mycat安装目录结构：
- bin：mycat命令，启动、重启、停止等
- catlet：catlet为Mycat的一个扩展功能
- conf：Mycat 配置信息,重点关注
- lib：Mycat引用的jar包，Mycat是java开发的
- logs：日志文件，包括Mycat启动的日志和运行的日志
- version.txt：mycat版本说明
logs目录:
- wrapper.log mycat启动日志
- mycat.log mycat详细工作日志
Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：
- server.xml #存放Mycat软件本身相关的配置文件，比如：连接Mycat的用户，密码，数据库名称等
user #用户配置节点
name #客户端登录MyCAT的用户名，也就是客户端用来连接Mycat的用户名。
password #客户端登录MyCAT的密码
schemas #数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如:db1,db2
privileges #配置用户针对表的增删改查的权限
readOnly #mycat逻辑库所具有的权限。true为只读，false为读写都有，默认为false
注意：
server.xml文件里登录mycat的用户名和密码可以任意定义，这个账号和密码是为客户机登录mycat时使用的账号信息逻辑库名(如上面的TESTDB，也就是登录mycat后显示的库名，切换这个库之后，显示的就是代理的真实mysql数据库的表)要在schema.xml里面也定义，否则会导致mycat服务启动失败！
这里只定义了一个标签，所以把多余的都注释了。如果定义多个标签，即设置多个连接mycat的用户名和密码，那么就需要在schema.xml文件中定义多个对应的库！- schema.xml schema.xml是最主要的配置项，此文件关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置的.MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物理服务器，此文件就是用来连接MySQL服务器的
参数说明
schema 数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应
dataNode 分片信息，也就是分库相关配置
dataHost 物理数据库，真正存储数据的数据库
配置说明
name属性唯一标识dataHost标签，供上层的标签使用。
maxCon属性指定每个读写实例连接池的最大连接。也就是说，标签内嵌套的writeHost、readHost标签都会使用这个属性的值来实例化出连接池的最大连接数
minCon属性指定每个读写实例连接池的最小连接，初始化连接池的大小每个节点的属性逐一说明
节点说明
sechma
name 逻辑数据库名，与server.xml中的schema对应
checkSQLschema 数据库前缀相关设置，这里为false
sqlMaxLimit select 时默认的limit，避免查询全表
table
name 表名，物理数据库中表名
dataNode 表存储到哪些节点，多个节点用逗号分隔。节点为下文dataNode设置的name
primaryKey 主键字段名，自动生成主键时需要设置
autoIncrement 是否自增
rule 分片规则名，具体规则下文rule详细介绍
dataNode
name 节点名，与table中dataNode对应
datahost 物理数据库名，与datahost中name对应
database 物理数据库中数据库名
dataHost
name 物理数据库名，与dataNode中dataHost对应
balance 均衡负载的方式
writeType 写入方式
dbType 数据库类型
heartbeat 心跳检测语句，注意语句结尾的分号要加balance 负载均衡类型，目前的取值有 4 种：
- balance="0"：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上,即读请求仅发送到writeHost上
- balance="1"：一般用此模式，读请求随机分发到当前writeHost对应的readHost和standby的writeHost上。即全部的readHost与stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1 ->S1 ， M2->S2，并且 M1 与 M2 互为主备)，正常情况下， M2,S1, S2 都参与 select语句的负载均衡
- balance="2"：读请求随机分发到当前dataHost内所有的writeHost和readHost上。即所有读操作都随机的在writeHost、 readhost 上分发
- balance="3"：读请求随机分发到当前writeHost对应的readHost上。即所有读请求随机的分发到wiriterHost 对应的 readhost 执行, writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有，1.3 没有
writeHost和readHost 标签
这两个标签都指定后端数据库的相关配置给mycat，用于实例化后端连接池。唯一不同的是：writeHost指定写实例、readHost指定读实例，组着这些读写实例来满足系统的要求。在一个dataHost内可以定义多个writeHost和readHost。但是，如果writeHost指定的后端数据库宕机，那么这个writeHost绑定的所有readHost都将不可用。另一方面，由于这个writeHost宕机系统会自动的检测到，并切换到备用的writeHost上去
注意：
Mycat主从分离只是在读的时候做了处理，写入数据的时候，只会写入到writehost，需要通过mycat的
主从复制将数据复制到readhost
schema.xml文件中有三点需要注意：balance="1"，writeType="0" ,switchType="1" schema.xml中的balance的取值决定了负载均衡对非事务内的读操作的处理。
- rule.xml Mycat分片（分库分表）规则配置文件,记录分片规则列表、使用方法等
Mycat构建实战
构建主从，需要注意下方第5步中的用户需要在构建完主从时进行创建
在Mycat所在主机上下载java
yum -y install java #确认安装成功 java -version安装mycat
mkdir /data tar -xvf Mycat-server-1.6.7.5-release-20200422133810-linux.tar.gz -C /data cd /data/mycat/ mkdir logs修改server.xml配置文件
vim conf/server.xml <user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">TESTDB</property> <property name="defaultSchema">TESTDB</property> #注意这里的schema的参数对应的之后schema.xml文件中的参数修改schema.xml配置文件
vim conf/schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" randomDataNode="dn1" dataNode="dn1" > </schema> <dataNode name="dn1" dataHost="localhost1" database="db" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostw" url="10.0.0.28:3306" user="mycat" password="111111"> <readHost host="hostr" url="10.0.0.38:3306" user="mycat" password="111111"/> </writeHost> </dataHost> </mycat:schema>在主服务器上创建Mycat连接的用户，以及绑定的数据库
CREATE USER mycat@'%' identified by '111111'; GRANT all on *.* to mycat@'%'; create database db;将mycat的路径添加为环境变量
echo "PATH=${PATH}:/data/mycat/bin/" >> /etc/profile source /etc/profile启动mycat
mycat start连接Mycat
mysql -uroot -p123456 -h127.0.0.1 -P8066
中间件proxysql
proxysql配置说明
官方说明：https://proxysql.com/documentation/
安装说明：https://proxysql.com/documentation/installing-proxysql/
配置文件说明：
ProxySQL组成
- 服务脚本：/etc/init.d/proxysql
- 配置文件：/etc/proxysql.cnf
- 主程序：/usr/bin/proxysql
启动ProxySQL：
启动后会监听两个默认端口
- 6032：ProxySQL的管理端口
- 6033：ProxySQL对外提供服务的端口
使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin：
mysql -uadmin -padmin -P6032 -h127.0.0.1
数据库说明：
main 是默认的”数据库”名，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载
disk 是持久化到硬盘的配置，sqlite数据文件
stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间，等等
monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查
说明：
- 在main和monitor数据库中的表， runtime开头的是运行时的配置，不能修改，只能修改非runtime表
- 修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效
- 执行save … to disk 才将配置持久化保存到磁盘，即保存在proxysql.db文件中
- global_variables 有许多变量可以设置，其中就包括监听的端口、管理账号等
proxysql构建实战
构建主从
安装proxysql
yum localinstall -y proxysql-2.2.0-1-centos8.x86_64.rpm启动proxysql
systemctl start proxysql.service连接至proxysql，添加两台后端数据库
mysql -uadmin -padmin -P6032 -h127.0.0.1 insert into mysql_servers(hostgroup_id,hostname,port) values(10,'10.0.0.28',3306); insert into mysql_servers(hostgroup_id,hostname,port) values(10,'10.0.0.38',3306); load mysql servers to runtime; save mysql servers to disk;在主服务器上创建一个用户以便于proxysql来进行监视
create user monitor@'%' identified by '111111'; grant replication client on *.* to monitor@'%';连接每个节点的read_only值来自动调整主从节点是属于读组还是写组
set mysql-monitor_username='monitor'; set mysql-monitor_password='111111'; load mysql variables to runtime; save mysql variables to disk;查看监控
#查看监控连接是否正常的 (对connect指标的监控），如果connect_error的结果为NULL则表示正常 select * from mysql_server_connect_log; #查看监控心跳信息 (对ping指标的监控)： select * from mysql_server_ping_log;设置分组信息
#需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：
- writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20
insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values(10,20,"test"); #将mysql_replication_hostgroups表的修改加载到RUNTIME生效 load mysql servers to runtime; save mysql servers to disk; #Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组 select hostgroup_id,hostname,port,status,weight from mysql_servers;配置访问数据库的SQL 用户
#在master节点上创建访问用户 create user sqluser@'%' identified by '111111'; grant all on *.* to sqluser@'%'; #在ProxySQL配置，将用户sqluser添加到mysql_users表中， default_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库 insert into mysql_users(username,password,default_hostgroup) values('sqluser','111111',10); load mysql users to runtime; save mysql users to disk;在proxysql上配置路由规则，实现读写分离
与规则有关的表：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后支持插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，应路由到10的写组
insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1); load mysql query rules to runtime; save mysql query rules to disk; #注意：因ProxySQL根据rule_id顺序进行规则匹配，select ... for update规则的rule_id必须要小于普通的select规则的rule_id连接proxysql中间件
mysql -usqluser -p111111 -P6033 -h127.0.0.1通过通用日志可以查看是否完成读写分离
Mysql要求
基础规范
必须使用InnoDB存储引擎
解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高使用UTF8MB4字符集
解读：万国码，无需转码，无乱码风险，节省空间，支持表情包及生僻字数据表、数据字段必须加入中文注释
解读：N年后谁知道这个r1,r2,r3字段是干嘛的禁止使用存储过程、视图、触发器、Event
解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧！禁止存储大文件或者大照片
解读：为何要让数据库做它不擅长的事情?大文件和照片存储在文件系统，数据库里存URI多好。
命名规范
只允许使用内网域名，而不是ip连接数据库
线上环境、开发环境、测试环境数据库内网域名遵循命名规范
业务名称：xxx 线上环境：xxx.db 开发环境：xxx.rdb 测试环境：xxx.tdb 从库在名称后加-s标识，备库在名称后加-ss标识 线上从库：xxx-s.db 线上备库：xxx-sss.db库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用
库名与应用名称尽量一致，表名:t_业务名称_表的作用，主键名:pk_xxx，非唯一索引名:idx_xxx，唯一键索引名:uk_xxx
表设计规范
单实例表数目必须小于500
单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。 说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表单表列数目必须小于30
表必须有主键，例如自增主键
解读： a)主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用 b)主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率 c)无主键的表删除，在row模式的主从架构，会导致备库夯住禁止使用外键，如果有外键完整性约束，需要应用程序控制
解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先
字段设计规范
必须把字段定义为NOT NULL并且提供默认值
解读： a)null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化 b)null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性;同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多 c)null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识 d)对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!='shenjian'，如果存在name为null值的记录，查询结果就不会包含name为null值的记录禁止使用TEXT、BLOB类型
解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能禁止使用小数存储货币
必须使用varchar(20)存储手机号
解读： a)涉及到区号或者国家代号，可能出现+-() b)手机号会去做数学运算么? c)varchar可以支持模糊查询，例如：like“138%”禁止使用ENUM，可使用TINYINT代替
解读： a)增加新的ENUM值要做DDL操作 b)ENUM的内部实际存储就是整数，你以为自己定义的是字符串?
索引设计规范
单表索引建议控制在5个以内
单索引字段数不允许超过5个
解读：字段超过5个时，实际已经起不到有效过滤数据的作用了禁止在更新十分频繁、区分度不高的属性上建立索引
解读： a)更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能 b)“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似建立组合索引，必须把区分度高的字段放在前面
SQL使用规范
禁止使用SELECT *，只获取必要的字段，需要显示说明列属性
解读： a)读取不需要的列会增加CPU、IO、NET消耗 b)不能有效的利用覆盖索引 c)使用SELECT *容易在增加或者删除字段后出现程序BUG禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性
禁止使用属性隐式转换
解读：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引，猜猜为什么?(这个线上问题不止出现过一次)禁止在WHERE条件的属性上使用函数或者表达式
解读：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描 正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')禁止负向查询，以及%开头的模糊查询
解读： a)负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描 b)%开头的模糊查询，会导致全表扫描禁止大表使用JOIN查询，禁止大表使用子查询
禁止使用OR条件，必须改为IN查询
解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢?应用程序必须捕获SQL异常，并有相应处理