-
最上层,客户端
连接处理、授权认证、安全
-
中间层,核心服务
查询解析、分析、优化、缓存、内置函数、存储引擎、触发器、视图
-
最下层,存储引擎
存储引擎负责mysql中数据的存储和提取
- 每个客户端连接都会在服务器进程中拥有一个线程
- MySQL会解析查询,并创建内部数据结构(解析树),然后对其进行各种优化,包括重写查询、决定表的读取顺序,以及选择合适的索引
- 读锁又叫共享锁,多用户可以同一个时刻读取同一个资源,而互不干扰
- 写锁又叫排它锁,同一个时刻只能有一个用户读写数据
- 表锁,开销最小的策略
- 行锁,最大程度支持并发处理,同时开销极大
事务就是一组原子性的sql查询,事务内的语句,要么全部执行,要么全部执行失败
start transaction;
xxx
rollback;
commit;
ACID特性:
-
原子性 A
事务要不就在执行中,要不然就是成功或者失败的状态
-
一致性 C
执行事务过程中,所有对数据库写入的操作都应该是合法的,并不能产生不合法的数据状态
-
隔离性 I
一个事务结束前,对另一个事务是不可见的
-
持久性 D
一旦事务被提交,那么数据一定会被写入到数据库中并持久存储起来。
-
未提交读 READ-UNCOMMITTED RU
事务可以读取未提交的数据,会产生脏读
-
提交读 READ-COMMITTED RC
不可重复读,一个事务从开始到提交之前,所做的任何修改其他事物都是不可见的
-
可重复读 REPEATABLE-READ RR
mysql
默认
隔离级别,会产生幻读,意思是当事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录 -
可串行化 SERIALIZABLE
最高隔离级别
指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用资源,从而导致恶性循环的现象
简单解决办法:将持有最少行级排他锁的事务进行回滚
可以认为MVCC是行级锁的一个变种,并且在很多情况下避免了加锁操作,因此开销更低
MVCC是通过保存数据在某个时间点的快照实现的
隐藏列,创建时间和过期时间,是系统版本号
show table status like 'users'\G
*************************** 1. row ***************************
Name: users
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 2
Create_time: 2019-03-06 22:15:42
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_unicode_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.05 sec)
- 吞吐量
- 响应时间或者延迟
- 并发性
- 可扩展性
- 。。。
- 更小的通常更好
- 简单更好
- 尽量避免NULL
有符号范围:-2^(n-1)到2^(n-1)-1
无符号unsigned范围:0到2^(n-1+1)-1
注意: int(10)
和int(1)
只是规定了交互工具
来显示�字符的个数,对于存储和计算
来说,是一样的
类型 | 存储空间(字节) |
---|---|
tinyint | 1 |
smallint | 2 |
mediumint | 3 |
int | 4 |
bigint | 8 |
精确运算用decimal
类型
类型 | 存储空间(字节) |
---|---|
decimal | 9【小数点左边4字节,小数点1字节,小数点右边4字节】 |
float | 4 |
double | 8 |
类型 | 存储空间(字节) |
---|---|
varchar | n |
char | n |
blob | n |
text | n |
enum | n |
- from_unixtime():将时间戳转换成日期
- unix_timestamp():将日期转换成时间戳
类型 | 存储空间(字节) |
---|---|
timestamp | 4 |
datetime | 8 |
范式的优点:
- 更新操作更快
- 很少的重复数据,只需要修改更少的数据
- 表通常小,执行操作更快
- 很少冗余数据,更少需要distinct或者group by操作
范式的缺点:schema通常需要关联
反范式的优点:避免关联
索引是存储引擎用于快速找到记录的一种数据结构
索引类型:
- B-Tree(技术上说B+Tree)
- 哈希索引
索引的优点:
- 大大减少了服务器需要扫描的数据量
- 可以帮助服务器避免排序和临时表
- 可以将随机I/O变为顺序I/O
select a_id from xx where a_id + 1 = 5;
以上sql无法自动解析,所以不会走索引
select count(*) as cnt, left(city, 7) as pref from xxx group by pref order by cnt desc limit 10;
选择合适的索引长度
索引合并
最左前缀原则
聚簇索引
并不是一种单独的索引类型,而是一种数据存储方式
数据行实际上存放在索引的叶子页中,术语叫聚簇
索引列包含的是整数值
- myisam主索引和次索引都指向物理行,比如id指向了物理行,由索引到磁盘拿数据(回行)
- innodb在主索引行上直接存储行的数据,称为聚簇索引,次索引指向主索引,比如id行包括了name、age等等数据,name包括了id
索引覆盖:查找的字段正好是索引,速度快
延迟关联:让一部分数据走索引,比全表扫描好