数据库系列-MySQL系列

Mysql知识点整理

2019-03-02  本文已影响222人  zhong0316

索引相关

索引类型

主键索引：数据列不允许重复，不允许为NULL。一个表只能有一个主键索引。InnoDB的主键索引为聚簇索引，而MyISAM的主键索引为非聚簇索引。
创建：ALTER TABLE table_name ADD PRIMARY KEY (column);

唯一索引：数据列不允许重复，允许为NULL，一个表中允许创建多个唯一索引。唯一索引可以用作业务防重。
创建：ALTER TABLE table_name ADD UNIQUE (column1, column2);

普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。
创建：ALTER TABLE table_name ADD KEY (column1, column2)

全文索引：是目前搜索引擎使用的一种关键技术，InnoDB不支持全文索引，MyISAM支持全文索引。
创建：ALTER TABLE table_name ADD FULLTEXT (column);

最左前缀原则

联合索引从左边的列到右边的列依次匹配，联合索引的最左前缀原则如下：
对于联合索引：index(co1, col2, col3)

  1. where col1=1，where col1=1 and col2=2，where col1=1 and col2=2 and col3=3都会走这个索引。第一个语句使用 col1，第二个语句使用 col1,col2，第三个使用 col1,col2,col3；
  2. where col2=2，where col2=2 and col3=3不会走索引;
  3. where col3=3 and col2=2 and col1=1未经查询优化的话不会走索引，但是经过查询优化后会走索引...；
  4. where col1=1 and col3=3 仅使用 col1；
  5. where col1=1 and col2>2 and col3=3 仅使用 col1,col2；
  6. where col1=1 and col2 like 'xx%' 使用 col1,col2；
  7. where col1=1 and col2 like '%xx'' 仅使用 col1。

索引算法（B-Tree 和 HASH）

B-Tree索引：

B-Tree索引是一棵多路查找平衡树，InnoDB 和 MyISAM 存储引擎都支持 B-Tree 索引。InnoDB 支持聚簇索引，InnoDB 默认会为主键创建聚簇索引，聚簇索引的非叶子节点不保存数据，只有叶子节点会保存数据，聚簇索引的叶子节点还会保存相邻的后一个节点的指针。非聚簇索引叶子节点不保存数据，只保存主键索引。

HASH索引：

Hash 索引检索效率高，只需要一次定位，不像 B-Tree 索引需要从根节点到叶子节点，最后才能访问到页节点这样多次的IO访问。
Hash索引虽然检索效率高，但是也有很多的不足之处：

  1. Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
    由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

  2. Hash 索引无法被用来避免数据的排序操作。
    由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且 Hash 值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

  3. Hash 索引不能利用部分索引键查询。
    对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

  4. Hash 索引在任何时候都不能避免表扫描。
    前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

  5. Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
    对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

B-Tree vs. HASH索引

索引名 HASH B-Tree
是否支持最左前缀匹配原则 不支持，只有索引的全部字段都用上才会匹配到 支持，用上索引的第一个字段就可以匹配索引
MyISAM和InnoDB是否支持 不支持（只有Memory和NDB引擎索引支持） 支持
范围查询能否命中索引 不可以，只有“=”,“IN”,“<=>”（等价于的意思）查询能命中 可以
是否一定会全表扫描 是 否

索引设计原则

  1. 为经常需要排序、分组和联合的字段建立索引
  2. 为经常作为 where 条件查询的字段建立索引
  3. 在选择性好的列上建立索引，所谓选择性好是指筛选出来的数据少，例如性别的选择性就比较差，而姓名的选择性就高
  4. 联合索引需要注意索引的顺序，将选择性高的放在前面
  5. 索引虽然能加快查询，但是索引也是有代价的，首先索引会占用存储空间。其次索引的维护也是有消耗的。因此，索引不是越多越好。

数据类型

数字类型

整数: tinyint、smallint、mediumint、int、bigint

