MySQL高级

MySQL核心配置文件

查看本机MySQL配置文件所在位置

[mysqld]

# socket=MYSQL

# The TCP/IP Port the MySQL Server will listen on
port=3306

# Path to installation directory. All paths are usually resolved relative to this.
# basedir="C:/Program Files/MySQL/MySQL Server 5.6/"

# Path to the database root
datadir=C:/ProgramData/MySQL/MySQL Server 5.6/Data

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=utf8

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

MySQL体系架构

（1）MySQL向外提供的交互接口（Connectors）

（2）连接池组件(Connection Pool)

（3）管理服务组件和工具组件(Management Service & Utilities)

​ 1)备份，集群，认证，权限，查询引擎

（4）SQL接口组件(SQL Interface)

​ 1)接收SQL命令，判断SQL的类型DML还是DDL还是视图等。

（5）查询分析器组件(Parser)

​ 1)语法解析，比如sql语法的检查就是在这里完成的，语法检查失败直接返回语法错误。

​ 2)语义解析，比如查询的某张的表不存在直接返回xx表不存在。

（6）优化器组件（Optimizer）

​ 1)对SQL命令进行优化，面试问的SQL优化主要就是针对这个模块。

​ 2)MySQLServer会对发送过来的SQL语句进行优化 ，根据sql语句，生成一系列的执行计划，然后从执行计划选出最优的一条计划执行。

​ 3)在这里判断是否使用索引，如何使用索引

（7）缓存主件（Caches & Buffers）

​ 1)查询缓存：主要对SQL语句缓存，把经常使用的SQL语句缓存起来

​ 2)数据缓存：从磁盘中加载数据到内存中，然后对内存中的数据进行过滤。

​ 3)日志缓存：通过日志缓存恢复记录。

（8）插件式存储引擎（Pluggable Storage Engines）

（9）物理文件（File System）

查看SQL语句执行流程

​ MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条Query 在整个执行过程中多种资源的消耗情况，如 CPU，IO，IPC，SWAP 等，以及发生的 PAGE FAULTS，CONTEXT SWITCHE 等等，同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置。

查看 profiling 参数

show variables like '%profi%';

profiling 默认是关闭的，所以需要手动开启

开启profiling

 set profiling = 1;

执行查询语句

select * from t_user where id = 2

查看所有sql的profiles

show profiles

查询单个sql的profiles

show profile cpu for query 6;   -- 6是id

总结

#1.show profile默认是关闭的，并且开启后只存活于当前会话，也就说每次使用前都需要开启。

#2.通过show profiles查看sql语句的耗时时间，然后通过show profile命令对耗时时间长的sql语句进行诊断。

#3.注意show profile诊断结果中出现相关字段的含义，判断是否需要优化sql语句。

MySQL储存引擎

  数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）**使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能**。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。 

常用的存储引擎有以下：

• Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。MySQL默认值的存储引擎。
• MyIASM引擎：不提供事务的支持，也不支持行级锁和外键。

MyISAM与InnoDB对比

	MyISAM	Innodb
存储结构	每张表被存放在三个文件： frm-表结构定义、 MYD(MYData)-数据文件、 MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中， InnoDB表的大小只受限于操作系统文件的大小， 一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储， 它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
文件格式	数据和索引是分别存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
锁支持	表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
SELECT	MyISAM更优
INSERT、UPDATE、DELETE		InnoDB更优
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
哈希索引	不支持	支持
全文索引	支持	支持(MySQL5.6之前不支持)

引擎的选择

如果没有特别的需求，使用默认的Innodb即可。
MyISAM：查询效率高，储存空间小。
Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。。

EXPLAIN SQL执行计划

EXPLAIN 命令是查看查询优化器如何决定执行查询的主要方法,使用EXPLAIN,只需要在查询中的SELECT关键字之前增加EXPLAIN这个词即可,MYSQL会在查询上设置一个标记,当执行查询时,这个标记会使其返回关于在执行计划中每一步的信息,而不是执行它,它会返回一行或多行信息,显示出执行计划中的每一部分和执行的次序,从而可以从分析结果中找到查询语句或是表结构的性能瓶颈。

EXPLAIN能干嘛

1. 分析出表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以使用
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

EXPLAIN如何用

explain select * from t_user;

EXPLAIN结果参数含义

ID

代表执行select子句或操作表的顺序,

-- id约大，越先执行
EXPLAIN
select * from t_user u1 where u1.id =(
select u.id from t_user u where u.id = 2)

