Mysql查询优化

优化数据访问(缓存、只查询需要显示的列等)重写SQL(避免连接太多表)重新设计库表添加索引on、using子句中的列确认有索引。最好是能转化为inner join、left join的成本比inner join高很多。使用explain检查连接，留意其输出的rows列，若rows列太高，比如几千，上万，则需考虑是否索引不佳或连接表的顺序不当。反范式设计，可减少连接表的个数，加快存取数据的速度。考虑在应用层实现连接。一些可能需要访问不同的数据库实例，这种情况下，在应用层实现连接将是更好的选择。尽量对较少的列进行排序。如果连接了多张表，order by 的列应该属于连接顺序的第一张表。利用索引排序，若不能利用索引排序，那explain查询语句将会看到有filesort。group by、order by 语句参考的列应该尽量在一个表中。需要保证索引列和order by 列相同，且各列均按相同的方向进行排序。增加sort_buffer_size(为每个排序线程分配的缓冲区的大小)。增加read_rnd_buffer_size(可改进order by性能)。改变tmpdir变量指向基于内存的文件系统或其他更快的磁盘。指定order by null(默认情况下mysql 模糊搜索 优化，MySQL会排序所有 group by 的查询，可指定order by null避免排序消耗)。优化group by with rollup，它可方便地获得整合分组的聚合信息，但若存在性能问题，可考虑在应用层实现这个功能，这样往往会更高效，伸缩性也更佳。使用非group by的列来代替group by的列。可以考虑使用Sphinx等产品来优化group by语句，一般来说，它可以有更好的可扩展性和更佳的性能。

对于数据库来说，在绝大多数情况下，连接会比子查询更快。子查询应尽量尽管改写成join的写法

限制页数要避免设置offset值，也就是避免丢弃记录使用Sphinx(

Sphinx是一个基于SQL的全文检索引擎，可以结合MySQL,PostgreSQL做全文搜索，它可以提供比数据库本身更专业的搜索功能，使得应用程序更容易实现专业化的全文检索。Sphinx特别为一些脚本语言设计搜索API接口，如PHP,Python,Perl,Ruby等，同时为MySQL也设计了一个存储引擎插件。

Sphinx 单一索引最大可包含1亿条记录，在1千万条记录情况下的查询速度为0.x秒（毫秒级）。Sphinx创建索引的速度为：创建100万条记录的索引只需 3～4分钟，创建1000万条记录的索引可以在50分钟内完成，而只包含最新10万条记录的增量索引，重建一次只需几十秒。

Sphinx的主要特性包括：

高速索引 （在新款CPU上，近10 MB/秒）; 高速搜索 (2-4G的文本量中平均查询速度不到0.1秒); 高可用性 （单CPU上最大可支持100 GB的文本，100M文档）; 提供良好的相关性排名 支持分布式搜索； 提供文档摘要生成； 提供从MySQL内部的插件式存储引擎上搜索 支持布尔，短语， 和近义词查询； 支持每个文档多个全文检索域（默认最大32个）; 支持每个文档多属性； 支持断词； 支持单字节编码与UTF-8编码；)

使用INNER JOIN

in列表不宜过长，最好不要超过200。对于高并发的业务，小于几十为佳。若能将其转化为多个等于的查询则会更优。

建议尽量使用union all

由于MySQL的内存临时表不支持BLOB、TEXT类型，如果包含该类型列的查询需要用到临时表，就会使用基于磁盘的临时表，性能将会急剧降低。所以，编写查询语句时，如果没有必要包含BLOB、TEXT列，就不要写入查询条件。

规避的办法：

使用substring()函数。设置MySQL变量tmpdir，把临时表存放在基于内存的文件系统中。如Linux下的tmpfs。可以设置多个临时表的路径(用分号分隔)，MySQL将使用轮询的方式。

优化的方法：

如果必须使用，可以考虑拆分表，把BLOB、TEXT字段分离到单独的表。如果有许多大字段，可以考虑合并这些字段到一个字段，存储一个大的200kb比存储20个10kb更高效。考虑使用compress()，或者在应用层进行压缩，再存储到BLOB字段中。

有时我们使用EXPLAIN工具，可以看到查询计划的输出中的Extra列有filesort。filesort往往意味着你没有利用到索引进行排序。

MySQL有两种filesort算法：two-pass和single-pass。

max_length_for_sort_data：如果各列长度之和(包括选择列、排序列)超过了max_length_for_sort_data字节，那么就使用two-pass算法。如果排序BLOB、TEXT字段，使用的也是two-pass算法，那么这个值设置得太高会导致系统I/O上升，CPU下降，建议不要将其设置得太高。

max_sort_length：如果排序BLOB、TEXT字段，则仅排序前max_sort_length个字节。

可以考虑的优化方向如下：

加大sort_buffer_size，一般情况下使用默认的single-pass算法即可。可以考虑加大sort_buffer_size以减少I/O。需要留意的是字段长度之和不要超过max_length_for_sort_data，只查询所需要的列，注意列的类型、长度。对于two-pass算法，可以考虑增大read_rnd_buffer_size，但由于这个全局变量是对所有连接都生效的，因此建议只在会话级别进行设置，以加速一些特殊的大操作。在操作系统层面，优化临时文件的读写。

MySQL的临时表分为“内存临时表”和“磁盘临时表”，其中，内存临时表使用MySQL的MEMORY存储引擎；磁盘临时表使用MySQL的MyISAM存储引擎。一般情况下，MySQL会先创建内存临时表，但当内存临时表超过配置参数指定的值后，MySQL会将内存临时表导出到磁盘临时表。

（编辑：海南站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!