mysql优化

sql优化

最近面试，在sql优化这块，一直都不是很熟悉，于是有了以下的总结，主要是在鲁玉成先生的笔记基础上，加入自己的理解。原文链接如下：
http://www.cnblogs.com/luyucheng/p/6323477.html

1.sql语句的优化

a.使用limit对查询结果的记录进行限定
b.避免select * ，将需要查找的字段列出来
c.使用连接（join）来代替子查询
d.拆分大的delete或者insert语句

1.1子查询

1.1.1mysql的五种查询子句：
    where:条件查询
    group by：按照属性名指定的字段进行分组
        其他字段如果想出现在select中，则必须包含在聚合函数中：
        min(),max(),sum(),avg(),count()
    having：有group by才能有having，满足条件在group by的属性名中输出
    order by：按照属性名指定的字段进行排序。排序方式有“asc”,"desc"
    limit：限制结果集
1.1.2子查询：
    where 子查询
        查询每个类别下id最大的商品
        select goods_id,goods_name,cat_id,shop_price from goods 
            where goods_id In
            (select max(goods_id) from goods group by cat_id);
    from型子查询
        select goods_id,goods_name,cat_id,shop_price from
            (select goods_id,goods_name,cat_id,shop_price from goods order by cat_id asc,goods_id desc)as temp 
            group by cat_id;
    exists子查询
    从类别表中取出其类别下有商品的类别(如果该类别下没有商品，则不取出)[使用exists子查询]
     SELECT c.cat_id,c.cat_name FROM category c WHERE EXISTS (SELECT 1 FROM goods g WHERE g.cat_id = c.cat_id);

1.2连接查询

1、全相乘，做笛卡儿积。（效率低）
2、左连接查询 left join ... on ... 
3、右连接查询 right join ... on ...
4、内连接查询 inner join ... on ...
5、全连接查询 full join ... on ...
6、联合查询 
    6.1、将两张表的数据合并查询出来
        SELECT id, content, user FROM comment UNION (SELECT id, msg AS content, user FROM feedback);
    6.2、union查询，列名不一致时，以第一条sql语句的列名对齐
    6.3、使用union会将重复的行过滤掉
    6.4、使用union all 查询所有，重复的行不会被过滤
        SELECT content,user FROM comment UNION ALL (SELECT msg, user FROM feedback);
    6.5、union查询，如果列数不相等，会报列数不相等错误
    6.6、union后的结果集还可以再筛选
        SELECT id,content,user FROM comment UNION ALL (SELECT id, msg, user FROM feedback) ORDER BY id DESC;
        注意order by放在排序放在内层sql是不起作用的，因为union查出来的结果会自动排序。但是内层，order by 和limit一起使用，就不会被优化掉

2.选择合适的数据类型

（1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
（2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
（3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
（4）尽可能使用not null定义字段
（5）尽量少用text，非用不可最好分表

3.选择合适的索引列

（1）查询频繁的列，在where，group by，order by，on从句中出现的列
（2）where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
（3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
（4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高：
SELECT COUNT(DISTINCT column_name) FROM table_name;

4.使用命令分析

（1）SHOW查看状态
    1.显示状态信息
        mysql> SHOW [SESSION|GLOBAL] STATUS LIKE '%Status_name%';
        session（默认）：取出当前窗口的执行
        global：从mysql启动到现在
    （a）查看查询次数（插入次数com_insert、修改次数com_insert、删除次数com_delete）
        mysql> SHOW STATUS LIKE 'com_select';
    （b）查看连接数(登录次数)
        mysql> SHOW STATUS LIKE 'connections';
    （c）数据库运行时间
        mysql> SHOW STATUS LIKE 'uptime';
    （d）查看慢查询次数
        mysql> SHOW STATUS LIKE 'slow_queries';
    （e）查看索引使用的情况：
        mysql> SHOW STATUS LIKE 'handler_read%';
            handler_read_key：这个值越高越好，越高表示使用索引查询到的次数。
            handler_read_rnd_next：这个值越高，说明查询低效。
    2.显示系统变量
        mysql> SHOW VARIABLES LIKE '%Variables_name%';
    3.显示InnoDB存储引擎的状态
        mysql> SHOW ENGINE INNODB STATUS;
    （2）EXPLAIN分析查询
        mysql> EXPLAIN SELECT column_name FROM table_name;
        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 使用了临时表。看到这个的时候，也需要优化
    （3）PROFILING分析SQL语句
        1.开启profile。查看当前SQL执行时间
            mysql> SET PROFILING=ON; 
            mysql> SHOW profiles;
        2.查看所有用户的当前连接。包括执行状态、是否锁表等
            mysql> SHOW processlist;
    （4）PROCEDURE ANALYSE()取得建议
        通过分析select查询结果对现有的表的每一列给出优化的建议
        mysql> SELECT column_name FROM table_name PROCEDURE ANALYSE();
    （5）OPTIMIZE TABLE回收闲置的数据库空间
        mysql> OPTIMIZE TABLE table_name;
        对于MyISAM表，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）。
        对于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE上，这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。
        只需在批量删除数据行之后，或定期（每周一次或每月一次）进行一次数据表优化操作即可，只对那些特定的表运行。
    （6）REPAIR TABLE修复被破坏的表
           mysql> REPAIR TABLE table_name;
    （7）CHECK TABLE检查表是否有错误
        mysql> CHECK TABLE table_name;