mysql进阶

作者：蒋增奎

版本：1.0

版权：企业云课堂

01-MYSQL进阶

-高级查询和修改-

第一章：DDL创建数据库

1.1 数据库启动及进入

在linux下

数据库服务启动

systemctl status  mysqld   #查看是否启动
systemctl start mysqld #启动

数据库进入

mysql -u root -p

登录后修改密码和允许远程访问

[root@localhost mysql]# mysql -uroot -p
Enter password: 
#登录成功后
mysql>  ALTER USER  'root'@'%'  IDENTIFIED WITH mysql_native_password BY 'segns110';
mysql> flush privileges;

#远程访问
use mysql； 
update user set host='%' where user='root';
flush privileges;

1.2数据库database创建使用

1.show databases;//显示有数据库
2.create database 数据库名;  //创建数据库
3.use  数据库名 ；//使用数据库，只有使用了才能执行sql命令
4.drop database 数据库名 ；//删除数据库
5.show tables ;//在一个数据库里查看表

1.3数据库字段类型

1.3.1数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
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的值	小数值

注意事项：

1.金额一般用decimal类型存储

2.小数字如数据字典，一般采用TINYINT 如性别等

3.主键是整数的，一般用INT或者bigint

1.3.2日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

型	大小 ( 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:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

注意1：

如果不确定存日期还是时间，用dtatime比较好

注意事项2：DATETIME和TIMESTAMP的区别

区别:

• 存储单元：datetime8字节，timestamp 4字节
• 时间范围：datetime是1000-9999年，timestamp 1970-2038年
• 时区：timestamp 存储和检索的时区不一样，数据也不一样，datetime存进去什么就是什么
• 不插入值时。timestamp 为当前时间，datetime为NULL

选择：

1.在时间值可能为null时，不要用timestamp ,他会默认为当前时间

2.如果在时间上要超过Linux时间的，或者服务器时区不一样的就建议选择 datetime。

3.如果是想要使用自动插入时间或者自动更新时间功能的，可以使用timestamp。如果create update数据时间戳

1.3.3字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
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	极大文本数据

char 和varchar的区别

为	char字段	varchar字段
最大长度	255字符	65535个字节，所以括号中最大的字符数还得通过编码来算
是否定长	定长，不足的部分用隐藏空格填充	不定长
空间使用	会有浪费	更加节省
查找效率	高	低
尾部空格	插入时省略	插入时不会省略，查找时省略
like查找	语句中like后的’ '不会省	语句中like后的’ '不会省，字段结尾的空格也不会省

总结

• char(n)中的n是字符数，范围是0~255（额外需要1到2个字节来存长度）
• varchar(n)中的n也是字符数，但是最大值需要通过编码来算，不能超过65535字节（从中还需要拿出1到2个字节来存长度）
• 一般定长的数据选用char类型，比如身份证号，手机号，电话等，长度变化很大的可以使用varchar类型
• 注意尾部空格的匹配，特别是插入时和使用like查找时

1.4 表的创建修改删除

1.4.1表创建

通用语法

CREATE TABLE table_name (column_name column_type 可选项);

实例：

drop table if exists tst1;
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：

1. 多个字段用,隔离
2. 字段可用``来包围也可不用
3. 主键声明放在最好一行，格式 PRIMARY KEY(字段名)
4. ENGINE 设置存储引擎，CHARSET 设置编码。
5. drop table if exists 表名，如果存在就先删除

可选项：

1.只针对整数：AUTO_INCREMENT 自增长

2.只针对数值：UNSIGNED大于等于0

3.NOT NULL数据不为空

创建外键

语法

alter table 外键表 add constraint 外键名【要唯一，一般可FK_表1—表2】 foreign key (外键表要增加的外键字段名)
      references 主表名 (主表要关联的字段) on delete restrict on update restrict;

实例

alter table sontbl add constraint FK_sontbl_parent1 foreign key (pid)
      references parenttbl (pid) on delete restrict on update restrict;

注意：联接修改和删除

ON DELETE CASCADE ON UPDATE CASCADE

CASCADE:删除和修改主表时把子表对应的数据修改和删除掉
restrict：如果子表有值不能删除，必须把子表值先删除掉

1.4.2表删/改

1 删除表

DROP TABLE 表名;
DROP TABLE IF EXISTS 表名; -- 删除表时判断表是否存在

2.修改表名

ALTER TABLE 表名 RENAME TO 新的表名;
eg:
 alter table t rename to tt; 

1.4.3字段增/删/改

新增字段

ALTER TABLE 表名 ADD 列名 数据类型;
eg:
ALTER  TABLE tst1 add sex VARCHAR(10) NOT NULL;

修改字段类型

ALTER TABLE 表名 MODIFY 列名 新数据类型;
eg:
ALTER  TABLE tst1 MODIFY sex VARCHAR(50);

修改字段名和数据类型

ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
eg:
ALTER  TABLE tst1 CHANGE sex  newsex VARCHAR(50);

删除字段

ALTER TABLE 表名 DROP 列名;
eg:
ALTER  TABLE tst1 DROP newsex  ;

第二章 高级查询

2.1查询条件where

比较运算符

>和>=:  大于和大于等于 
<和<=:  小于和小于等于
 =:     等于，注意不是==
<>或者!=:不等于

逻辑运算符

and      | 并且，两条件都满足           
 or      | 或者，满足其一               
 not     | 否定  not (age>18 and sex=1)

模糊查询

like "%%"

范围查询

| in           | 可以是一个范围值 in(3,5,8);可以是个子查询in (select ...) 

| not in       | in 的反义                                                

| between  and | A between 17 and 20 等于(A>=17 anf a<=20)                

| exist        | 和in 类似  

注意：in 和exist加子查询的区别

空判断

is null 和is not null

2.2聚合函数查询

常⻅的统计总数、计算平局值等操作，可以使⽤聚合函数来实现，常⻅的聚合函数有：

1. count（col）：表示求指定列的总行数
2. max（col）：表示求指定列的最大值
3. min（col）：表示求指定列的最小值
4. sum（col）：表示求指定列的和
5. avg（col）：表示指定列的平均值

注意事项：

1.count永远都有一条数据，没有记录数，显示记录为0，其他的有可能为空

2.count(*)和count(字段)的区别：

count(*)：统计的是查询的总记录数；count(字段)，统计的是这个字段非空的记录数

3.count(*),count(1),count(主键字段)，count(非主键字段)的查询效率

count(*)=count(1)>count(主键字段)>count(非主键字段)

4.聚合函数默认忽略字段为null的记录，要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换

2.3分组查询group

组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组

分组查询基本的语法格式：

group by 列名 [having 条件表达式] [with rollup]

说明：

列名：是指按照指定字段的值进行分组
having 条件表达式：用来过滤分组后的数据
with rollup：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

常见使用：

1.group by可用于单个字段分组，也可用于多个字段分组

#根据sex字段来分组

select gender from students group by sex;

#根据name和sex字段来分组

select name,sex from students group by name,sex;

2.group by 常见使用

#1.分组字段出现
select name,sex from students group by name,sex;

#2.聚合函数出现
SELECT class_id,avg(height),max(weight)  from stu GROUP BY class_id #班级平均身高和最重者

#3.GROUP_CONCAT()对应字段的集合
SELECT  class_id,GROUP_CONCAT(stu_name)  from stu GROUP BY class_id

# 4.group里用having来过滤（注意和where的区别）

SELECT  class_id,count(*)  from stu GROUP BY class_id 
HAVING count(*)>1 and AVG(height)>170
#having 只能用分组字段或者聚合函数来过过滤
【错误】：
SELECT  class_id,count(*)  from stu GROUP BY class_id HAVING count(*)>1  and height>170
group 
【正确】：先用where过滤数据，再分组
select class_id,COUNT(*) from stu where  height>140 
group by class_id

2.4多表联合查询

内联

求全部有的部分交集

select * from class a INNER JOIN  stu b ON a.class_id=b.class_id

左外联

就是inner jion +左边的表没有匹配上的数据

select * from class a left  JOIN  stu b ON a.class_id=b.class_id;

右关联

就是inner jion+右边没有匹配的表的数据

select * from class a RIGHT   JOIN  stu b ON a.class_id=b.class_id;

2.5 嵌套子查询

子查询是指嵌⼊在其他 sql 语句中的 select 语句，也叫嵌套查询。

常见用法：

（1）作为一个临时表，和其他表关联

selec  * from Atbl a,
(select * from B tble)  b where .....

(2)作为where 条件

select * from A where A.id in(select * from B)

2.6union和union all

union就是把两张表的数据合并起来返回记录集

语法:

select 字段1,字段2,字段3.. from A

union

select 字段1,字段2,字段3.. from A

合并前提条件

1.两个查询的字段数量必须相同，顺序要一致

2.数据类型要一致，长度可以不同

3.字段名可以不一样，结果是以第一个SQL字段为准

union和union all的区别

union会合并两条内容完全一样的数据，union all 则不会
注意：两条内容完全一样的内容是指各个字段对应的值都一样
如上图：
01-张三 和 01-张三1 不会合并
08-天豪 和 08-天豪  会合并

2.7 CASE WHEN用法

运用1：编码转换

select stu_name,sex,
(
CASE  sex
 WHEN 1 THEN '男'
 WHEN 0  THEN '女'
 ELSE '未知'
END 
) as sex_chinese
 from stu

效果：

运用2：case when 字段，这个可以对字段进行取范围。

SELECT a.*,

CASE

WHEN a.age BETWEEN 0 and 20 THEN '青年'

WHEN a.age BETWEEN 20 and 40 THEN '中年'

ELSE '非人类'

END AS '描述'

FROM

student

效果

、

2.10纵横表转化

（1）纵表变横表

原始数据：

SELECT
	`name` AS "姓名",
	MAX( CASE SUBJECT WHEN "语文" THEN score ELSE 0 END ) 语文,
	MAX( CASE SUBJECT WHEN "数学" THEN score ELSE 0 END ) 数学,
	MAX( CASE SUBJECT WHEN "英语" THEN score ELSE 0 END ) 英语,
	SUM(score) as 总分,
	avg(score) as 平均分
	from score
	GROUP BY `name`

效果：

（2）横表变综表

原始数据

select * from (
select `name`,yuwen as score,"语文" as subject from student
union 
select `name`,shuxue as score,"数学" as subject from student
) t ORDER BY t.name

效果

2.11其他常用函数

ifNull(字段，替换值)

如果这个字段为NULL,则替换成其他值 ，如: ifNull(sex,‘0’)

concat 拼接字段

注意：mysql里字符串链接不是+,而是concat函数

SELECT CONCAT('my name is ',stu_name,',sex is ',sex) from stu

字符串函数

常用的字符串函数
1、length(x）返回字符串x的长度
2、trim(）返回去除指定格式的值
3、concat(x,y) 将提供的参数x和y拼接成一个字符串
4、upper(x）将字符串x的所有字母变成大写字母
5、lower(x）将字符串x的所有字母变成小写字母
6、left(x,y）返回字符串x的前y个字符
7、right(x,y）返回字符串x的后y个字符
8、repeat(x,y) 将字符串x重复y次
9、space(x）返回x个空格
10、replace(x,y,z) 将字符串z替代字符串x中的字符串y
11、strcmp(x,y) 比较x和y，返回的值可以为-1,0,1
12、substring(x,y,z) 获取从字符串x中的第y个位置开始长度为z的字符串
13、reverse(x) 将字符串x反转

日期函数

MySQL也支持日期时间处理，提供了很多处理日期和时间的函数。一些常用的日期时间函数如下所示
1、curdate() 返回当前时间的年月日
2、curtime() 返回当前时间的时分秒
3、now(）返回当前时间的日期和时间
4、month(x) 返回日期×中的月份值
5、week(x) 返回日期×是年度第几个星期
6、hour(x) 返回x中的小时值
7、minute(x) 返回×中的分钟值
8、second(x) 返回×中的秒钟值
9、dayofweek(x) 返回×是星期几，1星期日，2星期一
10、dayofmonth(x) 计算日期×是本月的第几天
11、dayofyear(x) 计算日期×是本年的第几天
12. EXTRACT(unit FROM date)获取时间的具体年月日小时分钟 eg: EXTRACT(HOUR FROM now())

数学函数

1、abs(x)返回x的绝对值

2、rand()返回0到1的随机数

3、mod(x,y)返回x除以y以后的余数

4、power(x,y)返回x的y次方

5、round(x）返回离x最近的整数

6、round(x,y)保留x的y位小数四舍五入后的值

7、sqrt(x)返回x的平方根

8、truncate(x,y)返回数字x截断为y位小数的值

9、ceil(x)返回大于或等于x的最小整数

10、floor(x)返回小于或等于x的最大整数

11、greatest(x1,×2…)返回集合中最大的值

12、least(x1,x2…)返回集合中最小的值

2.12limit分页

SELECT column1, column2, … FROM table_name LIMIT [offset,] numbe

limit 开始行索引，记录条数

注意： 开始行索引是从0开始的

limit 1,3 表示第二条开始的3条数据

2.13 in 和exists区别

区别

in加子查询时，子查询返回字段只能是一个，如 where id in (select id from B)
而exists表示存在，子查询可以不止一个字段 where id exists (select * from B),只要子查询有记录是true

共同点

他们都可以解决一样的查询场景，那哪个的效率高呢？

select  * from A where id in/exists (select id from B)

1.当子查询结果集很大，而外部表较小的时候，Exists查询效率会优于IN。

2. 当子查询结果集较小，而外部表很大的时候，此时IN的查询效率会优于Exists。

表的规模不是看内部表和外部表，而是外部表和子查询结果集。

记忆：内大外小，存在(Exists)好；

第三章 高级插入和修改

2.1插入insert …select

insert into A(字段1,字段2…)[select 字段1,字段2… from ]

插入表的字段和select的字段数、顺序、数据内容要对应得上

INSERT INTO tr_user (sname, sage) 
SELECT 
  NAME AS sname,
  age AS sage 
FROM
  USER

2.2修改update …select

语法：

UPDATE T1
[INNER JOIN | LEFT JOIN] T2 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition

注意：

1. T2可以是个单表，也可是一个记录集

​ 2.inner 和left的区别

​ inner 是select记录集有对应数据才修改，否则不修改

​ left 是以左边表为准，如果记录集没有数据，左边表则设置为null

实例

UPDATE stu a INNER JOIN student b ON a.stu_id=b.sid
SET a.stu_name=b.`name`

一个容易混淆点：

如何select的记录集有多条，update只会选择更新第一条

执行：

UPDATE stu a INNER JOIN student b ON a.stu_id=b.sid
SET a.stu_name=b.`name`

发现stu表只修改成“张三1”

第四章 Mysql索引

4.1什么是索引？

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的示意图所示 :

上图就是索引二叉树，下个节点按照左小右大的原则建立树，提升检索速度

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

4.2索引的优缺点

优势

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

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

劣势

1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。

2） 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

4.3索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

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

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

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

4.3.1 BTREE

BTREE就是Balanced Tree ，一种很普遍的数据库索引结构，o。其特点是定位高效、利用率高、自我平衡，特别适用于高基数字段，定位单条或小范围数据非常高效。理论上，使用Btree在亿条数据与100条数据中定位记录的花销相同。

我们文首的二叉树就是一个最小的BTREE树，虽然效率提升，但树的层次会很高，IO读取的开销就会很大，要想办法降低树的高度。

BTREE可以是一个m叉树，二叉树 三叉树 四叉…叉越多，树的高就越小，磁盘读取的IO就越低。

m叉树最大特征：

非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1  
#ceil（）函数叫向上取整，如2.4 值应该是3
#以五叉树为例子。一个节点磁盘数据块存值应该是key=n; 2<=n<=4,就可以最多存4个key,5个指针
#注意：key对应一个数据，几个key就对应几个数据，key和数据是一一对应的。key最多数=叉数-1

BTREE数裂变过程，就是m差树的裂变

一个磁盘块最多存储m-1个数据，当第m个数据时，以前的数据块里的数据加上新进来的数据，从中间那个数据裂变，中间数据升级到父节点里去，中间左右两边数据分辨裂开成两个磁盘数据块

以btree的五叉树为例，讲解这个裂变过程，当一个磁盘块存到第五个数据时，发生裂变

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。

演变过程如下：

1). 插入前4个字母 C N G A

2). 插入H，n>4，中间元素G字母向上分裂到新的节点

3). 插入E，K，Q不需要分裂

4). 插入M，中间元素M字母向上分裂到父节点G

5). 插入F，W，L，T不需要分裂

6). 插入Z，中间元素T向上分裂到父节点中

7). 插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂

8). 最后插入S，NPQR节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂

到此，该BTREE树就已经构建完成了， BTREE树 和 二叉树 相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

BTREE的特征总结

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

4.3.2 B+Tree

B+Tree为BTree的变种，B+Tree与BTree的区别为：

1). n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。

2). B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。

