高性能MySQL
一、mysql简介
1、mysql逻辑架构
最上层,客户端
连接处理、授权认证、安全
中间层,核心服务
查询解析、分析、优化、缓存、内置函数、存储引擎、触发器、视图
最下层,存储引擎
存储引擎负责mysql中数据的存储和提取
2、并发控制
每个客户端连接都会在服务器进程中拥有一个线程
MySQL会解析查询,并创建内部数据结构(解析树),然后对其进行各种优化,包括重写查询、决定表的读取顺序,以及选择合适的索引
读锁又叫共享锁,多用户可以同一个时刻读取同一个资源,而互不干扰
写锁又叫排它锁,同一个时刻只能有一个用户读写数据
表锁,开销最小的策略
行锁,最大程度支持并发处理,同时开销极大
3、事务
事务就是一组原子性的sql查询,事务内的语句,要么全部执行,要么全部执行失败
start transaction;
xxx
rollback;
commit;
ACID特性:
原子性 A
事务要不就在执行中,要不然就是成功或者失败的状态
一致性 C
执行事务过程中,所有对数据库写入的操作都应该是合法的,并不能产生不合法的数据状态
隔离性 I
一个事务结束前,对另一个事务是不可见的
持久性 D
一旦事务被提交,那么数据一定会被写入到数据库中并持久存储起来。
4、隔离级别
未提交读 READ-UNCOMMITTED RU
事务可以读取未提交的数据,会产生脏读
提交读 READ-COMMITTED RC
不可重复读,一个事务从开始到提交之前,所做的任何修改其他事物都是不可见的
可重复读 REPEATABLE-READ RR
mysql默认隔离级别,会产生幻读,意思是当事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录
可串行化 SERIALIZABLE
最高隔离级别
5、死锁
指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用资源,从而导致恶性循环的现象
简单解决办法:将持有最少行级排他锁的事务进行回滚
6、多版本并发控制 MVCC
可以认为MVCC是行级锁的一个变种,并且在很多情况下避免了加锁操作,因此开销更低
MVCC是通过保存数据在某个时间点的快照实现的
隐藏列,创建时间和过期时间,是系统版本号
7、存储引擎
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)
二、mysql测试和性能
吞吐量
响应时间或者延迟
并发性
可扩展性
。。。
三、mysql数据类型
1、选择优化的数据类型
更小的通常更好
简单更好
尽量避免NULL
2、整数类型
有符号范围:-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
3、实数类型
精确运算用decimal类型
类型
存储空间(字节)
decimal
9【小数点左边4字节,小数点1字节,小数点右边4字节】
float
4
double
8
4、字符串类型
类型
存储空间(字节)
varchar
n
char
n
blob
n
text
n
enum
n
5、日期和时间类型
from_unixtime():将时间戳转换成日期
unix_timestamp():将日期转换成时间戳
类型
存储空间(字节)
timestamp
4
datetime
8
6、范式与反范式
范式的优点:
更新操作更快
很少的重复数据,只需要修改更少的数据
表通常小,执行操作更快
很少冗余数据,更少需要distinct或者group by操作
范式的缺点:schema通常需要关联
反范式的优点:避免关联
四、mysql高性能索引
1、索引基础
索引是存储引擎用于快速找到记录的一种数据结构
索引类型:
B-Tree(技术上说B+Tree)
哈希索引
索引的优点:
大大减少了服务器需要扫描的数据量
可以帮助服务器避免排序和临时表
可以将随机I/O变为顺序I/O
2、高性能索引策略
独立的列
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
索引覆盖:查找的字段正好是索引,速度快
延迟关联:让一部分数据走索引,比全表扫描好
五、mysql查询性能优化
六、mysql高级特性
七、参考资料