数据库进阶MYSQL分层及其优化

1.mysql分层

1. 连接层：提供与客户端连接的服务
2. 服务层：1.提供各种用户使用接口，2提供sql优化器（写的语句和执行可能不一致）
3. 引擎层：提供了各种存储数据的方式
   1. InnoDB :事物优先,（适合高并发操作 使用行锁）
   2. MyISAM:性能优先（表锁）
4. 存储层：存储数据
   查询数据库支持哪些引擎：show engines;

2.SQL优化简述

2.1原因：性能低、执行时间长，等待时间长，sql语句欠佳（连接查询），索引失效、服务器参数设置不合理（缓冲、线程数）
2.2sql优化:主要就是优化索引

• 索引相当于书的目录
• index是帮助mysql高效获取数据的数据结构。索引是数据结构（树：b树、二叉树。。）
• 索引的弊端：
  1. 索引本身很大，可以放到内存/硬盘中
  2. 索引不是所有的情况都适用：a.少量数据 b.频繁的更新的字段 c.很少使用的字段
  3. 索引会降低增删改的效率（因为更改以后还需要去更改索引）
• 优势
  1. 提高查询效率（降低了io的使用率）
  2. 降低cpu的使用率（因为已经做好了排序等）

3.索引

3.1分类

单值索引：单列，一个表可以有多个单值索引
唯一索引：不能重复 id（和主键索引的区别 主键不能为null）
复合索引：多个列构成的索引（相当于 一级目录二级目录。。。）

3.2创建索引

方式一：

create 索引类型 索引名 on 表名(字段)
例：
单值：create index name_index on user(name);
唯一索引:create unique index id_index on user(id)
复合索引:create index name_id_index on user(name,id)

方式二：

**alter table 表名 add 索引类型 索引名(字段) **
例：
单值：alter table user add index name_index(name);
唯一索引:alter table user add unique index id_index(id)
复合索引:alter table user add index name_id_index(name,id)

3.3删除索引

drop index 索引名 on 表名

3.4查询索引

show index from 表名;

4. SQL性能问题

• 分析SQL的执行计划
  • explain，可以模拟sql优化器执行SQL语句，从而让开发人员知道自己的编写语句的性能
• mysql查询优化会干扰我们的优化
• 查询执行计划：explain +SQL语句

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

4.1 查询参数问题：

id：

• id值相同，表记录小优先查询
• 多表查询时会因为表数量记录的个数改变而改变：笛卡尔积（中间值越小越好）导致最小的表优先查询
• id值不同，id值大的优先查询

select_type ：

• Primary:包含子查询sql中的 主查询（最外层）
• subquery：包含子查询sql中的 子查询 （非最外层）
• simple： 简单查询
• derived：衍生查询（用到了临时表）在from查询中只有一张表 from下时union关系

type ：索引类型

system>const>eq_ref>ref>range>index>all

• 性能system最大 all最小
• 需要优化的前提：有索引
• system,const只是理想情况，实际能达到 ref>range
  system:只有一条数据的系统表：或衍生表只有一条数据的主查询
  const:仅仅能查到一条数据的sql，用于primary key或unique索引
  eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且仅有1条）：表中的个数和连接查询的数据个数一致，则有可能eq_ref级别
  ref：非唯一索引，对于每个索引键的查询，返回匹配的所有行
  range：检索指定范围的行，where后面是一个范围查询
  index：查询全部索引的数据
  explain select 索引列 from user
  all：将全部表查询一表
  explain select 非索引列 from user
  总结：
  system/const:结果只有一条数据
  eq_ref:结果多条，但是每条数据是唯一的
  ref：结果多条，但是每条数据是0或者多条

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

key：实际使用到的索引

如果possible_key和key是NULL，则说明没用索引

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

在utf8：一个字符站三个字节
gbk：一个字符两个字节
latin：一个字符一个字节
如果索引字段可以为NULL，则会使用1个字节用于标识

ref:指明当前表所 参照的 字段

rows: 被索引优化查询的 数据个数

Extra：duan

using filesort : 性能消耗大；需要"额外"的一次排序（查询）(查询一个字段 排序一个另一个字段)
小结：对于单索引，如果排序和查找是同一字段，则不会出现using filesort；
复合索引：不能跨列（最佳左前缀）
避免：where 那些字段 就order by那些字段
using temporary：性能损耗大，用到了临时表。一般出现在group by 语句中（已经查了一张表但是不适用 还得在查一张表）
避免：查询 那些列 就根据那些列order by
**using index **:性能提升：索引覆盖（覆盖索引）。原因：不读取原文件。只从索引文件中获取数据
只要使用到的列：全部都在索引中，就是索引覆盖
如果用到了索引覆盖（using index时），会对possible_key和key造成影响
a.如果没有where，则索引只出现在key中；
b.如果有索引，出现在key和possobler_keys中
using where：（需要回表查询）
impossible where:where结果永远为false
using join buffer：extra中的一个选项，作用：mysql引擎使用了连接缓存

