MySQL数据库优化
MySQL数据库优化
主流版本:
mysql 5.6 5.6.36 5.38 5.6.40
mysql5.7 5.7.18 5.7.20 5.7.22
企业版本选择:6-12月之间的GA
mysql c/s结构
两种连接方式:
tcp/ip(远程,本地):
mysql -uroot -p123 -h 10.0.0.200 -P3306
socket(本地):
mysql -uroot -p123 /tmp/mysql.sock
mysql实例
实例=mysqld+内存结构
mysqld -------->master thread ------->N thread -------->内存结构
mysql三层结构
连接层
1.提供连接协议
2.用户验证
3.提供专用链接线程
SQL层
1.接收上层的命令
2.语法检测
3.语义(sql类型),权限
SQL类型:DDL定义语言 dcl数据控制语言 dml数据操作语言 dql数据查询
4.专用解析器解析SQL,解析成执行计划
5.优化器,帮我们选择一个代价最低的执行计划
6.按照优化器的选择,执行SQL语句,得出获取数据方法
7.查询缓存:默认是关闭的 一般会使用redis产品替代
8.记录日志:查询日志,二进制日志
存储引擎层
按照SQL层结构,找相应数据,结构化成表的形式
MySQL的逻辑结构
库:存储表的地方
表:二维表
表名字
表的属性
列:列名字,列属性(数据类型,约束,其他定义)
记录:数据行
SQL语句(sql92)
SQL种类
ddl数据定义语句
dcl数据控制语句
dml数据操作语句
dql数据查询语句
不同分类语句作用
ddl
库
create database
drop database
alter database
SQL语句规范:
1.关键字大写,字面量小写
2.库名字只能是小写,不能有数字开头,不能是预留的关键字
3.库名和业务名字有关
4.必须加字符集
表
CREATE TABLE
DROP TABLE
ALTER TABLE
CREATE TABLE t1(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID)ENDINE INNODB CHARSET utf8mb4;
规范:
1.关键字大写,字面量小写
2.表名字只能是小写,不能有数字开头,不能是预留的关键字
3.表名字和业务有关
4.必须加存储引擎和字符集
5.使用合适的数据类型
6.必须要有主键
7.必须加注释
8.避免外键
9.建立合理的索引
DCL(数据控制语句):
grant
revoke
lock
DML(数据操作语句):
insert
update
delete
规范:
1.insert语句按批量插入数据
2.update必须加where条件
3.delete尽量替换为update
4.清空全表数据,truncate
DQL:
select
show
规范:
1.select尽量避免使用select * from t;
2.select语句尽量加等值的where条件
3.select 语句对于范围查询
4.select 的where条件查询结果集尽量减少
5.不要出现3表以上的表连接,避免子查询
6.where条件中不要出现函数操作
mysql初始化数据
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/opt/mysql/data --basedir=/opt/mysql
Linux中mysql的配置
vim /etc/my.cnf
[mysqld]
basedir = /user/local/mysql
datadir = /user/local/mysql/mydata
socket = /tmp/mysql.sock
log_error = /var/log/mysql.log
user = mysql
port = 3306
server_id = 6
[mysql]
socket = /tmp/mysql.sock
作用:
1.影响服务端的启动
标签 [mysqld]
[mysqld_safe]
[server]
mysql多实例
创建相关目录
mkdir -p /data/330{7..9}/data
创建配置文件
cat>> /data/3307/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3307/data
user=mysql
socket=/data/3307/mysql.sock
port=3307
server_id=3307
EOF
#复制到其它目录
cp /data/3307/my.cnf /data/3308
cp /data/3307/my.cnf /data/3309
#修改其它目录的文件
sed -i 's#3307#3308#g' /data/3308/my.cnf
sed -i 's#3307#3309#g' /data/3309/my.cnf
#初始化数据
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/opt/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql
启动服务
#授权操作
chown -R mysql.mysql /data/*
#启动操作
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &
测试
netstat -lnp|grep 330
mysql -S /data/3307/mysql.sock
mysql -S /data/3308/mysql.sock
mysql -S /data/3309/mysql.sock
system管理多实例
cat >> /etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
#复制此文件到其它实例3308/3309
cp /etc/systemd/system/mysqld3307.service /etc/systemd/system/mysqld3308.service
cp /etc/systemd/system/mysqld3307.service /etc/systemd/system/mysqld3309.service
sed -i 's#3307#3308#g' /etc/systemd/system/mysqld3308.service
sed -i 's#3307#3309#g' /etc/systemd/system/mysqld3309.service
启动
systemctl start mysqld3307
systemctl stop mysqld3309
systemctl enable mysqld3307
忘记密码处理
mysqladmin -uroot -p password 123
select user,authentication_string,host from mysql.user;
1.停数据库
/etc/init.d/mysqld stop
2.启动数据库为无密码验证模式
mysqld_safe --skip-grant-tables --skip-networking &
update mysql.user set authentication_string=PASSWORD('456') where user='root' and host='localhost';
/etc/init.d/mysqld restart
mysql -uroot -p123
mysql -uroot -p456
数据类型和字符集
整型
int 最多存10位数据
字符串类型
char 定长,存储数据效率高,对于变化较多的字段,空间浪费较多
varchar 变长,存储时判断长度,存储会有额外开销,按需分配存储空间
enum
时间类型
datetime
timestamp
date
time
规范
1.少于10位的数字int,大于10位数 char
2.char和varchar选择时,字符长度一定不变的可以使用插入,可变的尽量使用varchar,在可变长度的存储时,将来使用不同的数据类型,对于索引树的高度是有影响的
3.选择合适的数据类型
4.合适长度
索引及执行计划
作用:优化查询,select查询有三种情况:缓存查询,全表扫描,索引扫描
索引种类
Btree(btree b+tree b*tree)
Rtree
HASH
FullText
b+tree原理图
Btree索引分类
聚集索引:基于主键,自动生成的,一般是建表时创建主键.如果没有主键,自动选择唯一索引作为主键索引(PRI)
辅助索引:人为创建的(MUL)
唯一索引:人为创建(普通索引,聚集索引)
聚集索引和辅助索引的区别
1.聚集索引:叶子节点,按照主键列的顺序,存储整行数据,就是真正的数据页
2.辅助索引:叶子节点,排序之后,存储到叶子节点+对应的主键的值,便于回表查询
创建索引
创建普通辅助索引(MUL)
alter table blog_userinfo add key idx_email(email);
create index idx_phone on blog_userinfo(phone);
查看索引
desc blog_userinfo;
show index from blog_userinfo;
删除索引
alter table blog_userinfo drop index idx_email;
drop index idx_phone on blog_userinfo;
前缀索引
select count(*),substring(password,1,20) as sbp from blog_userinfo group by sbp;
alter table blog_userinfo add index idx(password(10));
唯一键索引(如果有重复值就无法创建)
alter table blog_userinfo add unique key uni_email(email);
覆盖索引(联合索引)
作用:不需要回表查询,不需要聚集索引,所有查询的数据都是从辅助索引中获取
select * from people where gender , age , money
alter table t1 add index idx_gam(gender,age,money);
索引扫描性能
explain(desc)
type:查询 类型(越向下,性能越好)
ALL:全表扫描
Index:全索引索引
desc select county from city;
range:索引范围扫描
where > <
in or between and
like 'CH%'
ref:辅助索引的等值查询
in 或者 or 改写成 union
select * from city where country = 'CHN'
union all
select * from city where country = 'USA'
eq_ref:多表连接查询
const,system:主键或者唯一键等值查询
Extra:
using filesort: 文件排序(建立联合索引)
建立索引的原则
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit
(6) 索引维护要避开业务繁忙期
不走索引的情况(开发规范)
1) 没有查询条件，或者查询条件没有建立索引
select * from tab; 全表扫描。
select * from tab where 1=1;
2) 查询结果集是原表中的大部分数据，应该是25％以上。
3) 索引本身失效，统计数据不真实
4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
5)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
select * from t1 where telnum=110;
压力测试
1、模拟数据库数据
为了测试我们创建一个oldboy的库创建一个t1的表，然后导入50万行数据，脚本如下：
vim slap.sh
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="123"
DBNAME="oldboy"
TABLENAME="t1"
#create database
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"
create_db_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
#create table
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1)
not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100)default null)"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"
#insert data to table
i="1"
while [ $i -le 500000 ]
do
insert_sql="insert into ${TABLENAME} values($i,'alexsb_$i','1','110011198809163418','1990-05-16','2017-09-13','oldboyedu')"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"
let i++
done
#select data
select_sql="select count(*) from ${TABLENAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"
执行脚本：
sh slap.sh
2、检查数据可用性
mysql -uroot -p123
select count(*) from oldboy.t1;
3、在没有优化之前我们使用mysqlslap来进行压力测试
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t1 where stuname='alexsb_100'" engine=innodb \
--number-of-queries=2000 -uroot -p123 -verbose
存储引擎
作用:和磁盘打交道
简介
MySQL 基于存储引擎管理 表空间数据文件
种类:
Innodb存储引擎
ibd:存储表的数据行和索引
frm:表结构信息
Myisam存储引擎
frm
myi
myd
事务
ACID特性
Atomic(原子性)
所有语句作为一个单元全部成功执行或全部取消
Consistent(一致性)
如果数据库在事务开始时处于一致状态,则在执行该事务期间将保留一致状态
Isolated(隔离性)
事务之间不相互影响
两个方面:修改同一行 , 一致性读
Durable(持久性)
事务成功完成后,所做的所有更改都会准确地记录在数据库中.所做的更改不会丢失.
行级锁:事务修改行,会锁定这行(持有这行的锁)
隔离级别(一致性读):
RU
RC
RR(默认)
S
事务控制语句
begin;
xxx
xxx
commit;
begin;
xxx
xxx
begin;
xxx
xxx
rollback;
隐式提交
set autocommit = 0 ;
日志
错误日志:
log_errot = /var/log/mysql.log
二进制日志
作用:
记录所有变更类的语句
DDL,DCL:以语句方式(statement)记录
DML:默认是以行模式(row)记录
可以做数据库审计
配置方法:
log_bin = /opt/mysql/data/mysql-bin
binlog_format =row
server_id = 6
sync_binlog =1
分析和截取日志
#查看可以使用的二进制日志
show binary logs;
#正在使用的日志
show master status;
#查看二进制日志
mysqlbinlog --base64-output=decode-rows -vvv /opt/mysql/data/mysql-bin.000005
慢日志
记录慢语句的日志文件
slow_query_log = 1
slow_query_log_file = /opt/mysql/data/standby-slow.log
#记录时间超过0.1秒的语句
long_query_time = 0.1
#记录不走索引的语句
log_queries_not_using_indexes=1
备份恢复
备份的种类
逻辑备份:SQL语句的备份
物理备份:数据页备份
逻辑备份工具介绍
select xxxx from t1 into outfile '/tmp/redis.txt'
mysql -uroot -p123 -e "select concat('hmset city_',id,' id ', id,' name ',name,' countrycode ',countrycode,' district ',district,' population ',population) from world.city limit 10 "|redis-cli
mysqldump
-A全部备份
mysqlddump -uroot -p123 -A >/backup/a.sql
-B选择指定的数据库备份
mysqlddump -uroot -p123 -B world bbs >/backup/a.sql
只备份单库或者库中的表
mysqldump -uroot -p123 >/backup/ccc.sql
--master-data =2 备份时记录二进制日志的状态
--single-transaction 开启innodb热备功能
-R
--triggers
导出一行
select * from city where id = 1 into outfile '/tmp/redis.txt'
主从复制
基于二进制日志的结构
前提需要有两台服务器,或者在在一台服务器上开启了多实例
设置主机与从机:Master为3307,slave为3308
3307中创建复制用户
主库开启二进制日志
#在mysql的配置文件中添加log_bin=/data/3307/mysql-bin
vim /data/3307/my.cnf
log_bin=/data/3307/mysql-bin
#重启mysql数据库服务
systemctl restart mysqld3307
#进入数据库命令行
mysql -S /data/3307/mysql.sock
#设置密钥(在数据库的命令行下操作)
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
#查看密钥
show master status;
创建从机
#进入数据库
mysql -S /data/3308/mysql.sock
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.200',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
mysql> start slave;
#若显示下列则表示成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
"高可用"架构
99%
99.9%
99.99%
99.999%
MySQL高可用架构
MHA
PXC
galera cluster
"高性能架构"
读写分离
atlas 360 C++
maxscale mariadb
proxysql
DRDS
mysql router
分布式架构
分片集群
TDDL
mycat
DBLE
DRDS
