您现在的位置是：首页 > 数据库

当前栏目

MySQL学习笔记

mysql 笔记学习

2023-09-27 14:19:57 时间

MySQL 索引结构 - rickiyang - 博客园 (cnblogs.com)

文章目录

MySQL基础
MySQL 高级

MySQL基础

1.基础语法

Null 相关

使用 is null，例如查询哪些员工津贴为null：

select empno,ename,sal from emp where comm is null

IS NULL & <=>

前者仅可以判断NULL值，后者既可以判断NULL，又可以判断普通的数值

2.条件语句

and 和 or

and 优先级比 or 高

in

语法：
- select xxx from xxx where n in('a','b')

like（模糊查询）

%：匹配任意个字符
_：匹配一个字符
[ ] ：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
- 如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”
[^ ] ：表示不在括号所列之内的单个字符。其取值和 [ ] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

注意：

涉及到% _ 可以用 \ 转义
like 不匹配 null

3.排序

默认升序 ASC 可以指定降序 DESC
根据两个或多个字段排序
- 有先后之分，先写的优先排，只有其值相同，才会用到后面的排序
order by 后面可以跟数字，表示根据第几列排序
以上语句的执行顺序
- from -> where -> select -> order by（排序总是在最后）

4.单行处理函数

字符串处理函数

Lower/Upper：大小写
concat：拼接字符串

select concat(last_name,'_',first_name) from employees;

substr：取子串（substr(被截取的字符串, 起始下标, 截取的长度)）
- 起始下标从 1开始 ( field, [from, to] ) 包括两端取值
instr：字符串出现位置 ( [ source_str, example_str ] )，从1开始计算
length( )：长度
trim：去前后空格
- trim( ‘a’ from ‘aaadsdsaaaa’ ) 去除前后的 ‘a’
lpad( source_str, length, char )：用指定字符左填充指定长度
rpad( source_str, length, char )：用指定字符右填充指定长度
replace( source, old, new )：字符串替换
instr(source, target)：查找targer字符串第一次在source出现的位置（从1开始，不存在返回0）
locate(target, source)：查找targer字符串在source出现的位置（从1开始，不存在返回0）
position( targer IN source )：查找targer字符串第一次在source出现的位置（从1开始，不存在返回0）
substring_index(source, char ,n)：source 字符串截取函数，注意index不能为0，正数代表截取第n个出现的char之前的所有字符串，负数代表截取倒数第|n|个出现的char之后的所有字符串
- select device_id,substring_index(blog_url,'/',-1) user_name from user_submit

数学函数

cell( )：向上取整，返回>=该参数的最小整数
floor( )：向下取整，返回<=该参数的最小整数
trancate( num [,n ] )：数字截断，可以指定小数点后位数n
mod( )：取余 = a-a/b*b
round( )：四舍五入 ( field [,保留小数位数] )
- 负数代表向整数位舍入
format：设置千分位
rand：生成随机数
- 一百以内随机整数：round(rand()*100, 0)

日期函数

now( )：获取当前时间
curdate( )：返回当前年月日
curtime( )：返回当前时分秒
year( time )：截断取年
month( time )：截断取月
str_to_date：字符串转日期，–> 数据库识别
- select * from emp where hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

格式符	含义
%Y	四位年份
%y	2位的年份
%m	月份(01,02…11,12)
%c	月份(1,2,…11,12)
%d	日(01,02,…)
%H	小时（24小时）
%h	小时（12小时）
%i	分钟（00，01…59）
%s	秒（00，01…59）

date_format：数据库日期， --> 期望格式
- select DATE_FORMAT(now(),'%y-%m-%d');
timestampdiff( unit, start_time, end_time )：计算时间间隔函数

其它函数

ifnull( field, replace )：将 null 转换
version( )
database( )
user( )

5.多行处理函数

若不配合分组字段使用，则默认将整张表当做一组；

分组函数自动忽略 null

count：计数
sum：求和
avg：平均值
max/min：最值
count ( 具体字段 )：表示统计该字段下所有不为NULL的元素总数
count ( * )：统计表当中的总行数（只要有一行数据则count++）
count( n )：将所有行置1，统计行数
有主键 count( 主键字段 )最快
没主键 count( 1 )最快
对于以上两种情况，这种情况更优先：
- 表中只有一个字段，count( * )最快
MyISAM存储引擎下，COUNT(*)效率最高

详细对比：https://www.cnblogs.com/nov5026/p/12966889.html

6.常用SQL技巧

SQL执行顺序

编写顺序

select distinct
		查询字段
from
 		表名
join
		表名 on 条件
where
		条件
group by
		字段
having
		条件
order by
		字段
limit
		参数

执行顺序

from -> on -> join -> where -> group by -> having -> select distinct -> order by -> limit

模糊查询 - 正则表达式

select * from tableName where name regexp 表达式

7.分组查询

找出每个部门，不同工作岗位的最高薪资

SELECT
	deptno,
	job,
	max( sal ) 
FROM
	emp 
GROUP BY
	deptno,
	job

找出每个部门最高薪资，要求显示最高薪资大于3000的

SELECT
	deptno,
	max( sal ) maxsal 
FROM
	emp 
GROUP BY
	deptno 
HAVING
	maxsal > 3000
	
######↓改进↓######

SELECT
	deptno,
	max( sal ) 
FROM
	emp 
WHERE
	sal > 3000 
GROUP BY
	deptno

找出每个部门平均薪资，要求显示平均薪资高于2500的

SELECT
	deptno,
	avg( sal ) 
FROM
	emp 
GROUP BY
	deptno 
HAVING
	avg( sal )> 2500

8.distinct 关键字

只能用在所有字段最前方

统计所有工作岗位的数量

select count(distinct job) from emp

9.连接查询

内连接

等值连接

SELECT
	e.ename,
	d.dname 
FROM
	emp e  # inner 省略
	JOIN dept d ON e.deptno = d.deptno

非等值连接

SELECT
	e.ename,
	e.sal,
	s.grade 
FROM
	emp e
	JOIN SALGRADE s ON e.sal BETWEEN s.LOSAL 
	AND s.HISAL

自连接

SELECT
	e.ename emp,
	d.ename mgr 
FROM
	emp e
	JOIN emp d ON e.mgr = d.empno

外连接

左外连接/右外连接

SELECT
	e.ename emp,
	d.ename mgr 
FROM
	emp e   # 省略 outer -> left outer join
	LEFT JOIN emp d ON e.mgr = d.empno

全外连接

MySQL不支持，全外连接 = 内连接的结果 + 表1中有但表2中没有的 + 表2中有但表1中没有的

交叉连接

# 笛卡尔积的体现
select a.*,b.* from beauty b cross join boys a ;

10.子查询

# 放在 where 或者 having 后面 或 from后面 或 select 后面（单行单列）
select
	...(select)
from 
	...(select)
where
	...(select)

例：找出每个工作岗位的平均工资的薪资等级

SELECT
	* 
FROM
	SALGRADE s
	JOIN ( SELECT avg( sal ) avgsal, job FROM emp GROUP BY job ) t ON t.avgsal BETWEEN s.LOSAL 
	AND s.HISAL

例：找出每个员工的部门名称，要求显示员工名、部门名

SELECT e.ename,( SELECT d.dname FROM dept d WHERE e.DEPTNO = d.deptno ) dname 
FROM
	emp e;

例：查询每个部门的员工个数

# 子查询使用在 select 之后
SELECT d.*,(
  SELECT COUNT(*)
  FROM employees e
  WHERE e.department_id = d.department_id
) 个数
FROM departments d;

例：查询所有员工的部门名

# 子查询使用在 EXISTS 之后
SELECT department_name
FROM departments d
WHERE EXISTS(
  SELECT * 
  FROM employees e 
  WHERE d.department_id = e.department_id
)

多行子查询

操作符	含义
IN/NOT IN	等于列表中任意一个
ANY \| SOME	和子查询返回的某一个值比较 min
ALL	和子查询返回的所有值比较 max

NOT IN => <>ALL

IN => =ANY

UNION

例：查询工作岗位是MANAGER和SALESMAN的员工

select * from employees where email 条件1
union
select * from employees where email 条件2
###### UNION效率更高 ######

联合查询的多条查询语句列数必须一致
列对应关系与select后的字段顺序有关
union 默认去重，union all 取消去重

EXISTS

exists ( 子查询 ) 返回的是一个boolean值，有或没有

11.Limit 使用

SELECT * FROM 表名 limit 6,5;
结果：检索记录第7行至11行记录，共取出5条记录。

SELECT * FROM 表名 limit 6,-1;
结果：检索取出第7行至以后的所有数据。

SELECT * FROM 表名 limit 6;
结果：检索取出前6条记录行。

偏移量offset较小的时候，直接使用limit较优
偏移量offset越大，直接使用子查询越优。

