MySQL进阶——索引【核心】

1索引概述

2索引结构

2.1 B-Tree（多路平衡查找树）

2.2 B+Tree

2.3 hash

3索引分类

3.1MySQL中分4类

3.2 InnoDB存储引擎分两类（SQL优化中重要）

4索引语法

4.1创建和查看索引

4.2删除索引

5 SQL性能分析

5.1 查看执行频次

5.2慢查询日志

5.3profile详情（慢日志不行可以用这个）

5.4 explain（重要

Linux，MySQL，finalshell都安装好啦！开干！

（1）先启动虚拟机，然后打开远程连接finalshell

（2）输入mysql -u root -p，连接到Linux中的MySQL数据库。就可以在这上面操作Linux-MySQL啦（我是这么理解的）

1索引概述

索引 (index)是帮助MysQL高效获取数据的数据结构(有序)。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

（1）无索引情况：就需要从第一行开始扫描，一直扫描到最后一行，我们称之为全表扫描，性能很低。

（2）有索引情况：如果我们针对于这张表建立了索引，假设索引结构就是二叉树（存储结构），那么也就意味着，会对age这个字段建立一个二叉树的索引结构。此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。

优缺点：

2索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持情况：

2.1 B-Tree（多路平衡查找树）

普通的二叉树（理想情况）：

普通的二叉树（最糟糕情况——顺序插入）：

如果主键是顺序插入的，则会形成一个单向链表，结构如下。

通过红色树来解决上面这个平衡问题，因为红黑树是自平衡的二叉树：

那这样即使是顺序插入数据，最终形成的数据结构也是一颗平衡的二叉树,结构如下。但是红黑树也有缺点：大数据量情况下，层级较深，检索速度慢。

所以，在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+Tree，

数据结构可视化网(www.cs.usfca.edu)

https://www.cs.usfca.edu/~galles/visualization/BTree.html

随着插入，中间节点往上移。

2.2 B+Tree

B+Tree是B-Tree的变种，B+Tree中所有的节点都会出现在叶子节点。

最终我们看到，B+Tree 与 B-Tree相比，B+Tree主要有以下三点区别：

（1）所有的数据都会出现在叶子节点。

（2）叶子节点形成一个单向链表。

（3）非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

2.3 hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

补充数据结构只是，当遇到hash冲突的解决方法：

1、开放定址法：我们在遇到哈希冲突时，去寻找一个新的空闲的哈希地址。

（1）线性探测法

当我们的所需要存放值的位置被占了，我们就往后面一直加1并对m取模直到存在一个空余的地址供我们存放值，取模是为了保证找到的位置在0~m-1的有效空间之中。

（2）平方探测法（二次探测）

当我们的所需要存放值的位置被占了，会前后寻找而不是单独方向的寻找。

2、再哈希法：同时构造多个不同的哈希函数，等发生哈希冲突时就使用第二个、第三个……等其他的哈希函数计算地址，直到不发生冲突为止。虽然不易发生聚集，但是增加了计算时间。

3、链地址法：将所有哈希地址相同的记录都链接在同一链表中。

4、建立公共溢出区：将哈希表分为基本表和溢出表，将发生冲突的都存放在溢出表中。

Hash树特点：

（1） Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）

（2）无法利用索引完成排序操作

（3）查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

思考题：为什么InnoDB存储引擎选择使用B+tree索引结构?

（1）相对于二叉树，层级更少，搜索效率高;

（2）对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低;

（3）相对Hash索引，B+tree支持范围匹配及排序操作;

3索引分类

3.1MySQL中分4类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

3.2 InnoDB存储引擎分两类（SQL优化中重要）

而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：聚集索引和二级索引/辅助索引/非聚集索引。

聚集索引选取规则:

（1）如果存在主键，主键索引就是聚集索引。

（2）如果不存在主键，将使用第一个唯一(UNIQUE）索引作为聚集索引。

（3）如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

除开聚集索引，其他都是二级索引。索引的叶子节点下挂的是该字段值对应的主键值。

lnnoDB主键索引的B+tree高度为多高呢?

假设：一行数据大小为1k,，一页中可以存储16行这样的数据。InnoDB的指针占用6

个字节的空间，主键即使为bigint，占用字节数为8。

（1）高度为2:

n * 8+(n +1)*6=16*1024，算出n约为1170（每一行的节点个数）

1171*16=18736

（2）高度为3:

1171 * 1771 * 16= 21939856

（发现InnoDB存储就算存储2000多万个记录数，也才3层，检索效率很高）

4索引语法

4.1创建和查看索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, . .. ) ;

