mysql优化

1. 定位慢查询

show variables like 'slow_query%'

2. 选择合适的数据类型

• 使用可存下数据的最小的数据类型,int < date, time < char, varchar < blob
• 使用简单的数据类型，整型比字符串处理开销更小
• 使用合理的字段属性长度，固定长度的表会更快
• 尽可能使用not null 定义字段
• 尽量少用text

3. 选择合适的索引列

• 查询频繁的列，在where， group by， order by, join on从句中出现的列
• where条件中<, <=, =, >=, between, in, 以及like字符串+通配符（%）出现的列
• 长度小的列，索引字段越小越好
• （离散度大）不同的值多的列，放在联合索引前面

// 查看离散度
select count(distinct col_name) from table;

索引类型：
普通索引index：适用于name。email等一般属性
唯一索引unique：与普通索引类似，但唯一
主键索引primary：主键索引，一般建表的时候添加
全文索引：只适用于varchar和text字段，且只支持英文，中文需使用Sphinx等技术
组合索引：就是建立索引时指定多个字段属性
生效原则：从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；
例：（abc）索引 select * from mytable where a=3 and b=5 and c=4;
查看索引：show index/keys from table_name;

4. sql语句优化

• 避免全表扫描
• 避免在where字句中使用!=或<>操作符，否则将放弃使用索引而进行全表扫描
• 避免在where字句中进行null判断，select colname from table where col is null, 可以把null设为默认值0
• 避免在where字句中使用or连接，否则将进行全表扫描，可以使用union连接2个select查询
• 避免在where字句中使用like，否则将进行全表扫描
• in和not in也会进行全表扫描，对于连续的数字可以使用between，也可以使用exists代替in
• 避免在where字句的字段进行函数操作，如where num/2=100, 应该改为num=100*2
• 避免使用select * 要用具体字段代替
• 只查询一条数据时，使用limit 1
• 使用join来代替子查询

5. 使用命令行分析

• show查看状态

// 1. 显示状态信息
> show [session|global] status like '%status_name%'
//a 查看查询次数【插入，修改，删除】
com_select
b 查看连接数(登陆次数)
connections
c 数据库运行时间
uptime
d 慢查询次数
slow_queries
e 查看索引使用的情况
handler_read
> handler_read_key 越高越好，表示使用索引查询到的次数
handler_read_rnd_next 越高说明查询越低效

// 2 显示系统变量
> show variables like '%variables_name%'

// 3 显示InnoDB存储引擎的状态
> show engine innodb status;

// 4 expain 分析查询
> explain select ...
explain查询sql执行计划，各列含义：
table：表名；
type：连接的类型
    -const：主键、索引；
    -eq_reg：主键、索引的范围查找；
    -ref：连接的查找（join）
    -range：索引的范围查找；
    -index：索引的扫描；
    -all：全表扫描；
possible_keys：可能用到的索引；
key：实际使用的索引；
key_len：索引的长度，越短越好；
ref：索引的哪一列被使用了，常数较好；
rows：mysql认为必须检查的用来返回请求数据的行数；
extra：using filesort、using temporary（常出现在使用order by时）时需要优化。
    -Using filesort  额外排序。看到这个的时候，查询就需要优化了
    -Using temporary 使用了临时表。看到这个的时候，也需要优化

// 5 开启profile，查看当前sql的执行时间
> set profiling=on;
> show profiles;
// 查看所有用户当前连接，包括执行状态等
> show processlist;
// produce analyse()通过扥洗select结果对现有的表的每一列给出优化的建议
> select column_name from table produce analyse();

//6 optimize table 回收闲置的数据库空间
> optmize table table_name;
//7 repair table 修复被破坏的表
> repair table table_name;
//8 check table 检查表是否有错误
> check table table_name;