5. 优化

5.1 单表优化

优化：加索引

• 根据SQL实际解析的顺序，调整索引的顺序
• 索引一旦进行升级优化，需要将之间废弃的索引删掉，防止干扰

5.2 多表优化

优化：将数据小的表 放在外面 （小表在左）

• 索引建立在经常使用的字段上（左外连接给左表加索引，右外连接给右表加索引）
• 小表驱动大表

5.3 避免索引失效的一些原则

• 复合索引：不要跨列或无序使用（最佳左前缀）
• 复合索引：尽量使用全索引匹配
• 复合索引：左边失效右边全部失效
• 复合索引：不能使用不等于否则自身失效以及右侧全部失效
• 复合索引：有大于号则自身失效
• 独立索引：互不影响
• 不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效’
• SQL优化不会百分之百的优化，所以顺序要写对
• 服务层的优化器可能影响我们的优化
• 一般情况下范围查询之后的索引失效
• ！！！尽量使用索引覆盖（百分百成功）
• like经量使用常量开头，不要以**%**开头，否则索引失效，如果必须使用，则用索引覆盖
• 尽量不要使用类型装换（显示隐式）否则会失效
• 尽量不要用or否则索引失效，or会将左右都失效

5.4 一些优化方法

• 如果主查询的数据集大，则使用in
• 如果子查询的数据集大，则使用exist
• order by 优化 using filesort 有两种算法：双路排序、单路排序（根据IO的次数）

mysql4.1之前用的 双路排序：双路：扫描2次磁盘（1：从磁盘读取排序字段 （在buffer中进行排序）2：扫描其他字段）
改成单次 因为io比较消耗性能
mysql4.1之后用的 单路排序：只读取一次（全部字段），在buffer中进行排序 会有一定的隐患（不一定真的是”单路|1次IO“，有可能多次IO）。原因：如果数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行‘分片读取、多次读取’；
注意： 单路排序会比双路排序占用更多的buffer
单路排序在使用时，如果数据大，考虑调大buffer的容量大小。 set max_length_for_sort_data =1024; 单位字节
如果max_length_for_sort_data值太低，则mysql会自动从单路->双路

• 提高order by查询的策略：
  1. 选择使用单路，双路：调整buffer的容量；
  2. 避免使用 select *
  3. 复合索引 不要跨列使用
  4. 尽量保证排序字段的 排序一致性（都是升序 或 都是降序）

5.5 SQL排查-慢查询日志：

mysql提供的一种日志记录，用于记录MYSQL中响应时间超过阈值的sql语句（long_queryy_time,默认10秒）
慢查询日志默认是关闭的；建议：开发调优是 打开，而 最终部署时关闭；
慢查询日志是否开启：show variables like '%slow_query_log%';
开启慢查询：

• 临时开启：set global slow_query_log=1; --在内存中开启
• 永久开启：更改mysql配置文件
  /etc/my.cnf 中追加配置，在mysqld中追加
  slow_qurey_log=1
  slow_query_log_file=/var/lib/mysql/localhost-slow.log

慢查询阈值：show variables like '%long_query_time%';

• 临时设置阈值：
  set global long_query_time = 5; --修改后，需要重新登录（不需要重启服务）
• 永久设置阈值：
  /etc/my.cnf 中追加配置，在mysqld中追加
  long_query_time=3

测试一下：

1. select sleep(4);
2. show global status like ‘%slow_queries%’;
3. 慢查询的sql被记录在了日志里面，因此可以通过日志，查看具体的慢sql日志
4. 通过mysqldumpslow --help
   • s：排序方式
   • r：逆序
   • l：锁定时间
   • g：正则匹配模式

–获取返回记录最多的 mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log
–获取访问次数最多的3个SQLmysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log
–按照时间排序，前十条包含left join查询语句的mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

6. 分析海量数据

分析海量数据：profiles
show profiles; —默认关闭
set profiling = on; //开始记录
show profiles; 会记录所有profiling 打开之后的 全部sql查询语句所花费的时间
缺点：不够精确，只能看到总共消费的时间，不能看到各个硬件消费的时间
精确分析：sql诊断
show profile all for query id值 上一步查询的sqlid
show profile cpu,blockio fo query id值 只查看和cpu io相关的
看全局查询日志：记录开启之后的，全部sql语句；（这次全局的记录操作，仅仅在调优、开发过程中打开即可，在最终部署时，一定关闭）
show variables like '%general log%'
set global general_log=1;打开全局日志:开启后会记录所有的sql
set global log_output='table';记录到表中
会记录到mysql.general_log表中
select * from mysql.general_log

7. 锁机制

解决因资源共享而造成的并发问题
分类：
操作：

• 读锁（共享锁）
• 写锁（独占锁）
  范围：
• 表索：MyISAM存储引擎使用：开销小、加锁快、无死锁，容易发送锁冲突、并发度低
• 行锁 ： innoDB存储引擎使用：开销大、加锁慢、容易死锁，不容易锁冲突、并发度高
• 页锁