MySQL进阶

一、索引

1.1 索引概述

MySQL官方对索引的定义为：索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

一般来说，索引本身也很大，不可能全部存储在内存中，索引往往以索引文件的形式存储在磁盘中。

1.2 索引优缺点

优点：

• 提高数据检索效率，降低数据库IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

缺点：

• 索引也是一张表，需要占据空间。
• 索引提高了查询效率的同时，对于INSERT、UPDATE、DELETE操作，提高了处理时间。

1.3 索引结构

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

• BTREE索引：最常见、使用最多的索引，大部分存储引擎都支持B树索引。

• HASH索引：只有Memory引擎支持。

• R-tree索引：空间索引，是MyISAM引擎的特殊索引类型，一般用于地理空间数据类型。

• Full-Text索引：全文索引，是MyISAM引擎的特殊索引类型，一般用于全文查找，InnoDB引擎在5.6版本开始支持Full-Text全文索引。

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

平时所说的索引没有特别指明的话，实际上都是指B+树索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是B+树索引结构。

1.3.1 B-tree结构

BTREE树又叫做多路平衡搜索树。BTREE树和二叉树相比，查询效率更高。

更多关于B-tree结构，查看：https://blog.csdn.net/zyj0070/article/details/98892111

1.3.2 B+Tree结构

B+Tree是B-Tree的变种，他们的区别如下：

• 1、n叉B+Tree最多有n个Key，B-Tree最多有n-1个Key
• 2、B+Tree的叶子节点保存所有Key信息，按大小顺序排列。
• 3、所有非叶子节点都可以看作Key的索引部分。

1.3.3 MySQL中的B+Tree

MySQL索引数据结构对B+Tree进行了优化，在原有基础上增加了指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

1.4 索引分类

• 1、单值索引：一个索引只包含单列。
• 2、复合索引：一个索引包含多列。
• 3、唯一索引：索引列的值必须唯一，可以为空。

1.5 索引语法

1.5.1 创建索引

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

index_name:索引名称
index_type:索引类型
tab_name:表名
index_col_name:索引列名

1.5.2 查看索引

show index from tab_name;

tab_name:表名

1.5.3 删除索引

drop index index_name ON tab_name;

index_name:索引名称
tab_name:表名

1.6 索引设计原则

• 1、查询频次较高，且数据量比较大的表建立索引。
• 2、使用唯一索引，区分度高，效率越高。
• 3、where子句中提取索引字段。
• 4、索引能够提高检索数据效率，但并不是索引越多越好。在插入、更新、删除时，索引需要同时维护，过多的索引导致维护的代价较高。
• 5、尽量使用短索引，能够提高访问索引时的IO效率。
• 6、最左前缀索引。

--最左前缀
create index idx_table_a ON test(a,b,c);

该索引相当于创建了：
a索引、ab索引、abc索引

二、视图

2.1 视图概念

​ 视图是一种虚拟存在的表，视图并不真实存在数据库中。视图就是一条Select查询后的结果集。

​ 视图相较于普通表的优势如下：

• 简单：无需关心表结构，对于用户来说已经是处理完成的结果集。
• 安全：使用视图的用户只能访问被允许访问的结果集。
• 数据独立：一旦视图的结构确定，可以屏蔽表结构变化对用户的影响。源表修改列名，通过修改视图解决，不影响访问者。

2.2 创建、修改视图

2.2.1 创建视图

