一、定位慢查询
定位慢查询很简单,主要有以下方式:
- 通过运维工具分析
- 开启mysql慢日志
在mysql的配置文件
/etc/my.cnf
中配置并开启慢日志功能# 开启MySQL慢日志查询开关 slow_query_log=1 # 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志 long_query_time=2
- 分析某一条具体的sql语句
EXPLAIN/SELECT 字段列表 FROM 表名 WHERE 条件 ;
执行结果:
通过key和key_len检查是否命中了索引(索引本身存在是否有失效的情况)
通过type字段查看sql是否有进一步的优化空间,是否存在全索引扫描或全盘扫描
通过extra建议判断,是否出现了回表的情况,如果出现了,可以尝试添加索引或修改返回字段来修复
索引
- 索引是帮助MySQL高效查找数据的满足特定查找算法的
数据结构
- 索引的底层数据结构是
B+树
聚集索引和二级索引
分类 | 含义 | 特点 |
---|---|---|
聚集索引(Clustered Index) | 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 |
必须有,而且只有一个 |
二级索引(Secondary Index) | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 |
可以存在多个 |
聚集索引选取规则:
- 当存在主键或唯一索引时,优先使用主键或第一个唯一索引作为
聚集索引
- 如果主键和唯一索引都没有,InnoDB会自动生成一个
rowid
作为隐藏的聚集索引
回表查询:
通过二级索引查找到对应
主键值
,然后再根据主键值
查询整行数据,这个过程叫做回表
覆盖索引:
指查询使用了索引,并且所有需返回的
字段
全部能在该索引
中直接找到
联合索引:
指由多个字段共同构成的索引
索引失效情况
最左前缀匹配法则:
创建了联合索引(a,b,c)后,只有当前面的字段相同时,才会去比较后面的字段,优先按照联合索引最左字段进行匹配
索引失效情况:
① 如果不遵循最左匹配原则,则可能会导致索引失效
- where b='' c='': 此时索引全部失效
- where a='' c='':此时仅有索引a生效
② 范围查询右边的列,不能使用索引
- where a='' b>'': 此时c索引失效
③ 在索引字段上进行运算,索引也会失效
④ 字符串不加单引号,索引也会失效(造成类型转换)
⑤ 模糊查询
%xxx
模式,也会导致索引失效
SQL优化经验
1.表的设计优化,数据类型的选择
2.索引优化,索引创建原则
3.sql语句优化,避免索引失效,避免使用select * ….
4.主从复制、读写分离,不让数据的写入,影响读操作
5.分库分表
事务
- 并发事务可能出现的问题
- 脏读
- 不可重复读
- 幻读
- 解决方案 —— 事务的隔离级别
- 读未提交
- 读已提交:只能解决
脏读
- 可重复读:只能解决
脏读
、不可重复读
- 串行化:全部解决
MySQL日志
- redo log:记录的是数据页的物理变化,当服务器宕机或其他情况导致数据不一致时,可用来同步数据,保证了事务的
持久性
- undo log:记录的是逻辑日志,当事务回滚时,通过逆操作来恢复数据,保证了事务的
原子性
和一致性
保证事务的隔离性
- 排他锁:用于数据修改操作,确保不会同时同一资源进行多重更新
- MVCC(多版本并发控制):指维护一个数据的多个版本,使得读写操作没有冲突
- 隐式字段
①trx_id(事务id),记录每一次操作的事务id,是自增的
②roll_pointer(回滚指针),指向上一个版本的事务版本记录地址
- undo log
①回滚日志,存储老版本数据
②版本链:多个事务并行操作某一行记录,记录不同事务修改数据的版本,通过roll_pointer指针形成一个链表
readview
根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据
不同的隔离级别快照读是不一样的,最终的访问的结果不一样
RC :每一次执行快照读时生成ReadView
RR:仅在事务中第一次执行快照读时生成ReadView,后续复用
数据库主从同步原理
①主库在事务提交时,会把数据变更记录在二进制日志文件 Binlog 中。
②从库读取主库的二进制日志文件 Binlog ,写入到从库的中继日志 Relay Log 。
③从库重做中继日志中的事件,将改变反映它自己的数据
分库分表
- 水平分库
- 水平分表
- 垂直分库
- 垂直分表