SQL优化查询

优化查询的方法

1.使用索引

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

2.优化SQL语句

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

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

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

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

3.优化数据库对象

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

2.对表进行拆分,通过拆分表可以提高表的访问效率。

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

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