什么影响了数据库查询速度

1.1 影响数据库查询速度的四个因素

1.2 风险分析

QPS： QueriesPerSecond 意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。

TPS： 是 TransactionsPerSecond 的缩写，也就是事务数/秒。它是软件测试结果的测量单位。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务个数。[网站性能测试指标详解]更多看这篇文章。

常用的网站性能测试指标有：吞吐量、并发数、响应时间、性能计数器等。

并发数

并发数是指系统同时能处理的请求数量，这个也是反应了系统的负载能力。

响应时间

响应时间是一个系统最重要的指标之一，它的数值大小直接反应了系统的快慢。响应时间是指执行一个请求从开始到最后收到响应数据所花费的总体时间。

吞吐量

吞吐量是指单位时间内系统能处理的请求数量，体现系统处理请求的能力，这是目前最常用的性能测试指标。

QPS（每秒查询数）、TPS（每秒事务数）是吞吐量的常用量化指标，另外还有 HPS（每秒 HTTP 请求数）。

跟吞吐量有关的几个重要是：并发数、响应时间。

QPS（TPS），并发数、响应时间它们三者之间的关系是：

QPS（TPS）= 并发数/平均响应时间

性能计数器

性能计数器是描述服务器或操作系统性能的一些数据指标，如使用内存数、进程时间，在性能测试中发挥着“监控和分析”的作用，尤其是在分析统统可扩展性、进行新能瓶颈定位时有着非常关键的作用。

Linux 中可以使用 top 或者 uptime 命令看到当前系统的负载及资源利用率情况。

资源利用率：指系统各种资源的使用情况，如 CPU 占用率为 68%，内存占用率为 55%，一般使用“资源实际使用/总的资源可用量”形成资源利用率。

所以，一个网站优化的目的即是，最大限度的利用好服务器硬件资源提升资源利用率，减少用户请求的响应时间，提高系统吞吐量，提高系统并发数。

Tips： 最好不要在主库上数据库备份，大型活动前取消这样的计划。

1. 效率低下的 SQL：超高的 QPS 与 TPS。
2. 大量的并发：数据连接数被占满（ max_connection 默认 100，一般把连接数设置得大一些）。

并发量：同一时刻数据库服务器处理的请求数量

1. 超高的 CPU 使用率：CPU 资源耗尽出现宕机。
2. 磁盘 IO：磁盘 IO 性能突然下降、大量消耗磁盘性能的计划任务。解决：更快磁盘设备、调整计划任务、做好磁盘维护。

1.3 网卡流量：如何避免无法连接数据库的情况

• 1. 减少从服务器的数量（从服务器会从主服务器复制日志）
• 2、进行分级缓存（避免前端大量缓存失效）
• 3、避免使用 select 进行查询
• 4、分离业务网络和服务器网络

1.4 大表带来的问题（重要）

1.4.1 大表的特点

• 1. 记录行数巨大，单表超千万
• 1. 表数据文件巨大，超过 10 个 G

1.4.2 大表的危害

• 1. 慢查询：很难在短时间内过滤出需要的数据

查询字区分度低 -> 要在大数据量的表中筛选出来其中一部分数据会产生大量的磁盘 io -> 降低磁盘效率

• 2.对 DDL 影响：

建立索引需要很长时间：

MySQL-v<5.5 建立索引会锁表 MySQL-v>=5.5 建立索引会造成主从延迟（ MySQL 建立索引，先在组上执行，再在库上执行）

**修改表结构需要长时间的锁表：**会造成长时间的主从延迟('480 秒延迟')

1.4.3 如何处理数据库上的大表

分库分表把一张大表分成多个小表

难点：

• 1. 分表主键的选择
• 1. 2、分表后跨分区数据的查询和统计

1.5 大事务带来的问题（重要）

1.5.1 什么是事务

事务是数据库系统区别于其他一切文件系统的重要特性之一

事务是一组具有原子性的 SQL 语句，或是一个独立的工作单元

事务要求符合：原子性、一致性、隔离性、持久性

1.5.2 事务的 ACID 属性

• 1. 原子性（ atomicity)：全部成功，全部回滚失败。银行存取款。
• 1. 一致性（consistent)：银行转账的总金额不变。
• 1. 3.隔离性（isolation)：

隔离性等级：

