MySQL-基础知识总结

转x33g5p2x  于2021-11-19 转载在 Mysql  
字(6.2k)|赞(0)|评价(0)|浏览(341)

内容多摘自《MySQL技术内幕：SQL编程》

数据库的应用类型

OLTP

联机事务处理(OnLine Transaction Processing)，也被称为面向交易的处理系统，是传统数据库的主要应用，主要是面向基本的、日常的事务处理。例如银行交易。

特点

  1. 实时性要求高
  2. 查询的数据量不是很大
  3. 交易一般是确定的，是对确定的数据进行存取
  4. 并发性要求高，并且严格要去事务的完整性、安全性

OLAP

联机分析处理（OnLine Analysis Processing），是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。

特点

  1. 实时性要求不是很高，很多应用每天更新一次数据
  2. 数据量大，因为支持的是动态查询，要通过对很多数据的统计才能得到想要知道的信息
  3. 因为重点在于决策，所以查询一般是动态的。

SQL语句分类

  1. DDL（Data Definition Language）：
    数据定义语言，用来定义不同的数据段、数据库、表、列、索引等数据库对象。如：create、drop、alter
  2. DML（Data Manipulation Language）:
    数据操纵语句，用于添加、删除、更新和查询数据记录，并检查数据完整性。如：insert、delete、update、select
  3. DCL（Data Control Language）：
    数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。定义数据库、表、字段、用户的访问权限和安全级别。如：grant、revoke

数据类型

  1. 数值类型
    支持所有标准SQL中的数值类型，包括严格数值类型以及近似数值数据类型，并且还进行了扩展。
  2. 日期时间类型
  3. 字符串类型

MySQL数据库库的组成

  1. 连接池组件
  2. 管理服务和工具组件
  3. SQL接口组件
  4. 查询分析器组件
  5. 优化器组件
  6. 缓冲组件
  7. 插件式存储引擎
  8. 物理文件

存储引擎

InnoDB存储引擎

  • 支持事务，其设计目标主要面向联机事务处理（OLTP）的应用。
  • 其特点是行锁设计、支持外键，并支持类似Oracle的非锁定读，即默认读取操作不会产生锁，从MySQL 5.5.8版本开始默认的存储引擎。
  • InnoDB存储引擎将数据放在一个逻辑的表空间中，从4.1版本开始，每个InnoDB存储引擎的表单独存放在一个独立的ibd文件中。此外支持将裸设备（row disk）用于建立其表空间。
  • InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了SQL标准的四种隔离级别，同时用一种称为netx-key locking的策略来避免幻读现象的产生。还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read ahead）等高性能和高可用的功能。
  • 表中数据的存储采用聚集（clustered）的方式，每张表都是按主键的顺序进行存储的，如果没有显式的在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。
  • 具备高可用性、高性能以及高可扩展性。

MyISM 存储引擎

  • 不支持事务、表锁设计、支持全文索引，主要面向一些OLAP数据库应用，MySQL 5.5.8版本之前默认的搜索引擎。
  • 缓冲池只缓存索引文件，而不缓存数据文件，与大多数的数据库都不相同。

NDB存储引擎

  • 2003年，MySQL AB公司从Sony Ericsson公司收购了NDB存储引擎。
  • 是一个集群存储引擎，类似于Oracle的RAC集群，与Oracle RAC的share everything结构不同的是，其结构是share nothing的集群架构，因此能提供更高级别的高可用性。
  • 特点是数据全部放在内存中（5.1版本开始，可以将非索引数据放在磁盘上），因此主键查找的速度极快，并且能够在线添加NDB数据存储节点以便线性地提高数据库性能。由此可见，NDB存储引擎是高可用、高性能、高可扩展性的数据库集群系统，其面向的也是OLTP的数据库应用类型。

Memory存储引擎

  • 数据都放在内存中，数据库重启或发生崩溃，表中的数据都将消失。
  • 非常适合于存储OLTP数据库应用中临时数据的临时表，也可以作为OLAP数据库应用中数据仓库的维度表
  • 默认使用哈希索引，而不是通常熟悉的B+树索引

Infobright存储引擎

第三方的，特点是存储是按照列而非行的，因此非常适合OLAP的数据库应用。

NTSE存储引擎

网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务，但提供压缩、行级缓存等特性。。。

范式与反范式

数据库设计之三范式与反范式

数据类型

  • UNSIGNED
    尽量不要使用UNSIGNED，可能会带来意想不到的效果，对于INT类型可能存放不了的数据，可以在设计阶段提升为BIGINT类型。
  • ZEROFILL
    ZEROFILL对列的数字，比如int(4)的数字1，查询的话，显示结果为0001。
    会自动给列添加UNSIGNED属性。
  • SQL_MODE
    强烈建议生产环境设置为严格模式（STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES中的至少一种）

