MySQL优化详解

待我称王封你为后i 2022-07-10 05:58 217阅读 0赞

有 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的执行频率

show status like 'Com\_%'  
        了解 Com\_select,Com\_insert 的执行次数  
   2)  通过Explain分析低效的sql语句  
   3)   建立合适的索引  
   4)   通过show status like 'Handler\_%'查看索引的使用情况  
         handler\_read\_key：根据索引读取行的请求数。如果该值很大，说明你的查询和表都建立了很好的索引，表明索引效率的很高  
         Handler\_read\_rnd\_key：根据固定位置读取行的请求数。如果你执行很多需要排序的查询，该值会很高。你可能有很多需要完整表扫描的查询，或者你使用了不正确的索引用来多表查询。

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

5)   定期分析表和检查表  
         analyze table test\_table和check table test\_table  
         然后查看Msg\_text字段的值是否是ok  
   6）定期优化表 optimize table test\_table  
         如果对表的可变字段varchar blob，text等进行了很多更改， 则应用OPTIMIZE优化。

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

详细内容：[SQL优化大全][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适合做更新时间

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。  
　　另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。  
　　对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

**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）. 唯一索引**

普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个”员工个人资料”数据表里可能出现两次或更多次。  
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

**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%'表锁情况

![20190509142945566.png][]  
Table\_locks\_immediate 表示立即释放表锁数，Table\_locks\_waited表示需要等待的表锁数。

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

如果是myisam引擎表 table\_lock\_wait 值比较大，那说明并发高，存在着较严重的表级锁争用情况，性能有问题，存储引擎最好改为innodb引擎，在innodb引擎下，table\_lock\_wait值几乎没用。table\_lock\_wait 不同于innodb\_lock\_wait.

# #

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

--------------------

使用show variables 了解服务器参数  
show status 了解服务器运行状态，如锁等待情况，当前连接数等

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

### **1、key\_buffer\_size:** ###

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

通过： mysql> show global status like 'key\_read%';  
\+-------------------+------------+  
| Variable\_name     | Value      |  
\+-------------------+------------+  
| Key\_read\_requests | 3465117712 |  
| Key\_reads         | 624        |  
\+-------------------+------------+

Key\_read\_requests：从缓存读取索引的请求次数。  
   Key\_reads：从磁盘读取索引的请求次数。

一共有3465117712 个索引读取请求，有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) ###

table\_open\_cache数据库打开表的缓存数量 ，每个连接进来，都会至少打开一个表缓存。因此  
table\_open\_cache和max\_connections有关， 例如 对于200个并行运行的连接，应该让表的缓存至少是200 \*N , N 是可以执行查询的一个连接中的表的最大数.

如果Opened\_tables数量过大，说明配置中 table\_open\_cache值可能太小.

### 3、连接数：max\_connections ###

mysql>set globle max\_connections = 5000;  
最大可设置16384,超过没用。

mysql>show global status like 'max\_used\_connections';

max\_used\_connections / max\_connections \* 100% 的理想值 ≈ 85%

### **4、thread\_cache\_size 进程使用情况 ** ###

每建立一个连接，都需要一个线程来与之匹配。  
thread\_cache\_size：用来缓存空闲的线程，以至不被销毁，如果线程缓存中有空闲线程，这时候如果建立新连接，MYSQL就会很快的响应连接请求。

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

mysql> show variables like 'thread\_cache\_size';

![20190509140821175.png][]

mysql> show global status like 'Thread%';

![20190509140711859.png][]

show global 里关于线程缓存有如下几个状态变量，简单解释：  
Threads\_cached :代表当前此时此刻线程缓存中有多少空闲线程。  
Threads\_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。  
Threads\_created :代表从最近一次服务启动，已创建线程的数量。  
Threads\_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。  
最好将thread\_cache\_size设置成与threads\_connected一样。不过很少有情况将threads\_cache\_size设置成比200还大的数。

### 5、查询缓存(query cache)  ###

show global status like 'qcache%';

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

Qcache\_free\_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。   
Qcache\_free\_memory：缓存中的空闲内存。   
Qcache\_hits：每次查询在缓存中命中时就增大   
Qcache\_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。   
Qcache\_lowmem\_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的          free\_blocks和free\_memory可以告诉您属于哪种情况）   
Qcache\_not\_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。   
Qcache\_queries\_in\_cache：当前缓存的查询（和响应）的数量。   
Qcache\_total\_blocks：缓存中块的数量。   
  
我们再查询一下服务器关于query\_cache的配置：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hndWlzdQ_size_16_color_FFFFFF_t_70][]

各字段的解释：   
query\_cache\_limit：超过此大小的查询将不缓存   
query\_cache\_min\_res\_unit：缓存块的最小大小   
query\_cache\_size：查询缓存大小   
query\_cache\_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql\_no\_cache 查询   
query\_cache\_wlock\_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。   
  
