1.基础架构
1.1.一条查询sql语句的执行流程
1.2.常见msyql客户端命令
#客户端登录
mysql -h127.0.0.1 -p -P3310 -uroot
#查看版本
select version();
#显示数据库
show databases;
# 指定数据库，并显示表
use edu-common;
show tables;
#查询数据表
select * from a_test;
#查看客户端连接进程
show processlist;
#查看系统配置
show variables;
show variables like 'query%';
#常用参数
空闲连接超时：wait_timeout
查询缓存：query_cache_type
1.3.重要的日志模块
- 重做日志：redo log
- 两阶段提交
#重要的日志模块
##1.重做日志：redo log
-是InnoDB引擎特有，有crash-safe能力
-固定大小，比如配置一组4个文件，每个文件大小1G
-是物理日志，记录在数据页上做了什么修改
-循环写（相当于环形缓冲区），空间固定会用完
##2.归档日志：binlog
-是server层提供，所有存储引擎都可以使用
-是逻辑日志，记录sql语句的原始逻辑
-追加写，不会覆盖以前的日志
1.4.事务
#事务ACID原则
原子性、一致性、隔离性、持久性
#事务隔离级别
读未提交--->脏读
读已提交---> 通过一致性视图实现
可重复读--->通过一致性视图实现，所谓一致性视图，其实就是一个由事务id组成的数组
串行化--->加锁
#查看事务隔离级别
show variables like 'transaction_isolation%';
#关于mvcc
多版本并发控制，即一条记录在系统中存在多个版本，通过回滚段与事务Id实现
#关于长事务
在实际应用中，尽量不要使用长事务，原因：长事务会因为回滚段的存在而占用更多的内存资源，和锁资源
#关于事务的启动方式
begin/start transaction--->等到语句执行的时候，才真正开启事务
start transaction with consistency snapshot--->立即开启事务
#查看长事务
select * from information_schema.INNODB_TRX;
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
1.5.索引
#关于索引的理解
索引是利用空间换时间，提升查询数据的性能
索引有序
#索引数据模型
哈希--->哈希索引，适合精确匹配搜索，不能实现区间搜索
有序数组--->适合区间搜索，更新性能低
搜索树---> B+树，降低树的高度，减少操作磁盘IO的次数，提升性能
跳表--->
LSM树
#索引组织表
mysql中的InnoDB引擎，支持索引组织表，即一个表如果没有创建主键，会有默认的主键
#主键索引，二级索引，复合索引
主键索引存储数据页（一个数据页，默认16k大小，存储多个记录）
二级索引存储主键值，查询的时候，先根据索引获取到主键值，再回表查询获取记录数据
复合索引是有多个字段组成的索引，有索引覆盖（减少回表），前缀匹配等作用
#唯一索引，与普通索引
如果是写多读少，且在业务应用中保证了唯一性，推荐使用普通索引，可以利用上change buffer，降低操作磁盘次数，提升性能
如果是归档库，因为数据已经保证了唯一性，推荐使用普通索引
在实际业务中，当然在数据库层面借助唯一索引，保证唯一性更好
#查看执行计划
explain select * from a_test;
explain select * from a_test where name like 'xiao%';
#重建索引，删除索引
alter table a_test add index(name);
alter table a_test drop index name;
#分析表
analyze table a_test;
#索引hint
explain select * from a_test use index(name) where name like 'xiao%';
explain select * from a_test force index(name) where name like 'xiao%';
#mysql优化器，选择索引的参考因子
基数--->采样统计获取，选择N个数据页，当数据更新变化有1/M的时候，会更新统计信息；或者执行analyze table t;
排序--->
是否使用临时表--->
#选错索引
--建表
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=INNODB;
--初始化数据
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
--分析sql
mysql> select * from t where a between 10000 and 20000;
--强制走索引
set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/
#字符串建立索引，参考案例：区分度是关键
mysql> select count(distinct email) as L from SUser;
mysql> select
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;
假设区分度损失可接受：5%,那么统计出来的值：L4/L5/L6/L7，只要大于：L * 95%即可
1.6.锁
#全局锁
锁整个实例，让整个实例只读
flush table with read lock;
set global readonly=true;
unlock tables;
#表锁
lock tables a_test read/write;
unlock tables;
#MDL锁
它也是表级锁，不需要显示处理
如果是DML操作，系统会自动加上MDL读锁
如果是DDL操作，系统会自动加上MDL写锁
#行锁
行锁，即锁的粒度在行
行锁的两阶段锁协议，开始操作加锁，事务结束后才会释放锁
行锁死锁解决办法：超时，或者死锁检测
#查看行锁超时时间，以及死锁检测参数
show variables like 'innodb_lock_wait%';
show variables like 'innodb_deadlock_detect%';
#查看被锁的表
select * from INFORMATION_SCHEMA.INNODB_TRX；
#1、查询进程
show full processlist 查询到相对应的进程===然后 kill id
#2、查询是否锁表（查看正在被锁定的的表）
show OPEN TABLES where In_use > 0;
show status like '%lock%'
#更新逻辑走当前读，查询逻辑如果加锁走当前读
mysql> select k from t where id=1 lock in share mode;--->读锁，S锁，共享锁
mysql> select k from t where id=1 for update;--->写锁，X锁，排他锁
1.7.备份还原操作
#直接备份，指定备份文件位置
mysqldump -h127.0.0.1 -uroot -padmin -P3310 edu-common >D:\03other\02study\edu-common20200906.sql
# 删除表格式备份（能够让该备份覆盖已有数据库而不需要手动删除原有数据库）
mysqldump --add-drop-table -h127.0.0.1 -uroot -padmin -P3310 edu-common >D:\03other\02study\edu-common20200906_1.sql
#压缩备份（linux）
mysqldump -h127.0.0.1 -uroot -padmin -P3310 edu-common |gzip >D:\03other\02study\edu-common20200906.sql.gz
#指定表备份
mysqldump -h127.0.0.1 -uroot -padmin -P3310 edu-common a_test >D:\03other\02study\edu-common20200906_2.sql
#仅备份数据库结构，不备份数据
mysqldump -h127.0.0.1 -uroot -padmin -P3310 --no-data --databases edu-common >D:\03other\02study\edu-common20200906_3.sql
----------------------------------------------------------
#还原mysql数据库
mysql -h127.0.0.1 -uroot -padmin -P3310 edu-common < D:\03other\02study\edu-common20200906_3.sql
#还原压缩数据库
gunzip < backupfile.sql.gz | mysql -h127.0.0.1 -uroot -ppass myweb
全量备份参考脚本
#!/bin/bash
# mysql 数据库全量备份
# 用户名、密码、数据库名
username="root"
password="tencns152"
dbName="goodthing"
beginTime=`date +"%Y年%m月%d日 %H:%M:%S"`
# 备份目录
bakDir=/home/mysql/backup
# 日志文件
logFile=/home/mysql/backup/bak.log
# 备份文件
nowDate=`date +%Y%m%d`
dumpFile="${dbName}_${nowDate}.sql"
gzDumpFile="${dbName}_${nowDate}.sql.tgz"
cd $bakDir
# 全量备份（对所有数据库备份，除了数据库goodthing里的village表）
/usr/local/mysql/bin/mysqldump -u${username} -p${password} --quick --events --databases ${dbName} --ignore-table=goodthing.village --ignore-table=goodthing.area --flush-logs --delete-master-logs --single-transaction > $dumpFile
# 打包
/bin/tar -zvcf $gzDumpFile $dumpFile
/bin/rm $dumpFile
endTime=`date +"%Y年%m月%d日 %H:%M:%S"`
echo 开始:$beginTime 结束:$endTime $gzDumpFile succ >> $logFile
# 删除所有增量备份
cd $bakDir/daily
/bin/rm -f *
增量备份
# 检查log_bin是否开启
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.01 sec)
#开启 log_bin，并重启mysql
##编辑 mysql 的配置文件 vim /etc/my.cnf，在 mysqld 下面添加下面2条配置
[mysqld]
log-bin=/var/lib/mysql/mysql-bin
server_id=152
##重启mysql
service mysqld restart
#备份
##进入mysql命令行，执行 show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 430 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
##比如当前数据库test的bk_user只有2条记录
mysql> select * from test.bk_user;
+----+------+------+------+
| id | name | sex | age |
+----+------+------+------+
| 1 | 小明 | 男 | 25 |
| 2 | 小红 | 女 | 21 |
+----+------+------+------+
2 rows in set (0.00 sec)
##插入一条新的记录
mysql> insert into test.bk_user(name, sex, age) values('小强', '男', 24);
Query OK, 1 row affected (0.02 sec)
mysql> select * from test.bk_user;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
| 1 | 小明 | 男 | 25 |
| 2 | 小红 | 女 | 21 |
| 5 | 小强 | 男 | 24 |
+----+------+-----+-----+
3 rows in set (0.03 sec)
##执行命令mysqladmin -uroot -p密码 flush-logs，生成并使用新的日志文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
--------------------------------------------------------------------
恢复增量备份
#首先假装误删数据库记录
mysql> delete from test.bk_user where id=4;
Query OK, 1 row affected (0.01 sec)
mysql> select * from test.bk_user;
+----+------+------+------+
| id | name | sex | age |
+----+------+------+------+
| 1 | 小明 | 男 | 25 |
| 2 | 小红 | 女 | 21 |
+----+------+------+------+
2 rows in set (0.00 sec)
#从备份的日志文件mysql-bin.000003中恢复数据
[root@centos56 ~]# mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p test
Enter password:
ERROR 1032 (HY000) at line 36: Can't find record in 'bk_user'
##如果你也遇到这个问题的话，不妨修改 /etc/my.cnf 配置试试。
##我在server_id那一行下添加了 slave_skip_errors=1032 ，然后就执行成功了，不再报错
mysql> select * from test.bk_user;
+----+------+------+------+
| id | name | sex | age |
+----+------+------+------+
| 1 | 小明 | 男 | 25 |
| 2 | 小红 | 女 | 21 |
| 5 | 小强 | 男 | 24 |
+----+------+------+------+
3 rows in set (0.00 sec)
增量定时备份脚本
#!/bin/bash
# 增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
BakDir=/home/mysql/backup/daily
# 日志文件
LogFile=/home/mysql/backup/bak.log
# mysql的数据目录
BinDir=/var/lib/mysql-bin
# mysql的index文件路径，放在数据目录下的
BinFile=/var/lib/mysql-bin/mysql-bin.index
# 这个是用于产生新的mysql-bin.00000*文件
/usr/local/mysql/bin/mysqladmin -uroot -ptencns152 flush-logs
Counter=`wc -l $BinFile | awk '{print $1}'`
NextNum=0
# 这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
for file in `cat $BinFile`
do
base=`basename $file`
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $Counter ]
then
echo $base skip! >> $LogFile
else
dest=$BakDir/$base
#test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去
if(test -e $dest)
then
echo $base exist! >> $LogFile
else
cp $BinDir/$base $BakDir
echo $base copying >> $LogFile
fi
fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $LogFile
定时备份
# 每个星期日凌晨3:00执行完全备份脚本
0 3 * * 0 /bin/bash -x /root/bash/Mysql-FullyBak.sh >/dev/null 2>&1
# 周一到周六凌晨3:00做增量备份
0 3 * * 1-6 /bin/bash -x /root/bash/Mysql-DailyBak.sh >/dev/null 2>&1
2.最佳实践
#表分析
--当我们需要统计表的记录行数，比如执行：show table status;
--或者需要避免mysql选错索引的时候，我们可以执行表分析操作：analyze table a_test;
#重建表
--当一个表因为增删改操作，导致页空洞，而造成空间浪费的时候，我们可以执行重建表
--备注：mysql的delete操作只是标记删除，即被删除数据的位置可复用，而不是说真正的删除了数据。因此有时候我们会发现delete from t后，表空间大小并没有改变
--需要注意：重建表DDL，不是ONLINE的
--重建表方式一：alter table a_test engine=innodb;
--重建表方式二：optimize table a_test;
# mysql中redo log与binlog
--redo log 是重做日志，对于mysql数据库来说，使用了WAL技术，一条语句更新成功的标志是：更新内存，写入redo log即可
--redo log重做日志是两阶段提交，是Innodb引擎独有，有crash safe能力，即保证数据不丢失。两阶段提交保证数据一致性
--binlog是server层实现，所有存储引擎都可以使用，主要用于归档，主从复制等能力
--redo log的两阶段提交，写入redo log后预提交（prepare），写入binlog后再次提交（commit）
--mysql崩溃恢复后，通过两阶段提交保证不丢失数据，因为在redo log与binlog之间通过xid关联在一起
--在生产环境中，一般将redo log设置为四个文件，每个文件大小 1个G。redo log是循环写，binlog是追加写
#误删除表以后，该如何恢复？
--找出最近一次全量备份，从该备份恢复一个临时库
--从备份时间点开始，将线上库的binlog日志取出来，重放到临时库误删表之前的时刻
--这样即得到一个与线上库误删表前一致的临时库，再从临时库取出误删表，写入线上库即可
#关于索引的选择
--在实际项目中，如果业务应用保证了唯一性，那么我们推荐使用普通索引，因为普通索引可以利用change buffer，效率会更高（减少了访问操作磁盘）
--比如说归档库，我们就可以使用普通索引，而不使用唯一索引，因为归档库中的数据已经保证了唯一性
--在mysql中优化器选择索引的参考因素：基数（采样统计得到）、是否排序、是否使用临时表
#怎么给字符串添加索引
--添加前缀索引，或者reverse操作，或者冗余一个字段存储hash值
--关于前缀字符长度定义，比如允许损失区分度比例：5%，那么需要找出统计字符串子串长度值，比如L4-L7的数量，找出不小于L*95%的值。这里的L指字符串子串的长度
SELECT
count(DISTINCT LEFT(NAME, 2)) AS L2,
count(DISTINCT LEFT(NAME, 3)) AS L3,
count(DISTINCT LEFT(NAME, 4)) AS L4,
count(DISTINCT LEFT(NAME, 5)) AS L5,
count(DISTINCT LEFT(NAME, 6)) AS L6
FROM
a_test;
#为什么有时候mysql会“抖”一下，即会变卡顿
--原因一：可能是redo log写满了，需要刷脏页，导致所有的更新都被阻塞
--原因二：内存不够用了，需要刷脏页
#关于count(*)、count(1)、count(主键字段)、count(普通字段)的性能对比
--大的原则：server层需要什么，存储引擎层就返回什么
--count(*)，存储引擎层取行，并返回，server层直接加1
--count(1)，存储引擎层取行并返回1，server直接加1
--count(主键字段)，存储引擎层取行，解析主键字段，拷贝主键字段，server层加1
--count(普通字段)，存储引擎层取行，解析普通字段，拷贝普通字段，server层判断字段不为空，然后加1
--因此性能从低到高：count(普通字段)-->count(主键字段)-->count(1)=count(*)
# 关闭整个库查询缓存，通过sql_cache明确需要缓存的sql
set query_cache_type=DEMAND
select sql_cache * from my_user where id=1
#redo log持久化到磁盘参数配置
set innodb_flush_log_at_trx_commit=1
#binlog 持久化到磁盘参数配置
set sync_binlog=1
#事务隔离级别参数配置,oracle默认读提交，mysql默认可重复读
set tx_isolation=REPEATABLE-READ
#事务启动方式
1.显示启动： begin/start transaction....commit/rollback
2.set autocommit=0,关闭自动提交，必须通过commit/rollback提交或者回滚
3.在实践中，推荐打开自动提交，即set autocommit=1
#查询长事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
#排序相关-----------------------------
/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from information_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from information_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算Innodb_rows_read差值 */
select @b-@a;
# 构造for update问题，关于间歇锁，只在RR级别下有，在RC级别下无
# 如果将事务隔离级别设置成RC，需要考虑数据不一致问题（幻读），建议将binlog设置为row格式，即set binlog_format=row
--创建表，及初始化数据
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t1 values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
select * from t1;
--手动开启事务，且where字段无索引,现象：锁住全表
begin/start transaction;
select * from t1 where d=5 for update;
commit;
insert into t1 values(30,30,30);
--手动开启事务，且where字段有索引，现象：不锁住全表，锁住索引范围，解决幻读：Gap Lock
begin/start transaction;
select * from t1 where c=5 for update;
commit;
insert into t1 values(6,6,6);--->锁住
insert into t1 values(26,26,26);--->不锁
#插入数据，如果存在，即更新
insert … on duplicate key update
#sql语句改写
mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");
call query_rewrite.flush_rewrite_rules();
