MySQL优化详解

大家好，欢迎来到IT知识分享网。

有 3 种方法可以加快 MySQL 服务器的运行速度，效率从低到高依次为：

1. 升级硬件，比如升级服务器配置，磁盘换为SSD.
2. 对 MySQL 进程的设置进行调优。
3. 对查询进行优化。

升级硬件通常是我们的第一考虑，主要原因是数据库会占用大量资源。不过这种解决方案也就仅限于此了。实际上，您通常可以让CPU或磁盘速度加倍，也可以让内存增大 4 到 8 倍。

第二种方法是对 MySQL 服务器（也称为 mysqld）进行调优。对这个进程进行调优意味着适当地分配内存，并让 mysqld 了解将会承受何种类型的负载。加快磁盘运行速度不如减少所需的磁盘访问次数。类似地，确保 MySQL 进程正确操作就意味着它花费在服务查询上的时间要多于花费在处理后台任务（如处理临时磁盘表或打开和关闭文件）上的时间。对 mysqld 进行调优是本文的重点。

最好的方法是确保查询已经进行了优化。这意味着对表应用了适当的索引，查询是按照可以充分利用 MySQL 功能的方式来编写的。尽管本文并没有包含查询调优方面的内容（很多著作中已经针对这个主题进行了探讨），不过它会配置 mysqld 来报告可能需要进行调优的查询。

虽然已经为这些任务指派了次序，但是仍然要注意硬件和 mysqld 的设置以利于适当地调优查询。机器速度慢也就罢了，我曾经见过速度很快的机器在运行设计良好的查询时由于负载过重而失败，因为 mysqld 被大量繁忙的工作所占用而不能服务查询。

 1. 优化SQL

  1）通过show status了解各种sql的执行频率

         Handler read rnd next：从数据文件中读取行的请求数。如果你在扫描很多表，该值会很大。通常情况下这意味着你的表没有做好索引，或者你的查询语句没有使用好索引字段。

         在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。
       OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
       对于MyISAM表，OPTIMIZE TABLE按如下方式操作：
       如果表已经删除或分解了行，则修复表。
       如果未对索引页进行分类，则进行分类。
       如果表的统计数据没有更新（并且通过对索引进行分类不能实现修复），则进行更新。
   7)  优化 order by orgroup by等

    详细内容：SQL优化大全

2. 优化数据库对象

1）选择表合适存储引擎：

            MyISAM:  应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的.

            Innodb： 事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。（Innodb有效地降低删除和更新导致的锁定）。对于支持事务的InnoDB类型的表来说，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打开也可以），将大大提高性能。

            Memory：数据保存在RAM,快速访问数据。要求表不能太大或者对mysql异常终止后不用恢复数据的

            Merge:      
2）优化表的数据类型,选择合适的数据类型：

           原则：更小通常更好，简单就好，所有字段都得有默认值,尽量避免null：

           例如：数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型.(mediumint就比int更合适)

           比如时间字段：datetime和timestamp, datetime占用8个字节，而timestamp占用4个字节，只用了一半，而timestamp表示的范围是1970—2037适合做更新时间

3) 字符串数据类型：char，varchar，text选择区别
       (1)长度的区别
，char范围是0～255，varchar最长是64k，但是注意这里的64k是整个row的长度，要考虑到其它的column，还有如果存在not null的时候也会占用一位，对不同的字符集，有效长度还不一样，比如utf8的，最多21845，还要除去别的column，但是varchar在一般情况下存储都够用了。如果遇到了大文本，考虑使用text，最大能到4G。 

      (2) 效率来说基本是char>varchar>text，但是如果使用的是Innodb引擎的话，推荐使用varchar代替

     (3)默认值  charchar和varchar可以有默认值，text不能指定默认值

