MySQL数据库面试题
创始人
2024-02-01 12:38:00

基础知识

为什么要使用数据库/数据库的优点？

使用数据库可以高效且条理分明地存储数据，它使人们能够更加迅速和方便地管理数据，主要体现在以下几个方面。

  • 数据库可以结构化存储大量的数据信息，方便用户进行有效的检索和访问。数据库可以对数据进行分类保存，并且能够提供快速的查询。例如，我们平时使用百度搜索内容时，百度也是基于数据库和数据分类技术来达到快速搜索的目的。
  • 数据库可以有效地保持数据信息的一致性、完整性、降低数据冗余。可以很好地保证数据有效、不被破坏，而且数据库自身有避免重复数据的功能，以此来降低数据的冗余。
  • 数据库可以满足应用的共享和安全方面的要求，把数据放在数据库中在很多情况下也是出于安全的考虑。
    例如，如果把所有员工信息和工资数据都放在磁盘文件上，则工资的保密性就无从谈起。如果把员工信息和工资数据放在数据库中，就可以只允许查询和修改员工信息，而工资信息只允许指定人（如财务人员）查看，从而保证数据的安全性。
  • 数据库技术能够方便智能化地分析（数据挖掘），产生新的有用信息。

例如，超市中把物品销售信息保存在数据库中，每个月销售情况的排名决定了下半月的进货数量。数据库查询的结果实际上产生了新的数据信息。数据挖掘、联机分析等技术近年来发展非常快，其核心意义在于从一堆数据中分析出有用的信息。

什么是SQL？

结构化查询语言(Structured Query Language)简称SQL，用于存取数据、查询、更新和管理关系数据库系统

什么是MySQL?

MySQL是一个开源的关系型数据库管理系统，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database ManagementSystem，关系数据库管理系统) 应用软件之一，在Java企业级开发中非常常用

数据库三大范式是什么？

第一范式：确保每列的原子性，每个列都不可以再拆分。

第二范式：在第一范式的基础上，属性完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，属性不依赖于其它非主属性，属性直接依赖于主键，

第三范式确保没有传递函数依赖关系，也就是消除传递依赖，数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。

在设计数据库结构的时候，要尽量遵守第三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

好文参考：数据库三大范式_凉_ting的博客-CSDN博客

好文参考：什么是数据库三大范式,通俗讲解 一讲就懂_凉心良意的博客-CSDN博客

mysql有关权限的表都有哪几个？

mysql服务器通过权限表来控制用户对数据库的访问, 权限表存放在mysql数据库里, 有mysql_install_db脚本初始化. 这些权限表分别有user, db, table_priv, columns_priv和host. 下面分别介绍一下这些表的结构和内容.

MySQL在安装时会自动创建一个名为mysql的数据库，mysql 数据库中存储的都是用户权限表。用户登录以后，MySQL会根据这些权限表的内容为每个用户赋予相应的权限。

user表是 MySQL中最重要的一个权限表，用来记录允许连接到服务器的账号信息。需要注意的是，在user表里启用的所有权限都是全局级的，适用于所有数据库。

  • user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
  • db权限表：记录各个帐号在各个数据库上的操作权限。
  • table_priv权限表：记录数据表级的操作权限。
  • columns_priv权限表：记录数据列级的操作权限。
  • host权限表：host表中存储了某个主机对数据库的操作权限，配合db表对给定主机上数据库级操作权限做更细致的控制，但host表一般很少用，现在新版本MYSQL都已经没有host表了。这个权限表不受GRANT和REVOKE语句的影响。

MySQL的binlog日志有几种录入格式？分别有什么区别？

在MySQL中，我们经常需要打开binlog来观察用户对某一个数据库的操作，binlog中记载着对用户对某个数据库所做的所有修改类操作，例如delete，update，insert等等。binlog一般情况下分为三种格式，分别是row格式、statement格式、mixed格式

Row格式

此格式不记录sql语句上下文相关信息，仅保存哪条记录被修改。新版本的MySQL中队Row模式也被做了优化，并不是所有的修改都会以Row模式来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更

优点

binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以Row格式的日志内容会非常清楚的记录下每一行数据修改的细节。

缺点

所有的执行的语句记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句或者一条alter语句，修改多条记录，则binlog中每一条修改都会有记录，每条记录都发生改变，那么该表每一条记录都会记录到日志中，这样造成binlog日志量会很大。

Statement格式

该格式下每一条会修改数据的sql都会记录在binlog中。

优点

不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。它相比row模式能节约很多性能与日志量，具体节约的多少取决于应用的SQL情况。正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，考虑到整表删除等一些大量数据操作，ROW格式会产生大量日志，所以总体来讲statement模式会稍微好一些。

缺点

记录的只是执行语句，由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制

Mixed格式

该格式是以上两种混合使用，一般的语句修改使用statment格式保存binlog，当statement无法完成主从复制的操作时(设计一些函数时)，则采用Row格式保存binlog,

MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种

mysql有哪些数据类型

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

INT - 正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295。 可以指定多达11位的宽度。

TINYINT - 一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255，可以指定多达4位数的宽度。

SMALLINT - 一个小的整数，可以带符号。如果有符号，允许范围为-32768至32767。如果无符号，允许的范围是从0到65535，可以指定最多5位的宽度。

MEDIUMINT - 一个中等大小的整数，可以带符号。如果有符号，允许范围为-8388608至8388607。 如果无符号，允许的范围是从0到16777215，可以指定最多9位的宽度。

BIGINT - 一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615. 可以指定最多20位的宽度。

FLOAT(M,D) - 不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。这不是必需的，并且默认为10,2。其中2是小数的位数，10是数字(包括小数)的总数。小数精度可以到24个浮点。

DOUBLE(M,D) - 不能使用无符号的双精度浮点数。可以定义显示长度(M)和小数位数(D)。 这不是必需的，默认为16,4，其中4是小数的位数。小数精度可以达到53位的DOUBLE。 REAL是DOUBLE同义词。

DECIMAL(M,D) - 非压缩浮点数不能是无符号的。在解包小数，每个小数对应于一个字节。定义显示长度(M)和小数(D)的数量是必需的。 NUMERIC是DECIMAL的同义词。

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性。

DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间。 例如，1973年12月30日将被存储为1973-12-30。

DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之间。例如，1973年12月30日下午3:30，会被存储为1973-12-30 15:30:00。

TIMESTAMP - 1970年1月1日午夜之间的时间戳，到2037的某个时候。这看起来像前面的DATETIME格式，无需只是数字之间的连字符; 1973年12月30日下午3点30分将被存储为19731230153000(YYYYMMDDHHMMSS)。

TIME - 存储时间在HH:MM:SS格式。

YEAR(M) - 以2位或4位数字格式来存储年份。如果长度指定为2(例如YEAR(2))，年份就可以为1970至2069(70〜69)。如果长度指定为4，年份范围是1901-2155，默认长度为4。

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

CHAR(M) - 固定长度的字符串是以长度为1到255之间个字符长度(例如：CHAR(5))，存储右空格填充到指定的长度。 限定长度不是必需的，它会默认为1。

VARCHAR(M) - 可变长度的字符串是以长度为1到255之间字符数(高版本的MySQL超过255); 例如： VARCHAR(25). 创建VARCHAR类型字段时，必须定义长度。

BLOB 或 TEXT - 字段的最大长度是65535个字符。 BLOB是“二进制大对象”，并用来存储大的二进制数据，如图像或其他类型的文件。定义为TEXT文本字段还持有大量的数据; 两者之间的区别是，排序和比较上存储的数据，BLOB大小写敏感，而TEXT字段不区分大小写。不用指定BLOB或TEXT的长度。

TINYBLOB 或 TINYTEXT - BLOB或TEXT列用255个字符的最大长度。不指定TINYBLOB或TINYTEXT的长度。

MEDIUMBLOB 或 MEDIUMTEXT - BLOB或TEXT列具有16777215字符的最大长度。不指定MEDIUMBLOB或MEDIUMTEXT的长度。

LONGBLOB 或 LONGTEXT - BLOB或TEXT列具有4294967295字符的最大长度。不指定LONGBLOB或LONGTEXT的长度。

ENUM - 枚举，这是一个奇特的术语列表。当定义一个ENUM，要创建它的值的列表，这些是必须用于选择的项(也可以是NULL)。例如，如果想要字段包含“A”或“B”或“C”，那么可以定义为ENUM为 ENUM(“A”，“B”，“C”)也只有这些值(或NULL)才能用来填充这个字段。

注意

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

你了解的myql存储引擎有哪些？

存储引擎是什么？

存储引擎是 MySQL 的核心，是负责 MySQL 中数据的存储和提取。存储引擎就是存储数据、建立索引、更新、查询数据等技术的实现方式。存储引擎是基于表而不是基于库的，所以存储引擎也可以被称为表引擎。不同的存储引擎提供不同的存储机制、索引技巧等功能，使用不同的存储引擎还可以获得特定的功能。在 MySQL5.5 之后，InnoDB 是 MySQL 默认的存储引擎。

你了解的myql存储引擎有哪些？

mysql8.0提供了9种存储引擎，默认使用的是InnoDB。但是我们常用的一般也就是其中的四种存储引擎，分别是：InnoDB、MyISAM、MEMORY和ARCHIVE，所以我们重点分析这4中存储引擎。

1、InnoDB存储引擎

InnoDB是目前MYSQL的默认存储引擎，是事务型数据库的首选引擎，是目前最重要、使用最广泛的存储引擎。支持事务处理，支持外键，支持崩溃修复能力和并发控制

主要特性：

  • 提供了事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。
  • 支持自增长列（auto_increment）。
  • 支持外键（foreign key）。
  • 支持MVCC的行级锁。
  • 索引使用的是B+树。

优点：提供了良好的事务处理、崩溃修复能力和并发控制。

缺点：读写效率较差，占用的数据空间相对较大。

场景

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

2、MyISAM存储引擎

MyISAM 这种存储引擎不支持事务，不支持行级锁，只支持并发插入的表锁，插入数据快，空间和内存占的少。如果数据表主要用来查询记录，则MyISAM引擎能提供较高的处理效率

优点：占用空间小，处理速度快。

缺点：不支持事务。

场景

  • 表中绝大多数都只是读查询（一般R/W > 100:1且update相对较少），可以考虑 MyISAM。
  • 如果对应用的完整性、并发性要求比较低，也可以使用。

3、MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为.frm。该文件只存储表的结构，而其数据文件，都是存储在内存中，这样有利于对数据的快速处理，提高整个表的处理能力。所有的数据都在内存中，数据的处理速度快，但是安全性不高；它对表的大小有要求，不能建立太大的表，所以这类数据库只适用在相对较小的数据库表

MEMORY存储引擎默认使用哈希（HASH）索引，其速度比B+Tree要快，如果希望使用B树型，可在创建表的时候使用。

优点：数据快速访问和处理。

缺点：一旦发生异常，重启或关闭机器，数据都会丢失。

场景

  • 如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。
  • 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，例如在MySQL中使用该引擎作为临时表，存放查询的中间结果。

4、ARCHIVE存储引擎

Archive（啊 海 五）是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。

在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。

Archive拥有很好的压缩机制，它使用zlib压缩库，拥有高效的插入速度。

优点：高压缩，快速插入。

缺点：不适合频繁查询。

场景：

  1. 非常适合存储大量独立的、作为历史记录的数据。Archive非常适合存储归档数据。例如：非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。
  2. 如果主要是INSERT操作，对数据的安全性要求较低，可以选择Archive，Archive支持高并发的插入操作。

好文参考：MySQL的存储引擎有哪些？以及它们的对比和使用场景_Duktig丶的博客-CSDN博客_mysql存储引擎适用场景

MySQL存储引擎InnoDB与Myisam的区别

  • MyISAM不支持事务，而InnoDB支持，并且事务安全。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。
  • InnoDB是聚集索引，MyISAM是非聚集索引。聚簇索引的文件放在主键索引的叶子节点上，因此InnoDB必须有主键，通过主键索引效率很高，但是辅助索引需要2次查询，先查到主键，然后通过主键查询到数据，因此主键不应该太大。MyISAM是非聚集索引是非聚集索引，数据文件是分离的，索引保存的事数据文件的指针，主键索引和辅助索引是独立的。
  • InnoDB 不保存整个表的行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
  • InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。MyISAM 的一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。

索引

什么是索引？

MySQL官方对索引的定义为：索引就是用于实现数据的快速检索，由数据表中的一列或多列组合而成，索引实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便快速查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

索引有哪些优缺点？

优点

  • 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

缺点

  • 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
  • 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

索引适合和不适合使用的场景

哪些情况需要创建索引

  • 值具有唯一性的列，比如说主键、用户名；
  • 频繁作为查询的条件的字段应该创建索引 where id=?;
  • 查询中与其他表关联的字段，外键关系建立索引
  • 经常要用于排序(order by)，分组(group by)的列，排序字段若通过索引去访问将大大提高排序的速度，因为索引已经排好序了

哪些情况不要创建索引

  • 数据更新性能比查询性能要求要高的情况下不要使用索引，因为数据的更新的同时索引也要进行维护和更新（加了索引查询快但更新就会慢）;不要盲目的给表建太多索引，因为索引本身的存储也要占用存储空间，一旦更新操作频繁反而降低新性能;
  • 不要给不经常使用的列建索引，不怎么查询还建索引干嘛;
  • 不要给高重复值的列建索引，索引本身就是为了提高查询速度，然而数据值高度重复，数据区别性不高，索引起不了效果（比如说：性别）;

索引有哪几种类型？

普通索引

普通索引是最基本的索引，它没有任何限制，值可以为空；仅加速查询。

CREATE INDEX index_name ON table(column(length))
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
DROP INDEX index_name ON table

唯一索引

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。简单来说：唯一索引是加速查询 + 列值唯一（可以有null）。

CREATE UNIQUE INDEX indexName ON table(column(length))
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。简单来说：主键索引是加速查询 + 列值唯一（不可以有null）+ 表中只有一个。主键索引一般建议使用数据表的自增唯一主键来作为主键索引使用。主键索引简称主键，原文是PRIMARY KEY，由一个或多个列组成，用于唯一性标识数据表中的某一条记录。一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL。主键索引属于唯一索引的一个特殊种类，一个表的某列创建了主键索引后会具备唯一索引的功能同时还会对该列生成主键约束,所以简单来说主键索引是一种带有主键约束的唯一索引；

特点

  • 数据库在创建主键同时会自动建立一个唯一索引。
  • 每个表最多只能创建一个主键索引；
  • 创建了主键索引的列不允许有重复的值，并且不能为null值；
  • 创建了主键索引的列可以作为外键;
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

组合索引

组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。

可以说：组合索引是多列值组成的一个索引，专门用于组合搜索，其效率大于索引合并。

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

全文索引

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。全文索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。全文索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全文索引的表中，然后再用CREATE index创建全文索引要比先为一张表建立全文索引然后再将数据写入的速度快很多。

