MySql优化【3】--SQL语句优化

1、SQL优化的一般步骤
1-1、通过show status命令了解各种SQL的执行频率。
1-2、定位执行效率较低的SQL语句-（重点select）
1-3、通过explain分析低效率的SQL语句的执行情况
1-4、确定问题并采取相应的优化措施

面试题 ：sql语句有几类

ddl (数据定义语言) [create alter drop]
dml(数据操作语言)[insert delete upate ]
select
dtl(数据事务语句) [commit rollback savepoint]
dcl(数据控制语句) [grant  revoke]
2、SQL语句优化-show参数
    MySQL客户端连接成功后，通过使用show [session|global] status 命令可以提供服务器状态信息。其中的session来表示当前的连接的统计结果，global来表示自数据库上次启动至今的统计结果。默认是session级别的。
    show status like ‘Com_%’;其中Com_XXX表示XXX语句所执行的次数。
    重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。
    show status like ‘Com%’  <=> show session  status like ‘Com%’  //显示当前控制台的情况
    show global  status like ‘Com%’ ; //显示数据库从启动到 查询的次数

    还有几个常用的参数便于用户了解数据库的基本情况。

    Connections：试图连接MySQL服务器的次数
    show status like  'Connections';
    Uptime：服务器工作的时间（单位秒）
    Slow_queries：慢查询的次数 (默认是10)
3、SQL语句优化-定位慢查询
    在默认情况下mysql不记录慢查询日志，需要在启动的时候指定bin\mysqld.exe - -slow-query-log
通过慢查询日志定位执行效率较低的SQL语句。慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句。
show variables like ‘long_query_time’;
set long_query_time=2;

    查看慢查询日志：默认为数据目录data中的host-name-slow.log。

    低版本的mysql需要通过在开启mysql时使用- -log-slow-queries[=file_name]来配置

4、SQL语句优化-explain分析问题
5、利用索引优化
5-1、建立适当的索引
    说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行个正确的’createindex’，查询速度就可能提高百倍千倍，这可真有诱惑力。可是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。 

5-2、索引的代价

磁盘占用
对dml(update delete insert)语句的效率影响

5-3、哪些列上适合添加索引

较频繁的作为查询条件字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件：select * from emp where sex = '男'
更新非常频繁的字段不适合创建索引
不会出现在WHERE子句中字段不该创建索引

5-4、索引的类型

1.主索引，主键自动的为主索引 (类型Primary)(把某列设为主键，则就是主键索引)
2.唯一索引 (UNIQUE)（即该列具有唯一性，同时又是索引）
3.普通索引 (INDEX)（普通索引）
4.全文索引 (FULLTEXT)（只有MyISAM存储引擎支持）sphinx + 中文分词    coreseek
5.复合索引(多列和在一起) create index myind on 表名 (列1,列2);

5-5、索引的使用

如果创建unique / 普通/fulltext 索引
1. create [unique|FULLTEXT] index 索引名 on 表名 (列名...)
2. alter table 表名 add index 索引名 (列名...)
//如果要添加主键索引
alter table 表名 add primary key (列...)
删除索引
1.drop index 索引名 on 表名
2.alter table 表名 drop index index_name;
3.alter table 表名 drop primary key
查询索引(均可)
show index(es) from table_name;
show keys from table_name;
desc table_Name;

5-6、查询要使用索引最重要的条件是查询条件中需要使用索引。

下列几种情况下有可能使用到索引：
1，对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
2，对于使用like的查询，查询如果是  ‘%aaa’ 不会使用到索引
‘aaa%’ 会使用到索引。

5-7、下列的表将不使用索引：

1，如果条件中有or，即使其中有条件带索引也不会使用。
2，对于多列索引，不是使用的第一部分，则不会使用索引。
3，like查询是以%开头
4，如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。
5，如果mysql估计使用全表扫描要比使用索引快，则不使用索引

5-8、查看索引的使用情况

show status like ‘Handler_read%’;
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高，说明查询低效。
6、其他优化
6-1、大批量插入数据
对于MyISAM：
alter table table_name disable keys;
loading data;
alter table table_name enable keys;
对于Innodb：
1，将要导入的数据按照主键排序
2，set unique_checks=0,关闭唯一性校验。
3，set autocommit=0,关闭自动提交。
优化group by 语句
默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序
使用order by null  禁用排序
比如 select * from dept group by ename order by null

6-2、有些情况下，可以使用连接来替代子查询。

因为使用join，MySQL不需要在内存中创建临时表。(讲解)
如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引(与环境相关 讲解)select * from 表名 where 条件1=‘’ or 条件2=‘tt’

6-3、选择合适的存储引擎

MyISAM:默认的MySQL存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高。其优势是访问的速度快。
InnoDB：提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM，写的处理效率差一些并且会占用更多的磁盘空间

6-4、选择合适的数据类型

在精度要求高的应用中，建议使用定点数来存储数值，以保证结果的准确性。
对于存储引擎是MyISAM的数据库,如果经常做删除和修改记录的操作，要定时执行optimize table table_name;功能对表进行碎片整理。
日期类型要根据实际需要选择能够满足应用的最小存储的早期类型
create table bbs(id int ,con varchar(1024) , pub_time int);
date(‘Ymd’,时间-3*24*60*60); 2038年-1-19

6-5、对表进行水平划分

如果一个表的记录数太多了，比如上千万条，而且需要经常检索，那么我们就有必要化整为零了。如果我拆成100个表，那么每个表只有10万条记录。当然这需要数据在逻辑上可以划分。一个好的划分依据，有利于程序的简单实现，也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能，那么把表按月 拆分成12个，每个查询只查询一个表就够了。如果非要按照地域来分，即使把表拆的再小，查询还是要联合所有表来查，还不如不拆了。所以一个好的拆分依据是 最重要的。UNION 

6-6、对表进行垂直划分

有些表记录数并不多，可能也就2、3万条，但是字段却很长，表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。这个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。 (JOIN)

一句话: 如果一张表某个字段，信息量大，但是我们很少查询，则可以考虑把这些字段，单独的放入到一张表中，这种方式称为垂直分割.

6-7、选择适当的字段类型，特别是主键

选择字段的一般原则是保小不保大，能用占用字节小的字段就不用大字段。比如主键， 建议使用自增类型，这样省空间,空间就是效率!按4个字节和按32个字节定位一条记录，谁快谁慢太明显了。涉及到 几个表做join时，效果就更明显了。建议使用一个不含业务逻辑的id做主角

6-8、文件、图片等大文件用文件系统存储

数据库只存储路径。图片和文件存放在文件系统，甚至单独放在一台服务器(图床).
7、不同数据库引擎的选择

7-1、MyISAM 和 Innodb区别是什么

1.MyISAM 不支持外键, Innodb支持
2.MyISAM 不支持事务,Innodb支持
3.对数据信息的存储处理方式不同.（如果存储引擎是MyISAM的，则创建一张表，对于三个文件..,如果是Innodb则只有一张文件 *.frm,数据存放到ibdata1）

如果你的数据库的存储引擎是MyISAM的，则当创建一个表，后三个文件. *.frm 记录表结构. *.myd 数据  *.myi 这个是索引.

4.查询和添加速度

5.MyISAM支持全文索引,Innodb不支持

对于 MyISAM 数据库，需要定时清理，因为即使删除数据也不会释放空间。需要定时进行碎片整理。不然表会越来越大。optimize table 表名

7-2、在开发中，我们经常使用的存储引擎 myisam/innodb/memory
myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎.比如 bbs 中的 发帖表，回复表.
INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.
Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快.