MySQL-进阶篇

一、连接查询

图解示意图

1、建表语句

部门和员工关系表：

CREATE TABLE `tb_dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `deptName` varchar(30) DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `empName` varchar(20) DEFAULT NULL COMMENT '员工名称',
  `deptId` int(11) DEFAULT '0' COMMENT '部门ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

2、七种连接查询

• 图1：左外连接

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 LEFT JOIN tb_emp t2 on t1.id=t2.deptId;

• 图2：右外连接

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 RIGHT JOIN tb_emp t2 on t1.id=t2.deptId;

• 图3：内连接

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 inner join tb_emp t2 on t1.id=t2.deptId;

• 图4：左连接

查询tb_dept表特有的地方。

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 LEFT JOIN tb_emp t2 on t1.id=t2.deptId
WHERE t2.deptId IS NULL;

• 图5：右连接

查询tb_emp表特有的地方。

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 RIGHT JOIN tb_emp t2 on t1.id=t2.deptId
WHERE t1.id IS NULL;

• 图6：全连接

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 LEFT JOIN tb_emp t2 on t1.id=t2.deptId
UNION
select t1.*,t2.empName,t2.deptId 
from tb_dept t1 RIGHT JOIN tb_emp t2 on t1.id=t2.deptId

• 图7：全不连接

查询两张表互不关联到的数据。

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 RIGHT JOIN tb_emp t2 on t1.id=t2.deptId
WHERE t1.id IS NULL
UNION
select t1.*,t2.empName,t2.deptId 
from tb_dept t1 LEFT JOIN tb_emp t2 on t1.id=t2.deptId
WHERE t2.deptId IS NULL

二、时间日期查询

1、建表语句

CREATE TABLE `ms_consume` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `user_name` varchar(20) NOT NULL COMMENT '用户名',
  `consume_money` decimal(20,2) DEFAULT '0.00' COMMENT '消费金额',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='消费表';

2、日期统计案例

• 日期范围内首条数据

场景：产品日常运营活动中，经常见到这样规则：活动时间内，首笔消费满多少，优惠多少。

SELECT * FROM
	(
		SELECT * FROM ms_consume
		WHERE
			create_time 
		BETWEEN '2019-12-10 00:00:00' AND '2019-12-18 23:59:59'
		ORDER BY create_time
	) t1
GROUP BY t1.user_id ;

• 日期之间时差

场景：常用的倒计时场景

SELECT t1.*,
	   timestampdiff(SECOND,NOW(),t1.create_time) second_diff 
FROM ms_consume t1 WHERE t1.id='9' ;

• 查询今日数据

-- 方式一
SELECT * FROM ms_consume 
WHERE DATE_FORMAT(NOW(),'%Y-%m-%d')=DATE_FORMAT(create_time,'%Y-%m-%d');
-- 方式二
SELECT * FROM ms_consume 
WHERE TO_DAYS(now())=TO_DAYS(create_time) ;

• 时间范围统计

场景：统计近七日内，消费次数大于两次的用户。

SELECT user_id,user_name,COUNT(user_id) userIdSum 
FROM ms_consume WHERE create_time>date_sub(NOW(), interval '7' DAY) 
GROUP BY user_id  HAVING userIdSum>1;

• 日期范围内平均值

场景：指定日期范围内的平均消费，并排序。

SELECT * FROM
	(
		SELECT user_id,user_name,
			AVG(consume_money) avg_money
		FROM ms_consume t
		WHERE t.create_time BETWEEN '2019-12-10 00:00:00' 
							AND '2019-12-18 23:59:59'
		GROUP BY user_id
	) t1
ORDER BY t1.avg_money DESC;

三、树形表查询

1、建表语句

CREATE TABLE ms_city_sort (
	`id` INT (11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
	`city_name` VARCHAR (50) NOT NULL DEFAULT '' COMMENT '城市名称',
	`city_code` VARCHAR (50) NOT NULL DEFAULT '' COMMENT '城市编码',
	`parent_id` INT (11) NOT NULL DEFAULT '0' COMMENT '父级ID',
	`state` INT (11) NOT NULL DEFAULT '1' COMMENT '状态：1启用,2停用',
	`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
	PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '城市分类管理';

2、直接SQL查询

SELECT t1.*, t2.parentName
FROM ms_city_sort t1
LEFT JOIN (
	SELECT
		m1.id,m2.city_name parentName
	FROM
		ms_city_sort m1,ms_city_sort m2
	WHERE m1.parent_id = m2.id
	AND m1.parent_id > 0
) t2 ON t1.id = t2.id;

3、函数查询

• 查询父级名称

DROP FUNCTION IF EXISTS get_city_parent_name;
CREATE FUNCTION `get_city_parent_name`(pid INT) 
RETURNS varchar(50) CHARSET utf8
begin 
    declare parentName VARCHAR(50) DEFAULT NULL;
    SELECT city_name FROM ms_city_sort WHERE id=pid into parentName;
    return parentName;
end

SELECT t1.*,get_city_parent_name(t1.parent_id) parentName FROM ms_city_sort t1 ;

• 查询根节点子级

DROP FUNCTION IF EXISTS get_root_child;
CREATE FUNCTION `get_root_child`(rootId INT) 
    RETURNS VARCHAR(1000) CHARSET utf8
    BEGIN 
        DECLARE resultIds VARCHAR(500); 
        DECLARE nodeId VARCHAR(500);
        SET resultIds = '%'; 
		SET nodeId = cast(rootId as CHAR);
        WHILE nodeId IS NOT NULL DO 
			SET resultIds = concat(resultIds,',',nodeId);
            SELECT group_concat(id) INTO nodeId 
			FROM ms_city_sort WHERE FIND_IN_SET(parent_id,nodeId)>0;
        END WHILE; 
        RETURN resultIds; 
END  ;

SELECT * FROM ms_city_sort WHERE FIND_IN_SET(id,get_root_child(5)) ORDER BY id ;

====================================================================================================

一、数据场景

1、表结构简介

任何工具类的东西都是为了解决某个场景下的问题，比如Redis缓存系统热点数据，ClickHouse解决海量数据的实时分析，MySQL关系型数据库存储结构化数据。数据的存储则需要设计对应的表结构，清楚的表结构，有助于快速开发业务，和理解系统。表结构的设计通常从下面几个方面考虑：业务场景、设计规范、表结构、字段属性、数据管理。

2、用户场景

例如存储用户基础信息数据，通常都会下面几个相关表结构：用户信息表、单点登录表、状态管理表、支付账户表等。

• 用户信息表

存储用户三要素相关信息：姓名，手机号，身份证，登录密码，邮箱等。

CREATE TABLE `ms_user_center` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `user_name` varchar(20) NOT NULL COMMENT '用户名',
  `real_name` varchar(20) DEFAULT NULL COMMENT '真实姓名',
  `pass_word` varchar(32) NOT NULL COMMENT '密码',
  `phone` varchar(20) NOT NULL COMMENT '手机号',
  `email` varchar(32) DEFAULT NULL COMMENT '邮箱',
  `head_url` varchar(100) DEFAULT NULL COMMENT '用户头像URL',
  `card_id` varchar(32) DEFAULT NULL COMMENT '身份证号',
  `user_sex` int(1) DEFAULT '1' COMMENT '用户性别:0-女,1-男',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

• 单点登录表

用意是在多个业务系统中，用户登录一次就可以访问所有相互信任的业务子系统，是聚合业务平台常用的解决方案。

