mysql性能优化培训

一概述

数据库属于IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。

而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读

取一个IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一

步需要优化的就是IO，尽可能将磁盘IO转化为内存IO。

所以我们先从MySQL 数据库IO相关参数（缓存参数）的角度来看看可以通过哪些参数进行IO优化

二表结构优化：

由于MySQL数据库是基于行（Row）存储的数据库，而数据库操作IO 的时候是以page（block）的方式，也就是说，如果我们每条记录所占用的空间量减小，

就会使每个page中可存放的数据行数增大，那么每次IO 可访问的行数也就增多

了。反过来说，处理相同行数的数据，需要访问的page 就会减少，也就是IO 操

作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中

存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数

据命中的几率，也就是缓存命中率。

优化原则：使数据量最小，性能的瓶颈在于磁盘性能，数据量越小，磁盘读取数据的速度就越快，同时，在较小的列上建立索引，索引文件占用的资源也会更少。

1.尽可能地使用最有效(最小)的数据类型。

MySQL有很多节省磁盘空间和内存的专业化类型。尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间

要少25%

2.尽量使用NOT NULL

NULL 类型比较特殊，SQL 难优化。虽然MySQL NULL类型和Oracle 的NULL 有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极

大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外

的存放空间。很多人觉得NULL 会节省一些空间，所以尽量让NULL来达到节省IO

的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带

来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所

以尽量确保DEFAULT 值不是NULL，也是一个很好的表结构设计优化习惯。

3.只创建你确实需要的索引。

索引对检索有好处，但是当你需要快速存储东西时就变得糟糕。如果主要通过搜索列的组合来存取一个表，对它们做一个索引。第一个索引部分应该是最常用的

列。如果从表中选择时总是使用许多列，应该首先以更多的副本使用列以获得更好

的索引压缩。

4.垂直拆分

保证经常查询、显示的主要字段在一张表中，其他的信息放在明细表中。此类优化更利于常用字段频繁查询时，提高系统性能。例如教师信息表，经常用到的字

段非常有限，每次查询浪费大量的内存资源加载不使用的信息，对性能造成一定影

响。

5.水平拆分

当表中数据量非常大时，可以根据具体的业务情况，按照某个字段进行分类，存储在不同的表甚至是数据库中，使得某个表或库的数据量在一个比较小的范围内，一次来提高查询性能。

三索引的使用

1.索引对单个表查询的影响

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越

多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文

件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快

100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免

磁盘寻道。

例如对下面这样的一个student表：

很慢。例如，我们查找出所有english成绩不及格的学生：

对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表，例如一个非常大的学校，我们可能需要存储成千上万的记录，这样一个检索的所花的时间是十分可观的。

如果，我们为english列创建一个索引,

此索引存储在索引文件中，包含表中每行的english列值，但此索引是在english 的基础上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。假如我们要查找分数小于60的所有行，那么可以扫描索引，结果得出5行。然后到达分数为66的行，及Tom的记录，这是一个比我们正在查找的要大的值。索引值是排序的，因此在读到包含Tom的记录时，我们知道不会再有匹配的记录，可以退出了。如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描(如二分查找法)。这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，这些技术是什么并不重要，重要的是它们工作正常，索引技术是个好东西。

因此在执行下述查询

mysql>SELECT name,english FROM user WHERE english<60;

其结果为：

你应该可以发现，这个结果与未索引english列之前的不同，它是排序的。2.索引对多个表的影响

假如有三个无索引的表t1、t2、t3，分别只包含列c1、c2、c3，每个表分别由含有数值1 到1000 的1000 行组成。查找对应值相等的表行组合的查询如下所示：

此查询的结果应该为1000 行，每个组合包含3 个相等的值。如果我们在无索引的情况下处理此查询，则不可能知道哪些行包含那些值。因此，必须寻找出所有组合以便得出与WHERE 子句相配的那些组合。可能的组合数目为1000×1000×1000(十亿)，比匹配数目多一百万倍。很多工作都浪费了，并且这个查询将会非常慢，即使在如像MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有1000 行的情形。如果每个表中有一百万行时，将会怎样?很显然，这样将会产生性能极为低下的结果。如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理如下：

1) 如下从表t1 中选择第一行，查看此行所包含的值。

2) 使用表t2 上的索引，直接跳到t2 中与来自t1 的值匹配的行。类似，利

用表t3 上的索引，直接跳到t3 中与来自t1 的值匹配的行。

3) 进到表t1 的下一行并重复前面的过程直到t1 中所有的行已经查过。

在此情形下，我们仍然对表t1 执行了一个完全扫描，但能够在表t2 和t3 上进行索引查找直接取出这些表中的行。从道理上说，这时的查询比未用索引时要快一百万倍。

MySQL 利用索引加速了WHERE 子句中与条件相配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索。

3.多列索引对查询的影响

假定你发出下列SELECT语句：

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。

你可以这样创建一个多列索引：

mysql>ALTER TABLE tbl_name ADD INDEX(col1,col2);

而你应该这样创建分开的单行列索引：

mysql>ALTER TABLE tble_name ADD INDEX(col1);

mysql>ALTER TABLE tble_name ADD INDEX(col1);

如果表有一个多列索引，任何最左面的索引前缀能被优化器使用以找出行。例如，如果你有一个3行列索引(col1,col2,col3)，你已经索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜索能力。

如果列不构成索引的最左面前缀，MySQL不能使用一个部分的索引。假定你下面显示的SELECT语句：

