Java面试-mysql优化

矫情吗；* 2022-03-01 15:16 184阅读 0赞

##### 优化数据库方法 #####

先说如何用慢查询定位问题。然后再进行索引，调优。

Mysql的优化主要就在于：索引的优化，sql语句的优化，表的优化，在高并发网络环境下，除了优化数据库外，还会涉及到分布式缓存，CDN，数据库读写分离等高并发优化技术。

最好是按照以下顺序优化：

1.SQL语句及索引的优化

2.数据库表结构的优化

3.系统配置的优化

4.硬件的优化

*  表的设计合理化(符合3NF)
 *  添加适当索引(index) \[四种: 普通索引、主键索引、唯一索引unique、全文索引\]

索引优化

1.索引

一般的应用系统，读写比例在10：1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的也是最容易出现问题的，还是一些复杂的查询操作，因此对查询语句的优化是重中之重，加速查询最好的方法就是索引。

索引：简单的说，相当于图书的目录，可以帮助用户快速的找到需要的内容。

在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。

总结：索引的目的在于提高查询效率，与我们查询图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小结，然后找到页数。相似的例子还有：查字典，查地图等。

2.索引类型

普通索引

是最基本的索引，它没有任何限制。

唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

全文索引

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

3.索引优化

只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引

尽量使用短索引，如果可以，应该制定一个前缀长度

对于经常在where子句使用的列，最好设置索引，这样会加快查找速度

对于有多个列where或者order by子句的，应该建立复合索引

对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引

尽量不要在列上进行运算（函数操作和表达式操作）

尽量不要使用not in和<>操作

*  SQL语句优化

> 选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’、’性别’最好适用ENUM

> 使用连接(JOIN)来代替子查询

> 适用联合(UNION)来代替手动创建的临时表

*  事务处理

> 锁定表、优化事务处理

> 适用外键，优化锁定表

*  分表技术(水平分割、垂直分割)
 *  慢查询

MySQL优化概述

MySQL数据库常见的两个瓶颈是：CPU和I/O的瓶颈。

CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。

磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上。

我们可以用mpstat, iostat,  
sar和vmstat来查看系统的性能状态。除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能。

MySQL优化方案

Mysql的优化，大体可以分为三部分：索引的优化，sql语句的优化，表的优化

SQL慢查询的优化

1.如何捕获低效sql

1）slow\_query\_log

这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。

2）ong\_query\_time

当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。

3）slow\_query\_log\_file

记录日志的文件名。

4）log\_queries\_not\_using\_indexes

这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。

2.慢查询优化的基本步骤

1)先运行看看是否真的很慢，注意设置SQL\_NO\_CACHE

2）where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

3)explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

4)order by limit 形式的sql语句让排序的表优先查

5)了解业务方使用场景

6)加索引时参照建索引的几大原则

7)观察结果，不符合预期继续从1开始分析

2.优化原则

查询时，能不要\*就不用\*，尽量写全字段名

大部分情况连接效率远大于子查询

多使用explain和profile分析查询语句

查看慢查询日志，找出执行时间长的sql语句优化

多表连接时，尽量小表驱动大表，即小表 join 大表

在千万级分页时使用limit

对于经常使用的查询，可以开启缓存

数据库表优化

表的字段尽可能用NOT NULL

字段长度固定的表查询会更快

把数据库的大表按时间或一些标志分成小表

将表拆分

数据表拆分：主要就是垂直拆分和水平拆分。

水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询,  
提高效率。

垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。

*  读写\[写: update/delete/add\]分离
 *  存储过 程 \[模块化编程，可以提高速度\]
 *  对mysql配置优化 \[配置最大并发数my.ini, 调整缓存大小 \]
 *  mysql服务器硬件升级
 *  定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

##### SQL优化之道 #####

**1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by  
涉及的列上建立索引。**

**2.应尽量避免在 where 子句中对字段进行 null  
值判断，否则将导致**[引擎][Link 1]**放弃使用索引而进行全表扫描，如：**

**select id from t where num is null**

**可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：**

**select id from t where num=0**

**3.应尽量避免在 where  
子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。**

**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.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**

**6.下面的查询也将导致全表扫描：**

**select id from t where name like '%abc%'**

**7.应尽量避免在 where  
子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：**

**select id from t where num/2=100**

**应改为:**

**select id from t where num=100\*2**

**8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：**

**select id from t where substring(name,1,3)=‘abc’–name以abc开头的id**

**应改为:**

**select id from t where name like 'abc%'**

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

**10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，**

**否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。**

**11.不要写一些没有意义的查询，如需要生成一个空表结构：**

**select col1,col2 into \#t from t where 1=0**

**这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：**

**create table \#t(…)**

**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.并不是所有索引对查询都有效，SQL是根据表**[中数据][Link 2]**来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，**

**如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。**

**14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了  
insert 及 update 的效率，**

**因为 insert 或 update  
时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。**

**一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。**

**15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。**

**这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。**

**16.尽可能的使用 varchar 代替 char  
，因为首先变长字段存储空间小，可以节省存储空间，**

**其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。**

**17.任何地方都不要使用 select \* from t  
，用具体的字段列表代替“\*”，不要返回用不到的任何字段。**

**18.避免频繁创建和删除临时表，以减少系统表资源的消耗。**

**19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。**

**20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替  
create table，避免造成大量 log ，**

**以提高速度；如果数据量不大，为了缓和系统表的资源，应先create  
table，然后insert。**

**21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate  
table ，然后 drop table ，这样可以避免系统表的较长时间锁定。**

**22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。**

**23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。**

**24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST\_FORWARD  
游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。**

**在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。**

**25.尽量避免大事务操作，提高系统并发能力。**

**26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。**

**实际案例分析：拆分大的 DELETE 或INSERT 语句，批量提交SQL语句**

**如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT  
查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。**

**Apache  
会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。**

**如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你的WEB服务崩溃，还可能会让你的整台服务器马上挂了。**

**所以，如果你有一个大的处理，你一定把其拆分，使用 LIMIT  
oracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例：**

<table> 
 <thead> 
  <tr> 
   <th><strong>while(1){ //每次只做1000条 mysql_query(“delete from logs where log_date &lt;= ’2012-11-01’ limit 1000”); if(mysql_affected_rows() == 0){ //删除完成，退出！ break； } //每次暂停一段时间，释放表让其他进程/线程访问。 usleep(50000) }</strong></th> 
  </tr> 
 </thead> 
 <tbody></tbody> 
</table>

[Link 1]: https://www.baidu.com/s?wd=%E5%BC%95%E6%93%8E&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[5.in]: http://5.in
[Link 2]: https://www.baidu.com/s?wd=%E4%B8%AD%E6%95%B0%E6%8D%AE&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd