MySQL

什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

• 1.数据以表格的形式出现
• 2.每行为各种记录名称
• 3.每列为记录名称所对应的数据域
• 4.许多的行和列组成一张表单
• 5.若干的表单组成database

MySQL数据库

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• MySQL 是开源的，目前隶属于 Oracle 旗下产品。
• MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL 使用标准的 SQL 数据语言形式。
• MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
• MySQL 对 PHP 有很好的支持，PHP 是很适合用于 Web 程序开发。
• MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
• MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

MySQL三大范式

在 MySQL 中，三大范式是指第一范式、第二范式和第三范式。

第一范式(1NF)要求数据表中的每一列都必须是不可分割的单一值，并且表中的每一行都必须有唯一的标识。

第二范式(2NF)要求表中的每一列都必须完全依赖于主键，也就是说，如果表中有多个列作为主键，那么其它列都必须依赖于这些列中的所有列。

第三范式(3NF)要求表中的每一列都必须与主键或其它列没有直接的依赖关系，也就是说，表中的每一列都必须与主键直接相关，或者与其它列间接相关，但是不能直接依赖于其它列。

这三大范式是数据库设计中常用的规范，旨在规范数据表的结构，使其符合数据库设计的原则，以达到最小冗余、最小耦合、数据一致性高等

MySQL的执行顺序

结合上图，整理出如下伪SQL查询语句。

从这个顺序中我们可以发现，所有的查询语句都是从 FROM 开始执行的。在实际执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。 接下来，我们详细的介绍下每个步骤的具体执行过程。

1 FROM 执行笛卡尔积

