数据库学习：MySQL总结

最新推荐文章于 2024-08-30 20:44:52 发布

Programmie

最新推荐文章于 2024-08-30 20:44:52 发布

阅读量325

点赞数

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

本文链接：https://blog.csdn.net/qq_41989109/article/details/107590140

版权

数据库专栏收录该内容

5 篇文章

订阅专栏

MySQL学习总结

B+ Tree 原理

B Tree — Balance Tree。B树是一棵平衡树

B+ Tree。这个"+"，加的是叶子结点之间的指针。通过B树去划分区间，在叶子结点可以通过指针进行范围遍历。
结点的key按升序进行排列

查找：先从根节点开始进行二分查找，再在指针的位置上递归进行查找。直到找出key所对应的data
与红黑树对比：
- （数据结构）B+树的高度更低：有效的减少了指针向下递归的层树。红黑树出度为2，而B+树的出度会很多
- （硬件优化）磁盘读写快：磁盘寻道的次数与树高成正比，在同一个磁盘块上访问只需要很短的磁盘旋转时间
  - 可以借助磁盘的预读特性，将相邻的节点也预先载入（通过底层的连接指针）

MySQL索引

B+ 树索引：
- 不需要全表扫描，只需要进行树的搜索
- 基于B+Tree的有序特性，除了查找以外，还可以进行排序和分组（通过范围查找）
- InnoDB索引分类：
  - 主索引（聚簇索引）：叶子结的data域包含了完整的数据记录。即存储的行数据
    - 一个表只能有一个聚簇索引。因为不能把数据存储在两个不同的地方（区别MyISAM）
    - 如果没有定义主键，InnoDB引擎会隐式的定义一个主键来作为聚簇索引
      - 聚簇索引性能最好而且具有唯一性。如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。
      - 顺序添加可以有效利用每个页的存储空间，对页进行顺序填满。不然会导致页出现空位，占用额外的存储空间
      - 数据页通过双向链表进行连接
    - 缺点：聚簇索引新值的插入会比较慢 – 树的平衡操作较为复杂。
      - 插入速度依赖于插入顺序 – 按主键顺序插入最快，否则将会出现页分裂（自增ID）
    - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VNyn1aba-1595730754095)(https://blog.codinglabs.org/uploads/pictures/theory-of-mysql-index/10.png)]
  - 辅助索引（非聚簇索引）：叶子结点的data域包含主索引的主键值。需要先查找到主键值，然后再在主索引中进行查找
    - 索引结构的叶子节点指向了数据的对应行。
    - 辅助索引只保存主键值，占用空间小。当表需要建立多级索引的时候，比聚簇索引好（建立聚簇索引相当于又建立一个新表）

MyISAM引擎采用的是非聚簇索引，两棵B+树没有不同，结点的结构完全一致。

表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。
主索引要求key唯一，辅助索引的key可以重复
由于索引树是独立的，通过辅助键检索无需访问主键的索引树
由于MyISAM采用非聚簇索引，数据的物理地址是离散的，存储的是数据的指针，导致硬盘IO需要花费大量的寻道时间进行查找。而InnoDB的只需一次IO，因为其数据都是顺序分布在磁盘上的。
以下是MyISAM的索引实现

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O6uNxBqu-1595730754100)(https://blog.codinglabs.org/uploads/pictures/theory-of-mysql-index/8.png)]

不建议大量使用索引的原因：

索引会消耗存储空间，同时会加重CRUD的负担，MYSQL在运行时要消耗资源维护索引
- 当记录较少（<=2000)，不需要建立索引，可以直接做全表扫描。超过2000条需要酌情考虑
- 选择需要经常查询的列建立索引

百万索引测试：

CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '',
`email` varchar(50) NOT NULL,
`phone` varchar(20) DEFAULT '',
`gender` tinyint(4) unsigned DEFAULT '0',
`password` varchar(100) NOT NULL DEFAULT '',
`age` tinyint(4) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 插入100万数据.
DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
SET i=i+1;
END WHILE;
RETURN i;
END;

SELECT mock_data() -- 执行此函数 生成一百万条数据

explain select * from app_user where name='用户9999'

不加索引之前（我这里是因为之前不小心创建了两次函数）：
无索引的查找会从头到尾扫描一遍，效果很差

时间：2 rows retrieved starting from 1 in 1 s 119 ms (execution: 1 s 93 ms, fetching: 26 ms)

大概花了1s左右。

建立索引：

create index id_app_user_name on app_user(`name`)
 -- 格式 create index index_name on  table_name(`column_name`)

再次执行查询：

时间：2 rows retrieved starting from 1 in 42 ms (execution: 5 ms, fetching: 37 ms)

只查找了两行。正常应该是只有一行，我之前瞎搞了一下。

总结分析：

回到上文。建立索引，相当于是在内存再次建立了一个数据结构，为这一列单独建立了一棵B+树，从而减少了在聚簇索引中遍历查找的行数。先通过name建立的辅助索引B+树中去找到主键，再根据主键去寻找聚簇索引中的具体值。

哈希索引

可以将查找的时间复杂度降低到o(1)，但是不利于顺序查找。

不能进行排序与分组。只支持精确查找，无法用于模糊搜索和范围查找

InnoDB支持**“自适应哈希索引”**，每当某个索引值被频繁引用，会在B+树上创建一个哈希索引

全文索引

MyISAM支持全文索引。MYSQL在5.6过后也支持全文索引

select * from app_user where MATCH(name) AGAINST ('用户9999')

索引顺序性要求

让选择性最强的索引列放在前面：不重复的索引值和记录总数的比值越大，选择性越强。

前缀索引

对于BLOB，TEXT，VARCHAR等，必须使用前缀索引，只索引开始的部分字符。

覆盖索引

可以理解为叶子结点包含了该索引列的值。InnoDB可以通过直接访问索引来获取数据，而不需要再在聚集索引中查找

查询性能优化

使用Explain找到被拖慢的原因
优化数据访问
- 减少数据量：
  - 尽量不要使用 select *
  - 使用limit来截断数据
  - 合理利用缓存，避免在数据库中进行查询
- 减少服务端扫描行数：通过索引来覆盖查询
重构查询方式：
- 切分大查询：一次大查询一次性执行会锁住很多数据，占满整个事务日志，拖慢性能
- ```
 -- 切分成多次查询
rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0
```
- 将联表查询在应用程序中转换成单表查询：
  - 有效利用缓存，如果联表中有一个表数据被修改，那么缓存不可用
  - 减少锁竞争
  - 应用层可以实现更灵活的数据拆分