mysql及sql优化

一、mysql优化：

1.查询优化
慢查询日志开启并用mysqldumpslow分析
使用EXPLAIN查看SQL执行计划，用法：EXPLAIN SELECT * FROM products

2.使用索引
B-Tree索引
R-Tree索引在mysql很少使用，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。
Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用。
Full-text索引也就是我们常说的全文索引，目前在MySQL中仅有MyISAM存储引擎支持。

创建索引的原则：
较频繁的作为查询条件的字段应该创建索引；
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
更新非常频繁的字段不适合创建索引；
不会出现在WHERE子句中的字段不该创建索引；

3.存储优化
存储数据时，影响存储速度的主要是索引、唯一性校验、一次存储的数据条数等。
存储数据的优化，不同的存储引擎优化手段不一样，在MySQL中常用的存储引擎有，MyISAM和InnoDB。
MyISAM存储引擎是一种非事务性的引擎，适合数据仓库等查询频繁的应用。
Innodb 存储引擎是事务安全的,如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑应该使用InnoDB表。

MyISAM和Innodb的区别：
MyISAM是非事务安全型的，而InnoDB是事务安全型的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
MyISAM支持全文类型索引，而InnoDB不支持全文索引。
MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。

MyISAM存储优化：
在批量插入数据之前禁用索引，数据插入完成后再开启索引。
在插入记录之前禁用唯一性检查，插入数据完成后再开启。
批量插入数据，而不是一次只插入一条数据。
当需要批量导入数据时，使用LOAD DATA INFILE语句比INSERT语句插入速度快很多。load data infile "/data/mysql/e.sql" into table e fields terminated by ',';

InnoDB存储优化：
禁用唯一性检查
禁用外键检查
禁止自动提交

4.数据库结构优化

4.1优化表结构
尽量将表字段定义为NOT NULL约束，这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂。
对于只包含特定类型的字段，可以使用enum、set 等符合数据类型。
数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如P地址可以使用int类型。
尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
VARCHAR的长度只分配真正需要的空间
尽量使用TIMESTAMP而非DATETIME，
单表不要有太多字段，建议在20以内
合理的加入冗余字段可以提高查询速度。

4.2表拆分
4.3分区：使用分区是大数据处理后的产物。
4.4读写分离
4.5数据库集群

5.硬件优化

6.MySQL缓存
6.1全局缓存：启动MySQL时就要分配并且总是存在的全局缓存。
6.2局部缓存：除了全局缓冲，MySql还会为每个连接发放连接缓冲。
6.3其它缓存：缓存线程和将要读取的表等。

7.MySQL服务器参数
MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中。主要是连接数，超时时间等配置。

二、sql优化：

1.选择最有效率的表名顺序
在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,
然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

2.WHERE子句中的连接顺序
采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
同时在链接的表中能过滤的就应该先进行过滤。

3.SELECT子句中避免使用 ‘ * ‘
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.但这是一个非常低效的方法. 
实际上,在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

4.尽量多使用COMMIT,有利于释放资源和锁

5.计算记录条数
count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)

6.用Where子句替换HAVING子句
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

7.通过内部函数提高SQL效率

8.使用表的别名(Alias)
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

9.用EXISTS替代IN，用NOT EXISTS替代NOT IN

10.用表连接替换EXISTS

11.用索引提高效率

12.避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描

13.自动选择索引
如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性。
在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引。
唯一索引简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率。

14.避免在索引列上使用NOT

15.用>=替代>
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

16.用UNION替换OR (适用于索引列)
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描.

17.用IN来替换OR

18.避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列

19.总是使用索引的第一个列
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.

20.用UNION-ALL 替换UNION ( 如果有可能的话)
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.
union:如果查询出来的结果中有重复记录，那么就去重 ，英文称之为"distinct"
union all:就显示所有的符合条件的记录，重复也保留。

21.ORDER BY 子句只在两种严格的条件下使用索引.
ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
ORDER BY中所有的列必须定义为非空.

22.避免改变索引列的类型.
当比较不同数据类型的数据时,自动对列进行简单的类型转换.
注意当字符和数值比较时, ORACLE会优先转换字符类型到数字类型.

23.需要当心的WHERE子句
某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.
在下面的例子里, ‘!=’ 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. ‘+’是数学函数. 就象其他数学函数那样, 停用了索引

24.避免使用耗费资源的操作
带有DISTINCT,UNION,MINUS,INTERSECT（交叉）,ORDER BY的SQL语句会启动SQL引擎
执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.