未提交读( READ UNCOMMITED)脏读，两个事务之间互相可见；已提交读(READ COMMITED)符合隔离性的基本概念，一个事务进行时，其它已提交的事物对于该事务是可见的，即可以获取其它事务提交的数据。可重复读( REPEATABLE READ)InnoDB 的默认隔离等级。事务进行时，其它所有事务对其不可见，即多次执行读，得到的结果是一样的！可串行化（SERIALIZABLE） 在读取的每一行数据上都加锁，会造成大量的锁超时和锁征用，严格数据一致性且没有并发是可使用。

**查看系统的事务隔离级别：**show variables like'%iso%';**开启一个新事务：**begin;**提交一个事务：**commit;**修改事物的隔离级别：**setsession tx_isolation='read-committed';推荐：面试问烂的 MySQL 四种隔离级别，看完吊打面试官！关注 Java 技术栈微信公众号，在后台回复关键字：MySQL，可以获取更多栈长整理的 MySQL 技术干货。

• 1. 持久性( DURABILITY)：从数据库的角度的持久性，磁盘损坏就不行了

redolog 机制保证事务更新的一致性和持久性

1.5.3 大事务

运行时间长，操作数据比较多的事务；

风险：锁定数据太多，回滚时间长，执行时间长。

• 1. 锁定太多数据，造成大量阻塞和锁超时；
• 1. 回滚时所需时间比较长，且数据仍然会处于锁定；
• 1. 如果执行时间长，将造成主从延迟，因为只有当主服务器全部执行完写入日志时，从服务器才会开始进行同步，造成延迟。关注 Java 技术栈微信公众号，在后台回复关键字：MySQL，可以获取更多栈长整理的 MySQL 技术干货。

解决思路：

• 1. 避免一次处理太多数据，可以分批次处理；
• 1. 移出不必要的 SELECT 操作，保证事务中只有必要的写操作。

什么影响了 MySQL 性能（非常重要）

2.1 影响性能的几个方面

• 1. 服务器硬件。
• 1. 服务器系统（系统参数优化）。
• 1. 存储引擎。MyISAM：不支持事务，表级锁。InnoDB: 支持事务，支持行级锁，事务 ACID。
• 1. 数据库参数配置。
• 1. ** **数据库结构设计和 SQL 语句。（重点优化）

2.2 MySQL 体系结构

分三层：客户端-> 服务层-> 存储引擎

• 1. MySQL 是插件式的存储引擎，其中存储引擎分很多种。只要实现符合 MySQL 存储引擎的接口，可以开发自己的存储引擎！ 2、所有跨存储引擎的功能都是在服务层实现的。
• 1. MySQL 的存储引擎是针对表的，不是针对库的。也就是说在一个数据库中可以使用不同的存储引擎。但是不建议这样做。

2.3 InnoDB 存储引擎

MySQL5.5 及之后版本默认的存储引擎：InnoDB。

2.3.1 InnoDB 使用表空间进行数据存储。

show variables like'innodb_file_per_table

如果 innodbfileper_table 为 ON 将建立独立的表空间，文件为 tablename.ibd；

如果 innodbfileper_table 为 OFF 将数据存储到系统的共享表空间，文件为 ibdataX（X 为从 1 开始的整数）；

.frm：是服务器层面产生的文件，类似服务器层的数据字典，记录表结构。

2.3.2 (MySQL5.5 默认)系统表空间与( MySQL5.6 及以后默认)独立表空间

• 1. 系统表空间无法简单的收缩文件大小，造成空间浪费，并会产生大量的磁盘碎片。
• 1. 独立表空间可以通过 optimeze table 收缩系统文件，不需要重启服务器也不会影响对表的正常访问。
• 1. 如果对多个表进行刷新时，实际上是顺序进行的，会产生 IO 瓶颈。
• 1. 独立表空间可以同时向多个文件刷新数据。

强烈建立对 Innodb 使用独立表空间，优化什么的更方便，可控。

2.3.3 系统表空间的表转移到独立表空间中的方法

• 1. 使用 mysqldump 导出所有数据库数据（存储过程、触发器、计划任务一起都要导出 ）可以在从服务器上操作。
• 1. 停止 MySQL 服务器，修改参数（my.cnf 加入 innodbfileper_table），并删除 Inoodb 相关文件（可以重建 Data 目录）。
• 1. 重启 MySQL，并重建 Innodb 系统表空间。
• 1. 重新导入数据。

或者 Altertable 同样可以的转移，但是无法回收系统表空间中占用的空间。

2.4 InnoDB 存储引擎的特性

2.4.1 特性一：事务性存储引擎及两个特殊日志类型：Redo Log 和 Undo Log

