MySQL教程之查询优化程序

最新推荐文章于 2024-09-20 21:26:51 发布

gent__chen

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量367

点赞数

分类专栏： MYSQL 文章标签： mysql

本文链接：https://blog.csdn.net/gent__chen/article/details/70569962

版权

MYSQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL查询优化程序

当你调用语句时，MySQL会对它进行分析，判断出可以使用哪些优化操作来加快处理速度。例如

select * from tab where false;

Mysql会检查子句，发现没有符合查询条件的行，就根本不会去搜索表。通过执行explain可以看出

explain select * from tab where false;

指导意见

分析表

生成关于键值分布情况的统计数据，它们可以帮助优化程序更准确的评估索引效率。
2. 使用explain验证优化程序的操作

使用explain语句可以了解到各个索引是否会被用到
3. 在必要时给予优化程序提示或改写它

在连接操作的表列表里，可以在表名的后面加上force index、use index或ignore index，以告知服务器期望使用哪些索引。
4. 比较拥有相同数据类型的列

如果频繁进行比较的列拥有不同的类型，可以使用alter table语句修改其中的一个。注：char(10)和varchar(10)是同类型的。而 char(10)和char(12)是不同类型的。
5. 让索引列在比较表达式中单独出现

如果索引的列是复杂表达式中的一部分，这在检索时是不会使用索引的。
6. 不要在like模式的开始位置使用通配符(%)
7. 利用优化程序的长处

Mysql支持连接和子查询，但子查询的支持是最近才开始有的功能，因此对于连接的优化要比使用子查询的优化效果要更好一些。
8. 测试查询的各种替代形式，并多次运行他们
9. 避免多次使用自动类型转换

选择利于高效查询的数据类型

多用数字运算，少用字符串运算
当较小类型够用时，就不用较大类型
把数据列声明成not null

如果数据列为not null，那么处理速度会更快。这是因为在查询处理期间，不在需要检查该列的值是否为null，不用再把null当做一种特例去检查。
4. 考虑使用ENUM列
如果字符串列的基数低，则可以考虑换成ENUM列。
5. 使用procedure analyse()

select * from tab procedure analyse();

输出结果里会对表里各个列的优化数据类型给出建议。
6. 整理表碎片
对于那些频繁修改的表，尤其是那些包含有可变长度数据列的表，往往会产生大量的碎片，这会造成存储表的磁盘块空间的浪费。随着时间的推移，需要读取更多的磁盘块，才能获取有效的行。
定期使用optimize table，可以消除或减少碎片化的空间。碎片整理方法：先用mysqldump转储表，然后利用这个转储文件重建它。
7. 把数据压缩到blob或text列
使用blob或text列来存储那些可在应用程序里对其进行压缩和解压缩的数据，能够达到使用单个检索操作（而非多个操作）找出所有内容的目的。此办法特别适用于存储那些难以用标准表结构表示的数据，或者那些会随时间变化的数据。
8. 使用合成索引
先根据表里的其他列计算出一个散列值，把它存储到一个单独的列里。然后通过搜索散列值来检索。不过这个技术只适用于精确匹配型查询（运算符=）。
9. 避免使用很大的blob或text
10. 把blob或text列剥离出来形成一个单独的表
11. 存储不大，但在速度上有要求的可以使用char类型
char 长度是固定的，不管你存储的数据是多少他都会都固定的长度。而varchar则处可变长度但他要在总长度上加1字符，这个用来存储位置。由于某种原因char 固定长度，所以在处理速度上要比varchar快速很多，但是对费存储空间，所以对存储不大，但在速度上有要求的可以使用char类型，反之可以用varchar类型来实例。

选择利于高效查询的表存储格式

如果所有列的长度固定，那么MyISAM存储引擎默认会使用固定长度的行，但如果任何一列的长度是可变的，那么它会使用可变长度的行。
- 对于变长行，由于各行的大小不一，因此当执行过多的删除或更新操作时，会产生更多的碎片。
- 当表奔溃时，具有固定长度行的表更容易重建
MEMORY表使用固定长度行，char和varchar列都被隐式地当成char对待。
InnoDB 存储引擎：建议使用VARCHAR 类型。对于InnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR 列不一定比使用可变长度VARCHAR 列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR 平均占用的空间多于VARCHAR，因此使用VARCHAR 来最小化需要处理的数据行的存储总量和磁盘I/O 是比较好的

高效加载数据

数据化加载到数据库还是有许多策略可以提高效率。

把数据从缓存刷新到磁盘的次数越少，那么数据加载的速度就越快。因此，批量加载的效率比单行加载的效率更高，因为插入的行可以先缓存，然后再加载操作结束时一次性刷新。
表的索引越少，加载速度越快。
与长SQL语句相比，短语句的数据加载速度更快。

调度、锁定和并发

MySQL的调度策略总结如下：

写入的优先级比读取高
表的写入操作一次只能进行一个，多个写入请求按其到达的先后顺序依次处理。
可以同时处理多个对同一个表的读取操作。

InnoDB存储引擎利用行级的锁定操作实现这种调度。在许多情况下，如只读的操作完成之时，InnoDB根本不使用锁定操作。

MyISAM、MERGE和MEMORY使用表锁定，只要客户端访问表，就必须要先锁定它，当这个客户端完成对表的操作时，才会解除锁定（锁定并不是让其他人无法操作，多个客户端可以同时读）

在写入操作时，不允许其他客户端对表的任何操作
在读取操作时，不允许其他客户端写入，但允许读取

锁定级别的影响

更精细的锁定会有更好的并发性，因为如果各个客户端使用的是表的不同部分，可以同时使用这个表。
使用表锁定，不会出现死锁问题（是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁）。InnoDB表可能会出现死锁问题，因为事务在开始的时候，该存储引擎还没有获得所有必要的锁定，只有在必要时才需要获得锁定。有可能两个事务同时锁定着对方的数据，只有服务器终止其中一个事务才能解决此问题。