超详细MySQL数据库优化

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.

成都网络公司-成都网站建设公司创新互联建站10余年经验成就非凡，专业从事成都做网站、网站制作，成都网页设计，成都网页制作，软文推广，一元广告等。10余年来已成功提供全面的成都网站建设方案，打造行业特色的成都网站建设案例，建站热线：028-86922220，我们期待您的来电！

1. 优化一览图

2. 优化

笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.

2.1 软优化

2.1.1 查询语句优化

1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.

2.例:

显示:

其中会显示索引和查询数据读取数据条数等信息.

2.1.2 优化子查询

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

2.1.3 使用索引

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者MySQL数据库索引一文,介绍比较详细,此处记录使用索引的三大注意事项:

2.1.4 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

2.1.5 中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

2.1.6 增加冗余字段

类似于创建中间表,增加冗余也是为了减少连接查询.

2.1.7 分析表,,检查表,优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

1. 分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;

2. 检查表: 使用 CHECK关键字,如CHECK TABLE user [option]

option 只对MyISAM有效,共五个参数值:

3. 优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

2.2 硬优化

2.2.1 硬件三件套

1.配置多核心和频率高的cpu,多核心可以执行多个线程.

2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.

3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

2.2.2 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

2.2.3 分库分表

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

2.2.4 缓存集群

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。

一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.

MySQL的执行流程怎么优化?

选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

mysql数据库如何优化？谁能给出点具体的解决方案？

1、explain：解释sql的执行计划，后边的sql不执行

2、explain partitions ：用于查看存在分区的表的执行计划

3、explain extended：待验证

4、show warnings:

5、show create table:查看表的详细的创建语句，便于用户对表进行优化

6、show indexes :产看表的所有索引，show indexes from table_name，同样也可以从information_schema.statistics表中获得同样的信息。cardinality列很重要，表示数据量。

7、show tables status: 查看数据库表的底层大小以及表结构，同样可以从information_schema.tables表中获得底层表的信息。

8、show [global|session]status:可以查看mysql服务器当前内部状态信息。可以帮助却行mysql服务器的负载的各种指标。默认是session。同information_schema.global_status和information_schema.session_status

9、show [global|session] variables ：查看当前mysql系统变量的值，其中一些值能影响到sql语句的执行方式。同information_schema.global_variables和information_schema.session_variables;

10、information_schema:包含的表的数量和mysql的版本有关系。

MySQL 查询优化（六）： MySQL 的查询优化排序优化机制

对结果进行排序操作的代价可能很高，因此可以通过避免排序或让参与排序的数据行更少来优化查询性能。

当 MySQL 不能使用索引产生有序结果时，它必须对数据行进行排序。这有可能是在内存中进行也可能是在磁盘进行，但 MySQL 始终将这个过程称之为 filesort，即便实际上并没有使用一个文件。

如果用于排序的值可以一次性放入排序缓存中，MySQL 可以在内存中使用快排算法进行排序。如果 MySQL 不能在内存中进行排序，则会在磁盘中按块逐块排序。它对每个块使用快排算法，然后在将这些排序好的块合并到结果中。

有两个文件排序（filesort）算法：

很难说哪种算法更有效，对每个算法来说都会有最优和最坏案例。MySQL 在数据表全部列加上用于排序的列的大小不超过 max_length_for_sort_data 时会使用单次遍历算法。可以通过修改这个参数影响排序算法的选择。

需要注意的是，MySQL 的 filesort使用的临时存储空间可能会超出你的预期，这是因为它对每个排序元素都分配了固定大小的存储空间。这些存储空间要足够大以便容下存储最大的元素，而且 VARCHAR这类字段使用的是对应的最大长度。而且，如果使用的是 UTF-8字符集，MuSQL 会对每个字符分配3个字节。结果是，我们会发现那些没怎么优化的查询会导致磁盘上的临时存储空间是数据表自身存储空间的好几倍。

而在对联合查询进行排序时，MySQL 可能会在查询执行过程中执行两次文件排序。如果 ORDER BY 子句只是引用联合查询的第一张表，MySQL 可以先对这个表进行文件排序，然后再处理联合查询。如果是这种情况，在 EXPLAIN 时会在 Extra 字段显示“Using filesort”。而对于其他的排序情况——例如排序不是针对第一张表，或者是 ORDER BY 使用的列对应了不止一个数据表，MySQL 必须使用临时表缓存查询结果，然而在联合查询完成后，再对临时表进行文件排序。在这种情况下，EXPLAIN 会在 Extra 字段显示“Using temorary; Using filesort”。如果包含 LIMIT 约束的话，会发生在文件排序后，因此临时表和文件排序的存储空间可能非常大。