type Storage Minumun Value Maximum Value
(Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

浮点数: float、double、real、decimal

属性 存储空间 精度 精确性 说明
FLOAT(M, D) 4 bytes 单精度 非精确 单精度浮点型，m总个数，d小数位
DOUBLE(M, D) 8 bytes 双精度 比Float精度高 双精度浮点型，m总个数，d小数位

FLOAT容易造成精度丢失

定点数DECIMAL

  1. 高精度的数据类型，常用来存储交易相关的数据
  2. DECIMAL(M,N).M代表总精度，N代表小数点右侧的位数（标度）1 < M < 254, 0 < N < 60;
  3. 存储空间变长

日期和时间

date、time、datetime、timestamp、year

类型 字节 例 精确性
DATE 三字节 2015-05-01 精确到年月日
TIME 三字节 11:12:00 精确到时分秒
DATETIME 八字节 2015-05-01 11::12:00 精确到年月日时分秒
TIMESTAMP 2015-05-01 11::12:00 精确到年月日时分秒

字符串类型

字符串: char、varchar
文本: tinytext、text、mediumtext、longtext

类型 单位 最大 特性
CHAR 字符 最大为255字符 存储定长，容易造成空间的浪费
VARCHAR 字符 可以超过255个字符 存储变长，节省存储空间
TEXT 字节 总大小为65535字节，约为64KB -

二进制(可用来存储图片、音乐等)

tinyblob、blob、mediumblob、longblob

InnoDB和MyISAM的区别

MVCC

MVCC的实现，是通过保存数据在某个时间点的快照来实现的，根据事务开始时间的不同，每个事务对于同一张表，同一时刻看到的数据可能是不同的。

InnoDB的 MVCC ，是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间（或删除时间）， 当然存储的并不是实际的时间值，而是系统版本号。

可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

在MVCC协议下，每个读操作会看到一个一致性的 snapshot，并且可以实现非阻塞的读。MVCC 允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务 ID，在同一个时间点，不同的事务看到的数据是不同的。

其中，写操作（insert、delete和update）执行时，需要将系统版本号递增。
由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。
通过MVCC很好的实现了事务的隔离性，可以达到repeated read级别，要实现serializable还必须加锁。

MVCC只在READ COMMITED 和 REPEATABLE READ 两个隔离级别下工作。READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE 则会对所有读取的行都加锁。

InnoDB间隙锁

Record lock

单条索引记录上加锁，InnoDB 的行锁是通过索引实现的，如没有索引，则锁住的不是记录行而是整个表。

Gap lock

间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

Next-key lock

Next-key lock 就是 Record lock + Gap lock 的组合，它锁定的是一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

间隙锁（Next-key lock）的条件

mysql的事务隔离级别是可重复读（RR，Repeatable Read），并且 innodb_locks_unsafe_for_binlog 参数设置为0

事务ACID

  1. 原子性（Atomicity）：不可分割的操作单元，事务中所有操作，要么全部成功；要么撤回到执行事务之前的状态
  2. 一致性（Consistency）：如果在执行事务之前数据库是一致的，那么在执行事务之后数据库也还是一致的；
  3. 隔离性（Isolation）：事务操作之间彼此独立和透明互不影响。事务独立运行。这通常使用锁来实现。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
  4. 持久性（Durability）：事务一旦提交，其结果就是永久的。即便发生系统故障，也能恢复。

delete drop truncate区别

truncate 和 delete只删除数据，不删除表结构 ,drop删除表结构，并且释放所占的空间。

删除数据的速度，一般来说: drop> truncate > delete

delete属于DML语言，需要事务管理，commit之后才能生效。drop和truncate属于DDL语言，操作立刻生效，不可回滚

使用场合：

Mysql explain

explain 用于查看 SQL 语句执行计划，其结果主要包含以下几个重要参数：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释。

select_type

表示查询中每个select子句的类型

type

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

Key

key列显示MySQL实际决定使用的键（索引）
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

explain 总结

参考资料

https://ningyu1.github.io/site/post/50-mysql-gap-lock/
MySQL 的 MVCC 原理
MySQL - 索引详解

上一篇 下一篇

猜你喜欢

热点阅读