mysql> SELECT * FROM tbl_name WHERE col1=val1;

mysql> SELECT * FROM tbl_name WHERE col2=val2;

mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果一个索引存在于(col1、col2、col3)上，只有上面显示的第一个查询使用索引。第二个和第三个查询确实包含索引的列，但是(col2)和(col2、col3)不是(col1、col2、col3)的最左面前缀。

如果LIKE参数是一个不以一个通配符字符起始的一个常数字符串，MySQL也为LIKE比较使用索引。例如，下列SELECT语句使用索引：mysql> select * from tbl_name where key_col LIKE "Patrick%";

mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

在第一条语句中，只考虑有"Patrick" <= key_col < "Patricl"的行。在第二条语句中，只考虑有"Pat" <= key_col < "Pau"的行。

下列SELECT语句将不使用索引：mysql> select * from tbl_name where key_col LIKE "%Patrick%";

mysql> select * from tbl_name where key_col LIKE other_col;

在第一条语句中，LIKE值以一个通配符字符开始。在第二条语句中，LIKE值不是一个常数。

如果column_name 是一个索引，使用column_name IS NULL的搜索将使用索引。

MySQL通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列：=、>、>=、<、<=、BETWEEN和一个有一个非通配符前缀象'something%'的LIKE的列。

对于一个多列索引，如果在WHERE子句的所有AND层次使用索引，将不使用来索引优化查询。为了能够使用索引优化查询，必须把一个多列索引的前缀使用在一个AND条件组中。

假定表具有下面的结构：

CREATE TABLE test (

id INT NOT NULL,

last_name CHAR(30) NOT NULL,

first_name CHAR(30) NOT NULL,

PRIMARY KEY (id),

INDEX name (last_name,first_name)

);

name索引是一个对last_name和first_name的索引。索引可以用于为last_name，或者为last_name和first_name在已知范围内指定值的查询。因此，name索引用于下面的查询：

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test WHERE last_name='Widenius'

AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test WHERE last_name='Widenius'

AND first_name >='M' AND first_name < 'N';

然而，name索引不用于下面的查询：

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';

4.选择索引的准则

1)搜索的索引列，不一定是所要选择的列

最适合索引的列是出现在WHERE 子句中的列，或连接子句中指定的列，而不是出现在SELECT 关键字后的选择列表中的列，例如：

SELECT

col_a←不适合作索引列

FROM

Tbl1 LEFT JOIN tbl2

ON tbl1.col_b = tbl2.col_c←适合作索引列

WHERE

col_d = expr ←适合作索引列

当然，所选择的列和用于WHERE 子句的列也可能是相同的。关键是，列出现在选择列表中不是该列应该索引的标志。

2)使用惟一索引

考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“M”和“F”，则对此列进行索引没有多大用处(不管搜索哪个值，都会得出大约一半的行)。

3)使用短索引

如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个CHAR(200) 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘I/O 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。(当然，应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的，因为这个索引中不会有许多不同的值。)

4)利用最左前缀

在创建一个n 列的索引时，实际是创建了MySQL 可利用的n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同，索引一个列的前缀是利用该的前n 个字符作为索引值。)

假如一个表在分别名为state、city 和zip 的三个列上有一个索引。索引中的行是按state/city/zip 的次序存放的，因此，索引中的行也会自动按state/city 的顺序和state 的顺序存放。这表示，即使在查询中只指定state 值或只指定state 和city 的值，MySQL 也可以利用索引。因此，此索引可用来搜索下列的列组合：

MySQL 不能使用不涉及左前缀的搜索。例如，如果按city 或zip 进行搜索，则不能使用该索引。如果要搜索某个州以及某个zip 代码(索引中的列1和列3)，则此索引不能用于相应值的组合。但是，可利用索引来寻找与该州相符的行，以减少搜索范围。

5)不要过度索引

不要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍过。

在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，

所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓

表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也

要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能

会使MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优

化。

如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。

5.比较类型

索引可用于“<”、“<=”、“=”、“>=”、“>”和BETWEEN 运算。在模式具有一个直接量前缀时，索引也用于LIKE 运算。如果只将某个列用于其他类型的运算时(如STRCMP( ))，对其进行索引没有价值。

例如t1表中的c1字段,是varchar(32)类型。

Select * from t1 where c1=1;没有用到索引

Select * from t1 where c1=’1’;用到索引了

四explain执行计划

执行计划explain select……包含如下信息：

1.Id

包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下

如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

2.select_type

表示查询中每个select子句的类型（简单OR复杂）

1)SIMPLE：查询中不包含子查询或者UNION

2)查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY

3)在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

4)在FROM列表中包含的子查询被标记为：DERIVED（衍生）

5)若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在

FROM子句的子查询中，外层SELECT将被标记为：DERIVED

6)从UNION表获取结果的SELECT被标记为：UNION RESULT

第一行：id列为1，表示第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中3代表该查询衍生自第三个select查询，即id为3的select。

第二行：id为3，表示该查询的执行次序为2（4→3），是整个查询中第三个select 的一部分。因查询包含在from中，所以为derived。

第三行：select列表中的子查询，select_type为subquery，为整个查询中的第二个select。

第四行：select_type为union，说明第四个select是union里的第二个select，最先执行。

第五行：代表从union的临时表中读取行的阶段，table列的表示用第一个和第四个select的结果进行union操作。

3.Type

表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下：

由左至右，由最差到最好

1)ALL：Full Table Scan，MySQL将遍历全表以找到匹配的行

2)index：Full Index Scan，index与ALL区别为index类型只遍历索引树

3)range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，

常见于between、<、>、in等的查询

4)ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯

一索引即唯一索引的非唯一前缀进行的查找

5)eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。

常见于主键或唯一索引扫描

6)const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，

使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

system是const类型的特例，当查询的表只有一行的情况下，使用system

7)null:MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

4.possible_keys

指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

5.key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

TIPS：查询中若使用了覆盖索引，则该索引仅出现在key列表中

6.key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

TIPS：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

7.ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

本例中，由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即’ac’

8.rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

9.Extra

该列包含MySQL解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串：

1)Distinct

MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。

2)Not exists

MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。

下面是一个可以这样优化的查询类型的例子：

SELECT * 从t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL；

假定t2.id定义为NOT NULL。在这种情况下，MySQL使用t1.id的值扫描t1并查找t2中的行。如果MySQL在t2中发现一个匹配的行，它知道

t2.id绝不会为NULL，并且不再扫描t2内有相同的id值的行。换句话说，

对于t1的每个行，MySQL只需要在t2中查找一次，无论t2内实际有多少

匹配的行。

3)range checked for each record (index map: #)

MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查

是否可以使用range或index_merge访问方法来索取行。这并不很快，但

比执行没有索引的联接要快得多。

4)Using filesort

MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和

行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

5)Using index

从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策

略。

6)Using temporary

为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

7)Using where

WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类

型为ALL或index，查询可能会有一些错误。

如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。

8)Using sort_union(...), Using union(...), Using intersect(...)

这些函数说明如何为index_merge联接类型合并索引扫描。

9)Using index for group-by

类似于访问表的Using index方式，Using index for group-by表示MySQL 发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而

不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便

对于每个组，只读取少量索引条目。

五Sql优化基本原则

1.优化示例

关联优化left join:

SELECT * FROM t1

LEFT JOIN t2 ON t1.c1=t2.c1;

Mysql会对t1表进行全表扫描,在处理完t1前，是不会考虑t2表的内容

SELECT * FROM t1,t2

WHERE t1.c1=t2.c1;

2.尽量少 join

MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。

对于复杂的多表Join，一方面由于其优化器受限，再者在Join 这方面所下的功夫

还不够，所以性能表现离Oracle 等关系型数据库前辈还是有一定距离。但如果是

简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。

3.尽量少排序

排序操作会消耗较多的CPU 资源，所以减少排序可以在缓存命中率高等IO 能力足够的场景下会较大影响SQL 的响应时间。

对于MySQL来说，减少排序有多种办法，比如：

●上面误区中提到的通过利用索引来排序的方式进行优化

●减少参与排序的记录条数

●非必要不对数据进行排序

4.尽量避免 select *

很多人看到这一点后觉得比较难理解，上面不是在误区中刚刚说select 子句中字段的多少并不会影响到读取的数据吗？是的，大多数时候并不会影响到IO 量，但是当我们还存在order by 操作的时候，select 子句中的字段多少会在很大程度上

影响到排序效率.

5.尽量用 join 代替子查询

虽然Join 性能并不佳，但是和MySQL 的子查询比起来还是有非常大的性能

优势。MySQL 的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。

6.尽量少 or

当where 子句中存在多个条件以“或”并存的时候，MySQL 的优化器并没有很好的解决其执行计划优化问题，再加上MySQL 特有的SQL 与Storage 分层架构方式，造成了其性能比较低下，很多时候使用union all 或者是union（必要的时候）的方式来代替“or”会得到更好的效果。

7.尽量用 union all 代替 union

union 和union all 的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all 而不是union。

8.尽量早过滤

这一优化策略其实最常见于索引的优化设计中（将过滤性更好的字段放得更靠前）。在SQL 编写中同样可以使用这一原则来优化一些Join 的SQL。比如我们在多个表进行分页数据查询的时候，我们最好是能够在一个表上先过滤好数据分好页，然后再用分好页的结果集与另外的表Join，这样可以尽可能多的减少不必要的IO 操作，大大节省IO 操作所消耗的时间。

9.避免类型转换

这里所说的“类型转换”是指where 子句中出现column 字段的类型和传入的参数类型不一致的时候发生的类型转换：

人为在column_name 上通过转换函数进行转换直接导致MySQL（实际上其他数据库也会有同样的问题）无法使用索引，如果非要转换，应该在传入的参数上进行转换,由数据库自己进行转换,如果我们传入的数据类型和字段类型不一致，同时我们又没有做任何类型转换处理，MySQL 可能会自己对我们的数据进行类型转换操作，也可能不进行处理而交由存储引擎去处理，这样一来，就会出现索引无法使用的情况而造成执行计划问题。

10.优先优化高并发的 SQL，而不是执行频率低某些“大”SQL

对于破坏性来说，高并发的SQL 总是会比低频率的来得大，因为高并发的SQL 一旦出现问题，甚至不会给我们任何喘息的机会就会将系统压跨。而对于一些虽然

需要消耗大量IO 而且响应很慢的SQL，由于频率低，即使遇到，最多就是让整个系统响应慢一点，但至少可能撑一会儿，让我们有缓冲的机会。

11.从全局出发优化，而不是片面调整

SQL 优化不能是单独针对某一个进行，而应充分考虑系统中所有的SQL，尤其是在通过调整索引优化SQL 的执行计划的时候，千万不能顾此失彼，因小失大。

12.尽可能对每一条运行在数据库中的SQL进行 explain

