MySql笔记
[database=schema]
show databases/tables;
desc 表名; -- 查询表结构
show create table 表名; -- 查询建表语句
select database(); -- 查询当前数据库
create database [if not exists] mydb;
drop database [if exists] mydb;
use mydb;
DDL表结构操作
create table 表名(id int primary key autu_increment comment 'ID唯一标识', ... , ...)[comment '表注释'];
not null -- 非空约束
unique -- 唯一约束 唯一不重复
primary key -- 主键约束 (autu_increment自增) 主键是一行数据的唯一标识,非空且唯一
default -- 默认约束 保存数据时,如果未指定该字段值,则采用默认值
foreign key-- 外键约束 物理外键(影响增删改效率 建议逻辑外键)让两张表的数据建立连接,保证数据的一致性和完整性
-- (仅用于单节点数据库,不适用与分布式、集群场景。容易引发数据库的死锁问题,消耗性能)
-- constraint 外键名称 foreign key (外键字段名) references 主表(字段名)
例子 让两张表的数据建立连接
alter table tb_emp add constraint tb_emp_fk_dept_id foreign key (dept_id) references tb_dept (id) ;
tinyint 字节1 [signed(有符号)范围(-128,127)] [unsigned(无符号) 范围(0,255)]
-- smallint 字节2
-- mediumint 字节3
int 字节4
bigint 字节8
float 字节4 float(5,2)5是整个数字长度,2是小数个数
double 字节8
decimal 和java里BigDecimal一样 decimal(8,2)8是整个数字长度,2是小数个数
char 字节范围(0,255) 定长字符串 char(10):[性能高浪费空间]最多只能存10个字符,不足10个字符,占用10个字符空间
varchar 字节范围(0,65535) 变长字符串 varchar(10):[性能低节省空间]最多只能存10个字符,不足10个字符,按照实际长度存储
添加字段: alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
修改字段类型: alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型: alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
删除字段: alter table 表名 drop column 字段名;
修改表名: rename table 表名 to 新表名;
删除表名: drop table [if exists] 表名;
MySQL建表时注意什么?
- 注意选择存储引擎,如果要支持事务需要选择InnoDB。
- 注意字段类型的选择,对于日期类型如果要记录时分秒建议使用datetime, 只记录年月日使用date类型,对于字符类型的选择,固定长度字段选择char, 不固定长度的字段选择varchar,varchar比char节省空间但速度没有char快; 对于内容介绍类的长广文本字段使用text或longtext类型; 如果存储图片等二进制数据使用blob或longblob类型; 对金额字段建议使用DECIMAL;对于数值类型的字段在确保取值范围足够的前提下尽量使用占用空间较小的类型,
- 主键字段建议使用自然主键,不要有业务意义,建议使用int unsigned类型,特殊场景使用bigint类型。
- 如果要存储text、blob字段建议单独建一张表,使用外键关联。
- 尽量不要定义外键,保证表的独立性,可以存在外键意义的字段。
- 设置字段默认值,比如:状态、创建时间等。
- 每个字段写清楚注释。
- 注意字段的约束,比如:非空、唯一、主键等。
DML增删改
insert into tb_emp (username,name,create_time) values ('aaa","插入",now()), ('piliang","批量",now());
update tb_emp set name = '更新' , update_time = now() where id = 1; -- 不加where全部更新
delete from tb_emp where id = 1; -- 不加where全部删除此表数据
DQL查询
select (distinct去重记录) 字段列表 (as 别名 可省略) ( * 性能低?) ( cout(*)性能好)
from 表名 -- 表名,表名 多表查询用,隔开 笛卡尔积:笛卡尔乘积是指在数学中,两个集合(A集合和B集合)的所有组合情况。
where 条件
between and 在某个范围之内(含最小、最大值)
in(..) 在in之后列表中的值(多选一 单个符合就行)
like 模糊匹配(_匹配单个字符,%匹配任意个字符)
group by 分组字段 例1 例3 分组查询select返回两类字段,一类是分组字段例如gender,另一类是聚合函数例如 count(*)
having 分组后条件
order by 排序字段 例2多字段排序用 , 默认升序 -- ASC:升序 DESC:降序
limit 分页(起始索引, 查询记录数) -- 第一页=起始索引0 每页展示5条记录: 0,5
-- 起始索引 = (页码 - 1)* 每页展示记录数
-- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 5
例1
根据性别分组,统计男性和女性员工的数量
select gender,count(*) from tb_emp group by gender;
先查询入职时间在 '2015-01-01'(包含)以前的员工,并对结果根据职位分组 , 获取员工数量大于等于2的职位
select job,count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >=2;
where与having区别
1. 执行时机不同: where 是分组之前进行过滤,不满足where条件,不参与分组;而 having 是分组之后对结果进行过滤。
2. 判断条件不同: where 不能对聚合函数进行判断,而 having 可以。
执行顺序: where > 聚合函数 > having
例2
根据 入职时间 升序,入职时间相同,再按照 更新时间 降序
select * from 表 order by 入职时间 , 更新时间 desc
例3
员工性别信息的统计 count(*)
if(条件表达式,true取值,false取值)
select if(gender = 1,'男','女') 性别,count(*)from tb_emp group by gender
员工职位信息的统计
case 表达式 when 值1 then 结果1 when 值2 then 结果2 ... else ... end
select
(case job when 1 then "啊" when 2 then "我" when 3 then "额" else "无" end ),
count(*)
from tb_emp group by job;
连接查询
### 内连接:相当于查询A、B交集部分数据
查询员工的姓名,及所属的部门名称(隐式内连接实现)
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;
起别名 select e.name, d.name from tb_emp e, tb_dept d where e.dept_id = d.id;
查询员工的姓名,及所属的部门名称(显式内连接实现)
select tb_emp.name,tb_dept.name from tb_emp [inner 可省略] join tb_dept on tb_emp.dept_id = tb_dept.id;
### 外连接:
#### 左外连接:查询左表所有数据(包括两张表交集部分数据) select 字段列表 from 表1 left [outer] join 表2 on 连接条件;
查询员工表 所有 员工的姓名,和对应的部门名称(左外连接)
select e.name, d.name from {tb_emp e left join} tb_dept d on e.dept_id = d.id
#### 右外连接:查询右表所有数据(包括两张表交集部分数据)
查询部门表 所有 部门的名称,和对应的员工名称(右外连接)
select e.name,d.name from tb_emp e {right join tb_dept d} on e.dept_id = d.id
子查询
SQL语句中嵌套select语句,称为嵌套查询,又称子查询。
### 标量子查询(只有一个值)
select * from tb_emp where dept_id = (select id from tb_dept where name = '一部');
### 列子查询 返回的结果是一列(可以是多行) 常用的操作符: in 、 not 、 in 等
一行XXX1 -- 相当于数据库里查出来一列两行
两行XXX2 -- 相当于数据库里查出来一列两行
select id from tb_dept where name='一部' or name='二部'; -- 输出结果(2 , 3)
select * from tb_emp where dept_id in (select id from tb_dept where name = '一部' or name = '二部');
### 行子查询 返回的结果是一行(可以是多列 例如: 一列XXX1 两列XXX2 )。常用的操作符: = 、 ◇ 、 in 、 not in
select entrydate,job from tb_emp where name = '姓名';-- 输出结果('2000-01-01',2) 放入下面sql语句()中即可
select * from tb_emp where (entrydate,job)=('2000-01-01',2);
### 表子查询 返回的结果是多行多列,常作为临时表常用的操作符: in
查询入职日期是"2006-01-01"之后的员工信息
select * from tb_emp where entrydate > '2006-01-01';
查询这部分员工信息及其部门名称 - tb_dept
select e.* , d.name from (select * from tb_emp where entrydate > '2006-01-01') e , tb_dept d where e.dept_id = d.id;
事务
原子性(Atomicity) :事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
一致性(Consistency) :事务完成时,必须使所有的数据都保持一致状态。
隔离性(Isolation) :数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
持久性(Durability) :事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
脏读 不可重复读 幻读
START TRANSACTION;(或 BEGIN;)
COMMIT 和 ROLLBACK;
隔离级别
Read uncommitted 读 未提交
Read committed 读 已提交
Repeatable Read(mysql默认) 可重复读
Seriąlizable 串行化
--查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
--设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READUNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
注意:事务隔离级别越高,数据越安全,但是性能越低。
可插拔存储引擎
InnoDB (默认mysql5.5+)
用于事务处理,具有ACID事务支持等特性,如果在应用中执行大量insert和update操作,应该选择InnoDB
支持事务,行锁,支持外键
MyISAM
管理非事务表,提供高速存储和检索以及全文搜索能力,如果在应用中执行大量select操作,应该选择MyISAM
不支持事务,表锁,不支持外键
MEMORY
将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。
MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。
索引(index)
帮助数据库 高效获取数据 的 数据结构。相当于一本书的目录
mysql默认索引结构B+Tree(多路平衡搜索树)矮胖矮胖的树。叶子节点是双向链表便于排序
###索引分类
primary key 主键字段,在建表时,会自动创建主键索引。性能最高
unique 添加唯一约束时,数据库实际上会添加唯一索引。
常规索引 快速定位特定数据
全文索引 FULLTEXT 查找的是文本中的关键词,而不是比较索引中的值
创建 CREATE [UNIQUE | FULLTEXT ] INDEX 索引名 ON 表名 (字段名,...);
例子 create index idx_sku_sn on tb_sku(sn,...);
查看 show index from 表名;
删除 drop index 索引名 on 表名;
索引会占用存储空间。索引提高了查询效率,同时也降低了insert,update,delete的效率。
在InnoDB存储引擎中,根据索引的存储形式,又可以分为:
聚集索引 二级索引
# 覆盖索引
就是select的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。
SQL执行频率
mysql > show global status like 'Com_______';
MySQL的慢查询日志
默认没有开启,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
配置完毕之后,通过以下指令重新启动MySQL服务器进行测试,查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。
show profiles 能够在做SQL优化时了解时间都耗费在哪里
通过have-profiling参数,能够看到当前MySQL是否支持profile操作:
SELECT @@have_profiling;
默认profiling是关闭的,可以通过set语句在session/global级别开启profiling:
SET profiling=1;
#查看每一条SQL的耗时基本情况
show profiles;
#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
# 获取 MySQL 如何执行 SELECT 语句的信息
直接在select语句之前加上关键字explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE条件;
索引失效情况
- 不要在索引列上
进行运算操作
否则索引将会失效,性能将会降低 - 如果查询字符串类型的字段
没有加单引号
,索引将会失效 - 模糊查询中,
头部进行模糊匹配
会导致失效 前后都加百分号
会导致索引失效- 用or分割开的条件,如果or
前的条件中的列有索引,而后面的列中没有索引
,那么涉及的索引都不会被用到。前缀索引 当字段类型为字符串(varchar, text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO create index idx_xxxx on table_name(column(前缀长度)); 前缀长度 可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高, 唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。 select count(distinctemail) / count(*) from tb_user; select count(distinct substring(email,1,5)) / count(*) from tb_user ;
索引设计原则
- 针对于
数据量较大
,且查询比较频繁
的表建立索引。 - 针对于
常
作为查询条件(where)、排序(order by)、分组(group by)
操作的字段建立索引。 - 尽量选择区分度高的列作为索引,尽量建立
唯一索引
,区分度越高,使用索引的效率越高。 - 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立
前缀索引
。 - 尽量使用
联合索引
,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。 - 要控制
索引的数量
,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。 - 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
SQL优化
insert优化
批量插入
手动提交事务 start transaction;…
主键顺序插入
大批量插入数据
如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load
指令进行插入。
#客户端连接服务端时,加上参数--local-infile
mysql --local-infile -u root -p
#设置全局参数localinfile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
#执行load指令将准备好的数据,加载到表结构中
load data local infile '/root/sqll.log' into table 'tb_user'
fields terminated by ',' lines terminated by '\n';
每一个字段之间使用','分隔 每一行数据之间使用'\n'分隔
主键优化
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引
组织表(index organized table IOT
)
主键设计原则
满足业务需求的情况下,尽量降低主键的长度
。
插入数据时,尽量选择顺序插入
,选择使用AUTO_INCREMENT
自增主键。
尽量不要使用UUID
做主键或者是其他自然主键,如身份证号。
业务操作时,避免对主键的修改。
order by优化
①Using filesort:
通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
②Using index : 通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,操作效率高
。
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。
- 如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。
group by优化
在分组操作时,可以通过索引
来提高效率。
分组操作时,索引的使用也是满足最左前缀法则
的。
limit优化
一般分页查询时,通过创建 覆盖索引
能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。
explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
count优化
效率排序,coumt(字段) < count(主键 id) < count(1) ≈ count(*
),所以尽量使用 count(*
)。
update优化
InnoDB的行锁
是针对索引
加的锁,不是针对记录加的锁,并且该索引不能失效,否则
会从行锁升级为表锁
。
尽量根据主键/索引
字段进行数据更新
索引字段行锁,非索引字段表锁
锁
1. 全局锁:锁定数据库中的所有表
。
其典型的使用场景是做全库的逻辑备份,
mysql > flush tables with read lock ;
mysqldump -h192. 168. 200. 202 -uroot-p1234 db01>db01.sql
mysql > unlock tables;
数据库中加全局锁,是一个比较重的操作,存在以下问题:
- 如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆。
- 如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟。
在InnoDB引擎中,我们可以在备份时加上参数 --single-transaction
参数来完成不加锁的一致性数据备份。
2. 表级锁:每次操作锁住整张表
。
①. 表锁
表共享读锁(read lock) 表独占写锁(write lock)
- 加锁: lock tables表名… read/write。
- 释放锁: unlock tables /客户端断开连接。
读锁不会阻塞其他客户端的读,但是会阻塞写。
写锁既会阻塞其他客户端的读,又会阻塞其他客户端的写。
②. 元数据锁(meta data lock,MDL)
主要作用是维护表元数据的数据一致性
在表上有活动事务的时候,不可以对元数据进行写入操作。
在MySQL5.5中引入了MDL,当对一张表进行增删改查
的时候,加MDL读锁(共享);
当对表结构
进行变更操作的时候,加MDL写锁(排他)。
③. 意向锁
为了规避行锁和表锁它们之间在加锁时的冲突问题
意向共享锁(IS):由语句 select... lock in share mode
添加。
与表锁共享锁(read)兼容,与表锁排它锁(write)互斥。
意向排他锁(IX):由insert、update、delete、select… for update 添加。
与表锁共享锁(read)及排它锁(write)都互斥。意向锁之间不会互斥。
3. 行级锁:每次操作锁住对应的行数据
。
InnoDB
①.行锁(Record Lock) :
锁定单个行记录的锁,防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
- 共享锁(S) :允许一个事务去读一行,阻止其他事务获得相同数据集的排它锁。
- 排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他锁。
默认情况下, InnoDB在 REPEATABLE READ事务隔离级别运行, InnoDB使用 next-key锁进行搜索和索引扫描,以防止幻读。
- 针对唯一索引进行检索时,对已存在的记录进行等值匹配时,将会自动优化为行锁。
- InnoDB的行锁是针对于索引加的锁,
不通过索引
条件检索数据,那么InnoDB将对表中的所有记录加锁,此时就会升级为表锁
。
②.间隙锁(Gap Lock) :
锁定索引记录间隙(不含该记录) ,确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。
③.临键锁(Next-Key Lock) :
行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。在RR隔离级别下支持。
默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用 next-key 锁进行搜索和索引扫描,以防止幻读。
- 索引上的等值查询(唯一索引),给不存在的记录加锁时,优化为间隙锁。
- 索引上的等值查询(普通索引),向右遍历时最后一个值不满足查询需求时,next-key lock 退化为询隙锁。
- 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。
间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存,一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。
日志
错误日志
该日志是默认开启的,默认存放目录/var/log/,默认的日志文件名为mysqld.log
mysql > show variables like '%log_error*'; 日志位置
tail -50 /var/log/mysqld.1og
二进制日志(BINLOG)
记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句,但不包括数据查询(SELECT、SHOW语句)。
作用:①.灾难时的数据恢复;②.MySQL的主从复制。
mysql > show variables like '%log_bin%'; 日志位置
日志查看
通过二进制日志查询工具mysqlbinlog来查看 mysqlbinlog [参数选项] logfilename
日志删除
reset master; 删除全部 binlog日志,删除之后,日志编号,将从 binlog.000001重新开始
purge master logs to 'binlog.******'; 删除编号之前的所有日志
purge master logs before 'yyyy-mm-dd hh24:mi:ss'; 删除日志为"yyyy-mm-dd hh24:mi:ss" 之前产生的所有日志
设置二进制日志过期会自动删除
show variables like '%binlog_expire_logs_seconds%';
查询日志
慢查询日志
Redo Log:重做日志 是用来实现事务的持久性。
该日志文件由两部分组成:
重做日志缓冲(redo log buffer)以及重做日志文件(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 logsegment。
MVCC 多版本并发控制
(隐式字段+undo log版本链+ReadView(读视图)实现MVCC的原理)
(原子性 - undo log)
(持久性 - redo log)
(一致性 - redo log + undo log 共同保证事务的一致性) (隔离性 - MVCC+锁 )
当前读
读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁。
对于我们日常的操作,如:select.. lock in share mode(共享锁), select … for update,update, insert, delete(排他锁)都是一种当前读。
快照读
简单的select (不加锁)就是快照读,快照读,读取的是记录数据的可见版本,有可能是历史数据,不加锁,是非阻塞读。
Read Committed:每次select,都生成一个快照读 Repeatable Read:开启事务后第一个select语句才是快照读的地方。
Serializable:快照读会退化为当前读。
全称 Multi-Version Concurrency Control,多版本并发控制。指维护一个数据的多个版本,使得读写操作没有冲突,快照读为MysQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现,还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。
隐式字段: 三个甚至两个
DB_TRX_ID最近修改事务ID,记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR回滚指针,指向这条记录的上一个版本,用于配合undo log,指向上一个版本。
DB_ROW_ID隐藏主键,如果表结构没有指定主键,将会生成该隐藏字段。
undo log版本链
不同事务或相同事务对同一条记录进行修改,会导致该记录的undolog生成一条记录版本链表,链表的头部是最新的旧记录,链表尾部是最早的旧记录。
ReadView (读视图)
是快照读SQL执行时MVCC提取数据的依据,记录并维护系统当前活跃的事务(未提交的) id。
ReadView中包含了四个核心字段:
m_ids 当前活跃的事务ID集合
min_trx_id 最小活跃事务ID
max_trx_id 预分配事务ID,当前最大事务ID+1(因为事务ID是自增的)
creator_trx_id ReadView创建者的事务ID
trx_id:代表是当前事务ID。
版本链数据访问规则
①.trx_id == creator_trx_id?可以访问该版本 -> 成立,说明数据是当前这个事务更改的。
②. trx_id < min_trx_id?可以访问该版本 -> 成立,说明数据已经提交了。
③. trx_id > max_trx_id?不可以访问该版本 -> 成立,说明该事务是在ReadView生成后才开启。
④. min_trx_id <= trx_id <= max_trx_id?如果trx_id不在m_ids中是可以访问该版本的 -> 成立,说明数据已经提交。