CREATE TABLE `table` (`id` int(11) NOT NULL AUTO_INCREMENT ,`title` char(255) CHARACTER NOT NULL ,`content` text CHARACTER NULL ,`time` int(10) NULL DEFAULT NULL ,PRIMARY KEY (`id`),FULLTEXT (content));
ALTER TABLE article ADD FULLTEXT index_content(content)
CREATE FULLTEXT INDEX index_content ON article(content)

简单来说：全文索引是对文本的内容进行分词，进行搜索。

索引的数据结构

索引的数据结构主要有B树索引、Hash索引两种

B树/B+树索引

B-tree（B树）

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

  • d>=2，即B-Tree的度；
  • h为B-Tree的高；
  • 每个非叶子结点由n-1个key和n个指针组成，其中d<=n<=2d；
  • 每个叶子结点至少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶结点的指针均为NULL；
  • 所有叶结点都在同一层，深度等于树高h；
  • key和指针相互间隔，结点两端是指针；
  • 一个结点中的key从左至右非递减排列；
  • 如果某个指针在结点node最左边且不为null，则其指向结点的所有key小于v(key1)，其中v(key1)为node的第一个key的值。
  • 如果某个指针在结点node最右边且不为null，则其指向结点的所有key大于v(keym)，其中v(keym)为node的最后一个key的值。
  • 如果某个指针在结点node的左右相邻key分别是keyi和keyi+1且不为null，则其指向结点的所有key小于v(keyi+1)且大于v(keyi)

下图是一个d=2的B-Tree示意图。

由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。

BTree_Search(node, key) {if(node == null) return null;foreach(node.key){if(node.key[i] == key) return node.data[i];if(node.key[i] > key) return BTree_Search(point[i]->node);}return BTree_Search(point[i+1]->node);
}
data = BTree_Search(root, my_key);

B+tree

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

  • 每个结点的指针上限为2d而不是2d+1。
  • 内结点不存储data，只存储key；叶子结点不存储指针。

下图是一个简单的B+Tree示意。


由于并不是所有节点都具有相同的域，因此B+Tree中叶结点和内结点一般大小不同。这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个结点的域和上限是一致的，所以在实现中B-Tree往往对每个结点申请同等大小的空间。

一般来说，B+Tree比B-Tree更适合实现外存储索引结构

带有顺序访问指针的B+Tree

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。


如上图所示，在B+Tree的每个叶子结点增加一个指向相邻叶子结点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着结点和指针顺序遍历就可以一次性访问到所有数据结点，极大提到了区间查询效率。

MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎(MySQL数据库MyISAM和InnoDB存储引擎的比较)的索引实现方式。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶结点的data域存放的是数据记录的地址。

下面是MyISAM索引的原理图：


这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶结点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。


上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶结点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：


这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

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

哈希索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

对于hash相同的，采用链表的方式解决冲突。类似于hashmap。因为索引的结构是十分紧凑的，所以hash索引的查询很快。

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，这时疑问就来了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端

Hash 索引的弊端

  • Hash索引仅仅能够满足“=”，“IN”和“<=>”查询，不能使用范围查询。由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值得大小关系，并不能保证和Hash运算前完全一样。
  • Hash索引不能利用部分索引键查询。对于组合索引，Hash索引在计算Hash值的时候是组合键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。
  • Hash索引在任何时候都不能避免表扫描。Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的真实数据进行相应的比较，并得到相应的结果。也就是必须回表查询
  • Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

索引的基本原理

索引本质

  • 索引本质是一张"排序好的表，但不存储实际数据，而是附带实际数据的物理地址（数据的硬盘编号）"。
  • 索引查询的本质：通过对索引字段的字段值分区，可以快速缩减查找范围；通过索引底层采用B+树的数据结构存储每一个区，可以快速遍历查找；从而提高查询效率、检索效率。
  • 索引 = 实际数据的物理地址（采用java的思想，将右边的对象地址 赋给 左边变量）。

索引与数据表的关系

  • 数据表：是数据的集合或仓库，所有数据表的数据都存在于硬盘上，每一条数据在硬盘上都有唯一的编号作为数据标识。
  • 索引：则是数据表中字段的目录，每一个索引对象对应数据表的一个字段，而每一个索引值对应数据表字段的一个值（物理地址值），换种说法即是每一个索引"存储"一个字段数据在硬盘中的物理地址。
  • 通过查找索引，是查找索引"存储"的数据硬盘编号（数据物理地址），通过物理地址查找到真实存在于硬盘上的真实数据。

索引实现原理

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询

时遍历整张表。

索引的原理很简单，就是把无序的数据变成有序的查询

    1. 把创建了索引的列的内容进行排序
    2. 对排序结果生成倒排表
    3. 在倒排表内容上拼上数据地址链
    4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

索引算法有哪些？

BTree算法

BTree是最常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=，>=，>,<,<=和between这些比较操作符上，而且可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，例如：

--只要它的查询条件是一个不以通配符开头的常量

select * from user where name like ‘zhangsan%’；

--如果以通配符开头，或者没有使用常量，则不会使用索引

select * from user where name like '%zhangsan';

Hash算法

Hash索引只能用于对等比较，例如=，<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

创建索引的原则

索引虽好，但也不是无限制的使用，最好符合一下几个原则

  1. 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  2. 较频繁作为查询条件的字段才去创建索引
  3. 更新频繁字段不适合创建索引
  4. 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
  5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b) 的索引，那么只需要修改原来的索引即可。
  6. 定义有外键的数据列一定要建立索引。
  7. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
  8. 对于定义为text、image和bit的数据类型的列不要建立索引。
  9. 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
  10. 基数较小的列（数据量少），索引效果较差，没有必要在此列建立索引
  11. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
  12. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

创建索引的三种方式？怎么删除索引？

创建索引的三种方式？

第一种方式：在执行CREATE TABLE时创建索引

CREATE TABLE user_index(#建立主键索引并设置自增id INT auto_increment PRIMARY KEY, first_name VARCHAR(16),last_name VARCHAR(16),id_card VARCHAR(18),information text,#建立联合索引KEY index_name1 (first_name,last_name),#建立外键UNIQUE KEY index_name2 (id_card),#建立全文索引FULLTEXT KEY index_name3 (information)
)

CREATE TABLE 表名（索引类型 索引名（索引列））

第二种方式：使用ALTER TABLE 命令去增加索引（更新表时创建索引）

ALTER TABLE 表名 ADD 索引类型<索引名称> （索引列）

第三种方式：CREATE INDEX 命令创建（只能增加普通索引和UNIQUE索引）

#建立普通索引CREATE INDEX index_name ON table_name (column_list);
#建立外键
CREATE UNIQUE INDEX index_name  on table_name (id_card)

#建立普通索引

CREATE INDEX 索引名 ON 表名(字段/列);

#建立外键

CREATE UNIQUE INDEX 索引名 on表名(外键字段)

怎么删除索引？

根据索引名删除普通索引、唯一索引、全文索引

alter table table_name drop KEY index_name;

删除主键索引：alter table 表名 drop primary key index_name（因为主键只有一个）。

这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引），需要取消自增长再行删除：

alter table table_name
-- 重新定义字段
MODIFY id int,
drop PRIMARY KEY

但通常不会删除主键，因为设计主键一定与业务逻辑无关。

创建索引时需要注意什么？

  • 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
  • 避免在取值朝一个方向增长的字段（例如：日期类型的字段）上建立索引；对复合索引，避免将这种类型的字段放置在最前面。由于字段的取值总是朝一个方向增长，新记录总是存放在索引的最后一个叶页中，从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页的拆分。此外，如果所建索引是聚集索引，表中数据按照索引的排列顺序存放，所有的插入操作都集中在最后一个数据页上进行，从而引起插入“热点”。
  • 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用。因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。
  • 删除不再使用，或者很少被使用的索引。表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

使用索引查询一定能提高查询的性能吗？为什么？

使用索引查询一定能提高查询的性能吗？

通常，通过索引查询数据比全表扫描要快。但是使用索引查询不一定能提高查询性能

为什么？

因为索引需要额外的存储空间和处理，也需要定期维护，那些不必要的索引反而会使查询反应时间变慢， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，使用索引查询不一定能提高查询性能

索引就是为了提高查询性能而存在的,如果在查询中索引没有提高性能,只能说是用错了索引或者讲是场合不同

百万级别或以上的数据如何删除

由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）,然后删除其中无用数据（此过程需要不到两分钟）,删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

前缀索引

什么是前缀索引？

前缀索引也叫局部索引，类似这种给某列部分信息添加索引的方式叫做前缀索引，比如给身份证的前 10 位添加索引

为什么要用前缀索引？

有时需要在很长的字符列（如BLOB、TEXT或很长的VARCHAR类型的列）上创建索引，这会造成索引特别大且慢。 ,这个时候可以用前缀索引，前缀索引能有效减小索引文件的大小，节约索引空间，让每个索引页可以保存更多的索引值，从而提高索引效率

但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引。

什么情况下适合使用前缀索引？

当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；

前缀索引的使用？

ALTER TABLE 表名 ADD KEY(字段名(N));

N就是要用字段的前几位建立索引。

那么怎么来确认这个N是多少的呢？

先查询出来字段共有多少条数据

首先我们先查询一下字段共有多少条数据：

select count(字段名) from 表名;

这时候我们会得到一个数据，这个数据是这个字段所有数据的长度，然后我们将这个数据记录下来。记录下来之后将这个字段内的所有数据进行去重，去重函数为distinct，用我们刚才所取得的所有的数据数量除以我们去重过后得到的数据的数量，这个时候我们得到的就是我们这个字段的最大辨识度

