数据类型
整数类型
tinyint 占1个字节
smallint 占2个字节
mediumint 占3个字节
int 占4个字节
bigint 占8个字节
以上各种整数类型有可选属性unsigned,表示无符号。比如int占4个字节,则范围为-2^32/2 ~ (2^32-1)/2;如果为unsigned int也占用4个字节则范围为0 ~ 2^32-1。
实数类型
实数是指带有小数部分的数字。Mysql支持精确类型,也支持不精确类型。
float 占4字节 支持近似计算
dubole 占8字节 支持近似计算
decimal 存储精确的小数,支持精确计算,适用于财务数据
decimal适用于存储财务数据,计算的开销比float、double大,但是精确度高。有没有一种方式同时兼顾两者的优点呢?可以使用bigint代替decimal,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设存储的财务数据要精确到万分之一,则可以把所有金额乘以一万。
字符串类型
varchar 存储可变长字符串,最长可指定65535
char 存储定长字符串,最长可指定255
varchar
- 仅使用必要的空间,越短的字符串使用越少的空间。但是如果MySQL的ROW_FORMAT=FIXED,则每一行都会使用定长存储,这会浪费空间
- 需要使用1或2个额外字节记录字符串的长度:如果列的最大长度<=255字节,则只使用1个字节表示,否则使用2个字节表示
- 由于是可变长的,在update时候可能使行变得比原来更长。如果一行占用的空间增长,并在内存页没有更多的空间可以存储,InnoDB需要通过分裂也来使行可以放进页内。这样会产生内存碎片。 char
- mysql根据定义的字符串长度分配足够的空间
- 由于是定长的,对于进场变更的数据也不容易产生碎片
适用场景
- 对于字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题;varchar更合适
- 对于所有值都接近同一个长度;char更合适
- 对于非常短的列,char比varchar在存储空间上也更有效率。例如char(1)只需一个字节,varchar(1)需要两个字节,因为还有一个记录长度的字节
日期和时间类型
datetime 占用8字节,与时区无关 支持最大年份9999
timestamp 占用4字节,与时区有关 支持最大年份2038
date 占用4字节 年月日格式:0000:00:00
time 占用3字节 时分秒格式:00:00:00
year 占用1字节,有效范围1901~2155 年格式:0000
datetime或者timestamp类型的字段赋值与展示:'YYYY-MM-DD HH:MM:SS'或'YYYYMMDDHHMMSS'格式表示的字符串,使用select查询到的该字段展示格式为'YYYY-MM-DD HH:MM:SS'。举个例子,比如我现在输入'2008-08-08 08:08:08',dateTime类型转换为2008-08-08 08:08:08展示,输入'20080808080808',同样转换为2008-08-08 08:08:08展示。
常用SQL
访问控制
查看账户信息
use mysql;
select * from user;
创建账号
create user 用户名 identified by '密码'
修改密码
set passwd for 用户 = Passwd('新密码')
删除账号
drop user 用户名
查看访问权限
show grants for 用户名
字符集
数据库用于存储和检索数据,不同的字符集决定了什么方式存储和检索,不同的字符集有默认的校对规则,不同的校对规则决定了排序
查看支持的所有字符集
show character set
查看支持的所有校对规则
show collation
查看当前字符集
show variables like 'character%'
查看当前校对规则
show variables like 'collation%'
修改字符集合校对规则
character_set_server 服务器字符集,默认的字符集
存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
数据库
连接数据库
mysql -h 主机名(ip) -u 用户名 -P 端口 -p 密码
查看数据库列表
show databases;
选定数据库
use 数据库名
查看数据库创建语句
show create database 数据库名
删除数据库
drop 数据库名
数据表
基本操作
查看表列表
show tables;
创建表
create table 表名(
字段名 类型 约束(多约束使用空格隔开),
字段名 类型 约束,
字段名 类型 约束
);
重命名表
RENAME TABLE <旧表名> [TO] <新表名>;
查看表创建语句
show create table 表名
查看表结构
show columns from 表名
describe 表名 --快捷方式
desc 表名 --超快捷方式
删除表
drop 表名
修改表结构
添加字段
ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;
修改字段类型
ALTER TABLE <表名> MODIFY <字段名> <数据类型>[约束条件]
修改字段名和数据类型
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>[约束条件]
删除字段
ALTER TABLE <表名> DROP <字段名>;
添加/删除约束-非空、自增
ALTER TABLE <表名> MODIFY <字段名> <数据类型> not null auto_increment
ALTER TABLE <表名> MODIFY <字段名> <数据类型> null
添加/删除约束-唯一(唯一性约束的列可以为null值)、主键(主键约束不能为null且值不能重复)、外键约束
alter table <表名> add constraint <约束名> unique(字段)
alter table <表名> add constraint <约束名> primary key(字段1,字段2)
alter table <从表> add constraint <约束名> foregin key(外键) references 主表(主键)
alter table 表名 drop constraint 约束名
索引操作
新建索引
create [unique] index idx_name on table_name(column_list)
删除索引
drop index idx_name on table_name
查询索引
show index from table_name
数据操作
查询
select distinct <select_list>
from <left_table> <join_type> join <right_table> on <join_condition>
where <where_condition>
group by <group_by_list>
having <having_condition>
order by <order_by_list>
limit <limit_condition>
机读顺序
from <left_table> <join_type> join <right_table> on <join_condition>
where <where_condition>
group by <group_by_list>
having <having_condition>
select distinct <select_list>
order by <order_by_list>
limit <limit_condition>
where子句操作
=
!=
<
<=
>
>=
between 值1 and 值2 (包含值1和值2)
not between 值1 and 值2 (包含值1和值2)
is null
is not null
in(值1,值2,值3)
not in(值1,值2,值3)
like 支持通配符%和-,前者匹配0个或多个字符,后者只匹配一个字符
group by <字段名> having <条件>
首先系统根据SELECT语句得到一个结果集,然后根据分组字段,将具有相同分组字段的记录归并成了一条记录。这个时候剩下的那些不存在于Group By语句后面作为分组依据的字段就有可能出现多个值,但是目前一种分组情况只有一条记录,一个数据行是无法放入多个数值的,所以这里就需要通过一定的处理将这些多值的列转化成单值,然后将其放在对应的数据格中,那么完成这个步骤的就是聚合函数。这就是为什么这些函数叫聚合函数(aggregate functions)了。
支持按照多字段分组,多字段用逗号隔开,依次按照指定的字段从左向右的字段分组
order by <字段名 desc/asc>
支持按照多字段排序,多字段用逗号隔开,依次按照指定的字段从左向右的字段排序
limit
limit 5 --从下表为0开始最多查到5行数据
limit 2,5 --从下表为2开始最多查到5行数据
组合查询
select语句1 union select语句2 --去重
select语句1 union all select语句2 --不去重
前后两个语句查询的字段数一样、类型兼容才能使用。
适用于在查询不同表的数据但是要合并返回结果的情况。
常用函数
- 聚集函数
count(*) --不忽略值为NULL的行
count(字段) --忽略值为NULL的行
max(字段) --忽略值为NULL的行
min(字段) --忽略值为NULL的行
sum(字段) --忽略值为NULL的行
avg(字段) --忽略值为NULL的行
以上除count(*)外都有以下版本
count(distinct 字段) --忽略值为NULL的行,计算不同值的总数
max(distinct 字段) --忽略值为NULL的行,没有意义
min(distinct 字段) --忽略值为NULL的行,没有意义
sum(distinct 字段) --忽略值为NULL的行,计算不同值和
avg(distinct 字段) --忽略值为NULL的行,计算不同值的平均值
- 文本函数
concat(params...) --参数用逗号隔开,拼接一个字段值
trim(字段) --去掉字段值的左右两边的空格
ltrim(字段) --去掉字段值的左边的空格
rtrim(字段) --去掉字段值的右边的空格
lower(字段) --转成小写
upper(字段) --转成大写
length(字段) --返回长度
- 时间函数
date(字段) --返回日期时间的日期部分
year(字段) --返回日期的年份部分
month(字段) --返回日期的月份部分
day(字段) --返回日期的天数部分
time(字段) --返回日期时间的时间部分
hour(字段) --返回时间的小时部分
minute(字段) --返回时间的分钟部分
second(字段) --返回时间的秒部分
date_format() --返回格式化的日期或时间
datediff() --计算两个日期之差
now() --返回当前日期和时间
curdate() --返回当前日期
curtime() --返回当前时间
插入
INSERT INTO table_name (field1, field2,...fieldN ) --field可以不指定,则表示表的所有列
VALUES
(value1, value2,...valueN ),
(value11, value22,...valueNN );
INSERT INTO table_name (field1, field2,...fieldN ) --field可以不指定,则表示表的所有列
SELECT语句; --select查询的结果列数需要与field列数一样
更新
UPDATE table_name SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
删除
DELETE FROM <表名> [WHERE 子句]
优化
逻辑架构
Mysql分为Server层和存储引擎层
第一层Server层:包括连接器、查询缓存、分析器、优化器、执行器。连接器管理连接、权限验证;查询缓存,命中缓存就直接返回;分析器进行词法、语法分析;优化器生成执行计划、选择索引;执行器操作引擎,返回数据。跨存储引擎的功能都在这一层实现,比如还有内置函数、视图、触发器、存储过程。
第二层存储引擎层:负责数据的存储和提取。存储引擎是可拔插式的,Mysql使用InnoDB引擎。
一条SQL查询语句如何执行
select * from user where id=3;
1.连接器:客户端执行命令mysql -h x -P y -p z通过连接器连接到mysql
2.查询缓存:mysql接收到查询请求后,先在查询缓存中查询,查询到了直接返回客户端;如果没有查到,则继续向下走,后续从存储引擎查到了再缓存到查询缓存中并返回客户端--类似与key-value,查询的SQL是key,结果集是value
- 不建议使用查询缓存,只要对一个表有更新,这个表上的所有查询缓存都会失效,查询缓存失效非常频繁,弊大于利。查询缓存适用于静态表,很长时间才更新一次。
查看是否启用查询缓存
show variables like '%query_cache%';
3.分析器:分析器分析词法,比如字符串user识别为user表,字符串id识别为id列。也进行语法分析,判断SQL语句是否满足mysql语法--要做什么
4.优化器:经过分析器之后mysql已经知道客户端要做什么了,但是怎样更好的去做需要经过优化器进行优化,优化器选择使用的索引、生成执行计划等--怎样去做
5.执行器:
1)调用存储引擎的接口取user表的第一行数据,然后判断id是否等于3,如果不是则跳过,如果是则添加到结果集中
2)调用存储引擎的接口取表的下一行数据,重复相同的判断逻辑,直到表的最后一行数据
3)将上述遍历后的结果集返回给客户端
一条SQL更新语句如何执行
update user set address='香港' where id=3;
1.表的更新查询缓存会失效
2.执行的流程:连接器-分析器-优化器-执行器-存储引擎。执行器与存储引擎的流程图如下所示,暗色表示存储引擎操作,亮色为执行器操作:
InnoDB是事务型存储引擎,可以看到最后有提交事务的操作。记录两个日志采用了两阶段提交。
3.这个过程涉及两个重要的日志redo log和binlog
redo log 重做日志
- 该日志是InnoDB存储引擎所独有的
- redo log是固定大小的。可以配置一组4个文件,每个文件1g,如下所示,check point表示当前擦除的位置,write pos是当前写的位置,阴影部分为可写的空闲区域。当write pos追上check point时就无法写入,需要停下来将check point往前推进。
- redo log记录的是在某个内存页上做了什么修改
- 当一条记录需要更新的时候,InnoDB引擎先把记录写到redo log里,同时更新到内存里,此时更新已经完成。InnoDB引擎在适当的时候会把操作记录更新到磁盘。
- redo log保证了数据库异常重启后,之前提交的记录不会丢失
binlog 归档日志
- 该日志是Mysql Server层的
- 文件是追加写入的
- 记录的是语句的原始逻
数据库事务
事务ACID-原子性、一致性、隔离性、持久性
多事务会出现的问题:
- 脏读-脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。
1.Mary的原工资为1000, 财务人员将Mary的工资改为了8000(但未提交事务)
2.Mary读取自己的工资 ,发现自己的工资变为了8000,欢天喜地!
3.而财务发现操作有误,回滚了事务,Mary的工资又变为了1000。像这样,Mary记取的工资数8000是一个脏数据。
- 不可重复读-是指在一个事务内,多次读同一数据,读到另一个事务修改后并提交的数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。
1.在事务1中,Mary 读取了自己的工资为1000,操作并没有完成
2.在事务2中,这时财务人员修改了Mary的工资为2000,并提交了事务.
3.在事务1中,Mary 再次读取自己的工资时,工资变为了2000
- 幻读-是指当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。
1.事务1,读取所有工资为1000的员工。
2.这时事务2向employee表插入了一条员工记录,工资也为1000
3.事务1再次读取所有工资为1000的员工 共读取到了11条记录,
如何解决上述问题,mysql支持的事务隔离性有以下4种:
- 读未提交--一个事务还没有提交,它做的变更就能被其它事务看到
- 读已提交--一个事务提交之后,它做的变更才会被其它事务看到。可以避免
- 可重复读--一个事务在执行过程中看到的数据,总是和这个事务在启动时看到的数据是一致的
- 串行化 --对于同一行记录,写会加写锁,读会加读锁,当出现读写冲突的时候,后访问的事务必须等前一个事务执行完成后,才能执行 查询当前数据库的隔离级别
show variables like 'transaction%';
并发控制
读写锁
读锁是共享的,线程相互不阻塞;写锁时排他的,一个线程持有写锁会阻塞其它线程的写锁和读锁。读锁又名共享锁,写锁又名排他锁。
锁策略
加锁是需要消耗资源的,包括获取锁、检查锁、释放锁都会增加系统的开销。如果系统花费大量时间来管理锁,而不是存取数据,那么系统的性能可能会因此受到影响。因此,在锁的开销和数据的安全性(影响性能)之间寻求平衡就是锁策略。
mysql支持的锁策略
表锁
开销最小,但是锁粒度大,支持的并发处理小。
行锁
只存在存储引擎层,在mysql服务器层没有实现。行锁粒度小,支持最大程度支持并发处理,但是锁开销大。
何时加锁
隐式锁定
InnoDB采用两阶段锁定协议。在事务执行过程中,根据隔离级别在需要的时候自动加锁,锁只有在执行commit或者rollback的时候才会释放,并且所有的锁是在同一时刻被释放。
显示锁定
InnoDB也支持特定的语句进行显示锁定
select ... lock in share mode --加共享锁 行锁
select ... for update --加排他锁 行锁
死锁
两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。当多个事务以不同的顺序锁定资源时,就可能会产生死锁,多个事务同时锁定同一个资源时,也会产生死锁。以下模拟两个事务以不同顺序锁定资源:
事务1
start transaction;
update user set address='香港' where id=1;
update user set address='香港' where id=2;
commit;
事务2
start transaction;
update user set address='澳门' where id=2;
update user set address='澳门' where id=1;
commit;
以上事务穿插执行可能会出现死锁,那么数据库是如何解决死锁问题的呢?数据库系统实现了各种死锁检测和死锁超时机制。InnoDB存储引擎采用死锁检测策略,检测到死锁的时候回立即返回一个错误,并将持有最少行级排他锁的事务进行回滚。
示例,序号表示操作顺序,特别注意步骤9和10。执行完步骤9后,会发现事务1一直阻塞;执行步骤10后,由于发生了死锁,InnoDB检测到了死锁,所以事务2立即输出了DeadLock的提示,并且回滚了事务。由于步骤10释放了锁,所以步骤9也不在阻塞了。以下执行流程也体现出了事务的隔离机制。
多版本并发控制MVCC
汇总表
如果引用在表中保存计数器,则在更新技术其时可能碰到并发问题。
新建计数表
CREATE TABLE `counter` (
`cnt` int(10) unsigned NOT NULL
)
计数
update counter set cnt=cnt+1;
--这条记录上有一个全局的互斥锁。这会使得想执行这条语句的事务只能串行执行
为获取更高的并发更新性能,可以将计数器保存在多行中,每次随机选择一行进行更新,修改表结构为
CREATE TABLE `counter` (
`slot` tinyint unsigned not null primary key,
`cnt` int(10) unsigned NOT NULL
)
先预先在表中增加200行数据,选择随机的槽(slot)进行更新
update counter set cnt=cnt+1 where slot=100*rand();
这样要想获取统计结果,需要使用以下查询
select sum(cnt) from counter;
需求改为每隔一段时间开始一个新的计数器,则表修改为
CREATE TABLE `counter` (
`day` date not null,
`slot` tinyint unsigned not null,
`cnt` int(10) unsigned not null,
primary key(day,slot)
)
这个场景就不能像前面一只羊预先生成,而用on duplicate key update代替
insert into counter(day,slot,cnt)
values(CURRENT_DATE, 100*rand(), 1)
on duplicate key update cnt=cnt+1;
如果希望较少表的行数,避免表变得太大,可以写一个周期执行的任务,合并所有结果到0号槽,并删除其他的槽
alter table
Mysql的alter table操作的性能对大表来说是个大问题。Mysql执行大部分修改表结构操作的方法是用心的结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表。这样的操作可能花费的时间很长,如果内存不足而表有很大,而且还有很多索引的情况下尤其如此。一般而言,大部分alter table操作将导致mysql在服务中断。
常见场景
- 先在一台不提供服务的机器上执行alter table操作,然后和提供服务的主库进行切换
- 根据源表结构创建一张和源表无关的新表,然后通过重命名和删除操作交换两张表,俗称影子拷贝 特殊场景举例
- 修改列的默认值,列的默认值存在表的.frm文件,所以可以直接修改这个文件而不需要动表本身
alter table user modify age int mot null default 18; --mysql的所有modify操作都会导致表重建
alter table user alter age set default 18;--直接修改.frm文件而不涉及表数据。这个can所非常快
索引
索引是存储引擎用于快速找到记录的一种数据结构。索引优化是查询性能优化的最有效手段,能轻易将查询性能提高几个数量级。
MySQL的索引在存储引擎层实现,而不是在服务器层实现。
主键索引 非主键索引 唯一索引 普通索引 覆盖索引
查询优化
问题
索引
索引是存储引擎用于快速找到记录的一种数据结构。索引优化是查询性能优化的最有效手段,能轻易将查询性能提高几个数量级。并且被建立索引的字段是有序的。
SQL执行时间长的原因
- 没有建立索引
- 建立的索引,但是索引失效
- 关联查询太多join
- 服务器调优及参数设置(比如缓冲、线程数)
特点
- 优势
提高了查询效率,减少了数据IO
降低了排序成本,减少了CPU消耗
- 劣势
索引文件占用内存空间
索引提高了查询速度,同时会降低更新表的速度。如insert/update/delete操作,如果更新索引列数据,mysql不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段
索引类型
单值索引:一个索引只包含一个列
复合索引:一个索引包含多了列
唯一索引:索引列的值必须是唯一的,但允许空值
B+树的检索原理
3层的B+树可以表示上百万的数据(1200^3=),如果上百万的数据查找只需要3次IO,性能提交将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常高。
创建索引的情况
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段,外键关系建立索引
- 单值索引/组合索引选择的问题,在高并发下倾向创建组合索引
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
不创建索引的情况
- 表记录太少
- 频繁更新的字段不适合创建索引,见劣势
- where条件利用不到的字段不创建索引
- 数据重复且分布平均的表字段,字段列包含许多重复的内容,建立索引没有太大的意义。比如性别,只有男、女。一个索引的选择性越接近1,这个索引的效率就越高
索引的选择性:指的是索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99。
性能分析
EXPLAIN
mysql> explain select * from user where id=2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 1 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.09 sec)
用于查询SQL执行计划
使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理SQL语句的,分析查询语句或者表结构的性能瓶颈。
能做什么
- 表的读取顺序
- 读取数据的查询类型
查询结果解析
id
id相同,执行顺序由上至下
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
select_type --查询类型
- simple 简单的select查询,查询中不包含子查询
- primary 查询中包含任何复杂的子部分,最外成被标记为
- subquery 在select或where列表中包含了子查询
- derived 在from列表中包含的子查询被标记为derived(衍生)。mysql会递归执行这些资产寻,把结果放在临时表里
- union 若第二个select出现在union 之后,则被标记为union;若union包含在from子句的子查询中,外层select也被标记为union
- union result 从uion表获取结果的select
table-显示数据是关于哪张表的
type-访问类型
从最好到最差依次是:
system>const>eq_ref>ref>range>index>all
一般来说,保证查询至少达到range级别,最好能达到ref
- system 这是const类型的特例,平时不会出现可以忽略
- const 表示通过索引一次就找到了,const用于primary key或者unique索引。因为只匹配一行数据,所以很快。
- eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键索引或唯一性索引扫描。