MySQL优化指南及优化思路全解

1、原理：

MySQL逻辑分层：连接层，服务层，引擎层，存储层

InnoDB(默认)：事务优先(适合高并发操作：行锁)

MyISAM：性能优先（表锁）

2、查询数据库引擎：

支持那些引擎？show endines；

查看当前使用的引擎

show variables like ‘%storage_engine%’

3、索引:

分类：

主键索引：不能重复，不能为NULL

唯一索引：不能重复，可以为NULL

单值索引：单列值，一个表可以有多个单列索引

复合索引：多个列构成的索引

创建索引：

方式一：create 索引类型 索引名 on 表（字段）

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

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

4、SQL性能问题：

a.分析SQL的执行计划：explain ，可以模拟SQL优化器执行SQL语句，从而让开发人员知道自己编写的SQL性能

b.MySQL查询优化其会干扰我们的优化

查询执行计划：

explain + SQL语句

explain select * from tb；

ID：编号

ID值不同，id值越大越优先查询，（本质：在嵌套子查询时，先查内层，再查外层）

id值相同从上往下，顺序执行

select_type：查询类型

PRIMARY：包含子查询SQL中的主查询（最外层）

SUBQUERY：包含子查询SQL中的子查询（非最外层）

simple：简单查询（不包含子查询，union）

derived：衍生查询（使用到了临时表）

union ：

union result：告知开发人员，哪些表之间存在union查询。

table：表

type：索引类型，类型

system>const>eq_ref>ref>range>index>all（效率越往左越高）

其中，system,const只是理想状态，实际上能达到ref>range

system(忽略)：只有一条数据的系统表；或者衍生表只有一条数据的主查询

const：仅仅能查到一条数据的SQL,用于Primary key 或者unique索引（该类型与使用的索引类型有关，如果不是这俩个类型也没用）

eq_ref：唯一性索引，对于每个索引键的查询，返回匹配唯一行数据（结果是多条，但是每条数据是唯一的）

常见于唯一索引和主键索引

ref：非唯一性索引，对于每个索引键的拆线，返回匹配的所有行（结果多条，但是每条数据是0或者多条）

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

index：查询全部索引中的数据，只需要扫描索引表，不需要所有表中的所有数据

all：查询表中的全部数据，需要全表扫描，即需要表中的所有数据

possible_keys：预测用到的索引

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

如果possible_keys/key为NULL，则说明没用索引

key：实际使用的索引

key_len：实际使用索引的长度

作用：用于判断复合索引是否被完全使用

注意：如果索引字段可以为NULL，则会使用一个字节用于标识。

ref：表之间的引用

注意和type中ref值进行区分，它是指当前表所参照的字段

rows：通过索引查询到的数据量

被索引优化查询的数据个数（实际通过索引而查询到的数据个数）

Extra：额外的信息

using filesort：性能消耗大，需要额外的一次’排序’（查询）

using temporary：一般出现在group by 语句中；表示已经有表了，但是不适用，必须再来一张表。

using index：性能提升，索引覆盖

原因：不读取原文件，只从索引文件中获取数据，只要使用到的列，全部都在索引中，就是索引覆盖using index

如果用到了using index（索引覆盖），会对possible_keys和key造成影响：

a.如果没有where，则索引只出现在key中

b.如果有where，则索引出现在key和possible_keys中

5、优化案例

单表优化

优化：加索引

索引一旦进行升级优化，需要将之前废弃的索引删掉，防止干扰