mysql笔记
mysql笔记
死锁
死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象.
理解
A事务想要锁定的资源被其他事务占用,而其他事务也想锁定A事务占用的资源,形成循环的时候就是死锁.
解决
解除死锁需要有外部因素介入.
InnoDB处理死锁的方法
将持有最少行级排他锁的事务进行回滚.
事务日志
事务日志可以帮助提高事务的效率.使用事务日志,存储引擎在修改表的数据时只需要修改其内存拷贝, 再把该修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁盘.
理解
事务日志类似于I/O缓冲区.
innodb_flush_log_at_trx_commit = 2 // 写入日志并返回,然后每秒同步,可能会丢数据.
事务
MySQL默认采用自动提交(autocommit)模式.
在自动提交模式下,一个事务只包含一条语句.
当设置为不自动提交时,可以将一组语句做为一个事务,从而保证这一组语句都执行成功才做持久化写入.
在事务中使用混合存储引擎,非事务型的存储引擎无法回滚.
事务的执行核心是锁定资源,默认情况下都是隐式锁定,当然你也可以显示锁定.
select ... lock in share mode;
select ... for update
自动提交与事务的区别
// 是指不自动提交,但不支持回滚
autocommit
// 不自动提交,支持回滚
start transaction
多版本并发控制
多版本并发控制(MVCC)是乐观锁的一种实现,它的目的是避免加锁操作.
mysql使用MVCC实现了非阻塞的读操作,写操作为行级锁.
非锁定语句使用快照处理,这个快照就是MVCC中的一个时间版本,从而保证了一个事务多次查询的数据是一致的.
性能优化
性能优化的原则,减少响应时间,减少资源消耗.
开启慢日志
// 查询慢日志功能是否开启,off代表未开启
show variables like 'slow_query_log%';
// 查看慢日志保存的位置
show variables like 'slow_query_log_file%';
// 查看慢日志记录的阀值
show variables like 'long_query_time%';
// 开启慢日志功能
set global slow_query_log='ON';
// 设置超过1秒就写入慢日志
set global long_query_time=1;
数据类型
考虑的点:
运算性能,比如数字运算优于字符串运算.
磁盘内存,较小的数据类型,磁盘空间占用少,内存占用少.
cpu时间片,复杂的运算需要消耗更多的cpu时间片.
关于null
将字段为null的改为not null带来的性能提升较小.
InnoDB使用单独的位(bit)存储null值,对于稀疏数据有很好的空间效率.
设置为null的字段在搜索,统计时语句复杂度会提高.
varchar需要使用1或2个字节记录字符串的长度,长度大于255则要2个字节.
// varchar(20)和varchar(200)来保存"Moments"的区别.
mysql会分配固定大小的内存块如200或20来保存内部值,指定的大小决定了内存的使用效率.
text类型,当text的值太大时,InnoDB会使用专门的外部存储区域来进行存储, 此时每个值在行内需要1~4个字符存储一个指针,然后在外部存储区域存储实际的值.
位操作:insert into docs (type) values (b'00111100');
mysql字段自定义排序
select id from docs where id in (3,1,2) order by field(id, 3, 1, 2);
关联表的问题
mysql限制了每个关联操作最多只能有61张表.
关联表操作可能会导致索引失效.
关联表可能会导致索引开销,列如果在同一个表中可能可以使用同一个索引.
缓存表和汇总表和视图
有时提升性能最好的方法是在同一张表中保存衍生的冗余数据.
比如冗余一些排序和搜索需要的字段.
比如每次更新就更新一下统计字段,这样可以较好的提供实时数据.
视图可以理解为一个中间表,将所需要的字段组合起来,动态更新维护.
计数器表
如果一张表的某行写入量很大,可能会出现并发问题,因为每一次写入都要上锁的,可以使用多行解决.
比如计数功能,用多行记录,随机更新某一行,要看结果时累加所有值.
表增加字段的操作
对于大表进行字段增加或者添加索引,会是一个耗时的过程,如果数据量过于大怎么办?
切库:在新库中同步所有数据并更改完成后切换.
切表:复制现有表,然后重命名交换两张表.
索引
InnoDB使用了B+Tree.
存储引擎快速查找记录的理念基于块,块指一个数据量的范围.
当小于块时,全表查找效率更高.
当符合块时,索引的优势就很好.
当超出块时,应该将块分隔成合适的块大小,然后再使用索引的优势.
如分表,当数据达到TB级,直接用索引意义不大的,要先分表让数据量保持在合理的范围再使用索引.
少用函数,最左匹配.
聚簇索引
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式.
InnoDB中,它的数据行实际上存放在聚簇索引的叶子页中.
一个表只能有一个聚簇索引,通常是主键.
二级索引(非聚簇索引),它的叶子结点会存储聚簇索引的主键行指针.所以二级索引需要查询二次才能拿到记录.
排序中的索引
只有当索引的列顺序和order by子句的顺序完全一致(最左匹配原则),并且所有列的排序方向(asc,desc)都一样时,mysql才能够使用索引来对结果做排序.
如果查询需要关联多张表,则只有当order by子句引用的字段全部为每一个表时,才能使用索引做排序.
为第一列提供常量条件,从而使基满足最左匹配原则.
index (a,b,c)
select a,b,c from docs where a=1 order by b,c;
索引与锁
使用了索引的情况下,可以减少mysql锁定的行数,前提是要能明确找出要锁定的行.
查询优化
优化的方式,消除不必要的子任务,减少子任务的执行次数,让子任务运行的更快.
大部分性能你下的查询都可以通过减少访问的数据量来达到优化的效果.
减少访问的数据行,减少访问的数据列.
减少访问的数据列,可以减少I/O,内存和CPU的消耗.
重复的查询可以写入缓存,需要数据时直接从缓存取出,这样性能会明显提高.
回表
二级索引如果要取相关的列,需要根据主键的id再查询一次.
索引覆盖
二级索引会存储主键的id,所以二级索引其实是组合索引,即二级索引+主键id.
分块执行,涉及的记录行太多
mysql从设计上让连接和断开都很轻量级,在返回一个小的查询结果方面很高效,这给语句拆分带来可行性.
分而治之,将一个大的锁定语句改成分块执行,每执行一块暂停一会(释放锁,让其他事务获取锁机会),执行时间拉长,服务器的压力自然减小.
分而治之,涉及的连表数太多
将一条连表查询语句改成多条单表执行语句.
先取出需要的id,再去相应的表中拉取数据.
这样做的优势
让查询缓存的效率更高,减少锁的竞争,减少了要访问的数据行.
关联查询优化
在mysql中,每一个查询,每一个片段(子查询,甚至基于单表的select)都可能是关联.
mysql对任何关联都执行嵌套循环关联操作,即mysql先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行.
目标:小表驱动大表.
// t1表返回的结果要少些
explain
select * from t1
straight join t2
on t1.t2_id = t2.id
大部分情况下mysql可以很好的优化,某些情况下会失效,需要自己指定驱动表(小表).小表指返回的结果数少.
排序优化
无论如何排序都是一个成本很高的操作,从性能角度考虑,尽可能避免排序或者尽可能避免对大量数据进行排序.
如果order by子句中的所有列都来自关联的第一个表,那么mysql在关联处理第一个表的时候就进行文件排序,可以用到索引. 除此之外,mysql都会先将关联的结果存放到一个临时表中,然后在所有的关联结束后,再进行文件排序,因为在临时表排序,无法使用索引. 如果查询中有limit的话,也是在排序之后再应用,虽然返回的数据有限制,但临时表和需要排序的数据量仍然会非常大.
分页优化
我们通常会使用limit加上偏移量的办法实现分页功能,同时加上合适的order by子句.
在偏移量非常大的情况下如何优化:尽量少回表,算出边界值.
问题:分页慢是offset的问题,它会导致mysql扫描大量不需要的行然后再抛弃掉.
大部分的情况下都只能是减少回表,使用覆盖索引来处理.
算出边界值的方法,需要单调递增的条件.
// title上有索引,但是二级索引需要回表
select id, title from docs order by title limit 100000, 5;
// 延迟关联,使用了覆盖索引(title,id)扫描,而不是查询所有的列.
select docs.id, docs.title from dcos
inner join (select id from docs order by title limit 100000, 5) as tmp using (id);
// 直接使用边界值,某些情况下适用
select id, title from docs where id between 100000 and 100005 order by id;
// 使用索引直接过滤
select id, title from docs where id > 100000 order by id limit 5;
分区
一个表最多只能有1024个分区.
如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来.
分区表中无法使用外键约束.
语句执行,先找到分区,然后再执行操作,和分表一样,先使用路由算法定位表,然后再操作.
null值会使分区过滤无效!
查询是过滤掉不需要的分区.