数据库原理三---MySQL数据库优化

本文主要是介绍数据库原理三---MySQL数据库优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL优化

MySQL优化分为以下几个大类：

1. SQL调优

2. 事务优化

3. 表结构优化

4. 使用缓存和NoSQL数据库方式存储,如MongoDB/Memcached/Redis来缓解高并发下的数据库查询的压力

5. 减少数据库操作次数，尽量使用数据库访问驱动的批处理方法

6. 不常使用的数据迁移备份，避免每次都在海量数据中去检索

SQL调优

数据库调优在一般情况下都是SQL调优，那么，应该如何进行SQL调优呢？

1. 低性能SQL语句定位(找到有问题的SQL语句)
   使用执行计划explain
   执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。
   执行计划包含的信息:
   • id
     由一组数字组成。表示一个查询中各个子查询的执行顺序;id相同执行顺序由上至下，id不同，id值越大优先级越高，越先被执行。id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。
   • select_type
     每个子查询的查询类型，一些常见的查询类型。

　　　type:(非常重要，可以看到有没有走索引) 访问类型
　　　　all 扫描全表数据
　　　　index 遍历索引
　　　　range 索引范围查找
　　　　index_subquery 在子查询中使用 ref
　　　　unique_subquery 在子查询中使用 eq_ref
　　　　ref_or_null 对Null进行索引的优化的 ref
　　　　fulltext 使用全文索引
　　　　ref 使用非唯一索引查找数据
　　　　eq_ref 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。

　　　possible_keys:可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

　　　key:显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。查询中若使用了覆盖索引，则该索引仅出现在key列表中

　　　key_length:索引长度

　　　ref:表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

　　　rows:返回估算的结果集数目，并不是一个准确的值。

　　　extra:执行情况的描述和说明,extra的信息非常丰富，常见的有：
　　　　Using index 使用覆盖索引
　　　　Using where 使用了用where子句来过滤结果集
　　　　Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
　　　　Using temporary 使用了临时表 sql优化的目标可以参考阿里开发手册
　　　注: EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

2. 如果有告警信息，查看告警信息 show warnings

3. 查看SQL涉及的表结构和索引信息

4. 根据执行计划，思考可能的优化点

5. 按照可能的优化点执行表结构变更、增加索引、SQL改写等操作

6. 查看优化后的执行时间和执行计划

7. 如果优化效果不明显，重复第四步操作

SQL改写

1. 使用连接(Join)来代替子查询(Sub-Queries)
   连接之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

2. UNION ALL能满足业务需要不要使用UNION
   UNION会自动压缩多个结果集合中的重复结果，而UNION ALL则将所有的结果全部显示出来，不管是不是重复。

3. WHERE子句尽量避免使用!=或<>操作符
   在WHERE子句中使用!=或<>操作符，查询条件不会使用索引，会进行全表查询。即影响查询效率。

4. WHERE子句使用OR优化
   通常情况我们可以使用UNION ALL 或 UNION的方式替换OR会得到更好的效果。因为WHERE子句中使用了OR，将不会使用索引。

5. WHERE子句使用IN或NOT IN优化
   IN和NOT IN也要慎用，否则可能会导致全表扫描
   可用以下方案替换：

• BETWEEN AND 替换 IN
• EXISTS 替换 IN
• LEFT JOIN 替换 IN

6. WHERE子句使用IS NULL 或IS NOT NULL优化
   在WHERE子句中使用IS NULL或IS NOT NULL判断，索引将被放弃使用，会进行全表查询。

7. 一定不要使用SELECT * FROM

8. WHERE子句避免对字段进行表达式操作

数据库索引优化

1. 索引覆盖与回表查询
   如果要查询的字段都建立过索引，那么索引会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。
   因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。
   （不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。）
   回表查询：先定位主键值，再根据主键值定位行记录
   hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件的时候可以只通过索引完成查询。(具体留《MySQL架构体系》一文中补充，挖坑待填)

2. 创建索引的原则

• 较频繁作为查询条件的字段才去创建索引
• 更新频繁字段不适合创建索引
• 不能有效区分数据的列不适合做索引列(如性别)
• 定义有外键的数据列一定要建立索引
• 尽量扩展索引不要新建索引

事务优化

修改事务的隔离级别,具体查看上文MySQL事务

表结构优化

1. 数据库表优化

• 设计规范化表，消除数据冗余
  数据库三范式
  第一范式：属性(字段)的原子性约束，要求属性具有原子性，不可再分割 　　
  第二范式：记录的惟一性约束，要求记录有惟一标识，每条记录需要有一个属性来做为实体的唯一标识 　　
  第三范式：属性(字段)冗余性的约束，即任何字段不能由其他字段派生出来，在通俗点就是：主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们，当然外键除外)
  满足范式的表，称为规范化表
  如果数据库设计达到了完全的标准化，则把所有的表通过关键字连接在一起时，不会出现任何数据的复本(repetition)。
  标准化的优点是明显的，它避免了数据冗余，自然就节省了空间，也对数据的一致性(consistency)提供了根本的保障，杜绝了数据不一致的现象，同时也提高了效率。

• 适当的冗余，增加计算列
  数据库设计的实用原则是：在数据冗余和处理速度之间找到合适的平衡点
  满足范式的表一定是规范化的表，但不一定是最佳的设计。很多情况下会为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。
  合理的冗余可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。(例如合计、总量这种由其他字段计算出来的列)

• 字段设计优化
  字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：
  A、数据类型尽量用数字型，数字型的比较比字符型的快很多。
  B、数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。
  C、尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。
  D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。
  E、自增字段要慎用，不利于数据迁移

2. 读写分离(解决数据库读性能瓶颈)
   将数据库分为了主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过某种机制进行数据的同步。(用来解决数据库的读性能瓶颈)
   常见的数据库瓶颈：数据容量的瓶颈
   最好的解决办法：数据库水平切分

3. 数据库水平切分(解决数据容量瓶颈)
   通过算法，将数据库进行分割的架构。一个水平切分集群中的每个数据库，通常被称为一个"分片"。每一个分片中的数据没有重合，所有分片中的数据并集组成全部数据。

4. 数据库垂直切分(降低单节点数据库的负载)
   根据业务来拆分数据库，同一类业务的数据表拆分到一个独立的数据库，另一类的数据表拆分到其他数据库。
   垂直切分可以降低单节点数据库的负载，不能解决缩表问题
   通俗来讲：水平拆分行，行数据拆分到不同的表中，垂直拆分列，表数据拆分到不同表中。
   MySQL单表记录超过2000万，读写性能会下降的很快，因此说垂直切分并不能起到缩表的效果。

这篇关于数据库原理三---MySQL数据库优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！