MySQL优化总结

一、mysql架构图

                  （图片来源https://www.cnblogs.com/luxiaoxun/p/4694144.html）

 二、mysql引擎概述

1）MyISAM存储引擎

MyISAM存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件。首先肯定会有任何存储引擎都不可缺少的存放表结构定义信息的.frm文件，另外还有.MYD和.MYI文件，分别存放了表的数据（.MYD）和索引数据（.MYI）。每个表都有且仅有这样三个文件做为MyISAM存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI文件中。

MyISAM支持以下三种类型的索引：

1、B-Tree索引

B-Tree索引，顾名思义，就是所有的索引节点都按照balancetree的数据结构来存储，所有的索引数据节点都在叶节点。

2、R-Tree索引

R-Tree索引的存储方式和b-tree索引有一些区别，主要设计用于为存储空间和多维数据的字段做索引，所以目前的MySQL版本来说，也仅支持geometry类型的字段作索引。

3、Full-text索引

Full-text索引就是我们长说的全文索引，他的存储结构也是b-tree。主要是为了解决在我们需要用like查询的低效问题。

MyISAM不支持事务、也不支持外键，但其访问速度快，对事 务完整性没有要求

2）Innodb 存储引擎

1、支持事务安装

2、数据多版本读取

3、锁定机制的改进

4、实现外键

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事 务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

3）NDBCluster存储引擎

NDB存储引擎也叫NDBCluster存储引擎，主要用于MySQLCluster分布式集群环境，Cluster是MySQL从5.0版本才开始提供的新功能。

4）Merge存储引擎

MERGE存储引擎，在MySQL用户手册中也提到了，也被大家认识为MRG_MyISAM引擎。Why？因为MERGE存储引擎可以简单的理解为其功能就是实现了对结构相同的MyISAM表，通过一些特殊的包装对外提供一个单一的访问入口，以达到减小应用的复杂度的目的。要创建MERGE表，不仅仅基表的结构要完全一致，包括字段的顺序，基表的索引也必须完全一致。

5）Memory存储引擎

Memory存储引擎，通过名字就很容易让人知道，他是一个将数据存储在内存中的存储引擎。Memory存储引擎不会将任何数据存放到磁盘上，仅仅存放了一个表结构相关信息的.frm文件在磁盘上面。所以一旦MySQLCrash或者主机Crash之后，Memory的表就只剩下一个结构了。Memory表支持索引，并且同时支持Hash和B－Tree两种格式的索引。由于是存放在内存中，所以Memory都是按照定长的空间来存储数据的，而且不支持BLOB和TEXT类型的字段。Memory存储引擎实现页级锁定。

6）BDB存储引擎

BDB存储引擎全称为BerkeleyDB存储引擎，和Innodb一样，也不是MySQL自己开发实现的一个存储引擎，而是由SleepycatSoftware所提供，当然，也是开源存储引擎，同样支持事务安全。

7）FEDERATED存储引擎

FEDERATED存储引擎所实现的功能，和Oracle的DBLINK基本相似，主要用来提供对远程MySQL服务器上面的数据的访问接口。如果我们使用源码编译来安装MySQL，那么必须手工指定启用FEDERATED存储引擎才行，因为MySQL默认是不起用该存储引擎的。

8）ARCHIVE存储引擎

ARCHIVE存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不支持索引，通过一个.frm的结构定义文件，一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。由于其所存放的数据的特殊性，ARCHIVE表不支持删除，修改操

作，仅支持插入和查询操作。锁定机制为行级锁定。

9）BLACKHOLE存储引擎

BLACKHOLE存储引擎是一个非常有意思的存储引擎，功能恰如其名，就是一个“黑洞”。就像我们unix系统下面的“/dev/null”设备一样，不管我们写入任何信息，都是有去无回。

10）CSV存储引擎

CSV存储引擎实际上操作的就是一个标准的CSV文件，他不支持索引。起主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，而CSV文件是很多软件都支持的一种较为标准的格式，所以我们可以通过先在数据库中建立一张CVS表，然后将生成的报表信息插入到该表，即可得到一份CSV报表文件了。

如何选择表的存储引擎？

①如果你的应用是不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择

②一般来说，如果需要事务支持，并且有较高的并发读写 频率，InnoDB是不错的选择。

③Heap 存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法 用户的在线状态.) 

 三、MySQL性能优化如何实现

关于如何对mysql进行优化，可以从以下几个方面进行：