4）MySQL中float数据类型的问题

      (1) .FLOAT或DOUBLE列与具有数值类型的数值进行比较，不能使用等式(=)比较.这个是因为浮点数精度的问题,会产生误差。

  (2)对货币等对精度敏感的数据，应该用定点数表示或存储

      数据库选择合适的数据类型存储还是很有必要的，对性能有一定影响。这里在零碎记录两笔，对于int类型的，如果不需要存取负值，最好加上unsigned；对于经常出现在where语句中的字段，考虑加索引，整型的尤其适合加索引。

 5）在InnoDB数据表设计中，我们需要注意几点：

    1. 显式的定义一个 INT 类型自增字段的主键，这个字段可以仅用于做主键，不做其他用途
    2. 如果不显式定义主键的话，可能会导致InnoDB每次都需要对新数据行进行排序，严重损害性能
    3. 尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能
    4. 如果需要对主键字段进行更新，请将该字段转变成一个唯一索引约束字段，另外创建一个没有其他业务意义的自增字段做主键
    5. 主键字段类型尽可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT
    6. 主键字段放在数据表的第一顺序

3. 优化索引

　　索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(), MIN()和ORDERBY这些命令的时候，性能提高更为明显.

    那该对哪些字段建立索引呢？一般说来，索引应建立在那些将用于JOIN, WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况，例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTER TABLE或CREATE INDEX在以后创建索引

1）. 普通索引

        普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。

2）. 唯一索引

3）. 主索引

        在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的”主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

 4）. 外键索引

         如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

 5）. 复合索引

        索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使用复合索引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。

4. 表锁的问题

     跟性能相关的最重要的区别就是 MyISAM 和 InnoDB 实现的锁机制不一样！ MyISAM 使用的是表锁， 而 InnoDB实现的是行锁。

1) MyISAM为表级锁

        由于MyISAM写进程优先获得锁，使得读锁请求靠后等待队列。不仅如此，即使读请求先到锁等待队列，写请求后 到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。
        如果在大量更新操作的情况下，使得很难获得读锁。从而造成阻塞。
        所以MyIsam不适合做大量更新操作的原因

2 )INNODB的行锁是基于索引实现，如果不通过索引访问数据，Innodb会使用表锁

表级锁更适合以查询为主，只有少量按索引条件更新数据的应用。

行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又并发查询。因为只锁定要操作的行， 所以可以多个线程同时操作不同的行（只要不操作其他线程已经锁定的行）。

 通过show global status like ‘table_locks%’表锁情况 

Table_locks_immediate 表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数。

 如果 Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些. 

5. MySQL server服务器配置优化和内存优化

5.1 影响mysql性能的重要参数：

1、key_buffer_size:

设置索引块的缓存大小：key_buffer_size是对MyISAM表性能影响最大的一个参数

   Key_read_requests：从缓存读取索引的请求次数。
   Key_reads：从磁盘读取索引的请求次数。

一共有 个索引读取请求，有624个请求在内存中没有找到直接从硬盘读取索引。

计算索引未命中缓存的概率： key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% 远小于0.1% 

通常人们认为Key_read_requests / Key_reads越大越好

需要适当加大key_buffer_size 

2、table_open_cache (5.1.3之前是table_cache)

如果Opened_tables数量过大，说明配置中 table_open_cache值可能太小.

3、连接数：max_connections

mysql>show global status like ‘max_used_connections’; 

max_used_connections / max_connections * 100% 的理想值 ≈ 85%

4、thread_cache_size 进程使用情况 

如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器 thread_cache_size配置： 

mysql> show variables like ‘thread_cache_size’;  

mysql> show global status like ‘Thread%’;  

5、查询缓存(query cache) 

show global status like ‘qcache%’;  

mysql> show global status like 'qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 2226 | | Qcache_free_memory |  | | Qcache_hits |  | | Qcache_inserts |  | | Qcache_lowmem_prunes |  | | Qcache_not_cached |  | | Qcache_queries_in_cache | 7168 | | Qcache_total_blocks | 16820 | +-------------------------+----------+ 

6、read_buffer_size 表扫描情况 

show global status like ‘handler_read%’;  

