1. 索引按顺序排列存储
2.
选择索引
搜索的索引列,不一定是所要选择的列。出现在ON,WHERE和GROUP
BY后的列适合作索引。
使用惟一索引
对于惟一值的列,索引的效果最好。例如:存放年龄的列具有不同值,所以很容易区分各行;而用来记录性别的列,只有’M'和’F',不论搜索哪个值,都会得出大约一半的行,所以对其进行索引没有多大用途。
使用短索引
如果对字符串类型的列进行索引,应该指定一个前辍长度。比如一个varchar(200)的列,最好指定索引为前10个或20个字符内。(短的索引节省存储空间,并可能使查询更快)
利用最左前辍
在创建一个包含n列的索引时,实际上是创建了MySQL可以使用的n个索引。
多列索引可起几个索引的作用,因为可利用索引中最左边的列来匹配行。这样的列的集称为最左前辍。
比如在一个表中的state,
city和zip三个列上创建索引,那么索引中的行是按state/city/zip的次序存放的。因此,索引中的行也会自动按state/city的顺序和state的顺序来存放。所以,该索引可以用来搜索下列的列的组合:
state/city/zip
state/city
state
不要过度索引
考虑在列上进行的比较类型
索引可用于<, <=,
=, >=, >和BETWEEN运算,也可以用于LIKE运算。对于其他类型的计算(如STRCP()),则索引不起作用。
3.
MySQL查询优化程序
EXPLAIN SELECT * FROM student WHERE 1=0;
3.1
优化程序怎样工作
MySQL查询优化程序有几个目标,但主要目标是尽量利用索引,而且尽量使用最具有限制性的索引以排除尽可能多的行。
比如:WHERE
col1=’aaa’ AND
col2=’bbb’,col1和col2都是索引。
假设整个表内,满足这col1=’aaa’的行有900行,满足col2=’bbb’的行有300行,两个条件都满足的行有30行。
那么,如果首先测试col1,必须检查900行以找到同时与col2值相符的30行,那么在测试col2时,有870行会失败。如果首先测试
col2,要找到同时与col1相符的30行,只需要检测300行,这个过程中会有270行失败,这样所涉及的计算较少,磁盘I/O也较少,所以相对来说更快。
遵循下列准则,有助于优化程序利用索引:
a.
比较具有相同类型的列。
b. 比较中应尽量使用索引列独立。两个例子:
1. 比如WHERE col1 < 4 /
2的效果好于WHERE col1 * 2 < 4,后者不会使用索引,且会扫描表中所有行并进行计算。
2.
某表中的一个索引列date_col,日期类型
对于查询WHERE YEAR(date_col) <
1990,并不会使用索引与1990比较,而是将从列值计算出的值与1990比较,而且必须计算每一行。索引没有得到应用。
改进:WHERE
date_col < ‘1990-01-01’
――――――――――――――――――――――――――――
但如果没有特定的日期值,比如要查询到今天为止100天内的记录,有3条语句可以完成这个任务
WHERE
TO_DAYS(date_col) – TO_DAYS(CURRENT_DATE) < 100
WHERE TO_DAYS(date_col)
< 100 + TO_DAYS(CURRENT_DATE)
WHERE date_col < DATE_ADD(CURRENT_DATE,
INTERVAL 100 DAY)
第一条查询不能利用索引,因为必须检索每一行,以计算TO_DAYS(date_col)。
第二条查询要好一些,100和TO_DAYS(CURRENT_DATE)都是常量,因为表达式右边的值可以在查询处理前由优化程序一次计算出来,而不是每行计算一次,但date_col仍然在函数中,所以仍然没有使用索引。
第三条查询是最好的方法,表达式右边会在查询处理前一次性计算出来,其值是一个日期,可以直接和date_col比较,不需要再转换为天数,索引得到利用。
c. 在LIKE模式的起始处不要使用通配符。
查询WHERE name
LIKE ‘%zhang%’的效率是很差的。
如果要查询以Mac开始的行,可以写成WHERE name LIKE
‘Mac%’,
但是WHERE name >= ‘Mac’ AND name <
‘Mad’的效率好于前者。
4. 列类型的选择与查询效率
a.
使用定长列,不使用可变长列。特别对于经常修改的表,变长列更容易产生碎片。
b.
在较短的列能够满足要求时不要使用较长的列
比如能使用CHAR(40)就不要使用CHAR(60),好处:节省空间、节省I/O操作时间。
c.
将列定义为NOT NULL
这样处理速度更快,所需空间也更少。而且有时还能简化查询,因为不需要检查是否存在行例NULL。
d.
考虑使用ENUM列
如果某列的值的数量有限,应该考虑将其转换为ENUM类型。ENUM在内部使用数值表示,具有更快的处理速度。
e.
使用PROCEDURE ANALYSE()
比如SELECT * FROM commodity PROCEDURE
ANALYSE();
会告诉你该列的最大值、最小值、平均值,以及推荐的列类型等等(主要是ENUM)。
f.
对容易产生碎片的表使用OPTIMIZE TABLE
以常进行修改的表、特别包含了变长列(特别是BLOB类型)的表,容易产生碎片。
g.
除非需要,应避免检索BLOB或TEXT值 ――节省网络传输时间
h.
将BLOB或TEXT列分离到一个独立的表中
在某些情况下,将BLOB或TEXT列从表中移出可能具有一定意义,比如可将剩下的字段设置为定长格式,可以减少碎片,加快处理速度。
5. 有效地装载数据
基本理论:
a.
成批装载比单行装载更快,因为不需要在装载每个记录后就刷新索引。
b.
在表无索引的时候装载比有索引装载快,因为有索引的时候不仅需要写到数据文件,还需要写到索引文件。
c.
较短的语句比较长的语句快,因为服务器分析较少,网络传输量也较少。
实际结论:
a. LOAD DATA比INSERT效率高。
b. LOAD
DATE比LOAD DATA LOCAL效率高。因为使用LOAD DATA,文件必须在服务器上(需要有FILE权限),节省了网络传输时间。
c.
如果必须使用INSERT,应该使用其多行插入形式。比如:
INSERT INTO student VALUES (1, ‘AAA’), (2,
‘BBB’), …
这样会减少索引创建的次数,也可以减少网络传送SQL语句的时间。
如果使用mysqldump来生成SQL备份文件,应该使用—extended-insert选项,使备份文件生成为多行插入形式。或者使用—opt参数。
d.
使用压缩参数。当需要在客户机/服务器间传输数据时,对于大多数客户机,可以使用—compress参数。但一般只用于较慢的网络,因为—compress参数需要占用更多的处理器时间。
e.
让MySQL来插入缺省值 ――减少传输时间和服务器分析语句时间。
f.
在装载大量数据之前不要建立索引,待装载完成后再建立索引。或者在装载前删除索引,完成后再重建。
分享到:
相关推荐
MySQL查询优化技术_索引
因此,了解如何优化MySQL查询性能至关重要。 本文通过详细的示例和说明,深入探讨了MySQL查询优化的重要性及其实现方法。我们介绍了查询优化技术的各个方面,包括索引、查询语句、表结构等基本优化方法以及高级优化...
文章还探讨了优化查询语句的方法,如避免在列上进行函数或计算、使用连接代替子查询等。此外,还强调了优化表结构、服务器配置以及硬件和配置优化的重要性。具体实例和代码片段贯穿全文,为读者提供了具体的指导和...
描述 MySQL 查询优化器的工作原理。 MySQL 查询优化器主要为执行的查询决断最有效的路线
本文档详细介绍了Mysql的查询优化性能的研究,对一些常用方法进行了优化改进~
MySQL查询优化浅析
Mysql查询优化,查询优化器,子查询,分页查询1)在执行计划1中,哪张表是驱动表? 表的连接顺序是怎样的?每一步表的扫描类型是什么? 2)在执行计划2中,表的执行顺序是怎样的?每一步表的扫描类型是什么? 3)在...
第3课 查询优化技术理论与MySQL实践(一)------子查询的优化(一) 第4课 查询优化技术理论与MySQL实践(二)------子查询的优化(二) 从理论看,子查询包括的内容和范围,建立清晰的概念 从实践看,MySQL的子查询...
MySQL查询优化系列讲座之查询优化器.pdf
MySQL查询 优化 技术讲座 让网站速度更快
Mysql查询优化器.rar
1、理解MYSQL的Query Optimizer 2、Query语句优化基本思路和原则 3、充分利用Explain 和 Profiling 4、合理设计并利用索引 5、order by、group by 和 DISTINCT优化 6、小结
MySQL 查询优化 SQL诊断调优原则、原理及思路
MySQL查询优化系列讲座
MySQL数据库技术分享 MySQL查询优化浅析 共32页.pdf
在应用系统中MySQL查询优化大体分为: 1、升级硬件; 2、对进程的设置进行调优; 3、对查询操作进行优化。
MySQL查询优化实践-最终版.pdf
Mysql查询优化器[文].pdf
针对 MySQL 的查询优化,非常给力!针对 MySQL 的查询优化,非常给力!针对 MySQL 的查询优化,非常给力!针对 MySQL 的查询优化,非常给力!针对 MySQL 的查询优化,非常给力!针对 MySQL 的查询优化,非常给力!