3). 所有的非叶子节点都可以看作是key的索引部分。

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

m叉树的物理存储图：

重点：B+TREE 和BTREE的区别

（1）B+TREE物理结构

（2）BTREE物理结构

区别

1）BTREE的key、data和指针是放在一起的；B+树的非叶子节点中只有key和指针，data只放在叶子节点中。 　
2）在BTREE中，越靠近根节点的记录查找时间越快，只要找到key即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些

因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。

尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好

而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。

4.3.4 索引分类

1） 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引

2） 唯一索引 ：索引列的值必须唯一，但允许有空值

3） 复合索引 ：即一个索引包含多个列

4.5 索引管理

drop table if exists stu;

/*==============================================================*/
/* Table: stu                                                   */
/*==============================================================*/
create table stu
(
   stu_id               int not null,
   stu_name             varchar(50),
   stu_sex              varchar(50),
   primary key (stu_id)
);

4.5.1创建索引

语法 ：

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]

示例 ： 为stu表中的stu_name字段创建索引 ；

CREATE INDEX  idx_stu_name on stu(stu_name);
CREATE INDEX  idx_stu_name_sex on stu(stu_name,sex);//复合索引

4.5.2 查看索引

show index  from  table_name;

4.5.3删除索引

DROP  INDEX  index_name  ON  tbl_name;

eg:

drop INDEX idx_stu_sex ON stu;

4.5.4ALTER表来创建修改索引

1). alter  table  tb_name  add  primary  key(column_list); 
	该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL	
	
2). alter  table  tb_name  add  unique index_name(column_list);
	这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	
3). alter  table  tb_name  add  index index_name(column_list);
	添加普通索引， 索引值可以出现多次。
	
4). alter  table  tb_name  add  fulltext  index_name(column_list);
	该语句指定了索引为FULLTEXT， 用于全文索引

4.6 索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

• 对查询频次较高，且数据量比较大的表建立索引。

• 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

• 使用唯一索引，区分度越高，使用索引的效率越高。

• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

• 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

  创建复合索引:
  	CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
  
  就相当于
  	对name 创建索引 ;
  	对name , email 创建了索引 ;
  	对name , email, status 创建了索引 ;
  

第五章.mysql体系结构

5.1框架图

整个MySQL Server由以下组成

• Connection Pool : 连接池组件
• Management Services & Utilities : 管理服务和工具组件
• SQL Interface : SQL接口组件
• Parser : 查询分析器组件
• Optimizer : 优化器组件
• Caches & Buffers : 缓冲池组件
• Pluggable Storage Engines : 存储引擎
• File System : 文件系统

1） 连接层

最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2） 服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3） 引擎层

存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

4）存储层

数据存储层， 主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

5.2存储引擎详解

5.2.1 存储引擎概述

和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。

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

Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

注意：存储引擎是基于表的，而不是基于库的，默认是innoDB，在创建表时指定

create table stu
(
   stu_id               int not null,
   stu_name             varchar(50),
   stu_sex              varchar(50),
   primary key (stu_id)
) ENGINE=INNODB DEFAULT charset=utf8;

可以通过指定 show engines ， 来查询当前数据库支持的存储引擎 ：

show engines

