MySQL数据库原理与应用(微课版)ch16性能优化

第16章

性能优化本章概述MySQL性能优化就是通过合理安排资源、调整系统参数使MySQL运行得更快、更节省资源。MySQL性能优化包括查询速度优化、数据库结构优化、MySQL服务器优化等。本章学习目标了解什么是数据库性能优化；掌握常用的优化查询的方法；掌握常用的优化数据库结构的方法；掌握常用的优化MySQL服务器的方法；熟练掌握综合案例中性能优化的方法和技巧。目录01020304优化MySQL服务器优化数据库结构临时表性能优化优化查询05服务器语句超时处理06创建全局通用表空间07本章小结优化查询MySQL数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。在MySQL中，可以使用SHOWSTATUS语句查询一些MySQL数据库的性能参数。SHOWSTATUS语句语法如下：SHOWSTATUSLIKE'value';优化简介优化查询MySQL中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句。语法如下：EXPLAIN[EXTENDED]SELECTselect_options分析查询语句优化查询分析未使用索引时的查询情况，EXPLAIN语句如下：EXPLAINSELECT*FROMtb_bookWHEREauthor="明日科技";在tb_book表的author字段上加上索引，再分析：索引对查询速度的影响优化查询使用LIKE关键字的查询语句使用多列索引的查询语句使用OR关键字的查询语句使用索引查询优化查询在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。连接之所以更有效率，是因为MySQL不需要在内存中创建临时表来完成查询工作。优化子查询目录优化MySQL服务器优化数据库结构临时表性能优化优化查询服务器语句超时处理创建全局通用表空间本章小结01020304050607优化数据库结构将字段很多的表分解成多个表：对于字段较多的表，如果有些字段的使用频率很低，那么可以将这些字段分离出来形成新表。当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。增加中间表：通过建立中间表，把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。操作方法为：首先，分析经常联合查询表中的字段；然后，使用这些字段建立一个中间表，并将原来联合查询的表的数据插入中间表中；最后，使用中间表来进行查询。增加冗余字段：设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理地加入冗余字段可以提高查询速度。目录优化MySQL服务器优化数据库结构临时表性能优化优化查询服务器语句超时处理创建全局通用表空间本章小结01040506070302优化MySQL服务器优化MySQL服务器主要从两个方面来优化，一方面是对硬件进行优化；另一方面是对MySQL服务的参数进行优化。（1）配置较大的内存。足够大的内存是提高MySQL数据库性能的方法之一。内存的速度比磁盘I/O快得多，可以通过增加系统的缓冲区容量使数据在内存中停留的时间更长，以减少磁盘I/O。（2）配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。（3）合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。（4）配置多处理器，MySQL是多线程的数据库，多处理器可同时执行多个线程。优化服务器硬件优化MySQL服务器通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。MySQL服务的配置参数都在f或者my.ini文件的[MySQLd]组中。key_buffer_size：表示索引缓冲区的大小。table_cache：表示同时打开的表的个数。query_cache_size：表示查询缓冲区的大小。sort_buffer_size：表示排序缓存区的大小。read_buffer_size：表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节）。read_rnd_buffer_size：表示为每个线程保留的缓冲区的大小。innodb_buffer_pool_size：表示InnoDB类型的表和索引的最大缓存。max_connections：表示数据库的最大连接数。innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。back_log：表示在MySQL暂时停止回答新请求之前的短时间内，多少个请求可以被存储在堆栈中。interactive_timeout：表示服务器在关闭连接前等待行动的秒数。优化MySQL的参数目录优化MySQL服务器优化数据库结构临时表性能优化优化查询服务器语句超时处理创建全局通用表空间本章小结01050607020403临时表性能优化MySQL8.0使用CREATETABLESPACE语句来创建一个通用表空间。这个功能可以让用户自由地选择表和表空间之间的映射。优化普通SQL临时表的性能是MySQL8.0的目标之一。首先，通过优化临时表在磁盘中的不必要步骤，使得临时表的创建和移除成为一个轻量级的操作。将临时表移动到一个单独的表空间中，恢复临时表的过程就变得非常简单，就是在启动时重新创建临时表的单一过程。MySQL8.0去掉了临时表中不必要的持久化。临时表仅仅在连接和会话内被创建，然后通过服务的生命周期绑定它们。通过移除不必要的UNDO和REDO日志，改变缓冲和锁，从而为临时表做优化操作。目录优化MySQL服务器优化数据库结构临时表性能优化优化查询服务器语句超时处理创建全局通用表空间本章小结01060702030504服务器语句超时处理在MySQL8.0中可以设置服务器语句超时的限制，单位可以达到毫秒级别。当中断的执行语句超过设置的毫秒数后，服务器将终止查询影响不大的事务或连接，然后将错误报给客户端。设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现。例如：SETGLOBALMAX_EXECUTION_TIME=2000;默认情况下，MAX_EXECUTION_TIME的值为0，代表没有时间限制。通过上述设置后，如果SELECT语句执行超过2000毫秒，语句就会被终止。服务器语句超时处理目录优化MySQL服务器优化数据库结构临时表性能优化优化查询服务器语句超时处理创建全局通用表空间本章小结01070203040605创建全局通用表空间MySQL8.0支持创建全局通用表空间，全局表空间可以被所有的数据库的表共享，而且相比于独享表空间，使用手动创建共享表空间可以节约元数据方面的内存。可以在创建表的时候，指定属于哪个表空间，也可以对已有表进行表空间修改，具体的信息可以查看官方文档。下面创建名为dxy的共享表空间，SQL语句如下：CREATETABLESPACEabcADDdatafile‘abc.ibd’file_block_size=16k;指定表空间，SQL语句如下：CREATETABLEt1(idint,namevarchar(20))engine=innodbdefaultcharsetutf8mb4tablespaceabc;首先删除依赖该表空间的数据表，SQL语句如下：DROPTABLEt1;最后即可删除表空间，SQL语句如下：DROPTABLESPACEabc;目录优化MySQL服务器优化数据库结构临时表性能优化优化查询服务器语句超时处理创建全局通用表空间本章小结020304050706本章小结MySQL性能优化就是通过合理安排资源、调整系统参数使MySQL运行得更快、更节省资源。可以从查询速度、数据库结构、MySQL服务器等方面来进行优化，以提高系统运行性能。本章首先详细介绍了查询的优