计算表扫描率： 
表扫描率 ＝ Handler_read_rnd_next / Com_select =36520%
如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB。

7、还有innodb_buffer_pool_size等innodb参数的设置

innodb_buffer_pool_size是InnoDB用于缓存数据、索引、锁、插入缓冲、数据字典的缓冲池。该值越大，缓存命中率越高，但是过大会导致页交换。对于innodb表来说，innodb_buffer_pool_size的作用相当于key_buffer_size对于MyISAM表的作用一样。Innodb使用该参数指定大小的内存来缓冲数据和索引。最大可以把该值设置成物理内存的70%~80%。

8、Sort_buffer_size 排序使用情况

当 MySQL 必须要进行排序时，就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。如果要排序的数据太大，那么数据就必须保存到磁盘上的临时文件中，并再次进行排序。如果 sort_merge_passes 状态变量很大，这就指示了磁盘的活动情况。

Sort_merge_passes 包括两步。MySQL 首先会尝试在内存中做排序，使用的内存大小由系统变量 Sort_buffer_size 决定，如果它的大小不够把所有的记录都读到内存中，MySQL 就会把每次在内存中排序的结果存到临时文件中，等 MySQL 找到所有记录之后，再把临时文件中的记录做一次排序。这再次排序就会增加 Sort_merge_passes。实际上，MySQL 会用另一个临时文件来存再次排序的结果，所以通常会看到 Sort_merge_passes 增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增加 Sort_buffer_size 会减少 Sort_merge_passes 和 创建临时文件的次数。但盲目的增加 Sort_buffer_size 并不一定能提高速度，见 How fast can you sort data with MySQL?

在Sort_buffer_size 时要十分谨慎，因为它们在乘以可能存在的连接数时候，这些选项表示大量的内存！

5.2.mysql内存

优化原则
  a. 将尽量多的内存分配给mysql做缓存，但是也要给操作系统和其他程序预留足够内存。

  b. MyISAM的数据文件读取依赖于操作系统的io，因此如果有MyISAM表，就要预留更多的内粗给操作系统做io缓存。

 c. 合理设置排序区、链接区等缓存大小。

MyISAM内存优化
  MyISAM没有特别的缓存机制，完全依赖于操作系统的io缓存

        对于要做排序的MyISAM表查询，如带有order by子句的sql，适当增大read_rnd_buffer_size，来改善性能。它也是每个session独占的。

6. 磁盘IO优化

　　对于我们数据库调优来说，磁盘I/O优化是首屈一指的调优重点，我们都知道木桶原理，短板绝对整体的好坏，而数据库系统中这个短板正是由于我们使用的硬件设备里最弱的磁盘所导致。很多时候，我们会发现系统中I/O累得要死，而CPU却在那里空闲等待，主要是由于I/O执行响应时间太长，处理读写 的速度远远赶落后于CPU的处理速度，这时我们会尽可能的让操作放到内存中进行，由磁盘与CPU的关系，转变成内存与CPU的关系。但是，我们始终不能回 避磁盘I/O的弱点，优化是必须的。

        磁盘搜索是巨大的性能瓶颈。当数据量变得非常大以致于缓存性能变得不可能有效时，该问题变得更加明显。对于大数据库，其中你或多或少地随机访问数据，你可以确 信对读取操作需要至少一次硬盘搜索，写操作需要多次硬盘搜索。要想使该问题最小化， 应使用搜索次数较少的磁盘。

1）使用磁盘阵列  RAID (廉价磁盘冗余阵列)

         3) 数据读写都很频繁，但是可靠性要求不高的可以选择RAID 0

          MYSQL在默认的情况下，数据库和数据表都存放在参数datadir定义的目录下，这样如果不使用RAID或者逻辑卷，所有的数据都存放在一个磁盘设备上，无法发挥多磁盘并 行读写的优势。

         可以将表和数据库从数据库目录移动到其它的位置并且用指向新位置的符号链接进行替换。推荐的方法只需要将数据库通过符号链接指到不同的磁盘。符号链接表仅作为是 最后的办法。

        符号链接一个数据库的方法是，首先在一些有空闲空间的硬盘上创建一个目录，然后从 MySQL 数据目录中创建它的一个符号链接。

      例如：

