mysql数据库优化及sql调优_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. 如果优化效果不明显，重复第四步操作

从以上步骤会发现执行计划explain很多参数都是面向索引的，说明索引对SQL优化是有很大影响的，那么，什么是索引呢？

数据库索引：
索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。
索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

索引从实现上来说，分成两种：聚集索引(聚簇索引)和辅助索引(也叫二级索引或者非聚集索引、非聚簇索引)

聚簇索引：按照每张表的主键构造一棵B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚簇索引的叶子节点称为数据页。
聚簇索引将数据存储与索引放到了一块，找到索引也就找到了数据。

辅助索引：在聚簇索引之上创建的索引称之为辅助索引，辅助索引叶子节点存放的不再是行的数据，而是主键值。
辅助索引将数据存储与索引分开，索引结构的叶子节点指向了数据的对应行。

一般建表会用一个自增主键做聚簇索引，没有的话MySQL会默认创建，但是这个主键如果更改的话代价较高，故自增ID不能频繁update。
我们日常工作中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了寻找主键索引的二级索引，找到主键索引再通过主键索引找数据。

从功能上来说，分为六种：
普通索引：最基本的索引，没有任何约束
唯一索引：与普通索引类似，但具有唯一性约束
主键索引：特殊的唯一索引，不允许有空值
复合索引：将多个列组合在一起创建索引，可以覆盖多个列
外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性，完整性和实现级联操作
全文索引：MySQL全文索引在5.7之前只支持英文，5.7之后内置了ngram全文检索插件，用来支持中文分词，对MyISAM和InnoDB引擎都有效，不过一般更倾向使用全文索引引擎(ES，Solr)
注：主键就是唯一索引，但是唯一索引不一定是主键，唯一索引可以为空，但是空值只能有一个，主键不能为空。

设置主键时，会自动生成一个唯一索引，如果之前没有聚集索引，那么主键就是聚集索引。
没有设置主键时，会选择一个不为空的唯一索引作为聚集索引，如果还没有，那就生成一个隐式的6字节的索引。

关于索引的内容暂且到此，本文以数据库优化为核心，具体的底层原理在《MySQL架构体系》一文中详细阐述

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. 创建索引的原则

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

事务优化

什么是数据库事务？
事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。
事务是逻辑上的一组操作，要么都执行，要么都不执行。

数据库事务的四大特性
ACID
原子性:事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用
一致性:执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的
隔离性:并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
持久性:一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

脏读、幻读、不可重复读
脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

事务的隔离级别
为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别:
READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别

隔离级别与锁的关系
在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

通常，对于绝大多数的应用程序来说，可以优先考虑将数据库系统的隔离级别设置为读已提交，这能够避免脏读的同时保证较好的并发性能。
尽管这种事务隔离级别会导致不可重复读、虚读和第二类丢失更新等并发问题。
但较为科学的做法是在可能出现这类问题的个别场合，由应用程序主动采取悲观锁或乐观锁来进行事务控制。

表结构优化

1. 数据库表优化

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

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

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

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

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

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