12.表相关操作

创建表（DDL）

CREATE TABLE 表名称
(
    列名称1 数据类型,
    列名称2 数据类型,
    列名称3 数据类型,
    ....
)default character set = 'utf8';

复制建表

# 复制表结构 + 数据
create table tableName1  as select * from tableName2

# 仅复制表结构
create table newTable like oldTable

# 选择字段复制结构
create table tableName select filed1,filed2 from tableName1 where o;

#SQL Server
select * into newTable from oldTable

数据类型

主要包括以下五大类：

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型：FLOAT、DOUBLE、DECIMAL

字符串类型：CHAR(0_{255,fixed)、VARCHAR(0}65535,variable)、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型：Date、DateTime、TimeStamp、Time、Year

其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

约束（*********）

非空约束：not null（只能加在列上）
唯一性约束：unique
- 修饰的字段不能重复，但可以重复为NULL
- 表级约束：[constraint key_name]unique(field_1,field_2...)
主键约束：primary key（简称PK）
- auto_increment 主键自增
- 选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。
- 表级约束：[constraint key_name]primary key(field_name)
外键约束：foreign key（简称FK）
- 表级约束：[constraint key_name] foreign key(field_1) references tableName(field_2)
检查约束：check（mysql5.6不支持但不会报错，8.0支持；oracle支持）
列级约束：字段名类型 check(条件)
默认约束：default
- 列级约束：字段名类型 default 值
unique && not null = primary key （MySQL）

修改表的约束

# 除外键
alter table tableName modify [column] columnName typeName constraintName

# 外键
alter table tableName add constraint keyName foreign key(columnName) references tableName(columnName)

删除表的约束

# 删除非空约束
alter table tableName modify [column] columnName typeName NULL;
# 删除默认约束
alter table tableName modify [column] columnName typeName;
# 删除唯一约束
alter table tableName drop index;
# 删除外键约束，keyName默认为列名
drop table tableName drop foreign key keyName;

修改表（DDL）

# 修改列名/类型
alter table tableName change [column] oldName newName type
# 修改类型
alter table tableName modify [column] columnName type;
# 添加新列
alter table tableName add [column] columnName type;
# 删除列
alter table tableName drop [column] columnName;
# 修改表名
alter table oldName rename to newName

删除表（DDL）

# 删除支持回滚，但效率低
drop table if exists tableName
# 删除不支持回滚，但效率高
truncate table tableName

插入（DML）

insert into tableName(field_1,field_2,...) values(v1,v2,...)
# 一一对应，可以不全写 field
# 另外一种简写方法，需要全写字段,可以同时插入多条
insert into tableName values(AllFieldValue)
# 另外一种写法
insert into tableName set field1 = v1,field2 = v2 ...;

插入日期：

# varchar -> date
# %Y 年
# %m 月
# %d 日
# %h 时
# %i 分
# %s 秒
str_to_date('01-10-1990','%d-%m-%Y')
# 如果字符串正好是 1990-10-01 这种格式，可以省略这个函数

获取日期：

date_fromat(dateField, 'fromat')
# 例子 ↓
select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;

MySQL中如何获取当前时间？

可以通过 now() 函数，获取类型是 datetime 类型的

修改（DML）

update tableName set field1 = value 1, field 2 = value 2 ...  where 条件

删除（DML）

delete from tableName where 条件
# 没有条件，会删除表内全部数据

13.事务

只有DML（增删改）操作才会跟事务有关；

注意：MySQL事务默认值自动提交

事务四个特性

原子性（Atomicity）
- 原子性是指事务都是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
- 能够在错误时中止事务，丢弃该事务进行的所有写入变更的能力。
一致性（Consistency）
- 一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。
隔离性（Isolation）
- 隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。
持久性（Durability）
- 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

数据库隔离级别&问题

问题

脏读：对于两个事务T1，T2，T1读取了已经被T2更新但还没有被提交的字段，之后，若T2回滚，T1读取的内容就是临时且无效的
不可重复读：对于两个事务T1，T2，T1读取了一个字段，然后T2更新并提交了该字段，之后，T1再次读取同一个字段，值就不同了
幻读（虚读）：对于两个事务T1，T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后，如果T1再次读取同一个表，就会多出几行（这个只会在增删改时会体现出来！）

隔离级别

读未提交（read uncommitted）
- 没有提交就可以读到
读已提交（read committed）
- 存在不可重复读、幻读，解决脏读
- 提交后才可以读到
可重复读（repeatable read）
- 解决了不可重复读、脏读，存在幻读
- 提交后也读不到
- MySQL默认隔离级别
序列化（serializable）
- 事务排队，不能并发，效率低，解决了所有问题

相关命令

select @@transaction_isolation; # 查看隔离级别,MySQL80
select @@tx_isolation; # 查看隔离级别,MySQL56

set global transaction isolation level read uncommitted; # 设置事务隔离级别

select  @@global.autocommit;
set  @@global.autocommit  = 0; # 关闭自动提交

14.视图

重用SQL语句
简化复杂SQL操作，封装细节
保护数据，提高安全性

视图能不能更新、删除、添加？

如果视图的每一行是与物理表一一对应的，则可以，否则不可以。

视图的更新性和视图中查询的定义有关，以下类型的视图是不能更新的：

包含以下关键字的SQL语句：分组函数、distinct、group by、having、union或者union all
常量视图
select 中包含子查询
join
from 一个不能更新的视图
where 子句的子查询引用了 from 子句中的表

创建

create [or replace] [algorithm = {UNDEFINED|MEGRE|TEMPTABLE}] view 视图名 as 查询语句 [WITH [CASCADED|LOCAL] CHECK OPTION1];
# LOCAL:只要满足本视图的条件就可以更新
# CASCADED:必须满足所有针对该视图的所有视图的条件才可以更新，默认

修改

alter view 视图名 as 查询语句/视图;

删除

drop view [if exists] 视图名[,视图名...];

查看

# 查看指定数据库下所有视图
SHOW FULL TABLES IN databaseName WHERE TABLE_TYPE LIKE 'VIEW'; 
SHOW TABLE STATUS;

15. 34道作业题

取得每个部门最高最高薪水的人员名称

SELECT
	e.ename,
	t.* 
FROM
	emp e
	JOIN ( SELECT deptno, max( sal ) maxsal FROM emp GROUP BY deptno ) t ON e.sal = t.maxsal 
	AND t.deptno = e.deptno

哪些人的薪水在部门的平均薪水之上

SELECT
	e.ename,
	e.sal,
	t.avgsal
FROM
	emp e
	JOIN ( SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno ) t ON t.deptno = e.deptno 
	AND e.sal > t.avgsal;

取得部门中所有人的平均薪水等级

SELECT
	e.deptno,
	avg(s.grade)
FROM
	emp e
	JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
GROUP BY
	e.deptno

不准用（max），取得最高薪水

select ename,sal from emp order by sal desc limit 1;
# or
SELECT
	sal 
FROM
	emp 
WHERE
	sal NOT IN ( SELECT DISTINCT a.sal FROM emp a JOIN emp b ON a.sal < b.sal );

取得平均薪水最高的部门的部门编号

SELECT
	deptno,
	avg( sal ) avgsal 
FROM
	emp 
GROUP BY
	deptno 
ORDER BY
	avgsal DESC 
	LIMIT 1;

取得平均薪水最高的部门名称

SELECT
	d.dname,
	avg( e.sal ) avgsal 
FROM
	dept d
	JOIN emp e ON d.deptno = e.deptno 
GROUP BY
	d.dname
ORDER BY
	avgsal DESC 
	LIMIT 1;

求平均薪水的等级最低的部门的部门名称

SELECT
	d.dname 
FROM
	dept d
	JOIN ( SELECT deptno, sum( sal ) sumsal FROM emp GROUP BY deptno ORDER BY sumsal ASC LIMIT 1 ) t ON d.deptno = t.deptno;

取得比普通员工（员工代码没有在 mgr 字段上出现的）的最高薪水还要高的领导人姓名

SELECT
	ename,
	sal 
FROM
	emp 
WHERE
	sal > (
	SELECT
		max( sal ) 
	FROM
		emp 
WHERE
	empno NOT IN ( SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL ));

取得薪水最高的前五名员工

select ename,sal from emp order by sal desc limit 5;

取得薪水最高的第六到第十名员工

select ename,sal from emp order by sal desc limit 5,5;

取得最后入职的5名员工

select ename,hiredate from emp order by hiredate desc limit 5;

取得每个薪水等级有多少员工

SELECT
	s.grade,
	count(*) 
FROM
	emp e
	JOIN salgrade s ON e.sal BETWEEN s.LOSAL AND s.HISAL 
GROUP BY
	s.grade;

