面试题：谈谈你对Mysql数据库优化的见解

数据库优化大体分为四个方面，一是数据库服务器的优化，二是架构的设计层面的优化，三是my.cnf配置的优化，四是sql语句的优化。

一. 数据库服务器的优化

1. 服务器硬件方面：影响Mysql性能因素主要有 CPU、可用内存大小、磁盘读写速度、网络带宽等；

2. 操作系统方面：影响Mysql性能因素主要有 应用文件句柄数、系统的网络配置等；

这部分的优化一般由DBA和运维工程师去完成，在硬件资源的优化中，我们重点关注的应该是服务本身所承载的体量，然后提出合理的指标要求避免出现资源浪费的想象。

二. 架构的设计层面的优化

Mysql是一个磁盘IO访问特别频繁的关系型数据库，在高并发和高性能的场景中，Mysql必然会承受巨大的并发压力，优化方式如下：

1. 搭建Mysql主从集群：单个Mysql服务容易导致单点故障，一旦服务宕机将会导致依赖Mysql数接库的应用全部无法响应，主从集群可以保证服务的高可用性；

2. 读写分离设计：在读多写少的场景中，通过读写分离的方案可以去避免读写冲突，导致性能问题；

3. 分库分表机制：通过分库可以降低单个服务器节点的IO压力，通过分表的方式可以降低单表数据量从而提升sql的查询效率。

三. my.cnf配置的优化

四. sql语句的优化

常见提问：你有没有sql优化的实际经验，或者是你对sql优化有没有自己的见解？

答：后期在项目维护的时候，对sql进行过优化，(实际优化例子阐述)，通过对sql的优化，也有一些自己的理解，主要是如下几点：

1. 使用缓存优化查询：

(1) 原理：进行多次相同的查询，结果就会放入缓存中，后续在进行同样的查询，就会从缓存中查询，不会到表中提取数据了。

(2) 避免缓存失效：查询的sql不能变，也就是说sql语句的大小写不同、多空格、少空格、不确定数据等都会算做不同的sql，会导致缓存失效。

为什么？

MySql在实现缓存方面也采用用了Key-Value的方式，就是将SELECT语句和该查询语句的结果集做了一个HASH映射并保存在一定的内存区域中。

(3) 缓存数据失效时机：

1) 表的结构或数据发生改变：在表的结构或数据发生改变时，查询缓存中的数据不再有效，有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效，所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用；

2) 写入数据时缓存失效：当某个表正在写入数据，则这个表的缓存(命中检查,缓存写入等)将会处于失效状态，在Innodb中，如果某个事务修改了表,则这个表的缓存在事务提交前都会处于失效状态，在这个事务提交前，这个表的相关查询都无法被缓存。

4) Mysql会出现内存泄漏问题：

Mysql一般是不会出现这中情况的，如果出现了，一般都是自己的参数设置问题，需要在参数设置和设计上尽量合理，或者去官方bug里memory leak查找，是否存在修复的版本等等，本人暂时未遇到过该情况，如需了解可自行百度。

(3) 优点：提高sql的查询效率

(4) 缺点：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，因此很可能费劲地把结果存起来，还没使用，就被一个更新全清空了，对于更新压力大的数据库来说，查询缓存的命中率会非常低，除非你的业务就是有一张静态表，很长时间才会更新一次，所以对于某些写频繁的系统，开启Query Cache功能可能并不能让系统性能有提升，有时反而会有下降。

所以需要根据实际情况选择使用mysql自带缓存功能还是使用像redis等第三方缓存。

查询缓存的使用方法可以查看 Mysql之查询缓存介绍_BestandW1shEs_lsy的博客-CSDN博客

2. explain查看sql的执行计划：

用法：explain select ......；

explain的详细使用方法可以查看 Mysql之快速上手sql优化_BestandW1shEs_lsy的博客-CSDN博客

3. 给搜索的字段建立索引：

给哪些字段：

where后面一些常用字段建立索引，但是并不是所有的字段都适合建立索引，如果sql筛选出来的结果集只占表的少部分，就适合建立索引，如果查询出来的数据太大，超过了表的50%以上，就不适合建立索引，因为当数据量达到很大的时候，查询的速度就很慢，这是因为当数据量达到一定量的时候就会走全表扫描，这是因为mysql的内部的一个优化器进行了最优策略。