CREATE TABLE `ms_user_sso` (
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `sso_id` varchar(32) NOT NULL COMMENT '单点信息编号ID',
  `sso_code` varchar(32) NOT NULL COMMENT '单点登录码,唯一核心标识',
  `log_ip` varchar(32) DEFAULT NULL COMMENT '登录IP地址',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户单点登录表';

• 状态管理表

系统用户在使用时候可能出现多个状态，例如账户冻结、密码锁定等，把状态聚合到一起，可以更加方便的管理和验证。

CREATE TABLE `ms_user_status` (
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `account_status` int(1) DEFAULT '1' COMMENT '账户状态：0-冻结,1-未冻结',
  `real_name_status` int(1) DEFAULT '0' COMMENT '实名认证状态：0-未实名,1-已实名',
  `pay_pass_status` int(1) DEFAULT '0' COMMENT '支付密码是否设置：0-未设置,1-设置',
  `wallet_pass_status` int(1) DEFAULT '0' COMMENT '钱包密码是否设置：0-未设置,1-设置',
  `wallet_status` int(1) DEFAULT '1' COMMENT '钱包是否冻结:0-冻结,1-未冻结',
  `email_status` int(1) DEFAULT '0' COMMENT '邮箱状态:0-未激活,1-激活',
  `message_status` int(1) DEFAULT '1' COMMENT '短信提醒开启：0-未开启,1-开启',
  `letter_status` int(1) DEFAULT '1' COMMENT '站内信提醒开启：0-未开启,1-开启',
  `emailmsg_status` int(1) DEFAULT '0' COMMENT '邮件提醒开启：0-未开启,1-开启',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户状态表';

• 支付账户表

用户交易的核心表，存储用户相关的账户资金信息。

CREATE TABLE `ms_user_wallet` (
  `wallet_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '钱包ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `wallet_pwd` varchar(32) DEFAULT NULL COMMENT '钱包密码',
  `total_account` decimal(20,2) DEFAULT '0.00' COMMENT '账户总额',
  `usable_money` decimal(20,2) DEFAULT '0.00' COMMENT '可用余额',
  `freeze_money` decimal(20,2) DEFAULT '0.00' COMMENT '冻结金额',
  `freeze_time` datetime DEFAULT NULL COMMENT '冻结时间',
  `thaw_time` datetime DEFAULT NULL COMMENT '解冻时间',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
  PRIMARY KEY (`wallet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户钱包';

二、设计规范

1、涉及模块

通过上面几个表设计的案例，可以看到表设计关联到数据库的各个方面知识：数据类型，索引，编码，存储引擎等。表设计是一个很大的命题，不过也遵循一个基本规范：三范式。

2、三范式

• 基础概念

一范式

表的列的具有原子性,不可再分解，即列的信息，不能分解,关系型数据库MySQL、Oracle等自动的满足。

二范式

每个事实的数据记录只会出现一次, 不会冗余, 通常设计一个主键来实现。

三范式

要求一个表中不包含已经存在于其它表的非主键信息，例如部门和员工的信息，员工表包含部门表的主键ID，则可以关联获取相关信息，没必要在员工表保存相关信息。

• 优缺点对比

范式化设计

范式化结构设计通常更新快，因为冗余数据较少，表结构轻巧，也更好的写入内存中。但是查询起来涉及到关联，代价非常高，非常损耗查询性能。

反范式化设计

所有的数据都在一张表中，避免关联查询，索引的有效性更高，但是数据的冗余性极高。

• 建议结论

上述的两种设计方式在实际开发中都是不存在的，在实际开发中都是混合使用。比如汇总统计，缓存数据，都会基于反范式化的设计。

三、字段属性

合适的字段类型对于高性能来说非常重要，基本原则如下：简单的类型占用资源更少；在可以正确存储数据的情况下，选最小的数据类型。

1、数据类型选择

• 整数类型

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，根据数据类型范围合理选择即可。

• 实数类型

FLOAT、DOUBLE、DECIMAL，建议资金货币相关类型使用高精度DECIMAL存储，或者把数据成倍扩大为整数，采用BIGINT存储，不过处理相对麻烦。

• 字符类型

CHAR、VARCHAR，长度不确定建议采用VARCHAR存储，不过VARCHAR类型需要额外开销记录字符串长度。CHAR适合存储短字符，或者定长字符串，例如MD5的加密结构。

• 时间类型

DATETIME、TIMESTAMP，DATETIME保存大范围的值，精度秒。TIMESTAMP以时间戳的格式，范围相对较小，效率也相对较高，所以通常情况建议使用。

MySQL的字段类型有很多种，可以根据数据特性选择合适的，这里只描述常见的几种类型。

2、基础用法操作

• 数据类型

修改字段类型

ALTER TABLE ms_user_sso MODIFY state CHAR(1) DEFAULT '0' ;

ALTER TABLE ms_user_sso 
MODIFY state INT(1) DEFAULT '1' COMMENT '状态：0不可用,1可用';

修改名称位置

ALTER TABLE ms_user_sso 
CHANGE log_ip login_ip VARCHAR(32) AFTER update_time ;

• 索引使用

索引类型：主键索引，普通索引，唯一索引，组合索引，全文索引。这里演示普通索引的操作。MySQL的核心模块，后续详说。

添加索引

ALTER TABLE ms_user_wallet ADD INDEX user_id_index(user_id) ;
CREATE INDEX state_index ON ms_user_wallet(state) ;

查看索引

SHOW INDEX FROM ms_user_wallet;

删除索引

DROP INDEX state_index ON ms_user_wallet ;

修改索引

不具有真正意义上的修改，可以把原有的索引删除之后，再次添加索引。

• 外键关联

用处：外键关联的作用保证多个数据表的数据一致性和完整性，建表时先有主表，后有从表；删除数据表，需要先删从表，再删主表。复杂场景不建议使用，实际开发中用的也不多。

添加外键

ALTER TABLE ms_user_wallet 
ADD CONSTRAINT user_id_out_key FOREIGN KEY(user_id) REFERENCES ms_user_center(id) ;

删除外键

ALTER TABLE ms_user_wallet DROP FOREIGN KEY user_id_out_key ;

四、表结构管理

1、查看结构

DESC ms_user_status ;
SHOW CREATE TABLE ms_user_status ;

2、字段结构

• 添加字段

ALTER TABLE ms_user_status 
ADD `delete_time` datetime DEFAULT NULL COMMENT '删除时间' ;

• 删除字段

ALTER TABLE ms_user_status DROP COLUMN delete_time ;

3、修改表名

ALTER TABLE ms_user_center RENAME ms_user_info ;

4、存储引擎

• 存储引擎

SELECT VERSION() ; SHOW ENGINES ;

MySQL 5.6 支持的存储引擎有InnoDB、MyISAM、Memory、Archive、CSV、BLACKHOLE等。一般默认使用InnoDB，支持事务管理。该模块MySQL核心，后续详解。

• 修改引擎

数据量大的场景下，存储引擎修改是一个难度极大的操作，容易会导致表的特性变动，引起各种后续反应，后续会详说。

ALTER TABLE ms_user_sso ENGINE = MyISAM ;

5、修改编码

表字符集默认使用utf8，通用，无乱码风险，汉字3字节，英文1字节，utf8mb4是utf8的超集，有存储4字节例如表情符号时使用。

• 查看编码

SHOW VARIABLES LIKE 'character%';

• 修改编码

ALTER TABLE ms_user_sso DEFAULT CHARACTER SET utf8mb4; 

五、数据管理

1、增删改查

添加数据

INSERT INTO ms_user_sso (
	user_id,sso_id,sso_code,create_time,update_time,login_ip,state
)
VALUES
	(
		'1','SSO7637267','SSO78631273612',
		'2019-12-24 11:56:57','2019-12-24 11:57:01','127.0.0.1','1'
	);

更新数据

UPDATE ms_user_sso SET 
 user_id = '1',sso_id = 'SSO20191224',sso_code = 'SSO20191224',
 create_time = '2019-11-24 11:56:57',update_time = '2019-11-24 11:57:01',
 login_ip = '127.0.0.1',state = '1'
WHERE user_id = '1';

查询数据

一般情况下都是禁止使用 select* 操作。

SELECT user_id,sso_id,sso_code,create_time,update_time,login_ip,state 
FROM ms_user_sso WHERE user_id = '1';

删除数据

DELETE FROM ms_user_sso WHERE user_id = '2' ;

不带where条件，就是删除全部数据。原则上不允许该操作，优化篇会详解。TRUNCATE TABLE也是清空表数据，但是占用的资源相对较少。

2、数据安全

• 不可逆加密

这类加密算法，多用来做数据验证操作，比如常见的密码验证。

SELECT MD5('cicada')='94454b1241ad2cfbd0c44efda1b6b6ba' ;
SELECT SHA('cicada')='0501746a2e4fd34e1d14015fc4d58309585edc7d';
SELECT PASSWORD('smile')='*B4FB95D86DCFC3F33A3852714DC742C77504479D' ;

• 可逆加密

安全性要求高的系统，需要做三级等保，对数据的安全性极高，数据在存储时必须加密入库，取出时候需要解密，这些就需要可逆加密。

SELECT DECODE(ENCODE('123456','key_salt'),'key_salt') ;
SELECT AES_DECRYPT(AES_ENCRYPT('cicada','salt123'),'salt123');

上述数据安全的管理，也可以基于应用系统的服务(代码)层进行处理，相对专业的流程是从数据生成源头处理，规避数据传递过程泄露，造成不必要的风险。

====================================================================================================

 

一、系统封装函数

MySQL 有很多内置的函数，可以快速解决开发中的一些业务需求，大概包括流程控制函数，数值型函数、字符串型函数、日期时间函数、聚合函数等。以下列出了这些分类中常用的函数。

1、控制流程函数

• case...when

根据值判断返回值，类比编程中的IF-ELSE判断。

-- DEMO 01
SELECT CASE DATE_FORMAT(NOW(),'%Y-%m-%d') 
	WHEN '2019-12-29' THEN 'today' 
	WHEN '2019-12-28' THEN 'yesterday' 
	WHEN '2019-12-30' THEN 'tommor' 
	ELSE 'Unknow' END;
-- DEMO 02
SELECT (CASE WHEN 1>0 THEN 'true' ELSE 'false' END) AS result;

• if(expr1,expr2,expr3)

如果表达式 expr1 是TRUE，则 IF()的返回值为expr2; 否则返回值则为 expr3。

SELECT IF(1>2,'1>2','1<2') AS result ; 
SELECT IF(1<2,'yes ','no') AS result ;
SELECT IF(STRCMP('test','test'),'no','yes');

• ifnull(expr1,expr2)

如果表达式 expr1不为NULL，则返回值为expr1;否则返回值为 expr2。

SELECT IFNULL(NULL,'cicada');
SELECT IFNULL(1/1,'no');

2、常用字符串函数

• CHAR_LENGTH()

返回值为字符串的长度 。

SELECT CHAR_LENGTH(' c i c ') ;-- 包含空格
SELECT LENGTH(' S q l ') ;

• CONCAT(str1...)

拼接串联字符串。

SELECT CONCAT('My', 'S', 'ql');
SELECT CONCAT('My', NULL, 'QL'); -- 包含Null 则返回Null
SELECT CONCAT("%", "Java", "%"); -- mybatis中拼接模糊查询

• ELT(N,str1,str2,...)

若N = 1，则返回值为 str1 ，若N = 2，则返回值为 str2 ，以此类推，可以用来转换返回页面的状态。

SELECT ELT(1,'提交','审核中','规则通过') ;
SELECT ELT(2,'提交','审核中','规则通过') ;

• FORMAT(X,D)

格式化数字类型。

SELECT FORMAT(3.1455,2) ; -- 四舍五入保留两位
SELECT TRUNCATE(3.1455,2) ; -- 直接截取两位

• TRIM(str)

清空字符串空格。

SELECT LTRIM('  hel l o ') ;-- 清空左边
SELECT RTRIM('  hel l o ') ;-- 清空右边
SELECT TRIM('  hel l o ') ; -- 清空两边
SELECT REPLACE('M y S Q L',' ','') ; -- 替换掉全部空格

3、数值函数

• FLOOR(X)

返回不大于X的最大整数值 。

SELECT FLOOR(1.23); -- 1
SELECT FLOOR(-1.23); -- -2

• MOD(N,M)

模操作。返回N 被 M除后的余数。

SELECT MOD(29,9); -- 2
SELECT 29 MOD 9; -- 2

• RAND() RAND(N)

返回一个随机浮点值，范围在0到1之间。若已指定一个整数参数 N ，则它被用作种子值，用来产生重复序列。

SELECT RAND(); -- 0.923
SELECT RAND(20) = RAND(20) ; -- TRUE

4、时间日期函数

• ADDDATE(date,INTERVAL expr type)

给指定日期，以指定类型进行运算。

SELECT DATE_ADD('2019-12-29', INTERVAL 3 DAY); -- 2020-01-01

• CURDATE()

将当前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。

SELECT CURDATE(); -- '2019-12-29' 字符串
SELECT CURDATE() + 0; -- 20180725 数字

• DATE(expr)

提取日期或时间日期表达式expr中的日期部分。

SELECT DATE('2019-12-31 01:02:03'); -- '2019-12-31'
SELECT DATE('2019-12-31 01:02:03')+0; -- 20191231

• DATE_FORMAT(date,format)

根据format 字符串进行 date 值的格式化。

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2019-12-29
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日'); -- 2019年12月29日

5、聚合函数

AVG([distinct] expr)  求平均值
COUNT({*|[distinct] } expr)  统计行的数量
MAX([distinct] expr)  求最大值
MIN([distinct] expr)  求最小值
SUM([distinct] expr)  求累加和

二、自定义函数

1、概念简介

函数存储着一系列sql语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。函数注重返回值，而触发器注重执行过程，所以一些语句无法执行。所以函数并不是单纯的sql语句集合。

2、使用方式

create function 函数名([参数列表]) returns 数据类型
begin
 sql语句;
 return 值;
end;

参数列表的格式是： 变量名 数据类型。

• 无参案例

CREATE FUNCTION mysum1 () RETURNS INT RETURN (2+3)*2;
SELECT mysum1 () ;

• 有参函数

表结构

CREATE TABLE t01_user (
	id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY  COMMENT '主键ID',
  user_name varchar(20) DEFAULT NULL COMMENT '用户名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户表';

函数用法

create function get_name(p_id INT) returns VARCHAR(20)
begin 
    declare userName varchar(20);
    select user_name from t01_user where id=p_id into userName;
    return userName;
end;

SELECT get_name(1) ;

3、函数查看

show create function get_name ;

4、删除函数

drop function get_name ;

5、函数注意事项

函数是事先经过编译，才能在服务器环境调用，所以MySQL集群环境需要同步编译；MySQL是多线程环境，所以要保证函数也是线程安全 。

三、触发器

1、触发器简介

触发器是特殊的存储过程，不同的是存储过程要用CALL来调用，而触发器不需要使用CALL。也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MYSQL自动触发调用。

2、创建触发器

触发器语法

CREATE TRIGGER trigger_name trigger_time trigger_event 
ON tbl_name FOR EACH ROW trigger_stmt

• trigger_name：触发器命名 ;
• trigger_time: 触发动作的时间 ;
• trigger_event: 激活触发器的语句类型 ;
• tbl_name: 触发器作用的表明，非临时表 ;
• trigger_stmt：触发程序执行的语句 ;

表数据同步

当向用户表 t01_user 写入数据时，同时向 t02_back 表写入一份备份数据。

-- 用户备份表
CREATE TABLE t02_back (
	id int(11) NOT NULL PRIMARY KEY COMMENT '主键ID',
  user_name varchar(20) DEFAULT NULL COMMENT '用户名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户备份';

-- 触发器程序
DROP TRIGGER IF EXISTS user_back_trigger ;
CREATE TRIGGER user_back_trigger AFTER INSERT ON t01_user FOR EACH ROW
BEGIN
	INSERT INTO t02_back (id,user_name)
VALUES (new.id,new.user_name);
END ;

-- 测试案例
INSERT INTO t01_user (user_name) VALUES ('smile'),('mysql') ;
SELECT * FROM t02_back ;

3、查看触发器

查看触发器是指数据库中已存在的触发器的定义、状态、语法信息等。可以在TRIGGERS表中查看触发器信息。

SELECT * FROM `information_schema`.`TRIGGERS` 
WHERE `TRIGGER_NAME`='user_back_trigger';

4、删除触发器

DROP TRIGGER语句可以删除MYSQL中已经定义的触发器，删除触发器的基本语法。

DROP TRIGGER [schema_name.]trigger_name

5、触发器注意事项

• 触发事件

对于相同的表，相同的事件只能创建一个触发器，比如对表t01_user创建两次AFTER INSERT触发器，就会报错。

• 执行效率

触发器可以减少应用端和数据库的通信次数和业务逻辑，但是基于行触发的逻辑，如果数据集非常大，效率会降低。

• 事务问题

触发器执行和原表的执行语句是否在同一个事务中，取决于触发表的存储引擎是否支持事务。

====================================================================================================

 

一、存储过程

1、概念简介

存储程序是被存储在服务器中的组合SQL语句，经编译创建并保存在数据库中，用户可通过存储过程的名字调用执行。存储过程核心思想就是数据库SQL语言层面的封装与重用性。使用存储过程可以较少应用系统的业务复杂性，但是会增加数据库服务器系统的负荷，所以在使用时需要综合业务考虑。

2、基本语法格式

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

• 案例一：计算消费折扣

-- 创建存储过程
DROP PROCEDURE IF EXISTS p01_discount ;
CREATE PROCEDURE p01_discount(IN consume NUMERIC(5,2),OUT payfee NUMERIC(5,2))
BEGIN
	-- 判断收费方式
	IF(consume>100.00 AND consume<=300.00) THEN
		SET payfee=consume*0.8;
	ELSEIF (consume>300.00) THEN 
		SET payfee=consume*0.6;
	ELSE 
		SET payfee = consume;
	END IF;
	SELECT payfee AS result;
END ;
-- 调用存储过程
CALL p01_discount(100.0,@discount);

• 案例二：While..Do写数据

提供一张数据表

CREATE TABLE `t03_proced` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `temp_name` varchar(20) DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='触发器写数据';

存储程序

根据传入的参数，判断写入t03_proced表的数据条数。

DROP PROCEDURE IF EXISTS p02_batch_add ;
CREATE PROCEDURE p02_batch_add(IN count INT(11))
BEGIN
	DECLARE temp int default 0;
	WHILE temp < count DO
		INSERT INTO t03_proced(temp_name) VALUES ('pro_name');
		SET temp = temp+1 ;
	END WHILE;
END ;

-- 测试：写入10条数据
call p02_batch_add(10);

3、注意事项

• 业务场景

存储过程在实际开发中的应用不是很广泛，通常复杂的业务场景都在应用层面开发，可以更好的管理维护和优化。

• 执行速度

假如在单表数据写入的简单场景下，基于应用程序写入，或者数据库连接的客户端写入，相比存储过程写入的速度就会慢很多，存储过程在很大程度上没有网络通信开销，解析开销，优化器开销等。

二、MySQL视图

1、基本概念

视图本身是一张虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，获取的数据是MySQL从其它表中生成的，视图和表在同一个命名空间。视图查询数据相对安全，视可以隐藏一些数据和结构，只让用户看见权限内的数据，使复杂的查询易于理解和使用。

2、视图用法

现在基于用户和订单管理演示视图的基本用法。

• 基础表结构

CREATE TABLE v01_user (
	id INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  user_name VARCHAR(20) DEFAULT NULL COMMENT '用户名',
	phone VARCHAR(20) DEFAULT NULL COMMENT '手机号',
	pass_word VARCHAR(64) DEFAULT NULL COMMENT '密码',
	card_id VARCHAR(18) DEFAULT NULL COMMENT '身份证ID',
	pay_card VARCHAR(25) DEFAULT NULL COMMENT '卡号',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户表';

CREATE TABLE v02_order (
	id INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
	user_id INT(11) NOT NULL COMMENT '用户ID',
	order_no VARCHAR(32) DEFAULT NULL COMMENT '订单编号',
	good_name VARCHAR(60) DEFAULT NULL COMMENT '商品名称',
	good_id INT(11) DEFAULT NULL COMMENT '商品ID',
	num INT(11) DEFAULT NULL COMMENT '购买数量',
	total_price DECIMAL(10,2) DEFAULT NULL COMMENT '总价格',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '订单表';

• 基本语法

CREATE OR REPLACE VIEW view_name 
AS select_statement

注意事项：表和视图共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视图。

• 用户订单视图

CREATE OR REPLACE 
VIEW user_order_view AS SELECT
	t1.id,t1.user_name,t2.order_no,t2.good_id,
	t2.good_name,t2.num,t2.total_price
FROM v01_user t1
LEFT JOIN v02_order t2 ON t2.user_id = t1.id;

• 视图调用

这里和MySQL的表查询基本一致，可以使用各种查询条件。

SELECT * FROM user_order_view WHERE user_name='Cicada';

• 查看视图

SHOW CREATE VIEW user_order_view ;

• 修改视图

ALTER VIEW view_name AS select_statement ;

• 删除视图

DROP VIEW [IF EXISTS] view_name ;

3、视图更新

在指定条件允许的情况下，可以通过在视图上操作更新，删除，甚至写入数据，进而更新视图所涉及的相关表。

UPDATE user_order_view SET user_name='smile' WHERE id='1';

这里就通过对视图执行更新操作，进而更新v01_user表数据。如果视图定义时使用聚合函数，分组等特殊操作，则无法更新。MySQL不支持在视图上创建触发器。

4、视图实现

• 临时表算法

服务器会把视图查询SQL的数据保存在临时表中，临时表的结构和视图字段结构一致，这样是SQL查询优化中最忌讳的操作，数据量稍微偏大，就会严重影响性能。如果视图无法和原有表产生一对一的映射关系，就会产生临时表，由此也可见视图并不是很简单，甚至是非常复杂的功能。

• 合并算法

服务器基于视图中使用的表执行查询，最后把查询结构合并后返回给客户端。

• 区别方法

执行如下查询语句，可以分析执行的性能参数。

EXPLAIN SELECT * FROM user_order_view ; 

观察查询结果中select_type字段，如果是DERIVED则说明使用临时表。这里SQL执行分析的语法后面优化部分再详解。

5、注意事项

• 性能问题

MySQL并不支持在视图中创建索引，使用视图的时候可能会引发很多查询性能问题，所以建议使用的时候要慎重，多角度审视和测试。

• 特殊用法

基于视图的查询，可以修改部分表结构，只要不是在视图中使用的字段，就不会影响视图的查询。

====================================================================================================

 

一、MySQL逻辑架构

1、逻辑架构图

基于下面的逻辑架构图，可以大致熟悉MySQL各个架构组件之间的协同工作关系。

很经典的C/S架构风格，即客户端/服务端模式。

2、分层描述

• 客户端连接

通常会进行连接池管理，连接用户权限认证，安全管理等操作。

可以通过如下命令查看连接配置信息：SHOW VARIABLES LIKE '%connect%';可以看到最大连接和每个连接占用的内存等相关配置。

• 核心功能

第二层架构封装MySQL一系列核心操作，查询解析、优化、缓存、内置函数、触发器、视图等，跨存储引擎的功能都在这一层实现。

• 存储引擎

MySQL的最底层封装，也是最核心的功能，不同的存储引擎有不同的特点功能，共同点是处理数据的存储和提取。

二、概念简介

1、存储引擎

MySQL数据库存储引擎是数据库底层的架构组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还具有不同的特点功能，以满足不同场景下的业务需求。

2、支持关系

可以通过下面两个命令查看MySQL当前版本，和对存储引擎的支持情况。

SELECT VERSION() ; SHOW ENGINES ;

可以看出本地环境是MySQL5.7，支持如下几种存储引擎：

该版本下默认的存储引擎是：InnoDB,功能最为丰富和强大，支持事务，分布式事务，事务保存点。

三、常见存储引擎

1、InnoDB引擎

(1)、基本描述

InnoDB引擎是MySQL默认的事务型引擎，使用非常广泛，极擅长处理短期事务，具有自动崩溃恢复的特性，在日常开发中，一般都要求使用该引擎。

(2)、架构图解

• InnoDB架构图

该图片来自MySQL官网文档。

整体分三层：内存结构，Cache，磁盘结构。

• 内存结构

内存结构又包括四大组件

Buffer Pool：缓冲池：是主内存中的一个区域，在InnoDB访问表和索引数据时会在其中进行高速缓存，大量减少磁盘IO操作，提升效率。

Change Buffer：写缓冲区：避免每次增删改都进行IO操作，提升性能。

Adaptive Hash Index：自适应哈希索引：使用索引关键字的前缀构建哈希索引，提升查询速度。

Log Buffer：日志缓冲区：保存要写入磁盘上的日志文件的数据，缓冲区的内容定期刷新到磁盘。

• 磁盘结构

Tables：数据表的物理结构。

Indexes：索引的物理结构。

Tablespaces：表空间，数据存储区域。

Data Dictionary：数据字典，存储元数据信息的表，例如表的描述，结构，索引等。

Doublewrite Buffer：位于系统表空间的一个存储区域，InnoDB在BufferPool中刷新页面时，会将数据页写入该缓冲区后才会写入磁盘。

Redo Log：记录DML操作的日志，用来崩溃后的数据恢复。

Undo Logs：数据更改前的快照，可以用来回滚数据。

(3)、特点描述

• 支持事务

事务内在执行一组SQL语句时，要么全部成功，要么全部失败。

• 支持分布式事务

分布式事务指即使不同操作位于不同的服务应用上，仍然需要保证事务的特性。常见场景：订单和库存在不同的服务中，但却能保持一致性。

• 支持行级锁

加锁时锁定一行数据的锁机制就是行级别锁定(row-level)。MySQL5.7版本中只有InnoDB引擎支持。锁定的粒度小，自然支持的并发就高，锁定的机制也随之变的复杂。

• 支持MVCC

多版本并发控制，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

• 支持聚簇索引

是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。

2、MyISAM引擎

(1)、基础描述

MySQL5.1和之前版本的默认存储引擎，不支持事务和行级锁，自然崩溃之后不能自动恢复。

(2)、特点描述

• 锁表机制

对整张表加锁，不针对行加锁，读数据加共享锁，写数据加排他锁。

• 全文索引

支持全文索引，一种基于分词创建的索引，可以支持复杂的检索查询。

3、其他引擎

在MySQL的体系中，最常使用的就是InnoDB和MyISAM引擎，其他多样的存储引擎可以根据业务需求再去熟悉。

絮叨一句：人生苦短，编程语言更是五马六路，这点令人烦躁，所以学习的时候要挑重点，什么是重点，使用最多的就是重点内容。

四、存储引擎选择

在公司的开发规范中，一般硬性要求使用InnoDB引擎，除非有怪癖的业务InnoDB无法支持。

====================================================================================================

 

一、锁概念简介

1、基础描述

锁机制核心功能是用来协调多个会话中多线程并发访问相同资源时，资源的占用问题。锁机制是一个非常大的模块，贯彻MySQL的几大核心难点模块：索引，锁机制，事务。这里是基于MySQL5.6演示的几种典型场景，对面MySQL这几块问题时，有分析流程和思路是比较关键的。在MySQL中常见这些锁概念：共享读锁、排它写锁 ; 表锁、行锁、间隙锁。

2、存储引擎和锁

• MyISAM引擎：基于读写两种模式，支持表级锁 ;
• InnoDB引擎：支持行级别读写锁，跨行的间隙锁,InnoDB也支持表锁 ;

3、锁操作API

• LOCK TABLE name [READ,WRITE] ;加表锁
• UNLOCK TABLES ; 释放标所

二、MyISAM锁机制

1、基础描述

MySQL的表级锁有两种模式：共享读锁（Read-Lock）和排它写锁（Write-Lock）。针对MyISAM表的读操作，不会阻塞其他线程对同一表的读请求，但阻塞对同一表的写请求；针对MyISAM表的写操作，会阻塞其他线程对同一表的读和写操作；MyISAM引擎读写操作之间，以及写与写操作之间是串行化。当一次会话线程获取表的写锁后，只有当前持有锁的会话线程可以对表进行操作。其它线程的读、写操作都会等待，直到锁被释放为止。

2、验证案例

基于上面的表锁机制特点，使用下面两个案例验证。

• 基础表结构

CREATE TABLE `dc_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用户名',
  `tell_phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户表';
CREATE TABLE `dc_user_info` (
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `city` varchar(20) DEFAULT NULL COMMENT '城市',
  `country` varchar(20) DEFAULT NULL COMMENT '国家',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户信息表';

• 共享读锁

会话窗口一

-- 1、加读锁
LOCK TABLE dc_user READ ;
-- 2、当前会话查询，OK
SELECT * FROM dc_user ;
-- 4、当前会话写入，Error
INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');
-- 6、查询其他表,Error
SELECT * FROM dc_user_info ;
-- 7、释放锁
UNLOCK TABLES ;

会话窗口二

-- 3、其他会话查询，OK
SELECT * FROM dc_user ;
-- 5、其他会话写入，Error
INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');
-- 8、再次执行写入读取，OK
INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');
SELECT * FROM dc_user ;

这里验证表锁的共享读机制。

• 排它写锁

这里验证表锁的排它写机制。

• 查询锁争用

通过下面语句查看配置，

show status like 'table%';

Table_locks_waited的值越大，锁争用情况越严重，效率则越低下。

3、并发写入问题

针对排它写锁的测试案例再说明：在一定条件下，MyISAM表也支持查询和插入操作的并发执行。通过配置系统变量concurrent_insert的值[0,1,2]，可以实现并发写入。

• concurrent_insert=0，禁止并发写入;
• concurrent_insert=1，默认配置AUTO，在MyISAM表中没有空洞，即表的中间没有被删除的行，例如[1,2,3],删除2之后[1,,3]，则允许在读表的同时，另一个线程从表尾写入记录。
• concurrent_insert=2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
  在下面的例子中，session_1获得了一个表的READ LOCAL锁，该线程可以对表进行查询操作，但不能对表进行更新操作；其他的线程（session_2），虽然不能对表进行删除和更新操作，但却可以对该表进行并发插入操作，这里假设该表中间不存在空洞。

4、优先级问题

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。但是当一个读操作和写操作同时请求，写数据会优先获得锁，这一机制可以通过配置修改，指定配置参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
通过执行命令SET

• LOW_PRIORITY_UPDATES=1，使该会话的写操作优先级降低。
• 指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

5、表锁应用

数据一致性校验问题，比如销售量+剩余库存=货品总量，在校验时就要在一次会话中同时锁住订单表和库存表，免得在读取订单表的时候，库存表被修改，导致数据误差出现。

三、InnoDB锁机制

1、事务基础概念

• 事务概念

事务是指作为单个逻辑工作单元执行的一系列操作（SQL语句）。这些操作要么全部成功，要么全部不成功。

• 事务特性ACID

原子性(Atomicity)：事务中的多个操作要么都成功要么都失败

一致性(consistency)：事务的执行的前后数据的完整性保持一致

隔离性(isolation)：事务执行的过程中,不应该受到其他事务的干扰

持久性(durability)：事务一旦结束,数据就持久到数据库

• 事务问题

脏读：一个事务读到另一个事务没有提交的数据

不可重复读：一个事务前后多次读取相同数据，数据内容不一致，update场景问题

虚读(幻读)：一个事务前后多次读取，数据总量不一致，insert场景问题

• 隔离级别

read uncommitted:事务可以读取另一个未提交事务的数据。

read committed：事务要等另一个事务提交后才能读取数据,解决脏读。

repeatable read：在开始读取数据时,事务开启,不再允许修改操作,解决：脏读、不可重复读。

serializable：最高事务隔离级别，事务串行化顺序执行，解决脏读、不可重复读、幻读。但是效率低下，耗数据库性能。

2、锁机制描述

InnoDB与MyISAM的最大不同有两点：一是支持事务TRANSACTION,二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来新问题：并发，死锁等。

• 共享锁：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上共享锁，则事务T可以读A但不能修改A，其他事务只能再对A加共享锁，而不能加写锁，直到T释放A上的共享锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

• 排他锁：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的资源的共享读锁和排他锁。若事务T对数据对象A加上写锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的写锁。

3、验证案例

• 基础表结构

CREATE TABLE `dc_user_in01` (
  `id` int(11) DEFAULT NULL COMMENT 'id',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用户名',
  `tell_phone` varchar(20) DEFAULT NULL COMMENT '手机号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

CREATE TABLE `dc_user_in02` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用户名',
  `tell_phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用户表';

注意结构：表dc_user_in01主键没有索引。表dc_user_in02主键有索引，但是都使用INNODB存储引擎，下面验证案例会有不同。

• 无索引结构表

会话窗口一

-- 1、关闭自动提交
SET AUTOCOMMIT = 0 ;
-- 2、查询id=1，OK
SELECT * FROM dc_user_in01 WHERE id=1 ;
-- 3、添加写锁失败
SELECT * FROM dc_user_in01 
WHERE id=1 FOR UPDATE ;
-- 4、恢复事务提交
SET AUTOCOMMIT = 1 ;

会话窗口二

-- 1、关闭自动提交
SET AUTOCOMMIT = 0 ;
-- 2、查询id=2，OK
SELECT * FROM dc_user_in01 WHERE id=2 ;
-- 3、写入失败(等待)
INSERT INTO dc_user_in01 (id,user_name,tell_phone) 
VALUES (3,'lock01','13267788998');
-- 4、写锁失败(等待)
SELECT * FROM dc_user_in01 
WHERE id=2 FOR UPDATE ;
-- 5、恢复事务提交
SET AUTOCOMMIT=1 ;

• 索引结构表

会话窗口一

-- 1、关闭自动提交
SET AUTOCOMMIT = 0 ;
-- 2、查询id=1，OK
SELECT * FROM dc_user_in02 WHERE id=1 ;
-- 3、添加写锁成功
SELECT * FROM dc_user_in02 WHERE id=1 FOR UPDATE ;
-- 执行到这里，再执行窗口2
-- 4、恢复事务提交
SET AUTOCOMMIT = 1 ;

会话窗口二

-- 1、关闭自动提交
SET AUTOCOMMIT = 0 ;
-- 2、查询id=2，OK
SELECT * FROM dc_user_in02 WHERE id=2 ;
-- 3、查询id=1，OK，加读锁
SELECT * FROM dc_user_in02 WHERE id=1 ;
-- 4、写入成功
INSERT INTO dc_user_in02 (user_name,tell_phone) VALUES ('lock01','13267788998');
-- 5、加写锁成功，id为2的
SELECT * FROM dc_user_in02 
WHERE id=2 FOR UPDATE ;
-- 6、加写锁失败(等待)，占用id为1的
SELECT * FROM dc_user_in02 WHERE id=1 FOR UPDATE ;
-- 7、恢复事务提交
SET AUTOCOMMIT=1 ;

• 索引失效问题

这里要注意索引是否被使用问题，在很多查询中，可能因为种种原因导致索引不执行。

explain SELECT * FROM dc_user_in02 WHERE id=1 ;

• 查询锁争用

show status like 'innodb_row_lock%';

Innodb_row_lock_waits和Innodb_row_lock_time_avg的值越大，锁争用情况越严重，效率则越低下。

4、Next-Key锁

• 官方文档说明

为了防止幻读，InnoDB使用了一种名为Next-Key锁定的算法，它将记录锁和间隙锁定结合在一起即：InnoDB在执行行级锁的时候，会用这种方式-扫描索引记录，会在符合索引条件的记录上加共享锁或者独占锁。

[Next-Key]=[Record-lock]+[Gap-lock]

如果说上面的几种锁机制给人的感觉是昏天暗地，那个这个Next-Key算法就会叫人怀疑人生。

• 验证案例

这里主要验证Gap-lock间隙锁的存在机制。

CREATE TABLE `dc_gap` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `id_index` int(11) NOT NULL COMMENT 'index',
  PRIMARY KEY (`id`),
  KEY `id_index` (`id_index`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='间隙表';
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('1', '2');
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('3', '4');
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('6', '7');
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('8', '7');
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('9', '9');

会话窗口一

-- 1、开始事务
START TRANSACTION ;
-- 3、锁定id_index=7的两条记录
SELECT * FROM dc_gap 
WHERE id_index=7 FOR UPDATE ;
-- 9、提交
COMMIT ;

会话窗口二

-- 2、开始事务
START TRANSACTION ;
-- 4、写入等待,id_index=6
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('4', '6');
-- 5、写入等待,id_index=4
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('4', '4');
-- 6、写入成功,id_index=3
INSERT INTO `dc_gap` (`id`, `id_index`) 
VALUES ('4', '3');
-- 7、写入等待,id_index=9
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('7', '9');
-- 8、写入成功,id_index=10
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('7', '10');

7向上到4有间隙，7向下到9有间隙，所以间隙锁定[4,9]，且包含首尾值。

5、Dead-Lock锁

• 基础描述

两个或者多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致死循环现象，也就是死锁。

• 验证案例

会话窗口一

-- 1、开启事务
START TRANSACTION ;
-- 3、占用id=6的资源
SELECT * FROM dc_gap WHERE id=6 FOR UPDATE ;
-- 5、占用id=9的资源等待
SELECT * FROM dc_gap WHERE id=9 FOR UPDATE ;

会话窗口二

-- 2、开启事务
START TRANSACTION ;
-- 4、占用id=9的资源
SELECT * FROM dc_gap WHERE id=9 FOR UPDATE ;
-- 6、占用id=6的资源抛死锁
SELECT * FROM dc_gap WHERE id=6 FOR UPDATE ;

SQL 复制 全屏

补刀一句：数据库实现各种死锁检测机制，或者死锁超时等待结束，InnoDB存储引擎在检测到死锁后，会立即返回错误，不然两个事务会隔空对望，一眼万年。

注意：死锁在事务型业务中，是无法绝对避免的，锁定资源少，粒度细，尽量避免该情况出现。

====================================================================================================

 

一、MySQL用户

1、基础描述

在数据库的使用过程中，用户作为访问数据库的鉴权因素，起到非常重要的作用，安装MySQL时会自动生成一个root用户，作为数据库管理员，拥有所有权限。在多用户的应用场景下，可能需要给不同的用户分配不同的权限，用来提升系统的稳定性，比如常见：报表库只提供读权限，或者开放给第三方的库，也只提供可读用户。

2、用户管理

基本描述

MySQL将用户信息存储在系统数据库mysql的user表中。根据用户名密码和客户端主机来定义帐户。

用户密码：基本验证操作 ；

客户端IP：类似黑白名单的限制，支持通配符表达式 ；

SELECT t.`Host`,t.`User`,t.authentication_string 
FROM mysql.`user` t ;

添加用户

可以对user表进行增删改查一系列操作，进而添加用户，不同的用户就会涉及到不同的操作权限，这就是另外一个问题：用户的权限管理。

这里添加一个user01用户，作为权限模块的测试用户，权限先给和root用户一样的权限。

INSERT INTO `mysql`.`user`(`Host`, `User`, `authentication_string`) 
VALUES ('%', 'user01', '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9');
FLUSH PRIVILEGES ;

注意：这里host赋值%，就是代表所有IP可以通过user01用户连接MySQL服务器。修改系统表之后需要执行一次刷新操作。

二、访问鉴权

1、权限控制

MySQL数据库系统中，权限分配涉及到如下几张核心表：user、db、table_pric、columns_priv。在权限认证时候遵守该顺序逐步验证。

• 权限表描述

user表：存储用户和用户全局权限，也是MySQL鉴权流程首当其冲的表 ；

db表：保存数据库权限 ；

tables_priv表：存储表权限，面向一个特定表中的和其中所有列；

columns_priv表：存储列权限，面向一个特定表中的单一列；

注意：权限表的管理，不止上述描述的几个，但是人生苦短，把这几个理顺了，其他表也应该可以顺藤摸瓜找过去。

• user表结构

这里处理包含用户的连接信息，还有很多权限点认证。

CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  ... //此处省略很多
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';

注意：注释说明,用户和全局权限管理。

• 权限点描述

对一般开发流程而言，知道如下几个权限点管理即可，道理同上，到需要使用的时候再去熟悉，不差时。

SELECT: 查询表中的记录 ；
INSERT: 向表中写入新数据 ；
UPDATE: 更新表数据；
DELETE: 删除表的记录 ；
CREATE: 创建数据库和表 ；
DROP: 删除数据库和表 ；

絮叨一句：工作几年之后，你最喜欢写的程序逻辑是什么？反正我就想写写简单的增删改查操作。

2、鉴权流程

首先验证user表，其次db表，然后table表，再然后column表；

基于范围逐级缩小，权限不断的细化。

• 测试user01用户权限

权限查询

首先查看user01用户的查询权限。此时该用户是具有select权限的。

SELECT t.`Host`,t.`User`,t.Select_priv 
FROM mysql.`user` t WHERE t.`User`='user01' ;

基于客户端工具，使用user01用户登录MySQL服务器，可以正常使用查询权限。

禁用查询权限点

UPDATE `mysql`.`user` SET `Select_priv` = 'N' WHERE `User` = 'user01';
FLUSH PRIVILEGES ;

权限验证

使用user01登录的客户端，不能查询表数据，说明权限管理起到作用了。

三、日志记录系统

1、日志配置查看

基于该语句查看日志相关配置，例如日志地址，是否开启关闭，日志缓存大小，相关配置信息。

SHOW GLOBAL VARIABLES LIKE '%log%';

正常停止MySQL服务器，可以通过my.cnf更改相关配置。Linux下配置文件一般在/etc/my.cnf中。

2、InnoDB事务日志

InnoDB的事务日志包括Redo-log和Undo-log两种，这个日志的描述在MySQL5.7官方文档的InnoDB存储引擎-磁盘结构模块下面。

• Redo-log

重做日志：基于磁盘的数据结构，记录事务性操作崩溃期间没有正常写入库的数据，重做：处理日志中没有正常写入的数据记录，完成数据入库。

• Undo-log

回滚日志：提供回滚操作和多个行版本控制MVCC，事务提交时，会记录Undo-log，当事务失败或执行回滚，就需要通过Undo-log进行回滚。思维跳跃一层：当写入数据时，日志记录应该是新增标记，要执行的记录是删除这条数据操作，删除数据，过程应该相反，要记录的是删除的这条数据的写入操作。

2、错误日志

在MySQL的配置文件中，log_error是强制开启的，且没有关闭开关，用来记录mysql服务器每次启动和关闭时的详细信息，以及运行过程中出现的的严重警告信息和错误信息等，Linux下配置如下:

log-error=/var/log/mysqld.log

错误日志包含mysqld启动和关闭时间的记录。它还包含诊断消息，例如错误，警告和注释，它们在服务器启动和关闭期间以及服务器运行期间发生。例如，如果mysqld注意到需要自动检查或修复表，它将向错误日志中写入一条消息。

3、通用查询日志

General-Query-Log，所有连接和语句被记录到日志文件。当想知道客户端发生了错误并想确切地知道该客户端发送给mysqld的语句时，该日志可能非常有用。mysqld按照它接收的顺序记录语句到查询日志。可能与执行的顺序不同。这与更新日志和二进制日志不同，它们在查询执行后，但是任何一个锁释放之前记录日志。MySQL5.6版本下是默认关闭的。

4、二进制日志

Binary-Log，主要用来记录数据库更改，例如表创建操作或表数据更改的事件，对于主从复制流程，主库服务器上的二进制日志发送到从库服务器，从服务器执行这些事件，保证主从服务器的数据同步。

log_bin  OFF

MySQL5.6版本下，该日志默认是关闭的。

5、慢查询日志

Slow-Query-Log慢查询日志主要记录mysql中执行的时间比较长的sql，默认的阈值是10秒，执行时间超过10秒的sql语句就会被慢查询日志所记录，慢查询日志的配置可以在mysql的配置文件中配置，默认不开启。

SHOW GLOBAL VARIABLES LIKE '%long_query_time%';

开启慢查询日志，通过对该时间的调整，可以记录性能差的SQL语句，进行分析优化，对系统性能的提升十分有帮助。

====================================================================================================

 

一、服务器性能简介

1、性能定义

服务器性能优化是一项非常艰巨的任务，当然也是很难处理的问题，在写这篇文章的时候，特意请教下运维大佬，硬件工程师，数据库管理，单从自己的实际开发经验来看，看待这个问题的角度起码是不全面的。

补刀一句：在公司靠谱少撕逼，工程师这个群体是很好交朋友的，互相学习一起进步，升职加薪他不好吗？

服务性能定义：完成一个任务或者处理一次接口请求所需要的时间，这个时间是指响应完成时间，即请求发出，到页面响应回显结束，这是看待性能问题的基本逻辑。

2、分析性能

服务的基本过程一般如下图，这是一张最简单的前后端分离，加一台数据库存储的流程，但是想要说明一个复杂的逻辑。

从页面请求，到获取完整的响应结果，这个过程每个环节都可能导致性能问题，抛开网络，硬件，服务器，MySQL存储这些核心客观因素，单是下面这行代码就可以秒掉很多人的努力。

Thread.sleep(10000); // 仿佛整个世界都安静了。

影响性能的因素很多，一般说性能优化会从下面几个方面考虑：

• 网络传输，比如私有云和公有云的交互，接口传输内容过大；
• 应用服务，接口设计是否最简，没有逻辑问题，架构设计是否合理；
• 存储服务，MySQL的查询写入，表设计是否合理，连接池配置是否合理；
• 硬件设施，CPU和内存的利用是否在合理区间，缓存是否合理；

这些问题每个处理起来都是非常耗费时间，且对人员的要求相对较高，不说一定要到达专家水平，起码性能问题出现时候，基本的意识要有。

二、MySQL执行机制

基于上述流程图，MySQL性能分析主要从下面几个方面切入，基本方向就不会偏。

1、连接池配置

查看默认最大连接数配置：

SHOW VARIABLES LIKE 'max_connections';

最小连接数是连接池一直保持的会话连接，这个值相对好处理许多，评估服务在正常状态下需要多少会话连接。

最大连接数服务器允许的最大连接数值，这个参数的设计就比较飘逸，需要对高并发业务有把控，且要分析SQL性能，和CPU利用率（基本上是70%-85%），想获得这一组参数，可是相当不容易，需要测试精细，配合运维进行服务监控记录，开发不断优化，可能要分库分表，或者集群，拆服务分布式化等一系列操作，最终才能得到合理处理逻辑，当然这样费心对待的都是核心业务，一般的业务也就是经验上把控。

2、SQL执行过程

MySQL解析器识别SQL的基本语法，生成语法树，然后优化器输出SQL可执行计划，非常复杂的流程。

• 客户端发送请求到MySQL服务器；
• 如果执行查询，会检查缓存是否命中；
• 服务端进行SQL解析，预处理，最后优化器生成执行计划；
• 根据执行计划调用存储引擎API执行；
• 返回客户端处理结果；

补刀一句：这也就是为什么现在接口提倡最简化设计，或者接口拆分，分步执行，不要问这样会不会多次请求，给网络造成压力，这都5G时代了。

3、逻辑总结

总结一句话：分析是否存在MySQL服务的性能问题，需要考量是不是服务配置问题，或者SQL编译过程问题，导致大量等待时间，还是SQL执行有问题，或者查询数据量过大导致执行过程漫长。

补刀一句：MySQL性能问题的基本原因很简单，数据量不断变大，服务器承载不住。作为开发，这是面对数据库优化的根本原因。

三、执行语句分析

1、基本描述

上面几个方面都是在说明面对服务性能问题时，意识上要清楚的边界，作为开发实际上要面对两个直接问题：表设计，SQL语句编写，大部分的开发都被这两个问题毒打过。

2、表结构设计

表设计：表设计关系到数据库的各个方面知识：数据类型选择，索引结构，编码，存储引擎等。是一个很大的命题，不过也遵循一个基本规范：三范式。

规范的表结构，合适的数据类型可以降低资源的占用，索引可以提高查询效率，存储引擎更是关系到事务方面的问题。

表的结构的逻辑清晰，是后续查询和写入的基本条件，结构过大，会出现很多索引，分表结构多，带来很多连接查询，同样会把开发感觉按在地上。这就涉及到一个玄学：开发要根据经验和因素，权衡表结构设计。

补刀一句：如果你去问3.5年的开发，最想写什么业务，他肯定会说单表的增删改查，为什么？因为这类任务是不会排期给他的。

3、数据更新

假设在表结构符合逻辑的情况下，数据更新(增删改)操作一般情况下不会出现较大问题，遵循几个基本原则。

• 数据量大的写入，执行批量操作，占用连接少；
• 删除和更新要考虑锁定的粒度，不要导致大范围锁定；
• 经常执行删除操作，要考虑内存碎片问题；
• 批量操作可以基于应用层面使用多线程处理；

4、数据查询

查询是开发中最常面对的问题，针对查询的规范也是特别多，确实查询也是最容易出错的环节。但是影响查询的因素很多，可能很多情况下查询只是背黑锅：

• 表设计规范，减少各种关联，子查询；
• 列类型规范，数据值规范，Null和空处理；
• 索引结构和使用规范，对查询性能影响最大；
• 存储引擎选择合适，直接影响锁定粒度大小；
• 外键关联导致表强行耦合，最讨厌的一个功能；

SQL在执行的时候，如果性能很差，还需要基于MySQL慢查询机制进行分析，查看是否出现磁盘IO，临时表，索引失效等各种问题。

四、模块总结

上述的描述可能感觉有点乱，但是整体上看，就分为下面三个模块：

• 应用服务流程化分析，判断瓶颈出现环节；
• 熟悉MySQL基本机制，分析等待和执行时间；
• MySQL的表结构设计和SQL执行优化；

这篇文章只是笼统描述一下服务性能的问题，重点还是想陈述一个基本逻辑：具备服务性能问题分析的意识，且意识的边界相对全面，不要只盯着某个方面思考。

补刀一句：因为文章的分类是MySQL模块，所以重点的描述也在MySQL层面。实际情况中，任何层面都可能导致性能问题。

====================================================================================================

 

一、索引简介

1、基本概念

首先要明确索引是什么：索引是一种数据结构，数据结构是计算机存储、组织数据的方式，是指相互之间存在一种或多种特定关系的数据元素的集合，例如：链表，堆栈，队列，二叉树等等。

其次要清楚索引的作用：索引可以使存储引擎快速找到数据记录，这是最基本的作用，索引是对查询速度最关键的影响，良好的索引设计可以使查询的效率有质的飞越。

索引的使用：如果查询语句使用所有，MySQL会在索引的数据结构上查询，如果查询到，就返回包含该索引的数据行。

2、索引的优点

• 唯一或者主键索引,保证列数据的唯一性
• 减少数据扫描量，快速查询数据；
• 数据有序的索引，可以将随机IO变成顺序IO；
• 有效的索引查询，可以避免排序和临时表；

3、索引分类

索引的种类非常多，如何分类取决多个场景和不同的角度，常见的划分如下：

• 产生作用：主键索引，普通索引，非空索引，全文索引；
• 覆盖字段：单列索引，组合索引；
• 数据结构：B-Tree索引，哈希索引，R-Tree索引；

注意：索引的实现是在存储引擎层面，相同的索引在不同的存储引擎中，其实现方式可能都是不一样的。

二、索引用法详解

1、不同索引特点

普通索引

基本的索引，没有任何使用限制，主要用来加速数据查询。适合经常出现在查询条件或排序条件中的数据列。

主键索引

特殊的唯一索引，不允许有空值，在建表的时候指定主键，就会创建主键索引，MySQL中最核心的索引，大量的业务数据都是基于主键查询。

唯一索引

普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须是唯一性的。

全文索引

用于全文搜索，通过建立全文索引，基于分词的查询模式，可以极大的提升检索效率。

组合索引

创建的索引覆盖两个或者两个以上的列，适应组合查询的场景，也常用于要素验证的业务，例如判断用户身份ID，手机号，邮箱，是否为同一个用户。

2、管理索引语法

基础用户表

CREATE TABLE user_base (
	id INT (11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
	user_name VARCHAR (20) NOT NULL COMMENT '用户名',
	phone VARCHAR (20) NOT NULL COMMENT '手机号',
	email VARCHAR (32) DEFAULT NULL COMMENT '邮箱',
	card_id VARCHAR (32) DEFAULT NULL COMMENT '身份编号',
	create_time datetime DEFAULT NULL COMMENT '创建时间',
	state INT (1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
	PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '用户基础表';

创建单列索引

CREATE INDEX card_id_index ON user_base(card_id);

修改添加索引

ALTER TABLE user_base ADD INDEX state_index(state) ;

创建组合索引

CREATE INDEX bind_index ON user_base(phone,card_id);

删除索引

DROP INDEX card_id_index ON user_base ;

修改索引

MySQL不支持真正修改索引的语法规范，可以通过删除旧索引，添加新索引的方式进行操作。

3、查询索引

分析MySQL查询，多数情况下用来分析执行语句的SQL中是否使用索引，是否产生临时表等性能相关问题。

基础用法

EXPLAIN SELECT * FROM user_base WHERE id='1';

参数说明

• id：相同，按table列由上至下顺序执行，不同，如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行；
• select_type：表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询；

simple：简单select查询，查询中不包含子查询或者
primary：查询中若包含复杂的子部分，最外层查询则被标记为primary
subquery：select或where中包含子查询
derived：from中包含的子查询被标记为derived衍生，mysql会递归执行这些子查询，且生成临时表
union：第二个select出现在union后，标记为union
union-result：从union表获取结果的select

• table：指当前执行计划中的数据表；
• type：说明的是查询使用了哪种类型，下面从好到差排序；

system-const：对查询的某部分进行优化并转换成一个常量时，会使用该类型
eq_ref：常见于主键或唯一索引扫描，表中只有一条记录与之匹配
ref：非唯一性索引扫描，返回匹配某个单独值的所有行
index：遍历索引结构，索引文件通常比数据文件小
all：遍历全表进行查询

• possible_keys：在查询中可能使用到的索引；
• key：在查询中实际使用到的索引；
• key_len：查询中索引字段的最大可能长度，在不损失精确性的情况下，长度越短越好；
• ref：表示本行被操作的对象的参照对象，可能是一个常量用const表示，也可能是其他表的key指向的对象；
• rows：预估找到符合要求的记录所需要扫描的行数，扫描越少越好；
• extra：执行计划中，一些十分重要的信息；

Using-Filesort：查询使用文件排序，最差的执行计划
Using-Temporary：临时表保存中间结果，比文件排序稍微强点
Using-Index：查询操作中使用了覆盖索引
Using-Where：表明使用了where过滤条件
Using-Join-Buffer:表明使用了连接缓存
Impossible-Where：表示where条件false,不能过滤元素
Distinct：优化distinct找到第一匹配的数据后即停止找同样值的动作
Select-Tables-Optimized-Away：不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

三、B-Tree索引结构

1、B-Tree索引简介

MySQL官方比较推荐的索引结构类型，在实际的数据库开发中，基于MySQL中的表结构，大部分使用的都是B-Three索引结构，即二叉树的结构。可以加快数据的访问速度，存储引擎不再需要进行全表扫描来获取数据，数据分布在各个索引节点上，B-Tree索引结构如图：

该结构是典型的二叉树结构，特点：数据值按照顺序存储的，每个叶子节点到根部的距离是相同的，注意这里描述的是索引结构图。

实际存储结构上，数据顺序存储，每个节点包含索引值，索引指向的数据行的值，指向子页的指针，指向叶子页的指针，这样才能把索引和数据结构组织起来，结构如图：

这样完整描述B-Tree索引的数据特点，基于树搜索提升效率，减少扫描数据，数据被顺序的组织起来，按照索引值顺序排列。

2、搜索规则

索引的根本作用，减少扫描的数据量，提升查询效率，基于B-Tree索引的结构的查询规则基本如下：

• 查询从索引的根节点开始，逐步搜索；
• 根节点的槽中存放指向子节点的指针，指向下层；
• 根据节点页的值和查询值比较，判断是否符合条件；
• 不断执行上述逻辑，直到查询完成；

注意：必须要强调一点，查询必须是在执行索引的基础上，才是该逻辑，正常的开发中多分析一下查询语句，有时候可能只是自己感觉查询索引是执行的，实际可能是失效的。

3、索引查询失效

好的索引设计十分重要，但是查询的时候很可能因为触发各种索引失效机制，导致SQL语句不执行索引搜索，严重损失性能，所以基于业务下数据查询特点，设计相对好用的索引结构，是十分关键的，这里涉及很多场景问题，后续再详细记录。

四、索引导致的问题

索引有时候并不是最好的解决方式，当数据量庞大的时候，索引也会占据庞大的存储空间，这里提供一个业务测试场景，仅供参数：单表三个字符类型字段，两个字段使用索引结构，存储数据在700W量级，在A和B两个数据库，A数据库有索引结构，B数据库没有索引，A库占用的空间是B库的1.6倍，写入千万数据的速度也比B数据库慢9分钟。

这里只想说明一点：索引虽然好，使用妥当才能发挥作用。

====================================================================================================

 

一、高性能索引

1、查询性能问题

在MySQL使用的过程中，所谓的性能问题，在大部分的场景下都是指查询的性能，导致查询缓慢的根本原因是数据量的不断变大，解决查询性能的最常见手段是：针对查询的业务场景，设计合理的索引结构。

2、索引使用原则

索引的使用并不是越多越好，而是针对业务下的查询场景，不断的改进和优化，例如电商系统中用户订单的场景，假设存在如下表结构：

CREATE TABLE `ds_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

CREATE TABLE `ds_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `order_no` varchar(60) NOT NULL COMMENT '订单号',
  `product_name` varchar(50) DEFAULT NULL COMMENT '产品名称',
  `number` int(11) DEFAULT '1' COMMENT '个数',
  `unit_price` decimal(10,2) DEFAULT '0.00' COMMENT '单价',
  `total_price` decimal(10,2) DEFAULT '0.00' COMMENT '总价',
  `order_state` int(2) DEFAULT '1' COMMENT '1待支付，2已支付,3已发货,4已签收',
  `order_remark` varchar(50) DEFAULT NULL COMMENT '订单备注',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

用户和订单管理表，在电商的业务中很常见，可以通过对该业务分析，看看常用的索引结构：

用户方：

• 基于用户的查询，多数是基于用户ID(user_id)；
• 基于订单号(order_no)，查看物流的信息；

运营方：

• 基于时间段的流水明细(create_time)或排序;
• 基于订单状态的筛选(order_state)和统计；
• 基于产品(product_name)的数据统计分析；

这样一个流程分析走下来，即可以在开发初期，确定哪些结构是查询必须用到的，预先做好索引结构，避免数据量庞大到影响性能时再去考虑使用索引。

有些时候会考虑放弃一些查询条件，例如基于产品名称的数据统计，走定时任务的方式，用来缓解表的查询压力，处理的方式是多样的。

优秀的索引设计，都是建立在对业务数据的理解上，考虑业务数据的查询方式，提高查询效率。

二、索引创建

1、单列索引

单列索引，即索引建立在表的一个字段上，一个表可以有多个单列索引，使用起来相对比较简单：

CREATE INDEX user_id_index ON ds_order(user_id) USING BTREE;

主键索引，或者上述的user_id_index都是单列索引。

业务场景：基于用户自己对订单查询，和管理系统，订单和用户的关联查询，所以订单表的user_id需要一个索引。

2、组合索引

组合索引包含两个或两个以上的列，组合索引相比单列索引复杂很多，如何建立组合索引，和业务关联度非常高，在使用组合索引时，还需要考虑查询条件的顺序。

CREATE INDEX state_create_time_index ON `ds_order`(`create_time`,`order_state`);

如上就是组合索引，实际包含的是2个索引 (create_time) (create_time,order_state)，这样查询就涉及到最左前缀的原则，必须按照顺序来查询，这里下面详说。

业务场景：首先单说这里组合索引，在业务开发中，常见订单状态的统计，基于统计结果做运营分析，另外就是在运营系统中，基于创建时间段的筛选条件是默认存在的，避免全部数据实时扫描；一些其他的常见查询也都是条件加时间段的查询模式。

3、前缀索引

如果需要加索引的列是很长的字符串，那么索引会变的庞大臃肿，起到的效果可能并不是很明显。这时候可以截取列的前面一部分，创建索引，节省空间，这样可能会出现索引的选择性下降，即基于前缀索引查询出的相似数据可能很多：

ALTER TABLE ds_order ADD KEY (order_no(30)) ;

这里由于订单号太长，所以选择前面30位作为前缀索引，用作订单号的查询，当然这里涉及到一个非常经典的业务场景，订单号机制。

业务场景：前缀索引一个典型的应用场景就是处理订单号，一个看似很长的订单号，其实包含的信息非常多：

• 时间点：就是订单生成的时间，年月日时分秒；
• 标识位：即一个唯一的UID，保证订全单号唯一；
• 埋点一：在很多业务中，在订单号记录产品类目；
• 埋点二：通常会标识产品属性，例如颜色，口味等；
• 错位符：防止订单号被分析，会随机一段错位符号；

如此一段分析下来，实际订单号是非常长的，所以需要引入前缀索引机制，前缀索引期望使用的索引长度可以筛选整个列的基数，例如上面的订单号：

• 大部分业务基于时间节点筛选足够，即索引长度14位；
• 如果是并发业务，很多时间节点相同，则索引长度是时间点+标识位；

注意：如果业务允许的情况下，一般要求前缀索引的长度有唯一性，例如上面的时间和标示位。

4、其他索引

例如全文索引等，这些用到的场景不多，如果数据庞大，又需要检索等，通常会选择强大的搜索中间件来处理。显式唯一索引，这种也会在程序上做规避，避免不友好的异常被抛出。

三、索引查询

如何创建最优的索引，是一件不容易的事情，同样在查询的时候，是否使用索引也是一件难度极大的事情，经验之谈：多数是性能问题暴露的时候，才会回头审视查询的SQL语句，针对性能问题，做相应的查询优化。

1、单列查询

这里直接查询主键索引，MySQL的主键一般选择自增，所以速度非常快。

EXPLAIN SELECT * FROM ds_order WHERE id=2;
EXPLAIN SELECT * FROM ds_order WHERE id=1+1;
EXPLAIN SELECT * FROM ds_order WHERE id+1=1;

这里，id=2,id=1+1，MySQL都可以自动解析，但是id+1是在索引列上执行运算，直接导致主键索引失效。这里有一个基本策略，如果非要在单列索引上做操作，可以将该逻辑放在程序中，到MySQL层面，SQL语句越干净利落越好。

2、前缀索引查询

前缀索引的查询，可以基于Like对特定长度筛选，或者全订单号查询。

EXPLAIN SELECT * FROM ds_order WHERE order_no LIKE '202008011314158723628732871625%';
EXPLAIN SELECT * FROM ds_order WHERE order_no='20200801131415872362873287162572367';

3、组合索引查询

查询最麻烦的就是组合索引，或者说查询条件组合起来，都使用了索引：

EXPLAIN SELECT * FROM ds_order 
WHERE create_time>'2020-08-01 00:00:00' AND order_state='1';

上述基于组合索引中列的顺序，使用了组合索引：state_create_time_index。

EXPLAIN SELECT * FROM ds_order WHERE create_time>'2020-08-01 00:00:00';

上述只使用create_time列，也同样使用了索引结构。

EXPLAIN SELECT * FROM ds_order WHERE order_state='1';

上述如果只使用order_state条件，则结果显示全表扫描。

EXPLAIN SELECT * FROM ds_order 
WHERE create_time>'2020-08-01 00:00:00' AND order_no LIKE '20200801%';

上述则基于组合索引的create_time列和单列索引order_no保证查询条件都使用了索引。

通过上面几个查询案例，索引组合索引使用的注意事项如下：

• 组合索引必须按索引最左列开始查询；
• 不能跳过组合字段查询,这样无法使用索引；

四、索引其他说明

1、索引的优点

• 基于注解或唯一索引保证数据库表中数据的唯一性；
• 索引通过减少扫描表的行数提高查询的效率；

2、索引的缺点

• 创建索引和维护索引，会耗费空间和实际；
• 查询以外的操作增删改等，都需要动态维护索引；

3、索引使用总结

索引机制在MySQL中真的非常复杂，非专业的DBA(就是指开发人员)，基本要熟练常见的索引结构，待过两年所谓的大厂，每个版本开发涉及的核心表SQL都是有专业DBA验收，复杂的查询都是提交需求，DBA直接输出查询SQL，当然在一般公司是没有DBA，需要开发在开发的过程中不断的思考，逐步优化，这需要对业务数据有一定的敏感度，对核心接口有执行监控，当发现稍微出现耗时情况，就可以不断优化，这个积累是个枯燥和进步的过程。

====================================================================================================