优化SQL，需要做到心中有数，知道SQL 的执行计划才能判断是否有优化余地，才能判断是否存在执行计划问题。在对数据库中运行的SQL 进行了一段时间的优化之后，很明显的问题SQL 可能已经很少了，大多都需要去发掘，这时候就需要进行大量的explain 操作收集执行计划，并判断是否需要进行优化。

六常见误区：

1.count(1)和count(primary_key) 优于 count(*)

为了统计记录条数，很多人就使用count(1) 和count(primary_key) 而不是count(*) ，觉得这样性能更好，其实这是一个误区。这样做可能性能会更差，应为数据库对count(*) 计数操作做一些特别的优化。

2.count(column) 和 count(*) 是一样的

count(column) 和count(*) 是一个完全不一样的操作，所代表的意义也完全不一样。count(column) 是表示结果集中有多少个column字段不为空的记录，而count(*) 是表示整个结果集有多少条记录。

3.select a,b from …比 select a,b,c from …可以让数据库访问更少的数据量

大多数关系型数据库都是按照行(row)的方式存储，而数据存取操作都是以一个固定大小的IO单元(被称作block 或者page)为单位，一般为4KB，8KB…大多数时候，每个IO单元中存储了多行，每行都是存储了该行的所有字段(lob等特殊类型字段除外)。所以，我们是取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。

例外情况那就是我们的这个查询在索引中就可以完成，也就是说当只取a,b两个字段的时候，不需要回表，而c这个字段不在使用的索引中，需要回表取得其数据。在这样的情况下，二者的IO量会有较大差异。

4.order by 一定需要排序操作

索引数据实际上是有序的，如果我们的需要的数据和某个索引的顺序一致，而且又通过这个索引来执行，这样数据库就可以省略排序操作，直接将数据返回。实际上，利用索引来优化有排序需求的SQL，是一个非常重要的优化手段。

5.执行计划中有 filesort 就会进行磁盘文件排序

filesort 是我们在使用explain 命令查看一条SQL 的执行计划的时候可能会看到在“Extra”一列显示的信息。实际上，只要一条SQL 语句需要进行排序操作，都会显示“Using filesort”，这并不表示就会有文件排序操作。

MySQL数据库性能(SQL)优化方案-期末论文

高级数据库技术——期末论文 基于SQL查询的MySQL数据库性能优化研究 姓名：XX 学号:2014XXXXX 学院：计算机学院

摘要： 查询是数据库系统中最基本也是最常用的一种操作，是否具有较快的执行速度，已成为数据库用户和设计者极其关心的问题。在研究开源数据库管理系统MySQL 查询优化技术的基础上，主要结合传统SQL操作优化、深度分析 MySQL 源代码、现代数据库发展几方面进行诸如参数调优，MySQL关联查询，重写相关规则等内容展开优化分析研究。 关键词：查询优化，查询重用，查询重写，计划优化

一、传统SQL查询优化操作 1.选取最适用的字段属性 MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。 另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。 2.使用连接（JOIN）来代替子查询(Sub-Queries) MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示： DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成： SELECT * FROM customerinfo

sql语句(mysql优化)绝对经典

sql语句（mysql优化）绝对经典 误区1：count(1)和count(primary_key) 优于count(*) 很多人为了统计记录条数，就使用count(1) 和count(primary_key) 而不是count(*) ，他们认为这样性能更好，其实这是一个误区。对于有些场景，这样做可能性能会更差，应为数据库对count(*) 计数操作做了一些特别的优化。 误区2：count(column) 和count(*) 是一样的 这个误区甚至在很多的资深工程师或者是DBA 中都普遍存在，很多人都会认为这是理所当然的。实际上，count(column) 和count(*) 是一个完全不一样的操作，所代表的意义也完全不一样。count(column) 是表示结果集中有多少个column字段不为空的记录，count(*) 是表示整个结果集有多少条记录 误区3：select a,b from … 比select a,b,c from …可以让数据库访问更少的数据量 这个误区主要存在于大量的开发人员中，主要原因是对数据库的存储原理不是太了解。实际上，大多数关系型数据库都是按照行(row)的方式存储，而数据存取操作都是以一个固定大小的IO单元(被称作block 或者page)为单位，一般为4KB，8KB… 大多数时候，每个IO单元中存储了多行，每行都是存储了该行的所有字段(lob等特殊类型字段除外)。 所以，我们是取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。当然，也有例外情况，那就是我们的这个查询在索引中就可以完成，也就是说当只取a,b两个字段的时候，不需要回表，而c这个字段不在使用的索引中，需要回表取得其数据。在这样的情况下，二者的IO量会有较大差异。(覆盖索引) 误区4：order by 一定需要排序操作 我们知道索引数据实际上是有序的，如果我们的需要的数据和某个索引的顺序一致，而且我们的查询又通过这个索引来执行，那么数据库一般会省略排序操作，而直接将数据返回，因为数据库知道数据已经满足我们的排序需求了。实际上，利用索引来优化有排序需求的SQL，是一个非常重要的优化手段。延伸阅读：MySQL ORDER BY 的实现分析，MySQL 中GROUP BY 基本实现原理以及MySQL DISTINCT 的基本实现原理。（order by null）

mysql服务性能优化my_cnf配置说明详解16G内存

