01mysql

最新推荐文章于 2024-07-15 19:44:21 发布

monkey15332675317

最新推荐文章于 2024-07-15 19:44:21 发布

阅读量251

点赞数

分类专栏： Java笔记文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/weixin_49041068/article/details/122166694

版权

Java笔记专栏收录该内容

16 篇文章 1 订阅

订阅专栏

一. MySQL基础篇

(一) Linux(CentOS 7)安装MySQL 5.6

卸载

1 查看MySQL软件

rpm -qa|grep mysql 
yum repolist all | grep mysql

2 卸载

yum remove -y mysql mysql-libs mysql-common #卸载mysql 
rm -rf /var/lib/mysql #删除mysql下的数据文件 
rm /etc/my.cnf #删除mysql配置文件 
yum remove -y mysql-community-release-el6-5.noarch #删除组件

安装

1 下载MySQL

#下载rpm文件
wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
#执行rpm源文件
rpm -ivh mysql-community-release-el6-5.noarch.rpm

2 执行、启动MySQL

#执行安装文件
yum install mysql-community-server
#启动MySQL
systemctl start mysqld

3 设置密码（将 root 账号设置密码为 root ）

#没有原密码
/usr/bin/mysqladmin -u root password 'root'
#有原来的密码，则加（一般）
/usr/bin/mysqladmin -u root -p '123' password 'root'

4 登录MySQL

mysql -uroot -proot
#-u：指定数据库用户名
#-p：指定数据库密码，记住-u和登录密码之间没有空格

5 修改配置

vim /etc/my.cnf

注：配置文件

[mysqld]
# MySQL设置大小写不敏感：默认：区分表名的大小写，不区分列名的大小写
# 0：大小写敏感 1：大小写不敏感
lower_case_table_names=1
# 默认字符集
character-set-server=utf8

6 MySQL远程连接授权（grant 权限 on 数据库对象 to 用户）

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; 
#赋予root用户对所有数据库对象的全部操作权限：
FLUSH PRIVILEGES;   
#刷新权限

命令说明：

	ALL PRIVILEGES :表示授予所有的权限，此处可以指定具体的授权权限。
	*.* :表示所有库中的所有表
	'root'@'%' ： myuser是数据库的用户名，%表示是任意ip地址，可以指定具体ip地址。
	IDENTIFIED BY 'mypassword' ：mypassword是数据库的密码。

7 关闭linux的防火墙

systemctl stop firewalld          		（默认）
systemctl disable firewalld.service   	（设置开启不启动）

8 客户端远程访问

利用navicat可以远程访问MySQL

注：如果连接不上，可以按以下步骤排错
1、MySQL是否正常启动
[root@localhost ~]# ps -ef | grep mysql
root 1114 1 0 10:21 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe -
-datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pidfile=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 1698 1114 0 10:21 ? 00:00:03 /usr/sbin/mysqld

2、查看防火墙是否关闭
[root@localhost ~]# systemctl status firewalld
firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled)
Active: inactive (dead)

3、查看root权限为所有ip都可以访问
mysql> show grants for root;
+-------------------------------------------------------------------------------
-------------------------------------------------+
| Grants for root@%
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD
'*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |
+-----------------------------------------------------------------

4、服务器与客户端是否可以ping通
ping 192.168.239.129
正在 Ping 192.168.239.129 具有 32 字节的数据:
来自 192.168.239.129 的回复: 字节=32 时间<1ms TTL=64

5、客户端是否可以telnet到服务器端
telnet 192.168.239.129 3306

6、Navicat是否正确安装

(二) SQL语言分类

- 数据定义语言：简称【DDL】(Data Definition Language)，用来定义数据库对象：数据库，表，列 等。关键字：create，alter，drop等 

- 数据操作语言：简称【DML】(Data Manipulation Language)，用来对数据库中表的记录进行更 新。关键字：insert，delete，update等 

- 数据控制语言：简称【DCL】(Data Control Language)，用来定义数据库的访问权限和安全级别， 及创建用户；关键字：grant等 

- 数据查询语言：简称【DQL】(Data Query Language)，用来查询数据库中表的记录。关键字： select，from，where等

DDL语句

1.数据库操作：database

#创建数据库
create database 数据库名; 
create database 数据库名 character set 字符集

#查看所有的数据库
show databases;
#查看指定的数据库
show create database 数据库名;

#删除数据库
drop database 数据库名称;

#选择（切换）要使用的数据库
use 数据库名;

#查看当前使用的数据库
select database();

2.表操作：table

#创建表
create table 表名( 
    字段名 类型(长度) 约束, 
    字段名 类型(长度) 约束 
);

#约束条件(主键约束 = 唯一约束 + 非空约束)
- 主键约束：primary key 
- 唯一约束：unique 
- 非空约束：not null

#查看所有的表
show tables; 
#查看表结构
desc 表名;

#删除表
drop table 表名;

#修改表
alter table 表名 add 列名 类型(长度) 约束; 				   --修改表添加列. 
alter table 表名 modify 列名 类型(长度) 约束; 			   --修改表修改列的类型长度及约束. 
alter table 表名 change 旧列名 新列名 类型(长度) 约束; 		 --修改表修改列名. 
alter table 表名 drop 列名; 								--修改表删除列. 
rename table 表名 to 新表名; 								--修改表名 
alter table 表名 character set 字符集; 						--修改表的字符集

DML语句

1.插入记录：insert

insert into 表 (列名1,列名2,列名3..) values (值1,值2,值3..); 		-- 向表中插入某些列 
insert into 表 values (值1,值2,值3..); 							--向表中插入所有列 

insert into 表 (列名1,列名2,列名3..) values select (列名1,列名2,列名3..) from 表 
insert into 表 values select * from 表

#例子
	INSERT INTO sort(sid,sname) VALUES('s001', '电器'); 
	INSERT INTO sort VALUES('s004','书籍');

注：

1. 列名数与values后面的值的个数相等
2. 列的顺序与插入的值得顺序一致
3. 列名的类型与插入的值要一致. 
4. 插入值得时候不能超过最大长度. 
5. 值如果是字符串或者日期需要加引号’’ （一般是单引号）

2. 更新记录：update

update 表名 set 字段名=值,字段名=值; 
update 表名 set 字段名=值,字段名=值 where 条件;        			#常用

注：

1. 列名的类型与修改的值要一致. 
2. 修改值得时候不能超过最大长度. 
3. 值如果是字符串或者日期需要加’’.

3.删除记录：delete

delete from 表名 [where 条件];

注：

删除表中所有记录使用【delete from 表名】，还是用【truncate table 表名】？ 

删除方式： - delete ：一条一条删除，不清空auto_increment记录数。 
		- truncate ：直接将表删除，重新建表，auto_increment将置为零，从新开始。

DQL语句

提前创建表

#商品表 
CREATE TABLE product ( 
	pid INT PRIMARY KEY AUTO_INCREMENT, # 自增加 AUTO_INCREMENT 
	pname VARCHAR(20),#商品名称 
	price DOUBLE, #商品价格 
	pdate DATE, # 日期 
	cid int #分类ID 
);

#目录表 
create table category( 
    id INT PRIMARY KEY , 
    cname varchar(100) 
);