有三个表S（学生表）、C（课程表）、SC（学生选课表）
S（SNO，SNAME）-- 代表（学号，姓名）
C（CNO，CNAME，CTEACHER）-- 代表（课程号，课程名，教师名）
SC（SNO，CNO，SCGRADE）-- 代表（学号，课程号，成绩）

问题：

找出没选过”黎明“老师的所有学生姓名；

SELECT
	sname s 
WHERE
	sno NOT IN (SELECT sno FROM  sc WHERE cno IN 
              ( SELECT cno FROM c WHERE cteacher = '黎明' ));

找出两门及以上不及格学生姓名及平均成绩；

select
  t1.sno,t1.sname,t2.avggrade
from
  (select
    sc.sno,s.sname
  from
    SC sc
  join
    S s
  on
    sc.sno=s.sno
  where
    sc.scgrade < 60
  group by
      sc.sno,s.sname
  having
    count(*) >=2) t1
join
  (select sno,avg(scgrade) as avggrade from SC group by sno) t2
on
  t1.sno=t2.sno;

既学过1号课程又学过2号课程的所有学生的姓名。

SELECT
	s.sno,
	s.sname 
FROM
	sc
	JOIN s ON sc.sno = s.sno 
WHERE
	cno = 1 
	AND sno IN (
    SELECT
      sno 
    FROM
      sc 
  	WHERE
   	 	cno = 2)

列出所有员工及领导的姓名

SELECT
	a.ename '员工',
	b.ename "领导" 
FROM
	emp a
	LEFT JOIN emp b ON a.mgr = b.empno;

列出雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称

SELECT
	a.deptno,
	a.ename,
	d.dname 
FROM
	emp a
	LEFT JOIN emp b ON a.mgr = b.empno
	LEFT JOIN dept d ON a.deptno = d.deptno 
WHERE
	a.hiredate < b.hiredate

列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

SELECT
	d.dname,
	e.* 
FROM
	dept d
	LEFT JOIN emp e ON d.deptno = e.deptno

列出至少有5个员工的所有部门

select deptno from emp group by deptno having count(*) >=5;

列出比"SMIT"工资高的员工信息

SELECT
	* 
FROM
	emp 
WHERE
	sal > ( SELECT sal FROM emp WHERE ename = "SMITH" );

列出所有JOB为“CLERK”的员工姓名及其部门名称，部门人数

SELECT
	a.ename,
	a.dname,
	b.deptcount 
FROM
	( SELECT e.ename, d.dname, d.deptno FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE job = "CLERK" ) a
	JOIN ( SELECT deptno, count(*) deptcount FROM emp GROUP BY deptno ) b ON a.deptno = b.deptno;

列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数，按照工作岗位分组求最小值

select job,count(*) from emp group by job having min(sal)>1500;

列出部门“SALES”工作的员工姓名，假定不知道销售部的部门编号

SELECT
	ename
FROM
	emp 
WHERE
	deptno = ( SELECT deptno FROM dept WHERE dname = "SALES" );

列出薪金高于公司平均薪金的所有员工、所在部门、上级领导、雇员的工资等级

SELECT
	e.ename,
	d.dname,
	ee.ename manager,
	s.grade 
FROM
	emp e
	LEFT JOIN emp ee ON e.mgr = ee.empno
	JOIN dept d ON e.deptno = d.deptno
	JOIN salgrade s ON e.sal BETWEEN s.LOSAL 
	AND s.HISAL 
WHERE
	e.sal >(SELECT avg( sal )  FROM emp);

列出与“SCOTT”从事相同工作的所有员工及部门名称

SELECT
	ename,
	dname 
FROM
	emp e
	JOIN dept d ON e.deptno = d.deptno 
WHERE
	job = ( SELECT job FROM emp WHERE ename = "SCOTT" ) 
	AND ename != "SCOTT";

列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

SELECT
	ename,
	sal 
FROM
	emp 
WHERE
	sal IN ( SELECT DISTINCT sal FROM emp WHERE deptno = 30 ) 
	AND deptno != 30;

25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称

SELECT
	e.ename,
	e.sal,
	d.dname 
FROM
	emp e
	JOIN dept d ON e.deptno = d.deptno 
WHERE
	e.sal >(SELECT max( sal ) FROM emp WHERE deptno = 30);

列出每个部门工作的员工数量，平均工资和平均服务期限

SELECT
	d.*,
	count( e.ename ),
	ifnull( avg( e.sal ), 0 ),
	avg(
		timestampdiff(
			YEAR,
			hiredate,
		now())) 
FROM
	dept d
	LEFT JOIN emp e ON e.DEPTNO = d.DEPTNO 
GROUP BY
	d.deptno,
	d.dname,
	d.loc;

列出员工的姓名、部门名称和工资

SELECT
	e.ename,
	d.dname,
	e.sal 
FROM
	emp e
	JOIN dept d ON e.DEPTNO = d.DEPTNO

列出所有部门的详细信息和人数

SELECT
	d.*,
	count( e.ename ) 
FROM
	dept d
	JOIN emp e ON d.DEPTNO = e.DEPTNO 
GROUP BY
	d.DEPTNO,
	d.DNAME,
	d.LOC;

列出各种工作的最低工资及从事此工作的雇员姓名

SELECT
	e.ename,
	t.* 
FROM
	emp e
	JOIN ( SELECT job, min( sal ) minsal FROM emp GROUP BY job ) t ON e.sal = t.minsal 
	AND e.job = t.job;

列出各个部门的MANGER（领导）的最低薪金

SELECT
	deptno,
	min( sal ) 
FROM
	emp 
WHERE
	job = "MANAGER" 
GROUP BY
	deptno;

列出所有员工的年工资，按年薪从低到高排序

SELECT
	ifnull( sal, 0 )* 12 
FROM
	emp 
ORDER BY
	sal ASC;

求出员工领导的薪水超过3000的员工名字与领导名字

SELECT
	a.ename,
	b.ename manager,
	b.sal 
FROM
	emp a
	JOIN emp b ON a.mgr = b.empno 
WHERE
	b.sal > 3000;

求出部门名称中，带’S’字符的部门员工的工资合计、部门人数

SELECT
	d.dname,
	count( e.ename ) 
FROM
	dept d
	LEFT JOIN emp e ON e.DEPTNO = d.DEPTNO 
WHERE
	d.dname LIKE '%S%' 
GROUP BY
	d.dname;

给任职日期超过30年的员工加薪10%

UPDATE emp 
SET sal = sal * 1.1 
WHERE
	TIMESTAMPDIFF(
		YEAR,
	HIREDATE,
	now())> 30

16.变量

系统变量

由系统提供，服务器层面，只要服务器不重启，就会一直有效。有以下两种

全局变量
会话变量（将下面的 ‘global’ 换成 ‘session’ 即为会话变量操作）

查看所有的系统变量：show global variables
查看满足条件的部分系统变量：show global variables like '%xx%'
查看指定的某个系统变量的值：select @@global.系统变量名
- select @@global.autocommit
- select @@[session.]transaction_isolation （不写默认显示局部变量）
为某个具体的系统变量赋值
- set global 系统变量名 = 值 or set @@gloabl.系统变量名 = 值
  - set global autocommit = 0

自定义变量

用户自定义，作用域为当前会话（连接）有效，同于会话变量的作用域

自定义变量 - 用户变量

用户变量 - 定义

# 三种方式
set @用户变量名 = 值
set @用户变量名 := 值
select @用户变量名 := 值

用户变量 - 赋值

# 四种方式
set @用户变量名 = 值
set @用户变量名 := 值
select @用户变量名 := 值
select 端字段 into @用户变量名 from 表

自定义变量 - 局部变量

作用域：仅仅在定义它的 begin end 中有效，并且必须放在行首

局部变量 - 定义

declare 变量名 类型 [default 值];

局部变量 - 赋值

set 局部变量名 = 值;
set 局部变量名 := 值
select @局部变量名 := 值
select 字段 into 局部变量名 from 表

17.存储过程和函数

存储过程 procedure

创建

delimiter 变量1
create procedure 存储过程名(参数列表)
begin
		存储过程体（一组合法SQL语句） 
end 变量1

# 参数列表包括三部分：参数模式(in/out/inout) 参数名 参数类型
# in：该参数可以作为输入，也就是该参数需要调用方法传入值
# out：该参数可以作为输出，也就是该参数可以作为返回值
# inout：该参数是以上两种的合体

调用

call 存储过程名(参数列表);

删除

drop procedure [if exists] 存储过程名

查看

show create procedure 存储过程名
show procedure status

函数 function

创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END
# 参数列表包含两部分：参数名 参数类型
# 函数一定有返回语句 RETURN V
# 注意：函数不允许产生查询结果！

查看

show create function 函数名（不带括号）;

删除

