mysql

1.not in 子查询优化

select SQL_NO_CACHE count(*) from test1 where id not in(select id from test2);select SQL_NO_CACHE count(*) from test1 left join test2 on test1.id = test2.id where test2.id is null;在生产环境里，应尽量避免使用子查询，可以left join表连接取代之。

2.模式匹配 like '%xxx%' 优化 like   'xxx%'  可以用的索引，而like '%xxx%' 不行。 3.count(*) 优化

在MySQL中，日常开发中比较常用的有MyISAM和InnoDB两种存储引擎。两者之间的其中一个区别是使用count(*)函数计算表的具体行数。因为MyISAM会保存表的具体行数，因此这段代码在MyISAM存储引擎中执行，MyISAM只要简单地读出保存好的行数即可。因此，如果表中没有使用事务之类的操作，这是最好的优化方案。然而，InnoDB存储引擎不会保存表的具体行数，因此，在InnoDB存储引擎中执行这段代码，InnoDB要扫描一遍整个表来计算有多少行。在第一次使用了唯一索引(u_id)的时候，InnoDB使用了唯一索引作为表的聚簇索引。而在InnoDB存储引擎中，count(*)函数是先从内存中读取表中的数据到内存缓冲区，然后扫描全表获得行记录数的。因此，使用唯一索引作为聚簇索引的时候，InnoDB需要先读取110W条的数据到数据缓冲区中，这里发生了很多次I/O，因此造成了主要的时间消耗。而添加了辅助索引后，mysql在执行查询时会使用内部的优化机制：即使用辅助索引来统计数量。辅助索引保存的是index的值，此时只需要读取一个字段，I/O减少了，性能就提高了。因此在InnoDB中，如果有统计整张表的数量的需求，可以考虑增加一个辅助索引。 4. or 优化

     当使用or的时候是不会用到索引的

mysql> explain SELECT * FROM aladdin_resource WHERE  state = 1 OR state = 2; +----+-------------+------------------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table            | type | possible_keys | key  | key_len |  ref   | rows  | Extra       | +----+-------------+------------------+------+---------------+------+---------+------+-------+-------------+ |  1 | SIMPLE      | aladdin_resource | ALL  | state         | NULL | NULL    | NULL | 59074 | Using  where  | +----+-------------+------------------+------+---------------+------+---------+------+-------+-------------+ 1 row  in  set  (0.00 sec)

   解决办法就是用union替换or

1 2 3 4 5 6 7 8 9 explain  select  *  from  aladdin_resource  where  state=1 union  select  *  from  aladdin_resource  where  state=2; +----+--------------+------------------+------+---------------+-------+---------+-------+-------+-------------+ | id | select_type  | table            | type | possible_keys | key   | key_len |  ref    | rows  | Extra       | +----+--------------+------------------+------+---------------+-------+---------+-------+-------+-------------+ |  1 | PRIMARY      | aladdin_resource |  ref   | state         | state | 2       |  const  |   383 | Using  where  | |  2 | UNION        | aladdin_resource |  ref   | state         | state | 2       |  const  | 21370 | Using  where  | | NULL | UNION RESULT | <union1,2>       | ALL  | NULL          | NULL  | NULL    | NULL  |  NULL |             | +----+--------------+------------------+------+---------------+-------+---------+-------+-------+-------------+ 3 rows  in  set  (0.05 sec) 5.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引 6.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描 select id from t where num is null最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库。备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num = 0 7.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 8.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描 select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0