INSERT INTO product VALUES(NULL,'泰国大榴莲', 98, NULL, 1); 
INSERT INTO product VALUES(NULL,'泰国大枣', 38, NULL, 1); 
INSERT INTO product VALUES(NULL,'新疆切糕', 68, NULL, 2); 
INSERT INTO product VALUES(NULL,'十三香', 10, NULL, 2); 
INSERT INTO product VALUES(NULL,'泰国大枣', 20, NULL, 2);
insert into product values(null,'泰国大枣',98,null,20); #没有对应 

insert into product values(null,'iPhone手机',800,null,30);#没有对应 
INSERT INTO category VALUES(1,'国外食品'); 
INSERT INTO category VALUES(2,'国内食品'); 
INSERT INTO category VALUES(3,'国内服装'); #没有对应

简单查询

select * from product;		--查询所有的商品.
select pname,price from product;  --查询商品名和商品价格.
select * from product as p;		--别名查询，使用的as关键字，as可以省略的.表别名:
select pname as pn from product; 	--列别名
select distinct price from product; 	--去重
select pname,price+10 from product;			--查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.

条件查询(> ,<,=,>=,<=,<> like )

select * from product where pname = '十三香';	--查询商品名称为十三香的商品所有信息：
select * from product where price > 60;			--查询商品价格>60元的所有的商品信息: 
select * from product where pname like '%新%'; --使用占位符 _ 和 % _代表一个字符 %代表任意个字符.
select * from product where pid in (2,5,8);		--in在某个范围中获得值（exists）.

排序(asc-升序,desc-降序)

select * from product order by price;	--查询所有的商品，按价格进行排序
select * from product where pname like '%新%' order by price desc;
									--查询名称有新的商品的信息并且按价格降序排序.

聚合函数，分组函数

注:

sum()：求某一列的和 
avg()：求某一列的平均值 
max()：求某一列的最大值 
min()：求某一列的最小值 
count()：求某一列的元素个数

select sum(price) from product; 	--获得所有商品的价格的总和：
select avg(price) from product; 	--获得所有商品的价格的平均值：
select count(*) from product;		--获得所有商品的个数：

select cid,count(*) from product group by cid; 	--根据cno字段分组，分组后统计商品的个数
select cid,avg(price) from product group by cid having avg(price)>60;
                       --根据cno分组，分组统计每组商品的平均价格，并且平均价格> 60;
#注意事项          
1. select语句中的列（非聚合函数列），必须出现在group by子句中 
2. group by子句中的列，不一定要出现在select语句中 
3. 聚合函数只能出现select语句中或者having语句中，一定不能出现在where语句中。

分页查询

#格式
SELECT * FROM table LIMIT [offset,] rows
						--offset ：偏移量	rows ：每页多少行记录。
#注意
	逻辑分页：将数据库中的数据查询到内存之后再进行分页。 
	物理分页：通过LIMIT关键字，直接在数据库中进行分页，最终返回的数据，只是分页后的数据。（limit）

子查询

#定义
	子查询允许把一个查询嵌套在另一个查询当中。 
	子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。 
	子查询可以包含普通select可以包括的任何子句，比如：distinct、 group by、order by、limit、 join和union等； 
	但是对应的外部查询必须是以下语句之一：select、insert、update、delete。

#位置
	select中、from 后、where 中. 
	group by 和order by 中无实用意义。

其他查询语句（union）

union 集合的并集（不包含重复记录）
union all 集合的并集（包含重复记录）

(三) SQL解析顺序

1. FROM（将最近的两张表，进行笛卡尔积）---VT1 
2. ON（将VT1按照它的条件进行过滤）---VT2 
3. LEFT JOIN（保留左表的记录）---VT3 
4. WHERE（过滤VT3中的记录）--VT4…VTn 
5. GROUP BY（对VT4的记录进行分组）---VT5 
6. HAVING（对VT5中的记录进行过滤）---VT6 
7. SELECT（对VT6中的记录，选取指定的列）--VT7 
8. ORDER BY（对VT7的记录进行排序）--VT8 
9. LIMIT（对排序之后的值进行分页）--MySQL特有的语法

(四) 表与表之间的关系

一对一关系

常见实例：一夫一妻
一对多关系

常见实例：会员和订单
多对多关系（需要中间表实现）

常见实例：商品和订单

(五) 多表关联查询

JOIN 按照功能大致分为如下三类：

#CROSS JOIN （交叉连接）							
	**交叉连接也叫笛卡尔积连接。****笛卡尔积**是指在数学中，两个集合 X 和 Y 的笛卡尓积（ Cartesian 
    product ），又称直积，表示为 X*Y ，第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其
    中一个成员。

#INNER JOIN （内连接或等值连接）。
	内连接也叫**等值连接**，内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。
	
#OUTER JOIN （外连接）  
	左外连接left join、右外连接right join、全外连接join

(补) MySQL补充知识点

基本数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

数字型：int 
浮点型：double 
字符型：varchar（可变长字符串） 
日期类型：date（只有年月日，没有时分秒） 
		datetime（年月日，时分秒） 
		timestamp
boolean类型：不支持，一般使用tinyint替代（值为0和1）

比较运算符

符号	描述	备注
=	等于
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN	在两值之间	>=min&&<=max
NOT BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空

常见函数

MySQL 字符串函数

函数	描述	实例
CHAR_LENGTH(s)	返回字符串 s 的字符数	返回字符串 RUNOOB 的字符数`SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;`
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串	合并多个字符串`SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;`
FORMAT(x,n)	函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入。	格式化数字 “#,###.##” 形式：`SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56`
LOWER(s)	将字符串 s 的所有字母变成小写字母	字符串 RUNOOB 转换为小写：`SELECT LOWER('RUNOOB') -- runoob`
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来：`SELECT REVERSE('abc') -- cba`
STRCMP(s1,s2)	比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1	比较字符串：`SELECT STRCMP("runoob", "runoob"); -- 0`
TRIM(s)	去掉字符串 s 开始和结尾处的空格	去掉字符串 RUNOOB 的首尾空格：`SELECT TRIM(' RUNOOB ') AS TrimmedString;`
UPPER(s)	将字符串转换为大写	将字符串 runoob 转换为大写：`SELECT UPPER("runoob"); -- RUNOOB`

MySQL 数字函数