drop  function 函数名（不带括号）;

函数 vs 存储过程

存储过程是procedure用户定义的一系列sql语句的集合，涉及特定表或其它对象的任务，用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

18.流程控制结构

分支结构

if 函数 & 结构

# 函数 if
if(表达式a,结果b,结果c)
# 结构 if
if 条件1 then 语句1;
else if 条件2 then 语句2;
...
[else 语句n;]
end if;

case 结构

CASE [变量/表达式/字段] 	# 类似Java switch/if-else
WHEN 要判断的值 THEN 返回的值1;
WHEN 要判断的值 THEN 返回的值2;
WHEN 要判断的值 THEN 返回的值3;
...
ELSE 要返回的值 n;
END ;

例子：

# 1.统计每个班的男生和女生各是多少，统计结果的表头为：班号、男生数量、女生数量
SELECT 班号,
COUNT(CASE WHEN 性别='男' THEN '男' END) 男生数,
COUNT(CASE WHEN 性别='女' THEN '女' END) 女生数
FROM 学生表 GROUP BY 班号

# 2.判断成绩的等级，85-100为“优”，70-84为“良”，60-69为“及格”，60以下为“不及格”，并统计每一等级的人数。
SELECT
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END 等级,     COUNT(*) 人数
FROM SC
GROUP BY
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END

# 3.现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量
SELECT
CASE 
WHEN age < 25 OR age IS NULL THEN '25岁以下' 
WHEN age >= 25 THEN '25岁及以上'
END age_cut,COUNT(*) number
FROM user_profile
GROUP BY age_cut

循环结构

# 1.while
[标签:]while 循环条件 do
	循环体;
end while [标签];
# 标签可以搭配循环控制使用：iterate(continue)/leave(break)

# 2.loop  死循环
[标签:]loop
	循环体;
end loop [标签];

# 3.repeat(do-while)
[标签:] repeat
	循环体;
until 结束循环的条件
end repeat [标签];

游标（光标）

1.声明

declare 游标名 cursor for 查询语句 # MySQL SQL Server DB2
declare 有标明 cursor is 查询语句  # Oracle PostgreSQL

2.OPEN

open 游标名

3.FETCH

fetch 游标名 into 变量1,...

4.CLOSE

close 游标名

19.窗口函数

基本语法如下：

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)
# partition子句可是省略，省略就是不指定分组
# 这就失去了窗口函数的功能，所以一般不要这么使用

专用窗口函数 rank

select *,
   rank() over (partition by 班级 order by 成绩 desc) as ranking
from 班级表

rank & dense_rank & row_number 有什么区别？

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

聚合函数作为窗口函数

select *,
   sum(成绩) over (order by 学号) as current_sum,
   avg(成绩) over (order by 学号) as current_avg,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min
from 班级表

如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算

20.关于自增

自增列必须是键，但不一定非是主键。
一张表只能有一个自增列

自增主键利弊

优点

自增主键执行insert效率高，数据按顺序存储
占空间小，所有二级索引都含有主键并使用主键进行记录查找
物理存储要求表必须有主键，自增int主键开销小，使用便捷

缺点

高并发场景自增Id的生成影响系统性能
该值与业务无关，除了唯一标识一条记录并无太多意义（未必是缺点，正因为没有确定意义，业务变化时不会影响自增主键，从而不影响底层存储顺序）

MySQL :: MySQL 5.7 Reference Manual :: MySQL Glossary

使用UUID为主键？

优点

UUID便于分布式数据库并发插入
业务逻辑不依赖于Id生成，如业务需要通过Id关联多条记录，在自增Id条件下记录必须先行插入之后才能获取Id再行关联。UUID可以线下生成Id并直接关联，不依赖于数据库

缺点

相对自增主键，使得所有二级索引占据更多空间
数据插入效率较低，新插数据可能在索引的中间位置，为将数据插入合适的位置可能需要额外的IO操作，同时造成索引不连续，影响查询效率

总结

业务逻辑中含有自然唯一键值时（如accountId），可以考虑下accountId本身的属性，如果它本身就是个int型，那么就可以直接用来作为主键，如果它本身比较长（比如身份证号），那用来做主键可能会带来一些负面影响。此外，还要考虑使用自增主键可能会影响系统的并发度

建议在建表时默认加上一列自增int主键，至于按自然键值查找的需求，增加对应的二级索引即可。

21. 触发器（能不用就不用）

创建

create trigger 触发器名称
{before|after} {insert|update|delete} on 表名
for each row
触发器执行语句块;

删除

drop trigger [if exists] 触发器名;

22. 数据库三范式

第一范式：确保每列保持原子性
第二范式：确保表中每列都与主键相关
第三范式：确保每列都和主键直接相关，而不是间接相关

MySQL 高级

环境构建

Linux 下 Docker 初步配置

1. 卸载旧版本
sudo yum remove docker \
                  docker-client \
                  docker-client-latest \
                  docker-common \
                  docker-latest \
                  docker-latest-logrotate \
                  docker-logrotate \
                  docker-engine
2. 使用Docker仓库进行安装
sudo yum install -y yum-utils \
  device-mapper-persistent-data \
  lvm2
3. 配置阿里云镜像地址
sudo yum-config-manager \
    --add-repo \
    http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
// 另附官方地址
sudo yum-config-manager \
    --add-repo \
    https://download.docker.com/linux/centos/docker-ce.repo
4. 下载安装
sudo yum install docker-ce docker-ce-cli containerd.io
5. 启动服务 + 开机自启
sudo systemctl start docker
sudo systemctl enable docker
6. 配置docker镜像源
阿里云 -> 容器镜像服务 -> 执行CentOs命令

7. mysql 配置 // 注意这是mysql80
！！一定要本机先创建配置文件！！
vim /mydata/mysql/my.cnf
--------------------------
[mysqld]
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
expire_logs_days=7
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8
--------------------------
docker run --restart=always --privileged=true -p 3306:3306 --name mysql -v /mydata/mysql/log:/var/log/mysql -v /mydata/mysql/data:/var/lib/mysql -v /mydata/mysql/my.cnf:/etc/mysql/my.cnf -v /mydata/mysql/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=666666 -d mysql
  
    
8. mysql 密码设置、远程连接设置和配置文件（字符编码）(上面设置的密码可能不生效，密码或为空)
// 允许外部访问
use mysql; update user set host = '%' where user = 'root'; FLUSH PRIVILEGES;
// 先进入mysql
docker exec -it mysql /bin/bash
// 登录
mysql -uroot -p666666 // 
// 更改密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '666666';

9. docker 配置 redis
// 3步
mkdir -p /mydata/redis/conf
touch /mydata/redis/conf/redis.conf
docker run -p 6379:6379 --name redis -v /mydata/redis/data:/data -v /mydata/redis/conf/redis.conf:/etc/redis/redis.conf -d redis redis-server /etc/redis/redis.conf
// 测试
docker exec -it redis redis-cli
// 持久化配置
vi /mydata/redis/conf/redis.conf
appendonly yes
docker rest
10. 设置卡机自启 
docker update --restart=always mysql
docker update --restart=always redis

目录对应关系

路径	解释
/var/lib/mysql/	mysql 数据库文件的存放路径
/usr/bin/	mysql 相关命令存放路径
/etc/mysql/	mysql 配置文件路径

MySQL体系结构

存储引擎

概述

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可以被称之为表类型

Oracle，SQL Server等数据库只有一种存储引擎，MySQL提供了插件式的存储引擎架构，所以MySQL可以自由选择。查看支持的数据库引擎：show engines，建表时在末尾添加engine = 引擎名指定使用的引擎

各种存储引擎特性

存储引擎简述

InnoDB：如果对事务完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包含很多的更新、删除操作，那么InnoDB是比较合适的选择。InnoDB除了有效的降低由于删除和更新导致的锁定，还可以确保事物的完整提交和回滚，对于类似于计费系统这些要求数据准确性较高的，InnoDB是最合适的选择。
MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事物的完整性、并发性要求不是很高，那么MyISAM非常合适。
MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY通常用于更新不太频繁的小表，用以快速得到访问结果。
MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率，这对于存储诸如数据仓储等VLDB环境十分合适。

InnoDB

InnoDb 逻辑存储结构图

Innodb页结构单体图

InnoDB数据页由以下7部分组成，如图所示：

SQL优化步骤

查看SQL执行频率

# 查看当前增删改查次数
show [global] status like 'Com_______' 
# 查看当前增删改查次数『针对InnoDB』
show global status like 'Innodb_rows_%'

定位低效SQL

慢查询日志

# 注意开启慢查询日志会影响性能
slow_query_log=1
slow_query_log_file="LENVON-slow.log"
long_query_time=10