FROM 才是 SQL 语句执行的第一步，并非 SELECT 。对FROM子句中的前两个表执行笛卡尔积(交叉联接），生成虚拟表VT1，获取不同数据源的数据集。

FROM子句执行顺序为从后往前、从右到左，FROM 子句中写在最后的表(基础表 driving table)将被最先处理，即最后的表为驱动表，当FROM 子句中包含多个表的情况下，我们需要选择数据最少的表作为基础表。

2 ON 应用ON过滤器

对虚拟表VT1 应用ON筛选器，ON 中的逻辑表达式将应用到虚拟表 VT1中的各个行，筛选出满足ON 逻辑表达式的行，生成虚拟表 VT2 。

---

3 JOIN 添加外部行

如果指定了OUTER JOIN保留表中未找到匹配的行将作为外部行添加到虚拟表 VT2，生成虚拟表 VT3。保留表如下：

• LEFT OUTER JOIN把左表记为保留表
• RIGHT OUTER JOIN把右表记为保留表
• FULL OUTER JOIN把左右表都作为保留表

在虚拟表 VT2表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表中的数据被赋予NULL值，最后生成虚拟表 VT3。

如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1~3，直到处理完所有的表为止。

4 WHERE 应用WEHRE过滤器

对虚拟表 VT3应用WHERE筛选器。根据指定的条件对数据进行筛选，并把满足的数据插入虚拟表 VT4。

• 由于数据还没有分组，因此现在还不能在WHERE过滤器中使用聚合函数对分组统计的过滤。
• 同时，由于还没有进行列的选取操作，因此在SELECT中使用列的别名也是不被允许的。

5 GROUP BY 分组

按GROUP BY子句中的列/列表将虚拟表 VT4中的行唯一的值组合成为一组，生成虚拟表VT5。如果应用了GROUP BY，那么后面的所有步骤都只能得到的虚拟表VT5的列或者是聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组包含一行。

同时，从这一步开始，后面的语句中都可以使用SELECT中的别名。

6 AGG_FUNC 计算聚合函数

计算 max 等聚合函数。SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。常用的 Aggregate 函数包涵以下几种：

• AVG：返回平均值
• COUNT：返回行数
• FIRST：返回第一个记录的值
• LAST：返回最后一个记录的值
• MAX： 返回最大值
• MIN：返回最小值
• SUM： 返回总和

7 WITH 应用ROLLUP或CUBE

对虚拟表 VT5应用ROLLUP或CUBE选项，生成虚拟表 VT6。

CUBE 和 ROLLUP 区别如下：

• CUBE 生成的结果数据集显示了所选列中值的所有组合的聚合。
• ROLLUP 生成的结果数据集显示了所选列中值的某一层次结构的聚合。

8 HAVING 应用HAVING过滤器

对虚拟表VT6应用HAVING筛选器。根据指定的条件对数据进行筛选，并把满足的数据插入虚拟表VT7。

HAVING 语句在SQL中的主要作用与WHERE语句作用是相同的，但是HAVING是过滤聚合值，在 SQL 中增加 HAVING 子句原因就是，WHERE 关键字无法与聚合函数一起使用，HAVING子句主要和GROUP BY子句配合使用。

9 SELECT 选出指定列

将虚拟表 VT7中的在SELECT中出现的列筛选出来，并对字段进行处理，计算SELECT子句中的表达式，产生虚拟表 VT8。

10 DISTINCT 行去重

将重复的行从虚拟表 VT8中移除，产生虚拟表 VT9。DISTINCT用来删除重复行，只保留唯一的。

11 ORDER BY 排列

将虚拟表 VT9中的行按ORDER BY 子句中的列/列表排序，生成游标 VC10 ，注意不是虚拟表。因此使用 ORDER BY 子句查询不能应用于表达式。同时，ORDER BY子句的执行顺序为从左到右排序，是非常消耗资源的。

12 LIMIT/OFFSET 指定返回行

从VC10的开始处选择指定数量行，生成虚拟表 VT11，并返回调用者。

连接查询

 内连接

                内连接是关联查询的另一种写法；

                内连接查询只会查询到符合条件得记录，结果和表关联查询是一样的。

           语法：SELECT 子句

                FROM 表A J

                OIN 表B ON A与B的连接条件

                [JOIN 表C ON A与C或B与C的连接条件

                ...

                ]

                WHERE 过滤条件     

 外连接

                外连接也用于关联查询，特点：可以将不满足连接条件的记录也查询出来 

                左外连接（LEFT JOIN）：以JOIN左侧为驱动表，该表中所有记录都要体现在结果集                      中，右侧表不满足连接条件的记录对应的字段全部为NULL

                右外连接（RIGHT JOIN）：以JOIN右侧作为驱动表，该表中的记录都要体现在结果集                  中，左侧表不满足连接条件的字段都补NULL

 自连接

                自连接是指当前表中的一条记录可以对应自己的多条记录；

                具有相同属性的一组数据之间又存在上下级的树状结构数据。

示例：       

        查看'小黄'的下属都有谁?

        SELECT t.name,m.name

        FROM teacher t

        JOIN teacher m ON t.manager=m.id

        WHERE m.name='小黄';

子查询

 嵌套在一个SQL语言中的DQL语句，该DQL被称为子查询

        应用场景：

        DQL中使用子查询

                在SELECT字句中，将当前子查询结果作为一个字段展示

                WHERE字句中，将当前子查询结果作为过滤条件使用

        DML中使用：将一个查询结果集用于增删改操作

        子查询分类：

                单行单列子查询，该子查询的结果集只有一个值

                多行多列子查询，该子查询结果集是多个值

               多行单列子查询是可以检索出若干个值。因为作为过滤条件使用，一般配合：IN,NOT IN                 使用。因为等于是不能同时等于好几个值的，只能同于其中之一

               多行多列子查询，将该子查询当做一张表使用

               可以将一个查询结果集当做一张表创建出来

MySQL索引

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

MySQL 索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。

打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引：

• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
• 组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引虽然能够提高查询性能，但也需要注意以下几点：

• 索引需要占用额外的存储空间。
• 对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能。
• 过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引。

普通索引

索引能够显著提高查询的速度，尤其是在大型表中进行搜索时。通过使用索引，MySQL 可以直接定位到满足查询条件的数据行，而无需逐行扫描整个表。

创建索引

使用 CREATE INDEX 语句可以创建普通索引。

普通索引是最常见的索引类型，用于加速对表中数据的查询。

CREATE INDEX 的语法： 

CREATE INDEX index_name

ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...); 