CREATE TABLE `author` (`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',`name` VARCHAR(32) NOT NULL COMMENT '姓名',`gender` TINYINT(1) NOT NULL COMMENT '性别，0-男，1-女',`age` TINYINT(3) NOT NULL DEFAULT '0' COMMENT '年龄',`email` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '邮箱',`homepage` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '主页',`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;// email列创建前缀索引
CREATE INDEX idx_author_email ON author(email(3));// 插入5条数据
insert into `author` (`name`, `gender`, `age`, `email`) values('xx','0','20','xx@126.com');
insert into `author` (`name`, `gender`, `age`, `email`) values('yy','1','18','yy@126.com');
insert into `author` (`name`, `gender`, `age`, `email`) values('zz','0','25','zz@126.com');
insert into `author` (`name`, `gender`, `age`, `email`) values('xyz123','0','30','xyz123@126.com');
insert into `author` (`name`, `gender`, `age`, `email`) values('xyz123','0','120','xxx@163.com');

什么是最左前缀原则/最左前缀匹配原则/最左匹配原则？

当对多列创建索引后，并不是只要包含了创建索引的列就能使用索引，索引的使用要遵循最左前缀匹配原则。顾名思义就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

mysql会从左到右一直匹配直到遇到范围查询(>、<、between、like)就停止匹配

比如a = 1 and b = 2 and c > 3 and d = 4

  • 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为到c>3这里有个>号就会停止匹配了，所以即使你给d加了索引，也用不到这个索引
  • 如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整，简单来说就是=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

再例如对列(A, B, C)创建索引，那么对列(A, B, C)或者(A, C)或者(A, B)进行查询会匹配索引，对(C, A)或者(B, C)来说不能使用索引,也就是都包含了第一个索引A，整个联合索引才会起效

B树和B+树

二叉查找树

二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。
如下图所示就是一棵二叉查找树


对该二叉树的节点进行查找发现深度为1的节点的查找次数为1，深度为2的查找次数为2，深度为n的节点的查找次数为n，因此其平均查找次数为 (1+2+2+3+3+3) / 6 = 2.3次

二叉查找树可以任意地构造，同样是2,3,5,6,7,8这六个数字，也可以按照下图的方式来构造：


但是这棵二叉树的查询效率就低了。因此若想二叉树的查询效率尽可能高，需要这棵二叉树是平衡的，从而引出新的定义——平衡二叉树，或称AVL树。

平衡二叉树（AVL Tree）

平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。下面的两张图片，左边是AVL树，它的任何节点的两个子树的高度差<=1；右边的不是AVL树，其根节点的左子树高度为3，而右子树高度为1；

B树定义

定义：B-树是一类树，包括B-树、B+树、B*树等，是一棵自平衡的搜索树，它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。
B-树是专门为外部存储器设计的，如磁盘，它对于读取和写入大块数据有良好的性能，所以一般被用在文件系统及数据库中。

定义只需要知道B-树允许每个节点有更多的子节点即可（多叉树）。子节点数量一般在上千，具体数量依赖外部存储器的特性。

为什么会出现B-树这类数据结构？

传统用来搜索的平衡二叉树有很多，如 AVL 树，红黑树等。这些树在一般情况下查询性能非常好，但当数据非常大的时候它们就无能为力了。原因当数据量非常大时，内存不够用，大部分数据只能存放在磁盘上，只有需要的数据才加载到内存中。一般而言内存访问的时间约为 50 ns，而磁盘在 10 ms 左右。速度相差了近 5 个数量级，磁盘读取时间远远超过了数据在内存中比较的时间。这说明程序大部分时间会阻塞在磁盘 IO 上。那么我们如何提高程序性能？减少磁盘 IO 次数，像 AVL 树，红黑树这类平衡二叉树从设计上无法“迎合”磁盘。

上图是一颗简单的平衡二叉树，平衡二叉树是通过旋转来保持平衡的，而旋转是对整棵树的操作，若部分加载到内存中则无法完成旋转操作。其次平衡二叉树的高度相对较大为 log n（底数为2），这样逻辑上很近的节点实际可能非常远，无法很好的利用磁盘预读（局部性原理），所以这类平衡二叉树在数据库和文件系统上的选择就被 pass 了。

空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也会被访问。

我们从“迎合”磁盘的角度来看看B-树的设计。

索引的效率依赖与磁盘 IO 的次数，快速索引需要有效的减少磁盘 IO 次数，如何快速索引呢？索引的原理其实是不断的缩小查找范围，就如我们平时用字典查单词一样，先找首字母缩小范围，再第二个字母等等。平衡二叉树是每次将范围分割为两个区间。为了更快，B-树每次将范围分割为多个区间，区间越多，定位数据越快越精确。所以新建节点时，直接申请页大小的空间（磁盘存储单位是按 block 分的，一般为 512 Byte。磁盘 IO 一次读取若干个 block，我们称为一页，具体大小和操作系统有关，一般为 4 k，8 k或 16 k），计算机内存分配是按页对齐的，这样就实现了一个节点只需要一次 IO。

上图是一棵简化的B-树，多叉的好处非常明显，有效的降低了B-树的高度，为底数很大的 log n，底数大小与节点的子节点数目有关，一般一棵B-树的高度在 3 层左右。层数低，每个节点区确定的范围更精确，范围缩小的速度越快（比二叉树深层次的搜索肯定快很多）。上面说了一个节点需要进行一次 IO，那么总 IO 的次数就缩减为了 log n 次。B-树的每个节点是 n 个有序的序列(a1,a2,a3…an)，并将该节点的子节点分割成 n+1 个区间来进行索引(X1< a1, a2 < X2 < a3, … , an+1 < Xn < anXn+1 > an)。

点评：B树的每个节点，都是存多个值的，不像二叉树那样，一个节点就一个值，B树把每个节点都给了一点的范围区间，区间更多的情况下，搜索也就更快了，比如：有1-100个数，二叉树一次只能分两个范围（根节点是50），左子树范围是0-50和右子树范围是51-100，而B树（根节点是25、50、75、100），分成4个范围 1-25， 25-50，51-75，76-100一次就能筛选走四分之三的数据。所以作为多叉树的B树是更快的

平衡多路查找树（B-Tree，读B树不是B减树）

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

首先要知道系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：

mysql> show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵m阶的B-Tree有如下特性

  • 每个节点最多有m个孩子。
  • 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
  • 若根节点不是叶子节点，则至少有2个孩子
  • 所有叶子节点都在同一层，且不包含其它关键字信息
  • 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
  • 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
  • ki(i=1,…n)为关键字，且关键字升序排序。
  • Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程

  • 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
  • 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
  • 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
  • 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
  • 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】

在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

B+Tree（这里我读的B加树）

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上面的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同

  • 非叶子节点只存储键值信息。
  • 所有叶子节点之间都有一个链指针。
  • 数据记录都存放在叶子节点中。

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。

因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（聚簇索引）和辅助索引（非聚集索引/聚簇索引），如果B+Tree中的叶子节点存放的是整张表的行记录数据那就是聚集索引，如果叶子节点存储相应行数据的只包含主键值和索引字段那就是非聚集索引。当通过非聚集索引来查询数据时，InnoDB存储引擎会遍历非聚集索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

B树和B+树的区别

  1. B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 O(logn)。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。

如下所示B-树/B+树查询节点 key 为 50 的 data。

B-树


从上图可以看出，key 为 50 的节点就在第一层，B-树只需要一次磁盘 IO 即可完成查找。所以说B-树的查询最好时间复杂度是 O(1)。

B+树


由于B+树所有的 data 域都在根节点，所以查询 key 为 50的节点必须从根节点索引到叶节点，时间复杂度固定为 O(log n)。

点评：B树的由于每个节点都有key和data，所以查询的时候可能不需要O(logn)的复杂度，甚至最好的情况是O(1)就可以找到数据，而B+树由于只有叶子节点保存了data，所以必须经历O(logn)复杂度才能找到数据

  1. B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。

B+树

根据空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也会被访问。

B+树可以很好的利用局部性原理，若我们访问节点 key为 50，则 key 为 55、60、62 的节点将来也可能被访问，我们可以利用磁盘预读原理提前将这些数据读入内存，减少了磁盘 IO 的次数。
当然B+树也能够很好的完成范围查询。比如查询 key 值在 50-70 之间的节点。

点评：由于B+树的叶子节点的数据都是使用链表连接起来的，而且他们在磁盘里是顺序存储的，所以当读到某个值的时候，磁盘预读原理就会提前把这些数据都读进内存，使得范围查询和排序都很快

  1. B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确

这个很好理解，由于B-树节点内部每个 key 都带着 data 域，而B+树节点只存储 key 的副本，真实的 key 和 data 域都在叶子节点存储。前面说过磁盘是分 block 的，一次磁盘 IO 会读取若干个 block，具体和操作系统有关，那么由于磁盘 IO 数据大小是固定的，在一次 IO 中，单个元素越小，量就越大。这就意味着B+树单次磁盘 IO 的信息量大于B-树，从这点来看B+树相对B-树磁盘 IO 次数少。


从上图可以看出相同大小的区域，B-树仅有 2 个 key，而B+树有 3 个 key。

点评：由于B树的节点都存了key和data，而B+树只有叶子节点存data，非叶子节点都只是索引值，没有实际的数据，这就时B+树在一次IO里面，能读出的索引值更多。从而减少查询时候需要的IO次数！

  1. B-树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次

从这个图可以看出B+树的50关键字出现了多次

使用B+树的好处

优点

  • 单次请求涉及的磁盘IO次数少（出度d大，且非叶子节点不包含表数据，树的高度小）；
  • 查询效率稳定（任何关键字的查询必须走从根结点到叶子结点，查询路径长度相同）；
  • 遍历效率高（从符合条件的某个叶子节点开始遍历即可）；

在B+树中， 由于底层的各个叶子节点都通过指针组织成一个双向链表， 因此，只需要从跟节点到叶子节点定位到第一个满足条件的Key, 然后不断在叶子节点迭代next指针即可实现遍历，此时相当于顺序IO，结构如下图所示。

相反，如果通过每次从根节点查找进行遍历，相当于进行随机IO，效率低下，如下图所示：

　　

缺点

B+树最大的性能问题在于会产生大量的随机IO，主要存在以下两种情况：

  • 主键不是有序递增的，导致每次插入数据产生大量的数据迁移和空间碎片；
  • 即使主键是有序递增的，大量写请求的分布仍是随机的；

Hash索引和B+树有什么区别或者说优劣呢?

Hash索引和B+树索引的底层实现原理

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应 的键值，之后进行回表查询获得实际数据(回表查询是因为hash表中存储的是每行数据的地址)。B+树底层实现是多路平衡查找树。 对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

Hash索引和B+树有什么区别

  • hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持 一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
  • hash索引不支持使用索引进行排序，hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测，B+树都支持
  • hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
  • hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

数据库为什么使用B+树而不是B树

  • B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
  • B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘 上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；
  • B+树的查询效率更加稳定。B树的由于每个节点都有key和data，所以查询的时候可能不需要O(logn)的复杂度，甚至最好的情况是O(1)就可以找到数据，而B+树由于只有叶子节点保存了data，所以必须经历O(logn)复杂度才能找到数据（B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。）
  • B-树不支持范围查询，B+树支持范围查询。B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
  • 增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

B+树在满足非聚簇索引的时候需要回表查询数据吗？

在B+树的索引中，当查询使用聚簇（cu第四声）索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

数据库中的B+Tree索引可以分为聚集索引（聚簇索引）和辅助索引（非聚集索引/聚簇索引），如果B+Tree中的叶子节点存放的是整张表的行记录数据那就是聚集索引，如果叶子节点存储相应行数据的只包含主键值和索引字段那就是非聚集索引。当通过非聚集索引来查询数据时，InnoDB存储引擎会遍历非聚集索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

InnoDB中，每个表必须有一个聚簇索引，默认是根据主键建立的。如果表中没有主键，InnoDB会选择一个合适唯一的非空索引列作为聚簇索引，如果找不到合适的列，会使用一列隐藏的列DB_ROW_ID作为聚簇索引。

什么是聚簇索引和非聚簇索引？何时使用聚簇索引与非聚簇索引？

根据索引的存储方式来划分，索引可以分为聚簇索引和非聚簇索引。聚簇索引的特点是叶子节点包含了完整的记录行，而非聚簇索引的叶子节点只有索引字段和主键ID。

什么是聚簇（聚集）索引和非聚簇（聚集）索引？

聚簇索引

聚簇索引也叫聚集索引或主键索引，它实际上并不是一种单独的索引类型，而是一种数据存储方式，聚簇索引的叶子节点保存了一行记录的所有列信息。也就是说聚簇索引的叶子节点中包含了一个完整的记录行

特点

  • 一个表只能创建一个聚集索引;
  • 聚集索引尽量建在不会经常发生变动的列上，因为一旦列变动同时也会引索引结构变化，而索引结构中也包含者数据的变动；
  • 数据库在创建主键时如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立一个唯一的聚集索引

非聚簇索引

非聚簇索引也叫辅助索引或普通索引或二级索引，它的叶子节点只包含主键值和索引字段，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表

澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

例如一个包含了用户姓名和年龄的的数据表，假设主键是用户ID，聚簇索引的结构为（橙色的代表id，绿色是指向子节点的指针）：

叶子节点中，为了突出记录，把(id, name, age)区分开来了，实际上是连在一起的，它们是构成一条记录的整体。

而一个非聚簇索引（以age为索引）的结构是：

它的叶子节点中，不包含整个记录的完整信息，除了索引字段age本身以外，只包含当前记录的主键id。如果想要获取整行记录数据还需要再通过id号到聚簇索引中回表查询。

InnoDB中，每个表必须有一个聚簇索引，默认是根据主键建立的。如果表中没有主键，InnoDB会选择一个合适唯一的非空索引列作为聚簇索引，如果找不到合适的列，会使用一列隐藏的列DB_ROW_ID作为聚簇索引。

何时使用聚簇索引与非聚簇索引？

使用聚簇索引的情况

  • 列经常被分组排序
  • 返回某范围内的数据
  • 小数目的不同值
  • 外键列
  • 主键列

非聚簇索引使用情况

  • 大数目的不同值
  • 频繁更新的列

覆盖索引

非聚簇索引中因为不含有完整的数据信息，查找完整的数据记录需要回表，所以一次查询操作实际上要做两次索引查询。而如果所有的索引查询都要经过两次才能查到，那么肯定会引起效率下降，毕竟能少查一次就少查一次。

以上面的age索引为例，它是一个非聚簇索引，如果我想通过年龄查询用户的id，执行了下面一条语句：

select id from userinfo where age=10;

这种情况是否还有必要去回表？因为我只需要id的值，通过age这个索引就已经能拿到id了，如果还去回表一次不就做了无用的操作了吗？实际上确实是不需要的。索引查询中，如果辅助索引已经能够得到查询的所有信息了，就无需再回表，这个就是覆盖索引。

非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。其实就是如果是覆盖索引就不会回表了

以上面的age索引为例，它是一个非聚簇索引，如果我想通过年龄查询用户的id，执行了下面一条语句：

select id from userinfo where age=10;

这种情况是否还有必要去回表？因为我只需要id的值，通过age这个索引就已经能拿到id了，如果还去回表一次不就做了无用的操作了吗？实际上确实是不需要的。索引查询中，如果辅助索引已经能够得到查询的所有信息了，就无需再回表，这个就是覆盖索引。这个时候非聚簇索引就不会回表了

联合索引是什么？为什么需要注意联合索引中的顺序？

联合索引是什么？

联合索引（也叫组合索引）指的是同时对多列创建的索引，创建联合索引后，叶子节点会同时包含所有索引列的值和主键id，并且同时根据多列排序，在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

例如一个包含了用户姓名和年龄的的数据表，假设主键是用户ID，聚簇索引的结构为（橙色的代表id，绿色是指向子节点的指针）：

例如对同时对上面的姓名和年龄创建的索引结构：

每个叶子节点同时保存了所有的索引列，除此之外，还是只包含了主键id。

最左前缀匹配原则

当对多列创建索引后，并不是只要包含了创建索引的列就能使用索引，索引的使用要遵循最左前缀匹配原则。

假设对列(A, B, C)创建索引，那么只有以下场景能使用索引：

  • 对列(A, B, C)或者(A, C)或者(A, B)进行查询会匹配索引，对(C, A)或者(B, C)来说不能使用索引,也就是都包含了第一个索引A，整个联合索引才会起效
  • 通配符只能使用LIKE 'val%'形式，不能使用LIKE '%VAL%'，后者会导致全表扫描。
  • 索引列不能进行运算，例如WHERE A + 1 = 5这种场景会导致索引失效。
  • 索引列不能包含范围值查询，如LIKE/BETWEEN/>/<等都会导致后面的列无法匹配索引。
  • 索引列不能包含有NULL值。

为什么需要注意联合索引中的顺序？

MySQL使用联合索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可 以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

索引下推

新版本的MySQL（5.6以上）中引入了索引下推的机制：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

例如针对上面表中的(name, age)做联合索引，正常情况下的查询逻辑：

  • 通过name找到对应的主键ID
  • 根据id记录的列匹配age条件

这种做法会导致很多不必要的回表，例如表中存在(张三, 10)和(张三, 15)两条记录，此刻要查询(张三, 20)的记录。查询时先通过张三定位到所有符合条件的主键ID，然后在聚簇索引中遍历满足条件的行，看是否有符合age = 20的记录。实际情况是没有满足条件的记录的，这个回表过程也相当于是在做无用之功。

索引下推的主要功能就是改善这一点，在联合索引中，先通过姓名和年龄过滤掉不用回表的记录，然后再回表查询索引，减少回表次数。

加了索引一定会走索引吗？索引失效的情况有哪些？

不一定，因为索引可能会失效

1、列与列对比

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

select * from test where id=c_id;

这种情况会被认为还不如走全表扫描。

2、存在NULL值条件

我们在设计数据库表时，应该尽力避免NULL值出现，如果非要不可避免的要出现NULL值，也要给一个DEFAULT值，数值型可以给0、-1之类的， 字符串有时候给空串有问题，就给一个空格或其他。

如果索引列是可空的（注意是可为空！不是为空！），那索引失效，索引值是少于表的count(*)值的，所以这种情况下，执行计划自然就去扫描全表了。

3、NOT条件

我们知道建立索引时，给每一个索引列建立一个条目，如果查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。反过来当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描。说白了就是查询条件中如果有<>、NOT、in、not exists的话那么索引就会失效，例如下面的id如果建立了索引，那索引就可能失效

select * from test where id<>500;select * from test where id in (1,2,3,4,5);select * from test where id 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'); --INDEX RANGE SCAN

这样的函数还有：to_char、to_date、to_number、trunc等

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

select * from sunyang where id/2=:type_id;

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

select * from sunyang where id=:type_id*2;

就可以使用索引了。

6、数据类型的转换

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

select * from sunyang where id='123'; //'123'是字符串

如果我想要强制走某个索引，能实现吗？

使用 关键字 force表示强制走括号中的索引key

select * from table_name force index (index_name) where conditions=2;

事务

什么是数据库事务？

数据库的事务（Transaction）包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。
在数据库系统上执行并发操作时，事务是作为最小的控制单元来使用的，特别适用于多用户同时操作的数据库系统。例如，航空公司的订票系统、银行、保险公司以及证券交易系统等。

事务最经典例子就是转账了

假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。应该是出错误了就回滚到之前的状态，那小明就应该是没有减少余额，事务就是保证这两个关键操作要么都成功，要么都要失败。

事物的四大特性(ACID)介绍一下?

关系性数据库需要遵循ACID规则，具体内容如下：

  • 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  • 一致性： 执行事务前后，数据完整性保持一致
  • 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  • 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

MySQL（InnoDB）事务隔离级别有哪些？

  • Read Uncommitted（未提交读） >> 在该隔离级别，事务可以读取到其它事务未提交的数据。本隔离级别很少用于实际应用。可能导致脏读（读取未提交的数据，也被称之为脏读）、不可重复读和幻读
  • Read Committed（提交读） >> 也叫不可重复读，事务所做的修改只有在提交过后才能对其它事务可见。也是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。这种隔离级别可能出现不可重复读和幻读。（事务A读取了两次数据资源，在这两次读取的过程中事务B修改了数据，导致事务A在这两次读取出来的数据不一致。这种在同一个事务中，前后两次读取的数据不一致的现象就是不可重复读）
  • Repeatable Read（可重读） >>是MySQL的默认事务隔离级别，一个事务中连续执行2次或多次相同的查询，查询的结果集总是一致的。可能会出现幻读，InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control 间隙锁）机制解决了该问题。注：其实多版本只是解决不可重复读问题，而加上间隙锁（也就是它这里所谓的并发控制）才解决了幻读问题。（幻读是指一个事务中连续执行相同的查询，得到的结果集可能不一致（其他事务对数据进行了操作），对比两次的结果集，数据就好像是凭空出现亦或凭空消失）
  • Serializable（可串行化） >> 这是最高的隔离级别，各种问题（脏读、不可重复读、幻读）都不会发生，通过加锁实现（读锁和写锁）。

好文参考：彻底搞懂 MySQL 事务的隔离级别-阿里云开发者社区

什么是脏读？幻读？不可重复读？

1、脏读

读取到另一个事务未提交的数据可能会导致脏读(Dirty Read)，脏读也就是读取了错误的数据，不准确的数据来使用

常见的脏读情况如下

  • 在事务A执行过程中，事务A对数据资源进行了修改，事务B读取了事务A修改后的数据。由于某些原因，事务A并没有完成提交，发生了RollBack操作，则事务B读取的数据就是脏数据。

2、不可重复读

前后两次读取的数据的值不一致的现象就是不可重复读，例如事务A读取了两次数据资源，在这两次读取的过程中事务B修改了数据，导致事务A在这两次读取出来的数据不一致。

3、幻读

简单说就是同样的条件, 第1次和第2次读出来的记录数不一样，例如假设下面的事务A第一次读10条数据，然后其中没有骚戴这条数据，但是事务B在事务执行的时候插入了一条“骚戴”的数据，事务A同样的查询条件再去读的时候发现读出11条数据，其中有“骚戴”这条数据，这就是幻读，这种情况的出现必须要多个事务并发执行才会发生

例子2

时间点

事务A

事务B

1

开启事务

2

开启事务

3

查询数据“骚戴”，不存在

4

插入数据“骚戴”，插入成功

5

提交事务

6

插入数据“骚戴”，插入失败

7

查询数据“骚戴”，查询成功

8

提交事务

我的理解：幻读就是事务A查询某条数据的时候不存在，然后在准备添加这条数据的之前又有一个事务B插入了这条数据并提交了事务，所以当事务A添加这条数据添加不成功，因为数据库中已经有了，这就导致事务A查询也查询不到这条数据，添加也添加不了这条数据，最后事务A又查询了一次这条数据，惊奇的发现这条数据最后又的确出现在数据库里，从事务A的角度来看就像出现了幻觉，莫名其妙多了一条数据

不可重复读和幻读的区别

  • 不可重复读的重点是修改；同样的条件，第1次和第2次读取的值不一样。幻读的重点在于新增或者删除；同样的条件， 第1次和第2次读出来的记录数不一样。
  • 从控制角度来看，不可重复读只需要锁住满足条件的记录，幻读要锁住满足条件及其相近的记录。

锁

隔离级别与锁的关系

读未提交

事务读，不阻塞其他事务读和写，事务写，阻塞其他事务写但不阻塞读。

可以通过写操作加“持续-X锁”实现。

读已提交

事务读，不会阻塞其他事务读和写，事务写，会阻塞其他事务读和写。

可以通过写操作加“持续-X”锁，读操作加“临时-S锁”实现。

可重复读

事务读，会阻塞其他事务事务写但不阻塞读，事务写，会阻塞其他事务读和写。

可以通过写操作加“持续-X”锁，读操作加“持续-S锁”实现。

串行化

“行级锁”做不到（X锁和S锁是行级锁），需使用“表级锁”。

区分事务隔离级别是为了解决脏读、不可重复读和幻读三个问题的。

从锁的类别上分MySQL都有哪些锁呢？

按锁的类别来分，锁可以分为共享锁和排它锁，共享锁和排它锁都只是概念，不是具体的锁，共享锁和排他锁在MySQL中具体的实现就是读锁和写锁：

1、读锁（共享锁）

Shared Locks（S锁），针对同一份数据，多个读操作可以同时进行而不会互相影响，但是不能写。

2、写锁（排它锁）

Exclusive Locks（X锁），当前写操作没有完成前，它会阻断其他写锁和读锁

3、IS锁

意向共享锁、Intention Shared Lock。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。

4、IX锁

意向排他锁、Intention Exclusive Lock。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

IS、IX锁是表级锁，它们的提出仅仅为了快速判断表中的记录是否被上行锁，如果没有这个意向锁那就只能用遍历的方式来查看表中有没有上锁的记录。IS锁和IX锁就避免了判断表中行有没有加锁时对每一行的遍历。直接查看表有没有意向锁就可以知道表中有没有行锁。

注意：如果一个表中有多个行锁，他们都会给表加上意向锁，意向锁和意向锁之间是不会冲突的。

锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁。他们的加锁开销从大到小，并发能力也是从大到小。

MySQL中InnoDB引擎的行锁是怎么实现的？

答：InnoDB是基于索引来完成行锁

例 : select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

InnoDB存储引擎的行锁的算法？

InnoDB存储引擎有3种行锁的算法，其分别是

  • Record Lock：锁定单个行记录
  • Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
  • Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身

Record Lock总是会锁住索引记录，如果InnoDB存储引擎建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，innodb对于行的查询都是采用Next-Key Lock算法。

深入了解：MySQL技术内幕 InnoDB存储引擎：行锁的3种算法_衣舞晨风的博客-CSDN博客

什么是死锁？怎么解决？怎么降低死锁？

什么是死锁？

死锁是指两个或者两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。

虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

怎么解决？

解决死锁最有用最简单的方法是不要有等待，将任何等待都转化为回滚，并且事务重新开始。但是有可能影响并发性能。

1：超时回滚，即当两个事务互相等待时，当一个等待时间超过设置的某一阀值的时候，其中一个事务进行回滚，另一个事务就能继续进行。在InnoDB引擎中，参数innodb_lock_wait_time用来设置超时的时间。

2：wait-for-graph方法：跟超时回滚比起来，这是一种更加主动的死锁检测方式。InnoDB引擎也采用这种方式。这种方式一般要求数据库保存一下两种信息：锁的信息链表和事务等待链表。

通过这两条链表可以构造出一张图，而在这个图中若存在回路，就代表存在死锁，因此资源间互相发生等待。

在wait-for graph中，事务为图中的节点。而在图中，事务T1指向T2边的定义为：

  • 事务T1等待事务T2所占用的资源
  • 事务T1最终等待T2所占用的资源，也就是事务之间在等待相同的资源，而事务T1发送在事务T2的后面。

从wait-for graph图中我们可以发现存在回路（t1,t2），因此存在死锁。

怎么降低死锁？

下列方法有助于最大限度地降低死锁：

（1）按同一顺序访问对象。

（2）避免事务中的用户交互。

（3）保持事务简短并在一个批处理中。

（4）使用低隔离级别。

按同一顺序访问对象

如果所有并发事务按同一顺序访问对象，则发生死锁的可能性会降低。例如，如果两个并发事务获得 Supplier 表上的锁，然后获得 Part 表上的锁，则在其中一个事务完成之前，另一个事务被阻塞在 Supplier 表上。第一个事务提交或回滚后，第二个事务继续进行。不发生死锁。将存储过程用于所有的数据修改可以标准化访问对象的顺序。

避免事务中的用户交互

避免编写包含用户交互的事务，因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度，例如答复应用程序请求参数的提示。例如，如果事务正在等待用户输入，而用户去吃午餐了或者甚至回家过周末了，则用户将此事务挂起使之不能完成。这样将降低系统的吞吐量，因为事务持有的任何锁只有在事务提交或回滚时才会释放。即使不出现死锁的情况，访问同一资源的其它事务也会被阻塞，等待该事务完成。

保持事务简短并在一个批处理中

在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长，其持有排它锁或更新锁的时间也就越长，从而堵塞了其它活动并可能导致死锁。

保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并释放锁。

使用低隔离级别

确定事务是否能在更低的隔离级别上运行。执行提交读允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。使用较低的隔离级别（例如提交读）而不使用较高的隔离级别（例如可串行读）可以缩短持有共享锁的时间，从而降低了锁定争夺。

mysql里面的乐观锁和悲观锁熟悉吗？

1、乐观锁

顾名思义，就是对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测。

常规的方式，都是在数据行上加一个版本号或者时间戳等字段

乐观锁的实现原理：

  1. 事务A在读取数据时，将对应的版本号字段读取出来，假设此时的版本号是1。
  2. 事务B同时也执行的读取操作（注意这个时候事务A和事务B读取的版本号都是1）。当事务A执行完操作后提交事务时，对版本号执行+1（也也就是修改版本号），此时该数据行的版本号就是2。
  3. 事务B执行修改操作时，默认增加一个版本号作为where条件（这个版本号还是1，但实际上事务A提交事务后版本号已经变成了2）。此时修改语句中的版本号字段是不满足where条件，该事务执行失败。通过这种方式来达到锁的功能。

乐观锁是基于程序实现的，所以不存在死锁的情况，适用于读多的应用场景。如果经常发生冲突，上层应用不断的让用户进行重新操作，这反而降低了性能，这种情况下悲观锁就比较适用。

2、悲观锁

顾名思义，就是对于数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，别人会修改数据。所以在整个数据处理过程中，需要将数据锁定。

悲观锁的实现，通常依靠数据库提供的锁机制实现，比如mysql的排他锁，select .... for update来实现悲观锁。

注：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。FOR UPDATE必须在事务中才有效，查询和更新必须在同一个事务中！！！

我们可以使用命令设置MySQL为非autocommit模式：

set autocommit=0;

设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

//0.开始事务begin;/begin work;/start transaction; (三者选一就可以)//1.查询出商品信息select status from t_goods where id=1 for update;//2.根据商品信息生成订单insert into t_orders (id,goods_id) values (null,1);//3.修改商品status为2update t_goods set status=2;//4.提交事务commit;/commit work;

注：上面的begin/commit为事务的开始和结束，因为在前一步我们关闭了mysql的autocommit，所以需要手动控制事务的提交。

上面的第一步我们执行了一次查询操作：select status from t_goods where id=1 for update;

与普通查询不一样的是，我们使用了select…for update的方式，这样就通过数据库实现了悲观锁。此时在t_goods表中，id为1的 那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

注：需要注意的是，在事务中，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行，一般SELECT ... 则不受此影响。

拿上面的实例来说，当我执行select status from t_goods where id=1 for update;后。我在另外的事务中如果再次执行select status from t_goods where id=1 for update;则第二个事务会一直等待第一个事务的提交，此时第二个查询处于阻塞的状态，但是如果我是在第二个事务中执行select status from t_goods where id=1;则能正常查询出数据，不会受第一个事务的影响。

3、总结

读取频繁使用乐观锁，写入频繁使用悲观锁。

乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适,之所以用悲观锁就是因为两个用户更新同一条数据的概率高，也就是冲突比较严重的情况下，所以才用悲观锁。

悲观锁比较适合强一致性的场景，但效率比较低，特别是读的并发低。乐观锁则适用于读多写少，并发冲突多的场景。

mysql有哪些锁？

i、按照锁粒度来分

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要锁机制来保证访问的次序

MyISAM和MEMORY采用表级锁(table-level locking)

BDB采用页面锁(page-level locking)或表级锁，默认为页面锁

InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

1、行级锁

(1) 描述

行级锁是mysql中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁

(2) 特点

开销大，加锁慢，会出现死锁。发生锁冲突的概率最低，并发度也最高。

(3) InnoDB有三种行锁的算法

  • Record Lock（记录锁）：单个行记录上的锁。这个也是我们日常认为的行锁。
  • Gap Lock（间隙锁）：间隙锁，锁定一个范围，但不包括记录本身（只不过它的锁粒度比记录锁的锁整行更大一些，他是锁住了某个范围内的多个行【这里主要指的是范围，不是记录本身】，包括根本不存在的数据）。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。该锁只会在隔离级别是RR或者以上的级别内存在。间隙锁的目的是为了让其他事务无法在间隙中新增数据。
  • Next-Key Lock（临键锁）：它是记录锁和间隙锁的结合，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。next-key锁是InnoDB默认的锁

上面这三种锁都是排它锁（X锁）

2、表级锁

(1) 描述

表级锁是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分mysql引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

(2) 特点

开销小，加锁快，不会出现死锁。发生锁冲突的概率最高，并发度也最低。

(3)两种表级锁

LOCK TABLE my_table_name READ; 用读锁锁表，会阻塞其他事务修改表数据。

LOCK TABLE my_table_name WRITE; 用写锁锁表，会阻塞其他事务读和写。

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

但是在InnoDB中如果需要表锁就需要显式地声明了。

3 、页级锁

(1) 描述

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录。BDB 支持页级锁。

(2) 特点

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

ii、按照锁的共享策略（锁的类别）来分

共享锁和排他锁是概念，在MySQL中具体的实现就是读锁和写锁：

1、读锁（共享锁）

Shared Locks（S锁），针对同一份数据，多个读操作可以同时进行而不会互相影响，但是不能写。

2、写锁（排它锁）

Exclusive Locks（X锁），当前写操作没有完成前，它会阻断其他写锁和读锁

3、IS锁

意向共享锁、Intention Shared Lock。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。

4、IX锁

意向排他锁、Intention Exclusive Lock。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

为什么要IS和IX锁？

IS、IX锁是表级锁，它们的提出仅仅为了快速判断表中的记录是否被上行锁，如果没有这个意向锁那就只能用遍历的方式来查看表中有没有上锁的记录。IS锁和IX锁就避免了判断表中行有没有加锁时对每一行的遍历。直接查看表有没有意向锁就可以知道表中有没有行锁。

注意：如果一个表中有多个行锁，他们都会给表加上意向锁，意向锁和意向锁之间是不会冲突的。

iii、从加锁策略上分

1、悲观锁

总是假设最坏的情况，悲观锁认为对于同一个数据的并发操作，一定是会发生修改的（或者增删改多，查少），哪怕没有修改，也会认为修改。因此对于同一个数据的并发操作，悲观锁采取加锁的形式，悲观的认为，不加锁的并发操作一定会出问题。

2、乐观锁

乐观锁则认为对于同一个数据的并发操作，是不会发生修改的（或者增删改少，查多）。在更新数据的时候，会采用不断尝试更新的方式来修改数据。也就是先不管资源有没有被别的线程占用，直接取申请操作，如果没有产生冲突，那就操作成功，如果产生冲突，有其他线程已经在使用了，那么就不断地轮询。乐观的认为，不加锁的并发操作是没有事情的。就是通过记录一个数据历史记录的多个版本，如果修改完之后发现有冲突再将版本返回到没修改的样子，乐观锁就是不加锁。好处就是减少上下文切换，坏处是浪费CPU时间。

iv、自增锁（AUTO-INC锁）

自增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。防止并发插入数据的时候自增id出现异常。

当一张表的某个字段是自增列时，innodb存储引擎会在该索引的末位加一个排它锁。为了访问这个自增的数值，需要加一个表级锁，不过这个表级锁的持续时间只有当前sql，而不是整个事务，即当前sql执行完，该表级锁就释放了。其他session无法在这个表级锁持有时插入任何记录。

好文参考：【MySQL】MySQL有几种锁_小七mod的博客-CSDN博客_mysql有哪几种锁

select for update是表锁还是行锁？

  • 如果查询条件用了索引/主键，那么select … for update就会进行行锁。
  • 如果查询条件是普通字段(没有索引/主键)，那么select … for update就会进行锁表。
  • 好文参考：select......for update会锁表还是锁行。_油锅里的猪的博客-CSDN博客
  • 注意上面的文章里有个没有提交的案例，这跟提不提交没有关系

视图

什么是视图？

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。视图的数据来源于定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。所以视图是在使用视图时动态生成的
视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。这样做既使应用简单化，也保证了系统的安全。

为什么要使用视图/优缺点？

1) 定制用户数据，聚焦特定的数据

