MySQL
表操作
创建表
NOT NULL：不允许为 NULL
DEFAULT NULL：默认为 NULL
AUTO_INCREMENT：自增
COMMENT '注释'：添加注释
PRIMARY KEY (`id`)：设置主键
utf8mb4：设置对应编码
CREATE TABLE `table_name` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键自增',
`nick_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '昵称',
`name` char(4) DEFAULT NULL COMMENT '姓名',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`rule` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '规则',
`deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除（1：已删除）',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='示例表';修改表名
ALERT TABLE table_name RENAME TO new_table_name;删除表
-- 如果 table_name 表存在的话就删除
DROP TABLE if exists table_name;修改排序规则
-- 修改数据库排序规则
ALTER DATABASE BASE CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
-- 修改表的默认排序规则，表中字段排序规则不变
ALTER TABLE table_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 修改表和表中字段的排序规则
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 修改表中某个字段的排序规则
ALTER TABLE table_name MODIFY name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci default '' null comment '姓名' ;主键操作
添加主键
-- 表名 表名(`主键的字段`)
ALTER TABLE table_name ADD PRIMARY KEY table_name(`id`);
-- 设置自增
ALTER TABLE table_name MODIFY id int AUTO_INCREMENT;删除主键
-- 如果主键自增，先去掉自增
ALTER TABLE table_name MODIFY id int;
-- 去掉主键
ALTER TABLE table_name DROP PRIMARY KEY;字段操作
添加字段
ALTER TABLE table_name ADD cover_url varchar(255) COLLATE utf8mb4_general_ci COMMENT '封面图片';
ALTER TABLE table_name ADD relation_id int COMMENT '关联id';
ALTER TABLE table_name ADD env tinyint(1) COMMENT '环境（1-测试 2-生产）';
ALTER TABLE table_name ADD update_time datetime COMMENT '修改时间';删除字段
ALTER TABLE table_name DROP COLUMN cover_url;修改字段
如果修改字段有数据，如果可以转换直接修改就可以，如果不行，只能先改名，再新增一个字段，将数据导入进去
-- 修改字段名字 旧字段名 新字段名 新数据类型
ALTER TABLE table_name CHANGE cover_url new_cover_url varchar(255);
-- 修改字段数据类型 字段名 字段类型
ALTER TABLE table_name MODIFY cover_url varchar(256);索引
-- 创建普通索引 索引名 表名 索引字段
CREATE INDEX index_env_cover ON table_name (env, cover_url);
ALTER TABLE table_name ADD INDEX index_env (env);
-- 删除索引
ALTER table table_name DROP INDEX index_env_cover;
-- 创建唯一索引 索引名 表名 索引字段
CREATE UNIQUE INDEX index_env ON table_name (env);表分区
需要先移除索引
表分区类型：
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
LIST分区
# 建表后分区
# 去掉表中主键，新增联合主键
# 如果主键不是自增，可以
ALTER TABLE table_name MODIFY id int;
ALTER TABLE table_name DROP primary key;
ALTER TABLE table_name ADD PRIMARY KEY table_name(`id`, `env`);
ALTER TABLE table_name PARTITION BY LIST COLUMNS(env)
(
PARTITION env_0 VALUES IN(0),
PARTITION env_1 VALUES IN(1)
);
# 创建表时分区
CREATE TABLE `table_name` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键自增',
`env` tinyint(1) COMMENT '环境（1-测试 2-生产）',
PRIMARY KEY (`id`,`env`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='示例表'
PARTITION BY LIST(`env`) (
PARTITION env_0 values in(0),
PARTITION env_1 values in(1)
);
# 查看查询语句是否命中分区
explain select * from table_name where env=0;
# 增加分区
ALTER TABLE table_name ADD PARTITION (PARTITION env_2 VALUES in(2));
# 删除分区
ALERT TABLE table_name DROP PARTITION env_0;RANGE 分区（未做测试，常用List分区）
create table employees (
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int not null,
store_id int not null
) partition by range (store_id) (
partition p0 values less than (6),
partition p1 values less than (11),
partition p2 values less than (16),
partition p3 values less than (21)，
partition p3 values less than maxvalue
);安装
1、准备安装包
将提前准备好的mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz安装包，将其直接放在root目录下
2、卸载系统自带的 MARIADB
- 查询已经安装的
Mariadb安装包
rpm -qa | grep mariadb- 如果有的话都卸载掉
yum -y remove mariadb-server-5.5.56-2.el7.x86_64
yum -y remove mariadb-5.5.56-2.el7.x86_64
yum -y remove mariadb-devel-5.5.56-2.el7.x86_64
yum -y remove mariadb-libs-5.5.56-2.el7.x86_643、解压 MySQL 安装包
将MySQL安装包解压到/usr/local目录，并重命名为mysql
tar -zxvf /root/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
mv mysql-5.7.30-linux-glibc2.12-x86_64 mysql4、创建 MySQL 用户和用户组
groupadd mysql
useradd -g mysql mysql5、修改 MySQL 目录的归属用户
# /usr/local/mysql 目录下执行
chown -R mysql:mysql ./6、准备 MySQL 的配置文件
- 在
/etc⽬录下新建my.cnf⽂件
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock
[mysqld]
skip-name-resolve
#设置3306端⼝
port = 3306
socket=/var/lib/mysql/mysql.sock
# 设置mysql的安装⽬录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放⽬录
datadir=/usr/local/mysql/data
# 允许最⼤连接数
max_connections=200
# 服务端使⽤的字符集默认为8⽐特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使⽤的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M- 创建
/usr/local/mysql/data和/var/lib/mysql目录
# 新建`/usr/local/mysql/data`⽬录
mkdir /usr/local/mysql/data
# 新建 /var/lib/mysql 目录，并修改权限
mkdir /var/lib/mysql
chmod 777 /var/lib/mysql7、安装 MySQL
记住安装后打印出来的 root 密码，后面首次登录需要使用。没记住也没关系，后面有方式免密登录去修改密码。
cd /usr/local/mysql
./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data8、复制启动脚本到资源目录
[root@localhost mysql]# cp ./support-files/mysql.server /etc/init.d/mysqld- 修改
/etc/init.d/mysqld，修改其basedir和datadir为实际对应⽬录
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data9、设置MYSQL系统服务并开启⾃启
- ⾸先增加
mysqld服务控制脚本执⾏权限
chmod +x /etc/init.d/mysqld- 同时将
mysqld服务加⼊到系统服务
chkconfig --add mysqld-检查mysqld服务是否已经⽣效即可
chkconfig --list mysqld
这样就表明 mysqld 服务已经⽣效了，在2、3、4、5运⾏级别随系统启动⽽⾃动启动，以后可以直接使 ⽤ service 命令控制 mysql 的启停。
10、启动 mysqld
service mysqld start11、将 mysql 的 bin ⽬录加⼊ PATH 环境变量
# 编辑 ~/.bash_profile ⽂件
vi ~/.bash_profile
# 在⽂件末尾处追加如下信息:
export PATH=$PATH:/usr/local/mysql/bin
# 最后执⾏如下命令使环境变量⽣效
source ~/.bash_profile12、首次登录 mysql
以 root 账户登录 mysql ，使⽤上⽂安装完成提示的密码进⾏登⼊
mysql -u root -p13、修改 root 账户密码
mysql>alter user user() identified by "111111";
mysql>flush privileges;14、设置远程主机登录
mysql> use mysql;
mysql> update user set user.Host='%' where user.User='root';
mysql> flush privileges;15、重置密码
注意区分半本，这是是 5.7 版本的修改方式
5.7版本下的mysql数据库下已经没有password这个字段了，password字段改成了authentication_string
- 1、跳过 MySQL 密码认证
# 编辑配置文件
vi /etc/my.cnf
# [mysqld]后面任意一行添加“skip-grant-tables”用来跳过密码验证的过程
skip-grant-tables- 2、重启 MySQL
/etc/init.d/mysqld restart3、重启之后输入 mysql 即可进入 mysql
4、修改密码
# 切换到 mysql 数据库
mysql> use mysql;
# 修改密码
mysql> update mysql.user set authentication_string=password('123456') where user='root';
# 立即生效
mysql> flush privileges;
# 退出
mysql> quit- 5、去掉配置文件的免密
数据库压测指标
基于工具模拟一个系统每秒发出 1000 个请求到数据库上，观察他的 CPU负载、磁盘IO负载、网络IO负载、内存负载，然后看数据库能否每秒处理掉这 1000 个请求，还是只能处理 500 个请求，这个过程就是压测
QPS（Query Per Second）：数据库每秒可以处理多少个请求
TPS（Transaction Per Second）：每秒可以处理的事务量，衡量的是一个数据库每秒处理完事务的数量
IO 相关的压测性能指标
- IOPS：指的是机器的随机 IO 并发处理能力，比如机器可以达到 200 IOPS，意思就是说每秒可以执行 200 个随机 IO 读写请求
这个指标很关键，你在内存中更新的脏数据，最后都由后台 IO 线程在不确定的时间，刷回磁盘里去，这就是随机 IO 的过程，如果说 IOPS 指标太低了，那么会导致内存里的数据刷会磁盘的效率就会不高
- 吞吐量：指的是机器的磁盘存储每秒可以读写多少字节的数据量
这个指标也很关键，执行 SQL 语句和提交事务的时候，会大量的写 redo log 之类的日志，这些日志都会直接写入磁盘文件。
一台机器的存储每秒可以读写多少自己的数据量，就决定了他每秒可以把多少 redo log 之类的日志写入到磁盘里去。一般 redo log 之类的日志，都是对磁盘文件顺序写入的，不是随机的读写，那么一般普通磁盘的顺序写入的吞吐量每秒都可以达到 200MB 左右，所以通常而言，机器的磁盘吞吐量都是足够承载高并发请求的
- latency：指的是往磁盘里写入一条数据的延迟
这个指标同样重要，执行 SQL 语句和提交事务的时候，都需要顺序写 redo log 磁盘文件，所以此时你写一条日志到磁盘文件里，是延迟 1ms 还是延迟 100us，这就对你数据库的 SQL 语句执行性能是有影响的
一般来说，你的磁盘读写延迟越低，那么你的数据库性能就越高，你执行每个 SQL 语句和事务的速度就会越快
压测时要关注的其他性能指标
- CPU负载
CPU负载是一个很重要的性能指标，假设你数据库压测到了每秒处理 3000 请求了，可能其他性能指标都还正常，但是此时 CPU 负载特别高，那么也说明你的数据库不能继续往下压测更高的 QPS 了，否则 CPU 是吃不消的
- 网络负载
这个是主要看你的机器宽带情况，再压测到一定的 QPS 和 TPS 的时候，每秒中机器的网卡会输入多少 MB 数据，会输出多少 MB 数据。假设你的网络宽带每秒对多传输 100MB 的数据，可能你的 QPS 到 1000 的时候，网卡就打满了，已经每秒都传输 100MB 的数据库，即使其他指标都还算正常，但是你也不能继续压测了
- 内存负载
这个是看压测到一定情况下的时候，你的机器内存耗费了多少，如果说机器的内存耗费过高了，说明也不能继续压测下去
如何查看负载情况
CPU、内存 负载情况
最常用的监测linux机器性能的命令，top
# 当前时间 已经运行了70天3:16 当前5个用户 cpu 负载情况 1分钟 5分钟 15分钟
top - 22:36:02 up 70 days, 3:16, 5 users, load average: 0.53, 3.30, 3.12
Tasks: 92 total, 3 running, 89 sleeping, 0 stopped, 0 zombie
%Cpu(s): 1.3 us, 1.0 sy, 0.0 ni, 97.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
# 总共1.79个G 0.06个G空闲 0.75个G使用 0.97个G用作os内核的缓冲区
KiB Mem : 1882052 total, 66176 free, 790576 used, 1025300 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 920856 avail Mem如果你是 1 核的服务器（1个物理核等于2个虚拟核，云服务器虚拟核），CPU 负载是 1，那就说明 1 核 CPU 已经跑满了，如果负载是 2，CPU 很繁忙，很多进程可能一直在等待 CPU 去执行自己的任务
如果内存使用率在 80 % 以内，基本还能接受，在正常范围内。但是如果你机器的内存使用率到了70%-80%，说明有点危险，就不要继续增加压测的线程数量和 QPS 了
磁盘 IO 情况
# 安装性能检测 dstat
yum install -y dstat# 使用 dstat -d 命令
dstat -d
-dsk/total-
read writ
103k 16k存储的 IO 吞吐量是每秒钟 103kb 的数据，每秒写入是 16kb 的数据，普通机械硬盘可以做到每秒钟上百 MB 的读写数据量
# 使用 dstat -r
dstat -r
--io/total-
read writ
0.25 31.9
0 0读 IOPS 和写 IOPS 分别是多少，也就是说随机磁盘读取每秒钟多少次，随机磁盘写入每秒钟多少次，随机磁盘读写每秒在两三百次都是可以承受的
如果磁盘 IO 吞吐量太高了，都达到每秒上百 MB 了，或者随机磁盘读写每秒都到极限的两三百次了，此时就不要继续增加线程数量了，否则磁盘 IO 负载太高了
网卡的流量情况
# 使用 dstat -n
dstat -n
-net/total-
recv send
16k 17k每秒钟网卡接收流量有多少 kb，每秒钟通过网卡发送出去的流量有多少 kb。通常来说，机器使用的千兆网卡，那么每秒钟网卡的总流量也就在 100MB 左右，甚至更低一些
如果网卡传输流量已经达到了极限值了，此时你再怎么提高 sysbench 线程数量，数据库的 QPS 也上不去，因为这台机器每秒钟无法通过网卡传输更多的数据了
数据库压测
一台4核8G的机器如果每秒扛 500+ 的请求，那么他的 CPU 负载就已经很高了，基本上最多也就扛下每秒 1000+ 的请求，而且那个时候 CPU 负载基本会打满，机器有挂掉的风险
sysbench
数据库压测工具，这个工具可以自动帮你在数据库里构造出大量的数据，接着可以模拟几千个线程并发的访问你的数据，模拟各种各样 SQL 语句来访问你的数据库，包括模拟出各种事务提交到你的数据库里去，甚至可以模拟出几十万的 TPS 去压测你的数据库。
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
sysbench --version基于 sysbench 构造测试表和测试数据
数据库：test_db，账号：test_user，密码：test_user
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare--db-driver=mysql：基于 mysql 的驱动去连接 mysql 数据库，如果是其他数据库，换成其他数据库的驱动
--time=300：连续访问 300 秒
--threads=10：用 10 个线程模拟并发访问
--report-interval=1：每隔 1 秒输出一下压测情况
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user：数据库的ip、端口和账号、密码
--mysql-db=test_db --tables=20 --table_size=1000000：在 test_db 这个库里面，构造 20 个测试表，每个测试表里构造 100 万条测试数据，测试表的名字回事类似于 sbtest1，sbtest2 这样
oltp_read_write：执行 oltp 数据库的读写测试
--db-ps-mode=disable：禁止 ps 模式
最后的 prepare，意思是参照这个命令的设置去构造出来我们需要的数据库里的数据，他会自动创建 20 个测试表，每个表里创建 100 万条测试数据
对数据库进行全方位测试
注意：命令中最后不是 prepare 而是 run 了，就是运行压测
- 测试数据库的综合性读写 TPS
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run- 测试数据库的只读性能，使用的是
oltp_read_only模式（命令行中的oltp_read_write变成oltp_read_only）
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_only --db-ps-mode=disable run- 测试数据库的删除性能，使用的是
oltp_delete模式
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_delete --db-ps-mode=disable run- 测试数据库的更新索引字段的性能，使用
oltp_update_index模式
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_index --db-ps-mode=disable run- 测试数据库的更新非索引字段的性能，使用
oltp_update_non_index模式
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_non_index --db-ps-mode=disable run- 测试数据库的插入性能，使用
oltp_insert模式
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_insert --db-ps-mode=disable run- 测试数据库的写入性能，使用
oltp_write_only模式
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_write_only --db-ps-mode=disable run使用上面命令，sysbench 工具会根据你的指令构造出各种各样的 SQL 语句去更新或者查询你的 20 张测试表里的数据，同时检测出你的数据库的压测性能指标，最后完成压测之后，可以执行下面的 cleanup 命令，清理数据
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup压测结果分析
按照上面的命令，我们让他每隔 1 秒都会输出一次压测报告的，此时他会每隔 1 秒会输出类似下面的一段东西
[ 300s ] thds: 10 tps: 88.99 qps: 1791.78 (r/w/o: 1263.85/349.96/177.98) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00thds: 10：表示有 10 个线程在压测
tps: 88.99：每秒执行了 88.99 个事务
qps: 1791.78：每秒可以执行 1791.78 个请求
(r/w/o: 1263.85/349.96/177.98)：在每秒的 1791.78 个请求中，有 1263.85 个是读请求，349.96 个是写请求，177.98 个是其他请求，就是对 QPS 进行了拆解
lat (ms,95%): 196.89：95% 的请求的延迟都是在 196.89 毫秒以下
err/s: 0.00 reconn/s: 0.00：每秒有 0 个请求失败，发生了 0 次网络重连
完成压测之后，最后会显示一个总的压测报告
SQL statistics:
queries performed:
read: 449288 # 300s 的压测期间执行了 44 万多次读请求
write: 128368 # 300s 的压测期间执行了 12 万多次写请求
other: 64184 # 300s 的压测期间执行了 6 万多次读其他求
total: 641840 # 300s 的压测期间共执行了 64 万多次请求
transactions: 32092 (106.94 per sec.) # 一共执行了 3 万多个事务，每秒执行 106.94 个事务
queries: 641840 (2138.86 per sec.) # 一共执行了 64 万多次的请求，每秒执行 2138.86 个请求
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
# 一共执行了 300s 的压测，执行了 3W+ 的事务
General statistics:
total time: 300.0840s
total number of events: 32092
Latency (ms):
min: 13.12 # 请求中延迟最小的是 13.12ms
avg: 93.49 # 所有请求平均延迟是 93.49ms
max: 791.95 # 延迟最大的请求是 791.95ms
95th percentile: 173.58 # 95% 的请求延迟都在 173.58ms 以内
sum: 3000154.01
Threads fairness:
events (avg/stddev): 3209.2000/8.40
execution time (avg/stddev): 300.0154/0.03