SHOW INDEX FROM table_name ;

案例：按照下列的需求，完成索引的创建

1. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

2. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

3.为profession、age、status创建联合索引。

4.为email建立合适的索引来提升查询效率。

演示：

（1）新建itcast数据库，并在其中准备两个表格：

（2）查询tb_user表的索引情况，

在分号之前加上\G，可以转置展示。

（3）完成：1. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

输入创建索引语句：create index idx_user_name on tb_user(name);

（因为InnoDB引擎默认是BTree）

（4）完成：2. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

是非空，且唯一的就要加上关键字UNIQUE。

输入创建索引语句：create unique index idx_user_phone on tb_user(phone);

但是报错了：

猜想是原本的数据库中已经有重复不唯一的两个电话phone了，所以不能满足unique条件。

利用DELETE FROM表名[WHERE条件]，删除重复的数据：

现在输入：CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

就能成功创建索引：

（5）完成：3.为profession、age、status创建联合索引。

输入：create index idx_user_pro_age_sta on tb_user(profession,age,status);

注意，在联合索引中，字段顺序有讲究的，后面说

（6）完成：4.为email建立合适的索引来提升查询效率。

4.2删除索引

DROP INDEX index_name ON table_name ;

如：删除emai的索引

输入：drop index idx_user_email on tb_user;

5 SQL性能分析

5.1 查看执行频次

SQL优化的主要是查询select语句。MySQL 客户端连接成功后，通过 show [session | global] status 命令可以提供服务器状态信息。

通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：-- session 是查看当前会话;-- global是查询全局数据;

SHOW GLOBAL STATUS LIKE 'Com_______';

输入：show global status like 'Com_______';（7个_）

5.2慢查询日志

借助慢查询日志，来定位SQL执行效率比较低的语句，从而对这类上SQL语句进行优化。

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL语句的日志。

MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。

输入：show variables like 'slow_query_log';发现默认没有开启。

但是我Windows中的mysql是开启状态呢。

开启慢查询日志，利用Linux中的vi编辑器！

在Linux路径而不是mysql路径，输入：vi /etc/my.cnf;

然后按i进入输入状态，按esc退出输入，尾部:x保存并退出，vi操作不是很熟练，后面可能要补一下Linux方面的知识。

输入：systemctl restart mysqld 重新启动mysql，但是我这报错是为啥？

芭比Q了，路径不一样（安装方式不一样）。

课的：cd /var/lib/mysql;

我的：cd /usr/local/mysql/data;

5.3profile详情（慢日志不行可以用这个）

输入：select @@have_profiling;查看。。。

开启之后，查看目前的指令执行耗时情况：

执行一系列SQL之后再次查看当前执行SQL语句耗时情况：show profiles;

可以看到根据主键id查询，比二级索引name查询要快十倍！

查看指定语句的耗费在哪些操作中：show profile for query 10;

还可以查询CPU耗费情况：show profile cpu for query 10;

5.4 explain（重要）

通过时间来判断SQL语句的性能比较粗略，用explain可以查看SQL执行计划，一般通过explain来判断SQL性能。

在select前加上explain或desc就可以查询这个SQL语句的执行计划。

Explain 执行计划中各个字段的含义：

创建多表：

create table student(

    id int auto_increment primary key comment '主键ID',

    name varchar(10) comment '姓名',

    no varchar(10) comment '学号'

) comment '学生表';

insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊', '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');



create table course(

    id int auto_increment primary key comment '主键ID',

    name varchar(10) comment '课程名称'

) comment '课程表';

insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');



create table student_course(

    id int auto_increment comment '主键' primary key,

    studentid int not null comment '学生ID',

    courseid  int not null comment '课程ID',

    constraint fk_courseid foreign key (courseid) references course (id),

    constraint fk_studentid foreign key (studentid) references student (id)

)comment '学生课程中间表';



insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);