mysql

    xiaoxiao2021-12-12  6

    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 
    转载请注明原文地址: https://ju.6miu.com/read-900092.html

    最新回复(0)