• 数据库表设计的合理化，符合三范式（3NF）的要求
• 添加适当的索引：普通索引、主键索引、唯一值索引、全文索引
• 查询语句的优化
• MYSQL配置优化
• 读写分离
• 使用存储过程，模块化编程，提高速度
• 使用表分割技术（水平分割、垂直分割）
• MySQL服务器硬件升级
• 定时进行碎片整理（MyIsam引擎）

1、数据库表设计符合三范式

（1）第一范式：即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF。

（2）第二范式：即表中的记录是唯一的，没有重复的记录。

（3）第三范式：即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放。注意：反3NF ，可能没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。

2、query语句的优化

 （1）需求：取出某个group（假设id为1）下的用户编号（id），用户昵称（nick_name），并按照加入组的时间（user_group.gmt_create）来进行倒序排列，取出前20个。

解决方案一：

SELECT id,nick_name FROM user,user_group WHERE user_group.group_id=1 and user_group.user_id=user.id ORDER BY user_group.gmt_create desc limit 100,20;

 解决方案二：

SELECT user.id,user.nick_name FROM(
SELECT user_id
FROM user_group
WHERE user_group.group_id=1
ORDER BY gmt_create desc
limit 100,20)t,user
WHERE t.user_id=user.id;

通过比较两个解决方案的执行计划，我们可以看到第一中解决方案中需要和user表参与Join的记录数MySQL通过统计数据估算出来是31156，也就是通过user_group表返回的所有满足group_id=1的记录数（系统中的实际数据是20000）。而第二种解决方案的执行计划中，user表参与Join的数据就只有20条，两者相差很大，我们认为第二中解决方案应该明显优于第一种解决方案。

（2）ORDER BY的实现与优化

优化Query语句中的ORDER BY的时候，尽可能利用已有的索引来避免实际的排序计算，可以很大幅度的提升ORDER BY操作的性能。

优化排序：

1.加大max_length_for_sort_data参数的设置；

2.去掉不必要的返回字段；

3.增大sort_buffer_size参数设置；

（3）GROUP BY的实现与优化

由于GROUP BY实际上也同样需要进行排序操作，而且与ORDER BY相比，GROUP BY主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY的实现过程中，与ORDER BY一样也可以利用到索引。

（4）DISTINCT的实现与优化

DISTINCT实际上和GROUP BY的操作非常相似，只不过是在GROUP BY之后的每组中只取出一条记录而已。所以，DISTINCT的实现和GROUP BY的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成DISTINCT的时候，MySQL只能通过临时表来完成。但是，和GROUP BY有一点差别的是，DISTINCT并不需要进行排序。也就是说，在仅仅只是DISTINCT操作的Query如果无法仅仅利用索引完成操作的时候，MySQL会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行filesort操作。

（5）慢查询优化

①通过show status命令了解各种SQL的执行频率。

②定位执行效率较低的SQL语句-（重点select）

③通过explain分析低效率的SQL语句的执行情况

④确定问题并采取相应的优化措施

• show  status

MySQL客户端连接成功后，通过使用show [session|global] status 命令可以提供服务器状态信息。其中的session来表示当前的连接的统计结果，global来表示自数据库上次启动至今的统计结果。默认是session级别的。

重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

还有几个常用的参数便于用户了解数据库的基本情况。
                  Connections：试图连接MySQL服务器的次数
                  Uptime：服务器工作的时间（单位秒）
                 Slow_queries：慢查询的次数 (默认是慢查询时间10s)

        在默认情况下mysql不记录慢查询日志，需要在启动的时候指定

                binmysqld.exe - -safe-mode  - -slow-query-log [mysql5.5 可以在my.ini指定]

                binmysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

通过慢查询日志定位执行效率较低的SQL语句。慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句。

 

show variables like ‘long_query_time’;
           set long_query_time=2;

• 使用explain分析SQL问题

                Explain select * from emp where ename=“zrlcHd”
                会产生如下信息：
                       select_type:表示查询的类型。
                       table:输出结果集的表
                      type:表示表的连接类型
                      possible_keys:表示查询时，可能使用的索引
                      key:表示实际使用的索引
                      key_len:索引字段的长度
                      rows:扫描出的行数(估算的行数)
                      Extra:执行情况的描述和说明

3、添加适当的索引

（1）索引类型

          主键索引，主键自动的为主索引 (类型Primary)

         唯一索引 (UNIQUE)

         普通索引 (INDEX)

        全文索引与停止词  (FULLTEXT) [适用于MyISAM]，sphinx + 中文分词    coreseek [sphinx 的中文版 ]

        create index 索引名 on 表名(列1，列2);