在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。
例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。

2) 简化数据操作

在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。

3) 提高数据的安全性

视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

4) 共享所需数据

通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。

5) 更改数据格式

通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。

6) 重用 SQL 语句

视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

视图的缺点？

  • 性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。
  • 修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图， 可能是不可修改的

视图有哪些特点（视图和数据表的区别）？

  • 视图不是数据库中真实的表，而是一张虚拟表，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。
  • 视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。
  • 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。
  • 视图的建立和删除只影响视图本身，不影响对应的基本表
  • 总结:视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。

视图的用途和使用场景有哪些？

视图的用途？

视图根本用途在我看来就一个：简化sql查询，提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。

视图的使用场景？

1）计算列的需要

数据库设计范式要求我们减少冗余字段，因此现在很多数据表都没有计算列字段，如采购单：有价格、数量、税率、含税金额，多半没有不含税金额、税额，而这些字段在很多报表中有都会用到，所以我们可以创建一个含有计算列字段的视图来解决这个问题。

2）不同表字段聚合和信息重组

如：经销商通常有业务员，业务员通常有上下级关系（客户经理、区域经理、大区经理等），因此查看经销商业务员时我们需要看到直管业务员是谁？该业务员对应的区域经理、大区经理是谁（可能NULL）？因此我们可以联合经销商表、业务员信息表、业务员上下级关系表定义一个经销商视图。