函数名	描述	实例
ABS(x)	返回 x 的绝对值	返回 -1 的绝对值：`SELECT ABS(-1) -- 返回1`
FLOOR(x)	返回小于或等于 x 的最大整数	小于或等于 1.5 的整数：`SELECT FLOOR(1.5) -- 返回1`
GREATEST(expr1, expr2, expr3, …)	返回列表中的最大值	返回以下数字列表中的最大值：`SELECT GREATEST(3, 12, 34, 8, 25); -- 34`返回以下字符串列表中的最大值：`SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob`
LEAST(expr1, expr2, expr3, …)	返回列表中的最小值	返回以下数字列表中的最小值：`SELECT LEAST(3, 12, 34, 8, 25); -- 3`返回以下字符串列表中的最小值：`SELECT LEAST("Google", "Runoob", "Apple"); -- Apple`
MAX(expression)	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值：`SELECT MAX(Price) AS LargestPrice FROM Products;`
MIN(expression)	返回字段 expression 中的最小值	返回数据表 Products 中字段 Price 的最小值：`SELECT MIN(Price) AS MinPrice FROM Products;`
MOD(x,y)	返回 x 除以 y 以后的余数	5 除于 2 的余数：`SELECT MOD(5,2) -- 1`
POW(x,y)	返回 x 的 y 次方	2 的 3 次方：`SELECT POW(2,3) -- 8`
POWER(x,y)	返回 x 的 y 次方	2 的 3 次方：`SELECT POWER(2,3) -- 8`
RAND()	返回 0 到 1 的随机数	`SELECT RAND() --0.93099315644334`
ROUND(x)	返回离 x 最近的整数	`SELECT ROUND(1.23456) --1`
SIGN(x)	返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1	`SELECT SIGN(-10) -- (-1)`
SIN(x)	求正弦值(参数是弧度)	`SELECT SIN(RADIANS(30)) -- 0.5`
SQRT(x)	返回x的平方根	25 的平方根：`SELECT SQRT(25) -- 5`
SUM(expression)	返回指定字段的总和	计算 OrderDetails 表中字段 Quantity 的总和：`SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;`
TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）	`SELECT TRUNCATE(1.23456,3) -- 1.234`

MySQL 日期函数

函数名	实例	描述
ADDDATE(d,n)	`SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); ->2017-06-25`	计算起始日期 d 加上 n 天的日期
ADDTIME(t,n)	加 5 秒：`SELECT ADDTIME('2011-11-11 11:11:11', 5); ->2011-11-11 11:11:16 (秒)`添加 2 小时, 10 分钟, 5 秒:`SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -> 2020-06-15 11:44:26`	n 是一个时间表达式，时间 t 加上时间表达式 n
CURRENT_DATE()	`SELECT CURRENT_DATE(); -> 2018-09-19`	返回当前日期
CURRENT_TIME	`SELECT CURRENT_TIME(); -> 19:59:02`	返回当前时间
CURRENT_TIMESTAMP()	`SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43`	返回当前日期和时间
DATE()	`SELECT DATE("2017-06-15"); -> 2017-06-15`	从日期或日期时间表达式中提取日期值
DATEDIFF(d1,d2)	`SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32`	计算日期 d1->d2 之间相隔的天数
DATE_FORMAT(d,f)	`SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -> 2011-11-11 11:11:11 AM`	按表达式 f的要求显示日期 d
DATE_SUB(date,INTERVAL expr type)	Orders 表中 OrderDate 字段减去 2 天：`SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders`	函数从日期减去指定的时间间隔。
DAY(d)	`SELECT DAY("2017-06-15"); -> 15`	返回日期值 d 的日期部分
DAYNAME(d)	`SELECT DAYNAME('2011-11-11 11:11:11') ->Friday`	返回日期 d 是星期几，如 Monday,Tuesday
DAYOFMONTH(d)	`SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11`	计算日期 d 是本月的第几天
DAYOFWEEK(d)	`SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6`	日期 d 今天是星期几，1 星期日，2 星期一，以此类推
DAYOFYEAR(d)	`SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315`	计算日期 d 是本年的第几天
EXTRACT(type FROM d)	`SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') -> 11`	从日期 d 中获取指定的值，type 指定返回的值。 type可取值为： MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH
FROM_DAYS(n)	`SELECT FROM_DAYS(1111) -> 0003-01-16`	计算从 0000 年 1 月 1 日开始 n 天后的日期
HOUR(t)	`SELECT HOUR('1:2:3') -> 1`	返回 t 中的小时值
LAST_DAY(d)	`SELECT LAST_DAY("2017-06-20"); -> 2017-06-30`	返回给给定日期的那一月份的最后一天
MINUTE(t)	`SELECT MINUTE('1:2:3') -> 2`	返回 t 中的分钟值
MONTHNAME(d)	`SELECT MONTHNAME('2011-11-11 11:11:11') -> November`	返回日期当中的月份名称，如 November
MONTH(d)	`SELECT MONTH('2011-11-11 11:11:11') ->11`	返回日期d中的月份值，1 到 12
NOW()	`SELECT NOW() -> 2018-09-19 20:57:43`	返回当前日期和时间
PERIOD_ADD(period, number)	`SELECT PERIOD_ADD(201703, 5); -> 201708`	为年-月组合日期添加一个时段
PERIOD_DIFF(period1, period2)	`SELECT PERIOD_DIFF(201710, 201703); -> 7`	返回两个时段之间的月份差值
QUARTER(d)	`SELECT QUARTER('2011-11-11 11:11:11') -> 4`	返回日期d是第几季节，返回 1 到 4
SECOND(t)	`SELECT SECOND('1:2:3') -> 3`	返回 t 中的秒钟值
SEC_TO_TIME(s)	`SELECT SEC_TO_TIME(4320) -> 01:12:00`	将以秒为单位的时间 s 转换为时分秒的格式
STR_TO_DATE(string, format_mask)	`SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10`	将字符串转变为日期

MySQL 高级函数

函数名	描述	实例
BIN(x)	返回 x 的二进制编码	15 的 2 进制编码:`SELECT BIN(15); -- 1111`
BINARY(s)	将字符串 s 转换为二进制字符串	`SELECT BINARY "RUNOOB"; -> RUNOOB`
CAST(x AS type)	转换数据类型	字符串日期转换为日期：`SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29`
COALESCE(expr1, expr2, …, expr_n)	返回参数中的第一个非空表达式（从左向右）	`SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com'); -> runoob.com`
CONV(x,f1,f2)	返回 f1 进制数变成 f2 进制数	`SELECT CONV(15, 10, 2); -> 1111`
CONVERT(s USING cs)	函数将字符串 s 的字符集变成 cs	`SELECT CHARSET('ABC') ->utf-8 SELECT CHARSET(CONVERT('ABC' USING gbk)) ->gbk`
IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。	`SELECT IF(1 > 0,'正确','错误') ->正确`
IFNULL(v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。	`SELECT IFNULL(null,'Hello Word') ->Hello Word`
ISNULL(expression)	判断表达式是否为 NULL	`SELECT ISNULL(NULL); ->1`

二. MySQL架构篇

(一) MySQL文件结构

日志文件（顺序IO）

查看当前数据库中的日志使用信息:

mysql> show variables like 'log_%';

1）错误日志（errorlog）

2）二进制日志（bin log）

3）通用查询日志（general query log）

4）慢查询日志（slow query log）

数据文件（随机IO）

查看MySQL数据文件：

SHOW VARIABLES LIKE '%datadir%';

1）InnoDB数据文件

.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息 (表结构)
.ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件。 （表数据和索引）
.ibdata文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件。（共享）

2）MyIsam数据文件

.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息 (表结构)
.myd文件：主要用来存储表数据信息。 （表数据）
.myi文件：主要用来存储表数据文件中任何索引的数据树。（索引）

(二) 逻辑架构图

mysql server层	InnoDB层
连接器 --> 分析器(查询缓存) --> 优化器 --> 执行器	磁盘结构、内存结构、数据落盘

(三) MySqlServer 层对象

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

执行流程	结果	详情
连接器	连接管理、权限验证	先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。
分析器(查询缓存)	词法分析、语法分析(命中查询、返回结果)	(大多数情况下我会建议你不要使用查询缓存，因为查询缓存往往弊大于利)
优化器	生成计划、选择索引	1)当有多个索引可用的时候，决定使用哪个索引； 2)在一个语句有多表关联（join）的时候，决定各个表的连接顺序，以哪个表为基准表。
执行器	调用接口、返回结果	1)要先判断一下你对这个表customer有没有执行查询的权限; 2)执行器会根据表的引擎定义，去使用这个引擎提供的查询接口，提取数据。

