大数据第2讲：基于MySQL5.7的性能优化

一、查询数据优化

1.1、使用索引查询需要注意

索引可以提供查询的速度，但并不是使用了带有索引的字段查询都会生效，有些情况下是不生效的，需要注意！

1.1.1、使用LIKE关键字的查询

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引才会生效。

 

 1.1.2、使用联合索引的查询

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。

1.1.3、使用OR关键字的查询

 查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。

 

1.2、自查询优化

 MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询虽然很灵活，但是执行效率并不高。

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。

优化：

可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

二、 数据库结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

2.1、将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

 2.2、增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

2.3、增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

 三、插入数据的优化

插入数据时，影响插入速度的主要是索引、唯一性校验、一次插入的数据条数等。

插入数据的优化，不同的存储引擎优化手段不一样，在MySQL中常用的存储引擎有，MyISAM和InnoDB，两者的区别：

MyISAM是MySQL的默认存储引擎，基于传统的ISAM类型，支持全文搜索，但不是事务安全的，而且不支持外键。每张MyISAM表存放在三个文件中：frm 文件存放表格定义；数据文件是MYD (MYData)；索引文件是MYI (MYIndex)。

InnoDB是事务型引擎，支持回滚、崩溃恢复能力、多版本并发控制、ACID事务，支持行级锁定（InnoDB表的行锁不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，如like操作时的SQL语句），以及提供与Oracle类型一致的不加锁读取方式。InnoDB存储它的表和索引在一个表空间中，表空间可以包含数个文件。

主要区别：

• MyISAM是非事务安全型的，而InnoDB是事务安全型的。
• MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
• MyISAM支持全文类型索引，而InnoDB不支持全文索引。
• MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
• MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
• InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。

应用场景：

• MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
• InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

3.1、MyISAM

3.1.1、禁用索引

对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。

为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。

禁用索引的语句：

ALTER TABLE table_name DISABLE KEYS

开启索引语句：

ALTER TABLE table_name ENABLE KEYS

对于空表批量插入数据，则不需要进行操作，因为MyISAM引擎的表是在导入数据后才建立索引。

3.1.2、禁用唯一性检查

唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。

禁用唯一性检查的语句：SET UNIQUE_CHECKS = 0;

开启唯一性检查的语句：SET UNIQUE_CHECKS = 1;

 3.1.3、批量插入数据

插入数据时，可以使用一条INSERT语句插入一条数据，也可以插入多条数据。

 

第二种方式的插入速度比第一种方式快。 

3.1.4、使用LOAD DATA INFILE

当需要批量导入数据时，使用LOAD DATA INFILE语句比INSERT语句插入速度快很多。

 3.2、InnoDB

3.2.1、禁用唯一性检查

用法和MyISAM一样。

3.2.2、禁用外键检查

插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。

禁用：SET foreign_key_checks = 0;

开启：SET foreign_key_checks = 1;

3.2.3、禁止自动提交

插入数据之前执行禁止事务的自动提交，数据插入完成后再恢复，可以提高插入速度。

禁用：SET autocommit = 0;

开启：SET autocommit = 1;

四、服务器优化

4.1、优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性能，硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率。

需要从以下几个方面考虑：

1. 配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的IO。
2. 配置高速磁盘，比如SSD。
3. 合理分配磁盘IO，把磁盘IO分散到多个设备上，以减少资源的竞争，提高并行操作能力。
4. 配置多核处理器，MySQL是多线程的数据库，多处理器可以提高同时执行多个线程的能力。

 4.2、优化MySQL的参数

通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。

MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中，常用的参数如下：（要求至少记忆3条）

4.2.1、key_buffer_size

 表示索引缓冲区的大小。索引缓冲区所有的线程共享。增加索引缓冲区可以得到更好处理的索引（对所有读和多重写）。当然，这个值也不是越大越好，它的大小取决于内存的大小。如果这个值太大，导致操作系统频繁换页，也会降低系统性能。

4.2.2、table_cache

表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多，这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。

4.2.3、query_cache_size

表示查询缓冲区的大小。该参数需要和query_cache_type配合使用，当query_cache_type值是0时，所有的查询都不使用查询缓冲区，但是query_cache_type=0并不会导致MySQL释放query_cache_size所匹配的缓冲区内存。当query_cache_type=1时，所有的查询都将使用缓冲区，除非在查询语句中制定SQL_NO_CACHE，如SQL_NO_CACHE * FROM tbl_name。当query_cache_type=2时，只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓冲区。使用查询缓冲区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况。

4.2.4、sort_buffer_size

表示排序缓存区的大小，这个值越大，进行排序的速度越快。

4.2.5、read_buffer_size

表示每个线程扫描时为扫描的每个表分配的缓冲区的大小（字节）。当线程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read_buffer_size=n可以临时设置该参数的值。

4.2.6、read_md_buffer_size

表示为每个线程保留的缓冲区的大小，与read_buffer_size相似。但主要用于储存按特定顺序读取出来的记录。也可以用SET SESSION read_md_buffer_size=n来临时设置该参数的值。如果频繁进行多次连续扫描，可以增加该值。

4.2.7、innodb_buffer_pool_size

表示InnoDB类型的表和索引的最大缓存。这个值越大，查询的速度就会越快。但是这个值太大会影响操作系统的性能。

4.2.8、max_connections

表示数据库的最大连接数。这个连接数不是越大越好，因为这些连接会浪费内存的资源。过多的连接可能会的导致MySQL服务器僵死。

4.2.9、innodb_flush_log_at_trx_commit

表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别是0、1和2。当值为1时表示每隔1秒将数据写入日志文件并将日志文件写入磁盘；值为1时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘；当值为2时表示每次提交事务将数据写入日志文件，每隔1秒将日志写入磁盘。该参数的默认值是1。默认值1安全性最高，但是每次事务提交或事务外的指令都需要把日志写入（flush）硬盘，是比较费时的；0值更快一点，但安全方面比较差；2值日志仍然会每秒写入到磁盘，所以即使出现故障，一般也不会丢失超过1～2秒的更新。

4.2.10、back_log

表示在mysql暂时停止回答新请求之前的短时间内，多少个请求可以被存在堆栈中。换句话说，该值表示对到来的TCP/IP连接的侦听队列的大小。只有期望在一个短时间内有很多连接，才需要增加该参数的值。操作系统在这个队列大小上也有限制。设定back_log高于操作系统的限制将是无效的。

4.2.11、interactive_timeout

表示服务器在关闭连接前等待行动的秒数

4.2.12、sort_buffer_size

表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度。默人数值是2097144（2M）

4.2.13、thread_cache_size

表示可以复用的线程的数量。如果有很多新的线程，为了提高性能可以增大该参数的值。

4.2.14、wait_timeout

表示服务器在关闭一个连接时等待行动的秒数。默认数值是28800.