3）安全性需要

主要是早期遗留系统集成需要，如：需要xx系统数据，又没接口，怎么办？直接操作数据库，这时就涉及数据安全性，合理利用视图则可以减少很多授权工作和保证数据安全性。当下新构建的系统几乎都是暴露api接口，因此数据安全性更多关注在接口的身份认证和数据粒度方面。

4）兼容老的数据表

曾经碰到过这么个问题。 公司自主研发的进销存管理系统，一开始自用的，后来合作伙伴企业也要用，所以打算Saas化，当初做了一个最大的变动就是几乎所有表增加了一个使用单位(co_id)字段。悲催的是另外一个外购系统用到了采购单表，该系统已经停止维护、又继续在用。于是就创建了一个co_id=自有公司的视图，命名为原采购单表，而原采购单表改成新表名。这样一来外购系统可以继续使用，而且数据也没问题。

能否向Mysql视图中插入/更新/删除数据？

要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSET、UPDATE或DELETE等语句当中使用它们。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。
如果视图包含下述结构中的任何一种，那么它就是不可更新的
（1）聚合函数；
（2）DISTINCT关键字；
（3）GROUP BY子句；
（4）ORDER BY子句；
（5）HAVING子句；
（6）UNION运算符；
（7）位于选择列表中的子查询；
（8）FROM子句中包含多个表；
（9）SELECT语句中引用了不可更新视图；
（10）WHERE子句中的子查询，引用FROM子句中的表；
（11）ALGORITHM 选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）。

常用SQL语句

SQL语句主要分为哪几类

结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。

作用：用于存取数据、查询、更新和管理关系数据库系统。

如大家所知 sql语句被分为四大类

  • 数据定义语言DDL
  • 数据查询语言DQL
  • 数据操纵语言DML
  • 数据控制功能DCL

数据定义语言DDL（Data Definition Language）

通过CREATE,DROP,ALTER这些命令对逻辑结构等有操作的，其中包括表结构，视图和索引。

数据查询语言DQL（Data Query Language）

SELECT

这个较为好理解 即查询操作，以select关键字。各种简单查询，连接查询等都属于DQL。

数据操纵语言DML（Data Manipulation Language）

通过INSERT,UPDATE,DELETE这些命令对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种 被划分到DQL中。

数据控制功能DCL（Data Control Language）

通过GRANT,REVOKE,COMMIT,ROLLBACK这些命令对数据库安全性、完整性等操作，可以简单的理解为权限控制等。

超键、候选键、主键、外键分别是什么？

超键

在关系中能唯一标识元组的属性或属性集称为这个关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键

候选键是最小超键，即没有冗余元素的超键

主键

数据库表中对储存数据对象予以唯一和完整标识的属性或属性的组合。一个数据列只能有一个主键，且主键不能为空值（Null）。

  • 骚戴理解：简单说，能够唯一标识元组的属性或属性集都是超键，可能会有多个超键，都可以唯一标识元组，然后这里面根据需求找一个作为主键，那么这里面其他的超键没被选中作主键的就都是候选键，也就是主键只有一个，候选键有多个

外键

在一个表中存在的另一个表的主键称此表的外键。

SQL 约束有哪几种？

SQL约束（Constraints）主要用于规定表中的数据规则，如果存在违反约束的数据行为，行为被约束终止。约束可以在创建表时规定（通过CREATE TABLE语句），或者在表创建之后规定（通过ALTER TABLE语句）。

在SQL中，有6种约束

NOT NULL

指示某列不能存储NULL值

UNIQUE

保证某列的每行必须具有唯一的值，可以空，但只能有一个

PRIMARY KEY

唯一并且非空，NOT NULL和UNIQUE的结合

FOREIGN KRY

保证一个表中的数据匹配另一个表中的值的参照完整性。

CHECK

保证列中的值符合指定的条件

DEFAULT

规定没有给列赋值时的默认值。

在默认的情况下，表的列接受 NULL 值。但是，NOT NULL 约束强制列不接受 NULL 值 ，并且强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

其次，UNIQUE 约束唯一标识数据库表中的每条记录。它和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证，并且PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束，但是我们要注意的是，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

#一共有五种约束:这里以学生表stuinfo为例
#1、添加主键约束
alter table stuinfo add costraint pk_stuno primary key (stuno)
#2、唯一约束
alter table stuinfo add constraint uq_stuid unique (stuid)
#3、添加默认约束
alter table stuinfo add constraint df_address default ('地址不详') for address
#4、添加检查约束
alter table stuinfo add constraint ck_age check (age between 15 and 40) 
#这时年龄在15到40之间
#5、添加外键约束
alter table stumarks add constraint fk_stuno foreign key (stuno) references stuinfo (stuno)
#这是一个成绩表(stumarks)引用学生信息表中的列-学号

五种关联查询

有5种关联查询

  1. 交叉连接（CROSS JOIN）;
  2. 内连接（INNER JOIN）;
  3. 外连接（LEFT JOIN/RIGHT JOIN）;
  4. 联合查询（UNION 与 UNION ALL）；
  5. 嵌套查询

交叉连接（CROSS JOIN）

SELECT * FROM A, B(, C) 或者
SELECT * FROM A CROSS JOIN B (CROSS JOIN C);

注：没有任何关联条件，结果是 笛卡尔积，结果集 会很大，没有意义，很少使用。

内连接（INNER JOIN）

INNER JOIN 是 SQL 中最重要、最常用的表连接形式，只有当连接的两个或者多个表中都存在满足条件的记录时，才返回行。
SQL INNER JOIN 子句将 table1 和 table2 中的每一条记录进行比较，以找到满足条件的所有记录，然后将每一对满足条件的记录的字段值，合并为一条新的结果行。
INNER JOIN 是默认的表连接方式。当不加任何修饰性的关键字，只写 JOIN 时，默认就是 INNER JOIN 连接。

内连接分类

  • 等值连接：ON A.id = B.id；
  • 不等值连接：ON A.id > B.id;

等值连接演示

现在有如下所示的两个表，分别是客户表和订单表。

现在，让我们使用 INNER JOIN 连接这两个表，如下所示：

不等值连接和等值连接差不多

外连接

外链接又分为左外链接（LEFT JOIN）和右外链接（RIGHT JOIN）

1)左外连接

LEFT JOIN 和 RIGHT JOIN 是相对的，LEFT JOIN 将返回左表（table1）中的所有记录，即使右表（table2）中没有匹配的记录也是如此。当右表中没有匹配的记录时，LEFT JOIN 仍然返回一行，只是该行的左表字段有值，而右表字段以 NULL 填充。
LEFT JOIN 以左表为主，即左表中的所有记录都会被返回，具体分为以下三种情况：

  • 如果 table1 中的某条记录在 table2 中刚好只有一条记录可以匹配，那么在返回的结果中会生成一个新的行。
  • 如果 table1 中的某条记录在 table2 中有 N 条记录可以匹配，那么在返回结果中也会生成 N 个新的行，这些行所包含的 table1 的字段值是重复的。
  • 如果 table1 中的某条记录在 table2 中没有匹配的记录，那么在返回结果中仍然会生成一个新的行，只是该行所包含的 table2 的字段值都是 NULL。

简单说：LEFT JOIN以左表为主，先查询出左表，按照 ON 后的关联条件匹配右表，没有匹配到的用 NULL 填充

其实就是如果左表的id在右表中有，那就把右边的对应的id整合到左表中，如果没有，那就设置为null

2)右外连接

RIGHT JOIN 和 LEFT JOIN 是相对的，RIGHT JOIN 将返回右表（table2）中的所有记录，即使左表（table1）中没有匹配的记录也是如此。当左表中没有匹配的记录时，RIGHT JOIN 仍然返回一行，只是该行的右表字段有值，而左表字段以 NULL 填充。、
RIGHT JOIN 以右表为主，即右表中的所有记录都会被返回，具体分为以下三种情况：

  • 如果 table2 中的某条记录在 table1 中刚好只有一条记录可以匹配，那么在返回的结果中会生成一个新的行。
  • 如果 table2 中的某条记录在 table1 中有 N 条记录可以匹配，那么在返回的结果中也会生成 N 个新的行，这些行所包含的 table2 的字段值是重复的。
  • 如果 table2 中的某条记录在 table1 中没有匹配记录，那么在返回结果中仍然会生成一个新的行，只是该行所包含的 table1 的字段值都是 NULL

简单说：RIGHT JOIN以右表为主，先查询出右表，按照 ON 后的关联条件匹配左表，没有匹配到的用 NULL 填充

什么时候用内连接什么时候用外连接？

  • 需要查找两张表同时存在的数据（两个表匹配的记录都选取出来），使用内连接
  • 需要查找两张表中一张表存在数据，另一张表不存在数据的时候使用左外连接或右外连接 ，是左还是右取决于那个表是放在左边还是右边。

解析
内连接的查询结果都是满足连接条件的元组。但有时我们也希望输出那些不满足连接条件的元组信息。比如，我们想知道每个学生的选课情况，包括已经选课的学生（这部分学生的学号在学生表中有，在选课表中也有，是满足连接条件的），也包括没有选课的学生（这部分学生的学号在学生表中有，但在选课表中没有，不满足连接条件），这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件的连接方式

3)全外连接

全外连接FULL JOIN 将返回左表（table1）和右表（table1）中的所有记录，相当于 LEFT JOIN 和 RIGHT JOIN 的叠加。
FULL JOIN 先执行 LEFT JOIN 遍历左表，再执行 RIGHT JOIN 遍历右表，最后将 RIGHT JOIN 的结果直接追加到 LEFT JOIN 后面。注意，FULL JOIN 会返回重复的行，它们会被保留，不会被删除。

其实就是把左外连接和右外连接的结果合在一起就是全外连接

联合查询（UNION 与 UNION ALL）

SQL UNION 子句/运算符用于合并两个或者多个 SELECT 语句的结果集。
默认地，UNION 运算符会过滤掉两个结果集中重复的记录，只保留其中一条，也就是对两个结果集进行并集操作；此外，UNION 还会按照默认规则对结果集进行排序。
如果您希望保留原始结果，请使用 UNION ALL。UNION ALL 只是对结果集进行简单粗暴的合并，不会过滤重复的记录，也不会进行排序。
UNION 运算符使用注意事项

  • 每个 SELECT 语句都必须拥有相同数量的字段， SELECT 语句的字段名不需要相同，SQL 会将第一个 SELECT 语句的字段名作为结果集的字段名。
  • 就是把多个结果集集中在一起，UNION 前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并（如果前者 select 和 后者 select 有重复，则会合并，且以前面的为基准）；
  • 如果使用 UNION ALL，不会合并重复的记录行；
  • 在效率方面，UNION 高于 UNION ALL。

每个 SELECT 语句都必须拥有相同数量的字段；

嵌套查询【不推荐使用，效率不可把控】

其中，match赛程表 中的 hostTeamID 与 guestTeamID 都和 team表 中的 teamID关联，查询 2006-6-1 到 2006-7-1 之间举行的所有比赛，并且用以下形式列出：拜仁 2：0 不莱梅 2006-6-21。

解题思路：

  1. 先找出我们的结果需求：主队名称 比赛结果 客队名称 比赛日期
  2. 分析表与表之间的关联关系，match表中的主队ID（hostTeamID）和客队ID（guestTeamID）都和team表中的teamID关联，通过关联查询可以获得主队名称和客队名称
  3. 根据match表中的matchTime字段查询 2006-6-1到2006-7-1之间举行的比赛即可
SELECTt1.teamName,m.matchResult,t2.teamName,m.matchTime 
FROM`match` AS m LEFT JOIN team AS t1 ON m.hostTeamID = t1.teamIDLEFT JOIN team AS t2 ON m.guestTeamID = t2.teamID 
WHEREm.matchTime BETWEEN '2006-6-1' AND '2006-7-1';
SELECTT1.teamName,M.matchResult,T2.teamName,M.matchTime 
FROMteam T1,team T2,`match` M 
WHERET1.teamId = M.hostTeamID AND T2.teamId = M.guestTeamID AND M.matchTime >= "2006-06-01“ and M.matchTime <=”2006-07-01";

什么是子查询

