MySQL 优化查询的方法
对于 MySQL 数据库, 优化查询的方法
1. 使用索引
使用索引时, 应尽量避免全表扫描, 首先应考虑在 where 及 order by ,group by 涉及的列上建立索引.
2. 优化 SQL 语句
1) 分析查询语句: 通过对查询语句的分析, 可以了解查询语句执行情况, 找出查询语句执行的瓶颈, 从而优化查询语句.
通过 explain(查询优化神器) 用来查看 SQL 语句的执行结果, 可以帮助选择更好的索引和优化查询语句, 写出更好的优化语句.
例如: explain select * from news;
2) 任何地方都不要使用 select * from t , 用具体的字段列表代替 "*", 不要返回用不到的任何字段.
3) 不在索引列做运算或者使用函数.
4) 查询尽可能使用 limit 减少返回的行数, 减少数据传输时间和带宽浪费.
3. 优化数据库对象
1) 优化表的数据类型
使用 procedure analyse() 函数对表进行分析, 该函数可以对表中列的数据类型提出优化建议. 表数据类型第一个原则是: 使用能正确地表示和存储数据的最短类型. 这样可以减少对磁盘空间, 内存, CPU 缓存的使用.
使用方法: select * from 表名 procedure analyse();
2) 对表进行拆分
通过拆分表可以提高表的访问效率. 有两种拆分方法:
a. 垂直拆分 (按照功能模块)
将表按照功能模块, 关系密切程度划分出来, 部署到不同的库上. 例如: 我们会建立定义数据库 workDB, 商品数据库 payDB, 用户数据库 userDB 等, 分别用于存储项目数据定义表, 商品定义表, 用户数据表等.
把主键和一些列放在一个表中, 然后把主键和另外的列放在零一个表中. 如果一个表中某些列常用, 而另外一个些不常用, 则可以采用垂直拆分.
b. 水平拆分 (按照规则划分存储)
当一个表中的数据量过大时, 我们可以把该表的数据按照某种规则进行划分, 例如 userID 散列, 然后存储到多个结构相同的表和不同的库中.
根据一列或者多列数据的值吧数据行放到两个独立的表中.
3) 使用中间表来提高查询速度
创建中间表, 表结构和源表结构完全相同, 转移要统计的数据到中间表, 然后在中间表上进行统计, 得出想要的结果.
4. 硬件优化
1)CPU 优化
选择多核和主频高的 CPU.
2) 内存的优化
使用更大的内存. 将尽量多的内存分配给 MySQL 做缓存.
3) 磁盘 I/O 的优化
a. 使用磁盘阵列
RAID 0 没有数据冗余, 没有数据校验的磁盘陈列. 实现 RAID 0 至少需要两块以上的硬盘, 它将两块以上的硬盘合并成一块, 数据连续地分割在每块盘上.
RAID 1 是将一个两块硬盘所构成 RAID 硬盘阵列, 其容量仅等于一块硬盘的容量, 因为另一块只是当作数据 "镜像".
使用 RAID-0+1 磁盘阵列. RAID 0+1 是 RAID 0 和 RAID 1 的组合形式. 它在提供与 RAID 1 一样的数据安全保障的同时, 也提供了与 RAID 1 近似的存储性能.
b. 调整磁盘调度算法
选择合适的磁盘调度算法, 可以减少磁盘的寻道时间.
5.MySQL 自身的优化
对 MySQL 自身的优化主要是对其配置文件 my.cnf 中的各项参数进行优化调整. 如指定 MySQL 查询缓冲区的大小, 指定 MySQL 允许的最大连接进程数等.
6. 应用优化
1) 使用数据库连接池
2) 实用查询缓存
它的作用是存储 select 查询的文本及其相应结果. 如果随后收到一个相同的查询, 服务器会从查询缓存中直接得到查询结果. 查询缓存适用的对象是更新不频繁的表, 当表中数据更改后, 查询缓存中的相关条目就会被清空.
来源: http://www.linuxidc.com/Linux/2020-03/162666.htm