原理

MySQL的逻辑分层

MySQL的逻辑分层为四层或三层，我在知乎上找到的材料如下图：

MySQL是关系数据库，关系数据库，顾名思义，是建立在关系模型基础上的数据库，我们现实世界中的各种实体以及实体之间的各种联系一般可用关系模型来表示。经过数十年的发展，关系数据库在理论和工业实践中都已经发展到很成熟的地步，可以说，目前的绝大部分应用，使用MySQL都有成熟的解决方案。

数据库的架构一般可以分为应用层,逻辑层,物理层，MySQL也可以这样理解.

对应于MySQL：

应用层，负责和客户端,用户交互，需要和不同的客户端和中间服务器进行交互，建立连接，记住连接的状态，响应它们的请求，返回数据和控制信息（错误信息，状态码等）。

逻辑层，负责具体的查询处理、事务管理、存储管理、恢复管理,以及其他的附加功能。查询处理器负责查询解析、执行，当接收到客户端的Sql查询，数据库就分配一个线程来处理它，由查询处理器生成执行计划，交由计划执行器来执行，执行器的部分操作还需要访问更底层的事务存储管理器操作数据，事务、存储管理器主要负责我们的事务管理、并发控制、存储管理，由我们的事务管理器来确保“ACID”特性，由锁管理器来控制我们的并发，由日志管理器来确保我们的数据持久化，存储管理器一般还包括一个bufer管理器，由它来确定磁盘和内存缓冲之间的数据传输。

物理层，实际物理磁盘(存储)上的数据库文件.如我们的数据文件、日志文件等。

以下是MySQL官方文档的一个基础架构图，图中的Connectors可以理解为各种客户端端，应用服务，图中的Connection Pool 可以理解为应用层，负责连接、验证等功能，图中的Management Services & Utilities,SQL Interface,Parse,Optimizer,Cache & Buffers,Pluggable Storage Engines可以理解为我们数据库的大脑——逻辑层。图最下方的Files& Logs可以理解为物理层。

实际也可以将MySQL逻辑分为三层，如下图:

连接层：提供与客户连接的服务
服务层：1.提供各种用户使用的接口 2.提供SQL优化器（MySQL Query Optimizer）
引擎层：数据库引擎
存储层：数据存储

其中SQL的优化和SQL优化器有很大的联系

数据库引擎

InnoDB ：如果要提供提交、回滚、崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择

InnoDB 和 MyISAM之间的区别：

InnoDB支持事物，而MyISAM不支持事物
InnoDB支持行级锁，而MyISAM支持表级锁
InnoDB支持MVCC, 而MyISAM不支持
InnoDB支持外键，而MyISAM不支持
InnoDB不支持全文索引，而MyISAM支持。（X)

MyISAM：如果数据表主要用来插入和查询记录，则MyISAM（但是不支持事务）引擎能提供较高的处理效率

Memory：如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。数据的处理速度很快但是安全性不高。

Archive：如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

更详细的引擎介绍大家请自行参照Mysql 存储引擎的区别和比较。使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。

索引

分类：

主键索引：不能重复，id不能是null。
唯一索引 ：不能重复，id可以是null。
单值索引 ：单列， age ;一个表可以多个单值索引,name。
复合索引 ：多个列构成的索引（相当于二级目录： z: zhao） (name,age) (a,b,c,d,…,n)

创建索引：

方式一： create 索引类型索引名 on 表(字段)

单值：
create index dept_index on tb(dept);
唯一：
create unique index name_index on tb(name) ;
复合索引
create index dept_name_index on tb(dept,name);

方式二：alter table 表名索引类型索引名（字段）

单值：
alter table tb add index dept_index(dept) ;
唯一：
alter table tb add unique index name_index(name);
复合索引
alter table tb add index dept_name_index(dept,name);

注意：如果一个字段是primary key，则改字段默认就是主键索引

删除索引：

drop index 索引名 on 表名 ;
drop index name_index on tb ;

查看索引:

show index from 表名;
show index from 表名 \G;

索引相当于书的目录，索引是帮助MYSQL高效获取数据的数据结构。索引是数据结构（B+Tree）。

索引的弊端：

索引本身很大，可以存放在内存/硬盘（通常为硬盘）
索引不是所有情况均适用：a.少量数据 b.频繁更新字段 c.最少使用的字段
索引会减低增删改的效率

索引的优势：

提高查询效率（降低IO使用率）
降低CPU使用率（…order by age desc,因为 B树索引本身就是一个好排序的结构，因此在排序时可以直接使用）

SQL优化

SQL语句的性能损耗大主要有一下原因：原因：性能低、执行时间太长、等待时间太长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲、线程数）。

而SQL的优化，主要是在优化索引。

SQL解析顺序

SQL实际的解析过程为：

FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT

一看还是很自然和谐的，从哪里获取，不断的过滤条件，要选择一样或不一样的，排好序，那才知道要取前几条呢。