子查询，又叫内部查询。当一个查询的执行结果是另一个查询的查询条件时，称之为子查询。子查询可以使用几个简单命令构造功能强大的复合命令。

子查询是一个 SELECT 语句，它嵌套在一个 SELECT、SELECT…INTO 语句、INSERT…INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。

子查询分类

根据子查询返回的结果进行分类

  • 标量子查询：返回单一值的标量，如一个数字或一个字符串
  • 列子查询：返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回
  • 行子查询：返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。
  • 表子查询：返回的结果集是 N 行 N 列，该子查询的结果通常是一个表的数据

可以使用的操作符：= > < >= <= <> ANY IN SOME ALL EXISTS

一个子查询会返回一个标量（就一个值）、一个行、一个列或一个表，这些子查询称之为标量、行、列和表子查询。

如果子查询返回一个标量值（就一个值），那么外部查询就可以使用：=、>、<、>=、<=和<>符号进行比较判断；如果子查询返回的不是一个标量值，而外部查询使用了比较符和子查询的结果进行了比较，那么就会抛出异常。

1、 标量子查询

是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。 可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧

示例：

SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)
SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)
SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)

2、MySQL 列子查询

指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。

可以使用 IN、ANY、SOME 和 ALL 操作符，不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)
SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2)
SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)

NOT IN 是 <> ALL 的别名，二者相同。

特殊情况：

如果 table2 为空表，则 ALL 后的结果为 TRUE；

如果子查询返回如 (0,NULL,1) 这种尽管 s1 比返回结果都大，但有空行的结果，则 ALL 后的结果为 UNKNOWN 。

注意：对于 table2 空表的情况，下面的语句均返回 NULL：

SELECT s1 FROM table1 WHERE s1 > (SELECT s2 FROM table2)SELECT s1 FROM table1 WHERE s1 > ALL (SELECT MAX(s1) FROM table2)

3、MySQL 行子查询

指子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)
//注：(1,2) 等同于 row(1,2)
SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

4、MySQL 表子查询

指子查询返回的结果集是 N 行 N 列的一个表数据。

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)

mysql中 in 和 exists 区别？哪个时候用哪个？

mysql中 in 和 exists 区别？

（1）exists是对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），故内表大的使用exists，可加快效率；

（2）in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

（3）如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

in关键字

in查询相当于多个or条件的叠加，这个比较好理解，比如下面的查询：

select * from user where user_id in (1, 2, 3);
等效于
select * from user where user_id = 1 or user_id = 2 or user_id = 3;
select * from A where id in (select id from B)
#等价于
for select id from B：先执行；
子查询 for select id from A where A.id = B.id：再执行外面的查询；

执行过程：in是先查询内表【select id from B】，再把内表结果与外表【select * from A where id in …】匹配

小总结：in适合外部表数据大于子查询的表数据的业务场景

exists关键字

select ... from table where exists (select id from B where B.id = A.id)；
#等价于
for select id from A：先执行外层的查询；
for select id from B where B.id = A.id：再执行子查询；

可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或者 FALSE）来决定主查询数据结果是否得到保留。

如下：

执行过程：exists是对外表【select * from A where exists …】做loop循环，每次loop循环再对内表（子查询）【select 1 from B where B.id = A.id】进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（所以尽量用小表），故内表大的使用exists，可加快效率。

select * from A where exists (select 1 from B where B.id = A.id)
1)、表A中100000条数据，表B中100条数据，那么查询数据库的次数 = 1(表A查一次) + 100000(子查询：查询表B的次数)  ，一共 100001次；
2)、表A中 100条数据，表B中100000条，查询数据库次数 = 1(表A查一次) + 100(子查询次数)，一共 101次；

可见，只有当子查询的表数量远远大于外部表数据的时候，用exist查询效率好于in；

哪个时候用哪个？

  • 当A表的数据集大于B表的数据集时，用in 优于 exists；
  • 当A表的数据集小于B表的数据集， 用 exists 优于 in【注意： A与B表的id 字段应该建立索引】；
  • 如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in；

varchar(50)中50的涵义

最多存放50个字符（50是指字符数）

int(20)中20的涵义？mysql为什么这么设计int(20)？

int(20)中20的涵义？

20表示最大显示宽度为20，不是存储数据的大小，存储数据的大小仍是占4字节存储，存储范围不变；不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示，比如它是记录行数的id,插入10份数据，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加020表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

mysql为什么这么设计int(20)？

对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

varchar与char的区别

  • char存储的是固定字符串，不足补空格 ，varchar存储的是可变字符串
  • char的存取速度比varchar要快得多，因为其长度固定，方便程序的存储与查找；
  • varchar占据的空间比char更少，char表示存储定长的字符串，不足就用空格补齐，占用更多的存储空间，varchar存多少就占多少
  • char的存储方式是对英文字符(ASCII)占用1个字节，对一个汉字占用两个字节；varchar的存储方式是对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据
  • varchar空格也按一个字符存储，char的空格表示占位不算一个字符

FLOAT和DOUBLE的区别是什么？

  • 在内存中占有的字节数不同, 单精度FLOAT内存占4个字节, 双精度DOUBLE内存占8个字节
  • 有效数字位数不同(尾数) 单精度FLOAT小数点后有效位数7位, 双精度DOUBLE小数点后有效位数16位
  • 在程序中处理速度不同,一般来说，CPU处理单精度浮点数的速度比处理双精度浮点数快

drop、delete与truncate的区别

  • 执行速度，一般来说： drop> truncate > delete
  • delete是DML语句,操作完以后如果没有不想提交事务还可以回滚，不会自动提交。drop/truncate都是DDL语句,操作完马上生效，不能回滚，执行后会自动提交
  • truncate 和delete只删除数据（truncate 会释放空间，delete不会释放空间）， drop则删除整个表和数据（结构和数据）
  • truncate TABLE 不能用于参与有索引视图的表。

三者的介绍

drop：删除内容和定义，释放空间。（表结构和数据一同删除）

【drop语句将删除表的结构，被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。】

truncate：删除内容，释放空间，但不删除定义。（表结构还在，数据删除）

【truncate table 权限默认授予表所有者、sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员且不可转让。】

delete：删除内容，不删除定义，也不释放空间。

drop table user;
truncate table user;
delete from user;
注：user 为数据库表名

UNION与UNION ALL的区别？

  • union去重并排序，union all直接返回合并的结果，不去重也不排序；
  • union all比union性能好；

SQL优化

如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因？

对于低性能的SQL语句的定位，最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划，对于查询语句，最重要的优化方式就是使用索引。 而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

例如使用explain 命令可以用来分析select 语句的运行效果。

explain select * from mytest;

也可以直接在Navicat上直接解释对应的sql语句

explain分析结果说明

select_type：每个子查询的查询类型

table：查询的数据表

type：访问类型（非常重要，可以看出有没有走索引），有以下几个值：

possible_keys：可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

key：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

key_length：索引长度

ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 rows 返回估算的结果集数目，并不是一个准确的值。

SQL性能优化的目标？

至少要达到 range 级别。

深入了解：这一次，彻底读懂Mysql执行计划 - 掘金

具体优化措施：定位及优化SQL语句的性能问题_Franco蜡笔小强的博客-CSDN博客

SQL的生命周期？

SQL由客户端发出后，经过连接和验证，发送到服务器，由服务器派发给线程处理

SQL的生命周期？

  • 客户端与数据库服务器建立一个连接（tcp/ip连接，GLP协议）
  • 数据库进程拿到请求sql
  • 解析并生成执行计划并执行（解析优化过程）
  • 读取数据到内存并进行逻辑处理
  • 通过步骤一的连接发送结果到客户端
  • 关掉连接，释放资源

一条SQL中最重要的两个阶段是SQL解析和SQL优化（MySQL服务器自己对SQL做的优化，可能不是开发者所希望的）

对sql进行优化处理

【例如select语句的优化具体是在JOIN::optimise函数中完成。(MySQL针对select的处理是转换成JOIN操作处理的)】

select A.id, B.score from student A left join subject B on A.id=B.id where A.age > 10 and B.score > 60;

优化过程会将join的key也转换为一个where条件，经过处理后，上面的sql就有了3个where条件：

  • A.age > 10；
  • A.id = B.id；
  • B.score > 60；

sql执行

例如select语句的执行具体是在JOIN::exec（MySQL是将任何select都转换为JOIN来处理的）。即JOIN::exec函数，首先会调用send_fields函数，将最终结果的信息返回，
然后调用do_select。在do_select函数中，通过调用sub_select函数来具体实现join功能。

深入了解：SQL的生命周期_huang714的博客-CSDN博客

大表数据（表中数据很多）查询怎么优化

1. 索引优化

通过建立合理高效的索引,提高查询的速度..

2. SQL优化

组织优化SQL语句,使查询效率达到最优,在很多情况下要考虑索引的作用，务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

3. 水平拆表

如果表中的数据呈现出某一类特性,比如呈现时间特性,那么可以根据时间段将表拆分成多个。

比如按年划分、按季度划分、按月划分等等，查询时按时间段进行拆分查询，再把查询结果进行合并；

比如按地区将表拆分，不同地区的数据放在不同的表里面，然后对查询进行分拆，对查询结果进行合并。

4. 垂直拆表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。对于字段较多的表，如果有些字段的使用频率很低，则垂直分割该表，将原来的一个表分解为两个表。由于数据库每次查询都是以块为单位，而每块的容量是有限的，通常是十几K或几十K，将表按字段拆分后，单次IO所能检索到的行数通常会提高很多，查询效率就能提高上去。

垂直分割遵循以下原则：

  • 把不常用的字段单独放在同一个表中
  • 把大字段独立放入一个表中
  • 把经常使用的字段放在一同一个表中

5. 建立中间表，以空间换时间

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

6. 用内存缓存数据，以空间换时间

将常用而且不常修改的数据加载到内存中，直接从内存查询则可。

可以使用热门的缓存技术，如Memcache、Redis、Ehcache等。

7. 使用其他辅助技术

Solr:一种基于Lucene的JAVA搜索引擎技术

8.增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多， 性能也就越差。冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

超大分页怎么处理？

1、使用覆盖索引

如果一条SQL语句，通过索引可以直接获取查询的结果，不再需要回表查询，就称这个索引为覆盖索引。

把分页的SQL语句的查询字段使用覆盖索引来提高性能

在MySQL数据库中使用explain关键字查看执行计划，如果extra这一列显示Using index，就表示这条SQL语句使用了覆盖索引。

因为实际开发中，用SELECT查询一两列操作是非常少的，因此覆盖索引的适用范围就比较有限。

select * from t5 order by text limit 1000000, 10;
使用了覆盖索引后
select id, `text` from t5 order by text limit 1000000, 10;（text是加了索引的）

2、子查询优化

分页的SQL语句改写成子查询的方法获得性能上的提升

select * from t5 where id>=(select id from t5 order by text limit 1000000, 1) limit 10;

其实使用这种方法，提升的效率和上面使用了覆盖索引基本一致。

但是这种优化方法也有局限性：

  • 这种写法，要求主键ID必须是连续的
  • where后面加了其他条件就会导致效率降低

3、延迟关联

可以使用JOIN，先在索引列上完成分页操作，然后再回表获取所需要的列。

select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.id=b.id;

在采用JOIN改写后，上面的两个局限性都已经解除了，而且SQL的执行效率也没有损失。

4、记录上次查询结束的位置

和上面使用的方法都不同，记录上次结束位置优化思路是使用某种变量记录上一次数据的位置，下次分页时直接从这个变量的位置开始扫描，从而避免MySQL扫描大量的数据再抛弃的操作。

select * from t5 where id>=1000000 limit 10;

mysql 分页

什么是分页

一般在客户端实现分页功能的时候，要显示当前页的数据、当前所在页数、临近页面的按钮以及总页数等等。这些数据随着翻页的进行能够动态的变化，为了实现这样的效果，一般会采取两种办法：真分页和假分页。这样的划分方式是从与数据库的交互方式出发的，是每次翻页时都进行查询还是一次性查出所有的数据。

真分页

真分页指的是每次在进行翻页时都只查询出当前页面的数据，特点就是与数据库的交互次数较多，但是每次查询的数据量较少，数据也不需要一直保存在内存中。但是数据库的负担会很重，尤其是用户量大的情况下，适用于数据量比较大的场景，数据不适合全量查出的情况。

假分页

假分页指的是对于要显示的数据一次性全部查出，一直存在在服务端或客户端，在前端进行分页或由服务端控制分页。将根据当前所在页来计算应该显示的数据所在下标，用循环取出目标数据。只有当会话断开或页面关闭，相应的资源才会被释放。

缓存层

真分页和假分页都要和数据库进行交互，对于真分页来说不需要担心数据同步的问题，因为每次都是查询出最新的，但是数据库的负担会很重，尤其是用户量大的情况下。假分页可以在一定程度上减轻数据库的压力，但是数据不能及时得到同步，除非重新请求或页面刷新。一般在企业中会有缓存层的存在，既能有效降低数据库的压力，又能及时的进行数据同步。在对数据库中的数据进行修改后，要将变更后的数据及时同步到缓存层，在进行数据查询时从缓存层获取。

LIMIT用法

LIMIT出现在查询语句的最后，可以使用一个参数或两个参数来限制取出的数据。其中第一个参数代表偏移量：offset（可选参数），第二个参数代表取出的数据条数：rows。

单参数用法

当指定一个参数时，默认省略了偏移量，即偏移量为0，从第一行数据开始取，一共取rows条。

/* 查询前5条数据 */
SELECT * FROM Student LIMIT 5;

双参数用法

当指定两个参数时，需要注意偏移量的取值是从0开始的，此时可以有两种写法：

/* 查询第1-10条数据 */
SELECT * FROM Student LIMIT 0,10;
/* 查询第11-20条数据 */
SELECT * FROM Student LIMIT 10 OFFSET 10;

分页公式

在进行分页之前，我们需要先根据数据总量来得出总页数，这需要用到COUNT函数和向上取整函数CEIL，SQL如下：

/* 获得数据总条数 */
SELECT COUNT(*) FROM Student;
/* 假设每页显示10条，则直接进行除法运算，然后向上取整 */
SELECT CEIL(COUNT(*) / 10) AS pageTotal FROM Student;

核心信息

  • 当前页：pageNumber
  • 每页数据量：pageSize

在实际操作中，我们能够得到的信息有当前所在页以及每页的数据量，同时要注意一下是否超出了最大页数。以每页10条为例，则前三页的数据应为：

  • 第1页：第1~10条，SQL写法：LIMIT 0,10
  • 第2页：第11~20条，SQL写法：LIMIT 10,10
  • 第3页：第21~30条，SQL写法：LIMIT 20,10

据此我们可以总结出，LIMIT所需要的两个参数计算公式如下：

  • offset：(pageNumber - 1) * pageSize
  • rows：pageSize

慢查询日志

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

默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表