• 1. Innodb 是一种事务性存储引擎。
• 1. 完全支持事务的 ACID 特性。3、支持事务所需要的两个特殊日志类型：RedoLog 和 UndoLog

**Redo Log：**实现事务的持久性(已提交的事务)。**Undo Log：**未提交的事务，独立于表空间，需要随机访问，可以存储在高性能 io 设备上。

Undo 日志记录某数据被修改前的值，可以用来在事务失败时进行 rollback；Redo 日志记录某数据块被修改后的值，可以用来恢复未写入 data file 的已成功事务更新的数据。

2.4.2 特性二：支持行级锁

• 1. InnoDB 支持行级锁。
• 1. 行级锁可以最大程度地支持并发。
• 1. 行级锁是由存储引擎层实现的。

2.5 什么是锁

2.5.1 锁

锁的主要作用是管理共享资源的并发访问

锁用于实现事务的隔离性

2.5.2 锁类型

2.5.3 锁的粒度

MySQL 的事务支持不是绑定在 MySQL 服务器本身，** **而是与存储引擎相关

• 表级锁
• 行级锁

将 table_name 加表级锁命令：locktable table_name write;写锁会阻塞其它用户对该表的‘读写’操作，直到写锁被释放：unlock tables；

• 1. 锁的开销越大，粒度越小，并发度越高。
• 2、表级锁通常是在服务器层实现的。
• 3、行级锁是存储引擎层实现的。innodb 的锁机制，服务器层是不知道的

2.5.4 阻塞和死锁

• 1. 阻塞是由于资源不足引起的排队等待现象。
• 1. 死锁是由于两个对象在拥有一份资源的情况下申请另一份资源，而另一份资源恰好又是这两对象正持有的，导致两对象无法完成操作，且所持资源无法释放。

2.6 如何选择正确的存储引擎

参考条件：

• 1. 事务
• 2、备份( Innobd 免费在线备份)
• 3、崩溃恢复
• 4、存储引擎的特有特性

**总结：**nnodb 大法好。**注意：**别使用混合存储引擎，比如回滚会出问题在线热备问题。

2.7 配置参数

2.7.1 内存配置相关参数

确定可以使用的内存上限。

内存的使用上限不能超过物理内存，否则容易造成内存溢出；（对于 32 位操作系统，MySQL 只能试用 3G 以下的内存。[37 个 MySQL 数据库小技巧，]推荐看一下。关注 Java 技术栈微信公众号，在后台回复关键字：MySQL，可以获取更多栈长整理的 MySQL 技术干货。

37 个 MySQL 数据库小技巧

无论是运维、开发、测试，还是架构师，数据库技术是一个必备加薪神器，那么，一直说学习数据库、学 MySQL，到底是要学习它的哪些东西呢？

1、如何快速掌握 MySQL?

培养兴趣

兴趣是最好的老师，不论学习什么知识，兴趣都可以极大地提高学习效率。当然学习 MySQL 5.6 也不例外。

夯实基础

计算机领域的技术非常强调基础，刚开始学习可能还认识不到这一点，随着技术应用的深 入，只有有着扎实的基础功底，才能在技术的道路上走得更快、更远。对于 MySQL 的学习来说， SQL 语句是其中最为基础的部分，很多操作都是通过 SQL 语句来实现的。所以在学习的过程中， 读者要多编写 SQL 语句，对于同一个功能，使用不同的实现语句来完成，从而深刻理解其不同之处。

及时学习新知识

正确、有效地利用搜索引擎，可以搜索到很多关于 MySQL 5.6 的相关知识。同时，参考别 人解决问题的思路，也可以吸取别人的经验，及时获取最新的技术资料。

多实践操作

数据库系统具有极强的操作性，需要多动手上机操作。在实际操作的过程中才能发现问题， 并思考解决问题的方法和思路，只有这样才能提高实战的操作能力。

2、如何选择服务器的类型？

MySQL 服务器配置窗口中各个参数的含义如下。
【Server Configuration Type】该选项用于设置服务器的类型。单击该选项右侧的向下按钮， 即可看到包括 3 个选项。

3 个选项的具体含义如下：

