Mysql优化

---

前言

本文章主要描述如何使用mysql优化,并且面试题会面试哪些内容。

---

一、Mysql的逻辑分层

1. 链路层

主要的功能是与客户端进行连接服务,授权认证。

2. 服务层

内置sql优化器,主要执行用户的sql

3. 存储引擎

主要提供储存引擎,常用的存储引擎为innoDB和MyISAM,两者的区别主要为:MyISAM不支持主外键,不支持事务，表锁，只缓存索引。Innodb支持事务和行锁

4. 存储层

数据存储运行在文件系统上,并且与存储引擎进行交互

二、sql的执行顺序

1. FROM <left table>
2. on  <join condition>
3. join <right table>
4. WHERE
5. group by
6. having
7. select 
8. distinct 
9. union 
10.order by 
11.limit   

三、分析sql执行效率慢有哪些原因

 1. 查询语句写的烂
 2. 索引失效
 3. 查询关联太多join
 4. 服务器调优以及各个参数的设定(运维层次)

四、如何优化（创建索引）？

1.索引概述

• 帮助mysql高效获取数据的数据结构
• 排好序的快速查找数据结构

2.影响范围

• 查找和排序(where 和 order by)

3.优势和劣势

• 优势：提高查找效率
• 劣势：降低表更新速度。在进行更新的时候,不仅要保存数据还需要添加索引字段

4.索引的分类

1.单值索引

一个索引值只包含单个列,一个表可以有多个单列的索引

2.复合索引

一个索引包含多个列

3.唯一索引

索引列的值必须是唯一的,但是允许有空值

5.哪些情况创建索引

• 频繁查找的条件
• 主键自动创建索引
• 查询字段与其他字段有外键关系

6.哪些情况不创建索引

• 经常更新的字段
• 在where条件中使用不到的字段
• 表内容少不需要创建索引
• 数据重复且平均(某个字段只有true或者false)

7.Explain进行性能分析

1.Explain能做什么？

• 获取表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以被使用
• 哪些索引实际中被使用
• 表之间的引用

id	select_type	table	type	possible_key	key	key_len	ref	rows	extra

2.分析每个字段的含义

• id

select查询的序列号,包含一组数字,表示查询执行select子句的顺序
主要有三种情况
1.id相同,顺序由上往下依次执行
2.如果是子查询,id的序号会递增,越大优先级越高
3.id如果相同,可能是通一组顺序从上往下,否则序号越大越先执行

• select_type

simple
简单的查询,不包含子查询或者union
primary
查找中若包含任何复杂的子查询,最外层被标记为primary
subquery
查找中包含子查询
derived
在from列表中包含子查询被标记的（Derived）衍生表，会把结果集放到临时表中
union
若第二个select出现在union之后,则标记为union。若union包含在子查询中,外层的select会被标记为derived
union result
从union获得的select(两个union合并的结果集)

• table

具体的表

• type

all
index
index和all都是读全表,但是index是从索引中读取,all是从硬盘中读取
range
检索给定范围的行,使用一个索引来选择行(比如between，in)
ref
非唯一性扫描,使用匹配某个单值的所有行
eq_ref
唯一性所有扫描
const
表示一次就找到了,用于比较primary和unique的索引
system
表只有一条记录（等于系统表）
null
访问的类型,从最好到最差
system->const->eq_ref->ref->range->index->all

• possible_key

可能被使用到的索引

• key

实际被使用到的索引

• key_len

标识索引的字节数,越短越好

• ref

表示索引被哪一列使用到了,可能是一个常量

• rows

根据表统计信息,大致算出找到所需记录需要读取的行数

• extra

包含不适合在其他列中显示额外的重要信息
using_filesort：说明mysql会对数据使用一个外部索引排序,而不是按照表内的索引顺序进行读取。mysql无法利用索引完成排序操作的"文件操作"
using_temporary:新建一个临时表,常见于order by 和group by
using_index:使用到了覆盖索引
using where:查找条件使用到了索引
using join buffer：使用到了连接缓存
imposible where:where子句总是false，不能用来执行任何元素(例子:select * from A where A.id=‘A’ and A.id=‘B’ id不能同时是A或者B,假设id是唯一的)

索引失效

最佳左前缀法则：查询从索引的最左列开始并且不跳过索引的列
例子：假设我建立了一个复合索引p_index_nameAgePos 
select * from A where a.age = '1' and a.pos ='2' //索引失效,带头大哥没了
select * from A where a.name ='1' and a.pos ='2' //索引部分失效,因为age没有使用直接跳到pos

不在索引列上做任何操作(计算,函数,自(动or手动)类型转换 比如是字符串'2000' 你写成2000),会导致索引失效而转向全表扫描
例如： 
select * from A where name ='A' 正常
select * from A where left(name,1) = 'A' //索引失效

存储引擎不能使用范围条件右边的列
select * from A where name ='A' and age>25 and post='manager'; //age后面的索引失效

在mysql中使用(!=或者<>)的时候会导致索引失效
select * from A where name ='a' //索引正常
select * from A where name <> 'a' //索引失效

is null 或者 is not null;会导致索引失效
字符串不加引号会导致索引失效
少用or，用它来连接会导致索引失效

like以通配符开头('%abc..')mysql索引会失效
select * from A where name like '%a%' //索引失效
select * from A where name like 'a%'  //索引不失效
如果不想索引失效,使用覆盖索引

---

总结

以上就是mysql优化大致内容,注意在写sql时是否涉及到索引,如果查询效率慢,是否在建索引的情况下使用到了全文检索,在查询条件中索引又失效而导致的查询效率慢。