高性能MySQL
1. MySQL架构与历史
1.1 MySQL逻辑架构
客户端–> 连接处理(授权认证、安全)–> 查询、解析、优化、缓存(存储过程、触发器、视图)–> 存储引擎(负责存储和提取)
1.1.1 连接管理与安全性
每个客户端连接会在服务器进程中拥有一个线程,并且服务器会缓存线程,不用每次新建连接新建或销毁线程;
当应用连接MySQL服务器时,需要基于用户名,原始主机信息和密码进行认证,使用SSL连接,可以使用证书认证,连接成功后还会验证是否具有执行权限;
1.1.2 优化与执行
MySQL会解析查询,创建内部数据结构,然后进行优化;
优化方式:
- 重写查询
- 决定表的读取顺序
- 选择合适的索引
- 使用优化器
1.2 并发控制
当有多个查询需要在同一时刻修改数据,就会产生并发问题;
1.2.1 读写锁
读锁是共享锁,互不干扰;
写锁是排他锁,会阻塞其他的读锁或写锁,安全,优先级更高;
1.2.2 锁粒度
提高并发性的方式是让锁定对象更加有选择性,在锁的开销和数据的安全性之间寻求平衡;
- 表锁
开销最小,ALTER TABLE - 行锁
开销最大,InnoDB
1.3 事务
START TRANSACTION 开启事务
COMMIT提交事务
ROLLBACK回滚事务
了解事务的原子性ACID
- 原子性
要么全部成功提交,要么失败回滚 - 一致性
从一个一致性状态到另一个一致性状态 - 隔离性
在最终提交前对其他事务是不可见的 - 持久性
事务提交后,修改则会永久的保存到数据库中
1.3.1 隔离级别
- READ UNCOMMITED 读未提交 (脏读)
可以读取未提交的数据 - READ COMMITED 读提交(大部分数据库默认级别)
执行两次同样的查询,结果可能不一样 - REPEATABLE READ 重复读(mysql)
保证在多次读取同样记录的结果是一样的,可能造成幻读 - SERIALIZABLE 可串行化
隔离级别最高,强制串行,解决幻读,每一行都加锁
1.3.2 死锁
两个或多个事务在同一资源上占用,并请求锁定对方占用的资源即死锁;或多个事务同时锁定同一资源,也会产生死锁;
可以通过死锁检测和死锁超时机制,解决方式:
- 放弃锁请求(不好);
- InnoDB将持有的最少行级排他锁的事务进行回滚;