MySQL知识梳理

最新推荐文章于 2023-06-13 14:54:24 发布

conetwo

最新推荐文章于 2023-06-13 14:54:24 发布

阅读量193

点赞数

分类专栏： mysql/redis等

本文链接：https://blog.csdn.net/chenmeng729970897/article/details/81288068

版权

mysql/redis等专栏收录该内容

5 篇文章 0 订阅

订阅专栏

仅两种存储引擎的比较

InnoDB

InnoDB是MySQL 默认的事务型存储引擎，只有在需要InnoDB不支持的特性时，才考虑使用其它存储引擎。实现了四个标准的隔离级别，默认级别是可重复读.在可重复读隔离级别下，通过多版本并发控制和间隙锁防止幻读。

提供了具有提交、回滚和崩溃恢复能力的事务安全。支持真正的在线热备份,其它存储引擎不支持在线热备份。

MyISAM

MyISAM不支持事物、也不支持外键，其优势是房屋内速度快，对事物没有完整性要求或者以select、insert为主的应用基本上都可以使用这个引擎来创建表。

也不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入。

比较

事物：InnoDB是事务型的，可以用Commit和Rollback语句
并发：MyISAM只支持表级锁，而InnoDB还支持行级锁
外键： InnoDB支持外键
备份：InnoDB支持在线热备份
崩溃恢复：MyISAM崩溃后发生损坏的概率比InnoDB高，而且恢复的速度也慢
其他特性：MyISAM支持压缩表和空间数据索引，是对比MyISAM的存储引擎，InnoDB的写效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

主要数据类型

整型

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT 分别使用 8, 16, 24, 32, 64位存储空间，一般情况下越小的列越好。INT(11)中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

浮点数

FLOAT和DOUBLE为浮点类型，DECIMAL为高精度小数类型。

字符串

主要有CHAR和VA RCHAR两种类型，一种是定长的，一种是变长的。VARCHAR会保留字符串末尾的空格，而CHAR会删除。

时间和日期

MySQL提供了两种相似的日期时间类型：DATATIME 和 TIMESTAMP。

索引

对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效。对于中到大型的表，索引就非常有效。但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配。

索引是在存储引擎层实现的，不同存储引擎具有不同的索引类型和实现。

B+ 树索引

B+Tree 索引是大多数MySQL存储引擎的默认索引类型。
因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组。
可以指定多个列作为索引列，多个索引列共同组成键。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。

主索引的叶子节点data域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的data域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

哈希索引

InnoDB 引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在B+Tree索引之上再创建一个哈希索引，这样就让B+Tree索引具有哈希索引的一些优点，比如快速的哈希查找。
哈希索引能以O(1)时间进行查找，但是失去了有序性，它具有以下限制：
1. 无法用于排序与分组；
2. 只支持精确查找，无法用于部分查找和范围查找；

全文索引

MyISAM存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST, 而不是普通的WHERE。

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

索引的优点

大大减少了服务器需要扫描的数据行数
帮助服务器避免进行排序和创建临时表（B+Tree 索引是有序的，可以用来做ORDER BY和GROUP BY操作）

索引优化

独立的列。在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。
多列索引。在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

前缀索引。对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。
覆盖索引。索引包含所有需要查询的字段的值。

查询性能优化

使用Explain进行分析

Explain用来分析SELECT查询语句,开发人员可以通过分析Explain结果来优化查询语句。
比较重要的字段有：

select_type : 查询类型，有简单查询、联合查询、子查询等
key : 使用的索引
rows : 扫描的行数

优化数据访问

减少请求的数据量。返回必要的列，最好不要使用 SELECT * 语句。
只返回必要的行。使用 WHERE 语句进行查询过滤，有时候也需要使用 LIMIT 语句来限制返回的数据。
缓存重复查询的数据。使用缓存可以避免在数据库中进行查询，特别要查询的数据经常被重复查询，缓存可以带来的查询性能提升将会是非常明显的。
减少服务器端扫描的行数。最有效的方式是使用索引来覆盖查询。

重构查询方式

切分大查询。一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
分解大连接查询。将一个大连接查询（JOIN）分解成对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。

切分

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。
当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。
垂直切分。垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。