$ mkdir /dr1/databases/test $ ln -s /dr1/databases/test /path/to/datadir

    表符号链接还不支持以下操作：
    1. ALTER TABLE 忽略 DATA DIRECTORY 和 INDEX DIRECTORY 表选项。
    2. BACKUP TABLE 和 RESTORE TABLE 不考虑符号链接。
    3. .frm 文件必须绝不能是一个符号链接（如前面所述，只有数据和索引文件可以是符链接）。如果试图这样做（例如，生成符号链接）会产生不正确的结果。

3) 禁止操作系统更新文件的atime属性

7. 应用优化

1 )使用连接池
对于访问数据库来说，建立连接的代价比较昂贵，因此，我们有必要建立 ” 连接池 ” 以提高访问的性能。我们可以把连接当作对象或者设备，池中又有许多已经建立的连接，访 问本来需要与数据库的连接的地方，都改为和池相连，池临时分配连接供访问使用，结果返 回后，访问将连接交还。

2)减少对mysql的访问，使用mem缓存等

3)负载均衡，复制分流查询操作
           利用mysql的主从复制，分流更新操作和查询操作
          1), 创建复制账号：Gran replication slave on *.* to ‘rel’@’10.0.1.2’ identified by ”
          2), 修改主服务器的配置my.conf 开启binlog和设置server-id
          3), 将主服务器的数据一致性恢复到从服务器，保证将要复制的数据时一只的，否则出问题
          4), 在从服务器上修改配置my.conf
               server-id=2
               master-host=10.0.1.3
               master-user=’rel’
               master-password=”
               master-port=’3306′

          5), 从服务器启动slave线程： start slave
          show processlist 查看。

4) 分布式cluster 数据库架构

8. 业务优化：分库分表

1）水平划分
    如果某个表的数据太多，预期有上千条甚至上亿以上，我们可以化整为0：拆表。
    这里就涉及到拆表的算法：
    记录日志的表，也可以按周或者按月来拆。
    记录用户信息的表，按用户id的hash算法来拆。

2）垂直拆分
　　如果表记录数并不多，可能也就2、3万条，但是字段却很长，表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。这个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。  

分库解决方案原则：

n安全性拆分：

将高安全性数据与低安全性数据分库，这样的好处第一是便于维护，第二是高安全性数据的数据库参数配置可以以安全优先，而低安全性数据的参数配置以性能优先。参见运维优化相关部分。

n基于业务逻辑拆分

1）根据数据表的内容构成，业务逻辑拆分，便于日常维护和前端调用。

2）基于业务逻辑拆分，可以减少前端应用请求发送到不同数据库服务器的频次，从而减少链接开销。

3）基于业务逻辑拆分，可保留部分数据关联，前端web工程师可在限度范围内执行关联查询。

n基于负载压力拆分

           1）基于负载压力对数据结构拆分，便于直接将负载分担给不同的服务器。

2）基于负载压力拆分，可能拆分后的数据库包含不同业务类型的数据表，日常维护会有一定的烦恼。

n混合拆分组合

1）基于安全与业务拆分为数据库实例，但是可以使用不同端口放在同一个服务器上。

2）基于负载可以拆分为更多数据库实例分布在不同数据库上

如：

基于安全拆分出A数据库实例，
基于业务拆分出B,C数据库实例，
数据库存在较高负载，基于负载拆分为C1,C2,C3,C4等实例。
数据库服务器完全可以做到 A+B+C1 为一台，C2,C3,C4各单独一台。

1、一般数据量过大或者访问压力过大的数据表需要切分

          2、表的字段不宜过多。

