Mysql - 进阶

目录

存储引擎

MYSQL 体系结构

  • 连接层

最上层负责与 MySQL 客户端之间的通信，提供如连接处理，身份验证等功能。服务器也会为安全接入的每个客户端验证它所具有的操作权限

  • 服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口、并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。
所有跨存储引擎的功能也在这一层实现。如 存储过程、触发器、函数(日期、数学运算)等

  • 引擎层

存储引擎真正的负责了Mysql 中数据的存储和提取，服务器通过API 和 存储引擎进行通信。不同存储引擎具有不同的功能，这样我们可以根据自己的需要，
来选取合适的存储引擎

  • 存储层

主要是将数据存储在文件系统上，并完成与存储引擎的交互

https://zhuanlan.zhihu.com/p/608551131?utm_id=0

存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型

# 查看mysql 支持的存储引擎
show engines;


# 创建 my_isam 表，并指定使用的引擎为 MyISAM
create table my_isam( 
	id int primary key auto_increment,
	name varchar(10)
) ENGINE = MyISAM;

InnoDB 介绍

InnoDB 是一种兼顾高可性和高性能的通用存储引擎，在MySQL5.5 之后，InnoDB 是默认的MySQ存储引擎

特点：

  • DML操作遵循ACID模型，支持事务
  • 行级锁，提高并发访问性能
  • 支持外外键FOREIGN KEY 约束，保证数据的完整性和正确性

文件：

xxx.ibd： xxx代表的是表名。innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm,sdi)、数据和索引。

参数：innodb_file_per_table，代表是否是 每张表都有一个 独立的 表空间文件 还是共享一个表空间文件

默认是打开的，也就是每个表有一个独立的表空间文件，可以通过以下语句查看

show VARIABLES like '%innodb_file_per_table%'

InnoDB 逻辑存储结构：

MyISAM：

是MySQL 早期的默认存储引擎

特点：
1.不支持事务，不支持外键
2.支持表锁，不支持行锁
3.访问速度快

文件：

xxx.sdi： 存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引

InnoDB 与 MyISAM、Memory的区别：

索引

索引概述

索引(index) 是帮助MySQL 高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引.

演示：

select * from student where age = 10;

针对以上SQL:

如果age 没有索引，采用的是全表扫描：

如果age 有索引，查询会比较高效：

优缺点：

优势 劣势
1.提高数据检索的效率，减低数据库的IO成本 1.索引也是要占用空间的
2.通过索引列对数据进行排序，降低数据排序的成本，减低CPU的消耗 2.索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低

索引结构 - B-Tree

二叉树：

二叉树的缺点：
顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢

红黑树：

红黑树缺点：可以解决树的平衡问题，但是大数据量情况下，层级较深，检索速度慢

B-Tree(多路平衡查找树)：

以一颗最大度数(max-degree) 为5(5阶)的b-treee 为例(每个节点最多存储5个key，5个指针)：

注意：树的度数指的是一个节点的子节点个数。

可以使用下面数据可视化网站查看b-tree的具体过程：

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

我们以5阶为列,依次插入的数据汇总：0023 0234 0345 0899 1200 1234 1500 1000 123 245 12 1567 1800 1980 2000 1888 2456：

依次插入：0023 0234 0345 0899 1200 ，当插入1200 后，超过4个key 会发生一次裂变，将中间元素0345提升为根节点

继续一次插入：1234 1500 1000，当插入1000时发生一次裂变：

插入123：

插入245:

插入12 发生一次裂变：

插入1567：

插入1800：

插入1980 发生一次裂变：

插入2000：

插入1888:

插入2456 后发生两次裂变：

索引结构 - B+Tree

以一颗最大度数(max-degree) 为4(4阶) 的b+tree 为例:

相对于B-Tree 区别：
1.所有的数据都会出现在叶子节点
2.叶子节点形成一个单向链表

数据可视化案例，最大度数依然为5：

依次插入232 234 567 1000 888 1234 2345

插入 232 234 567 1000：

插入 888 发生一次分裂：

567 在向上分裂的同时，而且在叶子节点也存在，而且形成了一个单向链表

插入1234：

插入2345，发生一次分裂：

MySQL 索引结构对经典的B+Tree 进行了优化，在原B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree， 提高区间访问的性能

Hash 索引

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

Hash 索引特点：
1.Hash索引只能用于对等比较(=,in)，不支持范围查询(between、>、<)
2.无法利用索引完成排序操作
3.查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引

存储引擎支持：

在MySQL中，支持Hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

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

  • 相对于二叉树，层级更少，搜索效率高
  • 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，要同样保存大量数据，只能增加树的高度，导致性能降低
  • 相对hash 索引，B+Tree 支持范围匹配及排序操作

索引的分类

联合索引：
https://blog.csdn.net/jll126/article/details/119329138

show index from table_name:
https://c.biancheng.net/view/7364.html

在InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引的选取规则：

  1. 如果存在主键，主键索引就是
  2. 如果不存在主键，将使用都一个唯一(UNIQUE)索引作为聚集索引
  3. 如果表没有主键，或没有合适的唯一索引，则InnoDB 会自动生成一个rowid 作为隐藏的聚集索引

select * from where name = 'Arm';

查询流程：
1.先走二级索引拿到 name = ‘Arm’ 的行id
2.然后通过聚集索引获得根 行id 查询到 行数据

思考题：

InnoDB 主键索引的B+Tree 高度为多高？

假设：

一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB 的指针占用6个字节的空间，主键即使为bigint，占用字节数为8

假设高度为2：

n * 8 + (n + 1) * 6 = 16 * 1024 => 推算出 n = 1169