mysqldumpslow -s r -t 10 (日志文件位置)  -- 得到返回记录集最多的10个SQL
mysqldumpslow -s c -t 10 (日志文件位置)  -- 得到访问次数最多的10个SQL
mysqldumpslow -s t -t 10 -g "left join" (日志文件位置)  -- 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s r -t 10 (日志文件位置) | more  -- 另外建议在使用这些命令时结合 | 和 more 使用，否则条数太多

show processlist：慢查询日志在查询结束以后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用这个命令查看当前MySQL在进行的线程、是否锁表等，可以实时查看SQL执行情况，同时对一些锁表操作进行优化
explain：分析执行计划，详细见下索引章节
show profile：mysql 提供的可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

show profiles  -- 查看历史执行SQL的时间
show profile [cpu,block io] for query n   -- 诊断SQL，n对应上一步的编号
-- 出现以下语句代表有问题
converting HEAP to MyISAM  -- 查询结果太大，内存不够用了往磁盘上搬
creating tmp table  -- 创建临时表
copying to tmp table on disk -- 把内存中临时表复制到磁盘
locked

全局查询日志

开启会影响性能！

# 开启
general_log=1
# 记录日志文件的路径 /path 不代表实际路径
general_log_file=/path 
# 输出格式
log_output=FILE

-- SQL设置开启
set global general_log = 1;
set global log_output = 'TABLE';
-- 开启后，编写的SQL语句，将会记录到MySQL库里的general_log表中，可以使用以下命令查看
select * from mysql.general_log;

索引 Index

索引是什么

官方定义：帮助MySQl高效获取数据的数据结构
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是儿叉的）结构组织的索引。其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引等

优缺点

提高数据检索效率，降低数据库IO成本；
降低数据排序的成本，降低了CPU的消耗。
索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大提高了查询速度，但会降低更新表的速度
索引知识提高效率需要和实际情况反复磨合

索引结构

BTREE 索引：最常见的索引类型，大部分索引都支持B树索引
HASH 索引：只有Memory引擎支持，使用场景简单
R-tree 索引（空间索引）：MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，使用较少
Full-text（全文检索）：MyISAM的一个特殊索引类型，主要用于全文检索，InnoDB从MySQL5.6版本开始支持全文索引

我们平常所说的索引，如没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree树索引，统称为索引

B-Tree

B-Tree又叫做多路平衡搜索树，一颗m叉的BTree特性如下：

树中每一节点最多包含m个孩子
除根节点与叶子节点外，每个节点至少有[cell(m/2)]个孩子
若根节点不是叶子结点，则至少有两个孩子
所有叶子结点都在同一层
每个非叶子节点都由n个key与n+1个指针组成，其中[cell(m/2)-1] <= n <= m-1

以5叉BTree为例，key的数量：公式推导[cell(m/2)-1]<=n<=m-1。所以 2<=n<=4，当n>4时，中间节点分裂到父节点，两边节点分裂。

插入 CNGAHEKQMFWLTZDPRXYS 数据为例：

B+Tree

n 叉的B+Tree最多含有n个key，而BTree最多含有n-1个key
B+Tree的叶子节点保存所有的key信息，依key大小顺序排列
所有的非叶子节点都可以看做是key的索引部分

由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子，所以B+Tree查询效率比较稳定

MySQL中的B+Tree

MySQL对B+Tree进行了优化，在原有的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree

索引分类

单列索引：一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：一个索引包含多个列

基本语法

-- 方式之一
-- 创建
CREATE  [UNIQUE|FULLTEXT|SPATIAL|PRIMARY] INDEX indexName [USING index_type]
ON tableName(columnName(length),...);
-- 删除
DROP INDEX indexName ON mytable;
-- 查看
SHOW INDEX FROM tableName

-- 方式之二
-- 创建
Alter tableName ADD [PRIMARY|UNIQUE|FULLTEXT|SPATIAL] INDEX indexName ON (columnName(length))

Explain

id

表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高

select_type

SIMPLE：简单的select查询，查询中不包含子查询或者UNION
PRIMARY：查询中若包含任何的子部分，最外层查询则被标记为
SUBQUERY：在SELECT或WHERE列表中包含了子查询
DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT：从UNION表获取结果的SELECT

table

查询的表名

type

显示查询使用了何种类型，从好到差依次是： system > const > eq_ref > ref > range > index > ALL

null：不访问任何表，直接取值
system：表只有一行记录（等于系统表），这是const类型的特立，平时不会出现，这个也可以忽略不计
const：表示通过索引一次就找到了，const 用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，它本质上也是一种索引访问，它返回所有匹配某个单独值的行，然后，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引比全表扫描要好
index：Full Index Scan，index与ALL的区别在于index类型只遍历索引树，通常会比ALL快，因为索引文件通常比数据文件小。也就是说虽然all和index都是读全表，但是index是从索引中读取的，而all是从硬盘中读取的
all：Full Table Scan，将遍历全表以找到匹配的行

possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引（查询已建立索引的字段），则该索引仅出现在key列表中

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref

显示索引的哪一列被使用了，如果可能，是一个常数。哪些列或常量被用于查找索引列上的值

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

extra

Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作称为“文件排序”
Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
- 一般出现在 where 与 group by 后面字段不一致的情况下，如：explain select status from tb_item where title in('3','2') group by status;
Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率很高。如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表明通过索引来读取数据而非执行查找动作
- using index condition：会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；
Using where：使用了 where 过滤
Using join buffer：使用了连接缓存
Impossible where：where 子句的值总是false，不能用来获取任何元组
Selsect tables optimized away：在没有GROUPBY子句的情况下使用，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值得动作

避免索引失效

计算、函数、类型转换、左模糊、OR没有被索引全包、违反最左前缀法则、is not null、不等于

1.最左前缀法则

如果索引了多列，where 查询条件（ = ）应该按照索引的顺序查询，可以缺少后面的列或添加没有索引的列，不能跳过前面的列。

order by 配合 where 时尽量不要跨过中间的列，会导致 filesort

2.范围后面的索引失效（自己是生效的）

范围查询：>、<、>=、<=、!=、<>

3.索引列上使用运算操作索引失效

4.字符串类型必须加单引号

MySQL会类型为varchar的没加引号的数字进行隐式类型转换，导致索引失效

5.尽量使用覆盖索引，避免 select *

这种情况下，using index ; using where 比 using index condition 更快

6.对于 or 两边必须使用两个不同的索引，或是其中一边的索引对另一边有覆盖，否则索引失效

UNION替换 or 也是一种解决方案，可以解决 or 两边使用的索引是同一个复合索引且没有覆盖关系的情况

7.like 百分号写右边，不得不的情况应使用覆盖索引避免全表扫描

针对 where 后面只有一个 like 的情况准确，这个字段应被索引

8.如果MySQL评估使用索引比全表扫描更慢，则不会使用索引

这个跟存储的数据有关！查询条件对应数据较多不走索引，查询条件对应数据较少走索引

9.is [not] null 可能会导致索引失效

仍然跟存储的数据有关，null值多 is not null 走索引，null值少 is null 走索引

10.in 索引可能失效，not in 索引失效

11.单列索引 vs 复合索引

优化案例

单表案例

select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;   -- 需求SQL 

create index idx_article_ccv on article(category_id,comments,views);   -- 错误建法
drop index idx_article_ccv on article;
 
create index idx_article_cv on article(category_id,views);   -- 正确建法

双表案例

select * from class left join book on class.card = book.card;  -- 左连接SQL
alter table book add index Y (card);  -- 左连接索引建右表

select * from class right join book on class.card = book.card;  -- 右连接SQL
alter table book add index Y (card);  -- 右连接索引建左表

三表

接以上两表

SELECT * FROM class
	LEFT JOIN book ON class.card = book.card
	LEFT JOIN phone ON book.card = phone.card
	
alter table book add index Y (card);  
alter table phone add index Z (card);  

-- 索引最好建立在经常查询的字段上
-- 尽量减少Join语句中的NestedLoop的循环次数 “永远用小节果集驱动大的结果集”
-- 优先优化NestedLoop的内层循环
-- 保证Join语句中被驱动表上Join条件字段已经被索引
-- 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

几种优化案例

Order By 索引优化

尽量减少排序字段，多字段排序字段尽量都是升序或是降序
order by 配合 where 组成最左前缀法则，单独 order 则会失效！
- where -> order by / select

排序字段使用索引的情况下，select 字段尽量都使用覆盖索引，否则导致filesort

Filesort 的优化

show variables like 'max_length_for_sort_data'
show variables like 'sort_buffer_size'

Group By 索引优化

group by 实质是先排序后分组，group by 字段涉及索引就会生效！(sum、avg字段是索引的情况下)
不想排序只分组：order by null
where 高于 having ，能写在 where 限定的条件就不要去 having 限定

嵌套查询优化

