MySQL优化你懂多少

MySQL优化

docker 容器里面添加vi apt-get update

插件式的存储引擎结构将查询处理和他的系统任务以及数据的存储提取向分离

主要的两种存储引擎的对比

MyISAM和InnoDB

优化分析

1、性能下降 SQL慢 执行时间长 等待时间长

• SQL语句写的不好

• 索引失效

  • 创建索引 create index idx_name on user(name)或者create index idx_nameEmail on user(name,email)
    

• 关联查询太多

• 服务器调优以及各个参数的设置

4、常见通用的Join查询

SET DISTINCT <select_list> FROM <left_table> <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <GROUP_LIST> HAVING <HAVING_CONDITION> ORDER BY <order_by_condition> LIMIT

Join练习

左连接: select * from table a left/right/inner/full outer(无法使用) join table b on a.key= b.key

所以

对于两个数据库查询两个库的全部的时候使用union

select * from table a left join table b on a.key=b.key union select * from table a right join table b on a.key=b.key

查询两个库各自的独有

select * from table a left join table b on a.key=b.key where b.id is null union select * from table a right join table b on a.key=b.key where a.id is null

什么是索引

索引是帮助MYSQL高效获取数据的数据结构 可以得到索引的本质: 索引是数据结构用于排序和快速查找 注意索引会影响到where的查找和order by 后面的排序

一般来说索引本身也很大 不可能全部存储在内存中 因此索引往往以索引文件的形式存储在磁盘上

平时所说的索引基本都是指B树(多路搜索树 并不一定是二叉树)结构组织的索引 其中聚集索引 次要所以呢 符合索引 前缀索引 唯一索引都是使用B+树索引 统称为索引 当然除了B+树还有Hash索引

优势:1、提高数据检索的效率，降低数据库的IO成本 2、通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势：实际上索引也是一张表 该表或保存主键与索引的字段 并指向实体表的记录 所以索引也是要占用空间的 索引在提升查询效率的同时 会降低更新表的操作 因为在更新表的时候MYSQL不仅仅要保存数据 还要保存索引文件每次更新添加了索引列的子段 每次都要在索引文件中进行操作，并且索引只是提高效率的一-个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询

单值索引:即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引:索引列的值必须唯一，但允许有空值

复合索引:即一个索引包含多个列

基本语法

• 创建 create [unique唯一索引的关键字] index indexName ON mytable(columname(length)那些子段)

  Alter mytable ADD [unique] index [indexName] ON (columname(length))

• 删除 DROP INDEX [indexName] ON mytable;

• 查看 show INDEX FROM table_name

BTree索引原理

在BTree中 叶子节点存放的是真实地数据 非叶子节点不存放真实的数据 存放的是指引搜索方向的数据项

查找过程 首先先将磁盘块加载到内存里面 此时会发生一次IO 在内存中用二分法查找范围确定对应位置的指针 内存时间因为非常的短可以忽略不计 通过磁盘块1中指针的磁盘地址将磁盘2 加载到内存里面 发生第二次IO 在加载磁盘3 发生第三次IO 同时在内存中做二分查找法寻找

真实的情况是，3层的b+树可以表示上百万的数据。如果上百万的数据查投只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

那些情况下需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的子弹 外键关系建立索引
4. 频繁更新的字段不适合创建索引 因为每次更新不单单是更新了记录还会更新索引表中的数据
5. Where条件中用不到的子弹不创建索引
6. 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8. 查询中统计或者分组字段

那些情况下不需要创建索引

1. 表的记录太少 (三百万左右才进行创建索引)

2. 经常增删改的表 提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE.因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

   例如 一个表中有2000条数据 表的索引列有1980个不同的值 那么这个索引的选择性就是1980/2000=0.99接近1

   性

性能分析

1、MySql Query Optimizer查询优化器

• 通过计算分析系统中收集到的统计信息 为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的,哀部分最耗赀时间）

2、MySql常见的瓶颈

1. CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
2. IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
3. 服务器硬件的性能瓶颈: top,free, iostat和vmstat来查看系统的性能状态

3、Explain 查看执行计划

使用EXPLAIN关键字可以模拟优化器执行sQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

语法：explain SQL语句

作用

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

查询结果的属性解析

• id select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

  • id相同 执行顺序从上到下
  • id不同 如果是子查询 id的序号会递增 id值越大优先级越高 越先被执行
  • id有相同也有不同 不同的大的下执行 相同的按照顺序执行

• select_type 查询类型 主要是用于区别 普通查询 联合查询 子查询等复合查询

  1.SIMPLE 简单的select查询,查询中不包含子查询或者UNION

  2.PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为

  3.SUBQUERY 在SELECT或WHERE列表中包含了子查询

  4.DERIVED – 在FROM列表中包含的子在询被标记为DERIVED(衍生)MySQL会递归执行这些子查询，把结果放在临时表里。

  5.若第二个SELECT出现在UNION之后,则被标记为UNION:若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERvED

• table 来自与哪一个表

• type 访问类型排列 system>const>eq_ref>ref>range>index>ALL

  尽可能的将类型控制达到range级别 如果是更好的级别也是可以的

• possible_keys 显示可能应用在这张表的索引 一个或者多个 查询涉及到的字段若存在索引 则这个索引将被列出 但不一定被查询实际使用 这个显示的是理论上使用的索引

• key指定是实际使用的索引

• ley_len 表示索引中使用的字节数 可以通过该列计算查询中使用的索引的长度 在不损失精确度的情况下 长度越短越好 显示的是索引字段的最大长度 但是不是实际使用的长度

• ref 显示索引的哪一列被使用了 如果可能的话 是一个常数 那些列或常数用于查找索引列上面的值

• rous 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数 值越小越好

• Extra 包含不适合在其他的列中显示但十分重要的额外信息

的是索引字段的最大长度 但是不是实际使用的长度

• ref 显示索引的哪一列被使用了 如果可能的话 是一个常数 那些列或常数用于查找索引列上面的值

• rous 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数 值越小越好

• Extra 包含不适合在其他的列中显示但十分重要的额外信息