MYSQL

MYSQL

连接层，service层（sql执行和优化器），引擎层，存储层

SQL优化：主要是优化索引

1，索引（一种数据结构）（b+树，hash树）

MySQL索引分类：

1. 单值索引，
2. 唯一索引
3. 符合索引

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

​ 方式二 ：alert table 表名 add 索引 类型 索引名 （字段名）

删除索引 ： drop index 索引名 on 表名

查询索引 ： show index from 表明

explain 解析

参数：

​ id ：执行顺序 ： 如果id相同则从上往下顺序执行，如果不同 则 id值越大越先执行

​

​ table :

​ select_type :

​ primary：包含子查询的sql的 主查询（最外层）

​ subQuery: 包含子查询的sql的 子查询

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

​ derived:衍生查询（临时表）

​

​ type :

​ 索引类型：

​ system 》const 》 eq_ref 》 ref》range》index》all

​ system，一般出现在只有一条数据的系统表，

​ const，一般针对 索引必须为 priamry，unique 的字段，只能查出一条数据的sql

​ eq_ref, 针对唯一索引，针对每个索引键的查询，都有唯一行数据对应（只能为1，不能为0），

​ ref, 针对非唯一性索引，返回一条或多条都可。

​ range，针对索引列范围查询。（在where后面出现 > < = between , 注意 in 有时候会失效）

​ index，查询的列都是 索引字段，

​ all，扫描表中的全部列数据

​ possible_keys :

​ key :

​ key_len : 索引的长度，通常用于判断 复合索引是否 全部被使用

​ ref : 当前表所参照到的字段（可以是const常量 等等）

​ rows : 实际通过索引查询的到的行数

​ Extra :

​ 1，using filesort : 性能消耗较大，需要额外的一次排序

​

​

​ 2，using temporary; 性能消耗较大 ，用到了临时表 （一般出现在group by 语句中）

​ 3，using index ： 性能提升，索引覆盖，原因：查询的都是索引列数据，不用回表查询其他的列数据

只要使用到列， 都在索引中就会出现using index;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7Il3eK8V-1579240026199)(C:\Users\Administrator\Desktop\mysql\usringinde.png)]

​ 4, using where : 需要会原表查询。

优化

1，对于连接查询，以 小表驱动大表

左外连接以左表为主

2，复合索引使用（！= 或者 <> ）is null is not null （可能）会使自身索引或者右边索引失效，

注意：sql优化是个概率性时间，原因有 service层的优化器 会对优化进行干预。

所以 通过 explain 检验优化

3，补救，针对 服务层 优化器，尽量使用索引覆盖（using index）

select a , b , c from x where a =.. and  b=..  and c=...

4, like 关键字使用，尽量使用 ”x%“ 这种形式，

如果非要使用 ”%x%“ 这种形式的sql ，可以使用索引覆盖 挽救一部分性能

例如：select  name   from user  where   name like "%X%"

5, 尽量不要使用 类型 转换（显示 和 隐式） 否则会使索引失效

6，使用 or 关键字 会使 索引失效（甚至有可能会使or 左侧 的索引全部失效），

7， 使用in 和exists

exists : 将主查询的结果放到子查询的中匹配，匹配成功则 保留，失败则不保留

• 如果 主查询 的结果大于子查询 中的结果，使用 in 可能效率会高
• 如果主查询 结果小于 子查询 结构 ，则使用exists 效率会高

selcet  name  from  user where  id  in（1，2，3）

8，order by 优化

using filesort : 排序。单路排序，双路排序（io的次数）

在MySQL 4.1 之前，采用的是双路排序：第一扫描排序字段，在buffer 中排序，第二次扫描 其他字段。

在MySQL4.1之后，采用的是单路排序： 一次读取全部字段，排序也是在buffer中进行，存在问题：（未必是一次，也可能会两次或者多次IO ），

原因：buffer大小有限，可以设置buffer 参数 ：set max_length_for_srot_data= 2048

如果设置的值太小mysql会自动切到 双路；

优化：

• 设置buffer大小
• 避免使用 select * （* 代表的列不明确，而且会导致索引覆盖的情况消失）
• 不要跨列，防止复合索引失效
• 多个字段排序，要保证排序字段顺序的一致性

9，慢查询日志开启

注意：开始只是临时的，部署的时候要关闭否者会影响性能‘

show variables like “%slow_query_log%”

设置慢查询时间 ： set global long_query_time =1 (默认是10s)

设置慢查询日志保存地址： set slow_query_log_fiile =

临时开启：set global slow_query_log=1

永久开启需要在 my.cnf中设置;

对于慢查询的sql

可以通过 mysqldumpslow（自带）工具查看，也可以通过其他pt-query-digest

10,海量数据分析

锁机制

解决资源共享安全性问题

查看枷锁表情况

show open tables

分类

按照操作类型分：读锁（共享）

​ 写锁（互斥的，读操作也不允许）

按操作范围：

表锁：如myisam，使用表锁，枷锁开销小，速度快；单锁的粒度大，并发度低

行锁：一次锁一行数据，力度小，并发大，开销大，速度慢，但是会发生锁冲突，死锁。

在会话中对A表加读锁：

在同一个会话中：锁A表后，可以读，但是不能写，其他表也不能读

在不同会话中：对A表，可以读，但是写操作需要等待 上个锁表的会话 释放锁才能操作； 在其他会话中可以对其他表进行操作。

在会话中对A表加写锁：

在同一个会话中：可以对锁的A表，进行增删改 操作，但是不能对其他表操作

在其他会话中：对A表的操作须等待 上一个 会话释放锁。

MyIsam锁总结

select：默认加读锁

update,delete: 默认加写锁

Innodb锁

行增删改 操作，但是不能对其他表操作

在其他会话中：对A表的操作须等待 上一个 会话释放锁。

MyIsam锁总结

select：默认加读锁

update,delete: 默认加写锁

Innodb锁