从不同表获取数据并进行计算sql_高性能Mysql （上） Sql优化与排坑

“Sql优化作为后端系统的core，你确定不了解一下吗。”

写在前头：

在我们学习技术的过程中，总是会出现一些新颖、快速方便的框架，这些框架层出不穷，容易使我们在学习的路上迷失自我，那么我们是否应该学习一些原始具有奠基性的东西，万变不离其踪，本期我们来探究一下查询Mysql的底层原理以及优化机制，来作为我们架构成长之路上的第一块垫脚石。

01

—

Mysql结构

让我们来看一看Mysql的底层结构，和Sql工作流程。

连接层	提供与客户端连接的服务  连接池 线程 缓存
服务层	提供各种用户使用的接口  select     提供sql 优化器
引擎层	提供各种数据存储方式     ……	InnoDB  ：事务优先   适合高并发操作  行锁
		MyISAM ：性能优先   表锁
存储层	存储数据

Mysql执行Sql的流程优先级

From > on join >where >group by  >having  >select

如何理解流程优先级？为什么会是这种优先级？

根据上图我们可以发现，流程优先级是和筛选条件息息相关，首先是对表，链接表进行确定，确定表范围后，使用where条件确定条件范围，在进行分组，最后的优先级是筛选需要查询的select。

不难发现，通过采取这种优先级的方式，我们可以达到最大优化sql的目的，确定大范围，在确定小范围。

02

—

初识优化——MySql索引介绍

Sql优化 主要是优化索引   有了索引 可以加速查询的效率

索引介绍

索引	index 是帮助Mysql高效获取数据的数据结构
	索引是数据结构 树 B树 B+树 Hash树
索引的弊端	索引本身很大 可以存放在内存/硬盘 通常为硬盘
	索引不是所有情况均适用   a.少量数据  b.频繁更新的字段 需要更改索引
	索引会降低增删改的效率 (增删改)
索引的优势	提高查询效率 降低io使用率 降低CPU使用率   因为索引本身是一个排好序的结构 。

索引数据结构介绍

B树  Btree 一般是指 B+ , 数据全部存放在叶节点中

B+树的查询任意的数据次数 ：N次 (B+ 树的高度)

索引的分类：

单值索引	某个字段为索引  一个表可以有多个单值索引
唯一索引	不能重复   id  可以是null
主键索引	不能重复  不能为null                            primary key
复合索引	多个列构成的索引  两个不能同时重复
全文索引	检索长文本的时候效果最好
空间索引	针对空间数据类型使用的索引 只能在MyISAM引擎中使用

索引方法类型 

  BTREE    B树 主流使用 如索引数据结构  HASH     键值对 Key vlaue  这种方式对范围查询不是很友好 创建索引  ：  create  索引类型   索引名  on 表 (字段) 查询所有索引  ：  show index from 表名         注意 ：如果一个字段是primary key 则该字段默认就是主键索引  

什么？ 优化=加索引   可千万别这样以为 

   底层原理：学过计算机的都知道，我们的cpu是需要通过内存进行数据交互访问的，硬盘上的数据并不能直接的进行寻址，所以我们的索引其实就是工作在内存的一种数据结构，mysql能够根据条件直接通过索引定位到数据在硬盘上所存储的位置，然后进行读取。

结论：索引是工作在内存的数据结构。

另类思考：

    假设现在有一个张user表，表中存在user_id,sex,name，三条字段，里面有1000w条数据。

user_id	sex	name
1000001	男	meihua
1000002	男	小明

假设给userid添加索引，每个userid都是唯一，那么在我们的内存区域会存在1000w条索引数据，这些数据当然能很快的对我们的用户数据进行查找，但是对我们的内存也是一笔不小的开销。

关键：当我们对user进行添加和删除时，索引会进行添加和修改，增加开销。

假设我们给sex添加索引 sex只有男和女两种属性，所以即使添加了索引，我们查找的速度依然会很慢，sex并不能直接定位到我们指定想要的数据。优化效果不好。

假设给name进行添加索引，name字段也可也能会出现重复的情况，不过比sex要少很多，是一个折中方案，不过在这个场景完全没必要在name加索引，需要给name加索引的场景有，通过name字段链接其他表，其他表的数据量比较庞大，可以考虑做索引优化速度。

总结：业务扩充，和我们为了提升sql速度，加索引确实是最简单粗暴的方法，但是要加在关键的地方，尤其是链表字段，我在开发中就遇到过为了优化速度，一张表添加了5个以上的索引，这对增删改其实非常不利，而且可能引起索引重排负优化。

---

索引注意事项：当表中数据超过千万后，并且索引是唯一索引，那么短时间会在内存添加很多数据这时应当注意系统场景进行添加，避免数据库宕机。

03

—

SQL优化

一、SQL优化第一步 分析SQL执行计划

                   在查询语句前添加 explain  可以分析Sql执行计划

注意： Mysql查询器会干扰我们的优化 Sql执行计划 参数 属性信息 红色为重点信息

执行计划	属性
select_type   查询类型	PRIMARY	包含子查询SQL中的主查询
	SUBQUERY	包含SQL中的子查询 (非最外层)
	SIMPLE	简单查询
	DERIVED	衍生查询  from 子查询 中只有一张表 From 子查询中 如果有 table1 union table2
table	表名	这行数据关联的表
type	索引类型   System>const>eq_ref>ref>range>index>all
	System	是理想情况 结果是一条数据
	const
	eq_ref    唯一性索引	对于每个索引键的查询 返回匹配唯一的行  想要达到eq_ref 不能多 也不能为0  可以为多条数据 但是每条的结果是唯一的
	Ref  非唯一性索引	对于每个索引键的查询   返回匹配的所有行   0或多个
	Range 范围查询	检索指定范围的行  如 in(1,2)  < >     范围查询 特殊：in有时会失效 从而转为无索引
	Index	查询全部索引中的数据
	All	这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。需要优化
Possible_keys	索引名称	可能使用到的索引 是一种预测  不准
KEY	索引名称	实际上使用到的索引  null则表示没走索引
Key_len	计算法则	索引的长度  作用：用于判断符合索引是否被完全使用 多个索引在查询时 可能只用了一半  就将数据查询出来  如果索引字段可以为null 则会使用一个字节用于标识  Mysql 用2个字节标识可变长度 Utf8 一个字符占3个字节 gbk一个字符2个字节   latin(拉丁文) 一个字符一个字节
Ref	指明当前表所参照的字段	一般有 const 表示常量   xxx.xxx.id   某个字段   null 没有指明
Rows	行数	实际通过索引而查询到的数据个数
Extra	Using filesort	标识性能消耗比较大  当前sql需要额外一次排序(需要额外一次查找)需要优化‍

Extra 说明 ： 

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

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

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

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

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

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

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

using index  性能提升 索引覆盖 (覆盖索引)

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

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

Using where  需要回表查询 

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

Impossible where    不可能的where  where子句永远为 false

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

Using join buffer   一般出现这个 是性能太差了 mysql加入了连接缓存 

04

—

优化示例

假设有一个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    For(int j=0;j      3000次    }}For(int i=0 ;i    For(int j=0;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 否则索引失效 甚至连左边都能影响失效

05

—

进阶

一、根据数据场景优化

 如果主查询的数据极大 则使用 in
    如果子查询的数据极大 使用 exist
     Exist 语法 将主查询的结果 放到子查询中进行条件校验

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

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优化