mysql服务性能优化—https://www.sodocs.net/doc/8612667450.html,f配置说明详解 （16G内存） MYSQL服务器https://www.sodocs.net/doc/8612667450.html,f配置文档详解 硬件：内存16G [client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /usr/local/mysql datadir = /data/3306/data open_files_limit = 10240 back_log = 600 #在MYSQL暂时停止响应新请求之前，短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。 max_connections = 3000 #MySQL允许最大的进程连接数，如果经常出现Too Many Connections的错误提示，则需要增大此值。 max_connect_errors = 6000 #设置每个主机的连接请求异常中断的最大次数，当超过该次数，MYSQL服务器将禁止host 的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。 table_cache = 614 #指示表调整缓冲区大小。# table_cache 参数设置表高速缓存的数目。每个连接进来，都会至少打开一个表缓存。#因此， table_cache 的大小应与 max_connections 的设置有关。例如，对于 200 个#并行运行的连接，应该让表的缓存至少有 200 × N ，这里 N 是应用可以执行的查询#的一个联接中表的最大数量。此外，还需要为临时表和文件保留一些额外的文件描述符。 # 当 Mysql 访问一个表时，如果该表在缓存中已经被打开，则可以直接访问缓存；如果#还

优化mysql数据库性能

为了提高性能建议作如下优化修改： 优化mysql数据库性能的参数： (1)、max_connections： 允许的同时客户的数量。增加该值增加mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到too many connections错误。默认数值是16384，请根据实际情况设置此参数。 (2)、key_buffer_size： 索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是10M，请根据实际情况设置此参数。 (3)、sort_buffer： 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。默认数值是256K，请根据实际情况设置此参数。 4)、table_cache： 为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是256，，请根据实际情况设置此参数。 (5)、thread_cache_size： 可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能修改这个变量值。通过比较connections 和threads_created 状态的变量，可以看到这个变量的作用。默认数值是8，请根据实际情况设置此参数。 注：以上参数的调整可以通过修改C:\AppServ\MySQL\my.ini 文件并重启mysql 实现。这是一个比较谨慎的工作，上面的结果只供参考，请根据具体主机的硬件情况（特别是内存大小）进一步修改。 优化配置文件： C:\zxin10\Was\tomcat\conf\ server.xml (6)、在server.xml中修改标红相关参数。 (7)、

MYSQL-innodb性能优化学习总结

MYSQL-innodb性能优化学习总结 BSS测试部：newhackerman

数据库参数 MYSQL数据库的参数配置一般在my.ini配置文件中修改/添加（部分参数也可以用set global 参数名=值做临时调整，重启后失效），配置完后需要重启数据库才生效。 参数1：innodb_buffer_pool_size = Gb/MB 说明：此参数类似于oracle的SGA配置，当主机做为mysql数据库服务器时，一般配置为整机内存的60%~80%。 参数2：innodb_buffer_pool_instances=N 说明：内存缓冲池实例数，将innodb_buffer_pool_size配置的内存分割成N份，此参数当配置内存大小于1G时才生效，当数据库有多个会话进行数据库操作时，用于并行在多个内存块中处理任务，一般配置值《=服务器CPU的个数。 参数3：max_connections = 2000 说明：最大连接数，当数据库面对高并发时，这个值需要调节为一个合理的值，才满足业务的并发要求，避免数据库拒绝连接。 参数4：max_user_connections=1000 说明：设置单个用户的连接数。 参数5：innodb_log_buffer_size =32M 说明：日志缓冲区大小,一般不用设置太大，能存下1秒钟操作的数据日志就行了，mysql 默认1秒写一轮询写一次日志到磁盘。 参数6：innodb_flush_log_at_trx_commit 说明：（这个配置很关键）一般的实时业务交易配置为2，取值0,1,2 0:数据操作时，直接写内存，并不同时写入磁盘； 2:数据操作时，直接写内存，并不同时写入磁盘； 1:就每个事务提交就会要刷新到磁盘后才算提交完成,这种情况是保证了事务的一致性,但性能会有很大的影响。 0与2的区别： 0：当mysql挂了之后，可能会损失前一秒的事务信息 2：当mysql挂了之后，如果系统文件系统没挂，不会有事务丢失。 参数7：innodb_read_io_threads = 16 说明：数据库读操作时的线程数，用于并发。 参数8：innodb_write_io_threads = 16 说明：数据库写操作时的线程数，用于并发。

MySQL5.1性能优化方案

MySQL5.1性能优化方案 1.平台数据库 1.1.操作系统 Red Hat Enterprise Linux Server release 5.4 (Tikanga) ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped 32位Linux服务器，单独作为MySQL服务器使用。 1.2.M ySQL 系统使用的是MySQL5.1，最新的MySQL5.5较之老版本有了大幅改进。主要体现在以下几个方面： 1）默认存储引擎更改为InnoDB InnoDB作为成熟、高效的事务引擎，目前已经广泛使用，但MySQL5.1之前的版本默认引擎均为MyISAM，此次MySQL5.5终于将默认数据库存储引擎改为InnoDB，并且引进了Innodb plugin 1.0.7。此次更新对数据库的好处是显而易见的：InnoDB的数据恢复时间从过去的一个甚至几个小时，缩短到几分钟(InnoDB plugin 1.0.7，InnoDB plugin 1.1，恢复时采用红-黑树)。InnoDB Plugin 支持数据压缩存储，节约存储，提高内存命中率，并且支持adaptive flush checkpoint, 可以在某些场合避免数据库出现突发性能瓶颈。 Multi Rollback Segments：原来InnoDB只有一个Segment，同时只支持1023的并发。现已扩充到128个Segments，从而解决了高并发的限制。 2）多核性能提升

mysql性能优化-慢查询分析、优化索引和配置

mysql性能优化-慢查询分析、优化索引和配置目录 一、优化概述 二、查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profiling分析查询 2索引及查询优化 三、配置优化 1) max_connections 2) back_log 3) interactive_timeout 4) key_buffer_size 5) query_cache_size 6) record_buffer_size 7) read_rnd_buffer_size 8) sort_buffer_size 9) join_buffer_size 10) table_cache 11) max_heap_table_size 12) tmp_table_size

13) thread_cache_size 14) thread_concurrency 15) wait_timeout 一、优化概述 MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。 除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。 二、查询与索引优化分析 在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。 1 性能瓶颈定位 Show命令 我们可以通过show命令查看MySQL状态及变量，找到系统的瓶颈： Mysql> show status ——显示状态信息（扩展show status like ‘XXX’） Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’） Mysql> show innodb status ——显示InnoDB存储引擎的状态 Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

MySQL大数据量的查询提高性能优化

最近一段时间参与的项目要操作百万级数据量的数据，普通SQL查询效率呈直线下降，而且如果where中的查询条件较多时，其查询速度简直无法容忍。之前数据量小的时候，查询语句的好坏不会对执行时间有什么明显的影响，所以忽略了许多细节性的问题。 经测试对一个包含400多万条记录的表执行一条件查询，其查询时间竟然高达40几秒，相信这么高的查询延时，任何用户都会抓狂。因此如何提高sql语句查询效率，显得十分重要。以下是结合网上流传比较广泛的几个查询语句优化方法： 基本原则：数据量大的时候，应尽量避免全表扫描，应考虑在where 及order by 涉及的列上建立索引，建索引可以大大加快数据的检索速度。但是，有些情况索引是不会起效的，因此，需要下面的做法进行优化： 1、应尽量避免在where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 2、应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0 3、尽量避免在where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20 4、下面的查询也将导致全表扫描：

千万级的mysql数据库与优化方法

千万级的mysql数据库与优化方法 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引。 2.应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： Sql代码 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： Sql代码 3.应尽量避免在where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 4.应尽量避免在where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：Sql代码 可以这样查询： Sql代码 5.in 和not in 也要慎用，否则会导致全表扫描，如： 对于连续的数值，能用between 就不要用in 了： 6.下面的查询也将导致全表扫描： Sql代码

若要提高效率，可以考虑全文检索。 7.如果在where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： Sql代码 可以改为强制查询使用索引： 8.应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： 应改为: 9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：Sql代码 应改为: 10.不要在where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。 12.不要写一些没有意义的查询，如需要生成一个空表结构：

Mysql千万级别数据优化方案总结

Mysql千万级别数据优化方案 目录 目录 (1) 一、目的与意义 (2) 1)说明 (2) 二、解决思路与根据（本测试表中数据在千万级别） (2) 1)建立索引 (2) 2)数据体现（主键非索引，实际测试结果其中fid建立索引） (2) 3)MySQL分页原理 (2) 4)经过实际测试当对表所有列查询时 (2) 三、总结 (3) 1)获得分页数据 (3) 2)获得总页数：创建表记录大数据表中总数通过触发器来维护 (3)

一、目的与意义 1)说明 在MySql单表中数据达到千万级别时数据的分页查询结果时间过长，对此进行优达 到最优效果，也就是时间最短；(此统计利用的jdbc连接，其中fid为该表的主键;) 二、解决思路与根据（本测试表中数据在千万级别） 1)建立索引 优点：当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜 索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记 录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是 在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索 引中的ROWID（相当于页码）快速找到表中对应的记录。 缺点：当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降 低了数据的维护速度。 2)数据体现（主键非索引，实际测试结果其中fid建立索引） 未创建索引：SELECT fid from t_history_data LIMIT 8000000,10结果：13.396s 创建索引：SELECT fid from t_history_data LIMIT 8000000,10结果：2.896s select*fromt_history_datawherefidin (任意十条数据的id )结果：0.141s 首先通过分页得到分页的数据的ID,将ID拼接成字符串利用SQL语句 select * from table where ID in （ID字符串）此语句受数据量大小的影响比较小 （如上测试）； 3)MySQL分页原理 MySQL的limit工作原理就是先读取n条记录，然后抛弃前n条，读m条想要 的，所以n越大，性能会越差。 优化前SQL: SELECT * FROM v_history_data LIMIT 5000000, 1010.961s 优化后SQL: SELECT * FROM v_history_data INNER JOIN (SELECT fid FROM t_history_data LIMIT 5000000, 10) a USING (fid)1.943s 分别在于，优化前的SQL需要更多I/O浪费，因为先读索引，再读数据，然后 抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了， 然后通过member_id读取需要的列 4)经过实际测试当对表所有列查询时 select * from table 会比select （所有列名）from table 快些（以查询8000000

Mysql性能优化

MySQL性能优化 性能优化是通过某些有效的方法来提高MySQL的运行速度，减少占用的磁盘空间。性能优化包含很多方面，例如优化查询速度，优化更新速度和优化MySQL服务器等。本文介绍方法的主要有： 优化查询 优化数据库结构 优化MySQL服务器 数据库管理人员可以使用SHOW STATUS语句来查询MySQL数据库的性能。语法：SHOW STATUE LIKE ‘value’；其中value参数是常用的几个统计参数。 Connections：连接MySQL服务器的次数 Uptime：MySQL服务器的上线时间； Slow_queries：慢查询的次数； Com_select：查询操做的次数； Com_insert：插入操作的次数； Com_delete：删除操作的次数； Com_update：更新操作的次数； 1优化查询 查询操作是最频繁的操作，提高了查询速度可以有效提高MySQL 数据库的性能。 首先要对查询语句进行分析，分析查询语句的命令是EXPLAIN语句和DESCRIBE语句。比如 EXPLAIN SELECT * FROM student \G； 索引可以快速定位表中的某条记录。使用索引也可以提高数据库查