• CREATE INDEX: 用于创建普通索引的关键字。
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
• table_name: 指定要在哪个表上创建索引。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

以下实例假设我们有一个名为 students 的表，包含 id、name 和 age 列，我们将在 name 列上创建一个普通索引。

CREATE INDEX idx_name ON students (name);

上述语句将在 students 表的 name 列上创建一个名为 idx_name 的普通索引，这将有助于提高通过姓名进行搜索的查询性能。

需要注意的是，如果表中的数据量较大，索引的创建可能会花费一些时间，但一旦创建完成，查询性能将会显著提高。

修改表结构(添加索引)

我们可以使用 ALTER TABLE 命令可以在已有的表中创建索引。

ALTER TABLE 允许你修改表的结构，包括添加、修改或删除索引。

ALTER TABLE 创建索引的语法：

ALTER TABLE table_name

ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• ALTER TABLE: 用于修改表结构的关键字。
• table_name: 指定要修改的表的名称。
• ADD INDEX: 添加索引的子句。ADD INDEX用于创建普通索引。
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

下面是一个实例，我们将在已存在的名为 employees 的表上创建一个普通索引:

ALTER TABLE employees ADD INDEX idx_age (age);

上述语句将在 employees 表的 age 列上创建一个名为 idx_age 的普通索引。

创建表的时候直接指定

我们可以在创建表的时候，你可以在 CREATE TABLE 语句中直接指定索引，以创建表和索引的组合。

CREATE TABLE table_name (

column1 data_type,

column2 data_type,

...,

INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)

);

• CREATE TABLE: 用于创建新表的关键字。
• table_name: 指定要创建的表的名称。
• (column1, column2, ...): 定义表的列名和数据类型。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• INDEX: 用于创建普通索引的关键字。
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

下面是一个实例，我们要创建一个名为 students 的表，并在 age 列上创建一个普通索引。

CREATE TABLE students (

      id INT PRIMARY KEY,

      name VARCHAR(50),

      age INT, INDEX idx_age (age)

);

在上述实例中，我们在 students 表的 age 列上创建了一个名为 idx_age 的普通索引。

删除索引的语法

我们可以使用 DROP INDEX 语句来删除索引。

DROP INDEX 的语法：

DROP INDEX index_name ON table_name;

• DROP INDEX: 用于删除索引的关键字。
• index_name: 指定要删除的索引的名称。
• ON table_name: 指定要在哪个表上删除索引。

使用 ALTER TABLE 语句删除索引的语法如下：

ALTER TABLE table_name

DROP INDEX index_name;

• ALTER TABLE: 用于修改表结构的关键字。
• table_name: 指定要修改的表的名称。
• DROP INDEX: 用于删除索引的子句。
• index_name: 指定要删除的索引的名称。

以下实例假设我们有一个名为 employees 的表，并在 age 列上有一个名为 idx_age 的索引，现在我们要删除这个索引：

DROP INDEX idx_age ON employees;

或使用 ALTER TABLE 语句：

ALTER TABLE employees

DROP INDEX idx_age;

这两个命令都会从 employees 表中删除名为 idx_age 的索引。

如果该索引不存在，执行命令时会产生错误。因此，在删除索引之前最好确认该索引是否存在，或者使用错误处理机制来处理可能的错误情况。

---

唯一索引

在 MySQL 中，你可以使用 CREATE UNIQUE INDEX 语句来创建唯一索引。

唯一索引确保索引中的值是唯一的，不允许有重复值。

创建索引

CREATE UNIQUE INDEX index_name

ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• CREATE UNIQUE INDEX: 用于创建唯一索引的关键字组合。
• index_name: 指定要创建的唯一索引的名称。索引名称在表中必须是唯一的。
• table_name: 指定要在哪个表上创建唯一索引。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

以下是一个创建唯一索引的实例： 假设我们有一个名为 employees的 表，包含 id 和 email 列，现在我们想在email列上创建一个唯一索引，以确保每个员工的电子邮件地址都是唯一的。

