vlambda博客
学习文章列表

Mysql结构

Extra 说明 ： 

usingfilesort  额外查找   对于单索引 来说如果排序和查找是同一个字段 则不会出现 

如果排序和查找不是一个字段 则可能出现 usingfilesort ，原因是索引的的顺序被其他排序条件所打乱，需要额外消耗一次查询。

usingfilesort  复合索引不能跨列 （最佳左前缀） 多个索引 跨列 就会出现usingfilesort 

如何避免 ：where 那些字段 就order by 那些字段 符合 where 和 orderby按照复合索引的顺序使用 不要跨列不要跨列或者无序使用。

Using temporary ：性能损耗比较大 已经有表了但是不使用  查询时用到了临时表 

常见于group by 语句中一般是根据某个字段查 却根据其他字段分组 就会出现。

如何避免 查询那些列 就根据那些列 group by

 原因 ：不读取源文件 只从索引中获取数据 不需要回表查询 

结论：如果用到了索引 存在 where 条件 name 预测索引 和实际索引都会存在  如果用到了索引，但是没有where 条件 name预测索引中将不会存在 但是实际key还是存在

假设age是索引列 但是查询 select age,name 如果要查name 必须回原表进行查询

例：select * from teacher  where tid=1 and tid =2

优化示例

假设有一个sql 

select * from userwhere id=x and name =x and age =1 order by id,name,age

则所有索引全部使用 不会存在 中间表以及 额外查找操作 如果部分一直 则使用部分索引

和新 where 和order by 不要跨列

单表优化

• 优化第一阶段 加索引  根据sql实际解析的顺序 调整索引的顺序

• 索引一旦升级优化 及时删除不用的索引 防止干扰

• 再次优化 将 in的顺序放到最后 重新调整 索引顺序

• 小结  索引不能跨列使用 最佳左前缀

保持索引的顺序一致性  索引需要逐步优化  将含in的条件查询（可能失效）放到最后

多表优化

    索引往哪个表加？ 小表驱动大表

    索引建立在经常使用的字段上  小表中的字段 需要使用300次

    一般情况 对于左外连接 给左外加索引

Select .... where 小表 .x10 = 大表.x300

下方是两个代码 虽然他们都是运行了3000次  猜猜谁更快呢

For(int i=0 ;i<x小表.length10 ;i++){    For(int j=0;j<大表.length300;j++){      3000次 }}
For(int i=0 ;i<x大表.length300 ;i++){    For(int j=0;j<小表.length10;j++){      3000次 }}

小结

程序有个设计原则 一般来说 多的循环体 放里面   外层越小 内层大 程序速度快

     将数据量小的表放左边  Where 条件中 一般是需要加索引的  

  多表优化

小表驱动大表原则  将小的表放在左边

常用字段加索引原则  索引建立在经常使用的字段上

避免索引失效 优化失效的一些原则   复合索引

A. 复合索引的时候 不要跨列或无序使用 （最佳左前缀）

     正例 （a,b,c） Where a .. and b  order by c

B. 尽量使用全索引匹配   （a,b,c） 不要建了3个 只用两个 尽量全用上

C. 不要再索引上进行任何操作  在索引上进行任何操作 则索引失效 

（计算 函数 类型转换）  不要 select a.x*3 =x   此项操作会使索引失效

 结论： 对于复合索引 如果左边失效 右侧全部失效  最佳左前缀原则

     如果不是复合索引 则左边失效 不会影响右边

D. 复合索引 不能使用！= < > 或 is null 否则自身以及右侧索引全部失效 

          如果放在右边 可能会有一定概率不会失效

E. Sql优化是一种概率层面优化 至于是否实际使用了我们的优化需要分析sql执行计划进行推测  原因是服务层中存在sql优化器 可能会影响我们的优化

索引优化是一个大部分情况适用的结论 但由于sql 优化器等问题 可能会存在一定概率问题

   补救 使用索引覆盖

     （a,b,c） select a,b,c from xx where a=.. and b=.. 索引覆盖 永远不会失效

       Like 尽量以常量开头 不要以 % 开头 否则索引失效

       Select * from xx where name like ‘%x%’；--name 索引

       如果必须使用 %x% 使用索引覆盖可以挽救一部分

尽量不要使用类型转换 （显示 ，隐式）

尽量不要使用or 否则索引失效 甚至连左边都能影响失效

进阶

一、根据数据场景优化

（是否有数据 如果有数据 则校验成功）如果符合校验 则保留数据

Select tname form teacher where esists （select * from teacher ）；
Order by 优化
Using filesort 有两种排序 双路排序，单路排序 （根据IO的次数）
Mysql 4.1 之前默认使用的是双路排序

两次io叫双路排序 现将表字段中的id 查询出来 在缓存中进行排序 再根据id拿到其他的字段 单路排序 一次性全部拿出 减少io操作 但是此种操作也会存在隐患 不一定是真的单路 并且 当数据库字段过多时可能会出现 分片读取的操作 缓冲区不够 比双路会占用更多的buffer 单路排序在使用时 如果数据量大 可以考虑 调大buffer的容量大小

set max_length_for_sort_data= 1024 单位byte 字节

如果 max_length_for_sort_data 太低 那么mysql底层将会自动将单路切换双路

提高order by 查询的策略

• 选择使用单路双路 调整buffer 的容量大小

• 避免 select *

• 复合索引 不要跨列使用 避免 using filesort

• 保证全部的排序字段 排序的一致性 (都是升序或者都是降序)

二 、SQL排查  慢查询日志

