SQL-优化查询

优化查询的方法

1.使用索引

尽量避免全表扫描，首先应考虑在 where 及 order by ,group by 涉及的列上建立索引。

2.优化SQL语句

2.1 explain查看SQL语句执行效果

可以帮助选择更好的索引和优化查询语句， 写出更好的优化语句。 通常我们可以对比较复杂的尤其是涉及到多表的 SELECT 语句，
 把关键字 EXPLAIN 加到前面， 查看执行计划。例如： explain select * from news;

2.2 任何地方都不要使用 select * from t ， 用具体的字段列表代替“*” ， 不要返回用不到的任何字段。

2.3 不在索引列做运算或者使用函数。

2.4 查询尽可能使用 limit 减少返回的行数， 减少数据传输时间和带宽浪费。

3.优化数据库对象

3.1 优化表的数据类型

使用 procedure analyse()函数对表进行分析， 该函数可以对表中列的数据类型提出优化建议。 能小就用小。 
表数据类型第一个原则是： 使用能正确的表示和存储数据的最短类型。 这样可以减少对磁盘空间、 内存、 cpu 缓存的使用。
使用方法： 'select * from 表名 procedure analyse();'

3.2 对表进行拆分

通过拆分表可以提高表的访问效率。  

1.垂直拆分
把主键和一些列放在一个表中， 然后把主键和另外的列放在另一个表中。 如果一个表中某些列常用， 而另外一些不常用， 则可以采用垂直拆分。
2.水平拆分
根据一列或者多列数据的值把数据行放到二个独立的表中。

3.3 使用中间表来提高查询速度

创建中间表， 表结构和源表结构完全相同， 转移要统计的数据到中间表， 然后在中间表上进行统计， 得出想要的结果。

4.硬件优化

4.1 CPU 的优化

选择多核和主频高的 CPU。  

4.2 内存的优化

使用更大的内存。 将尽量多的内存分配给 MYSQL 做缓存。  

4.3 磁盘 I/O 的优化

4.3.1 使用磁盘阵列

(1)RAID 0 没有数据冗余， 没有数据校验的磁盘陈列。 实现 RAID 0至少需要两块以上的硬盘， 它将两块以上的硬盘合并成一块， 数据连续地
分割在每块盘上。

(2)RAID1 是将一个两块硬盘所构成 RAID 磁盘阵列， 其容量仅等于一块硬盘的容量， 因为另一块只是当作数据“镜像”。 

(3)RAID-0+1 磁盘阵列。 RAID 0+1是 RAID 0 和 RAID 1 的组合形式。 它在提供与 RAID 1 一样的数据安全保障的同时,也提供了与 RAID 0 
近似的存储性能。

4.3.2 调整磁盘调度算法

选择合适的磁盘调度算法， 可以减少磁盘的寻道时间。

5.MYSQL自身的优化

对 MySQL 自身的优化主要是对其配置文件 my.cnf 中的各项参数进行优化调整。 如指定 MySQL 查询缓冲区的大小， 指定 MySQL 允许的最大连接

进程数等。

6.应用优化

6.1 使用数据库连接池

6.2 使用查询缓存

它的作用是存储 select 查询的文本及其相应结果。 如果随后收到一个相同的查询， 服务器会从查询缓存中直接得到查询结果。 查询缓存适用的

对象是更新不频繁的表， 当表中数据更改后， 查询缓存中的相关条目就会被清空

总结：

(1) 选取最适合的字段：在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。还可以将字段设置为NOT NULL,在查询时就不用比较null值了

(2) 使用连接（JOIN）来替代子查询

(3) 使用联合（UNION)来代替手动创建的临时表：它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。

(4) 使用事务：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

(5) 锁定表：事务会将数据库锁定，因此其他用户请求只能暂时等待直到事务结束，会造成响应延迟。我们可以通过锁定表的方法来获得更好的性能

(6) 使用外键。锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

(7) 使用索引。索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。

(8) 优化的查询语句。

(9) 分库分表：可以按照业务分库，分流数据库并发压力，使数据库表更加有条理性

(10) 数据库引擎：一种是innodb、一种是myisam

myisam快，是因为他的数据存储结构、索引存储结构和innodb不一样的，mysiam的索引结构是在内存中存的。 当然，myisam也有弱点，那就是他是表级锁，而innodb是行级锁，所以，mysiam适用于一次插入，多次查询的表，或者是读写分离中的读库中的表，而对于修改插入删除操作比较频繁的表，就很不合适了

(11) 读写分离：数据库并发大的情况下，最好的做法就是进行横向扩展，增加机器，以提升抗并发能力，而且还兼有数据备份功能