详情可以参考：SQL解析顺序

SQL性能问题

分析SQL的执行计划 : explain ，可以模拟SQL优化器执行SQL语句，从而让开发人员知道自己编写的SQL状况
MySQL服务层的查询优化其会干扰我们的优化

查询执行计划： explain +SQL语句

explain select * from tb ;

例如：

id : 编号
select_type ：查询类型
table ：表
type ：类型
possible_keys ：预测用到的索引
key ：实际使用的索引
key_len ：实际使用索引的长度
ref :表之间的引用
rows ：查询到的数据量
Extra :额外的信息

插入测试数据

create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);

create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);


insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;

id

如果id值相同：从上往下顺序执行

1	explain select t.* from course c,teacher t,teacherCard tc where c.cid = t.tid and t.tid=tc.tcid and (t.tid = 2 or tc.tcid = 3);

如果id值不同:id值越大越优先；id值相同，从上往下顺序执行

explain select tc.tcdesc from teacherCard tc where tc.tcid = 
(select t.tcid from teacher t where  t.tid =  
	(select c.tid from course c where c.cname = 'sql')
);

select_type:查询类型

PRIMARY:包含子查询SQL中的主查询（最外层）
SUBQUERY：包含子查询SQL中的子查询（非最外层）
simple:简单查询（不包含子查询、union）
derived:衍生查询(使用到了临时表)

在from子查询中只有一张表

1	explain select cr.cname from ( select * from course where tid in (1,2) ) cr ;

在from子查询中，如果有table1 union table2 ，则table1 就是derived,table2就是union

1	explain select cr.cname from ( select * from course where tid = 1 union select * from course where tid = 2 ) cr ;

union:上例
union result :告知开发人员，那些表之间存在union查询

type:索引类型、类型

值：system>const>eq_ref>ref>range>index>all，要对type进行优化的前提：有索引

其中：system,const只是理想情况；实际能达到 ref>range

system（忽略）: 只有一条数据的系统表；或衍生表只有一条数据的主查询。

const:仅仅能查到一条数据的SQL ,用于Primary key 或unique索引（类型与索引类型有关）。

eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多、不能0）。

ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）。

range：检索指定范围的行 ,where后面是一个范围查询(between,> < >=,特殊:in有时候会失效，从而转为无索引all)。

index：查询全部索引中数据。

all：查询全部表中的数据。

总结:

system/const: 结果只有一条数据
eq_ref:结果多条；但是每条数据是唯一的；
ref：结果多条；但是每条数据是是0或多条；

possible_keys

可能用到的索引，是一种预测，不准。

key

实际使用到的索引

key_len

索引的长度。是字节长度
作用：用于判断复合索引是否被完全使用（a,b,c）。
utf8：1个字符站3个字节。
GBK：1个字符站2个字节。
latin:1个字符1个字节

如果索引字段可以为Null,则会使用1个字节用于标识。
用2个字节，标识可变长度。

ref

注意与type中的ref值区分。
作用：指明当前表所参照的字段。

rows

被索引优化查询的数据个数 (实际通过索引而查询到的数据个数)。

Extra

using filesort ：性能消耗大；需要“额外”的一次排序（查询）。常见于 order by 语句中。
using temporary:性能损耗大，用到了临时表。一般出现在group by 语句中。
using index :性能提升; 索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据（不需要回表查询）只要使用到的列全部都在索引中，就是索引覆盖using index。
using where （需要回表查询）。
impossible where ： where子句永远为false
Using join buffer:Mysql引擎使用了连接缓存。

优化原则

优化主要是通过合理添加索引。

查询语句的优化，将能筛选更多数据（选择更少数据）的条件语句放在前面，并且索引建立在经常使用的字段上。

避免索引失效也是优化的一部分，为了避免索引失效，需要尽量遵循下面几条规则：
1.复合索引，不要跨列或者无序使用（用最佳左前缀匹配）
例如：

1 2	alter table test add index idx_a1_a2_a3(a1,a2,a3); // 添加索引 select a1 from test where a1='' and a2='' and a3='';

2.不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效。
例如:

1	select id from test where id*3 = 3;

3.复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效。

4.尽量使用索引覆盖（using index）。

5.like尽量以“常量”开头，不要以’%’开头，否则索引失效。

6.尽量不要使用类型转换（显示、隐式），否则索引失效。

7.尽量不要使用or，否则索引失效。
OR会引起索引失效的说法是这样来的：

如果是这样一个查询

SELECT * FROM TB WHERE A=1 AND B>2 AND C<3 AND D IN (4,5,6)

并且在TB表上有这样一个索引：CREATE INDEX INX_TB_ABCD ON TB (A,B,C,D)
那么这个查询可以用到这个索引

如果同样是这个索引，查询换成

SELECT * FROM TB_ WHERE A=1 OR B>2 OR C<3 OR D IN (4,5,6)

