08 SQL优化

以你之姓@ 2023-03-05 03:09 1阅读 0赞

#### 1. MySQL优化方式有哪些？ ####

1.  开启查询缓存，优化查询；
2.  explain你的select查询，这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你，你的索引主键被如何利用的，你的数据表是如何被搜索和排序的；
3.  当只要一行数据时使用 limit 1，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据；
4.  为搜索字段建索引；
5.  使用 ENUM 而不是 VARCHAR，如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是VARCHAR；
6.  Prepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击；
7.  垂直分表；
8.  选择正确的存储引擎。
9.  语句优化，注意索引失效。
10. 分库分表

#### 2. 在数据库中查询语句速度很慢，如何优化？ ####

1.  建索引。
2.  减少表之间的关联。
3.  优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引,把数据 量大的表排在前面。
4.  简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据。
5.  尽量用PreparedStatement来查询，不要用Statement。

#### 3. 数据库数据量非常大怎么进行设计优化。 ####

*  表的设计具体注意的问题  
    在建表选择数据类型时选择更小的 (占用更少的磁盘) 、更简单的 (如能选择整形就不选字符串) 、避免使用NULL（为什么呢）。为了提高查找效率，可以增加冗余列。

1.  数据行的长度不要超过8020字节，如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片，降低查询效率。
2.  能够用数字类型的字段尽量选择数字类型而不用字符串类型的（电话号码），这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
3.  对于不可变字符类型char和可变字符类型varchar 都是8000字节,char查询快，但是耗存储空间，varchar查询相对慢一些但是节省存储空间。在设计字段的时候可以灵活选择，例如用户名、密码等长度变化不大的字段可以选择CHAR，对于评论等长度变化大的字段可以选择VARCHAR。
4.  字段的长度在最大限度的满足可能的需要的前提下，应该尽可能的设得短一些，这样可以提高查询的效率，而且在建立索引的时候也可以减少资源的消耗。

*  查询的优化

1.  保证在实现功能的基础上，尽量减少对数据库的访问次数；
2.  通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；
3.  能够分开的操作尽量分开处理，提高每次的响应速度；
4.  在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；
5.  不要过多地使用通配符如：SELECT \* FROM T1 语句，要用到几列就选择几列
6.  加缓存，memcached,redis；
7.  主从复制或主主复制，读写分离；

#### 4. 讲一讲数据库的慢查询？ ####

MySQL中的日志包括：错误日志、二进制日志、通用查询日志、慢查询日志等等。  
MySQL慢查询日志记录下所有执行超过long\_query\_time时间的SQL语句，帮你找到执行慢的SQL，方便我们对这些SQL进行优化。

#### 5. 如何做慢 SQL 优化？ ####

首先用 explain 分析语句的执行计划，查看使用索引的情况，是不是查询没走索引，如果可以加索引解决，优先采用加索引解决。  
分析语句，看看是否存在一些导致索引失效的用法，是否加载了额外的数据，是否加载了许多结果中并不需要的列，对语句进行分析以及优化。  
如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行垂直拆分或者水平拆分。

#### 6. explain执行计划 ####

explain 的作用是分析查询语句。它的字段有：

*  id：标识符
 *  select\_type：查询的类型（simple）
 *  table：输出结果集的表
 *  partitions：匹配的分区
 *  **type**：表的连接类型。从好到坏的顺序是：（const > eq\_ref >） ref > range > index > ALL。
    
     *  ref：通过普通索引查询，并且使用的等号查询。
     *  range：索引的范围查找（>=、<、in 等）。
     *  index：全索引扫描。
     *  All：全表扫描
 *  possible\_keys：查询时，可能使用的索引
 *  **key**：实际使用的索引
 *  key\_len：使用的索引字段的长度
 *  ref：列与索引的比较
 *  **rows**：估计要检查的行数
 *  filtered：按表条件过滤的行百分比（全查到的话，就显示为100）
 *  **Extra**：附加信息（using index 表示查询时不需要回表查询，直接通过索引就可以获取查询的数据）。需要观察是否有一些影响性能的指标，比如 using filesort，它在排序时无法使用索引时就会出现。

#### 7. 索引失效的原因?索引无法使用的原因 ####

1.  在索引列上使用函数或者运算，索引就无法使用了。需要建立函数索引就可以解决了。
2.  新建的表还没来得及生成统计信息，分析一下就好了
3.  基于cost的成本分析，访问的表过小，使用全表扫描的消耗小于使用索引。
4.  使用<>、not in 、not exist，对于这三种情况大多数情况下认为结果集很大，一般大于5%-15%就不走索引而走FTS。
5.  单独的>、<。
6.  like “%\_” 百分号在前。
7.  单独引用复合索引里非第一位置的索引列。
8.  字符型字段为数字时在where条件里不添加引号。
9.  当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
10. 索引失效，可以考虑重建索引，rebuild online。
11. B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走、联合索引 is not null 只要在建立的索引列（不分先后）都会走。