query\_cache\_min\_res\_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。   
  
查询缓存碎片率 = Qcache\_free\_blocks / Qcache\_total\_blocks \* 100%   
  
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query\_cache\_min\_res\_unit，如果你的查询都是小数据量的话。   
  
查询缓存利用率 = (query\_cache\_size – Qcache\_free\_memory) / query\_cache\_size \* 100%   
  
查询缓存利用率在25%以下的话说明query\_cache\_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache\_lowmem\_prunes > 50的话说明query\_cache\_size可能有点小，要不就是碎片太多。   
  
查询缓存命中率 = (Qcache\_hits – Qcache\_inserts) / Qcache\_hits \* 100%   
  
示例服务器 查询缓存碎片率 ＝ 20.46％，查询缓存利用率 ＝ 62.26％，查询缓存命中率 ＝ 1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。

### 6、read\_buffer\_size 表扫描情况  ###

show global status like 'handler\_read%';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hndWlzdQ_size_16_color_FFFFFF_t_70 1][]  
计算表扫描率：   
表扫描率 ＝ 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 排序使用情况 ###

![20190509142728460.png][]

当 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缓存

2.1 key\_buffer\_size  
        key\_buffer\_size决定MyISAM索引块缓存去的大小，直接影响MyISAM表的存取效率。对于一般MyISAM数据库，建议至少将1/4可用内存分配给key\_buffer\_size

2.2 read\_buffer\_size和read\_rnd\_buffer\_size  
        如果需要经常顺序扫描MyISAM表，可以通过增大read\_buffer\_size的值来提高性能。但是要注意它是每个session独占的，如果默认值太大，就可能浪费内存，造成物理内存耗尽。

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

** InnoDB内存优化**  
     InnoDB的缓存机制和MyISAM不太相同。InnoDB用一块内存区做IO缓存池，该缓存池不仅用来缓存InnoDB的索引块，也用来缓存数据块。

3.1 innodb\_buffer\_pool\_size  
         innodb\_buffer\_pool\_size决定InnoDB缓存区大小 就是上面提到的内存区。在保障操作系统及其他程序有足够的内存可用的情况下该值越大，性能越好。

3.2 调整innodb\_log\_buffer\_size  
          innodb\_log\_buffer\_size决定InnoDB重做日志缓存池的大小。对于一个会在事务中更新、插入、删除大量记录的应用，可以提高innodb\_log\_buffer\_size来提高事务处理的性能。

# #

# 6. 磁盘IO优化 #

--------------------

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

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

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

RAID就是按照一定的策略将数据分布到若干物理磁盘上，这样不仅增强了数据存储的可靠性，而且提高数 据读写的性能 （RAID有不能的级别）  
         1) 读写很频繁的，可靠性要求也很高的，最好RAID 10  
         2) 数据读很频繁，写相对较少的，对可靠性一定要求的，选择RAID 5

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

**2) 使用符号链接 分布I/O**

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

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

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

例如：

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

注意：只有 MyISAM 表完全支持符号链接。对于其它表类型，如果试图在操作系统 中的文件上用前面的任何语句使用符号链接，可能会出现奇怪的问题。  
对于 MyISAM 表的符号链接的处理如下：  
     1. 在数据目录指，一定会有表定义文件、数据文件和索引文件。数据文件和索引文件可 以移到别处和在数据目录中符号链接替代。表定义文件不能进行符号链接替换。  
     2. 可以分别通过符号链接将数据文件和索引文件指到不同的目录。

3. 如果 mysqld 没有运行，符号链接可以从服务器命令行使用 ln -s 手动完成。同样，通过使用 DATA DIRECTORY 和 INDEX DIRECTORY 选项创建表，你可以指示运行的 MySQL 服务器执行符号链接。  
     4. myisamchk 不用数据文件或索引文件替换符号链接。它直接工作在符号链接指向的文件。任何临时文件创建在数据文件或索引文件所处的目录中。  
     5. 注释：当你删掉一个表时，如果该表使用了符号链接，符号链接和该符号链接指向的 文件都被删除掉。这就是你不应以系统 root 用户运行 mysqld 或允许系统用户对 MySQL数据库目录有写访问权限的原因。  
     6. 如果你用 ALTER TABLE ... RENAME 重命名一个表并且不将表移到另一个数据库，数据库目录中的符号链接被重新命名为一个新名字并且数据文件和索引文件也相应地重新命名。  
     7. 如果你用 ALTER TABLE ... RENAME 移动一个表到另一个数据库，表移动到另一个数据库目录。旧的符号链接和其所指向的文件被删除。换句话说，新表不再被链接。  
     8. 如果不使用符号链接，你应对 mysqld 使用 --skip-symbolic-links 选项以确保没有人能够使用 mysqld 来删除或重新命名数据目录之外的文件。

