美文网首页
mysql高级

mysql高级

作者: wsj1211 | 来源:发表于2022-08-10 13:35 被阅读0次
    • 索引规则
    1. 全值匹配我最爱
    2. 最佳左前缀法则:如果索引了多列,要遵循最佳左前缀法则,指的是查询从索引的最左前列开始`并且不跳过索引中的列(火车头,中间车厢不能断,不然会缺少部分索引) 。
    3. 不在索引列上做任何操作(计算、函数、(手动或者自动)类型转换),会导致索引失效。
    4. 存储引擎不能使用索引中范围条件右边的列
    5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
    6. mysql在使用不等于!= 或者<> 的时候无法使用索引会导致索引失效。
    7. is null,is not null 也无法使用索引
    8. like以通配符开头('%abc...')mysql索引会失效
    9. 字符串不加单引号索引会失效
    10. 少用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时将索引加在左表上,因为右表是全部匹配。
    • 三表查询: 将索引加在非全部匹配的表上。

    优化原则:

    1. 小表驱动大表


      image.png
    2. 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复制过程分成三步

    1. master 将改变记录到二进制日志,这些记录过程叫二进制日志事件binary log events
    2. slave 将master的binary log events 拷贝到它的中继日志relay log
    3. slave重做relay log中的事件,将改变应用到自己的数据库中,这个过程是异步且串行的

    相关文章

      网友评论

          本文标题:mysql高级

          本文链接:https://www.haomeiwen.com/subject/zluwwrtx.html