那么这个查询就用不到上面那个索引，因为结果集是几个条件的并集，最多只能在查找A=1的数据时用索引，其它几个条件都需要表扫描，那优化器就会选择直接走一遍表扫描，所以索引就失效了。

那么像第二个查询这样的应该怎么建索引呢，答案就是四个列上各建一个索引，或者只在选择性最高的列上建索引，比如A=1的数据量很少，就在A上建，如果D是4，5，6的数据很少，就在D上建，这样优化器就会选择先走索引查找，再对找出的结果集进行筛选，扫描数就会大幅减少。

其他的优化方法

exist和in

exist用法:
select ..from table where exist (子查询);
例如：

1 2	select tname from teacher where exists (select * from teacher) ; --等价于select tname from teacher

in用法:
select ..from table where 字段 in (子查询) ;

如果主查询的数据集大，则使用In ,效率高。
如果子查询的数据集大，则使用exist,效率高。

order by优化

using filesort 有两种算法：双路排序、单路排序（根据IO的次数）—IO较消耗性能
MySQL4.1之前默认使用双路排序；双路：扫描2次磁盘（1：从磁盘读取排序字段 ,对排序字段进行排序（在buffer中进行的排序） 2：扫描其他字段）
MySQL4.1之后默认使用单路排序 ：只读取一次（全部字段），在buffer中进行排序。但种单路排序会有一定的隐患（不一定真的是“单路|1次IO”，有可能多次IO）。原因：如果数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行“分片读取、多次读取”。
注意：单路排序比双路排序会占用更多的buffer。
单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小：

1	set max_length_for_sort_data = 1024 单位byte

如果max_length_for_sort_data值太低，则mysql会自动从单路->双路（太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）

提高order by查询的策略:
a.选择使用单路、双路；调整buffer的容量大小；
b.避免select … （程序还需要计算是什么）
c.复合索引不要跨列使用，避免using filesort
d.保证全部的排序字段排序的一致性（都是升序或降序）

SQL排查

慢查询日志

MySQL提供的一种日志记录，用于记录MySQL种响应时间超过阀值的SQL语句（long_query_time，默认10秒）
慢查询日志默认是关闭的,建议开发调优是打开，而最终部署时关闭。

检查是否开启了慢查询日志：

1	show variables like '%slow_query_log%' ;

临时开启：

1	set global slow_query_log = 1 ; --在内存种开启

永久开启：

/etc/my.cnf 中追加配置：
vi /etc/my.cnf 
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log

慢查询阀值：

1	show variables like '%long_query_time%' ;

临时设置阀值：

1	set global long_query_time = 5 ; --设置完毕后，重新登陆后起效（不需要重启服务）

永久设置阀值：

/etc/my.cnf 中追加配置：
vi /etc/my.cnf 
[mysqld]
long_query_time=3

查询超过阀值的SQL：

1	show global status like '%slow_queries%' ;

mysqldumpslow

mysqldumpslow —help可显示其参数的使用
经常使用的参数：

-s 是order的顺序

al 平均锁定时间

ar 平均返回记录时间

at 平均查询时间（默认）

c 计数

l 锁定时间

r 返回记录

t 查询时间

-t 是top n的意思，即为返回前面多少条的数据

-g 后边可以写一个正则匹配模式，大小写不敏感的

例子：

1	mysqldumpslow -t 10 -s t -g "left join" host-slow.log

使用mysqldumpslow的分析结果不会显示具体完整的sql语句，说明：

1	SELECT * FROM sms_send WHERE service_id=10 GROUP BY content LIMIT 0, 1000;

mysqldumpslow显示的结果会是：

1 2	Count: 1 Time=1.91s (1s) Lock=0.00s (0s) Rows=1000.0 (1000), vgos_dba[vgos_dba]@[10.130.229.196] SELECT * FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N;

Count:会告诉我们这种类型的语句执行了几次
Time:会告诉我们这种类型的语句执行的最大时间
Lock:等待锁时间
Rows:返回的行数

SQL诊断

profiles:

查看是否开启：

1	show variables like '%profiling%';

开启:

1	set profiling = on ;

查看：

show profiles  ：会记录所有profiling打开之后的  全部SQL查询语句所花费的时间。缺点：不够精确，只能看到 总共消费的时间，不能看到各个硬件消费的时间（cpu  io ）

精确分析
查看上一步查询的Query_Id

1	show profile all for query + 上一步查询的Query_Id

show profile cpu,block io for query + 上一步查询的Query_Id

全局查询日志

记录开启之后会记录全部SQL语句。
注意：这次全局的记录操作，仅仅在调优、开发过程中打开即可，在最终的部署实施时一定关闭。

查看是否开启：

1	show variables like '%general_log%';

执行的所有SQL记录在表中

1 2	set global general_log = 1 ;--开启全局日志 set global log_output='table' ; --设置将全部的SQL 记录在表中

执行的所有SQL记录在文件中

1
2
3

set global log_output='file' ;
set global general_log = on ;
set global general_log_file='/tmp/general.log' ;