慢查询主要体现在慢上，通常意义上来讲，只要返回时间大于 >1 sec上的查询都可以称为慢查询。慢查询会导致CPU，内存消耗过高。数据库服务器压力陡然过大，那么大部分情况来讲，肯定是由某些慢查询导致的。

查看当前慢查询设置情况

#查看慢查询时间，默认10s，建议降到1s或以下
mysql> show variables like "long_query_time";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
#查看慢查询配置情况，其中，slow_query_log的值是on就是已开启功能了。
mysql> show variables like "%slow%";
+-----------------------------------+----------------------+
| Variable_name                     | Value                |
+-----------------------------------+----------------------+
| log_slow_admin_statements         | OFF                  |
| log_slow_filter                   |                      |
| log_slow_rate_limit               | 1                    |
| log_slow_rate_type                | session              |
| log_slow_slave_statements         | OFF                  |
| log_slow_sp_statements            | ON                   |
| log_slow_verbosity                |                      |
| max_slowlog_files                 | 0                    |
| max_slowlog_size                  | 0                    |
| slow_launch_time                  | 2                    |
| slow_query_log                    | ON                   |
| slow_query_log_always_write_time  | 10.000000            |
| slow_query_log_file               | /tmp/slow_querys.log |
| slow_query_log_use_global_control |                      |
+-----------------------------------+----------------------+
14 rows in set (0.01 sec)

如何开启慢查询功能

方法一：在服务器上找到mysql的配置文件my.cnf（slow_query_log = ON） , 然后再mysqld模块里追加一下内容，这样的好处是会一直生效，不好就是需要重启mysql进程。

vim my.cnf
[mysqld]
slow_query_log = ON
#定义慢查询日志的路径
slow_query_log_file = /tmp/slow_querys.log
#定义查过多少秒的查询算是慢查询，我这里定义的是1秒，5.6之后允许设置少于1秒，例如0.1秒
long_query_time = 1
#用来设置是否记录没有使用索引的查询到慢查询记录,默认关闭,看需求开启,会产生很多日志,可动态修改
#log-queries-not-using-indexes
管理指令也会被记录到慢查询。比如OPTIMEZE TABLE, ALTER TABLE,默认关闭,看需求开启,会产生很多日志,可动态修改
#log-slow-admin-statements

然后重启mysql服务器即可，这是通过一下命令看一下慢查询日志的情况：

tail -f /tmp/slow_querys.log

方法二：通过修改mysql的全局变量来实现（set global slow_query_log=1），这样做的好处是，不用重启mysql服务器，登陆到mysql上执行一下sql脚本即可，不过重启后就失效了。

#开启慢查询功能，1是开启，0是关闭
mysql> set global slow_query_log=1;
#定义查过多少秒的查询算是慢查询，我这里定义的是1秒，5.6之后允许设置少于1秒，例如0.1秒
mysql> set global long_query_time=1;
#定义慢查询日志的路径
mysql> set global slow_query_log_file='/tmp/slow_querys.log';
#关闭功能：set global slow_query_log=0;
然后通过一下命令查看是否成功
mysql> show variables like 'long%'; 
mysql> show variables like 'slow%';
#设置慢查询记录到表中
#set global log_output='TABLE';

MYSQL慢查询日志的记录定义

直接查看mysql的慢查询日志分析，比如我们可以tail -f slow_query.log查看里面的内容

# Time: 110107 16:22:11
# User@Host: root[root] @ localhost []
# Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774
SET timestamp=1294388531;
select count(*) from ep_friends;

字段意义解析

  • 第一行,SQL查询执行的时间
  • 第二行,执行SQL查询的连接信息，用户和连接IP
  • 第三行,记录了一些我们比较有用的信息，如下解析:
    • Query_time,这条SQL执行的时间,越长则越慢
    • Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
    • Rows_sent,查询返回的行数
    • Rows_examined,查询检查的行数，越长就当然越费时间
  • 第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间。
  • 第五行及后面所有行（第二个# Time:之前）,执行的sql语句记录信息，因为sql可能会很长。

MYSQL慢查询日志分析方法

虽然慢查询日志已经够清晰，但是往往我们的日志记录到的不是只有一条sql，可能有很多很多条，如果不加以统计，估计要看到猴年马月，这个时候就需要做统计分析了。

方法一：使用mysql程序自带的mysqldumpslow命令分析

例如：

mysqldumpslow -s c -t 10 /tmp/slow-log

这会输出记录次数最多的10条SQL语句，得出的结果和上面一般慢查询记录的格式没什么太大差别

参数解析：
-s：是表示按照何种方式排序，子参数如下：c、t、l、r：分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar：表示相应的倒叙；
-t：返回前面多少条的数据，这里意思就是返回10条数据了（也可以说是前十）
-g：后边可以写一个正则匹配模式，大小写不敏感的，比如：/path/mysqldumpslow -s r -t 10 /tmp/slow-log，得到返回记录集最多的10个查询。/path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log，得到按照时间排序的前10条里面含有左连接的查询语句。

方法二：使用pt(Percona Toolkit)工具的pt-query-digest进行统计分析

这个是由Percona公司出品的一个用perl编写的脚本，只有安装上pt工具集才会存在，有兴趣的朋友就要先安装pt工具了。直接分析慢查询文件，执行如下：

pt-query-digest slow_querys.log >t.txt

因为记录里还是可能有很多sql在，看起来还是费劲，所以建议输出到文件来看了

对慢查询都怎么优化过？

在业务系统中，除了使用主键进行的查询，其他的我都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么？

是查询条件没有命中索引？是加载（load）了不需要的数据列？还是数据量太大？

所以优化也是针对这三个方向来的

  • 首先分析语句，看看是否加载（load）了额外的数据，可能是查询了多余的行，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
  • 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
  • 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

为什么要尽量设定一个主键？

主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键， 也建议添加一个自增长的ID列作为主键。主键存在的意义在于不仅能保证数据的唯一性，而且当以主键构建聚簇索引时，能够对数据快速定位，提高数据操作效率

主键使用自增ID还是UUID？

推荐使用自增ID，不要使用UUID。

因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原 来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。

简单来说：因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，所以innodb的叶子节点按照主键顺序存放主键和对应行的数据，如果是uuid，添加需要从中间插入；如果是自增id，直接在末尾插入，总之，在数据量大一些的情况下，用自增主键性能会好一些。

关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

好文参考：自增还是UUID？数据库主键的类型选择，为啥不能用uuid做MySQL的主键？ - 古兰精 - 博客园

字段为什么要求定义为not null？

在mysql数据库中“NULL”和“空值”是不一样的。NULL是一种比较特殊的数据类型，这也可以解释为什么字段设置为NOT NULL，却仍然可以插入空值。设置了NOT NULL之后便不能插入NULL值了，但仍然可以插入像 ' ' 这样的空值。另外空值是不占用空间的，而NULL需要占用空间。

在平常我们设计数据表时，如果是索引字段，一定要定义为NOT NULL。因为NULL值会影响cordinate统计，影响优化器对索引的选择，索引效率会下降很多，会带来的存储空间的问题，还需要额外的特殊处理，导致更多的存储空间占用，使用NULL带来更多的问题，比如索引、索引统计、值计算更加复杂，如果使用索引，就要避免列设置成NULL

虽然表中允许空(NULL)列，但其它字段也尽量定义为NOT NULL。mysql在进行比较的时候，NULL 会参与字段比较。因为NULL是一种比较特殊的数据类型。数据库在处理的时候，需要进行特殊的处理。如此的话，就会增加数据库处理记录的复杂性。当表中有比较多的空字段时，在同等条件下，数据库处理的性能会降低许多。

深入了解：为什么数据库字段要使用NOT NULL？ - 掘金

如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar 来存储，这样可以节省空间且提高检索效率。

30种SQL查询语句优化方法

  1. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描
  2. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
  3. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null，可以在num列上设置默认值 0 ，确保表中num列没有null值，然后这样查询：select id from t where num=0
  4. 尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20
  5. 下面的模糊查询也将导致全表扫描：select id from t where name like ‘%c%’，若要提高效率，可以使用向左匹配 select id from t where name like c% (不能前置百 分号)，或者考虑全文检索。
  6. in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)，对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3
  7. 如果在 where 子句中使用参数，也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num
  8. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2
  9. 应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=’abc'生成的id应改为:select id from t where name like ‘abc%’。如：select id from t where datediff(day,createdate,’2005-11-30′)=’2005-11-30应改为：select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′
  10. 不要在 where 子句中的 “=” 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
  11. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。
  12. 不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into t from t where 1=0 ，这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table t(…)
  13. 很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)，用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)
  14. 并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段 sex、male、female几乎各一半，那么即使在 sex 上建了索引也对查询效率起不了作用。
  15. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
  16. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
  17. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
  18. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
  19. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
  20. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
  21. 避免频繁创建和删除临时表，以减少系统表资源的消耗。
  22. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
  23. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
  24. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
  25. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过 1 万行，那么就应该考虑改写。
  26. 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
  27. 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
  28. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
  29. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
  30. 尽量避免大事务操作，提高系统并发能力。

优化查询过程中的数据访问

  • 确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
  • 确认MySQL服务器是否在分析大量不必要的数据行

避免犯如下SQL语句错误

  • 查询不需要的数据。解决办法：使用limit解决
  • 多表关联返回全部列。解决办法：指定列名
  • 总是返回全部列。解决办法：避免使用SELECT *
  • 重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
  • 是否在扫描额外的记录。解决办法：使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化：
    • 使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。
    • 改变数据库和表的结构，修改数据表范式
    • 重写SQL语句，让优化器可以以更优的方式执行查询。

优化长难的查询语句

MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多

使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。

切分查询，将一个大的查询分为多个小的相同的查询

一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销。

分解关联查询，让缓存的效率更高。

执行单个查询可以减少锁的竞争，在应用层做关联更容易对数据库进行拆分，查询效率会有大幅提升，较少冗余记录的查询。

优化特定类型的查询语句

1.优化count()查询

  • count() 是一个特殊的函数，有两种非常不同的作用。它可以统计某个列值的数量，也可以统计行数。
    统计列值 要求列值是非空的。(不统计null，即null值计数为0)
  • count()的另一个用处是统计结果集的行数。当mysql确认括号的表达式值不可能为空时，实际上就是统计行数。最简单的就是当我们使用count(*)的时候，这种情况下统配符*并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。
    假如在同一个查询中统计同一个列不同值得数量
select sum(if(color = 'blue', 1, 0)) as BLUE, sum(if(color = 'red', 1, 0)) as RED from items
select count(color= 'blue' or null) blue, count(color= 'red' or null) red, from items

MySQL优化特定类型的查询（书摘备查）_wzy0623的博客-CSDN博客

优化特定类型的查询_鸢尾_1024的博客-CSDN博客

mysql查询优化之四：优化特定类型的查询 - duanxz - 博客园

为什么要优化

为了避免网站页面出现访问错误

由于数据库连接timeout产生页面5xx错误

由于慢查询造成页面无法加载

由于阻塞造成数据无法提交

为了增加数据库的稳定性

很多数据库问题都是由于低效的查询引起的

为了优化用户体验

流畅页面的访问速度

良好的网站功能体验

数据库结构优化

数据库结构优化的目的

1、减少数据冗余，数据冗余是指相同的数据在多个地方存在，表中的某个列可以在其他某个列中获取到

2、尽量避免数据维护中出现更新、插入和删除异常

3、节约数据库存储空间。

一.首先我们选择合适的数据类型

数据类型的选择，重点在于“合适”二字，如何确定选择的数据类型是否合适了？

  • 使用可以存下你的数据的最小的数据类型。（时间类型数据：可以使用varchar类型，可以使用int类型，也可以使用时间戳类型）
  • 使用简单的数据类型，int要比varchar类型在mysql处理上简单。（int类型存储时间是最好的选择）
  • 尽可能的使用not null定义字段。（innodb的特性所决定，非not null的值，需要额外的在字段存储，同时也会增加IO和存储的开销）
  • 尽量少用text类型，非用不可时最好考虑分表。

二、数据库表的范式化优化

范式化是指数据库设计的规范，目前说道范式化一般是指第三范式。也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。

范式化设计和反范式化设计的对比

1、范式化可以尽量的减少数据冗余

2、范式化的更新操作比反范式化更快

3、范式化的表通常比反范式化的表要小

4、反范式化减少表的关联

5、反范式化相比范式化可以更好的对索引进行优化，例如使用覆盖索引

三、数据库表的垂直拆分

垂直拆分将一个属性较多，一行数据较大的表，将不同的属性拆分到不同的表中，以降低单库（表）大小，达到提升性能的目的的方法，这解决了表的宽度问题。

四、数据库表的水平拆分

表的水平拆分是为了解决单表数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。相对于垂直拆分，水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，如果表中的数据呈现出某一类特性,比如呈现时间特性,那么可以根据时间段将表拆分成多个。

比如按年划分、按季度划分、按月划分等等，查询时按时间段进行拆分查询，再把查询结果进行合并；

比如按地区将表拆分，不同地区的数据放在不同的表里面，然后对查询进行分拆，对查询结果进行合并。

深入了解：数据库优化----结构优化_开花的萝卜头的博客-CSDN博客

MySQL数据库cpu飙升到500%的话他怎么处理？

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行？找出消耗高的 sql，看看执行计划是否准确？index 是否缺失？或者实在是数据量太大造成？

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

具体操作：MySQL数据库cpu飙升到500%的话他怎么处理？_lxw1844912514的博客-CSDN博客

MySQL的复制原理以及流程

MySQL复制：为保证主服务器和从服务器的数据一致性，在向主服务器插入数据后，从服务器会自动将主服务器中修改的数据同步过来。

主从复制简介

在实际的生产中，为了解决Mysql的单点故障已经提高MySQL的整体服务性能，一般都会采用「主从复制」。

比如：在复杂的业务系统中，有一句sql执行后导致锁表，并且这条sql的的执行时间有比较长，那么此sql执行的期间导致服务不可用，这样就会严重影响用户的体验度。

主从复制中分为「主服务器（master）「和」从服务器（slave）」，「主服务器负责写，而从服务器负责读」，Mysql的主从复制的过程是一个「异步的过程」。

这样读写分离的过程能够是整体的服务性能提高，即使写操作时间比较长，也不影响读操作的进行。

MySQL主从复制工作原理

在主库上把数据更新记录到二进制日志，从库将主库的日志复制到自己的中继日志，然后

从库读取中继日志的事件，将其重放到从库数据中

主从复制主要有三个线程：binlog线程，I/O线程，SQL线程。

  • binlog线程：负责将主服务器上的数据更改写入到二进制日志（Binary log）中。
  • I/O线程：负责从主服务器上读取二进制日志（Binary log），并写入从服务器的中继日志（Relay log）中。
  • SQL线程：负责读取中继日志，解析出主服务器中已经执行的数据更改并在从服务器中重放

