“MYSQL”查寻性能优化

“MYSQL”查寻性能优化

注 : 1  MYSQL官网的优化方法：http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#order-by-optimization

第一：索引问题

下面列出了目前 MySQL 中与索引使用相关的限制。

（1）MyISAM存储引擎索引键长度的总和不能超过1000字节；

（2）BLOB和TEXT类型的列只能创建前缀索引；

（3）MySQL 目前不支持函数索引；

（4）使用不等于（!= 或者 <>）的时候，MySQL 无法使用索引；

（5）过滤字段使用了函数运算（如abs（column））后，MySQL无法使用索引；

（6）Join 语句中 Join 条件字段类型不一致的时候，MySQL 无法使用索引；

（7）使用LIKE操作的时候如果条件以通配符开始（如’%abc…’）时，MySQL无法使用索引；

（8）使用非等值查询的时候，MySQL 无法使用 Hash 索引。

注：摘至http://www.kaishixue.com/tech/mysql/mysql-index-no/

1 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2 经常同时存取多列，且每列都含有重复值可考虑建立组合索引

3 组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。

4 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要.

5 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有 字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用.

6 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会 被使用，并且应尽可能的让字段顺序与索引顺序相一致.

第二： Where(IN)问题

 1 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3 .应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

4.in 和 not in 也要慎用，否则会导致全表扫描(有空值等)，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

5. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num <mailto:num=@num>

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num <mailto:num=@num>

6.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

7.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’–name以abc开头的id

select id from t where datediff(day,createdate,’2005-11-30′)=0–‘2005-11-30’生成的id

应改为:

select id from t where name like ‘abc%’

select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

8 .不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引

9 关于UNION(有时取代where in)

UNION:

  IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引.

   例如 select * from city where id in (2,5);

  SELECT * FROM city where id = 2 UNION SELECT * FROM city where id = 5

如果加上关键字ALL（即UNION ALL）

SELECT * FROM city where id = 2 UNION ALL SELECT * FROM city where id = 5

两者差异 :

UNION 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录， 最常见的是过程表与历史表UNION。UNION ALL 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。从效率上说，UNION ALL 要比UNION快很多。

UNION与ORDER BY

(1 )如果要使用ORDER BY 或者LIMIT子句来对全部 UNION结果进行分类或限定。则应对单个地SELECT语句加圆括号，并把ORDER BY

或LIMIT放到最后一个的后面。

(SELECT * FROM city where id = 2) UNION ALL (SELECT * FROM city where id = 5) ORDER BY id LIMIT 10;

(2 )如果要使用ORDER BY 或者LIMIT子句来对单个 UNION结果进行分类或限定,则应把ORDER BY或LIMIT放到圆括号中。

(SELECT * FROM city where id = 2 ORDER BY id LIMIT 10) UNION ALL (SELECT * FROM city where id = 5 ORDER BY id LIMIT 10) ;

另注：(1) 在UNION中ORDER BY 不能使用包括表名称(即table_name.col_name)列引用格式.

           (2) 如果带分类的一列有别名，则orderby 子句必须引用别名，而不能引用列名称。

第三：其他

 1 只要能满足你的需求，应尽可能使用更小的数据类型：例如使用MEDIUMINT代替I

 2 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段.

 3 在计算行总数时，select count(*) from t 较好。

  4 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert

 注：以上信息来致WEB~以及自己的总结。

 

Query 语句的优化思路和原则 :
1. 优化更需要优化的 Query ；
2. 定位优化对象的性能瓶颈；
3. 明确的优化目标；
4. 从 Explain 入手；
5. 多使用 profile
6. 永远用小结果集驱动大的结果集；
7. 尽可能在索引中完成排序；
8. 只取出自己需要的 Columns ；
9. 仅仅使用最有效的过滤条件；
10. 尽可能避免复杂的 Join 和子查询；

(摘至：《 MySQL性能调优与架构设计 》 )

再次提醒： 优化不是这上面几句话就搞定了的，应视具体情况而定（ 尽信书不如无书 ） :-)