2.22MySQL基础学习

最新推荐文章于 2024-09-13 17:29:38 发布

铃兰花的土壤

最新推荐文章于 2024-09-13 17:29:38 发布

阅读量739

点赞数 22

文章标签：学习

本文链接：https://blog.csdn.net/suzuran7/article/details/136261994

版权

存储引擎

什么是存储引擎

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

MySql中有哪些存储引擎？

MyISAM：这种引擎是mysql最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种，不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能。
MyISAM Merge引擎：这种类型是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。
InnoDB：InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能，也是目前MySQL默认的存储引擎。
Memory(heap)：这种类型的数据表只存在于内存中。它使用散列索引，所以数据的存取速度非常快。因为是存在于内存中，所以这种类型常应用于临时表中。
archive：这种类型只支持select 和 insert语句，而且不支持索引。常应用于日志记录和聚合分析方面。

存储引擎的对比

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表

索引

B-Tree索引（MySQL使用B+Tree）

B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

B+Tree索引

是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

慢查询日志介绍

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为 10，意思是运行10秒以上的SQL语句。

具体操作

使用set global slow_query_log=1开启慢查询日志指对当前数据库生效，如果MySQL重启后则会失效。

如果需要永久生效，就必须修改配置文件my.cnf，[mysqld]下增加或修改参数如下：

[mysqld] 
slow_query_log=1 
slow_query_log_file=/var/lib/mysql/atguigu-slow.log 
long_query_time=3 
log_output=FILE

开启慢查询日志后，什么样的SQL语句会记录到慢查询里面呢？这是通过设置的慢查询阈值控制的，通过以下命令可以查看：

SHOW VARIABLES LIKE 'long_query_time%';
或者
show global variables like 'long_query_time';

假如运行时间正好等于long_query_time的情况，并不会记录下来，也就是说。在MySQL源码里是判断大于long_query_time，而不是大于等于。

执行计划

启动执行计划

explain select 投影列 FROM 表名 WHERE 条件

查看执行计划命令

explain + SQL

最左匹配原则

MySQL 建立多列索引（联合索引）有最左匹配的原则，即最左优先：
如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；
如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引；

索引覆盖

索引覆盖就是为了避免回表查询的一种解决方案。见名知意，就是查询的所有列均被所使用的索引的列给到覆盖(可以是单列索引也可以是联合索引)。因为索引中已经包含了要查询的字段的值，因此查询的时候直接返回索引中的字段值就可以了，不需要再到表中查询，避免了对主键索引的二次查询，也就提高了查询的效率。

索引覆盖的优点
索引条目通常远小于数据行的大小，因为覆盖索引只需要读取索引，极大地减少了数据的访问量。
索引是按照列值顺序存储的，对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO小很多。
一些存储引擎比如MyISAM在内存中只缓存索引，数据则依赖操作系统来缓存，因此要访问数据的话需要一次系统调用，使用覆盖索引则避免了这一点。
由于InnoDB的聚簇索引，覆盖索引对InnoDB引擎下的数据库表特别有用。因为InnoDB的二级索引在叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，就避免了对主键索引的二次查询。