关系型数据库核心要点汇总——MySql

  • Z时代
  • 2024-01-10
  • 分类：综合

database

  • 内联接：在两张表进行连接查询时，只保留两张表中完全匹配的结果集。

    • select .... from t1 inner join t2 on t1.ID=t2.ID

    • 结果是只保留既是本校校友，又是两院院士的人的信息。

  • 外联接：分为左联接和右联接两种

    1. 左联接：在两张表进行连接查询时，会返回左表所有的行，即使左表在右表中没有匹配的记录。

      • select .... from t1 left (outer) join t2 on t1.ID=t2.ID

      • 结果是返回全部本校校友的记录，部分校友可能同时是院士，其他大部分校友，t2表的相关字段值都为null。

    2. 右联接：在两张表进行连接查询时，会返回右表所有的行，即使右表在左表中没有匹配的记录。

      • select .... from t1 right (outer) join t2 on t1.ID=t2.ID

      • 结果是返回全部两院院士的记录，部分院士可能是我校校友，其他大部分院士，t1表的相关字段值都为null。

  • 全联接：在两张表进行连接查询时，返回左表和右表中所有的行（即便没有匹配）。

    • select .... from t1 full join t2 on t1.ID=t2.ID

    • 结果是返回本校校友+两院院士所有人的记录（当然会去重）。

    • 其实也就是left join和right join的并集。

单纯的select * from a,b是笛卡尔乘积。比如a表有5条数据，b表有3条数据，那么最后的结果有5*3=15条数据。但是如果对两个表进行关联:select * from a,b where a.id = b.id 意思就变了，此时就等价于：select * from a inner join b on a.id = b.id。即就是内连接。但是这种写法并不符合规范，可能只对某些数据库管用，如sqlserver。推荐最好不要这样写。最好写成inner join的写法。

1.2 drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别

  1. delete和truncate只删除表的数据不删除表的结构，drop都删除。

  2. 一般来说，执行速度方面是 drop> truncate >delete

  3. delete语句是dml，这个操作会放到rollback segement中，事务提交之后才生效; 如果有相应的trigger，执行的时候将被触发。

  4. truncate、drop是ddl，操作立即生效，原数据不放到rollback segment中，不能回滚.。操作不触发trigger.

1.3 数据并发问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

  1. 脏读（Dirty read）:

    • 针对同一个字段，一个事务（假设事务A）读到了另一个的事务（假设事务B）提交前的数据。比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。

  2. 丢失修改（Lost to modify）:

    • 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

    • 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

  3. 不可重复读（Unrepeatableread）:

    • 一般发生在一个事务要在事务内读取一个字段多次的场景。

    • 事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据和第一次读取的时候不一样了，就是所谓的不可重复读了。

  4. 幻读（Phantom read）:

    • 幻读与不可重复读类似。也发生在一个事务在事务内部针对某些记录多次查询的情况。

    • 例如在一个事务（A）读取了几行数据，接着另一个并发事务（B）插入并提交了一些数据，并且这些数据符合事务A的where条件时。在第二次的查询中，事务（A）就会发现相比第一次查询，第二次多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读两者有些相似，他们的区别是：

| 不可重复读 | 幻读 |

| ------------ | ------------ |

| 针对的是update或delete | 针对的是insert |

| 重点是修改:同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 | 重点在于新增或者删除 (数据条数变化)：同样的条件, 第1次和第2次读出来的记录数不一样 |

1.4 事务隔离级别

SQL 标准定义了四个隔离级别：

  1. READ-UNCOMMITTED(读未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

  2. READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

  3. REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

  4. SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认的隔离级别是 REPEATABLE-READ（可重复读）。我们可以通过SELECT @@tx_isolation;命令来查看

我们知道隔离级别越低，事务请求的锁越少，并发效率越高，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。

与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server) 是不同的。

所以说InnoDB 存储引擎的默认的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。

2 数据库设计的三范式

2.1 第一范式（1NF）

1NF是对属性的原子性，要求每一列（或者叫字段，属性）具有原子性，不可再分解；

如

学生表（学号，姓名，性别，生日）

如果认为最后一列还可以再分成（出生年，出生月，出生日），它就不满足第一范式了；

2.2 第二范式（2NF）

第二范式是指在满足第一范式的条件下，除主键外的每一列都完全依赖于主键（主要针对于联合主键而言）。

2NF是对记录的惟一性，要求记录有惟一标识，即实体的惟一性，即不存在部分依赖；

