mysql查询优化

一、查询优化

• 用left join查询速度大于not in
• 避免 SELECT *全表扫描，只查询需要的字段
• 使用 IN 替代多个 OR 条件，因为 IN 可以更有效地使用索引。
• 避免在 WHERE 子句中对字段进行函数操作，这会导致 MySQL 无法使用索引。
• 小表驱动大表，即小的数据集驱动大的数据集
  • 当B表的数据集比A表小时，用in优化 exist两表执行顺序是先查B表再查A表查询语句：
    SELECT * FROM A WHERE id in (SELECT id FROM B)
  • 当A表的数据集比B表小时，用exist优化in ，两表执行顺序是先查A表，再查B表，查询语句：
    SELECT * FROM A WHERE EXISTS (SELECT id FROM B WHERE A.id = B.ID)
• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
• 一般情况下不推荐使用like操作。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引
• 尽量使用连接代替子查询，因为使用 join 时，MySQL 不会在内存中创建临时表
• or 查询改写成 union 查询
• 尽量避免在 where 子句中使用 != 或者 <> 操作符，查询引用会放弃索引而进行全表扫描
• 查询语句尽可能简单，大语句拆小语句，减少锁时间
• 避免频繁创建和删除临时表，以减少系统表资源的消耗
• 尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些
• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销
• 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert
• 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写
• 尽量避免大事务操作，提高系统并发能力
• 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
• 如果排序字段没有用到索引，就尽量少排序
• 使用合理的分页方式以提高分页的效率。

二、索引

• 查看表索引：
  show index from table
• 直接创建索引
  CREATE INDEX indexName ON table(column(length))
• 修改表结构的方式添加索引
  ALTER tableADD INDEX indexName ON (column(length))
• 主键索引
  ALTER TABLE table_name ADD PRIMARY KEY ( column )
• 唯一索引
  ALTER TABLE table_name ADD UNIQUE ( column )
• 普通索引
  ALTER TABLE table_name ADD INDEX index_name ( column (length) )
• 复合索引
  ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 ) index_name on table_name
• length的确定：
  如果索引列长度过长,这种列索引时将会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行索引，前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值就可以创建)。
  SELECT COUNT(DISTINCT(LEFT( title,10)))/COUNT(*) FROM Arctic
  这个值大于0.31就可以创建前缀索引,Distinct去重复
• 删除索引：
  • ALTER TABLE table_name DROP INDEX index_name
  • DROP INDEX
• 索引的缺点
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件
  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快