mysql高级笔记_MySQL高级部分笔记

有关于MySQL的高级部分笔记

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

一、逻辑架构部分

逻辑架构

逻辑架构介绍图如下

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

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

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

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

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

存储引擎

查看mysql的存储引擎命令

# 看你的mysql提供了生么存储引擎

show engines;

# 看当前默认的存储引擎

show variables like ‘%storage_engine%‘;

MyISAM与InnoDB的对比如下表

对比项MyISAMInnoDB

主外键

不支持

支持

事务

不支持

支持

行表锁

表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作

行锁，操作时只锁某一行，不对其他的行有影响，适合高并发的操作

缓存

只缓存索引不缓存真实数据

不仅缓存索引还要缓存真实数据，对内存的要求比较高，而且内存的大小对性能有决定性的影响

表空间

小

大

关注点

性能

事务

默认安装

Y

Y

性能下降SQL慢的原因

查询语句写的烂

索引失效

单值索引

符合索引

关联查询太多join(设计缺陷)

服务器调优及各个参数的设置(缓冲、线程数等)

SQL执行加载的顺序

手写顺序

select distinct

from

join on

where

group by

having

order by

limit

MySQL的执行

from

on

join

where

group by

having

select

distinct

order by

limit

?

总结

二、索引及优化部分

索引简介

是什么

索引是帮助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

哪些情况适合建索引

主键自动建立唯一索引

频繁作为查询条件的字段

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

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

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

单键/组合索引的选择问题(高并发下倾向于创建复合索引)

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

查询中统计和分组的字段

哪些情况不适合建索引

表记录太少

频繁修改的字段

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

性能分析

MySQL Query Optimizer 查询优化器

负责对select语句进行优化

性能瓶颈

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

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

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

三、Explain 执行计划

是什么

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

怎么用

explain + sql

执行计划包含的信息

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra

字段解释

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

常见的访问类型八种

allindexrangerefeq_refconst,systemnull

全表扫描

全索引扫描比all好

检索指定范围的行

非唯一性索引扫描，多条记录匹配

唯一性索引扫描，表中只有一条记录匹配常见于主键索引唯一索引

system表只有一行记录const表示通过索引一次就找到了以为只匹配一行数据如将主键置于where列表中，MySQL就能将查询转化为const

最好到最差

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

四、优化的几点建议

全值匹配

最佳左前缀法则

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

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

尽量使用覆盖索引只访问索引的查询(索引列与查询列一致)，减少select *

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

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

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

字符串不单引号索引失效

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

永远小表驱动大表

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

五、查询截取分析

慢查询日志截取慢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;

show profile

# 查看是否支持

show variables like ‘profiling‘

# 开启

set profiling=on

?

# 查看结果

show profiles;

show profile cpu,block io from query [问题sql的query_id];

六、MySQL锁

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