举个反例：

表（学号、课程号、姓名、学分） 联合主键为学号和课程号

这个表明显涵盖了两个信息主体：

  1. 学生信息：学号和姓名字段属于学生信息，且姓名依赖于学号（学生信息的唯一标识）

  2. 课程信息：课程号和学分字段属于课程信息，学分依赖课程号（课程信息的唯一标识）。

姓名由学号即可唯一标识，是对主键的部分依赖；

学分由课程号即可唯一标示，是对主键的部分依赖；

由于2NF要求非主键字段必须完全依赖主键，所以不符合二范式。

可能会存在问题：

  • 数据冗余:，每条记录都含有相同信息；

  • 删除异常：删除所有学生成绩，就把课程信息全删除了；

  • 插入异常：学生未选课，无法记录进数据库；

  • 更新异常：调整课程学分，所有行都调整。

正确做法:

  • 学生表：Student(学号, 姓名)；

  • 课程表：Course(课程号, 学分)；

  • 选课关系表：StudentCourse(学号, 课程号, 成绩)。

2.3 第三范式（3NF）

第三范式是指在满足第二范式的基础上，每一条数据不能依赖于其他的非主属性，也就是消除了传递依赖关系。

3NF是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖；

例如

表（学号, 姓名, 年龄, 学院名称, 学院电话）

因为存在依赖传递: (学号) → (学生)→(所在学院) → (学院电话) 。

可能会存在问题：

  • 数据冗余:有重复值；

  • 更新异常：有重复的冗余信息，修改时需要同时修改多条记录，否则会出现数据不一致的情况

正确做法：

  • 学生：(学号, 姓名, 年龄, 所在学院)；

  • 学院：(学院, 电话)。

2.4 反范式化

一般说来，数据库只需满足第三范式（3NF）就行了。没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。达到以空间换时间的目的。

比如：有一张存放商品的基本表，“金额”这个字段的存在，表明该表的设计不满足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“金额”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。

3. MySql存储引擎简述

简单来说，存储引擎就是指表的类型以及表在计算机上的存储方式。

存储引擎的概念是MySQL的特点，Oracle中没有专门的存储引擎的概念，Oracle有OLTP和OLAP模式的区分。不同的存储引擎决定了MySQL数据库中的表可以用不同的方式来存储。我们可以根据数据的特点来选择不同的存储引擎。

在MySQL中的存储引擎有很多种，可以通过mysql> show engines;语句来查看。下面重点关注InnoDB、MyISAM、MEMORY这三种。

3.1 InnoDB引擎

MySQL默认的事务型引擎，也是最重要和使用最广泛的存储引擎。在MySQL从3.23.34a版本开始包含InnnoDB。

InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。它是MySQL上第一个提供外键约束的存储引擎。而且InnoDB对事务处理的能力，也是其他存储引擎不能比拟的。

InnoDB的性能与自动崩溃恢复的特性，使得它在非事务存储需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

3.2 MyISAM引擎

在MySQL 5.1 及之前的版本，MyISAM是默认引擎。MyISAM提供的大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM并不支持事务以及行级锁，而且一个毫无疑问的缺陷是崩溃后无法安全恢复。正是由于MyISAM引擎的缘故，即使MySQL支持事务已经很长时间了，在很多人的概念中MySQL还是非事务型数据库。尽管这样，它并不是一无是处的。对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以使用MyISAM（但请不要默认使用MyISAM，而是应该默认使用InnoDB）

3.3 MEMORY引擎

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。

MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。

注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

3.4 如何合适的选择存储引擎

  • 有以下要求，则适合采用InnoDB：

    • 需要对事务的完整性要求比较高（比如银行）

    • 要求实现并发控制（比如售票）

    • 如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

  • 有以下要求，则适合采用MyISAM：

    • 如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。

    • 如果应用对数据的完整性、并发性要求比较低，也可以使用。

  • 有以下要求，则适合采用MEMORY：

    • 如果需要很快的读写速度，对数据的安全性要求较低，且数据量很小时，可以选择MEMOEY。

3.5 MyISAM与InnoDB区别

项目

InnoDB

MyISAM

存储结构

所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

每个MyISAM在磁盘上存储成三个文件。分别为：表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

存储空间

需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

MyISAM支持支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。

可移植性、备份及恢复

免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

事务支持

提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。

AUTO_INCREMENT

InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

锁