(四) InnoDB 存储引擎层

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

存储引擎	说明
MyISAM	高速引擎，拥有较高的插入，查询速度，但不支持事务
InnoDB	5.5版本后MySQL的默认数据库，支持事务和行级锁定，比 MyISAM处理速度稍慢
ISAM	MyISAM的前身，MySQL5.0以后不再默认安装
MRG_MyISAM（MERGE）	将多个表联合成一个表使用，在超大规模数据存储时很有用
Memory	内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失
Falcon	一种新的存储引擎，支持事物处理，传言可能是InnoDB的替代者
Archive	数据压缩后进行存储，非常适合存储大量的独立的，作为历史记录的数据，但是只能进行插入和查询操作
CSV	CSV 存储引擎是基于 CSV 格式文件存储数据(应用于跨平台的数据交换)

InnoDB的磁盘结构

1）系统表空间

系统表空间包含InnoDB数据字典(元数据以及相关对象)、double write buffer、change buffer、undo logs的存储区域

2）用户表空间

用户表空间只存储该表的数据、索引信息，其余信息还是存放在默认的系统表空间中。

3）rodo log 文件组

当InnoDB的数据存储文件发生错误时，重做日志文件就能派上用场。InnoDB存储引擎可以使用重做日志文件将数据恢复为正确状态，以此来保证数据的正确性和完整性。
为了得到更高的可靠性，用户可以设置多个镜像日志组，将不同的文件组放在不同的磁盘上，以此来提高重做日志的高可用性。

4）磁盘文件逻辑结构

文件 -> 段 -> 区 -> 页 -> 行

InnoDB对数据的存取是以页为单位的，一个数据页默认是16k

InnoDB的内存结构

1）buffer pool

1.数据页

2.索引页

InnoDB存储引擎工作时，需要以Page页为最小单位去将磁盘中的数据加载到内存中，与数据库相关的所有内容都存储在Page结构里。
Page分为几种类型，数据页和索引页就是其中最为重要的两种类型。

3.ChangeBuffer修改缓冲区（InsertBuffer插入缓冲区）

为了提高辅助索引（非聚集索引，除主键之外的索引）更新性能，暂时先把辅助索引的更新内容写到 ChangeBuffer中。后台有线程定时更新。

4.自适应hash索引

为了提高查询性能，由InnoDB自己维护了一个hash索引。用户无法干预。
参考：https://juejin.im/post/6847902217270198286

5.锁信息和数据字典

InnoDB有自己的表缓存，可以称为表定义缓存或者数据字典(Data Dictionary)。当InnoDB打开一张
表，就增加一个对应的对象到数据字典。
数据字典是对数据库中的数据、库对象、表对象等的元信息的集合。在MySQL中，数据字典信息内容就
包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内
**容。**MySQL INFORMATION_SCHEMA库提供了对数据局元数据、统计信息、以及有关MySQL server的
访问信息（例如：数据库名或表名，字段的数据类型和访问权限等）。该库中保存的信息也可以称为
MySQL的数据字典。

2）redo log buffer重做日志缓冲区

1.redolog是一个顺序写的日志文件。wal（write ahead log）模式。顺序写比随机写效率要高。使用redo 		log暂存提交成功的数据。如果一旦系统崩溃，可以使用redo log恢复数据。
2.redo log buffer就是一个缓冲区，定时将缓冲区的数据保存到磁盘。
	默认1s保存一次。默认在执行commit操作之前刷新redo log buffer。
	参数：innodb_flush_log_at_trx_commit控制redo log的落盘时机

	当属性值为0时，事务提交时，不会对重做日志进行写入操作，而是等待主线程按时写入每秒写入一次；
	当属性值为1时，事务提交时，会将重做日志写入文件系统缓存，并且调用文件系统的fsync，将文件系统缓冲中的数据真正写入磁盘存储，确保不会出现数据丢失；（一般）
	当属性值为2时，事务提交时，也会将日志文件写入文件系统缓存，但是不会调用fsync，而是让文件系统自己去判断何时将缓存写入磁盘。

3）双写缓冲区

数据落盘需要执行双写操作，需要使用到双写缓冲区。稍后详解。

4）数据落盘

脏页
当更新数据commit之后，数据不是马上更新到磁盘。把数据更新到缓冲池中对应的数据页和索引页中。此时就会造成内存中的数据页和索引页与磁盘上的数据页不一致，就形成了脏页。
CheckPoint 执行脏页落盘操作的。
sharp checkpoint：在关闭数据库的时候，将buffer pool中的脏页全部刷新到磁盘中
fuzzy checkpoint：模糊落盘
1、Master Thread Checkpoint；
会以每秒或者每10秒一次的频率，将部分脏页从内存中刷新到磁盘，参数可调。
2、FLUSH_LRU_LIST Checkpoint；
使用LRU算法需要把一些数据页移除，但是数据页是脏页，需要执行checkpoint。
3、Async/Sync Flush Checkpoint；
redo log文件快写满时。
Async：当需要落盘的日志超过日志文件的75%并且小于90%时。
sync：当需要落盘的日志超过日志文件超过90%时。
4、Dirty Page too much Checkpoint
bufferPool缓冲池脏页太多
Dirty Page 由[innodb_max_dirty_pages_pct]配置，innodb_max_dirty_pages_pct的默认值在innodb 1.0之前是90%，之后是75%
双写落盘double write
当脏页落盘过程中，是使用双写操作执行落盘的。
1.把要落盘的数据写先写入双写缓冲区2m大小的双写缓冲区。
2.把双写缓冲区的数据写入系统表空间中的双写区。
3.把双写缓冲区的数据写入用户表空间中。

redolog中记录的是数据页中修改了哪些内容，并不是完整的数据页。

场景1：系统表空间中的数据页写坏了，可以通过用户表空间的数据页+redolog恢复。
场景2：系统表空间中的数据页写完了，用户表空间写坏了。可以通过系统表空间的数据页恢复。

通过双写机制保证数据落盘过程万无一失。

三. MySQL事务篇

(一) 事务介绍

MySQL 是一个服务器／客户端架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话（ Session ）。我们可以同时在不同的会话里输入各种语句，这些语句可以作为事务的一部分进行处理。不同的会话可以同时发送请求，也就是说服务器可能同时在处理多个事务，这样子就会导致不同的事务可能同时访问到相同的记录。我们前边说过事务有一个特性称之为隔离性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样子的话对性能影响太大，所以设计数据库的大叔提出了各种隔离级别，来最大限度的提升系统并发处理事务的能力，但是这也是以牺牲一定的隔离性来达到的。

事务特征(ACID)

