一、Mysql基本功

1.1 基础

数据库：存储数据的仓库，软件专门用来存储数据 常用数据库软件：Mysql、Sql Server、Oracle、Postgresql、H2、Sqlite、Db2等等 OLD-SQL NO-SQL NEW-SQL 目前市场最为流行：Mysql SQL：结构化查询语言 操作数据库的语言，目前SQL99标准 目前SQL的分类： 1.DDL：数据定义语言 关键字：create drop alter 2.DML：数据操作语言 关键字：insert update delete 3.DQL：数据查询语言 关键字：select 4.DCL：数据控制语言 关键字：grant revoke 5.TPL：数据事务语言 关键字：begin transaction、commit 、rollback 6.CCL：指针控制语言 关键字：cursor 游标

传统行业对数据库的定义： 存储数据并可以实现业务处理(存储过程和函数) 互联网行业对数据库的定义： 只用来存储数据，不做其他操作（SQL优化） 数据库设计规范： 《阿里巴巴Java开发规范》 《58到家数据库军规》

1.2 SQL语句入门

操作数据库的语言：sql

  1. -- SQL入门
  2. -- DDL
  3. -- create
  4. create table t_stu2008(
  5. id int ,
  6. name varchar(20),
  7. ctime date,
  8. primary key(id)
  9. );
  10. -- alter 
  11. alter table t_stu2008 add sex varchar(8);
  12. -- drop 
  13. drop table t_stu2008;
  14. -- DML
  15. -- insert
  16. select now();
  17. insert into t_stu2008(id,name,sex,ctime) values(1,'富豪','男',now());
  18. insert into t_stu2008(id,name,sex,ctime) values
  19. (2,'张兵','男',now()),(3,'张兵2','男',now());
  20. -- update
  21. update t_stu2008 set name='张大兵' where id=3;
  22. -- delete
  23. delete from t_stu2008 where id=3;
  25. -- DQL
  26. -- select
  27. select * from t_stu2008;
  28. -- 各种常用条件
  29. select distinct * from t_stu2008 where id between 1 and 3;
  30. -- 一张表有重复数据，需要写sql语句实现重复数据保留1条
  31. -- TPL
  32. start transaction

一张表有重复数据，需要写sql语句实现重复数据保留1条

name sex age
a 男 18
c 女 19
a 男 18
a 男 18
b 男 21

实现下面的结果：

name sex age
a 男 18
c 女 19
b 男 21

1.3 复杂查询

1.聚合函数 配合分组 max min avg sum count 2.联合查询 自然查询（笛卡尔积） 等值查询 内连接查询 左外连接查询 右外连接查询 全连接查询（Mysql不支持） 3.子查询 4.合并查询结果 union union all

  1. -- SQL复杂查询
  2. -- 自然查询 笛卡尔积查询
  3. select * from sys_user,sys_role;
  4. -- 等值查询 -内连接 结果一致 性能很差
  5. select * from sys_user su,sys_role sr where su.user_id=sr.role_id;
  6. -- 内连接
  7. select * from sys_user su
  8. inner join sys_role sr on su.user_id=sr.role_id and su.user_id>1;
  9. -- 左外连接
  10. select * from sys_user su
  11. left join sys_role sr on su.user_id=sr.role_id;
  12. -- 右外连接
  13. select * from sys_user su
  14. right join sys_role sr on su.user_id=sr.role_id;
  16. -- 子查询 将一个查询语句的结果作为另一个查询的条件
  17. select * from sys_dept where dept_id in (select distinct dept_id from sys_user);
  18. -- 合并查询结果集union union all
  19. select user_id from sys_user union
  20. select role_id from sys_role;

1.4 数据库设计

1.4.1 数据库设计步骤

1.需求分析
分析用户的需求，包括数据、功能和性能需求
2.概念结构设计
主要采用E-R模型进行设计，包括画E-R图
3.逻辑结构设计
通过将E-R图转换成表，实现从E-R模型到关系模型的转换
4.物理结构设计
主要是为所设计的数据库选择合适的存储结构和存取路径
5.数据库实施
包括编程、测试和试运行
6.数据库的运行和维护
系统的运行与数据库的日常维护

1.4.2 数据库设计范式

仅供参考
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
image.png
第一范式(确保每列保持原子性)
所有字段值都是不可分解的原子值
第二范式(确保表中的每列都和主键相关)
一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
第三范式(确保每列都和主键列直接相关,而不是间接相关)
每一列数据都和主键直接相关，而不能间接相关
BC范式(BCFN)
Boyce-Codd范式的缩写,排除了任何属性对候选键的传递依赖与部分依赖
第四范式4NF(相互独立的多值情况)
限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖。
第五范式 5NF(处理相互依赖的多值情况)
表必须可以分解为较小的表，除非那些表在逻辑上拥有与原始表相同的主键

