MySQL进阶知识

admin管理员组

文章数量:1579391

1 存储引擎

1.1 MySQL体系结构

1.2 存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，同一个库的多个表可以采用不同的存储引擎，所以存储引擎也经常称为表类型。创建表时可以指定存储引擎，如果不指定默认存储引擎是InnoDB。

查询建表语句：

show create table tb_brand;

CREATE TABLE `tb_brand` (
  `id` int NOT NULL AUTO_INCREMENT,
  `brand_name` varchar(20) DEFAULT NULL,
  `company_name` varchar(20) DEFAULT NULL,
  `ordered` int DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `status` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
//主键是自增长的，下一条记录ID是8

在创建表时指定存储引擎：

ENGINE=XXX，例如ENGINE=InnoDB

查看当前数据库支持的存储引擎：

show engines;

1.2.1 InnoDB

innodb_file_per_table开关，控制着每张innodb表都创建一个表空间文件，还是所有的innodb表共用一个表空间文件。查看系统变量:

1.2.2 InnoDB的逻辑存储结构

1. Row（一行）中包含，最后一次操作事务的ID（Trx id）、指针、各个字段

2. page是磁盘操作的最小单元。一个Extent大小固定是1M，一个page大小固定是16K，一个Extent包含64个page

1.2.3 MyISAM和Memory

1. Memory数据存放到内存当中，访问速度很快

2. Memory在磁盘上只有.sdi文件

3. Memory支持hash索引

绝大多数场景都是用InnodDB引擎。MyISAM可使用mangodb替代，Memory可使用redis替代。

2 索引

2.1 索引概述

没有索引的话，查询数据时需要全表扫描，查询效率极低；

有索引的话，需要建立一种数据结构维护数据和数据所在的内存地址关系，查询效率可大大提高；

1. 磁盘很便宜，索引占点磁盘空间也没事

2. 实际应用上，查询比例远远多余插入、更新、删除比例，因此降低更新表的速度这个劣势可以不考虑。

2.2 索引结构

2.2.1 二叉树和红黑树

二叉树缺点：

1. 顺序插入时，会形成一个链表，查询性能大大降低，如图2

2. 每个节点只有两个叶子节点，在数据量很大时，二叉树的层级会很深，检索速度很慢

二叉树的1缺点，可通过红黑树解决，如图3；但是红黑树也是二叉树，无法解决缺点2

2.2.2 Btree

2.2.3 B+tree索引

想较于B树，B+树有如下特点：

1、所有的元素都出现在叶子节点，叶子节点用来存放数据；非叶子节点只起到索引的作用

2、叶子节点形成了一个单向链表

2.2.4 Hash索引

2.2.5 InnoDB为什么选择B+树作为索引

二叉树缺点：

1、顺序插入时，会形成一个链表，查询性能大大降低，如图2

2、每个节点只有两个叶子节点，在数据量很大时，二叉树的层级会很深，检索速度很慢

红黑树缺点：

1、二叉树的1缺点，可通过红黑树解决；但是红黑树也是二叉树，无法解决缺点2

B树缺点：

1、B树叶子节点或者非叶子节点都存储数据和指针，B+树只有叶子节点存储数据，非叶子节点存储指针。

2、每页大小只有16K，相较于B树，B+树一页当中能存储更多的Key和指针，相同数据量的话B+树层级会少一些，检索速度也会更快。

Hash索引缺点

1、Hash索引只能等值匹配，=、in；不支持范围匹配，beteen and、 >、<。B+树支持范围匹配。

2、Hash所以不支持排序操作。B+树支持排序操作；

2.3 索引分类

2.3.1 聚集索引和二级索引

在一个表中，聚集索引必须得有，并且只能有一个；聚集索引叶子节点下面挂的这一行的行数据，比如5下面挂的就是id是5的这一行数据。

如果针对name这一列建立索引，建立的索引是二级索引。二级索引叶子节点挂的是这一行数据的id值。

2.3.1 回表查询

举例select * from user where name=‘Arm’，MySQL的查询流程如下：

1、从对name列建立的二级索引中查找，Lee->Geek->Arm，找到Arm节点；

2、取出Arm节点下挂的id值10，返回聚集索引继续查找；15,30->10,20->10，找到id值为10的节点；

3、取出10这个节点下面挂的行数据返回

先走二级索引找到对应的主键值，再到聚集索引中根据主键值拿到行数据

答：1高于2。1扫描一遍聚集索引即可；2需要先扫描name字段的二级索引，然后再回表查询聚集索引，两遍扫描。

2.4 索引语法

2.4.1 创建索引

UNIQUE：唯一索引

FULLTEXT：全文索引

什么都不加：常规索引

table_name：表名

index_col_name:字段名，一个索引可以关联多个字段，用,隔开。如果一个索引只关联一个字段，称为单列索引，关联多个字段的索引叫作联合索引。

为哪一张表的哪一/多个字段创建索引。

2.4.2 查看索引

查看指定表中的所有的索引。

2.4.3 删除索引

删除哪一张表中的哪一个索引。

2.4.4 索引操作案例

1、常规索引

create index name_index on tb_user(name);

2、唯一索引

create unique index phone_index on tb_user(phone);

3 create index union_index on tb_user(profession,age,status)

4 唯一索引

create unique index email_index on tb_user(email);

2.5 SQL性能分析

2.5.1 查看SQL执行频率

show global status like 'Com_______';

本文标签： 进阶知识mysql