原子性(atomicity) ：事务最小工作单元，要么全成功，要么全失败。
一致性(consistency)：事务开始和结束后，数据库的完整性不会被破坏。
隔离性(isolation) ：不同事务之间互不影响，四种隔离级别为RU（读未提交）、RC（读已提交）、RR（可重复读）、SERIALIZABLE （串行化）。
持久性(durability) ：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失。

事务的隔离级别

隔离级别	标识	影响	详情
未提交读	RU(READ UNCOMMITTED)	脏读	一个事务读取到另一个事务未提交的数据
已提交读	RC(READ COMMITTED)	不可重复读	一个事务因读取到另一个事务已提交的update
可重复读	RR(REPEATABLE READ)	幻读	一个事务因读取到另一个事务已提交的insert数据或者delete数据
串行化	SERIALIZABLE		不允许读-写、写-读的并发操作

(二) 事务和MVCC底层原理详解

1、解决数据更新丢失的问题

1）LBCC

基于锁的并发控制。让操作串行化执行。效率低。

2）MVCC

基于版本的并发控制。使用快照形式。效率高。读写不冲突。主流数据库都是使用的MVCC。

2、InnoDB中MVCC的实现

特点：读不加锁，读写不冲突
实现方案：基于undolog+readview实现的。

1）undolog

	回滚日志。需要在undolog中记录未提交操作的原始状态。
	在undolog中会记录版本信息。在每一行记录上都有2-3个隐藏列。如果表没有主键时就是3个，如果有主键就是两个。

隐藏列：

rowid：如果没有主键，会自动生成一个隐藏列。（非必须）
回滚指针：指向记录的上一个版本。
事务id：记录了操作这条记录的事务id。

事务id在mysql中每个事务都有一个唯一的id，并且是自增的。

每次更新时，都是生成一个新的版本，并且由回滚指针指向旧版本。就会形成一个版本链。后台有个purge线程执行清理操作。删除记录时，是在记录上打上删除标记，并不直接删除。

2）readview

生成一个readview相当于生成了一个快照。只要是ReadView不发生变化读到的结果就是相同的。
readview是一个数组，生成的时机是执行select操作时生成。数组m_ids，其中记录的当前时刻，数据库中活跃的事务id列表。
例如：
m_ids:[105,110,111,120]

可见性判断条件：

如果被访问版本的trx_id属性值小于m_ids列表中最小的事务id，表明生成该版本的事务在生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于m_ids列表中最大的事务id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在m_ids列表中最大的事务id和最小事务id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

支持两种事务隔离级别：RC、RR。
读未提交和串行化和MVCC没有关系。

一个select对应一个ReadView（m_ids），select语句执行完毕后ReadView就失效。
如果当前数据库中没有活跃事务，那么ReadView（m_ids）中就包含将要生成的事务id。

3）RC事务隔离级别的实现

读已提交，当前事务中可以读取到其他事务提交的结果。
实现方案：在当前事务中执行select查询，就会生成一个ReadView。如果同一个select再次执行，会再次生成ReadView。

4）RR事务隔离级别

可重复读，当前事务中select语句多次执行得到的结果是相同的，无论其他事务是否已经提交。
实现方案：在当前事务中执行select查询，就会生成一个ReadView，之后同一个select使用同一个ReadView。

5）小结：RC和RR的区别

	`READ COMMITTD`、`REPEATABLE READ`这两个隔离级别的一个很大不同就是生成`ReadView`的时机不同，`READ COMMITTD`在每一次进行普通`SELECT`操作前都会生成一个`ReadView`，而`REPEATABLE READ`只在第一次进行普通`SELECT`操作前生成一个`ReadView`，之后的查询操作都重复这个`ReadView`就好了。

6）redo log 与 undo log

	redo log顾名思义，就是重做日志，每次数据库的SQL操作导致的数据变化它都会记录一下，具体来说，redo log是物理日志，记录的是数据库页的物理修改操作。如果数据发生了丢失，数据库可以根据redo log进行数据恢复。
	undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。

四. MySQL索引篇

(一) 索引介绍

1、索引是什么

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前

面的目录，能加快数据库的查询速度。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的

（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说

明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

2、索引的优势和劣势

优势：

1.可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。

2.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复

杂一些。

如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。

劣势：

1.索引会占据磁盘空间

2.索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不

仅要保存数据，还有保存或者更新对应的索引文件。

(二) 索引的使用

1、索引的类型

主键索引：索引列中的值必须是唯一的，不允许有空值。

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

ALTER TABLE table_name ADD INDEX index_name (column_name) ;
CREATE INDEX index_name ON table_name(column_name) ;

唯一索引：索引列中的值必须是唯一的，但是允许为空值。

ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name) ;
CREATE UNIQUE INDEX index_name ON table_name(column_name) ;

全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，

如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。