基本原理流程

  1. Master在每个事务更新数据完成之前，将操作记录写入到binlog中。
  2. Slave从库连接Master主库，并且Master有多少个Slave就会创建多少个binlog dump线程。当Master节点的binlog发生变化时，binlog dump会通知所有的Slave
  3. I/O线程接收通知后就从主服务器中读取到binlog内容后，将其写入到中继日志（Relay log）中。
  4. SQL线程读取中继日志，并在从服务器中重放。

以上就是主从复制的过程，当然，主从复制的过程有不同的策略方式进行数据的同步，主要包含以下几种：

  • 「同步策略」：Master会等待所有的Slave都回应后才会提交，这个主从的同步的性能会严重的影响。
  • 「半同步策略」：Master至少会等待一个Slave回应后提交。
  • 「异步策略」：Master不用等待Slave回应就可以提交。
  • 「延迟策略」：Slave要落后于Master指定的时间。

对于不同的业务需求，有不同的策略方案，但是一般都会采用最终一致性，不会要求强一致性，毕竟强一致性会严重影响性能。

主从复制的作用

确保数据安全

做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据的丢失。

提升I/O性能

随着日常生产中业务量越来越大,I/O访问频率越来越高，单机无法满足，此时做多库的存储，有效降低磁盘I/O访问的频率，提高了单个设备的I/O性能。

读写分离，使数据库能支持更大的并发

在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

MySQL主从复制解决的问题

  • 数据分布：通过复制将数据分布到不同地理位置
  • 负载均衡：读写分离以及将读负载到多台从库
  • 备份：可作为实时备份
  • 高可用性：利用主从复制实现高可用

深入了解：MySQL的复制原理以及流程 - 简书

读写分离有哪些解决方案？

做读写分离的原因

一般系统中数据读取频率高于写入频率，单个数据库实例在写入的时候会影响读取性能，这是做读写分离的原因。

MySQL读写分离的基础

实现方式主要基于mysql的主从复制，通过路由的方式使应用对数据库的写请求只在master上进行，读请求在slave上进行。读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。因为主从复制要求slave不能写只能读（如果对slave执行写操作，那么show slave status将会呈现Slave_SQL_Running=NO，此时你需要按照前面提到的手动同步一下slave）。

读写分离有哪些解决方案？

1、基于MySQL proxy代理的方式

在应用和数据库之间增加代理层，代理层接收应用对数据库的请求，根据不同请求类型转发到不同的实例，在实现读写分离的同时可以实现负载均衡。

MySQL的代理最常见的是mysql-proxy、cobar、mycat、Atlas等。这种方式对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多 个proxy的连接参数即可。

mysql-proxy是一个轻量的中间代理，是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等，依靠内部一个lua脚本实现读写语句的判断。项目地址： https://github.com/mysql/mysql-proxy ，该项目已经六七年没有维护了，官方也不建议应用于生成环境。
cobar是阿里提供的一个中间件，已经停止更新。项目地址：https://github.com/alibaba/cobar 
mycat的前身就是cobar，活跃度比较高，完全使用java语言开发。 项目地址：https://github.com/MyCATApache/Mycat-Server ，该项目当前已经有8.3k的点赞量。
moeba（变形虫）是阿里工程师陈思儒基于java开发的一款数据库读写分离的项目(读写分离只是它的一个小功能)，与MySQL官方的MySQL Proxy相比，作者强调的是amoeba配置的方便（基于XML的配置文件，用SQLJEP语法书写规则，比基于lua脚本的MySQL Proxy简单）。更多详细介绍请参考：https://www.biaodianfu.com/amoeba.html , 下载地址：https://sourceforge.net/projects/amoeba/ 。
Atlas奇虎360的一个开源中间代理，是在mysql官方mysql-proxy 0.8.2的基础上进行了优化，增加一些新的功能特性。 项目地址: https://github.com/Qihoo360/Atlas ，该项目当前已经有4.4k的点赞量。

2、基于应用内路由的方式

基于应用内路由的方式即为在应用程序中实现，针对不同的请求类型去不同的实例执行sql。

基于spring的aop实现: 用aop来拦截spring项目的dao层方法，根据方法名称就可以判断要执行的sql类型(即是read还是write类型)，进而动态切换主从数据源。类似项目有：

多数据源切换：dynamic-datasource-spring-boot-starter: 基于 SpringBoot 多数据源 动态数据源 主从分离 快速启动器 支持分布式事务

3 、基于mysql-connector-java的jdbc驱动方式

使用mysql驱动Connector/J的可以实现读写分离。即在jdbc的url中配置为如下的形示：

jdbc:mysql:replication://master,slave1,slave2,slave3/test

java程序通过在连接MySQL的jdbc中配置主库与从库等地址，jdbc会自动将读请求发送给从库，将写请求发送给主库，此外，mysql的jdbc驱动还能够实现多个从库的负载均衡。

关于mysql的jdbc说明官方文档地址：MySQL :: MySQL Connector/J 8.0 Developer Guide :: 6.2 Connection URL Syntax

关于mysql的读写分离文档地址：MySQL :: MySQL Connector/J 8.0 Developer Guide :: 9.4 Configuring Source/Replica Replication with Connector/J

4、基于sharding-jdbc的方式

sharding-sphere是强大的读写分离、分表分库中间件，sharding-jdbc是sharding-sphere的核心模块。

sharding-jdbc可以与springboot集成。官方网址：Apache ShardingSphere

org.apache.shardingspheresharding-jdbc-spring-boot-starter4.0.1

总结

以上四种方案各有优缺点，基于MySQL proxy代理的方式对于应用来说相对简单，但是在项目稳定性、事务支持性等方面还存在问题；而基于应用内路由的方式固然灵活度比较高，但是也增加了应用逻辑的复杂度；基于mysql-connector-java的jdbc驱动和sharding-jdbc的方式在使用上相对简单，但限制了需要使用java开发。

备份计划

备份计划

视库的大小来定，一般来说 100G 内的库，可以考虑使用 mysqldump 来做，因为 mysqldump更加轻巧灵活，备份时间选在业务低峰期，可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小，压缩之后更小)。100G 以上的库，可以考虑用 xtranbackup 来做，备份速度明显要比 mysqldump 要快。一般是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。

备份恢复时间

物理备份恢复快，逻辑备份恢复慢 ，这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考

  • 20G的2分钟（mysqldump）
  • 80G的30分钟(mysqldump)
  • 111G的30分钟（mysqldump)
  • 288G的3小时（mysqldump)
  • （xtra) 3T的4小时
  • （xtra) 逻辑导入时间一般是备份时间的5倍以上

备份恢复失败如何处理

首先在恢复之前就应该做足准备工作，避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错，再根据报错的提示来进行相应的调整。

mysqldump和xtrabackup实现原理

mysqldump和xtrabackup备份原理实现说明_weixin_33856370的博客-CSDN博客

数据表损坏的修复方式有哪些？导致mysql 表毁坏的常见原因？表损坏的症状 ？怎么预防 MySQL 表损坏 ？

导致mysql 表毁坏的常见原因

1、 服务器突然断电导致数据文件损坏。
2、 强制关机，没有先关闭mysql 服务。
3、 mysqld 进程在写表时被杀掉。
4、 使用myisamchk 的同时，mysqld 也在操作表。
5、 磁盘故障。
6、 服务器死机。
7、 mysql 本身的bug 。

表损坏的症状

  1. 当在从表中选择数据之时，你得到如下错误：
    Incorrect key file for table: '...'. Try to repair it
  2. 查询不能在表中找到行或返回不完全的数据。
    Error: Table 'p' is marked as crashed and should be repaired 。
  3. 打开表失败： Can't open file: ‘×××.MYI' (errno: 145) 。

预防 MySQL 表损坏

1 、定期使用myisamchk 检查MyISAM 表（注意要关闭mysqld ），推荐使用check table 来检查表（不用关闭mysqld ）。
2 、在做过大量的更新或删除操作后，推荐使用OPTIMIZE TABLE 来优化表，这样既减少了文件碎片，又减少了表损坏的概率。
3 、关闭服务器前，先关闭mysqld （正常关闭服务，不要使用kill -9 来杀进程）。
4 、使用ups 电源，避免出现突然断电的情况。
5 、使用最新的稳定发布版mysql ，减少mysql 本身的bug 导致表损坏。
6 、对于InnoDB 引擎，你可以使用innodb_tablespace_monitor 来检查表空间文件内文件空间管理的完整性。
7 、对磁盘做raid ，减少磁盘出错并提高性能。
8 、数据库服务器最好只跑mysqld 和必要的其他服务，不要跑其他业务服务，这样减少死机导致表损坏的可能。
9 、不怕万一，只怕意外，平时做好备份是预防表损坏的有效手段。

数据表损坏的修复方式有哪些？

使用 myisamchk 来修复，具体步骤：

1）修复前将mysql服务停止。

2）打开命令行方式，然后进入到mysql的/bin目录。

3）执行myisamchk –recover 数据库所在路径/*.MYI

使用repair table 或者 OPTIMIZE table命令来修复，REPAIR TABLE `table_name` 修复表 OPTIMIZE TABLE `table_name` 优化表 REPAIR TABLE 用于修复被破坏的表。

OPTIMIZE TABLE 用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）

其他

什么是存储过程？有哪些优缺点？

什么是存储过程？

存储过程是一个预编译的SQL语句，允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

优点

  • 存储过程是预编译过的，执行效率高。
  • 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
  • 安全性高，执行存储过程需要有一定权限的用户。
  • 存储过程可以重复使用，减少数据库开发人员的工作量。

缺点

  • 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
  • 移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
  • 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发 生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
  • 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用 户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

什么是触发器？触发器的使用场景有哪些？

什么是触发器？

触发器是用户定义在关系表上的一类由事件驱动的特殊存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。作用是监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

触发器的使用：MySQL数据库---触发器_cyy_0802的博客-CSDN博客_2、创建一个触发器,功能如下:在成绩表中插入一条记录,如果插入的学号和课程号存

触发器的使用场景有哪些？

  • 复杂的安全性检查:比如:禁止在非工作时间插入新员工
  • 数据库的确认:比如:涨工资,工资应该越长越多的,如果越长越少就不叫涨工资了
  • 数据库审计:比如:跟踪表上操作的记录,比如什么时间什么人操作了数据库,操作了表上的 记录是什么等
  • 数据库的备份和同步:比如有两个数据库一个在北京一个在上海,在北京的数据库是主数据库,在上海的数据库是备用数据库,在主数据库中的数据被修改了以后可以通过触发器监听,如果被修改会将修改的数据传递给备份数据库,当主数据崩溃以后不影响数据的使用

使用场景深入了解：触发器的四个应用场景_弄潮儿3040的博客-CSDN博客_触发器使用场景

MySQL数据库中有哪些触发器？

MySQL数据库中有6种触发器

  • BEFORE INSERT ： 在插入数据前，检测插入数据是否符合业务逻辑，如不符合返回错误信息。
  • AFTER INSERT ： 在表 A 创建新账户后，将创建成功信息自动写入表 B 中。
  • BEFORE UPDATE ：在更新数据前，检测更新数据是否符合业务逻辑，如不符合返回错误信息。
  • AFTER INSERT ：在更新数据后，将操作行为记录在 log 中
  • BEFORE DELETE ：在删除数据前，检查是否有关联数据，如有，停止删除操作。
  • AFTER DELETE ：删除表 A 信息后，自动删除表 B 中与表 A 相关联的信息。

具体使用和深入：MySQL 触发器使用教程 - 六种触发器案例详解 - 知乎

什么是游标？

在 MySQL 中，查询有时会返回多条记录，也就是一个结果集，而使用简单的 SELECT 语句，没有办法得到具体的第几行数据，例如第一行、下一行或前十行的数据，这时可以使用游标来逐条读取查询结果集中的某条数据。游标在部分资料中也被称为光标。
一般通过游标定位到结果集的某一行进行数据查询或者修改。

简单理解：游标是SQL的一种数据访问机制。可以将游标简单的看成是查询的结果集的一-个指针，可以根据需要在结果集上面来回移动，浏览需要的数据。

上一篇：python+django家政服务中介网站系统

下一篇：关于宽容和慷慨的一句名言

相关内容

热门资讯

北京的名胜古迹 北京最著名的景... 北京从元代开始，逐渐走上帝国首都的道路，先是成为大辽朝五大首都之一的南京城，随着金灭辽，金代从海陵王...
应用未安装解决办法 平板应用未... ---IT小技术，每天Get一个小技能！一、前言描述苹果IPad2居然不能安装怎么办？与此IPad不...
脚上的穴位图 脚面经络图对应的... 人体穴位作用图解大全更清晰直观的标注了各个人体穴位的作用，包括头部穴位图、胸部穴位图、背部穴位图、胳...
长白山自助游攻略 吉林长白山游... 昨天介绍了西坡的景点详细请看链接：一个人的旅行，据说能看到长白山天池全凭运气，您的运气如何？今日介绍...
demo什么意思 demo版本... 618快到了，各位的小金库大概也在准备开闸放水了吧。没有小金库的，也该向老婆撒娇卖萌服个软了，一切只...
猫咪吃了塑料袋怎么办 猫咪误食... 你知道吗？塑料袋放久了会长猫哦！要说猫咪对塑料袋的喜爱程度完完全全可以媲美纸箱家里只要一有塑料袋的响...
世界上最漂亮的人 世界上最漂亮... 此前在某网上，选出了全球265万颜值姣好的女性。从这些数量庞大的女性群体中，人们投票选出了心目中最美...
我喜欢你韩语怎么说 sikid...
埃菲尔铁塔在哪 中国仿建埃菲尔... 2019年4月26日，广西南宁市，街头惊现一座巨型山寨版埃菲尔铁塔，高约20米，白色塔身，造型逼真，...
苗族的传统节日 贵州苗族节日有... 【岜沙苗族芦笙节】岜沙，苗语叫“分送”，距从江县城7.5公里，是世界上最崇拜树木并以树为神的枪手部落...
北京的名胜古迹 北京最著名的景... 北京从元代开始，逐渐走上帝国首都的道路，先是成为大辽朝五大首都之一的南京城，随着金灭辽，金代从海陵王...
长白山自助游攻略 吉林长白山游... 昨天介绍了西坡的景点详细请看链接：一个人的旅行，据说能看到长白山天池全凭运气，您的运气如何？今日介绍...
应用未安装解决办法 平板应用未... ---IT小技术，每天Get一个小技能！一、前言描述苹果IPad2居然不能安装怎么办？与此IPad不...
脚上的穴位图 脚面经络图对应的... 人体穴位作用图解大全更清晰直观的标注了各个人体穴位的作用，包括头部穴位图、胸部穴位图、背部穴位图、胳...
猫咪吃了塑料袋怎么办 猫咪误食... 你知道吗？塑料袋放久了会长猫哦！要说猫咪对塑料袋的喜爱程度完完全全可以媲美纸箱家里只要一有塑料袋的响...