• Development Machine(开发机器)：该选项代表典型个人用桌面工作站。假定机器上运行 着多个桌面应用程序。将 MySQL 服务器配置成使用最少的系统资源。
• Server Machine (服务器)：该选项代表服务器，MySQL 服务器可以同其它应用程序一起 运行，例如 FTP、email 和 Web 服务器。MySQL 服务器配置成使用适当比例的系统资源。
• DedicatedMySQL Server Machine (专用 MySQL 服务器）：该选项代表只运行 MySQL 服务的服务器。假定运行没有运行其它应用程序。MySQL 服务器配置成使用所有可用系统资源。作为初学者，建议选择【DevelopmentMachine】（开发者机器）选项，这样占用系统的资源 比较少。

3、如何选择存储引擎

不同存储引擎都有各自的特点，以适应不同的需求，如下表所示。为了做出选择：

• 首先需 要考虑每一个存储引擎提供了哪些不同的功能。如果要提供提交，回滚和崩溃恢复能力的事务安全（ACID 兼容）能力，并要求实现并发控 制，InnoDB 是个很好的选择。如果数据表主要用来插入和查询记录，则 MyISAM 引擎能提供较 高的处理效率；如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的 Memory 引擎，MySQL 中使用该引擎作为临时表，存放查询的中间结果。如果只有 INSERT 和 SELECT 操作，可以选择 Archive 引擎，Archive 存储引擎支持高并发的插 入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信 息可以使用 Archive 引擎。
• 使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求。
• 使用合适的存储引擎，将会提高整个数据库的性能。

4、如何查看默认存储引擎？

使用 SHOW ENGINES 语句查看系统中所有的存储引擎，其中包括默认的存储引擎。可以看出来当前数据库系统中有五种存储引擎，默认是 MyISAM。还可以使用一种直接的方法查看默认存储引擎。执行结果直接显示了当前默认的存储引擎为 MyISAM。

5、表删除操作须谨慎

表删除操作将把表的定义和表中的数据一起删除，并且 MySQL 在执行删除操作时，不会有 任何的确认信息提示，因此执行删除操时，应当慎重。在删除表前，最好对表中的数据进行备份， 这样当操作失误时，可以对数据进行恢复，以免造成无法挽回的后果。

同样的，在使用 ALTER TABLE 进行表的基本修改操作时，在执行操作过程之前，也应该 确保对数据进行完整的备份，因为数据库的改变是无法撤销的，如果添加了一个不需要的字段， 可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。

6、每个表中都要有一个主键吗？

并不是每一个表中都需要主键，一般的，如果多个表之间进行连接操作时，需要用到主键。 因此并不需要为每个表建立主键，而且有些情况最好不使用主键。

7、每个表都可以任意选择存储引擎吗？

外键约束（FOREIGN KEY)不能跨引擎使用。MySQL 支持多种存储引擎，每一个表都可 以指定一个不同的存储引擎，但是要注意：外键约束是用来保证数据的参照完整性，如果表之间 需要关联外键，却指定了不同的存储引擎，这些表之间是不能创建外键约束的。所以说，存储引 擎的选择也不完全是随意的。

8、带 AUTO_INCREMENT 约束的字段值是从 1 开始的吗？

默认的，在 MySQL 中，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。设置自增属性（AUTO_INCREMENT)的时候，还可以指定第一条插入记录的自增字段的 值，这样新插入的记录的自增字段值从初始值开始递增，如在 tb_emp8 中插入第一条记录，同时 指定 id 值为 5，则以后插入的记录的 id 值就会从 6 开始往上增加。添加唯一性的主键约束时， 往往需要设置字段自动增加属性。