n: 一个节点可以存储的 key 的数量
n + 1: 指针的数量

因此高度为2可以存储的数据量为：（1169 + 1）* 16 = 18720k 的数据

假设高度为3：
可以存储 1170 * 1170 * 16 Kb 的数据

索引的语法

创建索引：

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

查看表的索引:

SHOW INDEX FROM table_name;

删除索引:

DROP INDEX index_name ON table_name;

案例：

# 为name字段姓名字段，该字段的值可能会重复，为该字段创建索引
create index idx_user_name on tb_user(name);

# phone手机号字段的值，是非空且唯一的，为该字段创建唯一索引
create unique index idx_user_phone on tb_user(phone)

# 为profession、age、status 创建联合索引
create index idx_pro_age_sts on tb_user(profession,age,status);

# 为email建立合适的索引来提升查询效率
create index idx_email on tb_user(email);

tb_user:

案例结果：

性能分析

查看数据库执行频次

SQL 执行频率：

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATA、DELETE、SELECT 的访问频次 

# Com后面是7个下划线
SHOW GLOABL STATUS LIKE 'Com_______';

慢查询

# 查询慢查询开关和日志存放位置
mysql> show variables like 'slow_query%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)

修改配置文件：/etc/my.cnf 加入以下配置

# 打开慢查询开关
slow_query_log=1 
# 慢查询为超过2s
slow_query_time=2

重启mysql:systemctl restart mysqld

慢查询日志：

执行计划

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

语法：

EXPLAIN SELCT 字段列表 FROM 表名 WHERE 条件；

案例1：内连接

# 左外
mysql> explain SELECT * from student s
    -> left join student_course sc on s.id = sc.studentid
    -> left join course c on c.id = sc.courseid;
+----+-------------+-------+------------+--------+---------------+--------------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key          | key_len | ref              | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+--------------+---------+------------------+------+----------+-------+
|  1 | SIMPLE      | s     | NULL       | ALL    | NULL          | NULL         | NULL    | NULL             |    4 |   100.00 | NULL  |
|  1 | SIMPLE      | sc    | NULL       | ref    | fk_studentid  | fk_studentid | 4       | test.s.id        |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY       | PRIMARY      | 4       | test.sc.courseid |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+--------------+---------+------------------+------+----------+-------+

# 内连接
mysql> explain select * from student s,student_course sc,course c where s.id = sc.studentid and sc.courseid = c.id;
+----+-------------+-------+------------+--------+--------------------------+--------------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys            | key          | key_len | ref              | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------------+--------------+---------+------------------+------+----------+-------+
|  1 | SIMPLE      | s     | NULL       | ALL    | PRIMARY                  | NULL         | NULL    | NULL             |    4 |   100.00 | NULL  |
|  1 | SIMPLE      | sc    | NULL       | ref    | fk_courseid,fk_studentid | fk_studentid | 4       | test.s.id        |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY                  | PRIMARY      | 4       | test.sc.courseid |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+--------------------------+--------------+---------+------------------+------+----------+-------+

案例2：子查询

# 查询报考了MySQL课程的学生信息， 要使用子查询完成
mysql> explain select * from student where id in (select studentid from student_course where courseid = (select id from course where name = 'MySQL'));
+----+--------------+----------------+------------+--------+--------------------------+-------------+---------+-----------------------+------+----------+-------------+
| id | select_type  | table          | partitions | type   | possible_keys            | key         | key_len | ref                   | rows | filtered | Extra       |
+----+--------------+----------------+------------+--------+--------------------------+-------------+---------+-----------------------+------+----------+-------------+
|  1 | PRIMARY      | <subquery2>    | NULL       | ALL    | NULL                     | NULL        | NULL    | NULL                  | NULL |   100.00 | NULL        |
|  1 | PRIMARY      | student        | NULL       | eq_ref | PRIMARY                  | PRIMARY     | 4       | <subquery2>.studentid |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | student_course | NULL       | ref    | fk_courseid,fk_studentid | fk_courseid | 4       | const                 |    2 |   100.00 | Using where |
|  3 | SUBQUERY     | course         | NULL       | ALL    | NULL                     | NULL        | NULL    | NULL                  |    4 |    25.00 | Using where |
+----+--------------+----------------+------------+--------+--------------------------+-------------+---------+-----------------------+------+----------+-------------+

EXPLAIN 执行计划个字段含义：

ID:

select 查询的序列号，表示查询中执行select 子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)

select_type:

表示SELECT 的类型，常见的取值有SIMPLE(简单表，既不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等

type:

表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all

# NULL: 不查询数据库表的情况下会出现
mysql> explain select 'A';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)


# const： 根据主键索引查询时会出现
mysql> explain select * from student where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


# const： 根据唯一索引查询时会出现
# 为 student 的 no 创建唯一索引
mysql> create unique index idx_stu_no on student(no);

mysql> explain select * from student where no = '2000100101';
+----+-------------+---------+------------+-------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | const | idx_stu_no    | idx_stu_no | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


# ref：根据常规索引查询时会出现, name 列为常规索引
mysql> explain select * from course where name = 'MySQL';
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | course | NULL       | ref  | idx_course_name | idx_course_name | 43      | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


# all： 全表扫描，未使用索引
# index: 使用了索引但是也会对索引进行遍扫描

possiable_key:
显示可能应用在这张表上的索引，一个或多个。

Key:
实际使用的索引，如果为NULL，则没有使用索引

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

rows:
MySQL 认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的

filtered：
表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好

posted @ 2023-10-05 16:56  chuangzhou  阅读(17)  评论(0)  编辑  收藏  举报