#创建表时，创建全文索引 
CREATE TABLE `t_fulltext` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `content` varchar(100) DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    FULLTEXT KEY `idx_content` (`content`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

#创建全文索引 
ALTER TABLE `t_fulltext` ADD FULLTEXT INDEX `idx_content`(`content`);
CREATE FULLTEXT INDEX index_name ON table_name(column_name) ;

可以使用MATCH() … AGAINST语法执行全文搜索。

SELECT * FROM t_fulltext WHERE MATCH(content) AGAINST('开课吧');

空间索引：MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。（少用）
前缀索引（少用）

在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不

能指定。
```
ALTER TABLE table_name ADD INDEX index_name (column1(length));
```

按照索引列的数量

单列索引：索引中只有一个列。

组合索引：使用2个以上的字段创建的索引。

1.组合索引的使用，需要遵循最左前缀原则
2.一般情况下，建议使用组合索引代替单列索引（主键索引除外）。

#添加方式
ALTER TABLE table_name ADD INDEX index_name (column1,column2);

2、删除索引

ALTER TABLE table_name DROP INDEX index_name;
DROP INDEX index_name ON table_name

3、查看索引

SHOW INDEX FROM table_name \G

(三) 索引的数据结构

1、索引分类

1）聚簇索引

基于主键创建的索引。或者叫做聚集索引，因为索引和数据是保存在一起的。

2）非聚簇索引

除了主键索引之外创建的索引都是非聚簇索引。辅助索引。

2、索引的数据结构

1）hash

key-value形式的数据结构。
查询速度非常快。直接通过可以找到value。
优点：速度快。时间复杂度O(1)
缺点：不适合范围查询。不适合做索引。

2）二叉查找树

特点：左子树一定是小于根节点，右子树一定是大于根节点。时间复杂度O(log2n)
问题：根节点的选取最好是所有数据中中间数值。否则左子树和右子树高度可能不一致。最差情况下退化成链表。

3）二叉平衡树查找树

特点：向树中增加节点，动态调整树的平衡状态，要求左右子树的高度相差最大不能超过1.
问题：不断的添加数据，二叉树会不断的调整状态。调整的过程也是非常耗时的。数据量大时，树的高度也会非常高，每查找一个节点就会产生一个磁盘的IO。总体上性能也不太好。
要求树高度要低，推荐使用多叉树。

4）B树

每个节点中可以有多个分支，分支的数量叫做度。分支越多效果越好。每个数据的左右两个分支，左子树小于当前数值，右子树大于当前数值。B树是一种多叉平衡查找树
前提：
每个节点中保存的数据 8byte+4byte+4byte，每个数据节点是16k时，b树的度应该是1000

高度为2的b树可以存储数据量：
1000*1000=10W

特点：
1、每个节点中都保存数据
2、数据出现在中间节点中，就不会出现在叶子节点中。

问题：
选择索引数据结构时，需要考虑两点，一个就是等值查询，一个是范围查询。
如果使用b树做范围查询效率不高。

5）B+树(常用)

对b树的改进：
1、b+树中除叶子节点外，中间节点不保存数据，中间节点保存的是主键。
2、b+树中所有的数据都存放在叶子节点中。
3、b+树中叶子节点之间有双向指针，形成一个双向链表。

b+树适合于等值查询也适合于范围查询。在mysql中所有的索引都是b+tree结构。

3、MyIsam引擎的索引结构

1）主键索引

 b+tree结构。在MyIsam引擎中索引和数据是分在不同的文件中存储的。
 中间节点保存都是主键的值，叶子节点中保存的是对应数据行的地址。

2）非主键索引，辅助索引

在普通字段上创建的索引。
对应MyIsam引擎来说**辅助索引和主键索引是相同的**。

4、InnoDB引擎的索引结构

1）主键索引

  聚集索引或者聚簇索引。如果使用的是InnoDB引擎，没有设置主键系统会自动创建一个主键，rowid。
  InnoDB中主键索引和数据是存储在一起的，所以叫做聚簇索引，聚集索引。
  通过主键查询数据效率是最高的，找到叶子节点就找到数据行。

InnoDB引擎必有主键：

1. 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
2. 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
3. 如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索
引。该ROWID字段会在插入新行时自动递增。

2）非主键索引，辅助索引

非主键索引也是b+tree结构，叶子节点中保存的就是主键的值。 如果根据辅助索引查询，只能得到主键的值，然后根据主键查询聚集索引找到对应的数据，这个过程就叫做回表。
如果使用辅助索引，效率是不如MyIsam引擎的辅助索引效率高。

3）组合索引（重点）

1、什么是组合索引

组合索引就是辅助索引的一种形式。基于多个字段创建的一个辅助索引。
 创建辅助索引的两种形式：
  1、在每个字段上分别创建一个索引。
  2、创建一个辅助索引，里面包含三个字段。（推荐使用形式，组合索引）
 SQL语句中如果使用到一个表的索引，那么只能使用一个。

#示例
在组合索引中，如果有三个字段a、b、c
  先按照a排序，
  如果a相同，按照b排序
  如果b相同，按照c排序

2、组合索引的使用方法

1）要求查询条件中必须包含组合索引中的第一个字段。
2）可以满足根据a、ab、abc查询都可以。
3）如果条件中只有b、c是否能使用索引？否。
4）如果使用组合索引时，必须有索引中第一个字段，而且后面的条件必须按照创建索引的顺序出现。
  如果条件中a、c，那么只能a条件使用索引，c无法使用索引。

索引使用口诀

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

in是可以使用到索引的。如果表中数据量小并且，in集合中数据较多的情况下可能不使用索引。如果数据量大in一定会使用索引。

3、创建组合索引的原则

 1）经常出现在where条件中字段
 2）经常出现order by、group by字段中的
 3）经常出现在select字段列表中的字段
 select id,a,b,c from user wehre xxxx

组合索引的字段顺序如何设计呢？
出现在where条件中频率最高的字段应该排第一位，按照使用频率进行排序。

order by使用索引情况的分析：
https://juejin.im/post/6854573217978253320

4）索引覆盖

 查询的字段内容可以直接从索引中获得，无需再回表，这样可以提高查询效率。
 在select语句中不要使用“*”,应该使用字段列表。

5）索引条件下推ICP

索引条件下推在mysql5.7以后才有的一个优化手段。默认开启的。
 作用就是，即便是组合索引条件中断或者范围查询，后面的条件依然可以使用索引。

 #optimizer_switch优化相关参数开关
 mysql> show VARIABLES like 'optimizer_switch'\G;
 #关闭ICP
 SET optimizer_switch = 'index_condition_pushdown=off';
 #开启ICP
 SET optimizer_switch = 'index_condition_pushdown=on';

(四) explain是否使用索引

1、Using index(覆盖索引)

#Extra中为Using index
1 查询的列被索引覆盖
2 并且where筛选条件是索引的是前导列

2、Using where ;Using index

#Extra中为Using where; Using index，
1 查询的列被索引覆盖
2 where筛选条件是索引列之一，但是不是索引的不是前导列，
  或者where筛选条件是索引列前导列的一个范围

3、NULL

#Extra中为NULL(没有信息)
1 查询的列未被索引覆盖，并且where筛选条件是索引的前导列，

意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，

4、Using where

#Extra中为Using where
1 查询的列未被索引覆盖
2 where筛选条件非索引的前导列或者非索引列

5、Using index condition

1 查询的列不全在索引中，where条件中是一个前导列的范围
2 查询列不完全被索引覆盖，查询条件完全可以使用到索引（进行索引查找）

五.MySQL锁篇

(一) 锁介绍

1. 按照锁的粒度来说

全局锁：锁的是整个database。由MySQL的SQL layer层实现的
表级锁：锁的是某个table。由MySQL的SQL layer层实现的
行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。

2. 按照锁的功能来说

共享锁Shared Locks（S锁）: 读锁

1、兼容性：加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁 
2、加锁方式：select…lock in share mode

排他锁Exclusive Locks（X锁）: 写锁

1、兼容性：加了X锁的记录，不允许其他事务再加S锁或者X锁 
2、加锁方式：select…for update

(二) 全局锁

全局锁就对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的MDL的写语句，DDL语句，

已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁

定，从而获取一致性视图，保证数据的完整性。

1. 对于innodb这种支持事务的引擎，使用mysqldump备份时可以使用--single-transaction参数，利用
mvcc提供一致性视图，而不使用全局锁，不会影响业务的正常运行。
2.而对于有MyISAM这种不支持事务的表，就只能通过全局锁获得一致性视图，对应的mysqldump参数为--lock-all-tables。

加锁：

	mysql> flush tables with read lock;

解锁：

	mysql> unlock tables;

或者会话关闭，锁自动解除。
注意：全局锁一般很少使用，会影响业务处理。

(三) 表级锁

1、表级锁介绍

1、表读、写锁。 
2、元数据锁（meta data lock，MDL)。 
3、意向锁 Intention Locks（InnoDB） 
4、自增锁(AUTO-INC Locks)

2、读锁S、写锁X

1) 表锁相关命令

MySQL 实现的表级锁定的争用状态变量：

mysql> show status like 'table%';

- table_locks_immediate：产生表级锁定的次数； 
- table_locks_waited：出现表级锁定争用而发生等待的次数；

表锁有两种表现形式：

表共享读锁（Table Read Lock） 
表独占写锁（Table Write Lock）

手动增加表锁：

lock table 表名称1 read(write),表名称2 read(write)，其他;

查看表锁情况：
```
show open tables;
```
删除表锁
```
unlock tables;
```

2) 表锁说明

读锁（当前session加读锁）

#当前session
1. 当前session可以查询该表记录，但是不能查询其他没有加锁的表
2. 当前session进行插入,更新，删除表会报错

#其他session
1. 其他session可以查询该表记录，也可以查询其他未加锁的表
2. 其他session对该表进行插入,更新，删除会一直等待获得锁