4. limit X 的使用：

在明确知道只有一条数据的时候，可以使用limit 1，明确知道有五行数据，使用limit 5等，如下图所示：

5. 永久连接：

数据库再创建完一个连接之后，会永久处于连接状态，apache在极端的环境中，它进行的每一个http请求都会创建子进程去请求数据库，到时候如果连接太多后，会增加数接库的负担，该问题是属于架构的问题，比如大型网站的访问，或者是恶意频繁请求数据等情况。

6. 选择正确的数据库引擎：

mysql中有两个数据库引擎的，myISAM 和 innoBD。

(1) myISAM适合大数据量的查询，但是对于一些插入的操作并不友好，因为在进行update的时候会进行表锁，此时如果还有用户在读表，查询是无效的，必须等到插入操作完成后，才可以去读数据；

(2) innoDB对于插入操作比较友好，但是对查询操作不是特别友好，它是一个行锁。

7. 在进行大量数据的delete或insert的时候：

该情况也是会经常遇到，该问题可能会导致数接库服务器宕机，因为在进行大量数据的delete或insert的时候，会将整个表锁起来，此时外部的一些web请求再来，就请求不到该表，当请求达到足够多的时候后，并且此时的执行语句完成，大量的请求就会把数据库搞宕机，并且如过数据量过大，在执行途中中止了该语句的运行，数据库就会回滚操作，可能导致数据库负载变高等影响。

优化方案：需要使用limit去做限制，不要一次性处理全部数据。

8. 数据类型，尽量使用小的：

如果一个硬盘的数据量特别多，不同的数据类型暂用的硬盘空间是不一样的，如果占用硬盘的空间特别小，特别紧凑，那么硬盘的读取速度就会特别快，在大型的公司里，数据库字段类型的选择是有明确的规定的，这样不仅可以节约硬盘空间，而且对数据读取的性能提升也很大。

9. 固定的字段长度：

(1) 优点：从速度来说，处理固定长度字符比可变长度字符的速度快；

用房子打比方简单的说，偏移量是门牌号，数据记录长度是房间数，根据起始位置加上偏移量定位到记录的位置，再从定位到的位置读取记录长度的这么多数据，就把这条记录完整读出来了。

(2) 缺点：从存储空间来说，可变长度字符比固定长度字符节省空间。

10. 尽量不要赋null：

可以用' '，不然容易报空指针的错误。

11. 明确的固定的字段上使用enum：

比如性别、国家、市，这些都是固定的，就可以使用enum，但是有人会选择使用varchar，最好别这么用，因为enum的速度比varchar的速度更快。

enum类型是非常快和紧凑的，实际上，其保存的是 tinyint，但其外表上显示为字符串，这样一来，用这个字段来做一些选项列表变得相当的完美，就比如一个表中存储性别，很多人喜欢存成：男，‘m’，女，'f' ，这时候还不如ENUM性能高，还有一些状态值，例如：新建，审批中，结束，当然中间应该还有其他状态，反正这些用ENUM是最高效的，而且显示的时候，看起来也特别的直观，虽然真实存储tinyint，但是表面是字符呀，这个看的时候特别直观，查询的时候特别高效；

因为无论enum(‘manmaman’,’womanwomanwoman’) 枚举的字符多长，内部都是用整型表示，在内存中产生的数据大小不变，而varchar型，却在内存中产生的数据越来越多。

12. id主键每张表都需要建立：

对于查询性能来说是非常重要的，因为不管通过任何方式去查询表，最终都会通过主键去定位到数据，所有建立主键是可以有效的提高性能的，而且在建主键的时候能不使用varchar，最好不用varchar，因为varchar的查询性能是比不上int的，严谨一点都是使用int，但是大多数都是使用varchar。

13. 避免使用select * ：

原因就是，查询的约多，速度就越慢，数据越多，对网络的传输也会负载过重。

14. 避免使用某些运算函数：

例如，rand() 函数，它的计算是在CPU上进行的，尽量避免使用它，实在必须使用也没办法。

15. 连接两表的时候，join尽量保持两个字段的类型要一致：

因为两个字段的类型不一样，比如一个是int，一个是varchar，join后会导致索引失效。

16. 垂直分割：

将表按列的形式分割成表，如果表中的字段特别多，可以将一张表分为两张表，可以提高访问效率。