MySQL语句的优化_MySQL

bitsCN.com 1.建立基准，建立基准，建立基准！如果需要做决定的话，我们需要数据说话。什么样的查询是最糟的？瓶颈在哪？我什么情况下会写出糟糕的查询？基准测试可以让你模拟高压情况，然后借助性能测评工具，可以让你发现数据库配置中的错误。这样的工具有supersmack, ab, SysBench。这些工具可以直接测试你的数据库(譬如supersmack)，或者模拟网络流量（譬如ab）。 2.性能测试，性能测试，性能测试! 那么，当你能够建立一些高压情况之后，你需要找出配置中的错误。这就是性能测评工具可以帮你做的了。它可以帮你发现配置中的瓶颈，不论是在内存中，CPU中，网络中，硬盘I/O，或者是以上皆有。 你要做的第一件事就是开启慢查询日志(slow query log)，装上mtop。这样你就能获取那些恶意的入侵者的信息了。有需要运行10秒的查询语句正在破坏你的应用程序吗？这些家伙会展示给你看他的查询语句是怎么写的。 在你发现那些很慢的查询语句后，你需要用MySQL自带的工具，如EXPLAIN，SHOW STATUS，SHOW PROCESSLIST。它们会告诉你资源都消耗在哪了，查询语句的缺陷在哪，譬如一个有三次join子查询的查询语句是否在内存中进行排序，还是在硬盘上进行。当然你也应该使用测评工具如top，procinfo，vmstat等等获取更多系统性能信息。 3.减小你的schema 在你开始写查询语句之前，你需要设计schema。记住将一个表装入内存所需要的空间大概是行数*一行的大小。除非你觉得世界上的每个人都会在你的网站注册2兆8000亿次的话，否则你不需要采用BITINT作为你的user_id。同样的，如果一个文本列是固定大小的话（譬如US邮编，通常是”XXXXX-XXXX”的形式），采用VARCHAR的话会给每行增加多余的字节。 有些人对数据库规范化不以为意，他们说这样会形成相当复杂的schema。然而适当的规范化会减少化冗余数据。（适当的规范化）就意味着牺牲少许性能，换取整体上更少的footprint，这种性能换取内存在计算机科学中是很常见的。最好的方法是IMO，就是开始先规范化，之后如果性能需要的话，再反规范化。你的数据库将会更逻辑化，你也不用过早的进行优化。（译者注，这一段我不是很理解，可能翻译错了，欢迎纠正。） 4.拆分你的表 通常有些表只有一些列你是经常需要更新的。例如对于一个博客，你需要在许多不同地方显示标题（如最近的文章列表），只在某个特定页显示概要或者全文。水平垂直拆分是很有帮助的： CREATE TABLE posts ( id int UNSIGNED NOT NULL AUTO_INCREMENT, author_id int UNSIGNED NOT NULL, title varchar(128), created timestamp NOT NULL, PRIMARY KEY(id)); CREATE TABLE posts_data ( post_id int UNSIGNED NOT NULL, teaser text, body text, PRIMARY KEY(post_id));上面的schema是对读数据进行的优化。经常要访问的数据存在一个表中，那些不经常访问的数据放在另一个。被拆分后，不经常访问的数据占据更少的内存。你也可以优化写数据，经常更新的数据放在一个表，不经常更新的放在另一个表。这可以使缓存更高效，因为MySQL不需要让没有更新过的数据移出缓存。 5.不要过度使用artificial primary key artificial primary key非常棒，因为他们使得schema更少的变化。如果我们将地理信息存在以美国邮编为基础的表中，如果邮编系统突然改变了，那我们就会有大麻烦了。另一方面，采用natural key有时候也很棒，譬如我们需要join多对多的关系表时，我们不应该这样： CREATE TABLE posts_tags ( relation_id int UNSIGNED NOT NULL AUTO_INCREMENT, post_id int UNSIGNED NOT NULL, tag_id int UNSIGNED NOT NULL, PRIMARY KEY(relation_id), UNIQUE INDEX(post_id, tag_id)); artificial key完全是多余的，而且post-tag关系的数量将会受到整形数据的系统最大值的限制。 CREATE TABLE posts_tags ( post_id int UNSIGNED NOT NULL, tag_id int UNSIGNED NOT NULL, PRIMARY KEY(post_id, tag_id));6.学习索引 你选择的索引的好坏很重要，不好的话可能破坏数据库。对那些还没有在数据库学习很深入的人来说，索引可以看作是就是hash排序。例如如果我们用查询语句SELECT * FROM users WHERE last_name = ‘Goldstein’，而last_name没有索引的话，那么DBMS将会查询每一行，看看是否等于“Goldstein”。索引通常是B-tree（还有其他的类型），可以加快比较的速度。 你需要给你要select,group,order,join的列加上索引。显然每个索引所需的空间正比于表的行数，所以越多的索引将会占用更多的内存。而且写数据时，索引也会有影响，因为每次写数据时都会更新对应的索引。你需要取一个平衡点，取决每个系统和实施代码的需要。 7.SQL不是C C是经典的过程语言，对于一个程序员来说，C语言也是个陷阱，使你错误的以为SQL也是一种过程语言（当然SQL也不是功能语言也不是面向对象的）。你不要想象对数据进行操作，而是要想象有一组数据，以及它们之间的关系。经常使用子查询时会出现错误的用法。 SELECT a.id, (SELECT MAX(created) FROM posts WHERE author_id = a.id)AS latest_postFROM authors a因为这个子查询是耦合的，子查询要使用外部查询的信息，我们应该使用join来代替。 SELECT a.id, MAX(p.created) AS latest_postFROM authors aINNER JOIN posts p ON (a.id = p.author_id)GROUP BY a.id8.理解你的引擎 MySQL有两种存储引擎：MyISAM和InnoDB。它们分别有自己的性能特点和考虑因素。总体来讲，MyISAM适合读数据很多的情况，InnoDB适合写数据很多的情况，但也有很多情况下正好相反。最大的区别是它们如何处理COUNT函数。 MyISAM缓存有表meta-data，如行数。这就意味着，COUNT(*)对于一个结构很好的查询是不需要消耗多少资源的。然后对于InnoDB来说，就没有这种缓存。举个例子，我们要对一个查询来分页，假设你有这样一个语句SELECT * FROM users LIMIT 5,10，而运行SELECT COUNT(*) FROM users LIMIT 5,10 时，对于MyISAM很快完成，而对InnoDB就需要和第一个语句相同的时间。MySQL有个SQL_CALC_FOUND_ROWS选项，可以告诉InnoDB运行查询语句时就计算行数，之后再从SELECT FOUND_ROWS()来获取。这是MySQL特有的。但使用InnoDB有时候是非常必要的，你可以获得一些功能（如行锁定，stord procedure等）。 9.MySQL特定的快捷键 MySQL提供了许多扩展，方便使用。譬如INSERT … SELECT, INSERT … ON DUPLICATE KEY UPDATE, 以及REPLACE。 我能用到它们时是毫不犹豫的，因为它们很方便，能在许多情况下发挥不错的效果。但是MySQL也有一些危险的关键字，应该少用。例如INSERT DELAYED,它告诉MySQL不需要立即插入数据(例如在写日志的时候)。但问题是如果在很高数据量的情况下，插入可能会被无限期延迟，导致插入队列爆满。你也可以使用MySQL的索引提示来指出哪些索引是需要使用的。MySQL大部分时间运行是不错的，但如果schema设计不好的话或语句写得不好的话，MySQL的表现可能很糟糕。 作者 wuyueyuljhbitsCN.com