写锁（当前session加写锁）

#当前session
1. 当前session可以进行增，删，改，查操作

#其他session
1. 其他session进行增，删，改，查操作被阻塞，要等待锁被释放

注意

1.S锁的作用，在读取过程中防止其他会话对表进行修改操作，如果一个表上有任何锁，包括S、X锁，都无法再加X锁。
	一个表上可以加多个S锁，数据可以共享读。
2.X锁，排它锁，在修改过程中，对表处于一个独享状态。如果一个表上有X锁，那么此时表上不允许加任何锁。

3.锁的兼容性：
	S、S可以
	S、X不可以
	X、X不可以
表锁一般也不经常使用，粒度比较粗。

3、元数据锁

1）元数据锁介绍

当事务未结束时，在事务中执行了select操作，不允许对表结构进行修改的。

MDL不需要显式使用，在访问一个表的时候会被自动加上。在 MySQL 5.5 版本中引入了 MDL：

1.当对一个表做增删改查操作的时候，加 MDL 读锁；
2.当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同

时给一个表加字段，其中一个要等另一个执行完才能开始执行。

2）元数据锁演示

1、session1: begin;--开启事务 
   select * from mylock;--加MDL读锁 
2、session2: alter table mylock add f int; -- 修改阻塞 
3、session1：commit; --提交事务 或者 rollback 释放读锁 
4、session2：Query OK, 0 rows affected (38.67 sec) --修改完成 
Records: 0 Duplicates: 0 Warnings: 0

4、自增锁(AUTO-INC Locks)

AUTO-INC锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

(四) 行级锁(InnoDB独有)

MySQL的行级锁，是由存储引擎来实现的，这里我们主要讲解InnoDB的行级锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

1 记录锁（Record Locks）:锁定索引中一条记录。 
2 间隙锁（Gap Locks）:要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。（范围查询，解决MVCC的幻读问题）
3 临键锁（Next-Key Locks）:是索引记录上的记录锁和在索引记录之前的间隙锁的组合（间隙锁+记录锁）。
4 插入意向锁(Insert Intention Locks)：做insert操作时添加的对记录id的锁。

MySQL的行级锁，是由存储引擎来实现的，这里我们主要讲解InnoDB的行级锁。 InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

1）记录锁

只有InnoDB引擎支持行锁。也就是说行锁是在InnoDB中实现。
行锁也有共享锁和排他锁。
加共享锁：

select * from t where id=1 lock in share mode;

加排它锁：

select * from t where id=1 for update;

2）意向锁

意向锁共享锁IS：表中某个记录上有共享锁是添加。
意向排他锁IX：表中某个记录有排他锁是添加
就是一个标志位，为了快速判断表上是否有行锁。

意向锁是表级锁。是InnoDB实现的，是行锁的一个副产品。
不需要人为干预，InnoDB自动处理。在对记录加行锁时由InnoDB自动加意向锁。

是否兼容	IS	IX	S	X
IS	是	是	是	否
IX	是	是	否	否
S	是	否	是	否
X	否	否	否	否

3）间隙锁

区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）
间隙锁可用于防止幻读，保证索引间的不会被插入数据
间隙锁就是MVCC防止幻读的解决方案。
MVCC实现的是快照读，加锁不影响读操作。

只有RR事务隔离级别才用到间隙锁，RC是不需要的。

4）临键锁

记录锁+间隙锁
默认情况下，innodb使用next-key locks来锁定记录。

记录锁：条件中根据主键等值查询，并且值存在时，只有记录锁。
间隙锁：条件中根据主键等值查询，并且值不存在时，只有间隙锁，锁等值所在的那个区间。
临键锁：条件中根据索引做范围查询，使用临键锁。

如果根据辅助索引更新：

	1）因为辅助索引不唯一，所以更新时，在辅助索引上加临键锁，
	2）并且在主索引上增加记录锁。

5）插入意向锁(Insert Intention Locks)

做insert操作时，会锁定未提交记录的id的值。

6）行锁加锁规则

主键索引

等值查询

1）命中记录，加记录锁。
2）未命中记录，加间隙锁。

范围查询

1）没有命中任何一条记录时，加间隙锁。
2）命中1条或者多条，包含where条件的临键区间，加临键锁

辅助索引

等值查询

1）命中记录，命中记录的辅助索引项+主键索引项加记录锁，辅助索引项两侧加间隙锁。
2）未命中记录，加间隙锁

范围查询

1）没有命中任何一条记录时，加间隙锁。
2）命中1条或者多条，包含where条件的临键区间加临键锁。命中记录的id索引项加记录锁。

(五) 死锁

有两个或两个以上的资源的情况下，有两个事物互相持有了对方期待的资源，导致互相等待对方释放资源时，产生死锁。

如何避免死锁呢？

MySQL默认会主动探知死锁，并回滚某一个影响最小的事务。等另一事务执行完成之后，再重新执行该事务。

如何避免死锁

1、注意程序的逻辑

根本的原因是程序逻辑的顺序，最常见的是交差更新
Transaction 1: 更新表A -> 更新表B
Transaction 2: 更新表B -> 更新表A
Transaction获得两个资源

2、保持事务的轻量

越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小

3、提高运行的速度

避免使用子查询，尽量使用主键等等

4、尽量快提交事务，减少持有锁的时间

越早提交事务，锁就越早释放

六.MySQL性能优化篇

(一)性能优化的思路

1. 首先需要使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句
2. 查看执行计划，查看有问题的SQL的执行计划
3. 针对查询慢的SQL语句进行优化
4. 使用【show profile[s]】 查看有问题的SQL的性能使用情况
5. 调整操作系统参数优化
6. 升级服务器硬件

(二)慢查询日志

1、慢查询日志介绍

数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化 SQL，更重要的是得先找到需要优化的SQL。

MySQL数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL语句，这将极大程度帮助我们快速定位到症结所在，以便对症下药。

至于查询时间的多少才算慢，每个项目、业务都有不同的要求。

MySQL的慢查询日志功能默认是关闭的，需要手动开启。

2、开启慢查询功能

查看是否开启慢查询功能

mysql> show variables like '%slow_query%';				#查看是否开启慢查询
mysql> show variables like '%long_query_time%';			#慢查询阈值，当查询时间多于设定的阈值时，记录日志，

临时开启慢查询功能

#在 MySQL 执行 SQL 语句设置，但是如果重启 MySQL 的话将失效
set global slow_query_log = ON; 
set global long_query_time = 1;

永久开启慢查询功能

#修改/etc/my.cnf配置文件，重启 MySQL, 这种永久生效.
[mysqld] 
slow_query_log=ON 
long_query_time=1

3、慢查询日志格式

mysql> select sleep(3);

查看日志信息：

tail -100f xxx.log;

格式说明：

第一行,SQL查询执行的具体时间
第二行,执行SQL查询的连接信息，用户和连接IP

第三行,记录了一些我们比较有用的信息，如下解析

Query_time,这条SQL执行的时间,越长则越慢 
Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间 
Rows_sent,查询返回的行数 
Rows_examined,查询检查的行数，越长就当然越费时间

第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间。
第五行及后面所有行（第二个# Time:之前）,执行的sql语句记录信息，因为sql可能会很长。