日期时间类型

  • DATETIME：占8个字节，范围1000-01-01 00:00:00到9999-12-31 23:59:59

  • DATE：占3个字节，范围1000-01-01到9999-12-31

  • TIMESTAMP：占用4字节，范围1970-01-01 00:00:00 UTC 到 2038-01-19 03:14:07 UTC。显示结果和DATETIME一样，实际存储的内容为1970-01-01 00:00:00到当前时间的毫秒数。
    UTC:协调世界时，又称世界统一时间、世界标准时间和国际协调时间。
    区别：

  • TIMESTAMP可以在建表时给默认值，DATETIME不行。

  • 更新表时，可以设置TIMESTAMP列自动更新时间为当前时间

  • YEAR:占用1个字节，可以在定义时指定宽度。YEAR(4)范围19012155；YEAR(2)范围19702070。YEAR(2)设置下的0069代表20002069

  • TIME：占用3个字节，范围-838:59:59~838:59:59.

数字类型

  1. 整型
  2. 浮点型
  3. 高精度型
  4. 位类型

字符类型

  • CHAR和VARCHAR:
    一般来说，CHAR(N)用来保存固定长度的字符串，VARCHAR(N)用来存储变长字符类型。
    CHAR(N)中N的范围0255；VARCHAR中N的范围065535.这里的N都代表字符长度而非字节长度。
  • BINARY和VARBINARY：
    二进制的字符串，这里的长度指的是字节长度
  • BLOG和TEXT
    BLOG(Binary Large Object):用来存储二进制大数据类型的，大多数情况下可以看做是足够大的VARBINARY类型的列
    TEXT:可以看做是足够大的VARCHAR类型的列。
    这两个跟VARBINARY和VARCHAR不同的地方在于：
    建立索引时，必须要指定长度
    不能有默认值
    排序时只能用前max_sort_length（默认值1024）个字节。
  • ENUM和SET
    ENUM最多可枚举65536个元素，SET最多可枚举64个元素

索引

数据结构与算法

  1. 二分查找
  2. 二叉查找树和平衡二叉树
    二叉查找树：左子树的值始终小于根的键值；右子树的值总是大于根的键值。
    B+树是通过二叉查找树，再由平衡二叉树、B树演化而来的。
    二叉查找树可以任意构造，如果排成一列，跟顺序查找一样了，因此若想最大性能的构造一个二叉查找树，需要这颗二叉查找树是平衡的，这就引入了新的定义——二叉平衡树，又称为AVL树。
    平衡二叉树的定义：首先要符合二叉查找树的定义，其次必须满足任何节点的两颗子树的高度最大差为1。
    平衡二叉树查询速度是快了，但是维护平衡的代价非常大。
  3. B+树
    B+树由B树和索引顺序访问方法（ISAM，MyISAM引擎最初参考的数据结构）演化而来。
    简单来说：
    B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接。

B+树索引

B+树索引的本质就是B+树在数据库的实现，而B+树索引在数据库中的一个特点就是高扇出性（例如：在InnoDB存储引擎中，每个页的大小为16KB。因此在数据库中，每个B+树的高度一般在2~4层，意味着查找某一个键值最多只需要2~4次IO操作。而现在一般的磁盘每秒至少可以做100次IO操作。）。
索引是在存储引擎中实现的，因此每个引擎的B+树的实现方式可能是不同的，取决于存储引擎本身。
B+树索引可以分为聚集索引和辅助索引（非聚集索引），区别在于存放数据的内容。

InnoDB的B+树索引

InnoDB存储引擎是索引组织表（Index Organized Table,IOT），也就是说数据文件本身 就是按照B+树方式存放数据的。其中B+树的键值为主键，若在建立时没有显式的指定主键，则InnoDB默认会自动创建一个6字节的列作为主键。
因此在InnoDB引擎中，可以将B+树索引分为聚集索引和辅助索引。无论哪种索引，每个页的大小都是16KB，且不能更改。

聚集索引

根据主键创建的一颗B+树，聚集索引的叶子节点存放了表中的所有记录。

辅助索引

根据索引键创建的一颗B+树，与聚集索引不同的是，其叶子节点仅存放索引键值，以及该索引键值指向的主键。
也就是说，通过辅助索引来查找数据，那么当找到辅助索引的叶子节点后，很可能还需要根据主键值查找聚集索引来得到数据，这种查找方式又被称为书签查找。
因为辅助索引不包含行记录的所有数据，这就意味着每页可以存放更多的键值，因此其高度一般都要小于聚集索引。
若辅助索引时一个包含主键的联合索引，那么并不需要一个额外的列来存放主键。辅助索引会选择通过联合索引中的主键进行查找。

MyISAM的B+数索引

MyISAM存储引擎其实更像一张堆表，所有的行数据都存放与MYD文件中，其B+树索引都是辅助索引，存放与MYI文件中。
PRIMARY_KEY索引和其它索引不同之处在于其必须是唯一的，并且不可为NULL值。其索引页大小默认为1KB，同样不可以调整。与InnoDB不同的是，因为没有聚集索引，其索引叶节点存放的键值不是主键值，而是在MYD文件中的物理位置。