支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE中指定主键是有效的，非主键的WHERE都会锁全表的。

只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

全文索引

原来不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。后来从InnoDB1.2.x版本（MySQL 5.6版本）起，InnoDB存储引擎开始支持全文索引

支持 FULLTEXT类型的全文索引

表主键

如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

允许没有任何索引和主键的表存在，索引都是保存行的地址。

表的具体行数

没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了where条件后，myisam和innodb处理的方式都一样。

保存有表的总行数，如果select count(*) from table;会直接取出出该值。

CRUD操作

如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。

如果执行大量的SELECT，MyISAM是更好的选择。

外键

支持

不支持

4. MySql索引

我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。

在数据之外，数据库系统维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

上图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

虽然这是一个货真价实的索引，但是实际的数据库系统几乎没有使用二叉查找树或其进化品种红黑树（red-black tree）实现的，原因会在下文介绍。

4.1 索引的优缺点

优点：

  • 可以快速检索，减少I/O次数，加快检索速度；

  • 根据索引分组和排序，可以加快分组和排序；

缺点：

  • 索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；

  • 索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；

  • 构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

4.2 索引的分类

4.2.1 按类型分类

  1. 聚集索引

    • 主键索引；

      • 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

  2. 二级索引（又称辅助索引、非聚簇索引）

    • 唯一索引；

      • 约束数据列不允许重复，允许为NULL值

      • 一个表允许组合多个列创建唯一索引，这时约束的是：不同记录，被唯一索引约束的这多个列不能让完全相同

    • 普通索引（又叫辅助索引）；

      • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

      • 会对该列创建索引。

    • 组合索引(又称联合索引，复合索引)；

      • 即普通索引的多字段版本

      • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

      • 如下图，可以理解成把几个字段拼接起来的一个普通索引

4.2.2 按按数据结构分类

  • BTree索引

    • 下文详解

  • B+Tree索引；

    • 下文详解

  • 哈希索引；

    • 只有memory存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存储该值所在行数据的物理位置。

    • 因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

  • 全文索引；

    • 通过建立倒排索引来实现，查询效率比like有很大提升。

    • 5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本之后InnoDB存储引擎开始支持全文索引

    • 在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。

4.2.3 聚簇索引和非聚簇索引的区别（针对InnoDB）

假设我们有如下表

mysql对ID生成了聚簇索引，我们再对k字段生成普通索引（非聚簇），如下图：

其中R代表一整行的记录。

从图中不难看出，聚簇索引和非聚簇索引的区别是：非聚簇索引的叶子节点存放的是主键的值，而聚簇索引的叶子节点存放的是整行数据。

根据这两种结构我们来进行下查询，看看他们在查询上有什么区别。

  1. 如果查询语句是 select * from table where ID = 100，即主键查询的方式，则只需要搜索 ID 这棵 B+树。

  2. 如果查询语句是 select * from table where k = 1，即非主键的查询方式，则先搜索k索引树，得到ID=100，再到ID索引树搜索一次，这个过程也被称为回表。

什么非主键索引结构叶子节点存储的是主键值？

一是保证一致性，更新数据的时候只需要更新主键索引树，二是节省存储空间。

4.2.4 为什么建议使用主键自增的索引

自增的主键，插入到索引的时候，直接在最右边插入就可以了

但是如果插入的是 ID = 350 的一行数据，由于 B+ 树是有序的，那么需要将下面的叶子节点进行移动，腾出位置来插入 ID = 350 的数据，这样就会比较消耗时间，如果刚好 R4 所在的数据页已经满了，需要进行页分裂操作，这样会更加糟糕。

所以使用自增主键，每次插入的 ID 都会比前面的大，那么就可以避免这种情况。

4.3 索引的数据结构

索引的数据结构，常见的是B树和B+树，MySql的索引使用的是B+树，关于B树一家子的分析，可以详见下文：B树/B+树分析

不过虽然都是使用B+树来做数据结构，但在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的（不过至少都是B+树）。

4.3.1 MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，其主键索引和普通索引在结构上没有区别，叶节点的data域存放的是数据记录的地址。

4.3.1.1 MyISAM主键索引

如下图，这时一个针对主键col1字段的索引结构图：

可以看出MyISAM的索引文件仅仅保存数据记录的地址。

4.3.1.2 MyISAM普通索引

在MyISAM中，主索引和普通索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而普通索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

