mySQL优化总结

mySQL优化总结

mysql执行计划解析

MySQL通过explain命令输出执行计划，对要执行的查询在这里插入图片描述进行分析。
执行计划的限制
无法展示存储过程，触发器，UDF(自定义函数)对查询的影响
无法使用EXPLAIN对存储过程进行分析
早期版本的MySQL只支持对SELECT语句进行分析，如果想要分析UPDATE,INSERT语句需要将它们通过某种手段转换成SELECT语句。
一、解释执行计划中EXPLAIN
关键注意select_type,type，possible_keys,key,Extra
（1）select_type

union，第二个select出现在union之后，被标记为该值；union包含在from的子查询中，外层select被标记为derived;union result，从union表获取结果的select；
（2）type
eq_ref，唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用语主键或唯一索引扫描； ref，非唯一性索引扫描，返回匹配某个单独值的所有行，用于=、<或>操作符带索引的列；
前5种情况都是理想的索引的情况。通常优化至少到range级别，最好能优化到ref。
（3）possible_keys ：指出 MySQL 使用哪个索引在该表找到行记录。如果该值为 NULL，说明没有使用索引，可以建立索引提高性能)
（4）key ：显示 MySQL 实际使用的索引。如果为 NULL，则没有使用索引查询
（5）key_len：表示索引中使用的字节数，通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好显示的是索引字段的最大长度，并非实际使用长度；
（6）ref：显示该表的索引字段关联了哪张表的哪个字段；
（7）extra：包含不适合在其他列中显示但十分重要的额外信息。
常见的值如下：
using filesort，MySQL会对数据使用一个外部索引排序，而不是按照表内索引顺序 进行读取，若出现改值，则应优化SQL语句；
using temporary，使用临时表缓存中间结果，比如，MySQL在对查询结果排序时使用临时表，常见于order by和group by，若出现该值，则应优化SQL；
using index，表示select操作使用了覆盖索引，避免了访问表的数据行；
using where，where子句用于限制哪一行；
using join buffer，使用连接缓存；
distinct，发现第一个匹配后，停止为当前的行组合搜索更多的行；

数据库创建索引

（1）ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
（2）ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
（3）column_list): 添加普通索引，索引值可出现多次。
（4）ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
例如：

创建索引：ALTER TABLE testalter_tbl ADD INDEX ©;索引名为c
等同于 ALTER TABLE testalter_tbl ADD INDEX c©
删除索引：ALTER TABLE testalter_tbl DROP INDEX ©;
显示索引信息
SHOW INDEX FROM table_name\G

联合索引的使用规则（重要）

总结：
联合索引：ALTER TABLE t ADD INDEX c1234(c1,c2,c3,c4);
索引的最左原则（左前缀原则），如（c1,c2,c3,c4…cN）的联合索引，where 条件按照索引建立的字段顺序来使用（and字段顺序无关），使用like时，条件列属于联合索引中间某列就不能使用索引，比如（

1. EXPLAN select * from t where c3 like ‘1’;
   直接使用c3是全表查询，无法使用该索引的，所以c3字段使用 索引的前提是c1，c2两字段均使用了索引。
2. EXPLAN select * from t where c1 = ‘1’ and c4 >‘0’ and c3 = ‘1’;
   c2字段没有使用索引，c2之后的字段都不能使用索引
   ）
   索引也能用于分组和排序，分组要先排序，在计算平均值等等。所以在分组和排序中，如果字段顺序可以按照索引的字段顺序，即可利用索引的有序特性
   联合索引的使用规则参考shuo

SQL优化方向

sql优化文档