小编还为您整理了以下内容，可能对您也有帮助：

mysql数据库优化的几种方法

其次，在建有索引的字段上尽量不要使用函数进行操作。

例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。

第三，在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。

例如下面的查询将会比较表中的每一条记录。

SELECT * FROM books WHERE name like"MySQL%"

但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：

SELECT * FROM books WHERE name＞="MySQL"and name＜"MySQM"

最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

mysql数据库优化的几种方法

标签：rod订单特殊code完成字符型数值子查询应用

mysql数据库的优化方法？

我们都知道，服务器数据库的开发一般都是通过java或者是PHP语言来编程实现的，而为了提高我们数据库的运行速度和效率，数据库优化也成为了我们每日的工作重点，今天，昌平IT培训就一起来了解一下mysql服务器数据库的优化方法。

为什么要了解索引

真实案例

案例一：大学有段时间学习爬虫，爬取了知乎300w用户答题数据，存储到mysql数据中。那时不了解索引，一条简单的“根据用户名搜索全部回答的sql“需要执行半分钟左右，完全满足不了正常的使用。

案例二：近线上应用的数据库频频出现多条慢sql风险提示，而工作以来，对数据库优化方面所知甚少。例如一个用户数据页面需要执行很多次数据库查询，性能很慢，通过增加超时时间勉强可以访问，但是性能上需要优化。

