1. MySQL基础
1.1 基础知识
数据库(DataBase,DB):存储数据的仓库,数据是有组织的进行存储。
数据库管理系统(DataBase Management System,DBMS):操纵和管理数据库的大型软件,如MySQL。
SQL(Structured Query Language,SQL):操作关系型数据库的编程语言,定义了一套操作关系型数据库统一标准。
通过net start mysql80
命令和net stop mysql80
命令来开启和关闭MySQL服务,也可在系统服务中找MySQL80开启和关闭。
可通过MySQL自带的Command对数据库进行操作,或CMD(mysql -u root -p
打开本机的MySQL),但要在环境变量中配置环境(将安装时bin文件夹路径添加到Path中)。
1.2 SQL
1.2.1 DDL(Data Definition Language)
DDL:数据定义语言,用来定义数据库对象(数据库,表,字段)。
- 数据库的创建、查询、使用和删除:
(1) 创建
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
(2) 查询所有数据库
show databases;
(3) 查询当前数据库
select database(); #在使用数据中才能调用这一命令
(4) 使用
use 数据库名;
(5) 删除
drop database [if exists] 数据库名;
- 基本表的创建,字段的添加、修改和删除
(1) 基本表的创建
create table 表名(
字段1 类型1 [comment 注释1],
字段2 类型2 [comment 注释2],
...
字段n 类型n [comment 注释n] #最后一个字段后不加逗号
)[comment 表注释];
(2) 查询当前数据库所有基本表
show tables;
(3) 查询某基本表的结构
desc 表名;
(4) 修改基本表的表名
alter table 表名 rename to 新表名;
(5) 查询某基本表的详细信息(创建语句、引擎、字符集、排序规则等)
show create table 表名;
(6) 在基本表中添加某字段
alter table 表名 add 字段名 类型 [comment 注释] [约束];
(7) 修改基本表中某字段的数据类型
alter table 表名 modify 字段名 新数据类型;
(8) 修改基本表中某字段名和数据类型
alter table 表名 change 旧字段名 新字段名 类型 [comment 注释] [约束];
(9) 删除基本表中某字段
alter table 表名 drop 字段名;
(10) 删除基本表
drop table [if exists] 表名;
(11) 删除基本表并重新创建(之前表存储的数据会没有)
truncate table 表名;
- 数据类型
(1) 数值类型
tinyint 1byte #可以指定signed或unsigned,即有符号数还是无符号数
smallint 2bytes
mediumint 3bytes
int/integer 4bytes #也可以integer(p)来指定整数的位数
bigint 8bytes
float 4bytes
double 8bytes #也可以指定精度p和小数点后位数s
decimal(p,s) 精确数值 #精度p,小数点后位数s。如decimal(5,2)是一个小数点前有3位数,小数点后有2位数的数字。
(2) 字符串类型
char 0-255bytes 定长字符串,无论数据多大都消耗一样的存储空间,性能较varchar好
varchar 0-65535bytes 变长字符串,会根据具体的大小计算存储空间,性能较char差
blob 0-65535bytes 二进制形式的长文本数据,还有tiny、medium、long等为前缀的blob
text 0-65535bytes 长文本数据
(3) 日期类型
date YYYY-MM-DD 日期值
time HH:MM:SS 时间值
year YYYY 年份值
datetime YYYY-MM-DD HH:MM:SS 混合日期时间值
1.2.2 DML(Data Manipulation Language)
DML:数据操作语言,用来对数据库表中的数据进行增删改。
- 添加数据
(1) 给指定字段添加数据
insert into 表名(字段1,字段2,..) values(值1,值2,...);
(2) 给全部字段添加数据
insert into 表名 values(值1,值2,...);
(3) 批量添加数据
insert into 表名(字段1,字段2,..) values(值1,值2,...),(值1,值2,...)...;
insert into 表名 values(值1,值2,...),(值1,值2,...)...;
- 修改数据
update 表名 set 字段名1=值1,字段名2=值2,... [where 条件]; # 如果没有条件则修改整个基本表的全部数据
- 删除数据
delete from 表名 [where 条件]; #如果不指定条件则删除全部数据
- 条件
1.2.3 DQL(Data Query Language)
DQL:数据查询语言,用来查询数据库中表的记录。执行顺序:from->where->group by->select->order by->limit
(1) 基本查询
select [distinct] 字段1 [as 别名1],字段2 [as 别名2],... from 表名; #distinct去重,as起别名(as可省略)
(2) 条件查询
select 字段列表 from 表名 where 条件列表; #多个条件可用逻辑运算符进行关联
(3) 分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]; #having可以使用聚合函数,而where不能
(4) 排序查询
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2,...; #asc:升序,默认;desc:降序
(5) 分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数; #起始索引=(查询页码-1)*每页显示记录数
聚合函数:将一列数据作为整体,进行纵向计算。如select max(字段列表) from 表名;
,且null值不参与所有聚合函数的运算。
1.2.4 DCL(Data Control Language)
DCL:数据控制语言,用来创建数据库用户、控制数据库的访问权限。
- 用户管理
(1) 查询用户
use mysql; select * from user;
(2) 创建用户
create user '用户名'@'主机名' identified by '密码'; #没有任何权限,主机名:localhost则通过当前主机访问;%则任意主机可以访问
(3) 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'; #mysql_native_password为加密方式
(4) 删除用户
drop user '用户名'@'主机名';
- 权限控制
(1) 查询权限
show grants for '用户名'@'主机名';
(2) 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; #权限列表如all,select,alter,delete,update,insert等
(3) 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
1.3 函数
- 字符串函数
(1) 字符串拼接 concat(S1,S2,...Sn)
(2) 转小写 lower(str)
(3) 转大写 upper(str)
(4) 左填充 lpad(str,n,pad) #用pad对字符串str进行填充,直到str达到n个字符串长度
(5) 右填充 rpad(str,n,pad)
(6) 去前后空格 trim(str)
(7) 截取子串 substring(str,start,len) #索引是从1开始,不是0了
- 数值函数
(1) 向上取整 ceil(x)
(2) 向下取整 floor(x)
(3) 取模 mod(x,y)
(4) 返回0-1随机数 rand()
(5) 四舍五入 round(x,y) #保留y位小数
- 日期函数
(1) 当前日期 curdate() #返回数据类型date
(2) 当前时间 curtime() #返回数据类型time
(3) 当前日期和时间 now() #返回数据类型datetime
(4) 获取date的年份 year(date)
(5) 获取date的月份 month(date)
(6) 获取date的日期 day(date)
(7) 求某间隔后的时间 date_add(date,interval 时间间隔 时间类型)
(8) 返回相隔天数 datediff(date1,date2) # date1-date2
- 流程函数
(1) if(value,t,f) #如果value位true,则返回t,否则返回f
(2) ifnull(value1,value2) #如果value1不为null,则返回value1,否则返回value2
(3) case when [val1] then [res1]...else [default] end #如果val1位true,则返回res1,...否则返回default默认值,可以有多个when..then
(4) case [expr] when [val1] then [res1]...else [default] end #如果expr的值等于val1,则返回res1,...否则返回default默认值
1.4 约束
约束是作用与表中字段上的规则,用于限制存储在表中的数据。为了保证数据库中数据的正确性、有效性和完整性。一个字段可以有多个约束,用空格隔开即可,如要求某字段为主键且自增,则可用primary key auto_increment
来进行约束。
外键约束:外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
(1) 直接在从表创建的时候建立外键约束(必须写在末尾)
[constraint] [外键名称] foreign key(从表外键字段名) references 主表(主表对应字段名);
(2) 主表和从表都创建完成,再建立外键约束
alter table 从表名 add constraint 外键名称 foreign key(从表外键字段名) references 主表(主表对应字段名);
(3) 删除外键
alter table 从表名 drop foreign key 外键名称;
(4) 当主表中删除和更新时,从表的行为(默认是no action)
no action/restrict 从主表中删除/更新对应记录时,若该记录有对应外键,则不允许删除/更新。
cascade 从主表中删除/更新对应记录时,若该记录有对应外键,则同样删除/更新外键在从表中的记录。
set null 从主表中删除/更新对应记录时,若该记录有对应外键,则将从表中对应记录的外键值设为null(该外键允许取null)。
set default 主表有变更时,从表将外键设置为一个默认的值(Innodb不支持)。
在设置外键约束的最后加上 on update 行为 on delete 行为; 即可设置外键的更新和删除时的行为。
1.5 多表查询
- 多表关系
(1)一对多(多对一):如部门与员工之间的关系,一个部门可有多个员工,需要在多的一方建立外键。
(2)多对多:如学生与课程之间的关系,需建立中间表,至少包含两个外键,分别关联双方的主键。
(3)一对一:如用户和用户详情的关系,常用于单表拆分,在任意一方加入外键,关联另一方的主键,且设置外键为unique。 - 内连接(表1与表2的交集)
(1) 隐式内连接
select 字段列表 from 表1,表2 where 条件; #可以对较长表名起别名,一旦起了别名则原来的表名就不能用了
(2) 显式内连接
select 字段列表 from 表1 [inner] join 表2 on 条件;
- 外连接(除了交集还要某表特有的)
(1) 左外连接(表1的全部数据,包含交集部分数据)
select 字段列表 from 表1 left [outer] join 表2 on 条件;
(2) 右外连接(表2的全部数据,包含交集部分数据)
select 字段列表 from 表1 right [outer] join 表2 on 条件;
- 自连接(自己和自己的连接)
select 字段列表 from 表A 别名A join 表A 别名B on 条件; #既可以是内连接(显式和隐式都可以),也可是外连接(左外和右外都行)
- 联合查询(将多次查询结果联合)
select 字段列表 from 表A ...
union [all] #有all代表两次查询结果合并,没all则合并之后还要去重
select 字段列表 from 表B ...; #两次查询字段列表长度和对应类型应该保持一致
- 子查询(嵌套查询)
select 字段列表 from 表1 where 某字段 操作符(select 字段列表 from 表2);
(1) 标量子查询(子查询结果为单个值):常用操作符 = <> > >= < <=
(2) 列子查询(子查询结果为一列):常用操作符 in, not in, any(有任意一个满足即可), some, all(必须全部都满足)
(3) 行子查询(子查询结果为一行):可以将where后的多个字段用括号括起来再判断,如where (字段1,字段2) = (行子查询)
(4) 表子查询(子查询结果为多行多列):常用操作符 in
1.6 事务
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。如银行转账
- 事务的操作
(1) 查看事务提交方式 select @@autocommit; #返回值为1则为自动提交,返回值为0则为手动提交 (2) 设置事务提交方式 set @@autocommit=0; #设置事务为手动提交 (3) 开启事务 start transaction; 或 begin; #这种方式可以在自动提交情况下开启事务,同样用commit提交事务才会使数据库更新 (4) 提交事务 commit; #必须提交事务,前提是事务没有出现异常,才能更改数据库(否则只是修改了临时存着,但没有提交到数据库中) (5) 回滚事务 rollback; #若事务执行出错,则调用回滚事务恢复之前的数据
- 事务的四大特性(ACID)
(1)原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
(2)一致性(Consistency):事务完成时,必要使所有的数据都保持一致状态。
(3)隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
(4)持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。 - 事务并发问题
(1)脏读
:一个事务读到另外一个事务还没有提交的数据。
(2)不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同。
(3)幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”。 - 事务隔离级别(用于解决事务并发问题)
事务隔离级别越高,安全性越高,但性能越差。
(1) 查看事务隔离级别
select @@transaction_isolation;
(2) 设置事务隔离级别
set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};
2. MySQL进阶
2.1 存储引擎
- MySQL体系结构
(1)连接层:最上层是一些客户端和链接服务,主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
(2)服务层:第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化,部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如过程、函数等。
(3)引擎层:存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。
(4)存储层:主要是将数据存储在文件系统之上,并完成与存储引擎的交互。
-
存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的,而不是基于库的,因此存储引擎也可被称为表类型。(1) 查询当前数据库支持的引擎 show engines; (2) 创建表时指定引擎 create table 表名(字段信息) engine = 存储引擎名;
-
常用的存储引擎
(1)InnoDB(默认):一种兼顾高可靠性和高性能的通用存储引擎。DML操作遵循ACID模型,支持事务;行级锁,提高并发访问性能;支持外键约束,保证数据的完整性和正确性。InnoDB存储引擎的每一张表都有对应的表名.idb表空间文件。
(2)MyISAM:不支持事务,不支持外键,支持表锁,不支持行锁,访问速度快。表名.sdi:存储表结构信息;表名.MYD:存储数据;表名.MYI:存储索引。
(3)Memory:Memory引擎的表数据时存储在内存中的,由于受到硬件问题、或断电问题的影响,只能将这些表作为临时表或缓存使用。特点:内存存放,hash索引。表名.sdi:存储表结构信息。
-
存储引擎的选择
在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。
(1)InnoDB:是Mysql的默认存储引擎,支持事务、外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包含很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择。
(2)MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。被MongoDB代替
(3)MEMORY:将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。被Redis代替
2.2 索引
索引(Index)
是帮助MySQL 高效获取数据的数据结构(有序)。
- 索引的结构
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构。
选择B+树的原因:
(1)相比于二叉树(红黑树),层级更少,搜索效率高。
(2)相比于B树,B树无论是叶子结点还是非叶子结点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,若同样保存大量数据,只能增加树的高度,导致性能降低;且B+树的叶子结点是由双向循环链表连接的,因此对于范围查找更快。
(3)相比于Hash索引,B+树支持范围匹配及排序操作。
2. 索引的分类
InnoDB引擎中索引分为聚簇索引和非聚簇索引:
(1)聚簇索引:将数据存储和索引放到了一块,索引结构的叶子结点保存了行数据
。必须有,且只有一个。
如果主键存在,主键索引就是聚簇索引。
如果主键不存在,则使用第一个唯一索引作为聚簇索引。
如果既没有主键,也没有唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚簇索引。
(2)非聚簇索引(二级索引):将数据与索引分开存储,索引结构的叶子结点关联的是对应的主键
,即二级索引会先通过自己的B+树找到要查询数据的主键,然后再根据主键从聚簇索引中查询对应的行数据,即回表查询
。可以存在多个。
- 索引的语法
(1) 创建索引
create [unique|fulltext] index 索引名称 on 表名 (索引列1,...); #若只有一个索引列,称为单列索引;若有多个索引列,称为联合索引
(2) 查看索引
show index from 表名;
(3) 删除索引
drop index 索引名 on 表名;
- SQL性能分析
(1)查看执行频次:show [session|global] status like 'Com_______';
,总共7个下划线,可以模糊查询Com_select、Com_insert等。
(2)慢查询日志:记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启,需要再MySQL的配置文件(/etc/my.cnf)中配置:slow_query_log=1来开启慢日志查询 和long_query_time=2自定义执行时间。可以通过show variables like ‘slow_query_log’;语句来查看是否开启。日志记录在localhost-slow.log日志文件中。
(3)show profiles:可以在做SQL优化时帮助我们了解时间都耗费到哪里去了。(1) 查看当前MySQL是否支持profile操作 select @@have_profiling; (2) 查看profiling是否开启 select @@profiling; (3) 开启profiling set [session|global] profiling=1; (4) 查看每一条SQL的耗时基本情况 show profiles; (5) 查看指定query_id的SQL语句各个阶段的耗时操作 show profile for query query_id; (6) 查看指定query_id的SQL语句CPU使用情况 show profile cpu for query query_id;
(4)explain:即explain执行计划,使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何执行SQL语句。分析查询语句或是表结构的性能瓶颈。只需在SQL语句前加上explain或desc关键字即可。
id:select序列号,表示查询中执行select子句或操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)。
select_type:select类型,如simple(简单查询)、primary(主查询,即最外层查询)、union(union第二个或后面的查询语句)、subquery(子查询)。
table:表名,若有别名显示的则为别名,<>括起来的表示临时表。
type
:连接类型,性能由好到差的连接类型为NULL(没有访问表或索引)、system(表中只有一行数据相当于系统表)、const(主键索引或唯一索引)、eq_ref(唯一性索引扫描,即扫描时索引键只有一行数据与之对应)、ref(非唯一性索引)、range(非唯一性索引扫描)、index(索引全表扫描)、all(全表扫描,不走索引)。
possible_keys
:查询可能用到的索引。
keys
:查询实际用的索引。
key_len
:处理查询的索引长度,表示索引中使用的字节数。最大可能长度,并非实际使用长度。
ref:显示索引的哪一列被使用了。
rows:MySQL认为必须要执行查询的行数,是一个估计值。
filtered:返回结果的行数占需读取行数的百分比,值越大越好。
extra
:其它额外信息。
- 索引的使用规则
(1)最左前缀法则:对于联合索引来说,索引的最左列必须存在(与位置无关),若中间有某列索引跳过(即不存在),则只有前边的列索引才有效。
(2)范围查询:在联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。规避方法:尽量使用>=或<=。
(3)索引失效:不要在索引列上进行运算操作,否则索引将失效;字符串不加引号,索引将失效;如果尾部模糊匹配,索引不会失效,但是如果头部模糊匹配,则索引失效。
(4)or连接:如果or有一侧没有索引,则都不会用索引进行查询。
(5)数据分布影响:如果MySQL评估使用索引比全表更慢,则不适用索引。
(6)SQL提示:人为提示用哪个索引。如use index(索引名)、ignore index(索引名)、force index(索引名),在表名后边声明。
(7)覆盖索引:即select返回的字段与索引字段或主键一致(在非聚簇索引中就已经可以获取全部所需信息,而不用回表查询),因此要减少使用select *。覆盖索引的extra字段为using where;using index:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询。非覆盖索引的extra字段为using index condition:查找使用了索引,但是需要回表查询。
(8)前缀索引:当字段类型为字符串(varchar,text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。语法:create index 索引名 on table(列名(n));。前缀长度可以根据索引选择性来决定(不重复的索引数/总记录数),索引选择性越高则查询效率越高。 - 索引的设计原则
(1)针对于数据量较大,且查询比较频繁的表建立索引。
(2)针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
(3)尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
(4)如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
(4)尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
(6)要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
(7)如果索引列不能存储NULL值,请在创建表时使用not null约束它。当优化器知道每列是否包含null值时,它可以更好地确定哪个索引最有效地用于查询。
2.3 SQL优化
- 插入数据优化
insert优化:批量插入;手动开启事务(在事务中多个批量插入);主键顺序插入(高于主键乱序插入)。
大批量插入数据:load指令(1) 客户端连接服务端时,加上参数 --local-infile mysql --local-infile -u root -p (2) 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关 set global local_infile=1; (3) 执行load指令将准备好的数据,加载到表结构中 load data local infile '路径' into table `表名` fields terminated by '间隔符' lines terminated by '\n';
- 主键优化
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表。满足业务需求的情况下,尽量降低主键的长度(非聚簇索引可以有很多,且其叶子结点存储的就是主键);插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键;尽量不要使用UUID做主键或者是其他自然主键,如身份证号;业务操作时,避免对主键的修改。
页是InnoDB的磁盘管理的最小单元,页可以为空,也可以填充一半,也可以填充100%,每个页包含了2-N行数据(如果一行数据过大,会行溢出),根据主键排序。
页分裂:主键乱序插入时,由于主键是顺序存储的,因此会根据插入的位置对页满的进行分裂(同时会创建新的页),再用指针进行连接。
页合并:删除记录时,只是逻辑删除,当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页看看是否可以将两个页合并以优化空间使用。 - order by优化
Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后再排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫filesort排序。若不是覆盖索引,也会在缓冲区中排序。
Using index:通过有序索引顺序扫描直接返回有序数据,即为using index,不需要额外排序,操作效率高。
多字段排序同样遵循最左前缀法则(比where语句更严格,这个前后顺序不能乱,而where存在即可);大量排序可增加排序缓冲区的大小sort_buffer_size(默认256k). - group by优化
Using temporary:分组时使用了临时表
Using index:分组时使用了索引,同样遵循最左前缀法则,并且还能和where共同组成最左前缀法则。 - limit优化
通过创建覆盖索引能够比较好的提高性能,可以通过覆盖索引加子查询形式进行优化(如子查询为覆盖索引实现的排序并分页,把该子查询结果看作一个表,再与原表进行内连接求得所需页的全部信息)。 - count优化
InnoDB引擎执行count(*)时会把数据一行一行从引擎里读出来,然后累积计数。优化思路:自己定义计数器。
count(*)
:InnoDB不会把全部字段取出来,而是做了优化,不取值,服务层按行进行累加。
count(1):不取值,服务层对返回的每一行,放数字1进去(也可以是其它数值),直接按行进行累加。
count(主键):取主键值,直接累加。
count(字段):有not null约束:取出字段值,直接累加;无not null约束:判断不为null再累加。 - update优化
使用update对数据进行更新时,要根据索引进行更新(即where根据索引选择),否则行级锁就会升级为表锁,降低并发性能。
2.4 视图
视图(View)
是一种虚拟存在的表。视图中的数据并不在数据库中实际存在,行和列数据来自自定义视图的查询中使用的表,并且是在使用视图时动态生成的。视图只保存了查询的SQL逻辑,不保存查询结果。
(1) 创建视图
create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded|local] check option];
(2) 查看创建视图语句
show create view 视图名称;
(3) 查看视图数据(把视图看作基本表即可)
select * from 视图名称;
(4) 修改视图
create or replace view 视图名称[(列名列表)] as select语句 [with [cascaded|local] check option]; #必须加or replace
alter view 视图名称[(列名列表)] as select语句 [with [cascaded|local] check option];
(5) 删除视图
drop view [if exists] 视图名称[,视图名称]; #可以删多个视图
- 视图的检查选项:当使用
with check option
子句创建视图时,MySQL会通过视图检查正在更改的每个行,例如插入、更新、删除,以使其符合视图的定义。MySQL允许基于另一个视图创建视图,它还会检查依赖视图中的规则以保持一致性。
cascaded
:默认值,级联,一旦使用,则会检查本视图以及所依赖的全部视图(依赖的依赖也包括,一直到顶)。
local
:哪个视图使用,就只检查是否符合那个视图(仅一个),凡是依赖于此视图的也会进行检查。 - 视图的更新:视图中的行与基本表中的行之间必须存在一对一的关系,才可以更新视图(增、删、改),视图的更新会直接改动对应的基本表。若出现聚合函数、distinct、group by、having、union等就无法更新视图。
- 视图的作用:
(1)简单:经常使用的查询可以定义为视图,使用户不用为以后的操作每次指定全部的条件(类似于将select语句封装成函数)。
(2)安全:数据库授权不能仅指定特定的行和列,而视图可以限制用户只能查询和修改他们所能见到的数据。
(3)数据独立:视图可帮助用户屏蔽真实表结构变化带来的影响。
2.5 存储过程
存储过程(StoredProcedure)
是事先经过编译并存储在数据库中的一段SQL语句的集合,特点:代码封装可复用;可接收参数,也可返回数据;减少网络交互,提升效率。在命令行中遇到分号就会运行,可以通过delimiter 结束字符来指定结束符号。
- 基本语法
(1) 存储过程的创建 create procedure 存储过程名称([in/out/inout 参数名 参数类型,...]) #默认为in(传入的参数),out(传出的参数),inout(既入又出) begin SQL语句 #可以是多条 end; (2) 调用存储过程 call 存储过程名([参数列表]); (3) 查询指定数据库的存储过程以及状态信息 select * from information_schema.routines where routine_schema='数据库名'; (4) 查询存储过程定义语句 show create procedure 存储过程名; (5) 删除 drop procedure [if exists] 存储过程名;
- 变量
(1)系统变量:MySQL服务器提供的,全局变量(global)、会话变量(session)。
(2)用户自定义变量:用户根据需要自定义的变量,用户变量不用提前声明,使用时直接用"@变量名"即可,作用域为当前连接。(1) 查看系统变量 show [session|global] variables; #查看所有系统变量 show [session|global] variables like '...'; # 模糊查询 select @@[session.|global.]系统变量名; #查看指定变量的值 (2) 设置系统变量 set [session|global] 系统变量名=值; set @@[session.|global.]系统变量名=值;
(3)局部变量:需要定义的局部生效的变量,需提前用declare声明。可以在存储过程中的begin end;中使用。(1) 自定义变量赋值 set @自定义变量名=值 [,@自定义变量名2=值2]...; #可以一次赋值多个变量,:=赋值也可以 select 字段名 into @自定义变量名 from 表名; #将select结果赋值给变量 (2) 使用自定义变量 select @自定义变量名; #同样可以显示多个变量,用逗号隔开
(1) 局部变量的声明 declare 变量名[,变量名2...] 变量类型 [default ...]; #可设定默认值,且可定义多个同变量类型的变量 (2) 局部变量赋值 set 变量名=值; set 变量名:=值; select 字段名 into 变量名 from 表名;
- if判断
if 条件1 then ... elseif 条件2 then ... else ... end if;
- case
(1) 根据具体值判断选择 case case_value when when_value1 then statement_list1 [when when_value2 then statement_list2]... [else statement_list] end case; (2) 根据表达式是否为true选择 case when search_condition1 then statement_list1 [when search_condition2 then statement_list2]... [else statement_list] end case;
- 循环
(1)while循环:先判定条件,条件为true则执行逻辑
(2)repeat循环:满足条件则退出循环(先执行一次逻辑,再判断是否满足条件,满足则退出循环)while 条件 do SQL逻辑... end while;
(3)loop循环:死循环,需用leave(类似于break)退出循环,iterate(类似于continue)跳过当前循环。repeat SQL逻辑... until 条件 end repeat;
[begin_label:] loop SQL逻辑 end loop [end_label];
- 游标
游标(cursor)是用来存储查询结果集的数据类型,再存储过程和函数中可以使用游标对结果集进行循环处理。(1) 声明游标(用于接收游标记录的变量的声明应在游标声明的前面) declare 游标名称 cursor for 查询语句; (2) 打开游标 open 游标名称; (3) 获取游标记录 fetch 游标名称 into 变量[,变量2...]; (4) 关闭游标 close 游标名称;
- 条件处理程序
条件处理程序(handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。declare handler_action handler for condition_value[,condition_value]... statement; #statement为要执行的语句,如关闭游标 handler_action continue:继续执行当前程序 exit:终止执行当前程序 condition_value sqlstate 状态码:如02000 sqlwarning:所有以01开头的简写 not found:所有以02开头的简写 sqlexception:除了01和02开头的简写
- 存储函数
存储函数是有返回值的存储过程,存储函数的参数只能是in类型的。create function 存储函数名([参数列表,只能是in类型,默认就是in]) returns 返回类型 [characteristic...] #deterministic:相同的输入参数产生相同的结果;no sql:不包含sql语句;reads sql data:包含读取语句,但不包含写入数据的语句 begin SQL语句 return ...; end;
2.6 触发器
触发器(Trigger)
是与表有关的数据库对象,旨在insert/update/delete之前或之后,触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作。
使用别名old和new来引发触发器中发生变化的记录内容,触发器只支持行级触发(比如update影响了5行数据,则触发器执行5次),不支持语句级别触发(无论影响几行数据,触发器执行1次)。
(1) 触发器的创建
create trigger 触发器名
before/after insert/update/delete
on 表名 for each row #行级触发器
begin
触发器语句
end;
(2) 查看触发器
show triggers;
(3) 删除触发器
drop trigger [数据库名.]触发器名; #若没有指定数据库名,默认为当前数据库
2.7 锁
- 全局锁:锁定数据库中的所有表,锁住后数据库就只能读,常用于数据库的备份
(1) 加全局锁
flush tables with read lock;
(2) 将数据库备份为.sql文件(这个命令在cmd中执行,不在数据库里执行)
mysqldump -h主机IP -u用户名 -p密码 数据库名>文件路径名.sql #这种方式会导致业务停止,或主从延迟
mysqldump --single-transaction -h主机IP -u用户名 -p密码 数据库名>文件路径名.sql #加上--single-transaction完成不加锁的一致性数据备份
(3) 释放全局锁
unlock tables;
- 表级锁:每次操作锁住整张表。
(1)表锁
表共享读锁(read lock):都能读数据,但是都不能写数据(别人写数据会阻塞直到自己释放锁)。
表独占写锁(write lock):自己既能读数据,又能写数据;别人既不能读数据,也不能写数据。
(2)元数据锁(meta data lock,MDL)(1) 加锁 lock tables 表名... read/write; (2) 释放锁 unlock tables; 或 客户端断开连接
MDL加锁过程是系统自动控制,无需显式使用,在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作。为了避免DML与DDL冲突,保证读写的正确性。
当对一张表进行增删改查的时候,加MDL读锁(共享);当对表结构进行变更操作的时候,加MDL写锁(排他)。
(3)意向锁
为了避免DML在执行时,加的行锁与表锁的冲突,在InnoDB中引入了意向锁,使得表锁不用检查每行数据是否加锁,使用意向锁来减少表锁的检查,进而提升效率。
意向共享锁(IS):与表锁共享锁(read)兼容,与表锁排它锁(write)互斥。由语句select … lock in share mode添加。
意向排他锁(IX):与表锁共享锁(read)及排它锁(write)都互斥,意向锁之间不互斥。由insert、update、delete、select … for update添加。 - 行级锁:每次操作锁住对应的行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁。
(1)行锁(Record Lock):锁定单个行记录的锁,防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持。
共享锁(S):允许一个事务去读一行,阻止其他事务获取相同数据集的排它锁。
排他锁(X):允许获取排它锁的事务更新数据,阻止其他事务获取相同数据集的共享锁和排它锁。
(2)间隙锁(Gap Lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下支持。
索引上的等值查询(唯一索引),给不存在的记录加锁时,优化为间隙锁。
索引上的等值查询(普通索引),向右遍历时最后一个值不满足查询需求时,临建锁退化为间隙锁。
索引上的范围查询(唯一索引),会访问到不满足条件的第一个值为止。
(3)临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。在RR隔离级别下支持。
2.8 InnoDB引擎
2.8.1 逻辑存储结构
- 表空间(ibd文件),一个mysql实例可以对应多个表空间,用于存储记录、索引等数据。
- 段,分为数据段(Leaf node segment)、索引段(Non-leaf node segment)、回滚段(Rollback segment),InnoDB是索引组织表,数据段就是B+树的叶子结点,索引段即为B+树的非叶子结点。段用来管理多个Extent(区)。
- 区,表空间的单元结构,每个区的大小为1M。默认情况下,InnoDB存储引擎页大小为16K,即一个区中一共有64个连续的页。
- 页,InnoDB存储引擎磁盘管理的最小单元,每个页的大小默认为16KB。为了保证页的连续性,InnoDB存储引擎每次从磁盘申请4-5个区。
- 行,InnoDB存储引擎数据是按行进行存放的。
Trx_id:每次对某条记录进行改动时,都会把对应的事务id赋值给trx_id隐藏列。
Roll_pointer:每次对某条记录进行改动时,都会把旧的版本写入到undo日志中,然后这个隐藏列就相当于一个指针,可以通过它来找到该记录修改前的信息。
2.8.2 架构
- 内存架构
Buffer Pool
:缓冲池是主内存中的一个区域,里面可以缓存磁盘上经常操作的真实数据,在执行增删改查操作时,先操作缓冲池中的数据(若缓冲池没有数据,则从磁盘加载并缓存),然后再以一定频率刷新到磁盘,从而减少磁盘IO,加快处理速度。
缓冲池以Page页为单位,底层采用链表数据结构管理Page。根据状态,将Page分为三种类型.
(1)free page:空闲page,未被使用。
(2)clean page:被使用page,数据没有被修改过。
(3)dirty page:脏页,被使用page,数据被修改过,也中数据与磁盘的数据产生了不一致。
Change Buffer
:更改缓冲区(针对于非唯一二级索引页
),在执行DML语句时,如果这些数据Page没有在Buffer Pool中,不会直接操作磁盘,而会将数据变更存在更改缓冲区Change Buffer中,在未来数据被读取时,再将数据合并恢复到Buffer Pool中,再将合并后的数据刷新到磁盘中。意义:二级索引通常是非唯一的,且增删改都很随机,可能会影响不相邻的二级索引页,如果每次都操作磁盘,会造成大量的磁盘IO,因此设置更改缓冲区可减少磁盘IO。
Adaptive Hash lndex
:自适应hash索引,用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询,如果观察到hash索引可以提升速度,则建立hash索引,称之为自适应hash索引。自适应哈希索引,无需人工干预,是系统根据情况自动完成。参数:adaptive_hash_index。
Log Buffer
:日志缓冲区,用来保存要写入到磁盘中的log日志数据(redo log ,undo log),默认大小为16MB,日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务,增加日志缓冲区的大小可以节省磁盘I/O。参数:innodb_log_buffer_size:缓冲区大小;innodb_flush_log_at_trx_commit:日志刷新到磁盘时机(0:每秒将日志写入并刷新到磁盘一次;1:日志在每次事务提交时写入并刷新到磁盘;2:日志在每次事务提交后写入,并每秒刷新到磁盘一次)。 - 磁盘架构
System Tablespace
:系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的,它也可能包含表和索引数据。参数: innodb_data_file_path。
File-Per-Table Tablespaces
:每个表的文件表空间包含单个InnoDB表的数据和索引,并存储在文件系统上的单个数据文件中。参数: innodb_file_per_table(默认开启,因此每一个表都有对应的ibd文件)。
General Tablespaces
:通用表空间,需要通过**create tablespace 通用表空间名 add datafile ‘对应ibd文件名’ engine=引擎名;来创建通用表空间。在创建表的时候,可以用create table … tablespace 通用表空间名;**来指定改表空间。
Undo Tablespaces
:撤销表空间,MySQL实例在初始化时会自动创建两个默认的undo表空间〈初始大小16M),用于存储undo log日志。
Temporary Tablespaces
:InnoDB使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。
Doublewrite Buffer Files
:双写缓冲区,InnoDB引擎将数据页从Buffer Pool刷新到磁盘前,先将数据页写入双写缓冲区文件中,便于系统异常时恢复数据。
Redo Log
:重做日志,是用来实现事务的持久性。该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log),前者是在内存中,后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中,用于在刷新脏页到磁盘时,发生错误时,进行数据恢复使用。 - 后台线程
Master Thread
:核心后台线程,负责调度其他线程,还负责将缓冲池中的数据异步刷新到磁盘中,保持数据的一致性,还包括脏页的刷新、合并插入缓存、undo页的回收。
lO Thread
:在InnoDB存储引擎中大量使用了AIO来处理IO请求,这样可以极大地提高数据库的性能,而IOThread主要负责这些lO请求的回调。
Purge Thread
:主要用于回收事务已经提交了的undo log,在事务提交之后,undo log可能不用了,就用它来回收。
Page Cleaner Thread
:协助Master Thread刷新脏页到磁盘的线程,它可以减轻Master Thread的工作压力,减少阻塞。
2.8.3 事务原理
- redo log
重做日志,记录的是事务提交时数据页的物理修改,是用来实现事务的持久性
。该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log),前者是在内存中,后者在磁盘中。当事务提交之后会把所有修改物理信息都会存到该日志中,用于在刷新脏页到磁盘时,发生错误时,进行数据恢复使用。 - undo log
undo log 保证了事务的原子性
,即事务要么全执行成功,要么全部回滚。
redo log + undo log 保证了事务的一致性
回滚日志,用于记录数据被修改前的信息,作用包含两个:提供回滚
和MVCC
(多版本并发控制)。undo log和redo log记录物理日志不一样,它是逻辑日志。可以认为当delete一条记录时,undo log中会记录一条对应的insert记录,反之亦然,当update一条记录时,它记录一条对应相反的update记录。当执行rollback时,就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
Undo log销毁: undo log在事务执行时产生,事务提交时,并不会立即删除undo log,因为这些日志可能还用于MVCC。
Undo log存储: undo log采用段的方式进行管理和记录,存放在前面介绍的rollback segment回滚段中,内部包含1024个undo log segment。
2.8.4 MVCC
MVCC + 锁 保证了事务的隔离性
当前读:读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁。
快照读:简单的select(不加锁)就是快照读,快照读,读取的是记录数据的可见版本,有可能是历史数据,不加锁,是非阻塞读。Read Committed:每次select,都生成一个快照读;Repeatable Read:开启事务后第一个select语句才是快照读的地方;Serializable:快照读会退化为当前读。
MVCC:全称Multi-Version Concurrency Control,多版本并发控制。指维护一个数据的多个版本,使得读写操作没有冲突,快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现,还需要依赖于数据库记录中的三个隐藏字段、undo log日志、readview。
(1)三个隐藏字段
(2)undo log版本链
不同事务或相同事务对同一条记录进行修改,会导致该记录的undo log生成一条记录版本链表,链表的头部是最新的旧记录,链表尾部是最早的旧记录。
(3)readview
ReadView (读视图)是快照读SQL执行时MVCC提取数据的依据,记录并维护系统当前活跃的事务(未提交的)id。
read committed:在事务中每一次执行快照读时生成ReadView。(即读取的数据应该是已经提交的事务)
repetable read:仅在事务中第一次执行快照读时生成ReadView,后续复用该ReadView。
2.9 MySQL管理
-
MySQL自带的数据库
-
mysql
-
mysqladmin
mysqladmin是一个执行管理操作的客户端程序。可以用来检查服务器的配置和当前状态、创建并删除数据库等。 -
mysqlbinlog
mysqlbinlog是二进制日志管理工具。
-
mysqlshow
mysqlshow客户端对象查找工具,用来很快的查找存在那些数据库、数据库中的表、表中的列或者索引。
-
mysqldump
mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表,及插入表的SQL语句。
-
mysqlimport/source
mysqlimport是客户端数据导入工具,用来导入mysqldump加-T参数后导出的文本文件。
语法:mysqlimport [options] db_name textfile1 [textfile2]
source指令导入sql文件
语法:source 路径.sql
3. MySQL运维
3.1 日志
- 错误日志
错误日志是MySQL中最重要的日志之一,它记录了当mysqld启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时,建议首先查看此日志。该日志是默认开启的,show variables like '%log_error%';
查看路径。 - 二进制日志
二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和DML(数据操纵语言)语句,但不包括数据查询(select、show)语句。作用:灾难时的数据恢复;MySQL的主从复制。该日志是默认开启的,show variables like '%log_bin%';
。
删除日志,mysql配置文件中有binlog_expire_logs_seconds参数默认为30天,日志存在超过它就会自动删除。
- 查询日志
查询日志中记录了客户端的所有操作语句,而二进制日志不包含查询数据的SQL语句。该日志默认是关闭的,show variables like '%general%';
查看是否开启以及路径。 - 慢查询日志
慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志,默认未开启。long_query_time默认为10秒,最小为0,精度可以到微秒。
3.2 主从复制
主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库(slave)和主库(master)的数据保持同步。MySQL支持一台主库同时向多台从库进行复制,从库同时也可以作为其他从服务器的主库,实现链状复制。
优点:主库出现问题,可以快速切换到从库提供服务;实现读写分离(从库读,主库增删改),降低主库的访问压力;可以在从库中执行备份,以避免备份期间影响主库服务。
原理
(1)Master主库在事务提交时,会把数据变更(DDL和DML)记录在二进制日志文件BinLog中。
(2)从库(IOThread)读取主库的二进制日志文件BinLog,写入到从库的中继日志Relay Log。
(3)从库slave重做(SQLThread)中继日志中的事件,将改变反映它自己的数据。
3.3 分库分表
起因:随着互联网及移动互联网的发展,应用系统的数据量也是成指数式增长,若采用单数据库进行数据存储,存在以下性能瓶颈:
(1)IO瓶颈:热点数据太多,数据库缓存不足,产生大量磁盘IO,效率较低。请求数据太多,带宽不够,网络lO瓶颈。
(2)CPU瓶颈:排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源。
分库分表的中心思想
都是将数据分散存储,使得单一数据库/表的数据量变小来缓解单一数据库的性能问题,从而达到提升数据库性能的目的。
- 拆分方式
(1)垂直分库:以表为依据,根据业务将不同表拆分到不同库中。(每个库结构数据都不同)
(2)垂直分表:以字段为依据,根据字段属性将不同字段拆分到不同表中。(每个表结构数据都不同)
(3)水平分库:以字段为依据,按照一定策略,将一个库的数据拆分到多个库中。(每个库的表结构数据都不同)
(4)水平分表:以字段为依据,按照一定策略,将一个表的数据拆分到多个表中。(每个表的表结构数据都不同) - 实现技术
(1)shardingJDBC:基于AOP原理,在应用程序中对本地执行的SQL进行拦截,解析、改写、路由处理。需要自行编码配置实现,只支持java语言,性能较高。
(2)MyCat:数据库分库分表中间件,不用调整代码即可实现分库分表,支持多种语言,性能不及前者。
Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。
3.4 读写分离
读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作,从数据库提供读操作,这样能有效地减轻单台数据库的压力。可以通过MyCat中间件实现。