CREATE UNIQUE INDEX idx_email ON employees (email);

以上实例将在 employees 表的 email 列上创建一个名为 idx_email 的唯一索引。

修改表结构

我们可以使用 ALTER TABLE 命令来创建唯一索引。

ALTER TABLE命令允许你修改已经存在的表结构，包括添加新的索引。

ALTER table mytable ADD UNIQUE [indexName] (columnName(length));

• ALTER TABLE: 用于修改表结构的关键字。
• table_name: 指定要修改的表的名称。
• ADD CONSTRAINT: 这是用于添加约束（包括唯一索引）的关键字。
• index_name: 指定要创建的唯一索引的名称。约束名称在表中必须是唯一的。
• UNIQUE (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

以下是一个使用 ALTER TABLE 命令创建唯一索引的实例：假设我们有一个名为 employees 的表，包含 id 和 email 列，现在我们想在 email 列上创建一个唯一索引，以确保每个员工的电子邮件地址都是唯一的。

ALTER TABLE employees

ADD CONSTRAINT idx_email UNIQUE (email);

以上实例将在 employees 表的 email 列上创建一个名为 idx_email 的唯一索引。

请注意，如果表中已经有重复的 email 值，那么添加唯一索引将会失败。在创建唯一索引之前，你可能需要确保表中的 email 列没有重复的值。

创建表的时候直接指定

我们也可以在创建表的同时，你可以在 CREATE TABLE 语句中使用 UNIQUE 关键字来创建唯一索引。

这将在表创建时同时定义唯一索引约束。

CREATE TABLE 语句中创建唯一索引的语法：

CREATE TABLE table_name (

   column1 data_type,

   column2 data_type,

   ...,

   CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...)

);

• CREATE TABLE: 用于创建新表的关键字。
• table_name: 指定要创建的表的名称。
• (column1, column2, ...): 定义表的列名和数据类型。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• CONSTRAINT: 用于添加约束的关键字。
• index_name: 指定要创建的唯一索引的名称。约束名称在表中必须是唯一的。
• UNIQUE (column1, column2, ...): 指定要索引的表列名。

以下是一个在创建表时创建唯一索引的实例：假设我们要创建一个名为 employees 的表，其中包含 id、name 和 email 列，我们希望 email 列的值是唯一的，因此我们要在创建表时定义唯一索引。

CREATE TABLE employees (

      id INT PRIMARY KEY,

     name VARCHAR(50),

     email VARCHAR(100) UNIQUE

);

在这个例子中，email 列被定义为唯一索引，因为在它的后面加上了 UNIQUE 关键字。

请注意，使用 UNIQUE 关键字后，索引名称将自动生成，你也可以根据需要指定索引名称。

---

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

以下实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

---

使用 ALTER 命令添加和删除主键

主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;

mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

---

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。

可以通过添加 \G 来格式化输出信息。

SHOW INDEX 语句：

mysql> SHOW INDEX FROM table_name\G

........

• SHOW INDEX: 用于显示索引信息的关键字。
• FROM table_name: 指定要查看索引信息的表的名称。
• \G: 格式化输出信息。

执行上述命令后，将会显示指定表中所有索引的详细信息，包括索引名称（Key_name）、索引列（Column_name）、是否是唯一索引（Non_unique）、排序方式（Collation）、索引的基数（Cardinality）等。

MySQL优化

1、explain 输出执行计划

在select语句前加上explain就可以了(MySQL 5.6开始，不仅仅支持select )能够简单分析sql的执行情况，是否走索引等。

• type列，连接类型。一个好的SQL语句至少要达到range级别。从最好到最差的连接类型为const、eq_reg、- ref、range、index和ALL。一般来说，得保证查询至少达到range级别（范围扫描），最好能达到ref（索引访问）。

• key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。

• key_len列，索引长度。

• rows列，扫描行数。该值是个预估值。

• extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

更多相关关内容联合索引、索引失效、索引下推优化、回表、覆盖盖索引等问题可参看：MySQL慢查询是怎么回事、MySQL索引

2、in 和 not in 要慎用