索引的优点

合适的索引，可以大大减小mysql服务器扫描的数据量，避免内存排序和临时表，提高应用程序的查询性能。

索引的类型

mysql数据中有多种索引类型，primarykey，unique，normal，但底层存储的数据结构都是BTREE;有些存储引擎还提供hash索引，全文索引。

BTREE是常见的优化要面对的索引结构，都是基于BTREE的讨论。

B-TREE

查询数据简单暴力的方式是遍历所有记录;如果数据不重复，就可以通过组织成一颗排序二叉树，通过二分查找算法来查询，大大提高查询性能。而BTREE是一种更强大的排序树，支持多个分支，高度更低，数据的插入、删除、更新更快。

现代数据库的索引文件和文件系统的文件块都被组织成BTREE。

btree的每个节点都包含有key，data和只想子节点指针。

btree有度的概念d>=1。假设btree的度为d，则每个内部节点可以有n=[d+1，2d+1)个key，n+1个子节点指针。树的大高度为h=Logb[(N+1)/2]。

索引和文件系统中，B-TREE的节点常设计成接近一个内存页大小(也是磁盘扇区大小)，且树的度非常大。这样磁盘I/O的次数，就等于树的高度h。假设b=100，一百万个节点的树，h将只有3层。即，只有3次磁盘I/O就可以查找完毕，性能非常高。

索引查询

建立索引后，合适的查询语句才能大发挥索引的优势。

另外，由于查询优化器可以解析客户端的sql语句，会调整sql的查询语句的条件顺序去匹配合适的索引。

怎样优化mysql数据库来提高mysql性能(mysql数据库的优化)

优化“mysql数据库”来提高“mysql性能”的方法有：

1、选取最适用的字段属性。

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

2、使用连接（JOIN）来代替子查询(Sub-Queries)。

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。

3、使用联合(UNION)来代替手动创建的临时表。

MySQL从4.0的版本开始支持UNION查询，它可以把需要使用临时表的两条或更多的SELECT查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。

4、事务。

要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。

5、锁定表。

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。

6、使用外键。

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

7、使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

怎样优化mysql数据库来提高mysql性能(mysql数据库的优化)

优化“mysql数据库”来提高“mysql性能”的方法有：

1、选取最适用的字段属性。

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

2、使用连接（JOIN）来代替子查询(Sub-Queries)。

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。

3、使用联合(UNION)来代替手动创建的临时表。

MySQL从4.0的版本开始支持UNION查询，它可以把需要使用临时表的两条或更多的SELECT查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。

4、事务。

要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。

5、锁定表。

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。

6、使用外键。

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

7、使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

如何优化Mysql数据库

1、添加主键ID

2、尽量避免使用select * form table

3、创建索引

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是

一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个

值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

4、复合索引

比如有一条语句是这样的：select * from users where area=‘beijing‘ and age=22;如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已

经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area,

age,

salary)的复合索引，那么其实相当于创建了