很多情况下，子查询效率不如JOIN

条件OR优化

对于 or 两边必须使用两个不同的索引，或是其中一边的索引对另一边有覆盖，否则索引失效

UNION替换 or 也是一种解决方案，可以解决 or 两边使用的索引是同一个复合索引且没有覆盖关系的情况

分页查询优化

这样一条sql：limit 200000,10 对MySQL性能浪费过高

优化思路一

在索引上完成排序操作，最后根据主键关联，例如

select * from tb_item t,
(select id from tb_item order by id limit 200000,10) a 
where t.id = a.id

优化思路二

该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询，这种方案最快

select * form tb_item where id > 2000000 limit 10

使用索引提示

SQL提示，是优化数据库的一个重要手段，就是在SQL语句中加入一些人为的提示来达到优化操作的目的

# 指定使用的索引，非强制性
select * from 表名 use index(索引名)
# 忽略一个或多个索引
select * from 表名 ignore index(索引1,索引2...)
# 强制使用某个索引
select * from 表名 force index(索引名)

索引条件下推

ICP（Index Condition Pushdown）是在MySQL 5.6版本上推出的查询优化策略，把本来由Server层做的索引条件检查下推给存储引擎层来做，以降低回表和访问存储引擎的次数，提高查询效率。

如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给MySQL服务器，由MySQL服务器评估where后面的条件是否保留行；
启用ICP后，如果部分where条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分where条件放到存储引擎筛选，然后，存储引擎通过使用索引条目来筛选数据，并且只有满足这一条件时才从表中读取行。

一文读懂什么是MySQL索引下推（ICP） - 简书 (jianshu.com)

五分钟搞懂MySQL索引下推 - 掘金 (juejin.cn)

参数优化

基本思路

1.使用连接池

对于数据库来说，建立连接的代价是比较昂贵的，因此有必要使用数据库连接池

2.减少对MySQL的访问

避免对数据进行重复检索

在编写应用代码时需要能够清理对数据库的访问逻辑。能够一次性连接获取到结果的，就不用两次连接，这样可以大大减少重复请求

增加cache层

缓存有很多实现方式，如MyBatis框架所提供的一级/二级缓存、redis作为缓存等等

3.负载均衡

通过MySQL的主从复制，实现读写分离

增删改操作走主节点，查询操作走从节点，可以降低单台服务器的读写压力

采用分布式数据库架构

分布式数据库架构适合大数据量、负载高的情况，它有良好的扩展性和高可用性。通过在多台服务器之间分布数据，可以实现在多台服务器之间的负载均衡，提高访问效率。

查询缓存

开启MySQL的查询缓存，当执行完相同的SQL语句时，服务器就会从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。注意MySQL8.0已被废弃

参数查看

查看当前数据库是否支持缓存

show variables like 'have_query_cache'

查看当前MySQL是否开启了查询缓存

show variables like 'query_cache_type'

查看查询缓存大小（单位字节）

show variables like 'query_cache_size'

查看查询缓存占用大小

show status like 'Qcache%'

查看数据库查询、插入情况

show global status like "Com_______";

开启查询缓存

MySQL的查询缓存默认是关闭的，需要手动配置 query cache type 来开启查询缓存。query_cache_type该参数的值可能有三个：

在 /usr/my.cnf (linux)中配置以下参数，配置完毕重启即可生效

# 开启mysql的查询缓存
query_cache_type = 1

SELECT选项

可以在SELECT语句中指定两个与查询缓存相关的选项：

SQL_CACHE：如果查询结果是可缓存的，并且query_cache_type 系统变量的值为ON或DEMAND，则缓存查询结果

SQL_NO_CACHE：服务器不使用查询缓存。它既不检查缓存，也不检查结果是否缓存，也不缓存查询结果

select SQL_CACHE id, name from customer;
select SQL_NO_CACHE id , name from customer;

查询缓存失效的情况

SQL语句不一致
查询语句中有一些不确定，如now(),current_date()…
不使用任何查询语句
查询MySQL, information_schema 或 performance_schema 数据库中的表
在存储的函数，触发器或事件的主体内执行的查询
如果表更改，则使用该表的所有高速缓存查询都将被删除

内存管理及优化

优化原则

将尽量多的内存分配给MySQL做缓存
MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留给更多的内存给操作系统做IO缓存
排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽

MyISAM内存优化

MyISAM存储引擎使用 key_buffer 缓存索引块，加速MyISAM索引的读写速度。对于MyISAM表的数据块，MySQL没有特别的缓存机制，完全依赖与操作系统的IO缓存

key_buffer_size

决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率，可以在MySQL参数文件中设置key_buffer_size的值

对于一般的MyISAM，建议至少将1/4的可用内存分配给它

key_buffer_size = 512M

read_buffer_size

如果需要经常顺序扫描MyISAM表，可以通过增大read_buffer_size的值来改善性能，但需要注意的是read_buffer_size是每个session独占的，如果设置过大，会导致内存浪费

read_rnd_buffer_size

对于需要做排序的MyISAM表的查询，如带有order by子句的SQL，适当增加read_rnd_buffer_size的值，可以改善此类SQL性能，但需要注意的是read_rnd_size仍然是每个session独占的

InnoDB内存优化

InnodB用一块内存区做IO缓存池，该缓存池不仅用来缓存索引块，也用来缓存数据块

innodb_buffer_pool_size

该变量决定了InnoDB存储引擎表数据或索引数据的最大缓存区大小。在保证操作系统及其他应用程序有足够的内存可用的情况下，innodb_buffer_pool_size值越大，缓存命中率越高，访问InnoDB表需要的磁盘IO就越少，性能也就越高

innodb_buffer_pool_size = 512M

innodb_log_buffer_size

决定了重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作

innodb_log_buffer_size = 10M

并发参数调整

从实现上来说，MySQL Server是多线程结构的，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。

max_connections

允许按连接到MySQL数据库的最大数量，默认151。如果状态变量 connectino_errors_max_connections 不为零，且一直增长，则说明并发连接数过多，可以考虑增大 max_conncetions 值

MySQL最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux平台下，性能好的服务器，支持500-1000个连接是应该的。

back_log

控制MySQL监听TCP端口时设置的挤压请求栈大小。如果MySQL的连接数达到max_connections时，新来的请求将会被堆在栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接，会报错。5.6.6之前的默认值是50，之后默认为50+（max_conncetions / 5），最大不超过900

table_open_cache

控制所有SQL语句执行线程可打开表缓存的数量，而在执行SQL语句时，每一个SQL执行线程至少要打开1个表缓存。该参数的值应该根据设置的最大连接数以及每个连接执行关联查询中涉及的表的最大数量来决定：max_connections * N

thread_cache_size

为了加快数据库的速度，MySQL会缓存一定数量的客户服务器线程以备重用

innodb_lock_wait_timeout

该参数是用来设置InnoDB事务等待行锁的时间，默认50ms。对于需要快速反馈的业务系统来说，可以将该参数设置小，避免事务长时间挂起；对于后台运行的批处理程序来说，可以将该参数调大，避免发生大的回滚操作。

实践优化

1.查看性能参数

一些常用的性能参数如下：

• Connections：连接MySQL服务器的次数。

• Uptime：MySQL服务器的上线时间。

• Slow_queries：慢查询的次数。

• Innodb_rows_read：Select查询返回的行数

• Innodb_rows_inserted：执行INSERT操作插入的行数

• Innodb_rows_updated：执行UPDATE操作更新的行数

• Innodb_rows_deleted：执行DELETE操作删除的行数 • Com_select：查询操作的次数。

• Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。

• Com_update：更新操作的次数。

• Com_delete：删除操作的次数。

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

2.explain

SHOW STATUS LIKE 'last_query_cost';  # explain上一条sql
explain ...

3.慢查询日志

# 临时开启（重启失效）
set global slow_query_log='ON';
set global long_query_time=1;   # 1s为慢查询sql
show variables like 'slow_query_log_file';    # 查看日志文件位置

# 永久开启
[mysqld]
slow_query_log = ON
long_query_time = 1
# slow_query_log_file = /var/lib/mysql/tmp_slow.log     //可选


SHOW GLOBAL STATUS LIKE '%Slow_queries%';  # 查看有多少条慢sql

# 个人习惯  cat /opt/mysql/data/2397e8f16638-slow.log

4.查看 SQL 执行成本：SHOW PROFILE

# 临时开启（重启失效） 注意这个优化手段需要在会话窗口手动执行一遍sql，才会有记录
set profiling = 'ON';
show variables like 'profiling';  # 校验
show profiles; 
show profile cpu,block io for query 2;  # 2是query_id

4.SHOW WARNINGS

# 仍然需要在会话中重现sql
show warnings

5.trace