SQL语句中IN包含的值不应过多，MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

再或者使用连接来替换。

当IN的取值范围较大时会导致索引失效，走全表扫描。By the way：如果使用了 not in，则不走索引

3、少用select *

SELECT语句务必指明字段名称，select * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；

4、善用limit 1

这是为了使explain中type列达到const类型。当只需要一条数据的时候，使用limit 1，如果加上limit1，查找到就不用继续往后找了。

5、 order by字段建索引

避免全表扫描，首先应考虑在 where 及 order by涉及的列上建立索引，如果排序字段没有用到索引，就尽量少排序
可以在程序中排序。

6、count(*)推荐使用

count()、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。
count() 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()，推荐使用count()

7、where 子句中避免is null /is not null

应尽量避免在 where 子句中对字段进行 null 值判断，使用is null 或者is not null 理论上都会走索引,存在Null值会导致mysql优化器处理起来比较复杂，容易导致引擎放弃使用索引而进行全表扫描。

select id from t where num is null

所以设计表字段时尽量避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。

8、应尽量避免在 where!=或<>

子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

9、应尽量避免在 where 子句中使用 or

如果使用or那么要求or两边的条件字段都要有索引,才会走索引,如果其中一边有一个字段没索引, 另一个字段上的索引也会失效。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。

应尽量避免在 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

10、尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

11、应尽量避免在where子句中对字段进行函数操作

select id from t where substring(name,1,3)=‘abc’

不要在子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

12、可以选择 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)

13、避免%xxx式查询

在非覆盖索引场景下,大家知道MySQL索引有最左原则,所以通过 like '%xx%'查询的时候一定会造成索引失效(5.7版本覆盖索引可以走索引),一般采用like 'xx%'右边匹配的方式来索引。

当想要获取的字段多了以后，select * from t_user where name like “%xx”; 要查询的数据就不能只在索引树里找了，得需要回表操作才能完成查询的工作，再加上是左模糊匹配，无法利用索引树的有序性来快速定位数据，所以得在索引树逐一遍历，获取主键值后，再到聚簇索引树检索到对应的数据行，这样实在太累了。
优化器认为上面这样的查询过程的成本实在太高了，所以直接选择全表扫描的方式来查询数据。

所以，使用左模糊匹配（like ‘%xx’）并不一定会走全表扫描，但也容易失效，关键还是看数据表中的字段。

14、选择重复值较低的字段建索引

在创建索引时，一定要选择重复值较低的字段。离散型非常的差，优化器可能直接就选择不走索引了，因为优化器可能认为，走索引和全表扫描差不多。值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段不适合做索引。

15、高效的分页

select id,name,age from user limit 10000, 20;

mysql会查询10020条，然后丢弃前面10000条，这个比较浪费资源
可以优化：

select id,name,age from user id>10000 limit 20;

找到上次分页最大id

16、join使用问题

用连接查询代替子查询、join表不易超过3个、小表驱动大表、链接字段建索引。

17、关于索引本身

使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引。

开窗函数

OVER的定义

OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

OVER的语法

OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )

PARTITION BY 子句进行分组；

ORDER BY 子句进行排序。

窗口函数OVER()指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。

开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。

OVER的用法

OVER开窗函数必须与聚合函数或排序函数一起使用，聚合函数一般指SUM(),MAX(),MIN,COUNT(),AVG()等常见函数。排序函数一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。

OVER在聚合函数中使用的示例

我们以SUM和COUNT函数作为示例来给大家演示。

--建立测试表和测试数据
CREATE TABLE Employee
(
ID INT  PRIMARY KEY,
Name VARCHAR(20),
GroupName VARCHAR(20),
Salary INT
)
INSERT INTO  Employee
VALUES(1,'小明','开发部',8000),
      (4,'小张','开发部',7600),
      (5,'小白','开发部',7000),
      (8,'小王','财务部',5000),
      (9, null,'财务部',NULL),
      (15,'小刘','财务部',6000),
      (16,'小高','行政部',4500),
      (18,'小王','行政部',4000),
      (23,'小李','行政部',4500),
      (29,'小吴','行政部',4700);

