MySQL 性能优化

简单就是美! 做数据库擅长做的事情, 尽量不在数据库中做运算, 什么now(), md5(), rand()等等, 全部干掉. 把运算复杂的, 业务复杂的都交给程序完成.

小就是美! 单库不超过 300-400 张表, 单表不超过 50 个纯INT字段, 不超过 20 个CHAR 字段, 单条记录不超过 200 Byte, 单表字段数上限 50 个.

合理分表, 水平切割, 把数据量减少, 或垂直切割. 垂直切割依据, 定长 or 不定长, 非空 or 可选, 经常更新 or 从不更新.

开启MySQL的查询缓存

关闭dns反向查询

选择正确的存储引擎, InnoDB ? MyISAM ? ...

SQL语句要精简, 大 SQL 可以采用多个简单 SQL 来实现

保持事务短小精简, 做到即开即用, 用完即关, 与事务无关的操作放到事务外面, 减少锁资源的占用

定义字段类型时, 数字类型优于字符类型

用 INT UNSIGNED 存储 IP 字段

优先使用 ENUM 与 SET 而不是 varchar.  char 要优于 varchar

避免使用 NULL 字段, 在字段定义的时候加上默认值 NOT NULL DEFAULT, 含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值

远离 TEXT 与 BLOB

不在MySQL中存储图片或文件

索引不是越多越好, 合理添加索引, 能不加索引尽量不加, SQL 查询优先考虑索引, 不要给"性别"这样的列添加索引

不在索引列做运算, 如某表id为索引, where id = 123 - 1 优于 where id + 1 = 123

主键不应更新修改, 忌用字符串做主键

少用 order by rand()

尽量不用外键, 对于数据迁移是个噩梦, 线上尽量不用外键

减少锁表时间, 特别是MyISAM

尽可能少用存储过程, 少用触发器, 这些东西由程序负责

尽量不用 select *, 只取需要数据列

同一字段, 将 or 改为 in(), 如 select id,name from tb where id=1 or id=10; 改为 select id,name from tb where id in(1,2);

注意控制in的个数, 建议小于200个

不同字段, 将 or 改为 union. 如果不需要对结果进行去重, 用 union all , 而非 union

避免像 not, !=, <>, not exists, not like, not in这样的负向查询, where子句的查询条件里有！=，MySQL将无法使用索引

避免前缀模糊查找 like 'abc%' 优于 like '%abc%',  如 like 'xxx%' 索引有效，而like '%xxx%' 时索引无效

select count(id) 优于 select count(*)

尽量用limit限制返回记录条数

分页优化: where id>123 limit 10 优于 limit 1000,10

对于group by, 如果不需要对结果排序, 请在group by 后面加上 order by NULL

拒绝不同类型的数据进行比较

尽量在多个条件的时候，把会提取尽量少数据量的条件放在前面，减少后一个where条件的查询时间

load data的方法比insert导入数据快

尽量不用insert ... select

 

----------------------------------------------

巧用 MySQL 的查询分析工具 explain

mysql> explain select * from (select id,name from tb) a;

+----+----------------+----------------------+----------+------------------+-------+-----------+-------+-------+--------+
| id   | select_type | table                    | type     | possible_keys | key   | key_len | ref     | rows | Extra  |
+----+----------------+----------------------+----------+------------------+-------+-----------+-------+-------+--------+
|  1  | PRIMARY       | <derived2>         | system | NULL               | NULL | NULL     | NULL  |    1   | NULL  |
|  2  | DERIVED       | tb                        | system | NULL               | NULL | NULL     | NULL  |    1   | NULL  |
+----+----------------+----------------------+----------+------------------+-------+-----------+-------+-------+--------+
1 row in set (0.00 sec)

 

参数说明:

id : SQL执行的顺利的标识,SQL从大到小的执行.

select_type : select类型, 主要是区别普通查询和联合查询, 子查询之类的复杂查询. 

table : 输出的行所引用的表

type : 联合查询所使用的类型, type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL . 一般来说，得保证查询至少达到range级别，最好能达到ref

possible_keys : 指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引

key : 显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL

key_len : 显示MySQL决定使用的键长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分

ref : 显示哪个字段或常数与key一起被使用

rows : 这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的

Extra : 如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。如果是where used，就是使用上了where限制。如果是impossible where 表示用不着where，一般就是没查出来啥。如果此信息显示Using filesort或者Using temporary的话会很吃力，WHERE和ORDER BY的索引经常无法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。

常见的一些名词解释 (网上摘抄)
Using filesort
MySQL需要额外的一次传递，以找出如何按排序顺序检索行。

Using index
从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

Using temporary
为了解决查询，MySQL需要创建一个临时表来容纳结果。

ref
对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取

ALL
完全没有索引的情况，性能非常地差劲。

index
与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。

SIMPLE
简单SELECT(不使用UNION或子查询)

----------------------------------------------

运维人员必备 : MySQL 的 show 命令

show status : 显示MySQL状态信息, 可以通过 show status like 'XXX' 快速查找

show variables : 显示MySQL的系统变量信息, 可以通过 show variables like 'XXX' 快速查找

show processlist : 显示当前SQL执行情况, 包括执行状态, 是否锁表等

 

 

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

 

留给日后更新