MySQL笔记

yfdddong

已于 2023-10-09 14:58:40 修改

阅读量38

点赞数

分类专栏：数据库文章标签： mysql 笔记数据库

于 2023-10-09 14:26:14 首次发布

本文链接：https://blog.csdn.net/yfdddong/article/details/133692652

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MySQL笔记

MySQL

tips: MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

MySQL日志

错误日志、查询日志、慢查询日志、事务日志、二进制日志。

binlog
redo log
undo log

redo log 崩溃恢复

Server层

连接器、查询缓存、分析器、优化器、执行器(校验用户有没有权限，调用引擎的接口，返回接口的执行结果)

存储引擎：存储数据，提高读写接口

MySQL支持InnoDB MyISAM

一条 SQL 语句是如何执行的呢？

查询
检查语句权限，没有则返回错误信息
查询缓存，缓存中有结果则返回结果
分析器词法分析，语法分析
优化器根据自己的优化算法选择方案
进行权限校验，没有权限则返回错误信息
调用数据库引擎接口
更新
执行更新时需要记录日志
MySQL自带的日志模块：binlog
InnoDB:

update tb_student A set A.age='19' where A.name=' 张三 ';

1、查询到张三这一条数据
2、修改数据，调用引擎接口写入这一行数据
3、InnoDB把数据保存在内存，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
4、执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
5、更新完成

MySQL索引

排好序的用于快速查询和检索的数据结果。
优点：

加快查询速度
创建唯一索引能够保证数据库表中每一行数据的为一性
缺点:
存储索引耗费空间
创建索引和维护索引需要耗费许多时间。
当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。

索引底层数据结构

Hash表

InnoDB存储引擎存在自适应哈希索引。自适应的哈希索引：每个哈希桶是一个小型B+树，该B+树可以存储多个键值对。减少哈希冲突链长度，提高哈希效率。缺点：不支持顺序和范围查询。每次IO只能取1个。

二叉查找树BST

二叉树的平衡：每个节点的左右子树深度差不超过1。最坏情况：二叉查找树退化成线性链表。

AVL树

问题：频繁地旋转保持平衡。每个树节点仅仅存储一个数据。
tips:磁盘 IO 是一项耗时的操作，在设计数据库索引时，我们需要优先考虑如何最大限度地减少磁盘 IO 操作的次数。

红黑树

1、根节点黑色
2、红节点孩子黑色
4、叶子节点null黑色
5、根节点到叶子节点路径上的黑色节点数量相同

B树&B+树

B树：多路平衡查找树

B树& B+树两者有何异同呢？
1、B树节点存放数据和key;B+树叶子节点存放数据和key,其他节点存放key
2、B+树的叶子节点右一条引用链指向与它相邻的叶子节点
3、B+树检索效率很稳定
4、中序遍历过程：B树，查找到下限 -> 中序遍历 ->直到查找到上限；B+树，对链表遍历。

B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

索引类型

数据结构维度：

Btree
哈希索引
RTree:仅仅支持geometry数据类型，优势：范围查找
全文索引：对文本的内容进行分词，搜索。目前，仅仅char,varche,text列上可以建立。效率低。
存储层次角度：
聚簇索引（聚集索引）：主键索引
非聚簇索引（非聚集索引）：二级索引

应用维度

主键索引 : 数据库表主键列使用的索引就是主键索引。InnoDB中，没有显式地指定主键时，自动检查是否有唯一索引且不允许存在null值的字段。有则选择该字段为默认主键，否则自动创建一个6Byte的自增主键。
唯一索引
普通索引
覆盖索引：一个索引包含所有需要查询的字段的值
联合索引
全文索引

索引知识

二级索引:

唯一索引：属性列不能有重复数据，允许数据为NULL,未来数据的唯一性创建。
普通索引：
前缀索引：适用于字符串类型数据，相比普通索引，只取文本的前几个字符创建索引。
全文索引

聚集索引与非聚集索引

聚集索引：
优点：速度、排序查找和范围查找
缺点：依赖于有序数据，如果索引的数据不是有序的，那么就需要在插入时排序，对于难以比较的数据，插入或查找的速度比较慢。更新代价大。
非聚集索引：
tips:因为二级索引的叶子节点就存放的是主键，不一定存放的指针。
缺点：依赖有序数据。回表。

tips:

非聚簇索引不一定回表查询。 – 查询主键

正确使用索引

适合创建索引的字段

不为NULL,频繁查询，作为查询条件，频繁连接，频繁需要排序。

频繁更新的字段应该慎重建立索引
单表索引不超过5个！ – 增加索引降低插入和更新效率，索引多时，会增加MySQL优化器生成执行计划的时间。
尽量建立联合索引
避免冗余索引，扩展已有索引，而不是创建新索引。
字符串类型使用前缀索引
索引失效是慢查询的主要原因之一。导致索引时效的原因：’%'开头的LIKE查询；在索引上进行计算、函数、类型转换等操作。
删除长期未使用的索引

`如何分析语句是否走索引查询`

EXPLAIN:分析SQL执行计划。执行计划：一条语句经过MySQL查询优化器优化后，具体的执行方式。
EXPLAIN输出格式

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)