SET optimizer_trace="enabled=on",end_markers_in_json=on; 
set optimizer_trace_max_mem_size=1000000;
# 这个工具暂时没有使用过
# 仍然需要在会话中重现sql
select * from information_schema.optimizer_trace;

6.MySQL监控分析视图-sys schema

# 索引相关
#1. 查询冗余索引
select * from sys.schema_redundant_indexes;
#2. 查询未使用过的索引 
select * from sys.schema_unused_indexes;
#3. 查询索引的使用情况 
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='mall_pms'; # 自定义dbname

# 表相关
# 1. 查询表的访问量 
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查询占用bufferpool较多的表 
select object_schema,object_name,allocated,data from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 查看表的全表扫描情况 
select * from sys.statements_with_full_table_scans where db='mall_pms';

# 语句相关
#1. 监控SQL执行的频率 
select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
#2. 监控使用了排序的SQL 
select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1; 
#3. 监控使用了临时表或者磁盘临时表的SQL 
select db,exec_count,tmp_tables,tmp_disk_tables,query from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

# IO相关
#1. 查看消耗磁盘IO的文件 
select file,avg_read,avg_write,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_read limit 10;

# Innodb 相关
#1. 行锁阻塞情况 
select * from sys.innodb_lock_waits;

MySQL 锁机制

查看锁

# 查看所有表的锁定情况
show open tables 
# 查看表的锁定和等待次数
show status like 'Table_locks%';

锁分类

从对数据操作的粒度分类：

表级锁：操作时，会锁定整个表
- 读锁（共享锁）
- 写锁（独占锁）
- 元数据锁（MDL）
- 意向锁
行级锁：操作时，会锁定当前操作行
- 行锁（Record Lock）
- 间隙锁（Gap Lock）
- 临键锁（Next-Key Lock）
- 插入意向锁
页锁

从对数据操作的类型分：

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会相互影响
写锁（排它锁）：当前操作没有完成，会阻塞其它锁

偏向于MyISAM存储引擎，开销小，加锁快；锁定粒度大，发生锁冲突的概率最高，并发度最低

表级锁

-- 查看当前锁
show open tables [where in_use>0]; 
-- 加锁示例
lock table tableName read|write;  
-- 解锁
unlock tables;

读锁（共享锁）

loca tables tableName read

一张表加了读锁则任何客户端都不能有修改操作，自己修改会报错，别的修改会阻塞
不影响读操作，可以同时被别的客户端加锁
加锁会话不能再去访问别的表

写锁（独占锁）

loca tables tableName write

一张表加了读锁则自己可以读写，阻塞别的会话的读写
只有拿到这张表的锁才能对其进行操作
加锁会话不能再去访问别的表

简而言之，读锁会阻塞读写，但不会阻塞读，而写锁会阻塞读和写

此外，MyISAM的读写调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。

元数据锁（MDL）

主要是为了防止表结构修改与增删改查的冲突（DDL语句与DML语句的冲突）

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

意向锁

如果我们给某一行加上了锁，数据库会自动给更大一级的空间加锁，主要是为了解决行锁与表锁的冲突问题（不必一行一行扫描是否有锁）

意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排它锁（write）互斥
意向排它锁（IX）：与表锁共享锁（read）及排它锁（write）都互斥。意向锁之间不互斥

行级锁

针对InnoDB引擎，行锁开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁

行锁（Record Lock、记录锁）

select ... lock in share mode;
select ... for share  # 读锁  8.0新增  nowait/skip locked
select ... for update  # 写锁

锁定单个行记录的锁，防止其他事物对此进行update和delete。在RC、RR隔离级别下都支持。

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排它锁（X）：允许获取排它锁的事务更新数据，阻止其他事物获得相同数据集的共享锁和排它锁。

注意：InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么将升级为表锁。

间隙锁（Gap Lock）

show variables like 'innodb_locks_unsafe_for_binlog';   # 默认OFF，启用

锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持。

索引上的等值查询（唯一索引），给不存在的记录加锁时。优化为间隙锁；
索引上的等值查询（普通索引），向右遍历第一个值不满足查询需求时，next-key lock退化为间隙锁；
索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止。

插入意向锁

InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。InnoDB就把这种类型的锁命名为 Insert Intention Locks。插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

页锁

页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。**页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。**每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

事务

事务是由一组SQL组成的逻辑处理单元，事务具有以下四个属性，通常称为事务的ACID属性

并发事务处理所带来的问题

事务隔离级别

读数据—致性及允许的并发副作用（隔离级别）	读数据一致性	脏读	不可重复读	幻读
未提交读（read uncommitted）	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交读（read committed）	语句级	否	是	是
可重复读（repeatable read）	事务级	否	否	是
可序列化（serializable）	最高级别，事务级	否	否	否

查看当前数据库的事务隔离级别：select @@transaction_isolation; ;

设置事务隔离级别：set global transaction isolation level REPEATABLE READ;

RedoLog

刷盘策略 innodb_flush_log_at_trx_commit

设置为0 ：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日志的同步）
设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（默认值）
设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。

UndoLog

两个作用：

提供回滚(保证事务的原子性)
MVCC(多版本并发控制)

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment回滚段中，内部包含1024个undo log segment。

MVCC ※※

MVCC 全称 Multi-Version Concurrency Control，多版本并发控制。用以无锁解决读写冲突问题。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode、select ... for update、update、insert、delete 都是一种当前读

快照读

简单的select（不加锁）就是快照读，读取的是记录的可见版本，有可能是历史数据，不加锁，是非阻塞读

Read Committed：每次select，都生成一个快照读；
Repeatable Read：开启事务后第一个select语句才是快照读的地方；
Serializable：快照读退化为当前读。

隐藏字段

undo log

回滚日志，在insert、update、delete时产生的便于数据回滚的日志。当insert时，产生的undolog日志只在回滚时需要，在事务提交后，即可删除，而update、delete时产生的回滚日志，不仅在回滚时需要，在快照读时也需要，不会立即被删除。

undo log 版本链

readview

ReadView（读视图）是快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id

不同的隔离级别，生成ReadView的时机不同：

READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

常用原生命令及日志

常用工具

1.mysql

mysql [options]  [database]

options

参数：
	-u	# 账号
	-p  # 密码
	-h	# 服务器IP或域名
	-p	# 端口号

执行选项

-e  # 执行SQL语句而不必进入其命令行
# 例
mysql -uroot -p123 db01 -e "select * from emp"

这样的方式一般用于批处理脚本之中

2.mysqladmin

mysqladmin 是一个执行管理操作的客户端程序，它可以用来检查服务器的配置和当前状态、创建并删除数据库等等。使用 --help 查看支持的命令

例如：mysqladmin -uxxx -pxxx drop 'db01'

3.mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以要想查看这些日志，就会用到该命令

语法：

mysqlbinlog [options] log-files1 log-files2 ...
options:
		-d   --database=name # 指定数据库
		-o  --offset=n  # 忽略掉日志中的前 n 行命令
		-r  --result-file=name # 将输出的文本格式日志输出到指定文件
		-s  --short-form # 显示简单格式
		--start-datetime=date1 --stop-datetime=date2 # 指定日期间隔内的所有日志
		--start-opsition=pos1 --stop-position=pos2 #指定位置间隔内的所有日志

4.mysqldump

用于备份数据库或在不同数据库之间进行数据迁移，备份内容包含创建表，插入表等SQL语句

语法：

mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1[db2 db3 ...]
mysqldump [options] --all-database/-A
options:
	-u  -p  -h  -p ...
	--add-drop-database  # 在每个数据库创建语句前加上 drop if exists 语句
	--add-drop-table  # 在每个表的创建语句上加上 drop if exists（默认开启）；想要不开启：（--skip-add-drop-table）
	-n  --no-create-db  # 不包含库的创建语句
	-t  --no-create-info   # 不包含表的创建语句
	-d  --no-data 	# 不包含数据
	-T  --tab=name  # 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件。数据文件，相当于select into outfile

示例：
mysqldump -uroot -pxxx db1 tb_book > tb_book.sql # 导出表
mysqldump -uroot -pxxx db1  > tb_book.sql # 导出库
mysqldump -uroot -pxxx -T /tmp db1 tb_book # 分别导出结构和数据

5.mysqlimport/source

用来导入mysqldump -T后生成的文件

语法：

mysqlimport [options] db_name textfile1 [textfile2...]
# 示例：
mysqlimport -uroot -p123 db1 /tmp/tb_book.txt   导入数据

# 如果需要导入.sql文件(不一定有数据)
source /root/tb_book.sql

6.mysqlshow

客户端对象查找工具，用来查找存在哪些数据库、数据库中的表、表中的列或者索引

语法：

mysqlshow [options] [db_name [col_name]]
options:
	--count   # 显示数据库及表的统计信息（数据库、表均可不指定）
	-i    		# 显示指定数据库或者指定表的状态信息

