MySQL高级

1.Mysql查询流程

• mysql客户端通过协议与mysql服务器连接，发送查询语句，先查询缓存，如果缓存命中，则直接返回结果，否则对语句进行解析、优化(mysql会优化语句的执行顺序)和执行。

1.常见join连接图

1.索引是什么

索引是一种帮助mysql高效获取数据的数据结构（一般来说为二叉树)

1.B+树

• B+树更适合实际应用中操作系统的文件索引和数据库索引
  • B+树非叶子节点不存放数据
  • 叶子节点存放关键字和数据，非叶子节点的关键字也会下沉到叶子节点，并且排序
  • 叶子节点指针两两相连，查询效率高
• 优势

  • B+树读写代价更低
    B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。每次io就可以读取更多的关键字。相对来说I/O读写次数也就降低了。降低io次数，极大提高了效率

  • B+树的查询效率更加稳定
    B+树非叶子节点不存放数据。所以任何关键字的查找必须走一条从根结点到叶子结点的路。 所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

B+树单值查询

• InnoDB 存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值 + 指针，在 B+ 树中叶子节点存放数据，非叶子节点存放键值 + 指针。
• 索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据；

B+树存放数据

• 在 MySQL 中我们的 InnoDB 页的大小默认是 16k，一般默认一行数据为1k，在叶子节点中每页可以存储16行数据
• 在非叶子节点，每页存储的是键值 + 指针，假设索引为主键id（int类型）长度为8字节，指针在InnoDB中一般为6字节，一共为14字节，则一页可以存储键值 + 指针的数量为16x1024/16=1170
• 那么可以算出一棵高度为 2 的 B+ 树，能存放 1170x16=18720 ，高度为3的可以存储1170x1170x16=21902400

1.索引类型

• 单值索引
  即一个索引只包含一个列，一个表可以有多个单值索引
  
• 唯一索引
  索引列的值必须唯一，但允许有null
  
• 主键索引
  设定为主键后数据库会自动建立索引
  
• 复合索引
  一个索引包含多个列
  

1.explain性能分析

用法：

• Explain+SQL语句

***①id

• id形同，执行顺序由上至下
  
• id不同，id值越大优先级越高，越先被执行
  
• 有相同也有不同
  
  id如果相同可以认为是一组，从上往下顺序执行，在所有组中，id越大优先级越高。

重点：id号每个号码，表示一趟独立的查询，一个SQL的查询趟数越少越好

***②select_type

select_type代表查询的类型，主要是用于区别普通查询、联合查询、子查询等复杂查询。

select_type	含义
simple	简单的select查询，查询中不包含子查询或者union
primary	查询中若包含任何复杂的子部分，最外层查询被标记为primary
subQuery	在select或where列表中包含了子查询
union	若第二个select出现在union之后，则被标记为union
union result	从union表获取结果的select

③table
显示这行数据是基于哪张表的
***④type
type是查询的访问类型。是较为重要的一个指标

结果值从最好到最坏依次是：
system>const>eq_ref>ref>range>index>all

一般来说，得保证查询至少达到range级别，最好能达到ref。

• eq_ref
  唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
• ref
  非唯一性索引扫描，返回匹配某个单独值的所有行
• range
  只检索给定范围的行,比如在where中出现的between，<、>、in限制查询范围
• index
  index和all的区别是index只遍历索引树，这通常比all快，因为索引文件通常比数据文件小。也就是说index和all都是读全表，但是index是从索引中读取的，all是从硬盘中读取的。
• all
  遍历查找全表

***⑥key
实际使用的索引，如果为null，则没有使用索引

⑦key_len

表示索引中使用的字节数，即索引字节的长度。索引长度越长查询结果越精确相反效率会变低。

***⑧rows

找到数据需要读取的行数，越小越好，可以通过索引优化将其变小

***⑨extra

• using filesort
  如果排序不是通过索引来完成，那么mysql有可能进行文件排序。出现此提示会降低性能，解决办法：最好通过索引来排序（将需要排序的字段建立索引）

