MySQL数据库-基础梳理(包含sql优化)

索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

索引的优缺点

优点

• 提供查询效率，降低IO成本
• 降低数据排序成本，降低CPU消耗

缺点

• 实际上索引也是一张表，表中保存了主键及索引字段，并指向实体类的记录，所以索引需要占用空间（硬盘）。
• 索引提供了查询效率，但是降低了更新效率，因为在更新时需要对索引的数据进行调整。

索引结构

索引是在MySQL的存储引擎层中实现的，不是在服务器实现的；所以每种存储引擎的索引都不完全一样，也不是索引的存储引擎都支持所有的索引类型。

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

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-TREE索引	不支持	支持	不支持
Full-text	5.6版本开始支持	支持	不支持

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

BTREE结构

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

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

以5叉BTree为例，key的数量：【ceil(m/2)-1】 <= n <= m-1。所以 2 <= n <= 4。当n>4时，中间节点分裂到父节点，两边节点分裂。以下将用C N G A H E K Q M F W L T Z D R X Y S数据为例模拟演变流程，演变工程为一个字符一个字符添加到5叉BTree。

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向上分裂。
   

B+TREE结构

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

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

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

MySQL中的B+Tree

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

索引分类

1. 单值索引：一个索引只包含单个列，一个表可以有多个单值索引；
2. 唯一索引：索引列的值必须唯一，允许有空值；
3. 主键索引：索引列的值必须唯一，不允许有空值；
4. 复合索引：一个索引包含多个列。

索引语法

环境准备