MySQL 5.6在只需要对数据行的子集（例如 LIMIT）进行排序时，引入了一个重大改进。相对于对整个结果集进行排序再返回部分数据，MySQL 有时候会在排序的时候直接丢弃掉不需要的数据行来提高效率。不管怎么样，排序也需要小心使用，很可能会导致存储占用的飙升最终导致系统负荷过大。

mysql 优化包括哪些内容？

mysql的优化大的有两方面：

1、配置优化

配置的优化其实包含两个方面的：操作系统内核的优化和mysql配置文件的优化

1)系统内核的优化对专用的mysql服务器来说，无非是内存实用、连接数、超时处理、TCP处理等方面的优化，根据自己的硬件配置来进行优化，这里不多讲；

2)mysql配置的优化，一般来说包含：IO处理的常用参数、最大连接数设置、缓存使用参数的设置、慢日志的参数的设置、innodb相关参数的设置等，如果有主从关系在设置主从同步的相关参数即可，网上的相关配置文件很多，大同小异，常用的设置大多修改这些差不多就够用了。

2、sql语句的优化

1）  尽量稍作计算

Mysql的作用是用来存取数据的，不是做计算的，做计算的话可以用其他方法去实现，mysql做计算是很耗资源的。

2）尽量少 join

MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈

3）尽量少排序

排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL的响应时间。

对于MySQL来说，减少排序有多种办法，比如：

通过利用索引来排序的方式进行优化

减少参与排序的记录条数

非必要不对数据进行排序

4）尽量避免 select *

在数据量少并且访问量不大的情况下，select * 没有什么影响，但是量级达到一定级别的时候，在执行效率和IO资源的使用上，还是有很大关系的，用什么字段取什么字段，减少不必要的资源浪费。

5）尽量用 join 代替子查询

虽然 Join 性能并不佳，但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。

MySQL数据库性能优化有哪些技巧？

1.存储引擎的选择如果数据表需要事务处理，应该考虑使用InnoDB，因为它完全符合ACID特性。如果不需要事务处理，使用默认存储引擎MyISAM是比较明智的。并且不要尝试同时使用这两个存储引擎。思考一下：在一个事务处理中，一些数据表使用InnoDB，而其余的使用MyISAM.结果呢？整个subject将被取消，只有那些在事务处理中的被带回到原始状态，其余的被提交的数据转存，这将导致整个数据库的冲突。然而存在一个简单的方法可以同时利用两个存储引擎的优势。目前大多数MySQL套件中包括InnoDB、编译器和链表，但如果你选择MyISAM，你仍然可以单独下载InnoDB，并把它作为一个插件。很简单的方法，不是吗？

2.计数问题如果数据表采用的存储引擎支持事务处理（如InnoDB），你就不应使用COUNT（*）计算数据表中的行数。这是因为在产品类数据库使用COUNT（*），最多返回一个近似值，因为在某个特定时间，总有一些事务处理正在运行。如果使用COUNT（*）显然会产生bug，出现这种错误结果。

3.反复测试查询查询最棘手的问题并不是无论怎样小心总会出现错误，并导致bug出现。恰恰相反，问题是在大多数情况下bug出现时，应用程序或数据库已经上线。的确不存在针对该问题切实可行的解决方法，除非将测试样本在应用程序或数据库上运行。任何数据库查询只有经过上千个记录的大量样本测试，才能被认可。

4.避免全表扫描通常情况下，如果MySQL（或者其他关系数据库模型）需要在数据表中搜索或扫描任意特定记录时，就会用到全表扫描。此外，通常最简单的方法是使用索引表，以解决全表扫描引起的低效能问题。然而，正如我们在随后的问题中看到的，这存在错误部分。

5.使用“EXPLAIN”进行查询当需要调试时，EXPLAIN是一个很好的命令，下面将对EXPLAIN进行深入探讨。

分享文章：mysql怎么测试优化 mysql查询性能优化
本文URL：http://kswjz.com/article/hjcseg.html