Cardinality

并不是所有在查询条件中出现的列都需要加索引。一般经验是：在访问表中很少一部分行时使用B+树索引才有意义。
比如性别字段（只有男、女）这种，取值范围较小，称为低选择性的。以性别为条件查询出的结果是表中50%的数据（假设男女比例1:1），此时设置B+树索引就完全没必要。
如果某个字段取值范围很广，几乎没有重复的，就是高可选择性的。
如何查看索引是否是高可选择性的：
通过SHOW INDEX语句中的Cardinality列来观察，Cardinality表示索引中唯一记录数量的预估值。在实际应用中，Cardinality/n_rows_in_table应尽可能接近1，如果非常小，就需要考虑是否还要建这个索引。

SHOW INDEX FROM table_name

联合索引

对表上的多个列（大于等于2）进行索引。
假定有两个整型列组成的索引，两个键值分别为a、b。
索引结构如图：

可以看到键值都是排序的，通过叶子节点可以逻辑上顺序的读出所有数据：(1,1),(1,2),(2,1),(2,4),(3,1),(3,2)。数据按照(a,b)的顺序存放。
显然，当查询
SELECT * FROM TABLE WHERE a=xxx and b=xxx,可以使用这个（a,b）索引。
SELECT * FROM TABLE WHERE a=xxx也可以使用这个(a,b)索引，
SELECT * FROM TABLE WHERE b=xxx,不可以使用这个索引。

覆盖索引

InnoDB支持覆盖索引，或称索引覆盖。即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。
个人理解：当你要查询的记录通过辅助索引就可以查到，这时的辅助索引就可以称为覆盖索引。

使用覆盖索引的好处是辅助索引不包含整行记录的信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

InnoDB版本小于1.0或者MySQL版本为5.0或以下的，不支持。

优化器不使用索引的情况

某些情况下，当执行EXPLAIN时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表扫描来得到数据。这种情况多发生于范围查找、JOIN操作等。

  • SELECT * FROM orderdetails WHERE orderid>10000 and orderid <10200;
    表中有（orderid,productid）的联合主键，此外还有对orderid的单个索引。但是优化器却选择了PRIMARY索引，而非orderid辅助索引。
    原因：orderid索引不能覆盖到我们要查询的信息，因此在对orderid索引进行查询到指定数据的操作后，还需要进行一次书签访问来查找整行数据的信息。虽然在orderid的索引中数据是顺序存放的，但是再一次的书签查找数据是无序的，因此变成了磁盘上的离散读取操作。
    如果要求访问的数据量很小，优化器还是会选择辅助索引，当访问的数据占整个表中数据的很大一部分时（一般20%左右），就会选择通过聚集索引来查找数据。

INDEX HINT（索引提示）

MySQL支持INDEX HINT显式的告诉优化器使用哪个索引。
SELECT * FROM table USE INDEX(a) WHERE a=1 AND b=2
USE INDEX只是告诉优化器可以选择该索引，但实际上优化器会根据自己的判断进行选择。
如果确定用哪个索引来完成查询时，最可靠的是使用FORCE INDEX而不是USE INDEX。

索引失效的情况

索引查询失效的情况
MySQL索引优化铁则
索引分析的工具：explain命令加在要执行的sql语句前面，执行结果中的key这一列，就是用到的索引，如果为NULL，则说明没有使用索引。

  • like以%开头时。
    前缀没有%，后缀有%则依然有效

  • or语句前后没有同时使用索引时。
    or左右查询字段只有一个是索引，该索引失效；左右的字段都是索引时，索引才有效。

  • 组合索引，不是使用第一列索引，索引失效。
    失效原理，见：索引>联合索引中的图解

  • 数据类型出现隐式转化。
    如：varchar不加单引号的话可能会自动转化为int类型，使索引失效，变成全表扫描。

  • 在索引列上使用not、<>、!= 。
    不等于操作是永远都不会用到索引的。

  • 对索引字段进行计算操作、字段上使用函数。

  • 当全表扫描速度比索引查询的速度快时，MySQL会使用全表扫描，此时索引失效。

日志

undo日志

假设有id=10这行数据的name=张三,现在要更新为xxx，此时得先把要更新的原来的值张三和id=10这些信息，写入到undo日志文件中去。用于回滚

redo日志

redo日志存的是对数据做了什么修改，比如对id=10这行记录修改了name字段的值为xxx。可用于异常宕机时恢复，可以继续执行

binlog日志

又叫做归档日志。记录的是偏向于逻辑性的日志，类似于:对users表中id=10这样数据做了更新操作，更新以后的值是xxx。
binlog日志不是InnodB存储引擎特有的额，是属于MySQL server自己的日志文件。可用于数据恢复，主从复制

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://www.cnblogs.com/onlyrun/p/15164826.html

内容来源于网络，如有侵权，请联系作者删除！

相关文章

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com