create database demo_01 default charset = utf8mb4;
use demo_01;
CREATE TABLE `city`  (
  `city_id` int(11) NOT NULL,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `country`  (
  `country_id` int(11) NOT NULL,
  `country_name` varchar(50) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `demo_01`.`city` (`city_id`, `city_name`, `country_id`) VALUES (1, '西安', 1);
INSERT INTO `demo_01`.`city` (`city_id`, `city_name`, `country_id`) VALUES (2, 'NewYork', 2);
INSERT INTO `demo_01`.`city` (`city_id`, `city_name`, `country_id`) VALUES (3, '北京', 1);
INSERT INTO `demo_01`.`city` (`city_id`, `city_name`, `country_id`) VALUES (4, '上海', 1);

INSERT INTO `demo_01`.`country` (`country_id`, `country_name`) VALUES (1, 'chain');
INSERT INTO `demo_01`.`country` (`country_id`, `country_name`) VALUES (2, 'America');
INSERT INTO `demo_01`.`country` (`country_id`, `country_name`) VALUES (3, 'Japan');
INSERT INTO `demo_01`.`country` (`country_id`, `country_name`) VALUES (4, 'UK');

创建索引

语法

/*
 * CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name(index_col_name,...);
 * [UNIQUE|FULLTEXT|SPATIAL] 指定索引类型（可选）；UNIQUE 唯一索引 FULLTEXT 全文索引 SPATIAL 空间索引
 * index_name : 索引名称（自定义）
 * [USING index_type] 指定索引结构（可选）；默认为b+树
 * tbl_name ：需要创建索引的表名称
 * index_col_name ： 创建索引的字段（多个值为复合索引，单个值为单值索引）
 * index_col_name : column_name[(length)][ASC|DESC]
 */

示例

create index idx_city_name on city(city_name);

查看索引

语法

-- show index from table_name;  table_name ： 为需要查询到的表名称；

示例

show index from city;
-- 或者
show index from city\G

删除索引

语法

-- drop index index_name on table_name; index_name :需要删除的索引名称  table_name ： 为需要查询到的表名称；

示例

drop index idx_city_name on city;

alter命令

语法

/* 
 * 解释： table_name ： 创建的表名称； column_list 创建的表字段(可以是一组数据)； index_name : 索引名称
 * 添加一个主键，要求索引值必须唯一，且不能为null；语法：alter table table_name add primary key (column_list); 
 * 创建一个唯一索引，要求索引值必须唯一，可以为null；语法：alter table table_name add unique index_name(column_list);
 * 添加普通索引，索引值可以出现很多次；语法：alter table table_name add index index_name(column_list);
 * 创建全文索引；语法：alter table table_name add fulltext index_name(column_list);
 */

示例

alter table city add unique idx_city_name(city_name);

索引的设计原则

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

• 对于查询频次较高，数据量比较大的表建立索引；
• 索引字段的选择，最佳候选列应当从where子句出现的字段，如果where子句中的组合较多，挑选最常用的、过滤效果最后的列的组合；
• 使用唯一索引，区分度高，效率高；
• 索引可以有效的提升查询数据的效率，但索引数量不是越多越好，索引越多维护索引的代价越大，对于插入、更新、删除等DML操作比较频繁的表来说，索引越多DML效率就会越低；切索引过多会引起MySQL的选择困难，提高了选择的代价；
• 使用短索引，索引创建后也是需要用硬盘存储的，因此提高索引访问的I/O效率，可以提升总体的访问效率，假如构成索引的字段总长度比较短，那么给定大小的索引块内可以存储更多的索引值，有效的提高I/O效率；
• 利用左前缀，N个列组合而成的组合索引相当于创建了N个索引，如果查询是where子句使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

/*
 * 创建符合索引
 * create index idx_name_email_status on table_name(`name`,email,status);
 * 相当于
 *     创建 name 索引
 *     创建 name、email 索引
 *     创建 name、email、status 索引
 */

视图

概述

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

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

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

创建视图

语法

/*
 * CREATE [or REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPRABLE}] VIEW view_name[(column_list)] 
 * AS select_statement [WITH[CASCADED | LOCAL] CHECK OPTION]
 * select_statement 查询语句
 * [WITH[CASCADED | LOCAL] CHECK OPTION] 决定了是否运行更新数据使记录不在满足视图的条件。
 * LOCAL ： 只需要满足本视图的条件就可以更新
 * CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新（默认值）
 */

示例

-- 创建视图
CREATE VIEW view_city_country as SELECT c1.*,c2.country_name FROM city c1 , country c2 WHERE c1.country_id = c2.country_id;
-- 查询视图数据
SELECT * FROM view_city_country
-- 更新视图，不建议更新；因为视图就是用来简化查询操作的
UPDATE view_city_country SET city_name = '西安市' WHERE city_id = 1;
-- 查询原始表，发现数据修改成功
SELECT * FROM city;

修改视图

语法

/*
 * ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPRABLE}] VIEW view_name [(column_list)] AS select_statement
 * [WITH[CASCADED | LOCAL] CHECK OPTION]
 * [WITH[CASCADED | LOCAL] CHECK OPTION] 决定了是否运行更新数据使记录不在满足视图的条件。
 * LOCAL ： 只需要满足本视图的条件就可以更新
 * CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新（默认值）
 */

示例

-- 修改视图
ALTER VIEW view_city_country as SELECT * from city;
-- 查询视图数据，发现修改成功
SELECT * FROM view_city_country

查询视图

语法

-- 查询视图或表，没有只查询视图的语句
show tables;
-- 查询创建视图的语句
-- show create view view_name; view_name：视图名称

示例

show create view view_city_country;

删除视图

语法

/* 
 * Drop view [IF exists] view_name[,view_name] ... [RESTRICT | CASCADE]
 * IF exists 如果存在
 */

示例

DROP VIEW IF EXISTS view_city_country;

存储过程和函数

概述

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

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

存储函数

语法

/*
 * CREATE FUNCTION fun_name([param type ...])
 * RETURNS type
 * BEGIN
 * 	...
 * END
 * fun_name：函数名称
 * type：返回值类型
 * [param type ...] 参数列表
 */

需求

通过countryid查询条数city表的总条数

示例

delimiter $
CREATE FUNCTION fun_demo1(country_id INT)
RETURNS INT
BEGIN
	DECLARE count int;
	SELECT COUNT(city_id) INTO count FROM city WHERE country_id = country_id;
	RETURN count;
END $
delimiter ;
-- 函数不可以使用call调用。
SELECT fun_demo1(1);

创建存储过程

语法

/* 
 * CREATE PROCEDURE procedure_name([proc_parameter[,...]])
 * begin
 * 	-- SQL语句
 * end;
 * procedure_name：存储过程名称
 * [proc_parameter[,...]]：存储过程的参数
 */

在使用存储过程的时候需要修改默认的结束字符，否则在抒写SQL的时候会自动结束导致存储过程创建失败。

默认的结束字符：；或者/G

修改结束字符的语句为：delimiter $

示例

delimiter $
CREATE PROCEDURE test_proc()
begin 
	select 'Hello MySQL';
end$
delimiter ;

调用存储过程

语法

-- call procedure_name() ; 注意末尾需要使用结束字符

示例

delimiter ;
call test_proc();

查看存储/函数过程

语法

/*
 * 查询db_name数据库中的所有存储过程
 * SELECT NAME FROM mysql.proc WHERE db = 'db_name';  注意末尾需要使用结束字符
 * 查询存储过程的状态信息
 * SHOW PROCEDURE/FUNCTION STATUS; 注意末尾需要使用结束字符
 * 查询某个存储过程的定义
 * SHOW CREATE PROCEDURE/FUNCTION db_name.proc_name; 注意末尾需要使用结束字符
 * db_name：数据库名称
 * proc_name：存储过程名称
 */

示例

delimiter ;
-- 查询数据库demo_01下的全部存储过程名字
SELECT NAME FROM mysql.proc WHERE db = 'demo_01';
-- 查询存储过程的状态信息
SHOW PROCEDURE STATUS;
-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE demo_01.test_proc;

删除存储/函数过程

语法

-- DROP PROCEDURE/FUNCTION [IF EXISTS] proc_name;注意末尾需要使用结束字符  proc_name ：存储过程名称

示例

DROP PROCEDURE IF EXISTS test_proc;

存储过程的语法结构

存储过程是可以编程的，可以使用变量、表达式、控制结构来完成比较复杂的功能。

变量

DECLARE

通过declare可以定义一个局部变量，改变量的范围只能在begin…end中使用

语法

-- declare value_name[,...] type [DEFAULT value] value_name:变量名称（可以是多个） type：变量类型 value 默认值

示例

delimiter $
CREATE PROCEDURE proc_demo1()
BEGIN 
	DECLARE num int DEFAULT 10;
	SELECT CONCAT( 'num的值为：',num);
END$
delimiter ;
call proc_demo1;

SET

直接赋值使用set，可以赋常量或者表达式

语法1

SET value_name = expr[,value_name = expr] ...

示例1

delimiter $
CREATE PROCEDURE proc_demo2()
BEGIN 
	DECLARE num int DEFAULT 10;
	set num = num + 10;
	SELECT CONCAT( 'num的值为：',num);
END$
delimiter ;
call proc_demo2;

---

也可以通过select … into方式进行赋值操作：

语法2

-- select table_column into value_name from table_name; table_name:查询表名称 table_column：表中的列字段 value_name ：需要赋值的内容

示例2

delimiter $
CREATE PROCEDURE proc_demo3()
BEGIN 
	DECLARE num int DEFAULT 10;
	select count(name)
	SELECT CONCAT( 'num的值为：',num);
END$
delimiter ;
call proc_demo3;

if条件判断

需求

根据定义的身高变量，判读当前身高的所属身材类型
	180及以上 ------> 身材高挑
	171-179  ------> 标准身材
	170及以下 ------> 一般身材

语法

/*
 * IF search_condition THEN statement_list 
 * [ELSEIF search_condition THEN statement_list ]...
 * [ELSE statement_list]
 * END IF;
 * search_condition 条件，如果满足条件就执行statement_list的SQL语句，否则执行后面的 ELSEIF或者ELSE
 */

示例

delimiter $
CREATE PROCEDURE proc_demo4()
BEGIN 
	DECLARE height INT DEFAULT 175;
	DECLARE description VARCHAR(50) DEFAULT '' ;
	IF height >= 180 THEN SET description = '身材高挑';
		ELSEIF height > 170 THEN SET description = '标准身材';
		ELSE SET description = '一般身材';
	END IF;
	SELECT CONCAT('身高 ',height,'对应的身材为：',description);
END $
delimiter ;
call proc_demo4;

传递参数

语法

/* 
 * CREATE PROCEDURE procedure_name([IN/OUT/INOUT]参数名 参数类型)
 * begin
 * 	-- SQL语句
 * end;
 * procedure_name：存储过程名称
 * IN：输入参数，该参数可以作为传入值（默认）
 * OUT：输出参数，该参数可以作为返回值
 * INOUT：输入、输出参数
 */

示例

IN-输入

delimiter $
CREATE PROCEDURE proc_demo5(IN height INT)
BEGIN 
	DECLARE description VARCHAR(50) DEFAULT '' ;
	IF height >= 180 THEN SET description = '身材高挑';
		ELSEIF height > 170 THEN SET description = '标准身材';
		ELSE SET description = '一般身材';
	END IF;
	SELECT CONCAT('身高 ',height,'对应的身材为：',description);
END $
delimiter ;
call proc_demo5(198);

OUT-输出

delimiter $
CREATE PROCEDURE proc_demo6(IN height INT, OUT description VARCHAR(50))
BEGIN 
	IF height >= 180 THEN SET description = '身材高挑';
		ELSEIF height > 170 THEN SET description = '标准身材';
		ELSE SET description = '一般身材';
	END IF;
END $
delimiter ;
call proc_demo6(198,@description);
SELECT @description;

@description：这种变量要在变量名称前面加上“@”符号，叫做用户变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。

@@GLOBAL.SORT_BUFFER_SIZE：这种变量前面加上“@@”符号，叫做系统变量。

INOUT-输入输出

delimiter $
CREATE PROCEDURE proc_demo7(INOUT height INT, INOUT description VARCHAR(50))
BEGIN 
	SELECT description;
	IF height >= 180 THEN SET description = '身材高挑';
		ELSEIF height > 170 THEN SET description = '标准身材';
		ELSE SET description = '一般身材';
	END IF;
END $
delimiter ;
SET @height = 180;
SET @description = '可以啊，这身材绝了';
call proc_demo7(@height,@description);
SELECT @description;

case结构

需求

给定一个月数，计算出所在的季度

语法

/*
 * case_value： 条件或值
 * statement_list： SQL语句
 * when_value：判断条件（值）
 * search_condition：判断条件
 * 方式一：
 * 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;
 */

示例

delimiter $
CREATE PROCEDURE proc_demo8(mon INT)
BEGIN 
	DECLARE quarterly VARCHAR(50);
	CASE
		WHEN mon <= 3 THEN SET quarterly = '第一季度';
		WHEN mon >= 4 AND mon <= 6 THEN SET quarterly = '第二季度';
		WHEN mon <= 9 THEN SET quarterly = '第三季度';
		ELSE SET quarterly = '第四季度';
	END CASE;
	SELECT CONCAT('根据传入的月份：',mon,'计算得出为：',quarterly);
END $
delimiter ;
call proc_demo8(4);

while循环

需求

计算从1-n的值

语法

/*
 * WHILE search_condition DO
 * 	   statement_list
 * END WHILE;
 * search_condition：判断条件
 * statement_list：循环体逻辑
 */

示例

delimiter $
CREATE PROCEDURE proc_demo9(num INT)
BEGIN 
	DECLARE val INT DEFAULT 1;
	DECLARE result INT DEFAULT 0;
	WHILE val <= num DO
		SET result = result + val;
		SET val = val + 1;
	END WHILE;
	SELECT CONCAT('根据传入的数据：',num,'计算结果为：',result);
END $
delimiter ;
call proc_demo9(2);

repeat循环

需求

计算从1-n的值

语法

/*
 * REPEAT
 * 	   statement_list
 * 	   UNTIL search_condition
 * END REPEAT;
 * search_condition：判断条件
 * statement_list：循环体逻辑
 */

示例

delimiter $
CREATE PROCEDURE proc_demo10(num INT)
BEGIN 
	DECLARE val INT DEFAULT 1;
	DECLARE result INT DEFAULT 0;
	REPEAT
		SET result = result + val;
		SET val = val + 1;
		UNTIL val > num
	END REPEAT;
	SELECT CONCAT('根据传入的数据：',num,'计算结果为：',result);
END $
delimiter ;
call proc_demo10(2);

loop循环

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

需求

计算从1-n的值

语法

/*
 * [begin_lable:] LOOP
 * 	   statement_list
 * END LOOP;
 * statement_list：循环体逻辑
 */

由于loop语句自己没有实现，在LEAVE语句中在演示loop的示例。

leave语句

用于从标注的流程构造中退出，通常和begin…end或者循环一起使用，示例接loop循环

示例

delimiter $
CREATE PROCEDURE proc_demo11(num INT)
BEGIN
	DECLARE result INT DEFAULT 0;
	count: LOOP
		SET result = result + num;
		SET num = num - 1;
		IF num <= 0 THEN LEAVE count;
		END IF;
	END LOOP count;
	SELECT CONCAT('计算结果为：',result);
END $
delimiter ;
call proc_demo11(2);

游标/光标

游标是用来存储查询结果集的数据类型，在存储过程的函数中可以使用游标对结果集进行循环处理；游标的使用包括：声明、OPEN、FETCH、CLOSE。

语法

声明

-- DECLARE cursor_name CURSOR FOR select_statement;  cursor_name:游标名称  select_statement:查询语句

OPEN

-- OPEN cursor_name;  cursor_name:游标名称 

FETCH

-- FETCH cursor_name INTO var_name[,var_name];  cursor_name:游标名称  var_name:行数据名称

CLOSE

-- CLOSE cursor_name;  cursor_name:游标名称 

示例

-- 初始化脚本
CREATE TABLE `demo_01`.`emp`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NULL COMMENT '姓名',
  `age` int(11) NULL COMMENT '年龄',
  `salary` int(11) NULL COMMENT '薪水',
  PRIMARY KEY (`ID`)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES (1, '金毛狮王', 55, 3800);
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES (2, '白眉鹰王', 60, 4000);
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES (3, '青翼蝠王', 38, 2800);
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES (4, '紫衫龙王', 42, 1800);

-- 游标遍历
delimiter $
CREATE PROCEDURE proc_demo12()
BEGIN
	-- 定义几个用于接受emp表中数据的变量，查询语句中有几个字段就需要定义
	DECLARE e_id INT;
	DECLARE e_name VARCHAR(50);
	DECLARE e_age INT;
	DECLARE e_salary INT;
	-- 定义一个边界变量来控制FECHE的数据抓取
	DECLARE has_data INT DEFAULT 1;
	-- 声明游标
	DECLARE result CURSOR FOR SELECT id,name,age,salary FROM emp;
	/*
	 * 设置必须在 声明游标 下方
	 * 设置句柄条件，句柄机制（FETCH抓取不到数据后会触发）
	 * EXIT ：退出
	 * HANDLER FOR NOT FOUND ：拿不到数据
	 */
	DECLARE EXIT HANDLER FOR NOT FOUND SET has_data = 0;
	OPEN result;
	-- 使用REPEAT循环
	REPEAT
		FETCH result INTO e_id,e_name,e_age,e_salary;
		SELECT CONCAT('id = ',e_id,' name = ',e_name,' age = ',e_age,' salary = ',e_salary);
		UNTIL has_data = 0
	END REPEAT;
	CLOSE result;
END $
delimiter ;
call proc_demo12();

触发器

介绍

触发器是与表有关的数据库对象，指在insert、update、delete之前或之后触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还不支持行级操作，不支持语句触发。

触发器类型	NEW和OLD的使用
INSERT型触发器	NEW 表示将要或者已经新增的数据
UPDATE型触发器	OLD 表示修改之前的数据，NEW 表示将要或已经修改后的数据
DELETE型触发器	OLD 表示将要或者已经删除的数据

创建触发器

语法

/*
 * CREATE TRIGGER trigger_name
 * BEFORE/AFTER INSERT/UPDATE/DELETE
 * ON table_name
 * [FOR EACH ROW]
 * BEGIN
 * 	trigger_statement
 * END
 * trigger_name：触发器名称
 * BEFORE/AFTER：在之前或在之后
 * INSERT/UPDATE/DELETE：配合上面就是，在insert之前或之后
 * table_name： 表名
 * [FOR EACH ROW]：行级触发器
 * trigger_statement：触发器语句
 */

需求

通过触发器记录emp表的数据变更日志，包括增加、修改、删除。

示例

-- 初始化脚本
CREATE TABLE `demo_01`.`emp_logs`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `operation` varchar(20) NOT NULL COMMENT '操作类型，insert、update、delete',
  `operation_time` datetime NOT NULL COMMENT '操作时间',
  `operation_id` int(11) NOT NULL COMMENT '操作表的ID',
  `operation_params` varchar(500) NULL COMMENT '操作参数',
  PRIMARY KEY (`ID`)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

delimiter $
-- 创建一个插入的触发器
CREATE TRIGGER emp_insert_trigger
AFTER INSERT ON emp FOR EACH ROW
BEGIN
	INSERT INTO emp_logs VALUES(NULL,"insert",NOW(),new.id,CONCAT("插入前（ID = ",new.id," name = ",new.name," age = ",new.age," salary = ",new.salary,"）"));
END$

-- 测试
INSERT INTO emp VALUES (NULL,"光明左使",30,3500)$
SELECT * FROM emp_logs\G

-- 创建一个修改的触发器
CREATE TRIGGER emp_update_trigger
AFTER UPDATE ON emp FOR EACH ROW
BEGIN
	INSERT INTO emp_logs VALUES(NULL,"update",NOW(),new.id,CONCAT("修改前（ID = ",old.id,"， name = ",old.name,"， age = ",old.age,"， salary = ",old.salary,"； 修改后ID = ",new.id," name = ",new.name," age = ",new.age," salary = ",new.salary,"）"));
END$

-- 测试
UPDATE emp SET age = 39 WHERE id = 3$
SELECT * FROM emp_logs\G

-- 创建一个删除的触发器
CREATE TRIGGER emp_delete_trigger
AFTER DELETE ON emp FOR EACH ROW
BEGIN
	INSERT INTO emp_logs VALUES(NULL,"delete",NOW(),old.id,CONCAT("删除前（ID = ",old.id,"， name = ",old.name,"， age = ",old.age,"， salary = ",old.salary,"）"));
END$

-- 测试
DELETE FROM emp WHERE id = 5$
SELECT * FROM emp_logs\G

delimiter ;

删除触发器

语法

-- drop trigger trigger_name;  trigger_name:触发器名称

示例

drop trigger emp_delete_trigger;

查看触发器

语法

-- show triggers;

示例

show triggers;

MySQL体系结构

整个MySQL Server由以下组成：

连接池（Connection pool）：连接池组件；由于每次建立建立需要消耗很多时间，连接池的作用就是将这些连接缓存下来，下次可以直接用已经建立好的连接，提升服务器性能；

管理工具和服务（Management Services & Utilities）：系统管理和控制工具，例如备份恢复、Mysql复制、集群等；

SQL接口（SQL interface）`：接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface；

解析器（parser）: SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本， 主要功能：

1. 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的；
2. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。

优化器（optimizer）：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询；用一个例子就可以理解： select uid,name from user where gender = 1;这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤；将这两个查询条件联接起来生成最终查询结果。

缓存器（caches）： 查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。通过LRU算法将数据的冷端溢出，未来得及时刷新到磁盘的数据页，叫脏页；这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

由此，可以将MySQL大致看成四层结构；

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

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

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

第四层（存储层）：主要是将数据存储在文件系统之上，并完成与存储引擎的交互，包括日志等信息；

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

存储引擎

概述

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

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

Oracle ， SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎， 可以根据需要使用相应引擎，或者编写存储引擎。MySQL 5.0支持的存储引擎包含：InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等， 其中InnoDB和BDB提供事务安全表， 其他存储引擎是非事务安全表。可以通过指定show engines!来查询当前数据库支持的存储引擎：

创建表的时候如果不指定存储引擎，系统会使用默认的存储引擎；MySQL在5.6版本以前默认的存储引擎是MyISAM，以后修改为了InnoDB。

查询MySQL数据库默认的存储引擎：

show variables like '%storage_engine%';

特性

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

存储引擎选择

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

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

SQL优化

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

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

研究SQL优化要求有索引，如果没有索引就没必要研究了。

优化步骤

查看SQL执行频率

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

针对InnoDB的SQL执行频率：

定位低效率执行SQL

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

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

1. id列：用户登录MySQL时， 系统分配的"connection_id"， 可以使用函数connection_id()查看；
2. user列：显示当前用户。如果不是root， 这个命令就只显示用户权限范围的SQL语句；
3. host列：显示这个语句是从哪个ip的哪个端口上发的， 可以用来跟踪出现问题语句的用户；
4. db列：显示这个进程目前连接的是哪个数据库；
5. command列：显示当前连接的执行的命令， 一般取值为休眠(s1eep) ， 查询(query) ， 连接(connect) 等；
6. time列：显示这个状态持续的时间， 单位是秒；
7. state列：显示使用当前连接的SQL语句的状态， 很重要的列。state描述的是语句执行中的某一个状态。一个SQL语句， 以查询为例， 可能需要经过copying to tmp table、sorting resu1t、sending data等状态才可以完成；
8. info列：显示这个SQL语句， 是判断问题语句的一个重要依据。

explain分析执行计划

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

查询SQL语句的执行计划：

explain select * from city where city_id = 1;

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

环境准备

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

CREATE TABLE `demo_01`.`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 `demo_01`.`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 `demo_01`.`t_user` (`id`, `username`, `password`, `name`) VALUES ('1', 'super', '123123', '超级管理员');
INSERT INTO `demo_01`.`t_user` (`id`, `username`, `password`, `name`) VALUES ('2', 'admin', '123123', '系统管理员');
INSERT INTO `demo_01`.`t_user` (`id`, `username`, `password`, `name`) VALUES ('3', 'itcast', '123123', 'test02');
INSERT INTO `demo_01`.`t_user` (`id`, `username`, `password`, `name`) VALUES ('4', 'stu1', '123123', '学生1');
INSERT INTO `demo_01`.`t_user` (`id`, `username`, `password`, `name`) VALUES ('5', 'stu2', '123123', '学生2');
INSERT INTO `demo_01`.`t_user` (`id`, `username`, `password`, `name`) VALUES ('6', 't1', '123123', '老是1');

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

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

explain之id

id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者操作表的顺序；id情况有三种：

1. id相同表示加载表的顺序是从上到下。

   explain select * from t_role r,t_user u,user_role ur where r.id = ur.role_id and u.id = ur.user_id;
   

2. id不同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'));
   

3. id有相同也有不同，同时存在；id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

   explain select * from t_role r,(select * from user_role ur where ur.user_id = (select id from t_user where username = 'stu1')) a where r.id = a.role_id;
   

   

explain之select_type

表示select的类型，常见的取值如下：

select_type	含义
SIMPLE	SIMPLE代表单表查询，其中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
DERIVED	在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DEPENDENT SUBQUERY	都是where后面的条件，subquery是单个值，dependent subquery是一组值
UNCACHEABLE SUBQUREY	当使用了@@来引用系统变量的时候，不会使用缓存
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT

示例

SIMPLE

explain select * from city;

PRIMARY

explain select * from t_user where id = (select id from user_role where role_id = '9');

SUBQUERY

explain select * from t_user where id = (select id from user_role where role_id = '9');

UNION

explain select * from t_user where id = '1' union select * from t_user where id = '2';

意思为查询select * from t_user where id = '1’和select * from t_user where id = ‘1’ 最后取他们的并集

explain之table

查询的数据来源于那张表

explain之type

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

type	含义
NULL	MySQL不访问任何表、索引，直接返回结果
system	表中只有一行记录且大多数是系统表
const	表中只有一行记录，只通过一次索引就可以找到，const用于比较primary key或者unique索引。因为只匹配一行数据所以很快；如果将主键置于where列表中，MySQL就能将该查询转换为一个常量。const于将主键或唯一索引的部分与常量值进行比较。
eq_ref	使用唯一索引，查询的关联数据只有一条；常见于主键或者唯一索引扫描
ref	非唯一索引，返回匹配某个单独值得所有行。本质上也是一种索引访问，返回所有匹配某个单独只的所有行
range	只检索给定返回的行，使用一个索引来选择行。where之后出现between、<、>、in等操作（in可能会失效）。
index	index和all的区别为index类型只遍历了索引树，通常比all快（不需要回表），all是遍历数据文件。
all	遍历全表找到匹配的行

执行效率依次降低，一般来说我们只需要优化到range就可以了；最好优化到ref级别。

null

explain select now();

const

explain select * from (select * from t_user where id = '1');

eq_ref

explain select * from t_user u,t_role r where u.id = r.id;

ref

create index idx_user_name on t_user(name);
explain select * from t_user where name = 'a';

index

explain select id from t_user;

all

explain select * from t_user;

explain之key

possible_keys：显示查询时可能用到的索引，一个或多个

key：实际使用到的索引，如果为null则表示没有使用索引

key_len：表示索引中使用的字节数，改值为索引字段最大可能长度；可以用来判断复合索引是否是被完全使用。

utf8：一个字符占3个字节 所以 char(20)，再有索引的情况下是60，如果可以为空则是61（null占一个用于标识）；如果是varchar则为63个字符因为用2个字节标识可变程度

explain之rows

被索引优化查询的数据个数（实际通过索引查询的数据个数）。

explain之extra

常见值	含义
using filesort	性能消耗较大（需要额外的一次排序（查找）），一般出现在order by语句中。
using temporary	性能消耗较大（用到了临时表），一般出现在group by语句中。
using index	性能提升（索引覆盖\覆盖索引）；不读取源文件，只从索引文件中获取数据（不需要回表查询）
using where	需要回表查询
impossible where	where子句不可能出现

using filesort

1. 在单索引的情况下，如果排序和查找是同一个字段，则不会出现using filesort；反之会出现；为了避免可以选择where那些字段，就order by那些数据；
2. 复合索引，不能跨列（最佳左前缀）；为了避免按照复合索引的顺序使用，不要跨列或无序使用；

using temporary

1. 一般出现在group by语句中；已经有表了，但不适用，必须在用一张表。

using index

1. 如果用到了索引覆盖，会对possible_keys和keys造成影响；
2. 如果没有where，索引只出现在key中；
3. 如果有where，则索引都会出现。

explain select * from t_user where username like 'a%' order by name;
explain select * from t_user where username like 'a%' order by username;

explain select name from t_user where username like 'a%' group by name;

索引使用原则

1. 全值匹配，索引生效执行效率最高；
2. 最左前缀，如果使用了复合索引，要遵循最左前缀法则；指的是查询从索引的最左前列开始，并且不跳过索引中的列；
3. 范围查询右侧列不能使用索引，使用了范围查询后面的将不会使用索引；
4. 不要再索引列上进行运算操作，如果使用了运算操作索引将失效；
5. 字符串不加单引号，如果字符串没有加单引号索引将失效；
6. **尽量使用覆盖索引，避免select ***。
7. 用or分割开的条件，如果or前的条件中的列有索引，后面的列没有索引则会造成索引失效；
8. 以%开头的like模糊查询会造成索引失效；
9. 如果MySQL评估使用索引比全表扫描慢则不会使用索引；
10. is null 或者 is not null可能会造成索引失效；
11. in 使用索引，not in 索引失效；
12. 尽量使用复合索引，少使用单列索引，因为复合索引相当于创建了多个索引，并且数据库会选择一个最优的索引来使用。

SQL优化

1. 在大数据量数据导入时建议采取主键顺序导入的方式；

2. 在进行数据的insert操作的时候可以采用以下几种优化方案

   1. 如果需要同时对一张表插入很多行数据，尽量使用多个值表的insert语句，这种方式将大大的缩减了客户端与数据库之间的连接、关闭等消耗；

      /*
       * 优化前
       * insert into table_name values();
       * insert into table_name values();
       * 优化后
       * insert into table_name values(),();
       */
      

   2. 在事务中进行数据插入，如果数据量比较大建议分段提交；

   3. 数据有序插入；

3. 多字段排序应统一升序或降序，排序顺序和索引顺序一致；

4. 如果查询包含group by但是用户想要避免排序结果的消耗可以执行order by null禁止排序；

5. 尽量使用多表连接查询来替换子查询；

6. 对于包含or的查询子句，如果利用索引则每个or之间的条件列都必须用索引，而且不能使用复合索引；如果没有索引可以考虑增加索引，建议使用union替换or语句；

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

   1. 可以在索引上完成排序分页操作，然后根据主键关联回源表查询；

   2. 在主键自增的表中可以把limit查询转换成某个位置的查询。

      -- select * from table_name where id > 2000000 limit 10;(替换掉原来的 select * from table_name limit 2000000,10 ;)
      

      要求主键自增，不能出现数据断层。如果不满足可能造成数据查询的不准确性

8. 使用SQL提示

   1. 使用use index

      在查询语句表名的后面添加use index来提供希望MySQL去参考的索引列表，避免使用其他的可用索引；

      -- select * from table_name use index(index_name);
      

   2. 使用ignore index

      在查询语句表名的后面添加ignore index来提供希望MySQL去参考的索引列表中忽略的索引，使用其他的可用索引；

      -- select * from table_name ignore index(index_name);
      

   3. 使用force index

      在查询语句表名的后面添加force index来提供强制MySQL使用这个索引。

      -- select * from table_name force index(index_name);
      

filesort优化

通过创建合适的索引， 能够减少Filesort的出现， 但是在某些情况下， 条件限制不能让Filesort消失，那就需要加快Filesort的排序操作。对于Filesort，MySQL有两种排序算法：

• 两次扫描算法：MySQL4.1之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序， 如果sortbuffer不够， 则在临时表temporary table中存储排序结果。完成排序之后， 再根据行指针回表读取记录， 该操作可能会导致大量随机I/O操作。
• 一次扫描算法：一次性取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销较大， 但是排序效率比两次扫描算法要高。

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

MySQL锁

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）；在数据库中，传统的计算机资源（CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。

锁分类

对数据操作的粒度分：

• 表锁：操作时，锁定整张表；
• 行锁：操作时，锁定当前操作行。

对数据操作分：

• 读锁（共享锁）：针对同一个数据，多个读操作不会相互影响；
• 写锁（排它锁）：当前操作没有完成之前会阻塞其他读写操作。

MySQL锁

MySQL锁的机制比较简单，最显著的特点是不同的存储引擎支持的不同锁。

存储引擎	表级锁	行级锁	页面锁
MyISAM	支持	不支持	不支持
INNODB	支持	支持（默认）	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

MySQL这三种锁的特性大致归纳为：

表级锁：偏向MyISAM存储引擎，开销小、加锁快、不会出现死锁、锁定粒度大、发生锁冲突的概率最高、并发度低；

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

页面锁：开销和加锁时间界于表锁和行锁之间、会出现死锁、锁定粒度界于表锁和行锁之间、并发度一般。

MyISAM

对MyISAM表系统会自动加入读写锁，在对MyISAM表进行读操作的时候会阻塞其他用户对表的写操作（不能获取到写锁）不会阻塞读操作；在对MyISAM表进行写操作的时候回阻塞其他用户的读、写操作；此外MyISAM的读写操作时写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因，在大量更新操作的时候查询很难得到锁，从而造成较长时间的阻塞。

MyISAM不支持事物操作。

InnoDB

对MyISAM表系统会自动加入读写锁，在对MyISAM表进行读操作的时候不会阻塞其他用户读、写操作；在对MyISAM表进行写操作的时候回阻塞其他用户的读、写操作；此外MyISAM的读写操作时写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因，在大量更新操作的时候查询很难得到锁，从而造成较长时间的阻塞。

InnoDB支持事物操作。

在索引失效的情况下可能会造成行锁升级为表锁；

在范围更新的时候可能会造成间隙锁（比如修改ID的值为1-10的数据，但原来没有ID为8的数据，在修改完成之前操作新增ID为8的数据将会被阻塞）；

事物特性

事物是由一组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）	×	×	×	×

oracle的默认值是：Read committed；MySQL的默认值是：Repeatable read

SQL技巧

SQL顺序

编写顺序

select distinct <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>

执行顺序

from <left table> on <join_condition> <join_type> join <right_table> where <where_condition> group by <group_by_list> having <having_condition> select distinct <select list> order by <order_by_condition> limit <limit_params>

正则表达式

符号	含义
^	在字符串开始处进行匹配
$	在字符串末尾处进行匹配
.	匹配任意单个字符，包括换行符
[…]	匹配括号内的任意字符
[^…]	匹配除括号内的任意字符
a*	匹配零个或多个a（包括空串）
a+	匹配一个或多个a（不包括空串）
a?	匹配零个或多个a
a1|a2	匹配a1或a2
a(m)	匹配m个a
a(m,)	至少匹配m个a
a(m,n)	匹配m个a到n个a
a(,n)	匹配0到n个a
(…)	将模式元素组成单一元素

示例：select * from emp where name regexp ‘^T’;

注意需要使用regexp来代替like

MySQL常用函数

数字函数

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

字符串函数

函数名称	作用
LENGTH	返回字符串的字节长度
CONCAT	合并字符串
INSERT	替换字符串
LOWER	将字符串的字符转换为小写
UPPER	将字符串的字符转换为大写
LEFT	从左侧截取字符串，返回字符串左边若干个字符
RIGHT	从右侧截取字符串，返回字符串右边若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回新的字符串
SUBSTRING	截取字符串，从指定位置开始的指定字符串
REVERSE	字符串反转（逆序）函数，返回与原来字符串顺序想法的字符串

日期函数

函数名称	含义
CURDATE和CURRENT_DATE	返回当前系统的日期
CURTIME和CURRENT_TIME	返回当前系统的时间值
NOW和SYSTEM	返回当前系统的日期和时间值
MONTH	获取指定日期中的月份
WEEk	获取指定日期是一年中的第几周
DAYOFYEAR	获取指定日期是一年中的第几天
DAYOFMONTH	获取指定日期是一个月的第几天
YEAR	获取年份
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC功能相反
DATE_ADD和ADDDATE	向指定日期添加指定的时间间隔
DATE_SUB和SUBDATE	向指定日期减少指定的时间间隔
ADDTIME	在原始时间上加上指定的时间
SUBTIME	在原始时间上减去指定的时间
DATEDIFF	获取两个日期的间隔（参数一减去参数二）
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值

聚合函数

函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列的平均值
LEFT	从左侧截取字符串，返回字符串左边若干个字符
RIGHT	从右侧截取字符串，返回字符串右边若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回新的字符串
SUBSTRING	截取字符串，从指定位置开始的指定字符串
REVERSE	字符串反转（逆序）函数，返回与原来字符串顺序想法的字符串

日期函数

函数名称	含义
CURDATE和CURRENT_DATE	返回当前系统的日期
CURTIME和CURRENT_TIME	返回当前系统的时间值
NOW和SYSTEM	返回当前系统的日期和时间值
MONTH	获取指定日期中的月份
WEEk	获取指定日期是一年中的第几周
DAYOFYEAR	获取指定日期是一年中的第几天
DAYOFMONTH	获取指定日期是一个月的第几天
YEAR	获取年份
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC功能相反
DATE_ADD和ADDDATE	向指定日期添加指定的时间间隔
DATE_SUB和SUBDATE	向指定日期减少指定的时间间隔
ADDTIME	在原始时间上加上指定的时间
SUBTIME	在原始时间上减去指定的时间
DATEDIFF	获取两个日期的间隔（参数一减去参数二）
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值

聚合函数

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