1.4.3 数据库设计工具

PDMan
http://www.pdman.cn/#/

二、数据库进阶

2.1 视图

视图：View 对查询语句的封装 作用： 1.隐藏内部实现细节 2.封装复杂的查询语句 操作视图就跟操作表一样 语法格式： 新建：create view 视图名称 as 查询sql语句 使用：select * from 视图名称 删除：drop view 视图名称 视图就是封装了查询语句，所以表中的数据发生了改变，那么视图也可以获取最新的数据

  1. -- 视图：封装查询语句
  2. create view v_s2008 as 
  3. select user_id from sys_user union select role_id from sys_role;
  4. -- 使用视图
  5. select * from v_s2008;
  6. -- 删除视图
  7. drop view v_s2008;

2.2 触发器

触发器：满足一定的条件下，自动执行指定的SQL语句的结构体 触发器的类型： 1.行级触发器（使用） 2.表级触发器 触发器的条件： 1.时间： after | before 2.操作方式： insert | update | delete 6种触发条件 语法格式： 新建： create trigger 触发器名称 after|before insert|uodate|delete on 表名 for each row begin 触发执行的sql语句; end； 删除： drop trigger 触发器名称; 满足条件 自动执行 获取原表操作的数据： NEW:新变化的数据对象 OLD:老的数据对象

  1. -- 触发器：满足条件自动执行指定SQL语句
  2. delimiter $
  3. create trigger tri_s2008
  4. after insert on t_stu2008
  5. for each row
  6. begin
  7.     insert into t_stulog2008(info,ctime) values(concat('新增了学生信息：',NEW.id),sysdate());
  8. end$
  9. -- 测试触发器
  10. delimiter ;
  11. insert into t_stu2008(id,name,sex,ctime) values(3,'李奔','男',now());
  12. select * from t_stulog2008;
  13. -- 删除触发器
  14. drop trigger tri_s2008;

2.3 函数

函数：实现特定功能的代码块 系统自带： 1.聚合函数：max min count sum avg 2.数学函数：abs pow round rand floor 3.字符串函数：concat、substr、reverse、length 4.日期函数：now 、sysdate、date_format、year、to_day、month、datediff（计算日期相差天数） 5.其他函数：ifnull 自定义函数： 语法格式： 新建： create function 函数名称(参数名 数据类型,……) returns 返回值类型 begin 实现功能的语句块 return 返回值 end; 删除： drop function 函数名称 函数必须有返回值

  1. -- 函数
  2. select abs(-99),round(3.56),rand(),floor(4.3),pow(10,3);
  3. -- 字符串
  4. select concat('紫阳','走了'),substr('abcdef',2),length('223'),reverse('asd');
  5. select now(),sysdate(),to_days(now()),year(now()),month(now()),date_format(now(),'%Y-%m-%d');
  7. select ifnull(info,'') from t_stulog2008;
  9. insert into t_stulog2008(info,ctime) values(null,sysdate());
  10. -- 自定义函数
  11. delimiter $
  12. create function f_add2008(n1 int,n2 int) returns int
  13. begin
  14.     return n1+n2;
  15. end$
  16. delimiter ;
  17. -- 使用函数
  18. select f_add2008(1,1);
  19. -- 删除函数
  20. drop function f_add2008;
  21. -- 实现年龄的计算函数  数据库只记录生日

2.4 存储过程

存储过程：实现特定功能的代码块 没有返回值 参数： 1.输入参数 只读 只能读取 2.输出参数 只写 只能赋值 返回 3.输入输出参数 读写 语法格式： 新建： delimiter $ create procedure 存储过程名称(参数类型 参数名称 数据类型,……) begin 实现的代码块 end$ 删除： drop procedure 存储过程名称 参数类型： in out inout

  1. -- 存储过程：跟函数一样 只是没有返回值
  2. delimiter $
  3. create procedure p_s2008(in n1 int,in n2 int,out n3 int)
  4. begin
  5.     set n3= n1+n2;
  6. end$
  8. delimiter ;
  9. -- 调用存储过程
  10. set @n =0;
  11. call p_s2008(101,110,@n);
  12. select @n;
  13. -- 删除存储过程
  14. drop procedure p_s2008;
  15. -- 学习和研究sql中的分支和循环

2.5 索引