SUM后的开窗函数

SELECT *,
     SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资,
     SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资,
     SUM(Salary) OVER(ORDER BY ID) 累计工资,
     SUM(Salary) OVER() 总工资
from Employee

(提示：可以左右滑动代码)

结果如下：

其中开窗函数的每个含义不同，我们来具体解读一下：

只对PARTITION BY后面的列Groupname进行分组，分组后求解Salary的和。

SUM(Salary) OVER (PARTITION BY Groupname)

对PARTITION BY后面的列Groupname进行分组，然后按ORDER BY 后的ID进行排序，然后在组内对Salary进行累加处理。

SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)

只对ORDER BY 后的ID内容进行排序，对排完序后的Salary进行累加处理。

SUM(Salary) OVER (ORDER BY ID)

对Salary进行汇总处理

SUM(Salary) OVER ()

COUNT后的开窗函数

SELECT *,
       COUNT(*) OVER(PARTITION BY Groupname ) 每个组的个数,
       COUNT(*) OVER(PARTITION BY Groupname ORDER BY ID) 每个组的累积个数,
       COUNT(*) OVER(ORDER BY ID) 累积个数 ,
       COUNT(*) OVER() 总个数
from Employee

返回的结果如下图：

OVER在排序函数中使用的示例

我们对4个排序函数一一演示

--先建立测试表和测试数据
WITH t AS
(SELECT 1 StuID,'一班' ClassName,70 Score
UNION ALL
SELECT 2,'一班',85
UNION ALL
SELECT 3,'一班',85
UNION ALL
SELECT 4,'二班',80
UNION ALL
SELECT 5,'二班',74
UNION ALL
SELECT 6,'二班',80
)
SELECT * INTO Scores FROM t;
SELECT * FROM Scores

ROW_NUMBER()

定义：ROW_NUMBER()函数作用就是将SELECT查询到的数据进行排序，每一条数据加一个序号，他不能用做于学生成绩的排名，一般多用于分页查询，比如查询前10个 查询10-100个学生。ROW_NUMBER()必须与ORDER BY一起使用，否则会报错。 

对学生成绩排序

SELECT *,
ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 班内排序,
ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序
FROM Scores;

结果如下：

这里的PARTITION BY和ORDER BY的作用与我们在上面看到的聚合函数的作用一样，都是用来进行分组和排序使用的。

此外ROW_NUMBER()函数还可以取指定顺序的数据。

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序
FROM Scores
) t WHERE t.总排序=2;

结果如下：

RANK() 

定义：RANK()函数，顾名思义排名函数，可以对某一个字段进行排名，这里和ROW_NUMBER()有什么不一样呢？ROW_NUMBER()是排序，当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同，而Rank()则不一样。如果出现相同的，他们的排名是一样的。下面看例子:

示例

SELECT ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;

SELECT RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;

结果：

其中上图是ROW_NUMBER()的结果，下图是RANK()的结果。当出现两个学生成绩相同是里面出现变化。RANK()是1-1-3-3-5-6，而ROW_NUMBER()则还是1-2-3-4-5-6，这就是RANK()和ROW_NUMBER()的区别了。

DENSE_RANK() 

定义：DENSE_RANK()函数也是排名函数，和RANK()功能相似，也是对字段进行排名，那它和RANK()到底有什么不同那？特别是对于有成绩相同的情况，DENSE_RANK()排名是连续的，RANK()是跳跃的排名，一般情况下用的排名函数就是RANK() 我们看例子：

示例

SELECT 
RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;

SELECT 
DENSE_RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;

结果如下：

上面是RANK()的结果，下面是DENSE_RANK()的结果

NTILE()

定义：NTILE()函数是将有序分区中的行分发到指定数目的组中，各个组有编号，编号从1开始，就像我们说的'分区'一样 ，分为几个区，一个区会有多少个。  

SELECT *,NTILE(1) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;

SELECT *,NTILE(2) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;

SELECT *,NTILE(3) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;

结果如下：

就是将查询出来的记录根据NTILE函数里的参数进行平分分区。