mysql 调优

【mysql 调优相关】

链接

第一章

mysql 逻辑架构

连接层——服务层——引擎层——存储层

【mysql支持插件式的存储引擎架构，将查询处理和其他系统任务以及数据的存储提取相分离】

mysql引擎

第二章【优化分析】

SQL 执行顺序

JOIN 图

【索引】

1、索引是什么？
索引（index） 是帮助MYSQL高效获取数据的数据结构【排好序的快速查找数据结构】，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉树）结构组织的索引。

2、索引的目的是？
提高查询效率，类比字典

3、索引本身也很大，不可能全部存储在内存中，以索引文件的形式存于磁盘
【索引两大重点：查询和排序 ==》where 后面的是查询条件，order by 是排序条件】

4、索引优势与劣势

5、索引分类
单值索引：即一个索引质保函单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列

6、mysql索引结构
BTree 索引、Hash索引、full-text全文索引、R-Tree索引
BTree 索引

7.1、需要创建索引的情况

7.2、不需要创建索引的情况

8、性能分析

1、MySQL Query Optimizer：Mysql中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划，但不见得最优

2、MySQL常见瓶颈（通过cpu 、io 、 服务器的硬件进行分析）
CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

3、通过explain的关键字进行分析
【explain介绍：
- 是什么==》（查看执行计划），使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理SQL语句的，用于分析查询语句或是表结构的的性能瓶颈
-能干嘛==》

-怎么用==》 explain + SQL语句

字段解释：
id：表的读取顺序。
select_type：数据读取操作的操作类型。
possible_keys：哪些索引可以使用。
key：哪些索引被实际使用。
ref：表之间的引用。
rows：每张表有多少行被优化器查询
】

4、id（查询序列号）
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：

情况一：id相同，执行顺序由上至下

情况二：id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行（在内层的子查询序列，其等级越高）

情况三：（以上两种情况同时存在的时候）
注意框框是一个临时表
按照上面的规则进行排序
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行，衍生=DERIVED

5、select_type（查询类型）
select_type：标注是什么类型的查询，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
类型：

6、type（访问类型）
类型：

但是常见为下面的【上面的不用记】

排序（最好 --》 最差）
system > const > eq_ref > ref > range > index >All
【一般要求至少达到range 级别，最好能达到 ref】

详解：

7、possible_keys 、key 和 key_len（可能用到索引、实际用到索引、长度）

8、ref（条件查询）
（显示使用到的条件查询，如果是常量就为const）
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’。
查询中与其它表关联的字段，外键关系建立索引。

9、rows（行数）
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。（越小越好）
每张表被优化器查询
把不合适的索引删除，慢慢优化

10、Extra （包含不适合在其它列中显示但十分重要的额外信息）

【Using filesort ---> 九死一生 、Using temporary ---> 十死无生 、 Using index ---> 幸运之子】

例子

11、

12、

13、

sql 优化

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描；
3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，
5.in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不要用 in 了，很多时候用 exists 代替 in 是一个好的选择（exists 和 in 的区别及使用场景请自行百度）；
6. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描；
7.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

8.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

9.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
10.Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

11.对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。
12.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
13.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
14.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

15.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

16.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
17.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

老师——SQL优化
1、硬件
2、网络带宽
3、软件

sql语句优化
数据结构优化【表字段】
库表拆分

二叉树

排好序的话，高度会很高

红黑树
B树

宽度 ——横向扩展，减少高度IO

B+树