MySQL高级

一，MySQL逻辑架构介绍

执行sql变慢原因：查询语句写的烂，索引失效，太多join，服务器调优

sql执行顺序：手写，机读

总结：先读 from   on  join where group by hiving  select distinct  order by  limit 

七种join：

链接：https://pan.baidu.com/s/1GUzPFVG3Je9uT419rHE8MQ 
提取码：ybfi

一.索引

1.索引：

是一种高效获取数据的数据结构，作用在于提高查询速度，可以类比字典。可以简单理解为：排好序的快速查找数据结构

所以对 查找和排序都有影响。

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。BTREE, 索引往往存在磁盘中

优势：提高了查询检索的速度，降低了数据排序的成本

劣势：占用磁盘空间，加大修改，插入，删除时间

 

2.索引分类：

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

唯一索引：索引列必须唯一，但是允许有空值

复合索引：即一个索引包含多个列

基本语句：

创建：

create 【UNIQUE】INDEX indexName on MyTable(columnname(length));

alter mytable add [unique] INDEX [indexName]  on MyTable(columnname(length));

删除：

DROP INDEX [indexname] on mytable

查看：

show index from table_name\G

四种方式创建

 

3.索引结构

BTREE索引

hash索引

full_text全文索引

R_TREE索引

 

4.哪些情况适合建索引

a.主键自动建立唯一索引

b.频繁作为查询条件字段

c.频繁更新的字段不适合索引

d.where 条件中用不到的字段也不适合索引

e.单值和组合索引的选择

f.查询中排序的字段，也就是order by 后面的字段

g.查询中分组或者统计字段，也就是group by后面字段

 

5.哪些情况不适合建索引

a.数据量较少300w 往上

b.经常增删改

c.重复且平均分配的值，如性别，国籍

 

二.性能分析

1.mysql 默认查询器：MySQL它本身是有sql优化器的

2.mysql常见瓶颈：cpu饱和，io，服务器硬件瓶颈

3.explain：使用explain关键字可以模拟优化器如何执行sql优化的，从而知道MySQL是如何处理你的sql的，分析查询你的sql瓶颈

a.怎么用：explain + sql

b.能干吗：1.表的读取顺序，2.数据读取类型 3.哪些索引可以使用 4.哪些索引被实际引用 5.表之间的引用 6.每张表有多少行查询优化器

id  select_type table   type   possible_keys  key  key_len  ref  rows extra

1.id  id相同  执行顺序是由上向下的。id不同 ，id越大越先执行。  id相同又不同

2.select_type:

a.simple 简单的select查询，不包含子查询或者union

   primary 查询中若包含任何复杂的子部分，最外层查询则会被标记

  subquery 子查询

  derived  在from列表中包含子查询会标记成derived，并且把结果放在临时表中

union 第二个select出现在union之后，则会标记为union

union result 两个union结果集

3.type

结果从好到坏

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

只要是百万级别all 的必须优化。一般来说要到range或者ref级别才行

system：单表就一行数据，相当于系统表

const：表示通过一次索引就找到了 select * from t1 where id=1;

eq_ref:唯一性索引扫描。

ref:非唯一性扫描

 

4.possible_keys  keys

1.是否使用到索引，索引是否失效，用到了哪个索引

possible_keys 可能用到哪些索引 ，keys实际用到的索引

 

5.key_len

表示索引中使用的字节数，可以通过该列计算查询中使用的索引长度，在不损失精确性情况下，长度越短越好

key_len 显示值为索引字段的最大可能长度，并非实际长度

 

6.ref

 

7.rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录，所需要读取的行数。越小越好，每张表有多少行被优化器所优化

8.extra：包含不适合在列中显示，但十分重要的字段

 

三，索引的优化

单表优化：建表

 

 

三.查询截取分析：

1.观察，至少跑一天，看看慢的sql

2.开启慢查询日志，设置阈值，比如时间超过5s的sql

3.explain+慢sql查询

4show profile

5.运维经理 or dba 调优

总结：

1.慢查询优化

a.永远小表驱动大表，即小的数据集渠道大的数据集

in 和 exists区别。

b.order by 优化：

2.explain+慢sql优化

a.慢查询，默认mysql没有开启慢查询，一般不建议开启，主要是用来查看查询速度

3.show profile

是什么：是mysql提供可以用来分析当前会话语句执行的资源消耗情况，可以用于sql优化测量

默认是关闭的，并且保存最近15次运行结果

分析步骤：

4.sql 参数调优

5.全局查询日志

永远不要在生产环境开启，

set global=1;

set  global log_output=1;