--创建视图语法
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name[(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

2.2.2 修改视图

--修改视图语法
ALTER [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name[(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

2.2.3 注意事项

--语句选项
[WITH [CASCADED | LOCAL] CHECK OPTION]:决定是否允许更新数据使记录不再满足视图的条件。
	
	LOCAL:只要满足本视图的条件就更新。
	CASCADED:必须满足所有针对该视图的所有视图的条件才可以更新。(默认值)
	
	注：通过视图去插入数据，这点本身就不够严谨。
	
	

--案例
CREATE 
VIEW view_xm_addr 
AS SELECT a.`name`,b.addr FROM	a	LEFT JOIN b ON a.id = b.a_id WHERE	a.`name` = '厦门';
--查询视图
SELECT	* FROM	view_xm_addr;

2.3 查看视图

--表和视图一起
show TABLES;

--查看视图创建语句
show create view view_name;

2.4 删除视图

DROP VIEW [IF EXISTS] view_name [,view_name] ...[RESTRICT | CASCADE]

三、存储过程和函数

3.1 存储过程和函数概述

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

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

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

3.2 创建存储过程

CREATE PROCEDURE procedure_name([proc_paramter[,...]])
BEGIN
	--sql...
END;

3.3 调用存储过程

CALL procedure_name();

3.3 查看存储过程

--查询所有的存储过程 db_name为要查询的数据库名
select name from mysq1.proc where db = 'db_name';

--查询存储过程的状态信息
show procedure status;

--查询存储过程的定义 db:数据库名，procedureName:存储过程名
show create procedure db.procedureName;

3.4 删除存储过程

DROP PROCEDURE [IF EXISTS]procedureName;

3.5 语法结构

3.5.1 变量

• DECLARE

  定义一个局部变量，作用范围仅在：BEGIN…END。

  DECLARE var_name[,...] type [DEFAULT value]
  
  var_name:变量名，多个变量用,分割
  type:变量类型
  value:默认值
  

• SET

  为变量赋值。

  SET var_name = expr [, var_name = expr] ...
  

3.5.2 IF判断

IF search_condition THEN statement_list
	[ELSEIF search_condition THEN statement_list] ...
	[ELSE statement_list]
END IF;

3.5.3 传递参数

CREATE PROCEDURE procedure_name([in/out/inout] 参数名 参数类型)
BEGIN
	--sql...
END;

IN:参数为入参。
OUT:参数为出参。
INOUT:参数既是入参、也是出参。

3.5.4 CASE结构

--方法一
CASE case_value
	WHEN when_value THEN statement_list
		[WHEN when_value THEN statement_list]
		[ELSE statement_list]
END CASE;

--方法二
CASE
	WHEN search_condition THEN statement_list
		[WHEN search_condition THEN statement_list]
		[ELSE statement_list]
END CASE;

3.5.5 WHILE循环

满足条件，才执行。

WHILE search_condition do
	statement_list
END WHILE;

3.5.6 REPEAT循环

满足条件，退出循环。

REPEAT
	statement_list
	UNTIL search_condition
END REPEAT;

3.5.7 LOOP循环

LOOP实现简单循环，退出循环的条件需要使用其他的语句定义，通常使用LEAVE语句实现。

[begin_label:]LOOP
	statement_list
END LOOP [end_label]

如果不在statement_list语句增加退出循环的语句，则LOOP为死循环！！！

3.5.7.1 LEAVE语句

CREATE procedure pa (n int)
BEGIN
	DECLARE total int default 0;
	
	countNum:LOOP
		SET total = total + n ;
		SET n = n -1;
		IF n <= 0 THEN LEAVE countNum;
		END IF;
	END LOOP countNum;

	SELECT total;
END;

3.5.8 游标

​ 游标是用来存储查询结果集的数据类型，可以使用游标对结果集进行处理。

--声明游标
DECLARE cursor_name CURSOR FOR select_statement;

--打开游标
OPEN cursor_name;

--取游标
FETCH cursor_name INFO var_name[,var_name] ...

--关闭游标
CLOSE cursor_name;

3.6 存储函数

CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
	...
END;

四、触发器

​ 触发器是与表有关的数据库对象，是指在insert\update\delete之前或之后，触发并执行相应的SQL语句的集合。

​ 触发器的作用：协助确保数据的完整性、日志记录、数据校验等操作。

​ 触发器有OLD和NEW变量，具体如下：

触发器类型	变量说明
INSERT	NEW标识将要新增的数据
UPDATE	OLD标识旧数据，NEW标识新数据。
DELETE	OLD标识将要删除的、已删除的数据。

​

4.1 创建触发器

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tab_name
[FOR EACH ROW] --行级触发器
trigger_stmt;

trigger_name:触发器名
tab_name:表名
trigger_stmt:触发器具体内容

4.2 删除触发器

DROP TRIGGER [schema_name.]trigger_name

4.3 查看触发器

SHOW TRIGGERS;

五、MySQL体系结构

• 连接层

  负责完成连接处理、授权认证等。

• 服务层

  核心服务功能：SQL接口、SQL的分析和优化、缓存等功能。

• 引擎层

  引擎层负责数据的存储和提取。

• 存储层

  数据存储层，数据存储文件系统上，与引擎层交互。

MySQL与其他数据库相比，特点在于插件式的存储引擎！！！

5.1 存储引擎

​ 存储引擎是具体的技术实现方式（存储数据、建立索引等等技术），是基于表的。Oracle、SQL Server只有一种存储引擎，而MySQL的存储引擎为插件式的，拥有多种存储引擎，可根据需要选择存储引擎。

5.2 常用存储引擎

	InnoDB	MyISAM	Memory
存储限制	Y	Y	Y
事务安全	Y
锁机制	行锁	表锁	表锁
B树索引	Y	Y	Y
Hash索引			Y
Full-Text索引	Y(5.6版本)	Y
聚集索引	Y
数据索引	Y		Y
索引缓存	Y	Y	Y
数据可压缩		Y
空间使用	高	低
内存使用	高	低	中
批量插入速度	低	高	高
支持外健	支持

5.2.1 InnoDB

​ 该引擎存储表和索引有以下两种方式：

• 1、共享表空间存储，表结构保存在.frm文件，数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中。
• 2、多表空间存储，表结构保存中.frm文件，数据和索引存储在.ibd中。

5.2.2 MyISAM

​ 该引擎不支持事务、不支持外健，优势是访问速度快。对事务完整性没有要求、或者以select、insert为主的应用可以使用该引擎创建表。

​ 该引擎存储文件如下：

• .frm：表结构。
• .MYD：表数据。
• .MYI：表索引。

5.2.3 Memory

​ 该引擎将表数据存储在内存，每个Memory表对应一个.frm磁盘文件，只存储表结构。

​ 特点：

• 数据在内存中，访问速度快。
• 一旦断电，数据丢失。

5.3 存储引擎的应用场景

• InnoDB：MySQL默认存储引擎，对于事务完整性有要求，对并发下数据一致性有要求，选择InnoDB引擎可以轻松应对。
• MyISAM：业务上以读、插入操作为主，对事务完整性、并发性要求不高，选择MyISAM引擎。
• Memory：该表通常用于更新不频繁的小表，用以快速得到结果。

5.4 SQL优化

​ 在项目开发初期，SQL编写更多是注重功能的实现；随着时间的推移，数据量逐渐累积，原有的SQL的性能缺陷就表现出来，此时就需要对原有SQL进行优化。

5.4.1 查看SQL执行频率

--查询全局相关操作次数
SHOW GLOBAL STATUS LIKE 'Com_______';

--查询全局InnoDB操作行
SHOW GLOBAL STATUS LIKE 'Innodb_rows_%';

5.4.2 定位低效SQL语句

• 慢查询日志：通过--log-show-queries[=file_name]启动MySQL，mysql会记录执行时间超过long_query_time的SQL语句的日志文件。
• 查看当前进行的线程：show processlist ，包括线程的状态，是否锁表等。

--show processlist 列含义
Id	User	Host									db		Command	Time	State			Info
740	youth	183.253.27.230:60746	youth	Query		0			starting	show PROCESSLIST
741	youth	183.253.27.230:60268	youth	Sleep		22		
742	youth	183.253.27.230:60452	youth	Sleep		22		

Id:用户登录MySQL时，系统分配的id,可以使用`show connection_id()`查看。
User:当前用户。
Host:语句从哪个ip:port发的。
db:连接的数据库。
Command:执行的命令，`Sleep`休眠；`Query`查询;`Connect`连接。
Time:当前状态持续时间。
State:当前SQL的状态。查询要经过`copying to tmp table`、`sorting result`、`sending data`等。
Info:当前SQL语句。

5.4.3 分析执行计划

MySQL提供EXPLAIN用于查看SQL执行计划。

Oracle提供：

EXPLAIN PLAN FOR sql_statement;

select * from table(dbms_xplan.display());

--EXPLAIN 列含义

ID	SELECT_TYPE TABLE	PRATITIONS	TYPE	POSSIBLE_KEYS	KEY					KEY_LEN	REF ROWS 	FILTERED 	EXTRA
1		SIMPLE			a									index								idx_a_name	767					2			100.00		Using index
1		SIMPLE			b									ALL																					2			100.00		Using where; Using join buffer (Block Nested Loop)

1、ID:查询的序列号，查询中执行SELECT子句或者是操作表的顺序。
2、SELECT_TYPE:表示SELECT的类型，`SIMPLE(简单表，不使用表连接或者子查询)`、`PRIMARY(主查询，外层的查询)`、`UNION(UNION中第二个或后面的查询语句)`、`SUBQUERY(子查询中的第一个Select)`。
3、TABLE:输出结果集的表。
4、PARTITIONS:使用哪个分区。
5、TYPE:表示表连接的类型，性能由好->差的连接类型为：`system -> const -> eq_ref -> ref -> ref_or_null -> index_merge -> index_subquery -> range -> index -> all`。
6、POSSIBLE_KEYS:查询时，可能使用的索引。
7、KEY:实际使用的索引。
8、KEY_LEN:索引字段的长度。
9、REF:显示索引的哪一列被使用了，如果可能的话，是一个常数
10、ROWS:扫描行的数量。
11、FILTERED:条件过滤后，对比总数的百分比。
12、EXTRA:执行情况的说明和描述。

5.4.3.1 ID列

• 1、ID相同时，执行顺序从上到下。
• 2、ID不同时，值越大优先级越高，越先执行。

5.4.3.2 SELECT_TYPE列

查询的类型。

• SIMPLE：简单查询，不包含子查询或UNION。
• PRIMARY：查询中包含复杂的子查询，最外层查询为该标识。
• SUBQUERY：在SELECT或WHERE列表中包含子查询。
• DERIVED：在FROM列包含的子查询，被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表。
• UNION：若第二个SELECT出现UNION后，标记为UNION；若UNION包含在FROM子查询中，外层将被标记为DERIVED。
• UNION RESULT：从UNION表获取结果的SELECT。

5.4.3.3 TYPE列

访问类型，重要的指标之一。

• NULL：不访问任何表、索引，直接返回结果。
• system：表只有一行记录，是const类型的特例。
• const：通过索引一次就查询到结果，只返回一条记录。
• eq_ref：使用唯一索引，查询的记录只有一条。常见于主键或唯一索引扫描。
• ref：非唯一性索引扫描，返回匹配值的所有行。（走索引）
• range：范围查询，只检索给定返回的行，使用一个索引来选择行。
• index：遍历索引，比ALL快。
• ALL：遍历数据，效率最低。

5.4.3.4 KEY相关列

• possible_keys：可能用到的索引。
• key：实际用到的索引。
• key_len：Key长度。（越短越好，效率越高）

5.4.3.5 ROWS列

扫描的行数。

5.4.3.6 Extra列

• Using filesort：使用一个外部的索引排序，不是按照表内索引的排序。
• Using temporary：临时表保存中间结果。
• Using index：使用了覆盖索引，效率不错。
• Using where：在查找使用索引的情况下，需要回表去查询所需的数据。在存储引擎检索后再进行过滤。
• Using index condition：查找使用了索引，但是需要回表查询数据。
• Using index;Using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

5.4.4 SHOW PROFILES分析SQL

开启profiling:

--查看是否支持
select @@have_profiling;

--查看是否开启 1开启 0关闭
select @@profiling;

--开启
set profiling = 1;

--查看所有
SHOW profiles;

--查看具体SQL
SHOW profile for query query_id;

--查询出来的数据，Sending data时间最长，表示MySQL线程开始访问数据行并把结果返回给客户端。

5.4.5 trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是选择B计划。

打开trace，设置格式为JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer _trace_max_mem_size=1000000;

执行SQL后，最后,检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的:

 select * from information_schema.optimizer _trace\G;

5.5 索引的使用

5.5.1 避免索引失效

5.5.1.1 全值匹配

对索引中所有列都指定具体值。

5.5.1.2 最左前缀原则

查询从索引的最左列开始，且不跳过索引中的列。

跳过索引中的列，走的索引是：第一值的索引。

如：索引（a,b,c） ，查询条件为：where a = xx and b =xx;

此时走索引，索引为a = xx 的索引 （走索引，但没完全走 😄）

5.5.1.3 避免范围查询

范围查询右边的列不走索引。

5.5.1.4 避免索引列上运算操作

索引列上运算操作，索引将失效。

5.5.1.5 字符串加引号

字符串不加引号，索引将失效。

5.5.1.6 尽量使用覆盖索引

尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列))，减少select *。

5.5.1.7 OR分割的条件

用or分割开的条件，索引都不会被用到。

5.5.1.8 %模糊查询

以%开头的Like模糊查询，索引失效。

开发中常见的 %xxx% ,可以通过覆盖索引的方法使SQL走索引。

5.5.1.9 MySQL认为走索引比全表慢

与数据库中的数据有关系，某条数据占比大，则不走索引。

5.5.1.10 IS NULL ，IS NOT NULL

有时走索引，有时不走。

与5.5.1.9类似，与数据库中的数据有关系，占比大时，数据库不走索引。

5.5.1.11 IN , NOT IN

IN走索引，NOT IN 不走索引（在比重小且为索引列，NOT IN也走索引。）。

5.5.1.12 单列索引和复合索引

尽量使用复合索引，少使用单列索引。

5.5.2 查看索引的使用情况

--查看当前会话索引使用情况
show status like "Handler_read%";
--查看全局索引使用情况
show global status like "Handler_read%'

该内容仅有参考作用，不做过多介绍。

5.6 SQL优化

TYPE从好到坏结果如下：

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

5.6.1 INSERT语句优化

• 插入数据，尽量使用一条语句，多个值的插入。

• 多条插入，事务手动提交。

• 数据有序插入。

5.6.2 ORDER BY 语句优化

• 多字段排序时，一个升序，一个降序，会使用Using filesort
• 多字段排序时，排序顺序和索引顺序一致，否则还是使用Using filesort

Using filesort优化：

​ 对于filesort排序，MySQL有2种排序算法：

• 两次扫描算法：MySQL4.1之前，使用该排序。
  • 该排序首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序，如果sort buffer不够，则在临时表temporary table中存储排序结果。完成排序后，再根据行指针信息回表读取数据。
• 一次扫描算法：一次性取出满足条件的所有字段，然后在排序区中排序后直接输出结果集。

​ MySQL通过对比max_length_for_sort_data和查询后取出的字段总大小进行比较；取出字段总大小相对较小，则取一次扫描算法；否则取二次扫描算法。

可以适当提高：sort_buffer_size和max_length_for_sort_data大小，提高排序效率。

5.6.3 GROUP BY 语句优化

GROUP BY底层还是会进行ORDER BY 排序，如果不想排序，加上：ORDER BY null

5.6.4 嵌套查询优化

某些情况下，子查询可以被JOIN（连接）替换，更加高效。

5.6.5 OR查询优化

OR之间的每个条件列都必须用到索引。且不能使用复合索引！！！

建议使用UNION替换OR。

5.6.6 分页查询优化

​ 常见分页查询就是使用limit xxxxx，这种分页查询在数据量大的情况下，MySQL需要排序前xxxxx条记录，其他记录丢弃，查询的代价是巨大的。

• 优化思路一：

​ 在索引上完成排序分页操作，然后根据主键回表查询其他内容。

• 优化思路二：

​ （适用于主键自增的表）对limit查询转换成某个位置的查询。（对数据有严格的要求，主键不允许断层！）

5.6.7 使用SQL提示

​ SQL提示就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

• USE INDEX

  在查询语句中的表名的后面，添加use index来提供希望MySQL参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

  select * from a use index (index_a_name) where name = 'xxx';
  

• IGNORE INDEX

  忽略指定索引。

  select * from a ignore index (index_a_name) where name = 'xxx';
  

• FORCE INDEX

  强制使用某个索引。

  select * from a force index (index_a_name) where name = 'xxx';
  

六、 MySQL应用优化

6.1 应用优化

• 一、使用数据库连接池

  C3P0、Druid等

• 二、减少对MySQL的访问

  • 1. 避免对数据库重复检索
    2. 增加cache层

• 三、负载均衡

  • 使用MySQL复制分流查询

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

  • 采用分布式数据库架构

    通过在多台服务器之间分布数据，可以实现在多台服务器间的负载均衡，提高访问效率。

6.2 MySQL中查询缓存优化

开启MySQL的查询缓存，当执行完全相同的SQL语句的时候，直接从缓存中读取结果，当数据被修改，之前的缓存会失效。

MySQL5.7.20 版本已过时，在 MySQL8.0 版本中被移除

6.3 MySQL内存管理及优化

6.3.1 内存优化原则

• 尽量多的内存分配给MySQL
• MyISAM存储引擎的数据文件依赖磁盘IO，所以如果有MyISAM存储引擎的表，就需要更多内存给操作系统做IO。
• 排序区、连续区等缓存是分配给每个数据库会话（Session）专用的，需要根据最大连接数进行合理分配，避免高并发情况下，内存耗尽。

6.3.2 MyISAM内存优化

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

• key_buffer_size

  决定MyISAM索引块缓存的大小，直接影响存取效率。一般为1/4的可用内存分配给索引块。

  /usr/my.cnf配置：
  
  key_buffer_size=512M
  

• read_buffer_size

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

• read_rnd_buffer_size

  对于需要做排序的MyISAM表的查询，如带有order by子句的sqI，适当增加 read_ rnd_buffer_size的值，可以改善此类的sql性能。但需要注意 的是read_rnd_buffer_size是每个session独占的，如果默认值设置太大，就会造成内存浪费。

6.3.3 InnoDB内存优化

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

• innodb_buffer_pool_size

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

  innodb_buffer_pool_size=512M
  

• innodb_log_buffer_size

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

6.4 MySQL并发参数调整

6.4.1 max_connections

最大连接数，控制运行连接MySQL的最大数量，默认151。根据服务器性能进行调整。

如果状态变量connection_errors_max_connections（达到最大连接数，报错信息）不为零，且一直增长，则说明当前连接达到最大数。

6.4.2 back_log

back_log控制MySQL监听TCP端口时设置的积压请求栈大小，（达到最大连接数后，新来的请求被积压在请求栈）。

如果等待连接的数量超过back_log，将不再授予连接资源（客户端连接报错）。

5.6.6版本之前默认值为50，之后版本默认为（max_connections / 5），最大不超过900。

6.4.3 table_open_cache

控制所有SQL语句执行线程可打开表缓存的数量，而在执行SQL语句时，每一个SQL执行线程至少打开一个表缓存。

该参数通过max_connections最大连接数以及每个连接执行关联查询中涉及的表的最大数量来设定：

max_connections * N

6.4.4 thread_cache_size

为了加快连接MySQL的速度，服务端会缓存一定数量的客户服务线程备用，通过该参数可控制客户服务线程池数量。

6.4.5 innodb_lock_wait_timeout

该参数是用来设置InnoDB事务等待行锁的时间，默认值是50ms，可以根据需要进行动态设置。

对于需要快速反馈的业务系统来说，可以 将行锁的等待时间调小，以避免事务长时间挂起；

对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚 操作。

6.5 MySQL锁问题

6.5.1 锁的概念

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

在数据库中，除传统的计算资源(如 CPU、 RAM、I/O等)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一 致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

从这个角度来说，锁对数据库而言 显得尤其重要，也更加复杂。

6.5.2 锁分类

按数据操作的粒度分：

• 表锁：整个表锁定。
• 行锁：锁定当前操作行。

按数据操作的类型分：

• 读锁（共享锁）：针对同一数据，多次读操作可以同时进行互不影响。
• 写锁（排他锁）：针对同一数据，阻断其他读锁和写锁。

6.5.3 MySQL锁

锁支持：

	表锁	行锁
InnoDB	支持	支持
MyISAM	支持	不支持
Memory	支持	不支持

• 表锁：偏向MyISAM存储引擎，开销小，加锁快；不会出现死锁；锁粒度大， 发生冲突概率高。
• 行锁：偏向InnoDb存储引擎，开销大，加锁慢；可能会出现死锁；锁粒度小，发生冲突概率低。

6.5.3.1 MyISAM表锁

MyISAM只支持表锁。

MyISAM存储引擎在select前，自动给表加读锁；在update/delete/insert前，自动给表加写锁。一般不用显式加锁。

显式加锁如下：

#加读锁
lock table table_name read;

#加写锁
lock table table_name write;

#解锁
unlock tables;

结论就是：读锁会阻塞写，但不会阻塞读；写锁会阻塞读，也会阻塞写。

6.5.3.2 InnoDB行锁

1. 事务相关概念

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

事务具有ACID属性。

• A：Atomicity 原子性；对数据修改要么全部成功，要么全部失败。
• C：Consistent 一致性；事务开始前，后数据保持一致。
• I：Isolation 隔离性；不同事务，互不干扰。
• D：Durable 持久性；事务完成后，对数据的修改是永久的。

并发操作带来的问题：丢失更新、脏读、幻读、不可重复读

• 丢失更新：多个事务同时提交时，最后提交的事务会覆盖之前的事务。
• 脏读：一个事务读取数据，修改，未提交；另一个事务读取到未提交的数据。
• 幻读：一个事务1查询读取数据，此时数据被另一个事务2新增、删除；事务1再次查询读取数据发现不一致。
• 不可重复读：一个事务读取数据，再次读取数据，发现不一致。

事务隔离级别：

为了解决并发操作问题，提供了4个隔离级别：RU(Read Uncommited)、RC(Read Commit)、 RR( Repeatable Read)、S(Serializable)

• RU：读未提交，最低级别；解决丢失更新问题。
• RC：读已提交；解决丢失更新、脏读问题。
• RR：可重复读；解决丢失更新、脏读、不可重复读问题。（默认隔离级别）
• S：序列化；解决丢失更新、脏读、幻读、不可重复读问题。

2. InnoDB的行锁模式

InnoDB实现了两种类型的行锁：

• 读锁（共享锁、S锁 ）：多个事务对同一个数据可以共享的一把锁，都能访问到数据，只能读不能写。
• 写锁（排他锁、X锁 ）：不能与其他锁并存；能够读、写。

对于UPDATE/INSERT/DELETE语句，InnoDB会自动加上排他锁。

对于普通的SELECT语句，不会加任何锁。

显示给SELECT语句加锁：

共享锁：
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁：
SELECT * FROM table_name WHERE ... FOR UPDATE

3. 行锁升级为表锁

如果不通过索引条件检索数据，那么MySQL会对所有数据添加行锁（所有数据行锁 = 表锁）。

4. 间隙锁

当我们使用范围条件时，请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙”，InnoDB也会对这个“间隙”进行加锁；这就是间隙锁。（Next- Key锁）

解决方案：缩小范围。

5. InnoDB行锁争用情况

show status like 'innodb_row_lock%';

变量	值
Innodb_row_lock_current_waits	0
Innodb_row_lock_time	3639505
Innodb_row_lock_time_avg	662
Innodb_row_lock_time_max	51635
Innodb_row_lock_waits	5494

• 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的整体性能和MylSAM相比就会有比较明显的优势。

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

优化建议：

• 尽可能让所有检索的数据通过索引来完成，避免无索引行锁升级为表锁。
• 合理设计索引，减少锁的范围。
• 尽可能减少索引条件、范围，避免间隙锁。
• 尽量控制事务大小，减少锁定资源量和时间长度。
• 尽可使用低级别事务隔离。（满足业务层面满足需求）

6.6 常用SQL技巧

6.6.1 执行顺序

SQL编写顺序

SELECT
	<select_list>
FROM
	<left_table> <join_type>
JOIN 
	<right_table> ON <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_params>

SQL执行顺序

FROM <left_table>

ON <join_condition>

<join_type> JOIN <right_table>

WHERE <where_condition>

GROUP BY <group_by_list>

HAVING <having_condition>

SELECT <select_list>

ORDER BY <order_by_condition>

LIMIT <limit_params>

6.6.2 正则表达式使用

正则表达式不走索引！！！这里就不做扩展！！！

6.6.3 MySQL常用函数

数字函数

函数	作用
ABS	求绝对值
SQRT	二次方根
MOD	求余叔
CEIL、CEILING	（向上取整）返回不小于参数的最小整数
FLOOR	向下取整，返回值转化为BIGINT
RAND	生成0-1之间随机数，参数可传可不传。传参数，产生重复序列。
ROUND	对参数进行四舍五入
SIGN	返回参数的符号
POW、POWER	所传参数的次方的结果值
SIN	求正弦值
ASIN	反正弦值
COS	求余弦值
ACOS	反余弦值
TAN	求正切值
ATAN	反正切值
COT	求余切值

字符串函数

函数	作用
LENGTH	返回字符串的长度
CONCAT	连接字符串
INSERT	替换字符串函数
LOWER	字母转化小写
UPPER	字母转化大写
LEFT	左截取字符串
RIGHT	右截取字符串
TRIM	去掉字符串两侧空格
REPLACE	替换字符串函数
SUBSTRING	截取字符串
REVERSE	字符串反转

日期函数

函数	作用
CURDATE、CURRENT_DATE	当前系统的日期值
CURTIME、CURRENT_TIME	当前系统的时间值
NOW、SYSDATE	当前系统的日期和时间值
MONTH	指定日期的月份
MONTHNAME	指定日期的月份的英文
DAYNAME	指定日期的星期几的英文
DAYOFWEEK	指定日期对应一周的索引值
WEEK	指定日期是一年中的第几周
DAYOFYEAR	指定日期是一年中的第几天
DAYOFMONTH	指定日期是一个月中的第几天
YEAR	获取年份
TIME_TO_SEC	时间参数转化秒数
SEC_TO_TIME	秒数参数转化时间
DATE_ADD、ADDDATE	向日期添加指定时间的间隔
DATE_SUB、SUBDATE	向日期减去指定时间的间隔
ADDTIME	向时间添加指定时间
SUBTIME	向时间减去指定时间
DATEDIFF	获取参数1日期减去参数2日期的值
DATE_FORMAT	格式化日期
WEEKDAY	指定日期在一周内的对应的工作日索引

聚合函数

函数	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

七、 MySQL高级部分

7.1 MySQL常用工具

1. mysqladmin

执行管理操作的客户端程序。用它来检查服务器的配置和当前状态、创建并删除数据库等。

2. mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具。

语法：

mysqlbinlog [options] log-file1

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

3. mysqldump

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

语法：

mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-database/-A

--输出内容选项
	--add-drop-database:创建数据库语句前加 Drop database
	--add-drop-table:创建表语句前加 Drop table 
	
	-n:不包含数据库的创建语句
	-t:不包含数据表的创建语句
	-d:不包含数据
	
	-T:自动生成两个文件，.sql文件和.txt文件
	

4. mysqlimport/source

客户端数据导入工具，用来导入mysqldump加-T参数后导出的文本文件。

语法：

mysqlimport [options] db_name textfile1 [textfile2...]

--.sql文件
source sqlfile

5. mysqlshow

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

语法：

mysqlshow [options] [db_name [table_name[col_name]]]

--count:数据库及表的统计信息
-i:状态信息

7.2 MySQL日志

MySQL中有4种日志，错误日志、二进制日志（binlog日志）、查询日志、慢查询日志。

1. 错误日志

记录了mysql启动、运行、停止时，发生的错误的相关信息。

默认存放路径为：var/lib/mysql，默认文件名为hostname.err（hostname为主机名）

--查看日志位置
show variables like 'log_error%';

2. 二进制日志

二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句，但是不包括数据查询语句。

此日志对于灾难时数据恢复起着极其重要的作用，MySQL的主从复制，就是通过该binlog实现的。

二进制日志默认没有开启，需要在配置文件中开启，并配置日志格式。

#开启binlog日志，前缀：mysql-bin
log-bin=mysql-bin
#指定日志格式
binlog_format=mixed

-statement：记录的都是SQL语句。
-row:记录的是每一行的数据变更。
-mixed:默认格式。使用了以上两种格式，默认使用statement，特殊情况下使用row。

#查看日志文件
mysqlbinlog filename

日志删除：

#方法一：通过Reset Master 删除全部binlog日志，重新从00001开始
Reset Master

#方法二：删除xxxxx编号之前的日志
purge master logs to 'mysql-bin.xxxxx'

#方法三：删除日期为xxxxxx日期之前产生的日志
purge master logs before 'yyyy-mm-dd hh24:mi:ss'

#方法四：设置过期时间，过了指定的天数后日志自动被删除
--expire_logs_day=3

3. 查询日志

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

默认情况下，查询日志是未开启的。

#开启查询日志 0：关闭 1:开启
general_log=1

#设置日志文件名 默认host_name.log
general_log_file=file_name

4. 慢查询日志

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

long_query_time:最小为0；默认为10秒。

#慢查询日志默认关闭，控制慢查询日志开启 0:关闭 1:开启
slow_query_log=1

#慢查询日志文件名
slow-query-log-file=filename

#慢查询执行限制时间
long_query_time=10

#查看慢查询日志
./mysqldumpslow /www/server/data/mysql-slow.log 

7.3 MySQL主从复制

1. 主从复制概述

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

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

2. 主从复制原理

​ 分为三步：

• Master主库在事务提交时，会将数据变更作为时间Events记录进二进制日志文件中（binlog）中。
• 主库推送二进制日志到从库的中继日志：Relay.log。
• 从库读取中继日志，将中继日志中的操作改变自己的数据。

3. 主从复制优点

主从复制的优点：

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

4. 搭建主从复制

4.1 master

1. 配置文件

   #mysql 服务ID，在集群中唯一
   server-id=1
   
   #mysql binlog日志路径 前缀
   log-bin=/www/server/data/mysql-bin
   
   #是否只读 0:读写 1:只读
   read-only=0
   
   #主从同步忽略的数据库
   binlog-ignore-db=mysql
   
   #指定同步的数据库
   binlog-do-db=task
   
   

2. 重启生效配置

3. 创建同步数据的账户，并且进行授权操作

   #授权
   grant replication slave on *.* to 'name'@'ip' identified by 'name';
   #刷新权限
   flush privileges;
   

4. 查看master状态

   show master status;
   

4.2 slave

1. 配置文件

   #mysql 服务ID，在集群中唯一
   server-id=2
   
   #mysql binlog日志路径 前缀
   log-bin=/www/server/data/mysql-bin
   

2. 重启生效配置

3. 指定master

   change master to master_host='ip',master_user='user',master_password='password',master_log_file='mysql-bin.00001',master_log_pos=413;
   
   - master_log_file:show master status的第一个参数
   - master_log_pos:show master status的第二个参数
   

4. 开启同步

   #开启同步
   start slave;
   
   #slave状态
   show slave status\G;
   
   --Slave_IO_RUNNING:YES
   --Slave_SQL_RUNNING:YES