- 索引规则
- 全值匹配我最爱
- 最佳左前缀法则:如果索引了多列,要遵循最佳左前缀法则,指的是查询从索引的最左前列开始`并且不跳过索引中的列(火车头,中间车厢不能断,不然会缺少部分索引) 。
- 不在索引列上做任何操作(计算、函数、(手动或者自动)类型转换),会导致索引失效。
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
- mysql在使用不等于!= 或者<> 的时候无法使用索引会导致索引失效。
- is null,is not null 也无法使用索引
- like以通配符开头('%abc...')mysql索引会失效
- 字符串不加单引号索引会失效
- 少用or,用它连接时索引失效。
【优化总结口诀】
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
Like百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用;
VAR引号不可丢,SQL高级也不难!
explain
id:
type:
key:
rows 越少越好
extra:
- Using filesort: 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作成为 ’文件排序‘
- Using temporary 使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于排序order by 和分组查询group by
- Using index:表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率不错!如果同时出现using where,表明索引被用来执行索引键值的查找(就是有where),如果没有同时出现Using where 说明只在select后出现没有where筛选。索引覆盖:就是select的字段刚好是索引。
索引
- 单表索引: 可以加复合索引在需要筛选和查询的字段,注意顺序,range 筛选会导致索引失效。
尤其是大于> 符号。 - 双表索引:left join 时将索引加在右边的表上,因为左边的表是全部匹配。同理,right join时将索引加在左表上,因为右表是全部匹配。
- 三表查询: 将索引加在非全部匹配的表上。
优化原则:
-
小表驱动大表
image.png -
group by 实质是先排序后进行分组,遵循最佳左前缀原则。
3.慢日志查询
查看慢日志是否开启,一般除非调优,不建议开启,因为多多少少会影响性能。
image.png
开启(本次生效,mysql重启后无效。除非改mysql.conf)
image.png
show variables like '%long_query_time%'
// 查询慢sql设置的时间
set global long_query_time=3
// 设置新值, 不过需要新开会话生效
mysql锁机制
表锁-偏读锁
show status like “table%”
show open tables
lock table mylock read
读锁后 当前会话不能改mylock表 不能改其他表 可以读mylock表 不能读其他没有加锁的表;其他会话可以读mylock表 可以查其他没有加锁的表 更新mylock表会阻塞 直到上个读锁释放
unlock tables 解锁
lock table mylock write
当前会话可以读写mylock 不能读写其他表;其他会话读mylock 会阻塞
unlock tables
简而言之 读锁会阻塞写 写锁会阻塞读写!
行锁-偏写锁
支持事务
acid 原子性atomocity 一致性 consistent 隔离性 isolation 持久性 durable
事务的隔离级别
mysql的默认级别是可重复读
有索引没有用 索引失效导致行锁变表锁(比如 where 字符串 不加引号)
间隙锁的危害
间隙锁:
范围条件检索数据,innoDB会给符合条件的已有数据记录的索引项加锁,对于键值在条件范围内并不存在的记录,叫做“间隙”(GAP);
危害:因为Query执行过程中通过范围查找的话,会锁定整个范围,即使这个id不存在,造成在锁定的时候无法插入锁定id范围内的任何数据。在某些场景下可能会对性能造成很大伤害。
锁一行
select * from mylock where id = 8 for update 其他操作会被阻塞。
行锁分析 show status like "innodb_row_lock&";
Innodb_row_lock_current_waits 7 // 当前
Innodb_row_lock_time 1116539980 // 等待总是厂
Innodb_row_lock_time_avg 250 平均锁时长
Innodb_row_lock_time_max 51693
Innodb_row_lock_waits 4454269 //等待总次数
总结
- 尽可能数据检索都通过索引
- 合理设计索引,缩小锁的范围
- 减少检索条件,避免间隙锁
- 尽量控制事务大小,减少锁定资源量和时间长度
- 尽可能低级别事务隔离
页锁
主从复制
mysql复制过程分成三步
- master 将改变记录到二进制日志,这些记录过程叫二进制日志事件binary log events
- slave 将master的binary log events 拷贝到它的中继日志relay log
- slave重做relay log中的事件,将改变应用到自己的数据库中,这个过程是异步且串行的
网友评论