慢查询日志 用于记录了 MYSQL中响应时间超过阈值的SQL语句
慢查询日志默认是关闭的 建议开发调优打开 最终部署关闭
检查是否开启了慢查询日志 ：

show variables like '%slow_query_log%'

临时开启 ： 退出重启会关闭

Set global slow_query_log =1;

永久开启 ：

/etc/my.cnf 中追加配置[mysqld] 中添加 slow_query_log =1slow_query_log_file= arb/mysql/e1ac21350f94-slow.log慢查询阈值：Show variables like '%long_query_time%'设置阈值 临时改变 set global long_query_time =5 设置完毕后 重新登录后生效永久改变 还是同上的地方 追加 long_query_time=3查询超过阈值的SQL 个数show global status like '%slow_queries%'

查看慢日志的具体sql  命令

 Cat arb/mysql/fb71e8597b04-slow.log

通过mysqldumpslow 工具查看  通过一些过滤条件

  S排序方式   R 逆序    i锁定时间   G 正则匹配模式


三、分析海量数据  

 通过存储过程 （无return）/存储函数 （有return）  创建80w条数据
 分析海量数据 ：  show profiles  会记录所有sql语句所花费的时间
 查看是否开启 ：  show variables like '%profiling%'
 默认关闭 打开      set profiling =on

精确分析：sql诊断  可以显示 sql使用io 与cpu 内存等信息

 show profile all for query 2(sql ID)

四、锁机制 解决因资源共享 而造成的的并发问题

根据操作类型分类：

操作范围分类：

• 表锁：MyISAM

一次性锁一张表 对一张表整体加锁 如MyISAM 就是表锁引擎

 开销小/加锁快 无死锁 但锁的范围小， 并发度低

 加读锁：lock table 表名 read 解锁：unlock 表名;   加写锁：lock table 表名 write 查看表锁定：show open tables     1表示被加了锁 查看表锁定的严重性:  show status like 'table%'   Table_locks_immediate 可立即获取的锁数 Table_locks_waited  需要等待的表锁数   （如果该值越大 说明存在越大的锁竞争）immediate / waited >5000 建议采用innodb引擎 否则建议采用 myISAM

• 当会话1对表进行添加read锁时 该会话只能对表进行读操作 不能写 并且对该表之外的任何表不能进行操作 只能对锁表进行读操作
  当会话2访问read锁的表时 只能进行读操作 写操作会进行等待 一直等到会话1释放为止

• 当前会话 可以对当前表 进行任意操作 增删改查都可以 但是不能操作其他表

• 其他会话  可以对会话0加写锁的表增删改查的前提是 会话0释放写锁
  

MyISAM在执行查询语句前 会自动给所有涉及的表加读锁
  在执行更新操作（DML）前，会自动给涉及的表加写锁
   所以对MyIsam表进行操作会有以下i情况：
A. 对MyISAM表的读操作（加读锁）不会阻塞其他会话的读操作 但是会阻塞对同一表的写操作，只有读锁释放后，才会执行其他的写操作。
B. 对MyISAM表的写操作，会阻塞其他会话对同一表的写操作和读操作 只有当写操作释放后，才执行其他进程的读写操作。

• 行锁 innodb
  

 一次性对一条数据加锁 如innodb引擎 使用行锁 开销大 加锁慢 容易出现死锁 锁的范围较小 不易发生锁程度 并发度高

Mysql 默认自动 commit  为了观察行锁 暂时关闭commit ：set autocommit =0
行锁小结 如果会话x 对每条数据a进行dml操作 （研究时 关闭了自动提交的情况）
其他会话必须等待会话x结束事务后 才能对数据a进行操作

表锁是通过 unlock tables; 解锁 行锁是通过事务提交回滚解锁 操作不同数据则互不干扰

 行锁的注意事项：

A. 如果没有索引 ，则行锁会转换为 表锁   比如索引失效 ……都会导致行锁变为表锁

B.   行锁的一种特殊情况 间隙锁 值在范围内 但却不存在 

间隙 mysql 会自动给间隙加锁 间隙锁 （行锁）

行锁如果有 where 则实际加锁的范围 就是 where 后面的范围 Innodb默认采用行锁；
 缺点 比表锁性能损耗大 优点并发能力强 效率高 因此建议 高并发用 inodb  

行锁分析   Show status like '%innodb_row_lock%' Innodb_row_lock_current_waits 当前正在等待锁的数量 Innodb_row_lock_time         等待总时长 Innodb_row_lock_time_avg      平均等待时长Innodb_row_lock_time_max     最大一次等待的时间Innodb_row_lock_waits 从系统等待到现在等待的次数

       如果是查询 能否加锁？ 

 查询语句后加上  for update  可以防止其他会话进行修改  

   好啦因为篇幅问题 这篇就到此结束了，因为本人画图比较菜，文章过于干涩，后续我会改进，接下来准备完成的有：

•       高性能Mysql （下 ） 架构升级 

•       Seata  直面分布式事务  

•       浅谈微服务 cap定理 

写在最后：

因为上周很忙，不好意思拖更了，本文介绍了索引和MySQL调优以及优化和sql中常会存在的坑点，锁机制，知识还是比较多的，mysql知识较多，下期我们在数据增量超大的情况，mysql分库分表的设计，主从复制，异地容灾，从架构的角度上去研究实现数据库高可用，由于笔者知识水平有限，如有不对之处欢迎指正。

阅读文献：

mysql官网 https://dev.mysql.com/doc

参考书籍:   高性能Mysql

bilbil：颜群 Sql优化