【转】30种MySQL索引优化的方法cswu

在数据库操作中，对查询进行优化极为关键。首先，应尽量规避全表扫描，优先考虑在 where 及 order by 涉及的列上构建索引。要避免在 where 子句中运用!=或操作符，不然引擎会舍弃索引而进行全表扫描。也不要在 where 子句里对字段开展 null 值判断，否则也会导致引擎放弃索引全表扫描，比如：select id from t where num is null ，可在 num 上设默认值 0 ，改为 select id from t where num = 0 。应避免在 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 。以下查询会引发全表扫描：select id from t where name like '%abc%' ，而对于 like '..%'(不以%开头)，可应用 colunm 上的 index 。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 。若在 where 子句中使用参数，会导致全表扫描，如 select id from t where num = @num ，可改为强制查询使用索引：select id from t with(index(索引名)) where num = @num 。应避免在 where 子句中对字段进行表达式操作，如 select id from t where num / 2 = 100 ，应改为 select id from t where num = 100 * 2 。也不要在 where 子句中对字段进行函数操作，像 select id from t where substring(name, 1, 3) = 'abc'以及 select id from t where datediff(day, createdate, '2005 - 11 - 30') = 0，应改为 select id from t where name like 'abc%' 和 select id from t where createdate = '2005 - 11 - 30' and createdate 关于 select Count()和 Select Count(1)以及 Select Count(column)的区别：一般情况下，Select Count()和 Select Count(1)两者返回结果相同。假如表沒有主键(Primary key)，那么 count(1)比 count()快，如果有主键的話，那主键作为 count 的条件时候 count(主键)最快，如果你的表只有一个字段的话那 count()就是最快的。count()跟 count(1)的结果一样，都包括对 NULL 的统计，而 count(column)是不包括 NULL 的统计。对于索引列上计算引起的索引失效及优化措施以及注意事项：创建索引、优化查询以便达到更好的查询优化效果。但实际上，MySQL 有时并不按我们设计的那样执行查询。MySQL 是根据统计信息来生成执行计划的，这就涉及索引及索引的刷选率，表数据量，还有一些额外的因素。Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size. 简而言之，当 MYSQL 认为符合条件的记录在 30%以上，它就不会再使用索引，因为 mysql 认为走索引的代价比不用索引代价大，所以优化器选择了自己认为代价最小的方式。是 MYSQL 认为记录是 30%以上，而不是实际 MYSQL 去查完再决定的。MYSQL 会先估算，然后决定是否使用索引。