表符号链接还不支持以下操作：  
    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 '123456'  
          2), 修改主服务器的配置my.conf 开启binlog和设置server-id  
          3), 将主服务器的数据一致性恢复到从服务器，保证将要复制的数据时一只的，否则出问题  
          4), 在从服务器上修改配置my.conf  
               server-id=2  
               master-host=10.0.1.3  
               master-user='rel'  
               master-password='123456'  
               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表 ，个人简介，地址，QQ号，联系方式，头像 这些字段为字符串类型，更新请求少； 最后登录时间，在线时常，访问次数，信件数这些字段为整数型字段，更新频繁，可以将后面这些更新频繁的字段独立拆出一张数据表，表内容变少，索引结构变少，读写请求变快。

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）基于展现的冗余设计

涉及分表操作后，一些常见的索引查询可能需要跨表，带来不必要的麻烦。确认查询请求远大于写入请求时，应设置便于查询项的冗余表。  
冗余表要点：  
数据一致性，简单说，同增，同删，同更新。  
可以做全冗余，或者只做主键关联的冗余，比如通过用户名查询uid，再基于uid查询源表。

实战范例1  
用户分表，将用户库分成若干数据表  
基于用户名的查询和基于uid的查询都是高并发请求。  
用户分表基于uid分成数据表，同时基于用户名做对应冗余表。  
如果允许多方式登陆，可以有如下设计方法  
nuid,passwd,用户信息等等，主数据表，基于uid分表  
nukey,ukeytype,uid基于ukey分表，便于用户登陆的查询。分解成如下两个SQL。  
uselect uid from ulist\_key\_13 where ukey=’$username’ and ukeytype=‘login’;  
uselect \* from ulist\_uid\_23 where uid=$uid and passwd=’$passwd’;  
nukeytype定义用户的登陆依据，比如用户名，手机号，邮件地址，网站昵称等。Ukey+ukeytype 必须唯一。  
n此种方式需要登陆密码统一，对于第三方connect接入模式，可以通过引申额外字段完成。

实战范例2：用户游戏积分排名  
表结构uid,gameid,score参见前文实时积分排行。表内容巨大，需要拆表。  
需求1：基于游戏id查询积分排行  
需求2：基于用户id查询游戏积分记录  
解决方案：建立完全相同的两套表结构，其一以uid为拆表主键，其二以gameid为拆表主键，用户提交积分时，向两个数据结构同时提交。

实战范例3：全冗余查询结构  
主信息表仅包括 主键及备注memo字段（text类型），只支持主键查询，可以基于主键拆表。所以需要展现和存储的内容均在memo字段重体现。  
对每一个查询条件，建立查询冗余表，以查询条件字段为主键，以主信息表主键id为内容。  
日常查询只基于查询冗余表，然后通过in的方式从主信息表获得内容。  
优点是结构扩展非常方便，只需要扩展新的查询信息表即可，核心思路是，只有查询才需要独立的索引结构，展现无需独立字段。  
缺点是只适合于相对固定的查询架构，对于更加灵活的组合查询束手无策。

3）基于统计的冗余结构

为了减少会涉及大规模影响结果集的表数据操作，比如count，sum操作。应将一些统计类数据通过冗余数据结构保存。  
冗余数据结构可能以字段方式存在，也可能以独立数据表结构存在，但是都应能通过源数据表恢复。  
实战范例：  
论坛板块的发帖量，回帖量，每日新增数据等。  
网站每日新增用户数等。  
参见Discuz论坛系统数据结构，有较多相关结构。  
参见前文分段积分结构，是典型用于统计的冗余结构。

后台可以通过源数据表更新该数字。  
Redis的Zset类型可以理解为存在一种冗余统计结构。

4）历史数据表  
历史数据表对应于热点数据表，将需求较少又不能丢弃的数据存入，仅在少数情况下被访问。

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

[SQL]: http://blog.csdn.net/hguisu/article/details/5731629
[20190509142945566.png]: /images/20220709/283d8a199d7742878459500dccc5538a.png
[20190509140821175.png]: /images/20220709/e36eb205070747f2b5c23bb68ddf3aaa.png
[20190509140711859.png]: /images/20220709/946026c443f34d2491d2e41db963b714.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hndWlzdQ_size_16_color_FFFFFF_t_70]: /images/20220709/715aad7fa93e45f48ed019d381a8f45e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hndWlzdQ_size_16_color_FFFFFF_t_70 1]: /images/20220709/e506aafecf7b485b8be42f22e032b8be.png
[20190509142728460.png]: /images/20220709/6f18708984e546e1b4adff4d93129bc1.png