索引：针对表的查询速度的优化的一种结构体，如果把表比喻成一本书，那么索引就是书中的目录 快速定位数据 索引提高了查询的速度，但是影响了新增和删除的性能 索引只适合快速查询，不适合频繁新增或删除的表 索引的分类： 1.主键索引 只要字段是主键，那么默认就是索引

2.唯一索引 值唯一

3.普通索引

4.复合索引（联合索引）

索引的最佳左匹配原则，对于复合索引来说，查询的时候会从左往右匹配

覆盖索引：SQL语句只需要通过索引就能查询出想要的结果，不必通过二级索引查询主键索引再去查询数据

索引的底层：B+Tree(Mysql 的InnoDB存储引擎)

语法格式： 新建索引： create [unique] index 索引名称 on 表名(字段) 一张表的索引的数量最好不超过16个

  1. -- 创建索引
  2. create index is2008 on t_stu2008(name);
  3. -- 查询索引是否生效  type的结果
  4. explain select * from t_stu2008 where name='张兵';

image.png

explain的结果分析： id

id相同，执行顺序由上至下

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同，同时存在 id相同的可以认为是一组，同一组中从上往下执行，所有组中id大的优先执行

type

type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种，从好到差依次是

system > const > eq_ref > ref > range > index > all

system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）

all Full Table Scan 将遍历全表以找到匹配的行

possible_keys 和 key

possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

Extra

Using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。

Using temporary

使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

Using index

表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

Using join buffer

表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些

三、SQL优化

3.1 SQL执行顺序

Mysql中一个查询语句的执行顺序 a.select b.distinct c.from d.join e.on f.where g.group by h.having i.order by j.limit Mysql中的执行顺序： 1.from 笛卡尔积计算 生成虚拟表v1 2.on 过滤数据 再次生成虚拟表v2 3.join 添加外部数据 v3 4.where 条件过滤 v4 5.group by 分组 6.聚合函数 avg max min sum count 7.having 条件过滤 8.select 查询需要的字段 9.distinct 去重重复结果 10.order by 排序 11.limit 分页 请解释on、where、having的区别？

Mysql是如何执行sql语句的： 客户端(Java程序 JDBC)—->连接器(DBMS)——>查询缓存——>分析器——>优化器——->执行器——>结果 把能过滤掉大量数据的条件进行前置处理，比如考虑on做筛选

3.2 优化步骤

定位： 发现需要优化的sql语句 常用的方式： 1.慢查询 Mysql默认支持慢查询 就是可以设置将执行过慢的sql语句记录到日志中 image.png 2.Druid SQL监控 3.第三方工具 Innotop、mysqltuner.pl 4.Java代码 aop实现查询语句超时记录

5.云数据库 阿里云、腾讯云 sql检测报告 优化建议 分析： 需要明白慢的原因 1.并发量 2.数据量 3.最短路径 4.索引（重点）—复合索引 最左前缀原则 5.计算 6.冗余 结合项目，对号入座 解决： 1.并发量— 1.程序中数据库连接池 控制有效连接，提高连接的复用率 2.扩容-Mysql服务器 —搭建Mysql集群 3.数据库读写分离 查询多 —Mycat 写库（主库）—->InnoDB , 读库（从库）——>Myisma 二进制日志

2.数据量 1.数据分片 垂直分片 水平分片 Mycat— 分片算法

3.最短路径 1.分析表关系 关系型数据库 最短关系 2.梳理sql语句

4.索引（重点）—复合索引 最左前缀原则 1.索引是否合适 之前是否有过相关索引 冗余 2.索引的数量 3.索引生效 4.索引 复合索引的字段顺序 最左匹配原则 索引在那些情况下会失效：自己验证 5.覆盖索引

5.计算 1.避免在查询字段上做运算 2.避免条件查询字段做运算

6.冗余

  1.         1.sql语句存在冗余  需要结合业务逻辑

2.保证网络环境

项目经验：冷静 程序无中生有 1.定位问题—debug、打印日志 2.分析问题—找到原因 3.解决问题—针对原因 解决问题 需求—->反问—->分析—->实现思路——>

3.3 优化总结

1、你必须选择记录条数最少的表作为基础表

from 是从前往后检索的，所以要最少记录的表放在最前面。 2、采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。同时在链接的表中能过滤的就应该先进行过滤。

where是从后往前检索，所以能过滤最多数据的条件应放到最后。

3、SELECT子句中避免使用 ‘*’

4、尽量多使用COMMIT

5、计算记录条数时候，第一快：count(索引列)，第二快：cout(*)

6、用WHERE子句替换HAVING子句

7、通过内部函数提高SQL效率

8、使用表的别名(Alias)

9、用EXISTS替代IN

10、用NOT EXISTS替代NOT IN