• using temporary
  使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，出现此字段严重影响性能。常见于排序order by和分组查询group by。
  解决办法：group by尽量按照索引的个数和顺序来分组

• using index
  表示响应的select操作中使用了覆盖索引，避免了访问表的数据行，效率不错。
  如果同时出现using where，表明索引被用来执行索引键值的查找；如果没出现using where，表明索引用来读取数据而非执行查找功能。

3.索引创建的时机

• 适合创建索引的情况
  • 主键自动创建索引
  • 频繁作为查询条件的字段应该建立索引
  • 查询中与外表关联的字段，外键创建索引
  • 在高并发的情况下倾向创建复合索引
  • 查询中排序的字段能通过索引显著提高查询速度
  • 查询中统计或者分组的情况
• 不适合建立索引的情况
  • 数据差异率不高不适合建立索引，比如性别
  • 表数据量较少（一般300万以上才会考虑优化)
  • 经常增删改查的数据（建立索引提高查询速度的同时也会降低表的更新速度）
  • where条件中用不到的字段不创建索引

3.索引优化

• 复合索引中，范围查找后的索引会失效（比如大于，小于）。优化思路：可以删除复合索引中需要范围查找的字段。
• 在左连接和右连接中，建立索引要相反建立。比如在左连接中，左表全部都有，所以为了提高查询效率，一定要在右表建立索引。即一般小表为驱动表，在小表上建立索引效率高
• 索引最好建在经常查询的字段
• 主键建议使用自增，能显著提高效率，尽量不要使用无序的UUID
• 尽量使用数据量小的字段建立索引，这样索引维护的空间和时间都能减小
• 尽量为order by和group by后面的字段建立索引，这样可以减少临时表的生成，提高效率

4.避免索引失效的方法

• 全值匹配
• 在选择组合索引中，当前过滤性越好的字段在索引中的位置越靠左越好
• 最佳左前缀法则
  指得是查询从复合索引的最左前列开始并且不跳过索引中的列
• 不在索引上列上做任何操作（计算、函数），会导致索引失效转向全表扫描
• 尽量使用覆盖索引，减少select*，需要什么取什么
• mysql在使用不等于 <> is null is not null 时无法使用索引
• 尽量少用or,导致索引失效
• 排序order by要按照复合索引的顺序来排序，能够提高性能

4.最佳左前缀原则

• 使用复合索引时要遵守最佳左前缀原则：即查询从索引的最左边开始，并且不跳过索引中的列。一旦跳过，会使索引后面的字段都无法使用，甚至造成索引失效

5.索引分析的一般过程

• 观察，至少跑一天，看看生产的慢SQL生产情况
• 开启慢查询日志，并设置阈值，比如超过5秒的就是慢SQL
• explain+慢SQL分析
• show profile查询SQL语句在服务器中的执行情况和生命周期
• 运维经理OR DBA，进行数据库参数调优

6.小表驱动大表

7.ACID

7.不可重复读

7.事务隔离级别

7.表锁和行锁

• 表锁
  偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 行锁
  偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度小，会发生锁冲突的概率低，并发度最高

• InnoDB和MyISAM
  最大区别为，InnoDB支持了行锁和事务

8.InnoDB&&MyISAM

9.慢查询日志

• Mysql慢查询日志是Mysql提供的一种日志记录，它用来记录在Mysql响应时间中超过阈值的语句，具体指运行时间超过long_query_time(默认为10s)值的SQL，则会被记录到慢查询日志中。
• 我们可以收集慢查询日志中的语句，进行explain分析
• Mysql默认是关闭慢查询日志，如果不是为了调优，建议不要开启此功能，因为会对性能带来影响
• 慢查询相关命令
  
  慢查询日志存放位置：
  
• 日志查询工具mysqldumpslow（了解）

10.mysql主从复制

11.覆盖索引&&聚簇索引

覆盖索引：一个索引包含所有要查询的字段的值。

• 索引条目通常远小于数据行，因此如果只读取索引，可以极大的减小数据访问量，同样索引也更容易放入内存，所以对于I/O密集型的应用有很大帮助。
• 索引是按照列顺序存储的

聚簇索引：聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

• 聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。