5.2.2各种存储引擎特性

下面重点介绍几种常用的存储引擎， 并对比各个存储引擎之间的区别， 如下表所示 ：

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储限制	64TB	有	有	没有	有
事务安全	支持
锁机制	行锁(适合高并发)	表锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持(5.6版本之后)	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低	低
内存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外键	支持

下面我们将重点介绍最长使用的两种存储引擎： InnoDB、MyISAM ， 另外两种 MEMORY、MERGE ， 了解即

可。

5.2.2 InnoDB

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎不同于其他存储引擎的特点 2.2.1 InnoDB

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎不同于其他存储引擎的特点

事务、外键、行锁【Supports transactions, row-level locking, and foreign keys】

事务

默认事务是关闭的。用start transaction启动事务，用commit提交事务

start transaction;#启动事务
#多条插入、更新SQL语句
insert into goods_innodb(id,name)values(null,'Meta20');
insert into goods_innodb(id,name)values(null,'Meta20');
commit;#提交事务

外键约束

支持主外键约束

create table country_innodb(
	country_id int NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL,
    primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


create table city_innodb(
	city_id int NOT NULL AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int NOT NULL,
    primary key(city_id),
    key idx_fk_country_id(country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ON DELETE RESTRICT:删除主表，如果有关联记录，不删除
-- ON UPDATE CASCADE:更新主表，如果子表有关联记录，更新子表记录

insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);

外键语法

CONSTRAINT `外键名` FOREIGN KEY(子表字段名) REFERENCES 主表名(主表字段名) ON DELETE RESTRICT ON UPDATE CASCADE

在ON DELETE/UPDATE 可以挂不同约束类型

包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION，在实际应用主要用前两者。

CASCADE表示父表在更新或者删除时，更新或者删除子表对应的记录；

RESTRICT表示限制在子表有关联记录的情况下， 父表不能删除更新；

RESTRICT和NO ACTION相同， 是指限制在子表有关联记录的情况下， 父表不能更新；

SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL 。

linux存储

InnoDB 存储表和索引有以下两种方式 ：

①. 使用共享表空间存储， 这种方式创建的表的表结构保存在.frm文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。

②. 使用多表空间存储， 这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。【#注意mysql8已经合并两个文件】

[root@centos7-1 var]# cd /var/lib/mysql
[root@centos7-1 mysql]# ll
drwxr-x---. 2 mysql mysql       28 9月  10 00:17 sys
drwxr-x---. 2 mysql mysql      139 9月  16 11:03 test  #建立的数据库对应的文件夹

[root@centos7-1 mysql]# cd test #进入数据库
[root@centos7-1 test]# ll  #注意mysql8已经合并两个文件
总用量 784
-rw-r-----. 1 mysql mysql 114688 9月  15 21:22 class.ibd
-rw-r-----. 1 mysql mysql 114688 9月  15 23:13 Employees_China.ibd
-rw-r-----. 1 mysql mysql 114688 9月  15 23:13 Employees_USA.ibd
-rw-r-----. 1 mysql mysql 114688 9月  16 11:13 student.ibd
-rw-r-----. 1 mysql mysql 114688 9月  16 11:14 stu.ibd
-rw-r-----. 1 mysql mysql 114688 9月  15 19:20 tst1.ibd
-rw-r-----. 1 mysql mysql 114688 9月  15 17:39 tt.ibd

5.2.3 MyISAM

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。有以下两个比较重要的特点：

create table goods_myisam(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;

优点：访问快

缺点：不支持事务、只支持表锁

文件存储方式

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是 ：

.frm (存储表定义)；【mysql8已没有】

.MYD(MYData , 存储数据)；

.MYI(MYIndex , 存储索引)；

[root@centos7-1 test]# cd /var/lib/mysql/test
[root@centos7-1 test]# ll
总用量 808
-rw-r-----. 1 mysql mysql      0 9月  18 10:28 goods_myisam.MYD
-rw-r-----. 1 mysql mysql   1024 9月  18 10:28 goods_myisam.MYI

5.2.4 存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。

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

第六章 视图/函数/存储过程

表准备

drop table if exists class;

/*==============================================================*/
/* Table: class                                                 */
/*==============================================================*/
create table class
(
   class_id             int not null auto_increment,
   class_name           varchar(100),
   primary key (class_id)
);


drop table if exists stu;

/*==============================================================*/
/* Table: stu                                                   */
/*==============================================================*/
create table stu
(
   stu_id               int not null auto_increment,
   cid                  int,
   stu_name             varchar(50),
   stu_sex              varchar(50),
   address              varchar(50),
   age                  int,
   primary key (stu_id)
);

6.1视图

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势主要包括以下几项。

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

创建视图

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

选项：一般不会用到

WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。
	
	LOCAL ： 只要满足本视图的条件就可以更新。
	CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.

eg:

CREATE VIEW stu_view
AS
select a.*,b.class_name FROM stu a
INNER JOIN class b ON a.cid=b.class_id

注意：创建视图时我们可以加_view后缀，便于维护

删除视图

DROP VIEW 视图名 ;

6.2存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

函数 ： 是一个有返回值的过程 ；

过程 ： 是一个没有返回值的函数 ；

创建存储过程

CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
	-- SQL语句
end ;

eg:

CREATE PROCEDURE  first_proc()
BEGIN
SELECT * from stu;
END

调用存储过程

call 存储过程名称();

删除存储过程

  DROP PROCEDURE  [IF EXISTS] sp_name ；

6.3函数

函数有返回值

CREATE FUNCTION function_name([param type ... ]) 
RETURNS type   #注意是returns 不是return
BEGIN
	...
END;

例子

set global log_bin_trust_function_creators=TRUE;
CREATE FUNCTION first_func(num int)
RETURNS INT
BEGIN
DECLARE ret INT DEFAULT 10;
SET ret=ret+num;
return ret;
END;

6.4mysql 脚本语法

-- 创建存储过程及语法练习
# create procedure procedure_name([in/out/inout] 参数名   参数类型)
#IN :   该参数可以作为输入，也就是需要调用方传入值 , 默认
#OUT:   该参数作为输出，也就是该参数可以作为返回值
#INOUT: 既可以作为输入参数，也可以作为输出参数
CREATE PROCEDURE first_proc(IN a INT,OUT str VARCHAR(100))
BEGIN
#申请变量并赋值  DECLARE 变量名 字段类型 [default value]
#注意声明变量必须放置在开头，放到其他地方是错的
DECLARE  str1 varchar(100) DEFAULT '字符串'; 
DECLARE  num  INT;

DECLARE stu_name VARCHAR(100);
DECLARE address  VARCHAR(100);
DECLARE stu_result CURSOR FOR SELECT * FROM stu;  #声明游标
SET  num=100;        #赋值
SET str='大哥';
-- DECLARE  num2  INT;   #放到这里会报错
#通过select语句赋值，select  ..into 
# select count(*) into num from stu;

#if 条件语句
IF num>1  AND num<50 THEN
SET str='大哥1-50';
ELSEIF num>50 AND num<100  THEN
SET str='大哥50-100';
ELSE
SET str='大哥大于100';
END IF;

#while循环语句
WHILE num<150 DO
SET num=num+1;
SET str=CONCAT('',num);
END WHILE;

 open stu_result;  #开始游标，
  
 fetch stu_result into stu_name,address;  #获取游标的数据，并赋值给变量

 close stu_result; #关闭游标

END

第七章Mysql锁

10.1 锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。

在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

10.2 锁分类

从对数据操作的粒度分 ：

1） 表锁：操作时，会锁定整个表。

2） 行锁：操作时，会锁定当前操作行。

从对数据操作的类型分：

1） 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

2） 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

10.3 Mysql 锁

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：

存储引擎	表级锁	行级锁	页面锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持(默认)	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

MySQL这3种锁的特性可大致归纳如下 ：

锁类型	特点
表级锁	偏向MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁	偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁	开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

10.4 MyISAM 表锁

MyISAM 存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显示加表锁语法：

加读锁 ： lock table table_name read;

加写锁 ： lock table table_name write；
解   锁：   unlock tables 

数据：

CREATE TABLE `tb_book` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  `publish_time` DATE DEFAULT NULL,
  `status` CHAR(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思想','2088-08-08','0');



CREATE TABLE `tb_user` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');

加读锁案例

打开两个客户端，模拟会话session

########session1锁定
lock table tb_book read; #读锁定
seelct * from tb_book  #锁定表可查

update tb_book set name='sss' where id=1; #锁定表不能改
update tb_user set name='d' where id=1;  #其他表不能改
seelct * from tb_user；   #其他表也不能读

###############session2操作
seelct * from tb_book ； #锁定表可查
seelct * from tb_user；   #其他表可读
update tb_book set name='sss' where id=1; #锁定表阻塞不能改
update tb_user set name='d' where id=1;  #其他表能改

总结：

锁表后
1.在连接会话后，本会话里除了锁定表可查，其他表都不可查和改
2.其他会话里，所有表可查，锁表不能改，但其他表可改

加写锁案例

###########session1==========加写锁
mysql> lock table tb_book write;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from tb_book; #可读

mysql> insert into tb_book(name)values('大话王'); #可写
Query OK, 1 row affected (0.02 sec)

###########session2==========

mysql> select * from tb_book; #不可读
mysql> insert into tb_book(name)values('大话王'); #不可写
===========session1====解锁==========
mysql> unlock tables;

总结：

加写锁后：
1.本用户操作都没有问题
2.其他用户都不可读写此表

可见：

由上表可见：

 1） 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

 2） 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；

 简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。

此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞,但对大量读的表有优势

10.5 InnoDB 行锁

1 行锁介绍

行锁特点 ：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

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

2 背景知识-事务

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元。

事务具有以下4个特性，简称为事务ACID属性。

ACID属性	含义
原子性（Atomicity）	事务是一个原子操作单元，其对数据的修改，要么全部成功，要么全部失败。
一致性（Consistent）	在事务开始和完成时，数据都必须保持一致状态。
隔离性（Isolation）	数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境下运行。
持久性（Durable）	事务完成之后，对于数据的修改是永久的。

并发事务处理带来的问题

问题	含义
丢失更新（Lost Update）	当两个或多个事务选择同一行，最初的事务修改的值，会被后面的事务修改的值覆盖。
脏读（Dirty Reads）	当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读（Non-Repeatable Reads）	一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现和以前读出的数据不一致。
幻读（Phantom Reads）	一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据。

事务隔离级别

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行，这显然与“并发” 是矛盾的。

数据库的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

隔离级别	丢失更新	脏读	不可重复读	幻读
Read uncommitted	×	√	√	√
Read committed	×	×	√	√
Repeatable read（默认）	×	×	×	√
Serializable	×	×	×	×

备注 ： √ 代表可能出现 ， × 代表不会出现 。

Mysql 的数据库的默认隔离级别为 Repeatable read ， 查看方式：

show variables like 'tx_isolation';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kDUP8RMR-1664120461584)(D:\BaiduNetdiskDownload\MySQL高级\MySQL高级.assets\1554331600009.png)]

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；`

对于普通SELECT语句，InnoDB不会加任何锁`

InnoDB 实现了以下两种类型的行锁。

• 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
• 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

数据准备

create table test_innodb_lock(
	id int(11),
	name varchar(16),
	sex varchar(1)
)engine = innodb default charset=utf8;

insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');

create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

案例演示1：

UPDATE、DELETE和INSERT是自动加锁的，这里把事务改成手动

---

update 行锁演示通过关闭自动事务，来体验

线程会话1	线程会话2
set autocommit=0; #set 关闭自动事务	set autocommit=0; #set 关闭自动事务
select * from test_innodb_lock where id=3;#读取不影响	select * from test_innodb_lock where id=3;#读取不影响
update test_innodb_lock set name=‘3000’ where id=3	update test_innodb_lock set name=‘x3000’ where id=3 ;#被阻塞；
	update test_innodb_lock set name=‘4’ where id=4；#不会阻塞，因为是行锁
commit; #提交	where id=3 ;#阻塞解除；
	cmmit; #提交
select * from test_innodb_lock where id=3 ;#发现会话2的修改并没有该表;
commit; #提交
select * from test_innodb_lock where id=3;#已经改变；事务隔离

update是自动加的排他锁

3 select行锁

select行锁分为悲观锁和乐观锁两种

悲观锁和乐观锁并不是某个具体的“锁”而是一种并发编程的基本概念，是根据看待并发同步的角度。乐观锁和悲观锁最早出现在数据库的设计当中，后来逐渐被 Java 的并发包所引入。

3.1悲观锁

悲观锁认为对于同一个数据的并发操作一定是会发生修改的，采取加锁的形式，悲观地认为，不加锁的并发操作一定会出问题。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。Java中Synchronized和ReentrantLock等独占锁就是悲观锁思想实现的。

悲观锁包含：共享锁和排他锁

3.1.1.共享锁

又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

走的是S锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加S锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。

注意：本线程是可写的，但其他线程不能修改

例子

################session 1

set autocommit=0;#关闭自动事务
SELECT * from  test_innodb_lock where id=4  LOCK IN SHARE MODE; #只读锁定
################session 2
set autocommit=0;#关闭自动事务
SELECT * from  test_innodb_lock where id=4  LOCK IN SHARE MODE; #读没有问题
update test_innodb_lock set name='session2-5' where id=5 ;  #其他行没有阻塞
update test_innodb_lock set name='session2-4' where id=4 ; #锁定写阻塞
################session 1
update test_innodb_lock set name='session1-4' where id=4 ; #写被阻塞
commit;
################session 2
update 阻塞解除
commit;
################session 1
SELECT * from  test_innodb_lock where id=4 ; #session2修改没有被传递，事务隔离
commit;
SELECT * from  test_innodb_lock where id=4 ;#最新数据

原理：

共享锁是可以叠加的，相同的程序在不同的进程里同时执行，大家都只能进行读操作，如果进行写操作，

就会出现死锁问题，异常后事务回滚。所以共享锁千万不能又读这个表又同时操作这个表，要谨慎。

应用场景

SELECT … LOCK IN SHARE MODE的应用场景适合于两张表存在关系时的写操作，拿mysql官方文档的例子来说，一个表是child表，一个是parent表，假设child表的某一列child_id映射到parent表的c_child_id列，那么从业务角度讲，此时我直接insert一条child_id=100记录到child表是存在风险的，因为刚insert的时候可能在parent表里删除了这条c_child_id=100的记录，那么业务数据就存在不一致的风险。
         正确的方法
#######插入子表前执行：
1. select * from parent where c_child_id=100 lock in share mode #锁定后父表不能删除、修改
##############执行子表后提交事务============
insert into child(child_id) values (100)；
commit;

总结：在同一程序里，又读又写某条数据要严格禁用共享模式，in share mode

3.1.2 排他锁

SELECT … FOR UPDATE 走的是X锁(意向排它锁)，即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁。如果不存在一致性非锁定读的话，那么其他session是无法读取和修改这些记录的，但是innodb有非锁定读(快照读并不需要加锁)，for update之后并不会阻塞其他session的快照读取操作，除了select …lock in share mode和select … for update这种显示加锁的查询操作。

例子：

################session 1
begin;
select * from test_innodb_lock  where id=3 for update;  #他独占后就排他
################session 2

begin;
select * from test_innodb_lock  where id=3 #不加锁，能访问
select * from test_innodb_lock  where id=3 for update;#加排他锁不能访问，形成阻塞
update test_innodb_lock set name='xx' where id=3; #不能修改
SELECT * from  test_innodb_lock where id=4  LOCK IN SHARE MODE；#加共享锁也不能访问，排他锁是独占的
################session 1
update test_innodb_lock set name='xx' where id=3; #能修改，独占了。
################session 1
commit; #提交后，session2 形成的阻塞就能进行了

应用场景：

典型应用，电子商务平台的商品表里的商品商品数量，再抢购、优惠券、秒杀设计中，查询还有商品数量后下单，下单后商品数量减一。

1 select amount from product where product_name='XX';
2 update product set amount=amount-1 where product_name='XX';

这个是容易产生并发的，显然是有问题，因为如果1查询出amount为1，但是这时正好其他session也买了该商品并产生了订单，那么amount就变成了0，那么这时第二步再执行就有问题。

那么采用lock in share mode可行吗，也是不合理的，因为两个session同时锁定该行记录时，这时两个session再update时必然会产生死锁导致事务回

所以这里只能用排他锁，先进入如的程序必须执行完，才能读取数据

一个线程,执行完后，另一个现场才启动读程序：

1 select amount from product where product_name='XX' for update;
2 update product set amount=amount-1 where product_name='XX';

3.1.3排他锁和共享锁的区别

共享锁和排他锁都是悲观锁，区别是共享锁大家都可以叠加，系统不阻塞 lock in share mode的表，但排他说for update是排他的，不能叠加，只能等一个进程执行完后，才能执行第二个。第二个select … for uodate 会阻塞掉

3.2乐观锁

乐观锁正好和悲观锁相反，它获取数据的时候，并不担心数据被修改，每次获取数据的时候也不会加锁，只是在更新数据的时候，通过判断现有的数据是否和原数据一致来判断数据是否被其他线程操作，如果没被其他线程修改则进行数据更新，如果被其他线程修改则不进行数据更新。

乐观锁，简单地说，就是从应用系统层面上做并发控制，去加锁。
实现乐观锁常见的方式：版本号version
实现方式，在数据表中增加版本号字段，每次对一条数据做更新之前，先查出该条数据的版本号，每次更新数据都会对版本号进行更新。在更新时，把之前查出的版本号跟库中数据的版本号进行比对，如果相同，则说明该条数据没有被修改过，执行更新。如果比对的结果是不一致的，则说明该条数据已经被其他人修改过了，则不更新，客户端进行相应的操作提醒。

使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新的版本号。

实例：

商品表里增加一个版本号version的字段，数量amount和版本号version作为重要的业务判断

begin;#启动事务
 select amount，version from product where product_id=?
 if(amount>0){
     ---------执行其他操作，如新增订单表insert或者update-------
     String version=product.version #字段读出来
    int updateNum= update product set amount=amount-1,version=version+1 where  product_id=? and version=${version};#重点通过ID和版本号取修改;
    if(updateNum==0){#说明其他并发修改了版本号，回滚并提示用户
        rollback;#事务回滚
    }
    else
     commit;//提交事务
 }
 else{
     下单失败
 }

3.3 总结：乐观锁悲观锁

乐观锁和悲观锁的思路不一样，乐观锁和悲观锁并没有优劣之分，它们有各自适合的场景。

• 在竞争不激烈（出现并发冲突的概率比较小）的场景中，乐观锁更有优势。因为悲观锁会锁住代码块或数据，其他的线程无法同时访问，必须等待上一个线程释放锁才能进入操作，会影响并发的响应速度。另外，加锁和释放锁都需要消耗额外的系统资源，也会影响并发的处理速度。

  乐观锁不会产生脏数据，但可能导致用户的业务处理失败，需要用户再次提交处理，所以乐观锁天然适合并发较少的业务，从而不影响性能

• 在竞争激烈（出现并发冲突的概率较大）的场景中，悲观锁则更有优势。因为乐观锁在执行更新的时候，可能会因为数据被反复修改而更新失败，进而不断重试，造成CPU资源的浪费。

乐观锁是否会加锁：

    乐观锁本身是不加锁的，只有在更新的时候才会去判断数据是否被其他线程更新了，比如AtomicInteger便是一个例子。但是有时候乐观锁可能会与加锁操作合作，比如MySQL在执行更新数据操作的时候会加上排他锁。因此可以理解为乐观锁本身是不加锁的，只有在更新数据的时候才有可能会加锁。

4.无索引行锁升级为表锁

在开发过程中，如果查询没有使用索引，则行锁会升级成为表锁，我们在开发时要注意到这一点

5.InnoDB 行锁争用情况

show  status like 'innodb_row_lock%';

Innodb_row_lock_current_waits: 当前正在等待锁定的数量

Innodb_row_lock_time: 从系统启动到现在锁定总时间长度

`Innodb_row_lock_time_avg:每次等待所花平均时长（重点）

Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间

`Innodb_row_lock_waits: 系统启动后到现在总共等待的次数（重点）


当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

6 总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。

但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

优化建议：

• 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。

• 合理设计索引，尽量缩小锁的范围

• 尽可能减少索引条件，及索引范围，避免间隙锁

• 尽量控制事务大小，减少锁定资源量和时间长度

• 尽可使用低级别事务隔离（但是需要业务层面满足需求）

第八章Mysql日志

在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件。MySQL 也不例外，在 MySQL 中，有 4 种不同的日志，分别是错误日志、二进制日志（BINLOG 日志）、查询日志和慢查询日志，这些日志记录着数据库在不同方面的踪迹。

8.1错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

该日志是默认开启的 ， 默认存放目录为 mysql 的数据目录（var/lib/mysql）, 默认的日志文件名为 hostname.err（hostname是主机名）。

查看日志位置指令 ：

show variables like 'log_error%';

8.2二进制日志

1 概述

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

二进制日志，默认情况下是没有开启的，需要到MySQL的配置文件中开启，并配置MySQL日志的格式。

配置文件位置 : /usr/my.cnf（mysql8不一样）于

日志存放位置 : 配置时，给定了文件名但是没有指定路径，日志默认写入Mysql的数据目录。

#配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin

#配置二进制日志的格式
binlog_format=STATEMENT

注意：

修改配置文件后要重启数据库 systemctl restart mysqld

2 日志格式

STATEMENT

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

记录的增、改、删的sql语句，没有select语句
=======日志内容============
/*!*/;
# at 317
#220924 12:59:48 server id 1  end_log_pos 449 CRC32 0xe5f2a4e1 	Query	thread_id=12	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1663995588/*!*/;
INSERT INTO `tt` (`id`, `username`) VALUES ('1', '的')
/*!*/;

ROW

该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。比如，执行SQL语句 ： update tb_book set status=‘1’ , 如果是STATEMENT 日志格式，在日志中会记录一行SQL文件； 如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更。

MIXED

这是目前MySQL默认的日志格式，即混合了STATEMENT 和 ROW两种格式。默认情况下采用STATEMENT，但是在一些特殊情况下采用ROW来进行记录。MIXED 格式能尽量利用两种模式的优点，而避开他们的缺点。

3 日志读取

由于日志以二进制方式存储，不能直接读取，需要用mysqlbinlog工具来查看，语法如下 ：

mysqlbinlog log-file；

查看STATEMENT格式日志

执行插入语句 ：

insert into tb_book values(null,'Lucene','2088-05-01','0');

查看日志文件 ：

可用

mysql> show binary logs;

mysqlbin.index : 该文件是日志索引文件 ， 记录日志的文件名；

mysqlbing.000001 ：日志文件

查看日志内容 ：

mysqlbinlog mysqlbing.000001；

查看ROW格式日志

配置 :

#配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin

#配置二进制日志的格式
binlog_format=ROW

插入数据 :

insert into tb_book values(null,'SpringCloud实战','2088-05-05','0');

如果日志格式是 ROW , 直接查看数据 , 是查看不懂的 ; 可以在mysqlbinlog 后面加上参数 -vv

mysqlbinlog -vv mysqlbin.000002 

4 日志删除

对于比较繁忙的系统，由于每天生成日志量大 ，这些日志如果长时间不清楚，将会占用大量的磁盘空间。下面我们将会讲解几种删除日志的常见方法 ：

方式一

通过 Reset Master 指令删除全部 binlog 日志，删除之后，日志编号，将从 xxxx.000001重新开始 。

查询之前 ，先查询下日志文件 ：

执行删除日志指令：

mysql> Reset Master

执行之后， 查看日志文件 ：

方式二

执行指令 purge master logs to 'mysqlbin.******' ，该命令将删除 ****** 编号之前的所有日志。

方式三

执行指令 purge master logs before 'yyyy-mm-dd hh24:mi:ss' ，该命令将删除日志为 “yyyy-mm-dd hh24:mi:ss” 之前产生的所有日志 。

方式四

设置参数 --expire_logs_days=# ，此参数的含义是设置日志的过期天数， 过了指定的天数后日志将会被自动删除，这样将有利于减少DBA 管理日志的工作量。

配置如下 ：

8.3 查询日志

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

默认情况下， 查询日志是未开启的。如果需要开启查询日志，可以设置以下配置 ：

#该选项用来开启查询日志 ， 可选值 ： 0 或者 1 ； 0 代表关闭， 1 代表开启 
general_log=1

#设置日志的文件名 ， 如果没有指定， 默认的文件名为 host_name.log 
general_log_file=file_name

在 mysql 的配置文件 /usr/my.cnf 中配置如下内容 ：

#该选项用来开启查询日志 ， 可选值 ： 0 或者 1 ； 0 代表关闭， 1 代表开启

general_log=1

#设置日志的文件名 ， 如果没有指定， 默认的文件名为 host_name.log 

general_log_file=mysql_query.log  #文件在mysql目录里

运行几个select

[root@centos7-1 mysql]# cat mysql_query.log 
/usr/sbin/mysqld, Version: 8.0.27 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2022-09-24T08:08:34.078664Z	    8 Connect	root@localhost on test using Socket
2022-09-24T08:08:34.098198Z	    8 Query	show databases
2022-09-24T08:08:34.157305Z	    8 Query	show tables
2022-09-24T08:08:40.814653Z	    8 Query	select * from tt
2022-09-24T08:08:55.825281Z	    8 Query	select * from test_innodb_lock
2022-09-24T08:10:20.295927Z	    8 Query	show variables like 'log_%'

8.4 慢查询日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志。long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒。

文件位置和格式

慢查询日志默认是关闭的 。可以通过两个参数来控制慢查询日志 ：

# 该参数用来控制慢查询日志是否开启， 可取值： 1 和 0 ， 1 代表开启， 0 代表关闭
slow_query_log=1 

# 该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log

# 该选项用来配置查询的时间限制， 超过这个时间将认为值慢查询， 将需要进行日志记录， 默认10s
long_query_time=10

日志的读取

运行一个百万级的SQL语句

[root@centos7-1 mysql]#  cat slow_query.log
/usr/sbin/mysqld, Version: 8.0.27 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2022-09-24T08:20:41.851220Z
# User@Host: root[root] @ localhost []  Id:    10
# Query_time: 12.480017  Lock_time: 0.000099 Rows_sent: 3000000  Rows_examined: 3000000
use test;
SET timestamp=1664007629;
select * from class;

第九章 MYSQL优化

9.1 SQL优化1-优化SQL步骤

在应用的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化，本章将详细介绍在 MySQL 中优化 SQL 语句的方法。

当面对一个有 SQL 性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题。

9.1.1 查看SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的统计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

show global status like 'Com_______'; #7个_, 需要加global，不加则是本次连接的操作
mysql> show status like 'Innodb_rows_%'; #查看影响记录的行数

下面的命令显示了当前 session 中所有统计参数的值,可统计增删改查的执行次数：

show global status like 'Com_______';  #需要加global，不加则是本次连接的操作
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 9     |
| Com_import    | 0     |
| Com_insert    | 1     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 94    |
| Com_signal    | 0     |

查看影响记录的行数：

mysql> show status like 'Innodb_rows_%'; #查看影响记录的行数
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 8     |
| Innodb_rows_inserted | 4     |
| Innodb_rows_read     | 328   |
| Innodb_rows_updated  | 19    |
+----------------------+-------+

9.12 定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句。

• 慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句，用–log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。具体可以查看本书第 26 章中日志管理的相关部分。
• show processlist : 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL正在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

show processlist  #显示当前执行情况的SQL数据

1） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看

2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

4） db列，显示这个进程目前连接的是哪个数据库

5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等

6） time列，显示这个状态持续的时间，单位是秒

7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成

8） info列，显示这个sql语句，是判断问题语句的一个重要依据

state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成

9.1.3 explain SQL执行分析器

9.1.3.1explain sql语句概述

过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序

explain sql语句

eg:

explain解析器的作用：

SELECT 语句执行过程中表如何连接和连接的顺序

字段喊一下

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------> all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

9.1.3.2数据准备

准备三张表：t_role，t_user，user_role并初始化数据

CREATE TABLE `t_role` (
  `id` varchar(32) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `user_role` (
  `id` int(11) NOT NULL auto_increment ,
  `user_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ur_user_id` (`user_id`),
  KEY `fk_ur_role_id` (`role_id`),
  CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');



INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');


INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

9.1.3.3 explain 之 id

说明：

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
id值越大，则优先执行
id值相同，则从上到下顺序执行

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ; 

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

9.1.3.4explain 之 select_type

表示 SELECT 的类型，常见的取值，如下表所示：从上往下效率越来越低

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

9.1.3.5 explain 之 type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。const于将 "主键" 或 "唯一" 索引的所有部分与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的多表关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个），就是对非唯一索引的字段进行查询
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index	index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行;如:select * from t_user 是 * +整张表

结果值从最好到最坏以此是：

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL


system > const > eq_ref > ref > range > index > ALL

区别3个type

SELECT *  FROM tst1     #type=all，扫描整张表
SELECT id  FROM tst1    #type=index 只遍历索引树
SELECT id  FROM tst1 where name='ss'  #type=all name是非索引字段，需要扫描整张表去过滤，优先级比id高

注意：

一般来说，在大表里， 我们需要保证查询至少达到 range 级别， 最好达到ref 。

9.1.3.6 explain 之 key

possible_keys : 显示可能应用在这张表的索引， 一个或多个。 

key ： 实际使用的索引， 如果为NULL， 则没有使用索引。

key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。

9.1.3.7 explain 之 rows

扫描行的数量。

9.1.3.8 explain 之 extra

其他的额外的执行计划信息，在该列展示 。需要优化的前面两个，保持的是后面using index

其他的额外的执行计划信息，在该列展示 。需要优化的前面两个，保持的是后面using index

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。 ，出现这种情况是order排序用了非索引字段
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低
using index	表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。

9.1.3.9分析总结

• 看id,分析执行顺序

• 分析type，尽量不出现 index和all

• 分析key,满足业务的情况下，长度越短越好

• 分析extra 出现using filesor和using temporary

• 分析row 扫描的行数，注意扫描的行数不代表输入的记录集行数。值太大，能否优化

  得出规律性的优化建议：

  1.select 不用*的尽量不用  *,取字段的尽量有索引
  
  2.where ,order,group by 尽量里面的内容尽量用索引字段
  
  3.尽量不要扫描整张表
  

9.2 SQL优化2-索引正确使用

我们在进行查询大表的时候，针对速度慢，可使用索引，但并不是建立索引就能有效利用起来。很多情况都会索引都会失效

数据准备

-- ----------------------------
-- Table structure for index_test
-- ----------------------------
DROP TABLE IF EXISTS `index_test`;
CREATE TABLE `index_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `u_name` varchar(255) DEFAULT NULL,
  `u_sex` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of index_test
-- ----------------------------
INSERT INTO `index_test` VALUES ('1', 'zhangsan', '1', '28', '成都');
INSERT INTO `index_test` VALUES ('2', 'wangwu', '0', '47', '成都');
INSERT INTO `index_test` VALUES ('3', 'lisi', '1', '50', '成都');
INSERT INTO `index_test` VALUES ('4', 'mike', '1', '23', '成都');
INSERT INTO `index_test` VALUES ('5', 'jone', '1', '45', '成都');
INSERT INTO `index_test` VALUES ('6', 'tom', '1', '23', '成都');
INSERT INTO `index_test` VALUES ('7', 'tony', '1', '23', '北京');

• 尽量用复合索引代替多单列索引

多个单列索引进行组合查询的时候，mysql只会选择一个最佳索引，其他的忽略掉

例子：

单独给u_name,u_sex,address增加索引

#创建索引
create index idx_name on index_test(u_name);
create index idx_sex on index_test(u_sex);
create index idx_address on index_test(address);
#查询
EXPLAIN SELECT * from index_test  where u_name='zhangsan' and u_sex='1' and  address='成都'

如图所示，实际只用了一个idx_name的索引，其他的都被忽略掉了。

创建联合索引测试

drop index idx_name on index_test;
drop index idx_sex on index_test;
drop index idx_address on index_test;
create INDEX idx_name_sex_address ON index_test(u_name,u_sex,address);#复合索引
#查询
EXPLAIN SELECT * from index_test  where u_name='zhangsan' and u_sex='1' and  address='成都'

三个索引都生效了。

• 复合索引-最左原则

要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

eg:create INDEX idx_name_sex_address ON index_test(u_name,u_sex,address);#复合索引

生效：只要包含了u_name

EXPLAIN SELECT * from index_test  where u_name='zhangsan';
EXPLAIN SELECT * from index_test  where u_name='zhangsan' and u_sex='1';
EXPLAIN SELECT * from index_test  where u_name='zhangsan' and u_sex='1' and  address='成都'
EXPLAIN SELECT * from index_test  where  u_sex='1' and  address='成都' and  u_name='zhangsan'

失效：

EXPLAIN SELECT * from index_test  where  u_sex='1' and  address='成都'； #没有u_name，完全失效
EXPLAIN SELECT * from index_test  where u_name='zhangsan'  and  address='成都' #虽然最左有name,但中间列sex没有，则addres失效，只有u_name生效

总结：

要把最常用的放到最做左边，要包含最左边的，中间最好不要跳

其他索引会失效

1.有查询范围的，范围右边的查询索引会失效
create INDEX idx_name_age_address ON index_test(u_name,age,address);#复合索引
EXPLAIN SELECT * from index_test  where u_name='zhangsan' and age>0 and  address='成都' #address失效
2.在索引列上进行运算操作， 索引将失效。
EXPLAIN SELECT *   from index_test  where SUBSTRING(u_name,2,3)='one'
3.字符串不加单引号，造成索引失效。
EXPLAIN SELECT *   from index_test  where u_name='zhangsan' and u_sex=1 #u_sex失效
4.用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
5.以%开头的Like模糊查询，索引失效。解决方案：在字段 like '%xx' 把字段放入select .. from里面
6.尽量使用覆盖索引，避免select *，尽量在seelct 索引字段集 from xxxx
7.在mysql自我优化时，如果判断走索引比不走索引快，则不使用索引： is null ,is not null.
8.in 走索引， not in 索引失效

总结：避免索引失效原则

1.尽量用复合索引

2.复合索引要尽量坚持最左原则，最常用查询放到最左边

3.有查询范围的:between,>，要放到最后

4.or 必须要全索引，否则都失效

5.like ‘%xx’，要用select 索引字段来解决

6.尽量不要用运算操作

7.字符必须有单引号

8.尽量不要用select * 用 select 索引字段

9.3 SQL优化3-SQL 语句优化

9.3.1大数据量插入优化

当使用load 命令导入数据的时候，适当的设置可以提高导入的效率。

对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：

1） 主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

2） 关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

3） 手动提交事务

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

9.3.2 insert 优化

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

• 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

  示例， 原始方式为：

  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  

  优化后的方案为 ：

  insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');
  

• 在事务中进行数据插入。

  start transaction;
  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  commit;
  

• 数据有序插入

  insert into tb_test values(4,'Tim');
  insert into tb_test values(1,'Tom');
  insert into tb_test values(3,'Jerry');
  insert into tb_test values(5,'Rose');
  insert into tb_test values(2,'Cat');
  

  优化后

  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  insert into tb_test values(4,'Tim');
  insert into tb_test values(5,'Rose');
  

9.3.3 order by 优化

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');

create index idx_emp_age_salary on emp(age,salary); #增加索引

mysql有两种排序方式：

1). 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。效率低
2). 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。y

EXPLAIN select * from emp ORDER BY age;        #Using filesort
EXPLAIN select * from emp ORDER BY age,salary;  #Using filesort


EXPLAIN select id from emp ORDER BY age ;               #Using index
EXPLAIN select id from emp ORDER BY age, salary;       #Using index
EXPLAIN select id,age,salary from emp ORDER BY age      #Using index


EXPLAIN select id,age,salary,name from emp ORDER BY age #Using filesort 因为name是非索引字段

EXPLAIN select id,age,salary from emp ORDER BY age asc,salary desc #第一个index ，第二个filesort 因为升降序不一致导致

EXPLAIN select id from emp ORDER BY salary,age;#第一个index ，第二个filesort 因为没有和复合索引的顺序保持一致

EXPLAIN select id from emp ORDER BY age,name ;#索引失效，因为name非索引字段

总结：

（1）如果是select * from xx order … 索引都会失效

(2) 如果select 有非索引字段 from xx order … 索引都会失效

(3）如果 order by 多字段，里有非索引字段，索引都会失效

(4)如果order by 多字段，没有按照索引先后顺序进行，在第二个起会失效

(5)如果order by 的asc,desc 升降序一致，在第二个起会失效

最佳实践：

• order by 里面字段都加索引
• 返回记录集result,只包含索引字段
• where 条件和Order by 使用相同的索引

部分失效:

• order by 多字段顺序没有和索引保持一致;
• 升降序混搭

Filesort 的优化

如果业务无法避免Filesort,尽量把他读到内存里去排序。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

9.3.4 group by 优化

由于GROUP BY 默认实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ：

drop index idx_emp_age_salary on emp;

explain select age,count(*) from emp group by age; #无法使用索引

create index idx_emp_age_salary on emp(age,salary)；
explain select age,count(*) from emp group by age; #使用索引

总结：group by 也会使用索引，所以group里面的字段也加上索引，可以增加查询速度

9.3.5嵌套子查询优化

嵌套子查询实际上是生成一张临时表，再用临时表与其他表关联，可尝试用inner语句来代替子查询

9.3.6 or查询优化

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。

create index idx_age_salary on emp(age,salary)
EXPLAIN select * from emp where age=10 or salary=59; #复合索引无效，必须使用单列索引

优化方案：用union替换掉or

9.3.7 limit优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

EXPLAIN select class_id from class LIMIT 1000000,3  #全表扫描无索引
EXPLAIN select * from class a,(select class_id  from class  ORDER BY class_id limit 2000000,3) b where a.class_id=b.class_id #优化1：获得索引取ID
EXPLAIN select *  from class where class_id>2000000 limit 0,3  #自增长方案

9.4 运维级-Mysql配置优化

9.4.1内存优化

内存优化原则

1） 将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序预留足够内存。

2） MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。

3） 排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽。

InnoDB 内存优化

innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块。

innodb_buffer_pool_size

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

innodb_buffer_pool_size=512M

innodb_log_buffer_size

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

innodb_log_buffer_size=10M

实例：修改innodb_buffer_pool_size、innodb_log_buffer_size配置

1.找到配置文件所在位置

[root@centos7-1 /]# [root@centos7-1 /]# mysqld --help --verbose | grep /etc/my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

这里找到4个文件，优先级是如何呢：如果同一个配置变量。

也就是先读取/etc/my.cnf,再读取/etc/mysql/my.cnf，接着读取安装目录下面的my.cnf，最后读取/home/USERNMAE下面的my.cnf.

换句话说： ~/.my.cnf 会覆盖以前的。

2.配置文件

[root@centos7-1 /]# vim /etc/my.cnf
----加入---
innodb_log_buffer_size=10M
innodb_buffer_pool_size=512M

3.重启mysql

[root@centos7-1 /]# systemctl restart mysqld

4.验证

mysql> show variables like 'innodb_log_buffer_size' ;
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 10485760 |
+------------------------+----------+
1 row in set (0.02 sec)

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
1 row in set (0.00 sec)

得到的值是字节数，除以/1024/1024则可得到M

9.4.2 Mysql并发参数调整

从实现上来说，MySQL Server 是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。在Mysql中，控制并发连接和线程的主要参数包括 max_connections、back_log、thread_cache_size、table_open_cahce。

max_connections

采用max_connections 控制允许连接到MySQL数据库的最大数量，默认值是 151。如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。

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

back_log

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

如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back_log 的值。

table_open_cache

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

max_connections x N ；

thread_cache_size

为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数 thread_cache_size 可控制 MySQL 缓存客户服务线程的数量。(在MySQL Server端设置了线程池的大小)

innodb_lock_wait_timeout

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

9.4.3应用级优化

前面章节，我们介绍了很多数据库的优化措施。但是在实际生产环境中，由于数据库本身的性能局限，就必须要对前台的应用进行一些优化，来降低数据库的访问压力。

1.使用连接池

对于访问数据库来说，建立连接的代价是比较昂贵的，因为我们频繁的创建关闭连接，是比较耗费资源的，我们有必要建立 数据库连接池，以提高访问的性能。

2. 减少对MySQL的访问

避免对数据进行重复检索

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

比如 ，需要获取书籍的id 和name字段 ， 则查询如下：

 select id , name from tb_book;

之后，在业务逻辑中有需要获取到书籍状态信息， 则查询如下：

select id , status from tb_book;

这样，就需要向数据库提交两次请求，数据库就要做两次查询操作。其实完全可以用一条SQL语句得到想要的结果。

select id, name , status from tb_book;

增加cache层

在应用中，我们可以在应用中增加 缓存 层来达到减轻数据库负担的目的。缓存层有很多种，也有很多实现方式，只要能达到降低数据库的负担又能满足应用需求就可以。

因此可以部分数据从数据库中抽取出来放到应用端以文本方式存储， 或者使用框架(Mybatis, Hibernate)提供的一级缓存/二级缓存，或者使用redis数据库来缓存数据 。

3.负载均衡

负载均衡是应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上， 以此来降低单台服务器的负载，达到优化的效果。

利用MySQL复制分流查询

通过MySQL的主从复制，实现读写分离，使增删改操作走主节点，查询操作走从节点，从而可以降低单台服务器的读写压力。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KnpNvuwz-1664120461591)(D:\BaiduNetdiskDownload\MySQL高级\MySQL高级.assets\1.jpg)]

分库分表

sharding-jdbc 和mycat解决方案，分库分表

采用分布式数据库架构

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

第十章 MYSQL事务

1.事务概述

事务就是由单独单元的一个或多个sql语句组成，在这个单元中，每个sql语句都是相互依赖的。而整个单独单元是作为一个不可分割的整体存在，类似于物理当中的原子（一种不可分割的最小单位）。

往通俗的讲就是，事务就是一个整体，里面的内容要么都执行成功，要么都不成功。不可能存在部分执行成功而部分执行不成功的情况。

就是说如果单元中某条sql语句一旦执行失败或者产生错误，那么整个单元将会回滚(返回最初状态)。所有受到影响的数据将返回到事务开始之前的状态，但是如果单元中的所有sql语句都执行成功的话，那么该事务也就被顺利执行。

大家都知道，我们的数据都是通过各种不同技术的存储引擎来引导存储的，不同的存储引擎，都有各自的特点。在mysql中，常见的存储引擎有innodb、myisam，memory等。其中innodb支持事务(transaction)，而myisam，memory等不支持事务。

2.ACID理解

• 原子性(Atomicity)：

  原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。

• 一致性（Consistency）：事务必须使数据库从一个一致状态变换到另外一个一致状态，举一个栗子，李二给王五转账50元，其事务就是让李二账户上减去50元，王五账户上加上50元；一致性是指其他事务看到的情况是要么李二还没有给王五转账的状态，要么王五已经成功接收到李二的50元转账。而对于李二少了50元，王五还没加上50元这个中间状态是不可见的。

• 隔离性（Isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

• 持久性（Durability）：一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响。

3.事务的分类

事务分为隐式事务和显式事务两种。我们的DML语句（insert、update、delete）就是隐式事务。

隐式事务：

该事务没有明显的开启和结束标记，它们都具有自动提交事务的功能；不妨思考一下，update语句修改数据时，是不是对表中数据进行改变了，它的本质其实就相当于一个事务。

显性事务

该事务具有明显的开启和结束标记；也是本文重点要讲的东西。使用显式事务的前提是你得先把自动提交事务的功能给禁用。禁用自动提交功能就是设置autocommit变量值为0（0:禁用 1:开启）

先查看一下当前的autocommit变量值，发现当前处于开启自动提交事务的状态

mysql>select @@autocommit; #查看是否开启自动提交事务
mysql>set autocommit=0;#设置不自动提交事务

显性事务例子

#步骤一：开启事务（可选）
start transaction;
#步骤二：编写事务中的sql语句（insert、update、delete）
#这里实现一下"李二给王五转账"的事务过程
update t_account set balance = 50 where vname = "李二";
update t_account set balance = 130 where vname = "王五";
#步骤三：结束事务
commit; #提交事务
# rollback; #回滚事务:就是事务不执行，回滚到事务执行前的状态

4.事务并发时出现的问题

因为某一刻不可能总只有一个事务在运行，可能出现A在操作t_account表中的数据，B也同样在操作t_account表，那么就会出现并发问题，对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采用必要的隔离机制，就会发生以下各种并发问题。

• 脏读：对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的
• 不可重复读 ：对于两个事务T1,T2，T1读取了一个字段，然后T2更新了该字段之后，T1在读取同一个字段，值就不同了
• 幻读：对于两个事务T1,T2，T1在A表中读取了一个字段，然后T2又在A表中插入了一些新的数据时，T1再读取该表时，就会发现神不知鬼不觉的多出几行了…

所以，为了避免以上出现的各种并发问题，我们就必然要采取一些手段。mysql数据库系统提供了四种事务的隔离级别，用来隔离并发运行各个事务，使得它们相互不受影响，这就是数据库事务的隔离性。

5.事务的隔离级别

mysql中的四种事务隔离级别如下：

1. read uncommitted（读未提交数据）：

   允许事务读取未被其他事务提交的变更。（脏读、不可重复读和幻读的问题都会出现）。

2. read committed（读已提交数据）：

只允许事务读取已经被其他事务提交的变更。（可以避免脏读，但不可重复读和幻读的问题仍然可能出现）

3. repeatable read（可重复读）：

确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新(update)。（可以避免脏读和不可重复读，但幻读仍然存在）

4. serializable（串行化）：

确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可避免，但性能十分低下（因为你不完成就都不可以弄，效率太低）

了解: oracle支持两种事务隔离级别：read committed、serializable。

oracle默认的事务隔离级别是：read committed。

mysql的默认事务隔离级别是：repeatable read。

实例：

1.数据准备：

DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `m` double(10,3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of t
-- ----------------------------
INSERT INTO `t` VALUES ('1', '张三', '100.000');
INSERT INTO `t` VALUES ('2', '李四', '50.000');

2.设置事务不自动提交

打开两个客户端会话session1,session2,都设置手动事务

set autocommit=0;

3.read uncommitted隔离（读未提交）

###session1
set session transaction isolation level read uncommitted; #设置事务隔离为 read uncommitted
select * from t;
###session2
set session transaction isolation level read uncommitted;
select * from t;
###session1
update t set m=50 where id=1;
###session2
mysql> select * from t;
+----+--------+--------+
| id | name   | m      |
+----+--------+--------+
|  1 | 张三   | 50.000 |
|  2 | 李四   | 50.000 |
+----+--------+--------+
2 rows in set (0.00 sec)
#可以看出，session1修改了数据没有提交，session2已经能查询出变更的数据了

4.read committed（读已提交）

###session1
set session transaction isolation level read committed; #设置事务隔离为 read committed
set autocommit = 0;
select * from t;
###session2
set session transaction isolation level read committed;
set autocommit = 0;
select * from t;
###session1
update t set m=50 where id=1;
###session2
mysql> select * from t;
+----+--------+--------+
| id | name   | m      |
+----+--------+--------+
|  1 | 张三   | 100.000 |
|  2 | 李四   | 50.000 |
+----+--------+--------+
2 rows in set (0.00 sec)

可以看出session1修改了但没有提交事务，session2读取依然是以前数据，而不是sesson1提交的数据
###session1
commit; #提交
###session2
mysql> select * from t;
+----+--------+--------+
| id | name   | m      |
+----+--------+--------+
|  1 | 张三   | 50.000 |
|  2 | 李四   | 50.000 |
+----+--------+--------+
2 rows in set (0.00 sec)
session1提交后在session2查询的数据就变化了

5.repeatable read（可重复读）

该隔离级别为mysql的默认隔离级别；它对某字段进行操作时，其他事务禁止操作该字段。它总能保持你读取的数据是一致的。

###session1
set session transaction isolation level read committed; #设置事务隔离为 read committed
set autocommit = 0;
select * from t;
###session2
set session transaction isolation level read committed;
set autocommit = 0;
select * from t;
###session1
update t set m=30 where id=1;
###session2
update t set m=0 where id=1; #发现session被堵塞
###session1
commit; #提交
###session2
update t set m=0 where id=1; #自动解除阻塞
###session2
commit; #提交

###session1
mysql> select * from t;
+----+--------+--------+
| id | name   | m      |
+----+--------+--------+
|  1 | 张三   |  0.000 |
|  2 | 李四   | 50.000 |
+----+--------+--------+

第十一章 主从复制

1.复制概述

复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

2. 复制原理

MySQL 的主从复制原理如下。

从上层来看，复制分成三步：

• Master 主库在事务提交时，会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中。
• 主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay Log 。
• slave重做中继日志中的事件，将改变反映它自己的数据。

MySQL 复制的优点主要包含以下三个方面：

• 主库出现问题，可以快速切换到从库提供服务。
• 可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力。
• 可以在从库中执行备份，以避免备份期间影响主库的服务。

3.搭建步骤

3.1服务器准备

用vmware虚拟两台服务器

#服务器1
ip: 192.168.80.102
hostname:7-2
mysql: root 密码:segns110
#服务器2
ip: 192.168.80.103
hostname:7-3
mysql: root 密码:segns110

3.2master mysql配置

1.关闭防火墙

systemctl stop firewalld

2.在master服务器my.cnf文件增加配置：

#mysql 服务ID,保证整个集群环境中唯一
server-id=1

#mysql binlog 日志的存储路径和文件名
log-bin=/var/lib/mysql/mysqlbin

#错误日志,默认已经开启
#log-err

#mysql的安装目录
#basedir

#mysql的临时目录
#tmpdir

#mysql的数据存放目录
#datadir

#是否只读,1 代表只读, 0 代表读写
read-only=0

#忽略的数据, 指不需要同步的数据库
binlog-ignore-db=mysql

#指定同步的数据库
#binlog-do-db=db01

3.重启mysql

systemctl restart mysqld

4.登录mysql，创建用户、授权、刷新

mysql> CREATE USER 'jzk'@'%' IDENTIFIED BY '123456'; #注意是master数据库的用户哦
mysql> GRANT REPLICATION SLAVE ON *.* TO 'jzk'@'%';
mysql> FLUSH PRIVILEGES;

5.查看master状态：

show master status;

字段含义：

File : 从哪个日志文件开始推送日志文件 
Position ： 从哪个位置开始推送日志
Binlog_Ignore_DB : 指定不需要同步的数据库

3.3 slave mysql配置

1.关闭防火墙

systemctl stop firewalld

2.修改my.cnf配置文件

#mysql服务端ID,唯一
server-id=2

#指定binlog日志
log-bin=/var/lib/mysql/mysqlbin

3.重启数据库

systemctl restart mysqld

4.登录mysql执行

mysql>change master to master_host= '192.168.80.102', master_user='jzk', master_password='123456', master_log_file='mysqlbin.000001', master_log_pos=413;

-- 指定主节点的ip，账户，密码，二进制日志文件名称，以及从哪一行开始同步
-- master_log_file  master_log_pos两个配置查看master的show master status;

5.开启同步操作

mysql>start slave;

mysql>show slave status \G;

可停止服务：

mysql>stop slave;

3.4验证数据

在master创建数据库、新增、修改、删除、看数据是否同步

3.5异常处理

在show slave status \G; 核心看下面两个指标

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Slave_IO_Running: No

可以看下面的错误描述，可能用了clone服务器，

提示主库和从库的UUID是同一个导致的问题，这时可以修改主库或者从库中的auto.cnf配置文件中的uuid值

1.退出mysql登录
2.执行以下三条命令：
  systemctl stop mysqld.service   //关闭MySQL服务

  mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak  //删除auto.cnf
  
  systemctl start mysqld.service   //重启mysql服务

再查看

mysql>start slave;

mysql>show slave status \G;

Slave_SQL_Running:No

1)首先停掉Slave服务：slave stop

2)到主服务器上查看主机状态： 记录File和Position对应的值

3)手动同步

mysql>change master to master_host= '192.168.80.102', master_user='jzk', master_password='123456', master_log_file='mysqlbin.000002', master_log_pos=865;

4)启动，再查看

mysql>start slave;

mysql>show slave status \G;

第十二章 阿里MYSQL规范

1.建表规范

1. 【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned
   tinyint（1 表示是，0 表示否）。
   说明：任何字段如果为非负数，必须是 unsigned。
   注意：POJO 类中的任何布尔类型的变量，都不要加 is 前缀，所以，需要在设置从 is_xxx
   到 Xxx 的映射关系。数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的命名方式是为了明确其取
   值含义与取值范围。
   正例：表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。
2. 【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间
   只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重
   考虑。
   说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表
   名、字段名，都不允许出现任何大写字母，避免节外生枝。
   正例：aliyun_admin，rdc_config，level3_name
   反例：AliyunAdmin，rdcConfig，level_3_name
3. 【强制】表名不使用复数名词。
   说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合
   表达习惯。
4. 【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。
5. 【强制】主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。
   说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称。
6. 【强制】小数类型为 decimal，禁止使用 float 和 double。
   说明：在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的
   结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。
7. 【强制】如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
8. 【强制】varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长
   度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索
   引效率。
9. 【强制】表必备三字段：id, create_time, update_time。
   说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。create_time, update_time
   的类型均为 datetime 类型。Java 开发手册
   32/44
   10.【推荐】表的命名最好是遵循“业务名称_表的作用”。
   正例：alipay_task / force_project / trade_config
   11.【推荐】库名与应用名称尽量一致。
   12.【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
   13.【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
   1） 不是频繁修改的字段。
   2） 不是 varchar 超长字段，更不能是 text 字段。
   3） 不是唯一索引的字段。
   正例：商品类目名称使用频率高，字段长度短，名称基本一不变，可在相关联的表中冗余存储类目名
   称，避免关联查询。
   14.【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
   说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
   15.【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检
   索速度。
   正例：如下表，其中无符号值可以避免误存负数，且扩大了表示范围

2.索引规范

1. 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
   说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，
   即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
2. 【强制】超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致；多表关联查询
   时，保证被关联的字段需要有索引。
   说明：即使双表 join 也要注意表索引、SQL 性能。
3. 【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据
   实际文本区分度决定索引长度即可。Java 开发手册
   33/44
   说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达
   90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
4. 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
   说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
5. 【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合
   索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
   正例：where a=? and b=? order by c; 索引：a_b_c
   反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无
   法排序。
6. 【推荐】利用覆盖索引来进行查询操作，避免回表。
   说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这
   个目录就是起到覆盖索引的作用。
   正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效
   果，用 explain 的结果，extra 列会出现：using index。
7. 【推荐】利用延迟关联或者子查询优化超多分页场景。
   说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当
   offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL
   改写。
   正例：先快速定位需要获取的 id 段，然后再关联：
   SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
8. 【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是
   consts 最好。
   说明：
   1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
   2） ref 指的是使用普通的索引（normal index）。
   3） range 对索引进行范围检索。
   反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range
   还低，与全表扫描是小巫见大巫。
9. 【推荐】建组合索引的时候，区分度最高的在最左边。
   正例：如果 where a=? and b=? ，如果 a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。
   说明：存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么
   即使 c 的区分度更高，也必须把 d 放在索引的最前列，即索引 idx_d_c。
   10.【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。Java 开发手册
   34/44
   11.【参考】创建索引时避免有如下极端误解：
   1） 宁滥勿缺。认为一个查询就需要建一个索引。
   2） 宁缺勿滥。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
   3） 抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

3.SQL规范

1. 【强制】不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的
   标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
   说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
2. 【强制】count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct
   col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
3. 【强制】当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果
   为 NULL，因此使用 sum()时需注意 NPE 问题。
   正例：使用如下方式来避免 sum 的 NPE 问题：SELECT IFNULL(SUM(column), 0) FROM table;
4. 【强制】使用 ISNULL()来判断是否为 NULL 值。
   说明：NULL 与任何值的直接比较都为 NULL。
   1） NULL<>NULL 的返回结果是 NULL，而不是 false。
   2） NULL=NULL 的返回结果是 NULL，而不是 true。
   3） NULL<>1 的返回结果是 NULL，而不是 true。
5. 【强制】代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。
6. 【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
   说明：以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外
   键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级
   联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风
   险；外键影响数据库的插入速度。
7. 【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
8. 【强制】数据订正（特别是删除、修改记录操作）时，要先 select，避免出现误删除，确认无
   误才能执行更新语句。
9. 【推荐】in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控
   制在 1000 个之内。
   10.【参考】如果有国际化需要，所有的字符存储与表示，均以 utf-8 编码，注意字符统计函数
   的区别。Java 开发手册
   35/44
   说明：
   SELECT LENGTH(“轻松工作”)； 返回为 12
   SELECT CHARACTER_LENGTH(“轻松工作”)； 返回为 4
   如果需要存储表情，那么选择 utf8mb4 来进行存储，注意它与 utf-8 编码的区别。
   11.【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但
   TRUNCATE 无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。
   说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

4.ORM 映射规范

1. 【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
   说明：1）增加查询分析器解析成本。2）增减字段容易与 resultMap 配置不一致。3）无用字段增加网络
   消耗，尤其是 text 类型的字段。
2. 【强制】POJO 类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中进行
   字段与属性之间的映射。
   说明：参见定义 POJO 类以及数据库字段定义规定，在中增加映射，是必须的。在
   MyBatis Generator 生成的代码中，需要进行对应的修改。
3. 【强制】不要用 resultClass 当返回参数，即使所有类属性名与数据库字段一一对应，也需要
   定义；反过来，每一个表也必然有一个 POJO 类与之对应。
   说明：配置映射关系，使字段与 DO 类解耦，方便维护。
4. 【强制】sql.xml 配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现 SQL 注入。
5. 【强制】iBATIS 自带的 queryForList(String statementName,int start,int size)不推荐使用。
   说明：其实现方式是在数据库取到 statementName 对应的 SQL 语句的所有记录，再通过 subList 取
   start,size 的子集合。
   正例：Map<String, Object> map = new HashMap<>();
   map.put(“start”, start);
   map.put(“size”, size);
6. 【强制】不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出。
   说明：resultClass=”Hashtable”，会置入字段名和属性值，但是值的类型不可控。
7. 【强制】更新数据表记录时，必须同时更新记录对应的 gmt_modified 字段值为当前时间。
8. 【推荐】不要写一个大而全的数据更新接口。传入为 POJO 类，不管是不是自己的目标更新
   字段，都进行 update table set c1=value1,c2=value2,c3=value3; 这是不对的。执行 SQL
   时，不要更新无改动的字段，一是易出错；二是效率低；三是增加 binlog 存储。Java 开发手册
   36/44
9. 【参考】@Transactional 事务不要滥用。事务会影响数据库的 QPS，另外使用事务的地方
   需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。
   10.【参考】中的 compareValue 是与属性值对比的常量，一般是数字，表示相等时
   带上此条件；表示不为空且不为 null 时执行；表示不为 null 值
   时执行。

附件

linux的mysql

服务启动命令

system start mysqld;
system  restart mysqld;
system  stop mysqld;

mysql安装路径

whereis mysql
which mysqld  #mysqld的启动路径

日志的路径

mysql> show variables like 'log_%';

mysql配置文件

mysql的配置文件路径

mysqld --verbose --help |grep -A 1 'Default options'