11、用表连接替换EXISTS

12、用索引提高效率

13、尽量避免在索引列上使用计算，

包括在SELECT后面 WHERE后面等任何地方，因为在索引列上计算会导致索引失效。

14、避免在索引列上使用NOT

在索引列使用not会导致索引失效。

15、用>=替代>

16、用UNION替换OR (适用于索引列)

17、用IN来替换OR

18、避免在索引列上使用IS NULL和IS NOT NULL

19、总是使用索引的第一个列

20、尽量用UNION-ALL 替换UNION ( 如果有可能的话)

21、ORDER BY 子句只在两种严格的条件下使用索引.

22、避免改变索引列的类型

23、需要当心的WHERE子句

24、避免使用耗费资源的操作（如DISTINCT,UNION,MINUS,INTERSECT,ORDER BY等）

3.4 阿里巴巴Java开发规范

《阿里巴巴Java开发规范》+58到家 SQL军规 一、基础规范

  • 表存储引擎必须使用InnoDB
  • 表字符集默认使用utf8，必要时候使用utf8mb4

解读：

  1. 通用，无乱码风险，汉字3字节，英文1字节
  2. utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它
  • 禁止使用存储过程，视图，触发器，Event

解读：

  1. 对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层
  2. 调试，排错，迁移都比较困难，扩展性较差
  • 禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径
  • 禁止在线上环境做数据库压力测试
  • 测试，开发，线上数据库环境必须隔离

二、命名规范

  • 库名，表名，列名必须用小写，采用下划线分隔

解读：abc，Abc，ABC都是给自己埋坑

  • 库名，表名，列名必须见名知义，长度不要超过32字符

解读：tmp，wushan谁TM知道这些库是干嘛的

  • 库备份必须以bak为前缀，以日期为后缀
  • 从库必须以-s为后缀
  • 备库必须以-ss为后缀

三、表设计规范

  • 单实例表个数必须控制在2000个以内
  • 单表分表个数必须控制在1024个以内
  • 表必须有主键，推荐使用UNSIGNED整数为主键

潜在坑：删除无主键的表，如果是row模式的主从架构，从库会挂住

  • 禁止使用外键，如果要保证完整性，应由应用程式实现

解读：外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈

  • 建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据

四、列设计规范

  • 根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节
  • 根据业务区分使用char/varchar

解读：

  1. 字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高
  2. 字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间
  • 根据业务区分使用datetime/timestamp
    解读：前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime
  • 必须把字段定义为NOT NULL并设默认值

解读：

  1. NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化
  2. NULL需要更多的存储空间
  3. NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑
  • 使用INT UNSIGNED存储IPv4，不要用char(15)
  • 使用varchar(20)存储手机号，不要使用整数

解读：

  1. 牵扯到国家代号，可能出现+/-/()等字符，例如+86
  2. 手机号不会用来做数学运算
  3. varchar可以模糊查询，例如like ‘138%’
  • 使用TINYINT来代替ENUM

解读：ENUM增加新值要进行DDL操作 五、索引规范

  • 唯一索引使用uniq_[字段名]来命名
  • 非唯一索引使用idx_[字段名]来命名
  • 单张表索引数量建议控制在5个以内

解读：

  1. 互联网高并发业务，太多索引会影响写性能
  2. 生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选择不到最优索引
  3. 异常复杂的查询需求，可以选择ES等更为适合的方式存储
  • 组合索引字段数不建议超过5个

解读：如果5个字段还不能极大缩小row范围，八成是设计有问题

  • 不建议在频繁更新的字段上建立索引
  • 非必要不要进行JOIN查询，如果要进行JOIN查询，被JOIN的字段必须类型相同，并建立索引

解读：踩过因为JOIN字段类型不一致，而导致全表扫描的坑么？

  • 理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)

六、SQL规范

  • 禁止使用select *，只获取必要字段

解读：

  1. select *会增加cpu/io/内存/带宽的消耗
  2. 指定字段能有效利用索引覆盖
  3. 指定字段查询，在表结构变更时，能保证对应用程序无影响
  • insert必须指定字段，禁止使用insert into T values()

解读：指定字段插入，在表结构变更时，能保证对应用程序无影响

  • 隐式类型转换会使索引失效，导致全表扫描
  • 禁止在where条件列使用函数或者表达式

解读：导致不能命中索引，全表扫描

  • 禁止负向查询以及%开头的模糊查询

解读：导致不能命中索引，全表扫描

  • 禁止大表JOIN和子查询
  • 同一个字段上的OR必须改写成IN，IN的值必须少于50个
  • 应用程序必须捕获SQL异常

解读：方便定位线上问题