4、分析慢查询日志的工具

使用mysqldumpslow工具，mysqldumpslow是MySQL自带的慢查询日志工具。可以使用mysqldumpslow工具搜索慢查询日志中的SQL语句。

[root@localhost mysql]# mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

说明：

-s：是表示按照何种方式排序

al 平均锁定时间
ar 平均返回记录时间
at 平均查询时间（默认）
c 计数
l 锁定时间
r 返回记录
t 查询时间

-t：是top n的意思，即为返回前面多少条的数据
-g：后边可以写一个正则匹配模式，大小写不敏感的

[root@mysql132 mysql]# mysqldumpslow -s t /var/lib/mysql/mysql132-slow.log 

Reading mysql slow query log from /var/lib/mysql/mysql132-slow.log 

Count: 1 Time=143.16s (143s) Lock=0.00s (0s) Rows=27907961.0 (27907961), 
root[root]@localhost select * from t_slow a left join t_slow b on a.name=b.name
    
Count: 5 Time=5.80s (28s) Lock=0.00s (0s) Rows=0.0 (0), 
root[root]@localhost insert into t_slow(name,address) select name,address from t_slow 

Count: 1 Time=3.01s (3s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost select sleep(N)

(三)查看执行计划explain

1.参数说明

mysql> explain select * from tuser where id = 2 \G 
*************************** 1. row ***************************
	id: 1 
	select_type: SIMPLE 
	table: tuser 
	partitions: NULL 
	type: const 
	possible_keys: PRIMARY 
	key: PRIMARY 
	key_len: 4 
	ref: const 
	rows: 1 
	filtered: 100.00 
	Extra: NULL 
1 row in set, 1 warning (0.01 sec)

说明：

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

id相同：执行顺序由上到下			#多表同级查询
id不同：如果是子查询，id号会自增，id越大，优先级越高。		#多表存在子查询

select_type: SELECT 查询的类型.

1.simple
	表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple。
2.primary
	一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。
3.union
	union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union
4.dependent union
	与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
5.union result
	包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
6.subquery
	除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
7.dependent subquery
	与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
8.derived
	from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select


#注意；
·DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
·UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
·UNION RESULT 从UNION表获取结果的SELECT

table: 查询的是哪个表

1 如果查询使用了别名，那么这里显示的是别名
2 如果不涉及对数据表的操作，那么这显示为null
3 如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。
4 如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

partitions: 匹配的分区

#使用的哪些分区（对于非分区表值为null）。
什么是分区表？
	mysql内部实现的表的水平拆分，所有数据还在一个表中，但物理存储根据一定的规则放在不同的文件中。这个是mysql支持的功能，业务代码无需改动。
技术现状：
	业内进行一些技术交流的时候也更多的是自己分库分表，而不是使用分区表。
	1）分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
	2）一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难
	3）自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控
	4）运维的坑

type: join 类型

#显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差：
system 
const 
eq_ref 
ref 
fulltext 
ref_or_null 
unique_subquery 
index_subquery 
range 
index_merge 
index ALL

#注意
- 除了all之外，其他的type都可以使用到索引 
- 除了index_merge之外，其他的type只可以用到一个索引 
- 最少要使用到range级别

possible_keys: 此次查询中可能选用的索引

此次查询中可能选用的索引，一个或多个

key: 此次查询中确切使用到的索引.

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

key_len：
```
key_len越小 索引效果越好
```

ref: 哪个字段或常数与 key 一起被使用

1 如果是使用的常数等值查询，这里会显示const
2 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
3 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows: 显示此查询一共扫描了多少行. 这个是一个估计值.

这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原
因是InnoDB里面使用了MVCC并发机制）

filtered: 表示此查询条件所过滤的数据的百分比

filtered列指示将由mysql server层需要对存储引擎层返回的记录进行筛选的估计百分比，也就是说存储引擎层返回的结果中包含有效记录数的百分比。最大值为100，这意味着没有对行进行筛选。值从100减小表示过滤量增加。rows显示检查的估计行数，rows×filtered显示将与下表联接的行数。例如，如果rows为1000，filtered为50.00（50%），则要与下表联接的行数为1000×50%=500。

extra: 额外的信息

(四)SQL语句优化（开发人员）

1、索引优化

为搜索字段（where中的条件）、排序字段、select查询列创建合适的索引，不过要考虑数据的

业务场景：查询多还是增删多？
尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查询条件放到最左边。
尽量使用覆盖索引，SELECT语句中尽量不要使用*。
order by、group by语句要尽量使用到索引
索引长度尽量短，短索引可以节省索引空间，使查找的速度得到提升，同时内存中也可以装载更多的索引键值。太长的列，可以选择建立前缀索引
索引更新不能频繁，更新非常频繁的数据不适宜建索引，因为维护索引的成本。
order by的索引生效，order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排序，那么排序的规则必须相同（同是升序或者降序），否则索引同样会失效。

2、LIMIT优化

如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描。
处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常差。 LIMIT OFFSET , SIZE；

LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。

解决方案：单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后面只写rows

3、其他查询优化

小表驱动大表，建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描的，以少关联多就可以减少这个扫描次数。
避免全表扫描，mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描。在查询的时候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描
避免mysql放弃索引查询，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。（最典型的场景就是数据量少的时候）
尽量不使用count(*)、尽量使用count（主键）
JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的。
WHERE条件中尽量不要使用not in语句（建议使用not exists）
合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况

(五)profile分析语句

1、介绍

Query Profiler是MySQL自带的一种query诊断分析工具**，通过它可以分析出一条SQL语句的**硬件性能

瓶颈在什么地方。通常我们是使用的explain,以及slow query log都无法做到精确分析

工具只有在MySQL 5.0.37以及以上版本中才有实现。

**默认的情况下，**MYSQL的该功能没有打开，需要自己手动启动。

2、开启Profile功能

Profile 功能由MySQL会话变量 : profiling控制,默认是OFF关闭状态。

查看是否开启了Profile功能:

select @@profiling; 
-- 或者 
show variables like '%profil%';

开启profile功能

set profiling=1; --1是开启、0是关闭

3、语句使用

show profile 和 show profiles 语句可以展示当前会话(退出session后,profiling重置为0) 中执行语句的资源使用情况.
show profiles :以列表形式显示最近发送到服务器上执行的语句的资源使用情况.显示的记录数由变量:profiling_history_size 控制,默认15条
show profile: 展示最近一条语句执行的详细资源占用信息,默认显示 Status和Duration两列
show profile 还可根据 show profiles 列表中的 Query_ID ,选择显示某条记录的性能分析信息

七.MySQL集群篇

(一)集群搭建之主从复制

(二)集群搭建之读写分离

八.MySQL分库分表篇

九.补充篇

monkey15332675317

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
01mysql

一. MySQL基础篇(一) Linux(CentOS 7)安装MySQL 5.6卸载1 查看MySQL软件rpm -qa|grep mysql yum repolist all | grep mysql 2 卸载yum remove -y mysql mysql-libs mysql-common #卸载mysql rm -rf /var/lib/mysql #删除mysql下的数据文件 rm /etc/my.cnf #删除mysql配置文件 yum remove -y mysql-
复制链接

扫一扫

专栏目录