随着尺寸和负载的增长,MySQL的性能会趋于下降。记住这些诀窍,便可保持MySQL的流畅运行。
测量应用程序的方法之一是看性能。而性能的指标之一便是用户体验,通俗的说法就是“用户是否需要等待更长的时间才能得到他们想要的东西”。
这个指标在不同的应用场合而有所改变。对于移动购物应用,响应时间不能超过几秒钟。对于员工的人力资源页面,可能需要多花几秒钟的时间。
无论采用何种标准,都必须保持良好的应用性能。否则,用户会抱怨(或者更糟的是,转到不同的应用程序)。影响应用程序性能的因素之一是数据库性能。应用程序、网站和数据库之间的交互对于建立应用程序性能的好坏至关重要。
这种交互的一个核心组件是应用程序如何查询数据库以及数据库如何响应请求。无论如何,MySQL都是最受欢迎的数据库管理系统之一。在生产环境中,越来越多的企业正在转向使用MySQL(和其他开源数据库)作为数据库解决方案。
有许多配置MySQL的方法可以帮助确保数据库对查询作出快速响应,并使应用程序性能降低到最低限度。
以下是帮助优化MySQL数据库性能的一些基本技巧。
优化技巧 #1:学习如何使用 EXPLAIN
使用任何数据库所做的两个最重要的决定是设计应用程序实体之间的关系如何映射到表(数据库模式),以及设计应用程序如何以所需的格式获得所需的数据(查询)。
复杂的应用程序可以有复杂的模式和查询。如果想得到应用程序所需要的性能和扩展性,不能仅仅依靠直觉来理解如何执行查询。
应该学习如何使用EXPLAIN命令,而不是随意的猜测和想象。此命令展示了如何执行查询,并让您了解所期望的性能,以及查询将如何随着数据大小的变化而伸缩。
有许多工具–比如MySQLWorkbench–可以可视化EXPLAIN输出,但仍然需要理解基础知识才能理解它。
EXPLAIN命令提供输出的有两种不同的格式:老式的表格式和更现代的结构化JSON文档,它提供了更多的细节(如下所示):
mysql> explain format=json select avg(k) from sbtest1 where id between 1000 and 2000 \G
*************************** 1. row ***************************
EXPLAIN: {
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “762.40”
},
“table”: {
“table_name”: “sbtest1”,
“access_type”: “range”,
“possible_keys”: [
“PRIMARY”
],
“key”: “PRIMARY”,
“used_key_parts”: [
“id”
],
“key_length”: “4”,
“rows_examined_per_scan”: 1874,
“rows_produced_per_join”: 1874,
“filtered”: “100.00”,
“cost_info”: {
“read_cost”: “387.60”,
“eval_cost”: “374.80”,
“prefix_cost”: “762.40”,
“data_read_per_join”: “351K”
},
“used_columns”: [
“id”,
“k”
],
“attached_condition”: “(`sbtest`.`sbtest1`.`id` between 1000 and 2000)”
}
}
}
应该查看的一个组件是“query cost”。query cost是指MySQL根据查询执行的总开销来考虑这个特定查询的代价,并且基于许多不同的因素。
简单查询的查询开销通常小于1,000。开销在1,000到100,000之间的查询被认为是中等开销的查询,而且如果每秒只运行数百个这样的查询(而不是数万个),通常会比较快。
开销超过100,000的查询可以当作是昂贵的。通常,当您是系统上的单个用户时,这些查询仍会快速运行,但您应该仔细考虑在交互式应用程序中使用此类查询的频率(尤其是随着用户数量的增长)。
当然,这些数字只是性能的一个大概的体现,但它们展示了一般原则。您的系统可能更好地处理查询工作负载,也可能更糟,这取决于其体系结构和配置。
EXPLAIN 是如此重要。
优化技巧 #2:创建正确的索引
索引通过减少查询必须扫描的数据库中的数据量来提高查询效率。MySQL中的索引用于加速数据库中的访问,并帮助执行数据库约束(如 UNIQUE和FOREIGN KEY )。
数据库索引很像图书索引。它们被保存在自己的位置,并且包含主数据库中已经存在的信息。它们是指向数据所在位置的参考方法或映射。索引不会更改数据库中的任何数据。它们只是指向数据的位置。
没有完全适用于任何工作负载的索引。而应该始终在系统运行的查询上下文中查看索引。
索引良好的数据库不仅运行得更快,而且即使缺少一个索引也会使数据库慢如蜗牛。使用EXPLAIN(如前所述)查找缺少的索引并添加它们。但是要小心:不要添加你不需要的索引!不必要的索引会降低数据库的速度
优化技巧 #3:拒绝使用默认设置
与任何软件一样,MySQL有许多可配置的设置,可用于修改行为(以及最终的性能)。与任何软件一样,管理员忽略了许多这些可配置的设置,最终在默认模式下使用。
要从MySQL中获得最佳性能,了解可配置的的MySQL设置是非常重要的,更重要的是将它们设置为最适合您的数据库环境。
默认情况下,MySQL用于小规模的开发安装,而不是生产规模。您通常希望配置MySQL以使用所有可用的内存资源,并允许应用程序需要的连接数量。
下面是三个MySQL性能优化设置,您应该始终仔细检查:
innodb_ buffer_ pool_size:缓冲池用于存放缓存数据和索引。这是使用具有大容量RAM的系统作为数据库服务器的主要原因。如果只运行InnoDB存储引擎,通常会将80%的内存分配给缓冲池。如果您正在运行非常复杂的查询,或者有大量的并发数据库连接,或大量的表,可能需要将此值降低一个档次,以便为其他操作分配更多的内存。
在设置InnoDB缓冲池大小时,需要确保不要设置得太大,否则会导致交换。这绝对会影响数据库性能。一种简单的检查方法是查看Percona Monitoring and Management中的系统概述图中的交换活动