# 示例
mysqlshow -uroot -p123 --count     # 
mysqlshow -uroot -p123 test --count
mysqlshow -uroot -p123 test book --count

日志

在MySQL中，有四种不同的日志，分别是错误日志、二进制日志（BINLOG日志）、查询日志和慢查询日志

错误日志

记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生了任何严重错误时的相关信息。当数据库出现严重错误时，可以优先查看此日志。

该日志默认开启，默认存放目录为（var/lib/mysql），默认日志文件名为 hostname.err（hostname-主机名）

查看日志位置指令：

show variables like 'log_error%'
# mac 结果
# /usr/local/mysql/data/mysqld.local.err

二进制日志

记录了所有的DDL（数据定义语言）和DML（数据操纵语言），但不包括查询语句，此日志对于故障时数据恢复起着极其重要的作用，MySQL主从复制，就是通过该binlog实现。

默认没有开启，需要在配置文件中开启：

# 开启并设置日志文件名为 mysqlbin.xxx
log_bin=mysqlbin	
# 日志格式
binlog_format=STATEMENT

日志格式

STATEMENT

该日志格式在日志文件中记录的都是SQL语句（statement），对每一条数据进行修改的SQL都会记录在日志文件中，通过MySQL提供的mysqlbinlog工具，可以清晰地看到每条语句的文本，主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。

该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。

MIXED

默认格式，即以上两者的混合。默认

查看日志内容

sudo mysqlbinlog mysqlbin.000001

删除

# 全部删除
reset master;
# 删除 xxxxxx 编号之前的所有日志 
purge master logs to 'mysqlbin.xxxxxx'
# 删除以下具体时间之前的所有日志
purge master logs before 'yyyy-mm-dd hh24:mm:ss'
# 设置参数 expire_logs_days=# 过期时间
expire_logs_days=3

查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询语句。

默认关闭，如需开启：

# 开启，可选值：0 or 1
general_log=1
# 设置日志文件名，默认 host_name.log
general_log_file=file_name

慢查询日志

记录了所有执行时间超过long_query_time设置值并且扫描记录不小于min_examined_row_limit的所有的SQL语句的日志。

默认关闭，如需开启：

# 1开启，0关闭
slow_query_log=1
# 指定慢查询日志文件名
slow_query_log_file=slow_query.log
# 时间阈值，默认10s
long_query_time=10
# 扫描行阈值，默认0
min_examined_row_limit=n

主从复制

架构图

环境搭建

一主一从常见配置，Windows主，linux从

1.主机修改my.ini配置文件

[mysqld]
# [必须]集群唯一ID
server-id=1
# [必须]启用二进制日志
log-bin=自己本地的路径/mysqlbin
# log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin

# [可选]启用错误日志
log-err=自己本地的路径/mysqlerr
# log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr
# [可选]根目录
basedir=“自己本地路径”
# basedir=“D:/devSoft/MySQLServer5.5/”
# [可选]临时目录
tmpdir=“自己本地路径”
# tmpdir=“D:/devSoft/MySQLServer5.5/”
# [可选]数据目录
datadir=“自己本地路径/Data/”
# datadir=“D:/devSoft/MySQLServer5.5/Data/”

# [可选]主机读写方式
read-only=0
# [可选]设置不要复制的数据库
binlog-ignore-db=mysql
# [可选]设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字

2.在Windows主机上建立帐户并授权slave

GRANT REPLICATION SLAVE ON *.* TO ‘zhangsan’@‘从机器数据库IP’ IDENTIFIED BY ‘666666’;
flush privileges; 	-- 刷新
-- 查询master的状态,记录下File和Position的值
show master status;
-- 执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

3.【如必要】配置文件，请主机+从机都重启后台mysql服务，关闭防火墙

# windows手动服务重启
service mysql restart
#关闭虚拟机linux防火墙 
service iptables stop

4.从机修改my.cnf配置文件

# server-id 避免重复
server-id=2
# 指定logbin日志
log-bin=/var/lib/mysql/data/mysqlbin

5.从机上配置需要复制的主机

CHANGE MASTER TO 
MASTER_HOST=’主机IP’, 
MASTER_USER=‘主机用户名’, 
MASTER_PASSWORD=’666666’, 
MASTER_LOG_FILE='File名字’,MASTER_LOG_POS=Position数字';

start slave;

show slave status\G

-- 下面两个参数都是Yes，则说明主从配置成功!
-- Slave_IO_Running：Yes
-- Slave_SQL_Running：Yes

停止从服务复制功能：stop slave ，停止之后注意主机Position数字会变化

其它问题

主从延迟： MySQL 主从同步延迟的原因及解决办法_BrickCarrier的博客-CSDN博客

项目优化案例

分页查询优化

优化count

创建一张表用来记录日志表的总数据量：

create table log_counter(
  longcount bigint not null
)engine = innodb default charset = utf8;

每次插入数据后，更新该表。在进行分页查询时，获取总记录数，从该表中查询即可。

这种方式的不足在于条件查询无法使用

优化limit

在进行分页时，一般通过建立索引，能够比较好的提高性能。但是在这种场景下“limit 100000,10”是一个问题，因此，可以采用以下方式解决：

select field1... from tableName a,
(select id from tableName order by id limit 300000,id) b 
where a.id = b.id;

索引优化

针对查询条件字段建立复合索引（阶梯式建立，防止索引失效）
如果有排序需求，还需要对排序字段建立适当的索引

读写分离

核心问题在于请求的分流。对于SSM项目，可以通过SpringAOP框架拦截请求实现该逻辑。

SpringAOP实现

properties 配置读写数据源
datasource.xml 配置读写数据源和 数据源选择bean（该bean[ChooseDataSource]需要继承AbstractRoutingDatasource）

实现DataSourceHandler类逻辑

实现ChooseDataSource类逻辑

实现DataSourceAspect类逻辑

原理：https://www.bilibili.com/video/BV1UQ4y1P7Xr?p=117&spm_id_from=pageDriver

应用优化

缓存

可以在业务系统中使用redis来做缓存，缓存一些基础性的数据，来降低关系型数据库的压力，提高访问效率

全文检索

如果业务系统中的数据量比较大（千万级别）。此时，如果再对数据库进行查询，特别是分页查询，速度将不可避免的变慢（首先需要count），为了提高访问效率，此时，可以考虑加入 Solr 或者 ElasticSearch 全文检索服务，来提高访问效率

非关系型数据库

也可以考虑将非核心（重要）数据，存在MongoDB中，这样可以提高插入及查询效率

附

大量数据SQL测试数据

优化导入（针对InnoDB）：

主键顺序插入
关闭唯一性校验：set unique_checkes=0，用完记得开
手动提交事务：set autocommit=0

-- 建表
create table tb_item
(
    id         int auto_increment
        primary key,
    title      varchar(100)   not null,
    price      decimal(20, 2) not null,
    num        int            not null,
    categoryid bigint         not null,
    status     varchar(1)     null,
    sellerid   varchar(50)    null,
    createtime datetime       null,
    updatetime datetime       null
);

-- 存储过程
create
    definer = root@localhost procedure insert_tb_item(IN num int)
begin
while num <= 10000000 do
insert into tb_item values(num,concat('货物',num,'号'),round(RAND() * 100000,2),FLOOR(RAND() * 100000),FLOOR(RAND() * 10),'1','5435343235','2019-04-20 22:37:15','2019-04-20 22:37:15');
set num = num + 1;
end while;
end;

猜你喜欢

IBM Watson健康与美国糖尿病协会结成合作伙伴
【theano】tutorial
明智行动的艺术2
大数据运维之MySQL备份及恢复
学会用数据分析汇报工作，升职加薪指日可待
CentOS7.2安装python2.7.12
字节开源视频动画特效 SDKAlphaPlayer
放苹果 POJ - 1664 递推
【数据库开发】MySQL命令大全
js页面跳转整理
《汇编语言程序设计》余朝琨课后习题参考答案
预约中，2022京东云产业融合新品发布会线上开启
datatables：如何禁用一列的排序
IDEA的查询引用、调用关系图的功能
KMP算法（A + B for you again—HDU - 1867 ）
密码破解技术分类与工具使用
未来IT发展趋势预测：BYOD、大数据上榜
【懒人专属】通用RecylerAdapter，内置XRecyclerView，兼容上下拉与动画，高复用，通用所有页面，支持空页
连接postgres特别消耗cpu资源而引发的PostgreSQL性能优化考虑
进入MFC讲坛的前言(四)
1250：The Castle 2021-01-05

相关主题

3.MySQL(三)
【mysql】mysql批量修改
Mysql--函数
vs------连接MySQL
MySQL 字段类型
mySQL笔记1
mysql临时表
MySQL之二