发现没有？MyISAM的索引方式，跟我们上文说的非聚簇索引十分相像（一个是存放id，一个是存放地址）。所以MyISAM索引的实现方式是非聚簇索引。

4.3.2 InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。对，InnoDB的索引是聚簇式的：InnoDB的数据文件本身就是索引文件，树的叶节点data域保存了完整的数据记录。

4.3.2.1 InnoDB主键索引实现

我们先来看 InnoDB的主键索引，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

4.3.2.2 InnoDB普通索引实现

在MyISAM中主索引和普通索引（Secondary key）在结构上没有任何区别，但InnoDB中，普通索引和主键索引是不同的，前文我们也介绍过，InnoDB的普通索引是非聚簇式的。

例如，图11为定义在Col3上的一个辅助索引：

图中的15,18这些数字，就是col3所对应的主键值，普通索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

4.3.3 联合索引的数据结构

我们知道了Mysql的索引采用B+树，那么，联合索引的B+树长什么样呢？？

假如我们有一张表

那么，联合索引的B+树结构是长这样的：

即每个元素的key，都是b,c,d三个字段的组合。那么不同元素之间的排序是依照什么规则呢？第一列的值大小吗？

答案是：先判断 b 再判断 c 然后是 d，即优先级为b>c>d。

4.4 索引生效条件

我们创建了索引，但很多时候，我们发现我们的查询语句无法使用到索引，基于此，我们首先要了解索引的命中规则。

那么怎么知道我们写的sql语句是否有使用到索引呢，可以使用explain命令，直接在sql语句前加explain执行：

explain执行结果关注以下几个字段：

  1. select_type:

    • 查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询

      • SIMPLE：查询中不包含子查询或者UNION

      • 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY

      • 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

  2. possible_keys

    • 表示查询时可能使用的索引。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引

  3. key

    • 显示sql执行过程中实际使用的键或索引，如果为null则表示未使用任何索引，必须进行优化。

  4. rows

    • rows是指这次查找数据所内循环的次数。

  5. Extra:

    • 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息

  6. type

    • type意味着类型，这里的type官方全称是“join type”，意思是“连接类型”,这样很容易给人一种错觉觉得必须需要俩个表以上才有连接类型。事实上这里的连接类型并非字面那样的狭隘，

    • 它更确切的说是一种数据库引擎查找表的一种方式，在《高性能mysql》一书中作者更是觉得称呼它为访问类型更贴切一些。

    • mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all<index<range<ref<eq_ref<const。从左到右，它们的效率依次是增强的。

    • 撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠右，但实际运用中还是要综合考虑各个方面的。

4.4.1 explain的type字段类型

  1. all：这便是所谓的“全表扫描”，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。all是一种非常暴力和原始的查找方法，非常的耗时而且低效。

  2. index：这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据

  3. range：range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及">","<"外，in和or也是索引范围扫描。

  4. ref：出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复（使用了普通索引的意思）。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

  5. ref_eq：ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个。什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况。比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。

  6. const：通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。即直接按主键或唯一键读取。

  7. NULL：不用访问表或者索引，直接就能得到结果，如select 1 from test where 1

看起来const和ref_eq貌似是一样的啊，都是使用主键或者唯一性索引，其实eq_ref是用于联表查询的情况，按联表的主键或唯一键联合查询。

4.4.2 索引失效场景