9、TIMESTAMP 与 DATATIME 两者的区别

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别就是： DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关； 而 TIMESTAMP 值的存储是以 UTC(世界标准时间）格式保存的，存储时对当前时区进行转换， 检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

10、选择数据类型的方法和技巧是什么？

MySQL 提供了大量的数据类型，为了优化存储，提高数据库性能，在任何情况下均应使用 最精确的类型。即在所有可以表示该列值的类型中，该类型使用的存储最少。

整数和浮点数

如果不需要小数部分，则使用整数来保存数据；如果需要表示小数部分，则使用浮点数类 型。对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。例如如果列的值的范 围为 1〜99999，若使用整数，则 MEDIUMINT UNSIGNED 是最好的类型；若需要存储小数，则 使用 FLOAT 类型。浮点类型包括 FLOAT 和 DOUBLE 类型。DOUBLE 类型精度比 FLOAT 类型高，因此，如要求存储精度较高时，应选择 DOUBLE 类型。

浮点数和定点数

浮点数 FLOAT，DOUBLE 相对于定点数 DECIMAL 的优势是：在长度一定的情况下，浮点 数能表示更大的数据范围。但是由于浮点数容易产生误差，因此对精确度要求比较高时，建议使 用 DECIMAL 来存储。DECIMAL 在 MySQL 中是以字符串存储的，用于定义货币等对精确度要 求较高的数据。在数据迁移中，float(M,D)是非标准 SQL 定义，数据库迁移可能会出现问题，最 好不要这样使用。另外两个浮点数进行减法和比较运算时也容易出问题，因此在进行计算的时候， 一定要小心。如果进行数值比较，最好使用 DECIMAL 类型。

日期与时间类型

MySQL 对于不同种类的日期和时间有很多的数据类型，比如 YEAR 和 TIME。如果只需要 记录年份，则使用 YEAR 类型即可；如果只记录时间，只须使用 TIME 类型。如果同时需要记录日期和时间，则可以使用 TIMESTAMP 或者 DATETIME 类型。由于 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，因此存储范围较大的日期最好使用 DATETIME。TIMESTAMP 也有一个 DATETIME 不具备的属性。默认的情况下，当插入一条记录但并没 有指定 TIMESTAMP 这个列值时，MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要 插入记录同时插入当前时间时，使用 TIMESTAMP 是方便的，另外 TIMESTAMP 在空间上比 DATETIME 更有效。

CHAR 与 VARCHAR 之间的特点与选择

CHAR 和 VARCHAR 的区别：

• CHAR 是固定长度字符，VARCHAR 是可变长度字符；CHAR 会自动删除插入数据的尾部 空格，VARCHAR 不会删除尾部空格。
• CHAR 是固定长度，所以它的处理速度比 VARCHAR 的速度要快，但是它的缺点就是浪费 存储空间。所以对存储不大，但在速度上有要求的可以使用 CHAR 类型，反之可以使用 VARCHAR 类型来实现。

存储引擎对于选择 CHAR 和 VARCHAR 的影响：

• 对于 MyISAM 存储引擎：最好使用固定长度的数据列代替可变长度的数据列。这样可以使 整个表静态化，从而使数据检索更快，用空间换时间。
• 对于 InnoDB 存储引擎：使用可变长度的数据列，因为 InnoDB 数据表的存储格式不分固定 长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照 实际的长度存储，比较节省空间，所以对磁盘 I/O 和数据存储总量比较好。

ENUM 和 SET

ENUM 只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有 65 535 个成员。因此，在需要从多个值中选取一个时，可以使用 ENUM。比如：性别字段适合定义为 ENUM 类型，每次只能从’男’或’女’中取一个值。SET 可取多值。它的合法取值列表最多允许有 64 个成员。

空字符串也是一个合法的 SET 值。 在需要取多个值的时候，适合使用 SET 类型，比如：要存储一个人兴趣爱好，最好使用 SET 类型。ENUM 和 SET 的值是以字符串形式出现的，但在内部，MySQL 以数值的形式存储它们。

BLOB 和 TEXT

BLOB 是二进制字符串，TEXT 是非二进制字符串，两者均可存放大容量的信息。BLOB 主 要存储图片、音频信息等，而 TEXT 只能存储纯文本文件。应分清两者的用途。点击[这里]总结了 55 道去 BAT 面试的 MySQL 面试题。

11、MySQL 中如何使用特殊字符？

诸如单引号（’），双引号（"），反斜线（)等符号，这些符号在 MySQL 中不能直接输入 使用，否则会产生意料之外的结果。在 MySQL 中，这些特殊字符称为转义字符，在输入时需要 以反斜线符号（’\’）开头，所以在使用单引号和双引号时应分别输入（\’）或者（")，输入反 斜线时应该输入（)，其他特殊字符还有回车符（\r)，换行符（\n)，制表符（\tab)，退格 符（\b)等。在向数据库中插入这些特殊字符时，一定要进行转义处理。

12、MySQL 中可以存储文件吗？

MySQL 中的 BLOB 和 TEXT 字段类型可以存储数据量较大的文件，可以使用这些数据类型 存储图像、声音或者是大容量的文本内容，例如网页或者文档。虽然使用 BLOB 或者 TEXT 可 以存储大容量的数据，但是对这些字段的处理会降低数据库的性能。如果并非必要，可以选择只 储存文件的路径。

13、MySQL 中如何执行区分大小写的字符串比较？

