mysql高级部分内容学习笔记

mysql高级部分内容学习笔记

1索引

mysql官方对索引的定义为：索引(index) 是帮助mysql搞笑获取数据的数据结构(有序),在数据之外，数据库系统还维护这满足特定查找算法的数据结构，这些数据结构 就是索引。

  左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

索引的优势和劣势

优势

1 类似书籍的目录，提高数据检索的效率，降低数据库的IO成本

2 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

1 索引其实也是一张表，该表保存了主键和索引字段，并指向了实体的记录，所以索引也是要占用空间的。

2索引虽然提高了查询的效率，但是对更新表的速度降低了，如果要对mysql表进行insert update delete mysql 不仅要保存数据，还要保存索引文件每次更新添加了的索引字段。

索引的结构：

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

• BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

• 树中每个节点最多包含m个孩子。
• 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。 向上取整
• 若根节点不是叶子节点，则至少有两个孩子。
• 所有的叶子节点都在同一层。
• 每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1

这个n指的就是key的数量 也就是ACGN这些值的数量。 

以5叉BTree为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。当n>4时，中间节点分裂到父节点，两边节点分裂。

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。

演变过程如下：

1). 插入前4个字母 C N G A

2). 插入H，n>4，中间元素G字母向上分裂到新的节点 

3). 插入E，K，Q不需要分裂

 4). 插入M，中间元素M字母向上分裂到父节点G

5). 插入F，W，L，T不需要分裂

 6). 插入Z，中间元素T向上分裂到父节点中

 7). 插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂

8). 最后插入S，NPQR节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂

到此，该BTREE树就已经构建完成了， BTREE树 和 二叉树 相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

B+TREE 结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：

1). n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。

2). B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。

3). 所有的非叶子节点都可以看作是key的索引部分。 

 由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

索引的分类

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

2） 唯一索引 ：索引列的值必须唯一，但允许有空值

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

4） 主键索引： 即每一个主键就是一个索引

准备环境

create database demo_01 default charset=utf8mb4;

use demo_01;

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);

insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

# 创建索引
#CREATE  INDEX  INDEXName  ON city(city_name);#
#执行计划
#explain/desc
#EXPLAIN	 SELECT *  from city;  执行计划


#SELECT  STATUS  FROM tb_item where  `status`=1; # 查询时间用了0.264 秒 1百万条数据

#SELECT  *  FROM   tb_item WHERE sellerid='123'; # 查询时间用了1.090 秒 1百万条数据   没有走索引
#select  * from tb_item

#SELECT  *  FROM   tb_item  where   createtime='2021-08-19 16:21:33'  # 不走索引


#创建了复合索引
CREATE  INDEX  INDEX_ZUHE_tb_item  ON tb_item(status,sellerid,createtime);

SHOW INDEX FROM  tb_item; # 查看索引

DROP  INDEX  INDEX_ZUHE_tb_item  ON tb_item; # 删除索引


select  * from tb_item   # 全部查询出来用了0.79s

1). alter  table  tb_name  add  primary  key(column_list); 

	该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
	
2). alter  table  tb_name  add  unique index_name(column_list);
	
	这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	
	例如：alter table city add unique index_cityname(city_name);
	
3). alter  table  tb_name  add  index index_name(column_list);

	添加普通索引， 索引值可以出现多次。
	
	例如：alter table city add index index_xxxx(xxxx);
	
4). alter  table  tb_name  add  fulltext  index_name(column_list);
	
	该语句指定了索引为FULLTEXT， 用于全文索引
	创建复合索引:

	CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;

2执行计划

可以通过 EXPLAIN或者获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

下面我们详细说明： mysql环境准备

CREATE TABLE `t_role` (
  `id` varchar(32) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `user_role` (
  `id` int(11) NOT NULL auto_increment ,
  `user_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ur_user_id` (`user_id`),
  KEY `fk_ur_role_id` (`role_id`),
  CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');



INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');


INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

explain之id

id控制的是表结构的执行顺序

id字段是select 查询的序列号，是一组数字，表示的是查询中执行select子句或者操作表的顺序， id值越大，优先级越高。id情况有三种，

id都是一样的

id相同，表示加载表是从上到下的 

id都是不一样的

id值越大，优先级越高，越先被执行，这里先加载id为3的表 

id有一样的也有不一样的。

id 有相同的也有不同的 id 相同的可以认为是一组，从上到下执行，id大的优先执行 

explain之select_type

select 选择 type 类型 表示select的类型，常见的取值，如下表所见

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION **单表操作 **
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识 除了子查询的，最外层查询
SUBQUERY	在SELECT 或 WHERE 列表中包含了子查询 子查询
DERIVED	在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED
UNION RESULT	从UNION表获取结果的SELECT

从上到下效率是越来越低的

explain之table

展示这一行的数据是关于哪一张表的

explain 之 type

type显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。const于将 "主键" 或 "唯一" 索引的所有部分与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。也就是说范围查询
index	index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行

效率也是从高到低顺序的 一般我们只是需要将type类型提升到ref 或者是range 类型 即可。

explain 之于key 相关的字段

possible_keys:显示可能应用在这张表的索引，一个或者多个

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

key_len 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际的长度，在不损失精确性的前提下，长度越短越好
长度越短，效率是越高的。

explain 之rows

扫描行的数量

explain 之 extra（额外的）

其他的额外的执行计划信息，在该列展示 。

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低
using index	表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。

如果出现了前面两个，就需要优化了。 怎么优化呢？在排序的字段上，建立index 索引即可。

3：show profile分析SQL

Mysql 从5.0.37版本就开始增加对Show profiles和 show profile语句的支持，show profile能够在做sql 优化时候帮助我们了解时间都耗费在哪里了。 通过 have_profiling参数，能够看到当前MySQL是否支持profil： 

这里可以看到，我们的mysql 版本是可以支持show profile的

然后需要查看一下profiling是开启的还是关闭的

select @@profiling;

 0代表没有开启

set profiling=1; //开启profiling 开关；

 现在我们开启了profiling 执行下面这些sql语句

show databases;

use hxw;

show tables;

select * from tb_item where id < 5;

select count(*) from tb_item;

通过show profiles 命令 查看sql语句的执行耗时。

show profiles;   查看sql语句的执行耗时。

 解释：Query_ID 代表当前查询的id Duration 代表的是 耗时 Query 代表的是执行的sql

之后呢，我们可以看一下耗时的sql 是在那个阶段耗费时间的

通过show  profile for  query  query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

 解释：status 经历阶段 duration 花费多长时间

TIP ：
	Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。