很多时候，我们在列上建了索引，查询条件也是索引列，但最终执行计划没有走它的索引。那到底哪些场景，会导致索引失效呢？

  1. 列与列对比

    • 某个表中，有两列（id和c_id）都建了单独索引，下面这种查询条件不会走索引

    • select * from test where id=c_id;

  2. 存在NULL值条件

    • 我们在设计数据库表时，应该尽力避免NULL值出现，如果非要不可避免的要出现NULL值，也要给一个DEFAULT值

    • select * from test where id=c_id;

  3. NOT条件

    • 我们知道建立索引时，给每一个索引列建立一个条目，如果查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。反过来当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描，这类的查询条件有：<>、NOT、not exists

    	select * from test where id<>500;
    	select * from test where not in (6,7,8,9,0);
    	select * from test where not exists (select 1 from test_02 where test_02.id=test.id);

  4. LIKE通配符的前匹配

    • 当使用模糊搜索时，尽量采用后置的通配符，例如：name%，因为走索引时，其会从前去匹配索引列，这时候是可以找到的，如果采用前匹配，那么查索引就会很麻烦，比如查询所有姓张的人，就可以去搜索’张%’。相反如果你查询所有叫‘明’的人，那么只能是%明。这时候索引如何定位呢？前匹配的情况下，执行计划会更倾向于选择全表扫描。后匹配可以走INDEX RANGE SCAN。

    • select * from test where name like "张%";

  5. 条件上对列使用函数

    • 查询条件上尽量不要对索引列使用函数，比如下面这个SQL——这样是不会走索引的，因为索引在建立时会和计算后可能不同，无法定位到索引。

    • select * from test where upper(name)="SUNYANG";

    • 但如果查询条件不是对索引列进行计算，那么依然可以走索引。比如

    • select * from test where name=upper("sunyang");

  6. 数据类型的转换

    • 当查询条件存在隐式转换时，索引会失效。比如在数据库里id存的number类型，但是在查询时，却用了下面的形式：

    • select * from sunyang where id="123";

  7. 谓词运算

    • 我们在上面说，不能对索引列进行函数运算，这也包括加减乘除的谓词运算，这也会使索引失效。建立一个sunyang表，索引为id，看这个SQL：

    • select * from sunyang where id/2=15;

    • 这里很明显对索引列id进行了’/2’除二运算，这时候就会索引失效，这种情况应该改写为：

    • select * from sunyang where id=30;

  8. or连接中包含非独立索引

    • 先看如下这个sql：

    • SELECT * from t WHERE id = 1 or uid = 2;

    • 如果id和uid都有单独的索引，那么mySql优化器会采用index merge 技术使其走索引。index merge 技术简单说就是在用OR，AND连接的多个查询条件时，可以分别使用前后查询中的索引，然后将它们各自的结果合并交集或并集。

    • 但如果uid列上没有单独的索引，那么这个sql将不会走索引，即便id上有主键索引。

4.4.3 联合索引生效条件（最左前缀原则）

上文中我们介绍了联合索引的数据结构，对于index(b,c,d)是长这样的：

因为联合索引中的元素key都是一个组合值<b,c,d>，且排序依据的优先级是b>c>d，所以联合索引的生效条件，要满足最左前缀原则。我们看如下sql：

SELECT * from t1 WHERE b = 1 and c = 2 and d = 3; //走索引
SELECT * from t1 WHERE b = 1 and c = 2                  //走索引
SELECT * from t1 WHERE b = 1                                  //走索引
SELECT * from t1 WHERE c = 2 and d = 3;                 //不走索引
SELECT * from t1 WHERE d = 3;                                 //走索引
SELECT * from t1 WHERE b = 1 and d = 3                 //走部分索引，至少会走到b = 1的子树上。
//范围查询
SELECT * from t1 WHERE b < 1;                                  //走索引
SELECT * from t1 WHERE b < 1 and c < 2 and d>3;   //走部分索引，b<1走了索引，后面两个条件无法走索引。（索引最多用于一个范围列）

这就是最左前缀原则，还是比较好理解的，需要注意的是索引最多用于一个范围列（且只能是最左的列）。

不过大多数时候，mySql优化器会按照现有的索引来优化sql语句的where条件顺序，比如SELECT * from t1 WHERE c = 2 and b = 1 就会被优化为SELECT * from t1 WHERE b = 1 and c = 2，使得这条sql可以走索引。

4.5 索引优化

4.5.1 索引的选择性

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

  1. 表记录比较少:

    • 例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

  2. 索引的选择性较低。

    • 所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：

    • Index Selectivity = Cardinality / #T

    • 显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。例如，employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

    • SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;

4.5.2 前缀索引

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

下面以employees.employees表为例介绍前缀索引的选择和使用。

如果我们需要频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+

<first_name>显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+

选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+

这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引建上：

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

此时再执行一遍按名字查询，比较分析一下与建索引前的结果：MYSQL中使用SHOW PROFILE命令分析性能

SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name="Eric" AND last_name="Anido" |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name="Eric" AND last_name="Anido" |
+----------+------------+---------------------------------------------------------------------------------+

性能的提升是显著的，查询速度提高了120多倍。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

参考材料

  1. 数据库逻辑设计之三大范式通俗理解，一看就懂，书上说的太晦涩

  2. 《高性能MySQL》

  3. MySQL索引背后的数据结构及算法原理

  4. mysql中explain的type的解释

以上是 关系型数据库核心要点汇总——MySql 的全部内容， 来源链接： utcz.com/z/533427.html

回到顶部