MYSQL - ( 一 )
事务隔离级别
- read-uncommitted 三种问题都有
- read-committed 不可重复读、幻读、
- repeatable-read 幻读(加上
MVCC可防止这个问题) - serializable 无
1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，
导致事务A多次读取同一数据时，结果 不一致。
3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，
但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，
就好像发生了幻觉一样，这就叫幻读。
注意 不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。
解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
分布式集群 和 主从,怎么样保证节点数据一致 ?
同步复制:全部写成功了,才返回用户成功(分布式集群)
异步复制:产生binlog,但不能保证从库一定能接收到(mysql主从),主库挂了,从库数据丢失
半同步复制: 至少有一个从库可以执行成功
mysql内部复制方式结构
[mysql主从结构] --> 5.5-单线程 5.6-多线程
Bin-log/relay log
行模式
buffer pool
Mysql升序排序速度大于降序排序速度，因为page之间是双链表，page内部是单向链表
一个page 最大16k，至少要保证有两块数据（因为双向列表），所以一条数据最大只能是8k。单条数据过大的时候，会申请额外内存，page中存储部分数据+额外内存地址
覆盖索引
等值索引在前，范围索引在后，比较好
根据索引估计出的行数大于总行数的30%的时候，会走全表，因为全表是顺序读取，根据索引读取有随机读取，索引在行数大于30%的时候全表更快
Force index(idx_value)可以强制指定使用某个索引，常在执行计划中使用，生产慎用，因为在数据量改变的时候，固定使用某个索引可能不是最优的，但强制之后，mysql无法自行优化
Ps: use index(索引1，索引2，索引3)，可以让mysql在以上三个索引中选取索引进行查询
In 条件可以命中索引。
使用in查询的时候，mysql内部有一个参数5.5的时候是10，5.6之后是一个接近100的值，
当in里面的数据小于该值的时候，mysql会逐条根据索引分析，然后相加，
确定索引覆盖数据是否小于总数据的30%，若小于，则走索引；若in里面数据大于该值的时候，
会根据索引分布估计一个覆盖值出来，并决定是否使用索引。
explain extended 可以获取优化后的sql语句
PXC
MGR
b+树结构
- 节点(page) 之间双向链表,节点(page) 里面是单项链表
- 升序比降序效率高,同一个节点内可能要做反转的;
- 2个 < page.size < 16K
索引建立规则
- 字段区分度高的-->比如:state见索引就不好,b+tree退化成有序链表
- 联合索引:区分度高的放前面
-- 等值查询
select a from b where staff_id=1 and customer_id=589;
- INDEX idx_staff(staff_id)+INDEX idx_customer(customer_id)
这种情况在 5.5 只能用一个索引,在 5.6 加了index merge, 会使用两个索引交集 - INDEX idx_staff_customer(staff_id, customer_id)
- INDEX idx_customer_staff(customer_id, staff_id)(最好的方式)
区分度高的字段放前面,sql语句and的条件顺序没影响.
-- 范围查询
SELECT f1 FROM t1 WHERE customer_id = 2 AND payment > 10
- INDEX idx_payment_customer(payment, customer_id)
第一种会查出>10 然后过滤=2 - INDEX idx_customer_payment(customer_id, payment))(最好的方式)
- 避免冗余索引
- 前缀索引
- 避免索引过多:写入要更新b+tree
sql优化
全表扫描 or 索引扫描
如果扫描行数超过30%那就全表扫描,直接走主键,认为随机访问代价较高链表查询
关联字段创建索引,如果关联表没有建立索引,那么就会全表扫描的批量写入的写法
INSERT INTO t(id, name) VALUES(1, 'Bea');
INSERT INTO t(id, name) VALUES(2, 'Belle');
INSERT INTO t(id, name) VALUES(3, 'Bernice');
网络开销大
INSERT INTO t(id, name) VALUES(1, 'Bea'), (2, 'Belle'), (3, 'Bernice');
网络开销小,数量大,执行时间长,死锁概率高,回滚时间长(大小控制在几十左右,经验判断)
避免大事务
避免事务内的外部依赖,比如mq相应超时,如果当前记录与其他查询操作有阻塞,那么服务将长时间不可用死锁,排序基本上能解决
-
分页查询
SELECT id FROM t LIMIT 10000, 10; 需要查询10010条记录,拿最后的10条SELECT id FROM t WHERE id > 10000 LIMIT 10 通过额外条件过滤,就不需要扫描全部记录.
truncate drop delete
MySQL5.6版本以下：使用truncate table + drop table 替代 drop table
MySQL5.6版本+ ： 直接使用drop table
为什么要设置不为null,并且要给定默认值.
Null 列需要更多的存储空间：需要一个额外字节作为判断是否为 NULL 的标志位,
如果一个!=null的值插入进来,那么page的大小会改变,会发生重平衡.
使用null是想让数据库做校验,但是数据库是处理存储的,这种校验的逻辑放到上层做才对.