(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作条件的列放在最左边，依次递减。

4、索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

5、使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询

速度而且可以节省磁盘空间和I/O操作。

6、排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个

列的排序，如果需要最好给这些列创建复合索引。

7、like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

8、不要在列上进行运算

select * from users where YEAR(adddate)<2007;将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成select * from users where adddate<‘2007-01-01‘;

9、不使用NOT IN和<>操作

NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

10、优化mysql查询缓存

在MySQL服务器上进行查询，可以启用高速查询缓存。让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一。当同一个查询被执行多次时，如果结果是从缓存中提取，那是相当快的。

但主要的问题是，它是那么容易被隐藏起来以至于我们大多数程序员会忽略它。在有些处理任务中，我们实际上是可以阻止查询缓存工作的。

1. // query cache does NOT work

2. $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

3.

4. // query cache works!

5. $today = date("Y-m-d");

6. $r = mysql_query("SELECT username FROM user WHERE signup_date >= ‘$today‘");

7.

8. // query cache does NOT work

9. $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

10.

11. // query cache works!

12. $today = date("Y-m-d");

13. $r = mysql_query("SELECT username FROM user WHERE signup_date >= ‘$today‘");

11. 利用LIMIT 1取得唯一行

有时，当你要查询一张表是，你知道自己只需要看一行。你可能会去的一条十分独特的记录，或者只是刚好检查了任何存在的记录数，他们都满足了你的WHERE子句。

在这种情况下，增加一个LIMIT 1会令你的查询更加有效。这样数据库引擎发现只有1后将停止扫描，而不是去扫描整个表或索引。

1. // do I have any users from Alabama?

2. // what NOT to do:

3. $r = mysql_query("SELECT * FROM user WHERE state = ‘Alabama‘");

4. if (mysql_num_rows($r) > 0) {

5. // ...

6. }

7. // much better:

8. $r = mysql_query("SELECT 1 FROM user WHERE state = ‘Alabama‘ LIMIT 1");

9. if (mysql_num_rows($r) > 0) {

10. // ...

11. }

12. 不要使用BY RAND()命令

这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用BY RAND（）命令时就开始创建了。

如果您真的需要随机显示你的结果，有很多更好的途径去实现。诚然这需要写更多的代码，但是能避免性能瓶颈的出现。问题在于，MySQL可能会为表中每一个的行执行BY RAND()命令（这会消耗处理器的处理能力），然后给你仅仅返回一行。

1. // what NOT to do:

2. $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

3. // much better:

4. $r = mysql_query("SELECT count(*) FROM user");

5. $d = mysql_fetch_row($r);

6. $rand = mt_rand(0,$d[0] - 1);

7.

8. $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

如何优化Mysql数据库

标签：程序长度创建避免磁盘空间高性能form失效查询缓存

如何进行mysql的优化

1，这个比较简单：在phpmyadmin中有提供

2.先设计表的时候考虑选择什么样的存储引擎，myisam不支持事务，但查询速度快，不过现在一般采用的都是InnoDB，能符合95%的项目需求。具体的你可以看看两者的区别。

3.要避免全表查询的操作，这个网上有资料。

4.在where和order by的字段上面建索引，当然索引不是多就好，会使insert和update速度变慢

5，尽量不要采用null值，因为这样搜索引擎会花费大量的时间在这里，而且存储上也有区别，建议采用not null。

6.varchar 和char的选择。可以节省不少空间。

7.字段尽可能短，起的有意义的名字，符合命名规范。

8.避免在where 条件中来使用 <> 或 !=，因为这样会造成全表扫描的。

9，进行分区分表

10.建视图来代替程序中的复杂查询。

11.设置缓存

12.like效率较低，尽量采用不同的sql查询

13. 在where后面的字段中尽量不要采用运算和使用mysql中的函数，例如LOWER()等。

14.between 和in，前者更好

15，写sql语句的时候，思考是否要返回那么多的数据，limit可以进行

16，删除表使用truncate不要用delete，因为delete会一行一行的删除。

17.如果一些sql语句都是相同的只是变量不相同，可以进行sql语句合并，采用预处理，可以节省编译的时间。

18.数据库连接池的使用

19，采用集群

20，不用的资源马上释放

21.拆分大的 DELETE 或INSERT 语句，批量提交SQL语句

22.usleep避免服务器宕机

23，select在从服务器上面进行，修改数据的操作在主服务器上

如果还有其他的，后面待补充。您可能感兴趣的文章:MySQL优化全攻略-相关数据库命令非常不错的MySQL优化的8条经验mysql优化配置参数MySQL优化配置文件my.ini(discuz论坛)有关mysql优化的一些东东整理MySQL优化必须调整的10项配置101个MySQL优化技巧和提示MySQL优化之表结构优化的5大建议（数据类型选择讲的很好）MySQL优化之如何了解SQL的执行频率21条MySQL优化建议（经验总结）

本文如未解决您的问题请添加抖音号：51dongshi（抖音搜索懂视），直接咨询即可。