询的速度，从而提高数据库的性能。如果不使用索引，查询语句将 表中的所有字段。这样查询的速度会很慢。如果使用了索引，查询语句只会查询索引字段。这样就减少查询的记录数，达到提高查询效率的目的。 现在看一个查询语句中没有索引的使用情况： SELECT * FROM student WHERE name = ‘张三’；这样会对student表中的所有数据都查询一下，对比一下name的字段是否是张三。 然后我们在name字段上建立一个名为index_name的索引：CREATE INDEX index_name ON student（name）； 现在name字段上面已经有索引了，再进行该select语句查询的速度就非常快了，不需要遍历整个表。 但是有些时候即使查询时使用的是索引，但索引并没有起作用。比如使用了LIKE关键字进行查询时，如果匹配字符串的第一个字符 为‘%’，索引不会被使用。如果‘%’不是在第一个位置，索引就会被使用。 另一种情况是在表的多个字段上创建一个索引，比如 CREATE INDEX index ON student（birth，department）；这样只有查询语句条件中使用字段name时，索引才会被用到。因为name字段是多列索引的第一个字段，只有查询条件中使用了name字段才会使索引index起作用。 2优化子查询 很多查询中需要使用子查询。子查询可以使查询语句很灵活，但子查询的执行效率不高。MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句在临时表中查询记录。查询完毕后，MySQL需要插销这些临时表。所以在MySQL中可以使用连接查询来代替子查询。连接查询不需要建立临时表，其速度比子查询要快。

基于MySQL数据库性能优化的实验报告

广州中医药大学医学信息工程学院 实验报告 课程名称：网络数据库编程 专业班级：计算机科学与技术( 2012 )级 学生姓名：张鹏燕76 薛丽梅80 杨晓珠18 翁浩彬07 实验名称：数据库性能优化 实验成绩： 课程类别：□限选 公选□其它□ 数据库系统性能优化

（基于MySQL数据库，采用一定的查询优化方案，用MySQL的内部数据说明优化前与优化后CPU的情况） 一、实验背景 数据库系统是管理信息系统的核心，基于数据库的联机事务处理（OLTP）以及联机分析处理(OLAP)是银行、企业、政府等部门最为重要的计算机应用之一。从大多数系统的应用实例来看，查询操作在各种数据库操作中所占据的比重最大，而查询操作所基于的SELECT语句在SQL语句中又是代价最大的语句。举例来说，如果数据的量积累到一定的程度，比如一个银行的账户数据库表信息积累到上百万甚至上千万条记录，全表扫描一次往往需要数十分钟，甚至数小时。如果采用比全表扫描更好的查询策略，往往可以使查询时间降为几分钟，由此可见查询优化技术的重要性。 小组通过不少的科研文档中发现，许多程序员在利用一些前端数据库开发工具（如PowerBuilder、Delphi等）开发数据库应用程序时，只注重用户界面的华丽，并不重视查询语句的效率问题，导致所开发出来的应用系统效率低下，资源浪费严重。因此，如何设计高效合理的查询语句就显得非常重要。 通过调查得出许多程序员认为查询优化是DBMS（数据库管理系统）的任务，与程序员所编写的SQL语句关系不大，这是错误的。一个好的查询计划往往可以使程序性能提高数十倍。查询计划是用户所提交的SQL语句的集合，查询规划是经过优化处理之后所产生的语句集合。 本实验以应用实例为基础，结合数据库理论，介绍查询优化技术在现实系统中的运用。 二、实验优化方案 DBMS处理查询计划的过程是这样的：在做完查询语句的词法、语法检查之后，将语句提交给DBMS的查询优化器，优化器做完代数优化和存取路径的优化之后，由预编译模块对语句进行处理并生成查询规划，然后在合适的时间提交给系统处理执行，最后将执行结果返回给用户。在实际的数据库产品(如Oracle、Sybase等)的高版本中都是采用基于代价的优化方法，这种优化能根据从系统字典表所得到的信息来估计不同的查询规划的代价，然后选择一个较优的规划。虽

优化MySQL数据库性能的几个好方法

1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。 另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。 2、使用连接（JOIN）来代替子查询(Sub-Queries) MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示： DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

【黑马程序员】MySQL的性能调优一

【黑马程序员】MySQL的性能调优一 什么是MySQL，怎么安装，怎么使用，我这里不做说明了。 一、MySQL 与其他数据库的简单比较 1.1性能比较 性能方面，一直是MySQL 引以为自豪的一个特点。在权威的第三方评测机构多次测试较量各种数据库TPCC 值的过程中，MySQL 一直都有非常优异的表现，而且在其他所有商用的通用数据库管理系统中，仅仅只有Oracle 数据库能够与其一较高下。至于各种数据库详细的性能数据，我这里就不便记录，大家完全可以通过网上第三方评测机构公布的数据了解具体细节信息。 MySQL 一直以来奉行一个原则，那就是在保证足够的稳定性的前提下，尽可能的提高自身的处理能力。也就是说，在性能和功能方面，MySQL 第一考虑的要素主要还是性能，MySQL希望自己是一个在满足客户99%的功能需求的前提下，花掉剩下的大部分精力来性能努力，而不是希望自己是成为一个比其他任何数据库的功能都要强大的数据库产品。 1.2可靠性 关于可靠性的比较，并没有太多详细的评测比较数据，但是从目前业界的交流中可以了解到，几大商业厂商的数据库的可靠性肯定是没有太多值得怀疑的。但是做为开源数据库管理系统的代表，MySQL 也有非常优异的表现，而并不是像有些人心中所怀疑的那样，因为不是商业厂商所提供，就会不够稳定不够健壮。从当前最火的Facebook 这样大型的网站都是使用MySQL 数据库，就可以看出，MySQL 在稳定可靠性方面，并不会