在 Windows 平台下，MySQL 是不区分大小的，因此字符串比较函数也不区分大小写。如果 想执行区分大小写的比较，可以在字符串前面添加 BINARY 关键字。例如默认情况下，’a’=‘A’ 返回结果为 1，如果使用 BINARY 关键字，BINARY’a’=‘A’结果为 0，在区分大小写的情况下，’a’ 与’A’并不相同。

14、如何从日期时间值中获取年、月、日等部分日期或时间值？

MySQL 中，日期时间值以字符串形式存储在数据表中，因此可以使用字符串函数分别截取日期时间值的不同部分，例如某个名称为 dt 的字段有值“2010-10-01 12:00:30”，如果只需要获 得年值，可以输入 LEFT(dt, 4)，这样就获得了字符串左边开始长度为 4 的子字符串，即 YEAR 部分的值；如果要获取月份值，可以输入 MID(dt,6,2)，字符串第 6 个字符开始，长度为 2 的子 字符串正好为 dt 中的月份值。同理，读者可以根据其他日期和时间的位置，计算并获取相应的值。

15、如何改变默认的字符集？

CONVERT()函数改变指定字符串的默认字符集，在开始的章节中，向读者介绍使用 GUI 图形化安装配置工具进行 MySQL 的安装和配置，其中的一个步骤是可以选择 MySQL 的默认字符集。但是，如果只改变字符集，没有必要把配置过程重新执行一遍，在这里，一个简单的方式是 修改配置文件。在 Windows 中，MySQL 配置文件名称为 my.ini，该文件在 MySQL 的安装目录下面。修改配置文件中的 default-character-set 和 character-set-server 参数值，将其改为想要的字 符集名称，如 gbk、gb2312、latinl 等，修改完之后重新启动 MySQL 服务，即可生效。读者可以在修改字符集时使用 SHOW VARIABLES LIKE ’character_set_°%’;命令查看当前字符集，以进行对比。

16、DISTINCT 可以应用于所有的列吗？

查询结果中，如果需要对列进行降序排序，可以使用 DESC，这个关键字只能对其前面的列 进行降序排列。例如，要对多列都进行降序排序，必须要在每一列的列名后面加 DESC 关键字。

而 DISTINCT 不同，DISTINCT 不能部分使用。换句话说，DISTINCT 关键字应用于所有列而不 仅是它后面的第一个指定列。例如，查询 3 个字段 s_id，f_name，f_price，如果不同记录的这 3 个字段的组合值都不同，则所有记录都会被查询出来。

17、ORDER BY 可以和 LIMIT 混合使用吗？

在使用 ORDER BY 子句时，应保证其位于 FROM 子句之后，如果使用 LIMIT，则必须位 于 ORDER BY 之后，如果子句顺序不正确，MySQL 将产生错误消息。

18、什么时候使用引号？

在查询的时候，会看到在 WHERE 子句中使用条件，有的值加上了单引号，而有的值未加。 单引号用来限定字符串，如果将值与字符串类型列进行比较，则需要限定引号；而用来与数值进 行比较则不需要用引号。

19、在 WHERE 子句中必须使用圆括号吗？

任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确操作顺序。 如果条件较多，即使能确定计算次序，默认的计算次序也可能会使 SQL 语句不易理解，因此使 用括号明确操作符的次序，是一个好的习惯。

20、更新或者删除表时必须指定 WHERE 子句吗？

在前面章节中可以看到，所有的 UPDATE 和 DELETE 语句全都在 WHERE 子句中指定了条 件。如果省略 WHERE 子句，则 UPDATE 或 DELETE 将被应用到表中所有的行。

因此，除非 确实打算更新或者删除所有记录，否则要注意使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。建议在对表进行更新和删除操作之前，使用 SELECT 语句确认需要删除的记录，以免造 成无法挽回的结果。点击[这里]总结了 55 道去 BAT 面试的 MySQL 面试题。

21、索引对数据库性能如此重要，应该如何使用它？

为数据库选择正确的索引是一项复杂的任务。如果索引列较少，则需要的磁盘空间和维护开销 都较少。如果在一个大表上创建了多种组合索引，索引文件也会膨胀很快。

而另一方面，索引较多 可覆盖更多的查询。可能需要试验若干不同的设计，才能找到最有效的索引。可以添加、修改和删 除索引而不影响数据库架构或应用程序设计。因此，应尝试多个不同的索引从而建立最优的索引。

22、尽量使用短索引。