n纵向分表

   单数据表字段过多，可将频繁更新的整数数据与非频繁更新的字符串数据切分

范例user表 ，个人简介，地址，号，联系方式，头像 这些字段为字符串类型，更新请求少； 最后登录时间，在线时常，访问次数，信件数这些字段为整数型字段，更新频繁，可以将后面这些更新频繁的字段独立拆出一张数据表，表内容变少，索引结构变少，读写请求变快。

n横向切表

1）等分切表，如哈希切表或其他基于对某数字取余的切表。等分切表的优点是负载很方便的分布到不同服务器；缺点是当容量继续增加时无法方便的扩容，需要重新进行数据的切分或转表。而且一些关键主键不易处理。

2）递增切表，比如每1kw用户开一个新表，优点是可以适应数据的自增趋势；缺点是往往新数据负载高，压力分配不平均。

3）日期切表，适用于日志记录式数据，优缺点等同于递增切表。

4）个人倾向于递增切表，具体根据应用场景决定。

n热点数据分表

1）将数据量较大的数据表中将读写频繁的数据抽取出来，形成热点数据表。通常一个庞大数据表经常被读写的内容往往具有一定的集中性，如果这些集中数据单独处理，就会极大减少整体系统的负载。

2）热点数据表与旧有数据关系

可以是一张冗余表，即该表数据丢失不会妨碍使用，因源数据仍存在于旧有结构中。优点是安全性高，维护方便，缺点是写压力不能分担，仍需要同步写回原系统。

可以是非冗余表，即热点数据的内容原有结构不再保存，优点是读写效率全部优化；缺点是当热点数据发生变化时，维护量较大。

具体方案选择需要根据读写比例决定，在读频率远高于写频率情况下，优先考虑冗余表方案。

3）热点数据表可以用单独的优化的硬件存储，比如昂贵的闪存卡或大内存系统。

4）热点数据表的重要指标

热点数据的定义需要根据业务模式自行制定策略，常见策略为，按照最新的操作时间；按照内容丰富度等等。
数据规模，比如从1000万条数据，抽取出100万条热点数据。
热点命中率，比如查询10次，多少次命中在热点数据内。
理论上，数据规模越小，热点命中率越高，说明效果越好。需要根据业务自行评估。

5）热点数据表的动态维护
加载热点数据方案选择
定时从旧有数据结构中按照新的策略获取
在从旧有数据结构读取时动态加载到热点数据
剔除热点数据方案选择
基于特定策略，定时将热点数据中访问频次较少的数据剔除
如热点数据是冗余表，则直接删除即可，如不是冗余表，需要回写给旧有数据结构。
6）通常，热点数据往往是基于缓存或者key-value方案冗余存储，所以这里提到的热点数据表，其实更多是理解思路，用到的场合可能并不多….

9. 反范式设计（冗余结构设计）

反范式设计的概念

无外键，无连表查询。
便于分布式设计，允许适度冗余，为了容量扩展允许适度开销。
基于业务自由优化，基于i/o 或查询设计，无须遵循范式结构设计。

冗余结构设计所面临的典型场景

原有展现程序涉及多个表的查询，希望精简查询程序
数据表拆分往往基于主键，而原有数据表往往存在非基于主键的关键查询，无法在分表结构中完成。
存在较多数据统计需求（count, sum等），效率低下。

冗余设计方案

1）基于展现的冗余设计

为了简化展现程序，在一些数据表中往往存在冗余字段：

举例，信息表message，存在字段fromuid,touid,msg,sendtime四个字段，其中touid+sendtime是复合索引。存在查询为select * from message where touid=$uid order by sendtime desc limit 0,30;

展示程序需要显示发送者姓名，此时通常会在message表中增加字段fromusername，甚至有的会增加fromusersex，从而无需连表查询直接输出信息的发送者姓名和性别。这就是一种简单的，为了避免连表查询而使用的冗余字段设计。

2）基于展现的冗余设计

3）基于统计的冗余结构

===================================================