mysql监控分析（3）

刚才已经获取到慢sql，再看sql执行计划之前，先补一下索引的基础
再进行深入监控时，需要了解索引相关
索引基础、操作、数据结构

1、应用场景

索引的应用场景：与查字典一样。

查字典有三种方式

第一种：查数据从第一页找，如全表扫描

第二种：偏旁查找，即就是相当于通过普通索引列查找数据

第三种：通过拼音查找，相当于通过主键索引列查找类似

主键默认是索引，即就是主键索引

2、索引简介

创建在表上对数据库表中一列或多列的值进行排序的一种结构。

大部分是存在磁盘中，小部分是加载在内存中，加载到内存中的即是索引数的头节点。

3、索引优缺点

优点：
①加快查询速度（降低io使用率)
②降低了排序的时间（b数索引是排好序的结构）
③加快了表与表之间的连接速度

缺点：
①存储索引占用磁盘空间
②降低了插入和更新的速度（inset、update、delete都会产生索引维护，更新数据时，数据库不仅要保存数据，还需要保存索引文件，总之表数据更新，就需要重构索引，往磁盘写索引，增加磁盘io）

4、索引类型

索引类型主要分为聚集索引和非聚集索引
非聚集索引也叫辅助索引

4.1 聚集索引（主键索引）

4.1.1特点

①按主键排序

②叶子节点保存一行记录的所有内容。（不管记录有多少个字段所有字段内容都会保存）

③innodb中主键索引就是聚集索引

4.1.2优点

①如果根据主键查询，不需要回表，因为叶子节点保存了一行记录的所有内容

4.1.3缺点

①占用存储空间大（叶子节点保存一行记录的所有内容

②新增数据时，主键的值不规则，会造成频繁的页分裂，影响性能；一般主键的值都是连续增长，不会是跳跃式、随机值。

4.1.4包含

注：
唯一索引，不能重复、可为空。

聚集索引就是主键索引、主键索引是特殊的唯一索引。

聚集索引（主键索引）不能重复、不能为空
如果某个字段是primary key，那么该字段默认就是主键索引，也就是自动增加一个主键索引。

4.1.5说明

innodb里，只有主键索引是聚集索引。

4.2 非聚集索引（辅助索引）

①特点：在innodb里，非聚集索引叶子节点存的是索引列的值和主键值。

非聚集索引叶子节点和聚集索引叶子节点比较存的数据更少
相对而言，非聚集索引占用存储空间更小
如果索引不能覆盖要获取的数据，就需要到聚集索引查找（回表查询）

对于一个表来说，可以创建多个非聚集索引

4.2.1优缺点

优点：占用存储空间少、一个表可以创建多个非聚集索引
缺点：如果辅助索引没有索引覆盖，就需要到聚集索引查找（回表查询）

4.2.2非聚集索引包含：唯一索引、普通索引（单列索引）、组合索引（复合索引、联合索引）

4.2.2.1唯一索引：不能重复、但是可以为空

唯一索引比主键索引的条件更宽松一些
分为：手动索引、自动索引
手动索引：（手动在表上创建索引）
自动索引：create table user5(id int UNIQUE,name CHAR(20) UNIQUE);
若将某一字段设置成unique，系统会自动设置该字段的唯一索引

主键索引也是自动的，只要创建表时指定某一个字段的约束primary key
就会自动加一个主键索引

3.2.2.2普通索引（单列索引）

无任何限制条件，不要求是唯一，非空，可以创建在任何类型的字段上，一个表可以创建多个普通（单列）索引。

3.2.2.3组合索引（多列索引、复合索引、联合索引)

即关键字有多个

在多列上同时创建索引，使多列的组合值唯一，这样比创建单列索引要快
（id,username,job）
多列索引要遵循最左原则。
有多少个字段就创建了多少个索引，此处有三个字段，创建三个索引
第一个索引：id
第二个索引：id,username
第三个索引：id,username,job
where后面就要按顺序进行。
where若后面有多列，可以考虑加组合索引，避免对组合索引更新。

5、索引操作

创建索引

报错执行一些语句

创建后可以进行查看

5.1 查看索引

可以看到创建表的时候的索引

复制表中的sql语句到上面的语句里

5.2创建索引

直接在创建表上直接创建

现在有两个索引
第三种创建索引添加索引

此处有两个字段，先要删除一个索引

删除再查看一下，只有一个索引

第三种创建索引的方式

上面是创建普通索引的方式

下面添加唯一索引

建表时
额外创建一张表

第三种修改表的方式添加

5.3删除索引

两种方式

有普通索引也有唯一索引，怎么查看是哪种索引
可以通过

刚刚创建了索引
可以通过 show index from test来看索引type

索引type为b树
为啥要选择这种数据结构
为啥不选择其他数据结构

数据结构有很多：如数组、链表、二叉树、平衡二叉树、红黑树

6、索引的数据结构

6.1、在innodb中，索引的数据结构是b+树

左侧是聚集索引，右侧是辅助索引

左侧：

一个框就是叶子节点，上面页1是根节点，页2（中间节点，可以称为枝节点）
页4页5称为叶子节点，存的是一行完整数据，这条数据包含表里所有的字段

右侧：

叶子节点包含主键值和索引列的值。如果根据name，id直接在叶子节点获取数据，不需要在聚集索引找数据。

6.2、回表

如果要查找name，id，job，条件是根据name查找，首先会走辅助索引，查找到jack，不包含job，就会根据主键到聚集索引查找，就会找到左边id=1的jack是一行完整数据,像这种通过辅助索引没有找到所需数据，而去聚集索引找到数据，这种称为回表

6.3、b+树特点

①非叶子节点可以放更多的关键字，降低了树的高度，减少io次数

从主键开始到叶子节点的上一层（枝节点），是不存放记录数据，可以存放更多关键字，降低了树的高度，每加载一个磁盘块，获取到的关键字就更多，即就减少io次数

②根节点和支节点没有数据（也就是说：非叶子节点上是不存储数据的，仅存索引），只有叶子节点才存储数据

聚集索引：叶子节点存储索引列的值和数据，即就是完整的行记录
辅助索引：叶子节点存储索引列的值和主键值

③最末尾的一层叶子节点形成双向链表结构，天然有序