MySQL优化大全

最新推荐文章于 2024-07-04 10:20:47 发布

读书与远方

最新推荐文章于 2024-07-04 10:20:47 发布

阅读量574

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/yingchunhua365/article/details/17757755

版权

数据库专栏收录该内容

18 篇文章 0 订阅

订阅专栏

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优化大全

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会使用表锁

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

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

5. MySQL server服务器配置优化

   1）使用show variables 了解服务器参数
   2）show status 了解服务器运行状态，如锁等待情况，当前连接数等
   3）影响mysql性能的重要参数：

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：从磁盘读取索引的请求次数。

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

需要适当加大key_buffer_size

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

4) 还有innodb_buffer_pool_size等innodb参数的设置

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 数据库架构