MySQL高级部分笔记

有关于MySQL的高级部分笔记

这是一篇关于MySQL高级部分的笔记主要是，sql优化以及mysql锁的相关内容，以及主从配置等内容等比较基础的优化

一、逻辑架构部分

1. 逻辑架构

   逻辑架构介绍图如下

• 连接层：最上层是一些客户端和连接服务，包含本地的sock通讯大多时基于客户端/服务端工具实现的类似于tcp/ip的通讯

• 服务层：完成大多数的核心服务的功能，如，SQL接口，并完成缓存的查询SQL的分析和优化以及部分内置函数的执行，所有款存储引擎的功能

• 引擎层：存储引擎真正的负责了MySQL中的数据的存储和提取，服务器通过api与存储引擎进行通讯，常用的有MyISAM和InnoDB

• 存储层：数据存储在裸设备上，并完成与存储引擎的交互

   

  优化主要是只使SQL的解析格式符合优化器的优化格式

2. 存储引擎

   查看mysql的存储引擎命令

   # 看你的mysql提供了生么存储引擎
   show engines;
   # 看当前默认的存储引擎
   show variables like ‘%storage_engine%‘;

   MyISAM与InnoDB的对比如下表

   对比项	MyISAM	InnoDB
   主外键	不支持	支持
   事务	不支持	支持
   行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其他的行有影响，适合高并发的操作
   缓存	只缓存索引不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存的要求比较高，而且内存的大小对性能有决定性的影响
   表空间	小	大
   关注点	性能	事务
   默认安装	Y	Y

3. 性能下降SQL慢的原因

   • 查询语句写的烂

   • 索引失效

     • 单值索引

     • 符合索引

   • 关联查询太多join（设计缺陷）

   • 服务器调优及各个参数的设置（缓冲、线程数等）

4. SQL执行加载的顺序

   • 手写顺序

     select distinct
         <select_list>
     from 
         <left_table> <join_type>
     join <right_tablr> on <join_condition>
     where 
         <where_condition>
     group by 
         <group_by_list>
     having
         <having_condition>
     order by
         <order_by_condition>
     limit <limit_number>

      

   • MySQL的执行

     from <left_table>
     on <join_condition> <join_type>
     join <right_table>
     where <where_condition>
     group by <group_by_list>
     having <having_condition>
     select
     distinct <select_list>
     order by <order_by_condition>
     limit <limit_number>
     ?

     总结

     

二、索引及优化部分

1. 索引简介

   • 是什么

     索引是帮助MySQL高效获取数据的数据结构，本质上是数据结构（查找+排序）

   • 种类

     B+树索引

     hash索引

     全文索引

     RTree索引

   • 优势

     提高数据的检索效率降低的磁盘的io

     降低数据的排序成本降低了cpu的消耗

   • 劣势

     实际上索引也是一张表，保存主键与索引字段，指向实体表的记录，也是要占用空间的

     会降低对于insert，update，delete的速度

     索引只是提高效率的一个因素

   • 分类

     • 单值索引：一个索引只包含一个列

     • 唯一索引：索引的列必须唯一，可以有空值

     • 复合索引：一个索引包含多个列

     • 基本语法

       # 创建 
       create [unique] index indexName on table(columnname(length));
       alter tablename add [unique] index indexname on (columnname(length));
       # 删除
       drop index [indexname] on tablename;
       # 查看
       show index from tablename\G

   • 哪些情况适合建索引

     1. 主键自动建立唯一索引

     2. 频繁作为查询条件的字段

     3. 查询中与其他表关联的字段，外键关系建立索引

     4. 频繁更新的字段不适合建立索引

     5. where用不到的字段不创建索引

     6. 单键/组合索引的选择问题（高并发下倾向于创建复合索引）

     7. 查询中排序的字段排序字段通过索引访问将大大提高访问的速度

     8. 查询中统计和分组的字段

   • 哪些情况不适合建索引

     1. 表记录太少

     2. 频繁修改的字段

     3. 数据重复且分布平均的字段

   • 性能分析

     MySQL Query Optimizer 查询优化器

     负责对select语句进行优化

     性能瓶颈

     cpu：cpu饱和的时候一般发生在数据装入内存或者从磁盘上读取数据的时候

     io：装入数据元大于内存容量的时候

     服务器硬件：top，free，iostat，vmstat命令查看系统的性能状态

三、Explain 执行计划

• 是什么

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

• 怎么用

  explain + sql

  执行计划包含的信息

• 字段解释

id

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

三种情况：id相同执行顺序由上至下

id不同如果是子查询，id的序号会递增，id越大优先级越高，越先被执行

id相同又不同会遵循上两条规律

select_type

select_type: 查询的类型主要是用于区别，普通查询，联合查询，子查询等复杂查询

simple：简单查询不包含子查询，或者union

primary：主查询子查询最外层的查询

subquery：子查询

derived：在from列表中包含的子查询被标记为dervied衍生，MySQL会递归执行这些子查询，把最终结果放在临时表里

union：第二个select出现在union之后则被标记为union，若union包含在from子句中，外层的select将被标记为derived

union result：从union表中获取结果的select

table 显示这一行数据是关于那一张表的

type

常见的访问类型八种

最好到最差

system > const > eq_ref > ref > range > index > all

一般来说得保证查询至少优化到range最好到ref

prossiable_keys和key

prossiable_keys：显示出在本次查询中可能用到的索引，但是不一定用

keys：实际查询的过程中实际使用的索引为null没有使用索引，若查询中使用覆盖索引则在该索引仅出现在key列表中

key_len

索引字段的最大可能长度，并非实际长度，再不损失精确性的情况下越小越好

ref

显示索引的那一列被使用了，如果可能的话尽量是个常数

rows

找到所需的记录读取的行数

extra

很重要的额外信息

using filesort：使用文件内排序（坏）

using temporary：使用临时文件（坏）

using index：使用索引（好）

using where：使用where

四、优化的几点建议

1. 全值匹配

2. 最佳左前缀法则

3. 不在索引列上做任何的操作（计算，函数，（自动or手动的类型转换）），会导致索引失效而全表扫描

4. 存储引擎不能使用索引中范围条件右边的列

5. 尽量使用覆盖索引只访问索引的查询（索引列与查询列一致），减少select *

6. mysql在使用不等于（!= 或 <>）的时候无法使用索引回单之全表扫描

7. is null ,is not null 也无法使用索引

8. like以通配符开头会导致索引失效全表扫描

9. 字符串不单引号索引失效

10. 少用or用来连接时会使索引失效

11. 永远小表驱动大表

12. group by/order by排序字段也会用到索引(左前缀，尽量使用where)

五、查询截取分析

1. 慢查询日志截取慢sql

   # 查看
   show variables like ‘%slow_query_log%‘;
   # 开启
   set global slow_query_log=1
   # 查看记录sql的阈值时间
   show variables like ‘long_query_time%‘;
   # 设置阈值时间
   set global long_query_time=3;

    

2. show profile

   # 查看是否支持
   show variables like ‘profiling‘
   # 开启
   set profiling=on
   ?
   # 查看结果
   show profiles;
   show profile cpu,block io from query [问题sql的query_id];

六、MySQL锁

MySQL高级部分笔记

原文：https://www.cnblogs.com/yangenyu/p/11604262.html