最全Mysql查询性能优化总结（超详细）

通过explain可以知道mysql是如何处理语句的，其实就是在干查询优化器的事。语句：EXPLAIN+sql查询语句

• Id: MySQL QueryOptimizer 选定的执行计划中查询的序列号。表示查询中执行select 子句或操作表的顺序,id 值越大优先级越高,越先被执行。id 相同,执行顺序由上至下
• Select_type: 一共有9中类型,只介绍常用的4种: SIMPLE: 简单的 select 查询,不使用 union 及子查询；PRIMARY: 最外层的 select 查询；UNION: UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集；DERIVED: 用于 from 子句里有子查询的情况。 MySQL 会递归执行这些子查询, 把结果放在临时表里
• Table: 输出行所引用的表
• Type: 从优到差的顺序如下:（* 的是常见的级别。）system-->const *-->eq_ref *-->ref *-->ref_or_null-->index_merge-->unique_subquery-->index_subquery-->range *-->index *-->all *

注：一般需要达到 ref、eq_ref 级别，范围查找需要达到 range

• possible_keys : 哪些索引可能有助于查询。如果为空,说明没有可用的索引
• key: 实际从 possible_key 选择使用的索引，如果为 NULL,则没有使用索引。其中key为null、all 、index时，需要调整、优化索引。很少的情况下,MYSQL 会选择优化不足的索引。这种情况下,可以在 SELECT语句中使用 USE INDEX (indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
• key_len: 使用的索引的长度。在不损失精确性的情况下,长度越短越好
• ref: 显示索引的哪一列被使用了
• rows: 请求数据返回的大概行数。根据rows可以直观看出优化结果
• extra: 其他信息，出现Using filesort、Using temporary 意味着不能使用索引,效率会受到重大影响。应尽可能对此进行优化。三者区别如下：

1. Using filesort: 没有办法利用现有索引进行排序，需要额外排序，建议：根据排序需要，创建相应合适的索引
2. Using temporary: 需要用临时表存储结果集，通常是因为group by的列上没有索引。也有可能是因为同时有group by和order by，但group by和order by的列又不一样
3. Using index ：利用覆盖索引，无需回表即可取得结果数据（即数据直接从索引文件中读取），这种结果是好的

合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容

1、星形模型(Star Schema)和雪花模型(SnowflakeSchema)的模型选择

2、增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率

3、优化数据类型

Mysql常用数据类型存储大小及范围：

4、表设计不能有太多的列，数千的列会影响性能

5、关联操作设计的表不要太多，否则执行会很慢

mysql限制了每个关联操作最多只能有61张表，在实际应用中，经验法则得出关联操作的表数量最好控制在12个以内

6、扩展：范式和反范式

范式主要优化的是增删改的性能，比如数据冗余、更新异常、删除异常等。绝对的范式化是实验室中的产物，在实际的应用中要混用范式化和反范式化，根据具有情况，往往会带来较高的查询效率

遵循数据库的范式要求，尤其是前三个范式。严格遵循范式设计的表通常更小、数据冗余少，做更新操作简单快捷，但是，唯一的缺点就是在做查询时需要表关联，关联查询会不仅会带来高的代价，而且还可能造成索引策略失效，导致更低效率的查询

索引应该是对查询性能优化最有效的手段了，它能够轻易地将查询效率提高几个数量级

4.2.1 Mysql索引类型

索引总结：

--查看表索引：

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去重复

--删除索引：

1）ALTER TABLE table_name DROP INDEX index_name

2）DROP INDEX

4.2.2 索引的缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件

建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快

4.2.3 索引的注意事项

• 避免 SELECT *，只查询需要的字段
• 小表驱动大表，即小的数据集驱动大的数据集

当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万行，那么就应该考虑改写
• 尽量避免大事务操作，提高系统并发能力
• 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
• 如果排序字段没有用到索引，就尽量少排序
• 使用合理的分页方式以提高分页的效率。

优化的方法可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。

select id,name from product where id> 866612 limit 20

• 巧用STRAIGHT_JOIN。inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join

? sort-buffer-size/join-buffer-size / read-rnd-buffer-size，4～8MB为宜

?optimizer_switch=“index_condition_pushdown=on,mrr=on,mrr_cost

_based=off,batched_key_access=on”

? tmp-table-size=max-heap-table-size，100MB左右为宜

? log-queries-not-using-indexes & log_throttle_queries_not_using_indexes

如何选择：

1. 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差