• 建立索引

        create [UNIQUE|FULLTEXT]  index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);
       alter table table_name ADD INDEX [index_name]  (index_col_name,...)

  添加主键(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 联合主键

• 删除索引

   DROP INDEX index_name ON tbl_name;
   alter table table_name drop index index_name;

   删除主键(索引)比较特别: alter table t_b drop primary key;

• 查询索引(均可)

   show index(es) from table_name;
   show keys from table_name;
  desc table_Name;

（2）在那些列上适合使用索引

• 较频繁的作为查询条件字段应该创建索引
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
• 更新非常频繁的字段不适合创建索引
• 不会出现在WHERE子句中字段不该创建索引

即变更不频繁的，但被频繁作为查询条件的，可以出现在where子句中的字段。

（3）索引不会使用的情况

• 如果条件中有or，即使其中有条件带索引也不会使用。
• 对于多列索引，不是使用的第一部分，则不会使用索引。
• like查询是以%开头
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’)
• 如果mysql估计使用全表扫描要比使用索引快，则不使用索引

4、存储过程的使用

这里推荐一篇https://blog.csdn.net/boonya/article/details/8592342

5、事务优化

•  脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
•  不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
•  幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

Innodb在事务隔离级别方面支持的信息如下：

• READ UNCOMMITTED

常被成为Dirty Reads（脏读），可以说是事务上的最低隔离级别：在普通的非锁定模式下SELECT的执行使我们看到的数据可能并不是查询发起时间点的数据，因而在这个隔离度下是非Consistent Reads（一致性读）；

• READ COMMITTED

这一隔离级别下，不会出现DirtyRead，但是可能出现Non-RepeatableReads(不可重复读)和PhantomReads（幻读）。

•  REPEATABLE READ

REPEATABLE READ隔离级别是InnoDB默认的事务隔离级。在REPEATABLE READ隔离级别下，不会出现DirtyReads，也不会出现Non-Repeatable Read，但是仍然存在PhantomReads的可能性。

• SERIALIZABLE

SERIALIZABLE隔离级别是标准事务隔离级别中的最高级别。设置为SERIALIZABLE隔离级别之后，在事务中的任何时候所看到的数据都是事务启动时刻的状态，不论在这期间有没有其他事务已经修改了某些数据并提交。所以，SERIALIZABLE事务隔离级别下，PhantomReads也不会出现。

6、数据库参数配置

• 最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大

　　innodb_additional_mem_pool_size = 64M

　　innodb_buffer_pool_size =1G

• 对于myisam，需要调整key_buffer_size

  当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数

• 在my.ini修改端口3306，默认存储引擎和最大连接数

7、水平分割和垂直分割

源自https://www.cnblogs.com/luxiaoxun/p/4694144.html

• 数据的垂直切分

数据的垂直切分，也可以称之为纵向切分。将数据库想象成为由很多个一大块一大块的“数据块”（表）组成，我们垂直的将这些“数据块”切开，然后将他们分散到多台数据库主机上面。这样的切分方法就是一个垂直（纵向）的数据切分。

垂直切分的优点

◆数据库的拆分简单明了，拆分规则明确；

◆应用程序模块清晰明确，整合容易；

◆数据维护方便易行，容易定位；

• 垂直切分的缺点

◆部分表关联无法在数据库级别完成，需要在程序中完成；

◆对于访问极其频繁且数据量超大的表仍然存在性能瓶颈，不一定能满足要求；

◆事务处理相对更为复杂；

◆切分达到一定程度之后，扩展性会遇到限制；

◆过读切分可能会带来系统过渡复杂而难以维护。

• 数据的水平切分

数据的垂直切分基本上可以简单的理解为按照表按照模块来切分数据，而水平切分就不再是按照表或者是功能模块来切分了。一般来说，简单的水平切分主要是将某个访问极其平凡的表再按照某个字段的某种规则来分散到多个表之中，每个表中包含一部分数据。

• 水平切分的优点

◆表关联基本能够在数据库端全部完成；

◆不会存在某些超大型数据量和高负载的表遇到瓶颈的问题；

◆应用程序端整体架构改动相对较少；

◆事务处理相对简单；

◆只要切分规则能够定义好，基本上较难遇到扩展性限制；

• 水平切分的缺点

◆切分规则相对更为复杂，很难抽象出一个能够满足整个数据库的切分规则；

◆后期数据的维护难度有所增加，人为手工定位数据更困难；

◆应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。

 

关于MySQL优化原理的介绍，写的不错！https://www.cnblogs.com/zishengY/p/6892345.html