比我们的商业厂商的产品有太多逊色。而且排在全球前10 位的大型网站里面，大部分都有部分业务是运行在MySQL数据库环境上，如Yahoo，Google 等。 总的来说，MySQL 数据库在发展过程中一直有自己的三个原则：简单、高效、可靠。从上面的简单比较中，我们也可以看出，在MySQL 自己的所有三个原则上面，没有哪一项是做得不好的。而且，虽然功能并不是MySQL 自身所追求的三个原则之一，但是考虑到当前用户量的急剧增长，用户需求越来越越多样化，MySQL 也不得不在功能方面做出大量的努力，来不断满足客户的新需求。比如最近版本中出现的Eent Scheduler （类似于Oracle 的Job 功能），Partition 功能，自主研发的Maria 存储引擎在功能方面的扩展，Falcon 存储引擎对事务的支持等等，都证明了MySQL 在功能方面也开始了不懈的努力。 任何一种产品，都不可能是完美的，也不可能适用于所有用户。我们只有衡量了每一种产品的各种特性之后，从中选择出一种最适合于自身的产品。 二、MySQL 的主要适用场景 据说目前MySQL 用户已经达千万级别了，其中不乏企业级用户。可以说是目前最为流行的开源数据库管理系统软件了。任何产品都不可能是万能的，也不可能适用于所有的应用场景。 那么MySQL 到底在什么场景下适用什么场景下不适用呢？ 1、Web 网站系统 Web 站点，是MySQL 最大的客户群，也是MySQL 发展史上最为重要的支撑力量，这一点在最开始的MySQL Server 简介部分就已经说明过。 MySQL 之所以能成为Web 站点开发者们最青睐的数据库管理系统，是因为

mysqlsql百万级数据库优化方案

mysql sql 百万级数据库优化方案 2010-04-25 编辑:kp12345 我要投递文章稿1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引。 2.应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0 3.应尽量避免在where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 4.应尽量避免在where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20 5.in 和not in 也要慎用，否则会导致全表扫描，如： select id from t where num in(1,2,3) 对于连续的数值，能用between 就不要用in 了： select id from t where num between 1 and 3 6.下面的查询也将导致全表扫描： select id from t where name like '%abc%' 若要提高效率，可以考虑全文检索。 7.如果在where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： select id from t where num=@num 可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num 8.应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where num/2=100 应改为: select id from t where num=100*2 9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where substring(name,1,3)='abc'--name以abc开头的id select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id 应改为: select id from t where name like 'abc%' select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

万字总结：学习MySQL优化原理,这一篇就够了!

万字总结：学习MySQL优化原理，这一篇就够了！ Java开源分享 说起MySQL的查询优化，相信大家收藏了一堆奇技淫巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？我想未必。因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在实际业务场景下合理的运用。 MySQL逻辑架构 如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。 MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。 MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。 MySQL查询过程

我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。 当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？ MySQL查询过程 客户端/服务端通信协议 MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。 客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。 与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。 查询缓存

mysql性能优化培训

一概述 数据库属于IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。 而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读 取一个IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一 步需要优化的就是IO，尽可能将磁盘IO转化为内存IO。 所以我们先从MySQL 数据库IO相关参数（缓存参数）的角度来看看可以通过哪些参数进行IO优化 二表结构优化： 由于MySQL数据库是基于行（Row）存储的数据库，而数据库操作IO 的时候是以page（block）的方式，也就是说，如果我们每条记录所占用的空间量减小， 就会使每个page中可存放的数据行数增大，那么每次IO 可访问的行数也就增多 了。反过来说，处理相同行数的数据，需要访问的page 就会减少，也就是IO 操 作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中 存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数 据命中的几率，也就是缓存命中率。 优化原则：使数据量最小，性能的瓶颈在于磁盘性能，数据量越小，磁盘读取数据的速度就越快，同时，在较小的列上建立索引，索引文件占用的资源也会更少。 1.尽可能地使用最有效(最小)的数据类型。 MySQL有很多节省磁盘空间和内存的专业化类型。尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间 要少25% 2.尽量使用NOT NULL NULL 类型比较特殊，SQL 难优化。虽然MySQL NULL类型和Oracle 的NULL 有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极 大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外 的存放空间。很多人觉得NULL 会节省一些空间，所以尽量让NULL来达到节省IO 的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带 来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所 以尽量确保DEFAULT 值不是NULL，也是一个很好的表结构设计优化习惯。 3.只创建你确实需要的索引。 索引对检索有好处，但是当你需要快速存储东西时就变得糟糕。如果主要通过搜索列的组合来存取一个表，对它们做一个索引。第一个索引部分应该是最常用的 列。如果从表中选择时总是使用许多列，应该首先以更多的副本使用列以获得更好