-- id相同,执行顺序由上至下
EXPLAIN
select * from t_user u1 where u1.id in(
select u.id from t_user u where u.id = 2)

-- id相同和不同,同时存在,遵从优先级高的优先执行,优先级相同的按照由上至下的顺序执行
EXPLAIN
select * from t_user u1,t_user u2 where u1.id =(
select u.id from t_user u where u.id = 2)

select_type

查询的类型,主要用于区别普通查询,联合查询,子查询等复杂查询

• simple: 简单的select查询,查询中不包含子查询或union查询
• primary: 查询中若包含任何复杂的子部分,最外层查询则被标记为primary
• subquery 在select 或where 列表中包含了子查询
• derived 在from列表中包含的子查询被标记为derived,mysql会递归这些子查询,把结果放在临时表里
• union 做第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为derived
• union result union之后的结果集产生

-- 临时表
explain
select * from t_user u1,(select * from t_user u2) u3

-- UNION和UNION Result
explain
select * from t_user u1
UNION
select * from t_user u2

table

显示一行的数据时关于哪张表的

type

查询类型从最好到最差依次是:system>const>eq_ref>ref>range>index>All,一般情况下,得至少保证达到index级别,最好能达到ref。

system:表只有一行记录,这是const类型的特例,平时不会出现

const:表示通过索引一次就找到了,const即常量,它用于比较primary key或unique索引,因为只匹配一行数据,所以效率很快,如将主键置于where条件中,mysql就能将该查询转换为一个常量

eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描

ref:非唯一性索引扫描,返回匹配某个单独值的行,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体

range:只检索给定范围的行,使用一个索引来选择行,如where语句中出现了between,<,>,in等查询,这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

index: 扫描所有索引,这通常比All快,因为索引文件通常比数据文件小,index是从索引中读取,all从硬盘中读取

all:全表扫描,是最差的一种查询类型

-- const:主键索引扫描
explain
select empno from emp where empno = 7369;

-- eq_ref：唯一索引扫描(需要删除主键索引后才能看到效果)
alter table emp  add UNIQUE(ename); -- 添加唯一索引
explain
select ename,sal from emp where ename = 'ALLEN';

-- ref:非唯一索引扫描
alter table emp  add index job_index(job); -- 添加普通索引
explain
select * from emp where job = 'SALESMAN';

-- range：范围扫描
explain
select ename from emp where ename like 'al%' -- 前面加%会导致索引失效

-- index: 扫描所有索引
explain
select empno from emp 

-- all：全表扫描
explain
select * from emp

possible_keys

显示可能应用在这张表中的索引,一个或多个,查询到的索引不一定是真正被用到的

key

实际使用的索引,如果为null,则没有使用索引,因此会出现possible_keys列有可能被用到的索引,但是key列为null,表示实际没用索引。

key-length

表示索引中使用的字节数,而通过该列计算查询中使用的 索引长度,在不损失精确性的情况下,长度越短越好,key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即,key_len是根据表定义计算而得么不是通过表内检索出的

ref

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

explain
select * from emp e,dept d where e.deptno = d.deptno

rows

根据表统计信息及索引选用情况,大只估算出找到所需的记录所需要读取的行数

explain
select * from emp

explain
select * from emp e,dept d where e.deptno = d.deptno

Extra

Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为"文件排序"

Using temporary :使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于order by和分组查询group by

Using index:表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。 其中的覆盖索引含义是所查询的列是和建立的索引字段和个数是一一对应的

Using where:表明使用了where过滤

Using join buffer:表明使用了连接缓存,如在查询的时候会有多次join,则可能会产生临时表

impossible where:表示where子句的值总是false,不能用来获取任何元祖。

select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作，即一旦MySQL找到了与行相联合匹配的行，就不再搜索了。

null：代表的没有使用索引，扫描的是全表。

-- Using index
explain
select empno from emp order by empno

-- Using filesort
explain
select * from emp order by sal

-- Using temporary;
explain
select comm from emp group by comm;

-- Using where
explain
select job from emp where job like 'a%'

小结

type：访问类型，查看SQL到底是以何种类型访问数据的。
key：使用的索引，MySQL用了哪个索引，有时候MySQL用的索引不是最好的，需要force index()。
rows：最大扫描的列数。
extra：重要的额外信息，特别注意损耗性能的两个情况，using filesort和using temporary。