对字符串类型的字段进行索引，如果可能应该指定一个前缀长度。例如，如果有一个 CHAR(255)的列，如果在前 10 个或 30 个字符内，多数值是惟一的，则不需要对整个列进行索引。 短索引不仅可以提高查询速度而且可以节省磁盘空间、减少 I/O 操作。

23、MySQL 存储过程和函数有什么区别？

在本质上它们都是存储程序。函数只能通过 return 语句返回单个值或者表对象；而存储过程 不允许执行 return，但是可以通过 out 参数返回多个值。函数限制比较多，不能用临时表，只能用表变量，还有一些函数都不可用等等；而存储过程的限制相对就比较少。函数可以嵌入在 SQL 语句中使用，可以在 SELECT 语句中作为查询语句的一个部分调用；而存储过程一般是作为一个独立的部分来执行。

24、存储过程中的代码可以改变吗？

目前，MySQL 还不提供对已存在的存储过程代码的修改，如果必须要修改存储过程，必须使用 DROP 语句删除之后，再重新编写代码，或者创建一个新的存储过程。

25、存储过程中可以调用其他存储过程吗？

存储过程包含用户定义的 SQL 语句集合，可以使用 CALL 语句调用存储过程，当然在存储 过程中也可以使用 CALL 语句调用其他存储过程，但是不能使用 DROP 语句删除其他存储过程。

26、存储过程的参数不要与数据表中的字段名相同。

在定义存储过程参数列表时，应注意把参数名与数据库表中的字段名区别开来，否则将出 现无法预期的结果。

27、存储过程的参数可以使用中文吗？

一般情况下，可能会出现存储过程中传入中文参数的情况，例如某个存储过程根据用户的 名字查找该用户的信息，传入的参数值可能是中文。这时需要在定义存储过程的时候，在后面加 上 character set gbk，不然调用存储过程使用中文参数会出错，比如定义 userInfo 存储过程，代码 如下：

CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT)

28、MySQL 中视图和表的区别以及联系是什么？

两者的区别：

• (1)视图是已经编译好的 SQL 语句，是基于 SQL 语句的结果集的可视化的表，而表不是。
• (2)视图没有实际的物理记录，而基本表有。
• (3)表是内容，视图是窗口。
• (4)表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它 进行修改，但视图只能用创建的语句来修改。
• (5)视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来说，视图可以防止用户接触数据表，因而用户不知道表结构。
• (6)表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
• (7)视图的建立和删除只影响视图本身，不影响对应的基本表。

两者的联系：

• 视图（view)是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有记录） 都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也
• 可以对应多个基本 表。视图是基本表的抽象和在逻辑意义上建立的新关系。

29、使用触发器时须特别注意。

在使用触发器的时候需要注意，对于相同的表，相同的事件只能创建一个触发器，比如对 表 account 创建了一个 BEFORE INSERT 触发器，那么如果对表 account 再次创建一个 BEFORE INSERT 触发器，MySQL 将会报错，此时，只可以在表 account 上创建 AFTER INSERT 或者 BEFORE UPDATE 类型的触发器。灵活的运用触发器将为操作省去很多麻烦。点击[这里]总结了 55 道去 BAT 面试的 MySQL 面试题。

30、及时删除不再需要的触发器。

触发器定义之后，每次执行触发事件，都会激活触发器并执行触发器中的语句。如果需求 发生变化，而触发器没有进行相应的改变或者删除，则触发器仍然会执行旧的语句，从而会影响 新的数据的完整性。因此，要将不再使用的触发器及时删除。

31、应该使用哪种方法创建用户？

创建用户有几种方法：GRANT 语句、CREATE USER 语句和直接操作 user 表。一般情况， 最好使用 GRANT 或者 CREATE USER 语句，而不要直接将用户信息插入 user 表，因为 user 表中存储了全局级别的权限以及其他的账户信息，如果意外破坏了 user 表中的记录，则可能会对 MySQL 服务器造成很大影响。

32、mysqldump 备份的文件只能在 MySQL 中使用吗？

mysqldump 备份的文本文件实际是数据库的一个副本，使用该文件不仅可以在 MySQL 中恢 复数据库，而且通过对该文件的简单修改，可以使用该文件在 SQL Server 或者 Sybase 等其他数 据库中恢复数据库。这在某种程度上实现了数据库之间的迁移。

33、如何选择备份工具？

直接复制数据文件是最为直接、快速的备份方法，但缺点是基本上不能实现增量备份。备 份时必须确保没有使用这些表。如果在复制一个表的同时服务器正在修改它，则复制无效。备份 文件时，最好关闭服务器，然后重新启动服务器。为了保证数据的一致性，需要在备份文件前， 执行以下 SQL 语句：

CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT)

目录下即可。mysqlhotcopy 是一个 PERL 程序，它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件所在的 机器上，并且 mysqlhotcopy 只能用于备份 MyISAM 表。

mysqlhotcopy 适合于小型数据库的备份， 数据量不大，可以使用 mysqlhotcopy 程序每天进行一次完全备份。mysqldump 将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适， 这也是最常用的备份方法。mysqldump 比直接复制要慢些。也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证复制过程中不会有新的 数据写入。这种方法备份出来的数据恢复也很简单，直接复制回原来的数据库

34、平时应该打开哪些日志？

日志既会影响 MySQL 的性能，又会占用大量磁盘空间。因此，如果不必要，应尽可能少地 开启日志。根据不同的使用环境，可以考虑开启不同的日志。例如，在开发环境中优化查询效率 低的语句，可以开启慢查询日志；如果需要记录用户的所有查询操作，可以开启通用查询日志； 如果需要记录数据的变更，可以开启二进制日志；错误日志是默认开启的。

35、如何使用二进制日志？

二进制日志主要用来记录数据变更。如果需要记录数据库的变化，可以开启二进制日志。 基于二进制日志的特性，不仅可以用来进行数据恢复，还可用于数据复制。

在数据库定期备份的情况下，如果出现数据丢失，可以先用备份恢复大部分数据，然后使用二进制日志恢复最近备份 后变更的数据。在双机热备情况下，可以使用 MySQL 的二进制日志记录数据的变更，然后将变 更部分复制到备份服务器上。

36、如何使用慢查询日志？

慢查询日志主要用来记录查询时间较长的日志。在开发环境下，可以开启慢查询日志来记 录查询时间较长的查询语句，然后对这些语句进行优化。通过配 long_query_time 的值，可以灵活地掌握不同程度的慢查询语句。

37、是不是索引建立得越多越好？

合理的索引可以提高查询的速度，但不是索引越多越好。在执行插入语句的时候，MySQL 要为新插入的记录建立索引。所以过多的索引会导致插入操作变慢。原则上是只有查询用的字段 才建立索引。

确定 MySQL 的每个连接 ****单独使用的内存。

sort_buffer_size

定义了每个线程排序缓存区的大小，MySQL 在有查询、需要做排序操作时才会为每个缓冲区分配内存（直接分配该参数的全部内存）；

join_buffer_size

定义了每个线程所使用的连接缓冲区的大小，如果一个查询关联了多张表，MySQL 会为每张表分配一个连接缓冲，导致一个查询产生了多个连接缓冲；

read_buffer_size

定义了当对一张 MyISAM 进行全表扫描时所分配读缓冲池大小，MySQL 有查询需要时会为其分配内存，其必须是 4k 的倍数；

read_rnd_buffer_size

索引缓冲区大小，MySQL 有查询需要时会为其分配内存，只会分配需要的大小。

**注意：**以上四个参数是为一个线程分配的，如果有 100 个连接，那么需要 ×100。

MySQL 数据库实例：

MySQL 是单进程多线程（而 Oracle 是多进程），也就是说 MySQL 实例在系统上表现就是一个服务进程，即进程；

MySQL 实例是线程和内存组成，实例才是真正用于操作数据库文件的；

一般情况下一个实例操作一个或多个数据库；集群情况下多个实例操作一个或多个数据库。

如何为缓存池分配内存：

Innodb_buffer_pool_size

定义了 Innodb 所使用缓存池的大小，对其性能十分重要，必须足够大，但是过大时，使得 Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中；

总内存-（每个线程所需要的内存*连接数）- 统保留内存

key_buffer_size

定义了 MyISAM 所使用的缓存池的大小，由于数据是依赖存储操作系统缓存的，所以要为操作系统预留更大的内存空间；

select sum(index_length) from information_schema.talbes where engine='myisam'

**注意：**即使开发使用的表全部是 Innodb 表，也要为 MyISAM 预留内存，因为 MySQL 系统使用的表仍然是 MyISAM 表。

max_connections

控制允许的最大连接数， 一般 2000 更大。不要使用外键约束保证数据的完整性

2.8 性能优化顺序

• 库结构设计和 SQL 语句
• 数据库存储引擎的选择和参数配置
• 系统选择及优化
• 硬件升级

🐶 你走，我不送你。你来，风雨无阻，我去接你。