20多个MySQL最佳实践与优化

蔚落 2022-06-01 13:29 284阅读 0赞

本文地址：https://code.tutsplus.com/tutorials/top-20-mysql-best-practices--net-7855

数据库操作往往是当今大多数Web应用程序的主要瓶颈。不仅是DBA（数据库管理员）不得不担心这些性能问题。作为程序员，我们需要通过正确构建表格来完成我们的工作，编写优化的查询和更好的代码。在本文中，我将列出一些针对程序员的MySQL优化技术。

在开始之前，请注意，您可以在Envato Market上找到大量有用的[MySQL脚本和实用程序][MySQL]。

[![Envato市场上的MySQL脚本和实用程序][Envato_MySQL]][MySQL] [Envato市场上的MySQL脚本和实用程序][MySQL]

## 1.优化查询对于查询缓存 ##

大多数MySQL服务器都启用了查询缓存。这是提高性能的最有效方法之一，由数据库引擎静静地处理。当多次执行相同的查询时，结果将从缓存中获取，这相当快。

主要的问题是，程序员很容易和隐藏，我们大多数人往往忽视它。我们所做的一些事情实际上可以阻止查询缓存执行任务。

查询缓存在第一行不起作用的原因是CURDATE（）函数的用法。这适用于NOW（）和RAND（）等所有非确定性函数。由于该函数的返回结果可以更改，因此MySQL决定为该查询禁用查询缓存。我们所需要做的就是在查询之前添加一行额外的PHP，以防止发生这种情况。

## 2.解释你的SELECT查询 ##

使用[EXPLAIN][]关键字可以让你了解MySQL正在做什么来执行你的查询。这可以帮助您发现查询或表结构的瓶颈和其他问题。

EXPLAIN查询的结果将显示正在使用哪些索引，如何对表进行扫描和排序等。

采取一个SELECT查询（最好是一个复杂的，与连接），并在其前面添加关键字EXPLAIN。你可以使用这个phpmyadmin。它会显示在一个不错的表格中的结果。例如，假设我忘记将索引添加到我执行连接的列中：

![unoptimized_explain.jpg][]

将索引添加到group\_id字段后：

![optimized_explain.jpg][]

现在不是扫描7883行，而是只扫描2个表中的9行和16行。一个好的经验法则是将“行”列下的所有数字相乘，并且查询性能将与结果数量成比例。

## 3.获取唯一行时限制1 ##

有时当你在查询你的桌子时，你已经知道你正在寻找一行。您可能正在获取唯一记录，或者您可能只是检查是否存在满足WHERE子句的任意数量的记录。

在这种情况下，将LIMIT 1添加到查询中可以提高性能。这样数据库引擎就会在找到1后停止扫描记录，而不是通过整个表或索引。

## 4.索引搜索字段 ##

索引不只是主键或唯一键。如果你的表中有任何你要搜索的列，你应该总是索引它们。

![search_index.jpg][]

正如你所看到的，这个规则也适用于像“last\_name LIKE'a％'”这样的部分字符串搜索。当从字符串的开始搜索时，MySQL能够利用该列上的索引。

您还应该了解哪些类型的搜索不能使用常规索引。例如，当搜索一个单词（例如“WHERE post\_content LIKE'％apple％'”）时，您将看不到正常索引的好处。你会更好的使用[MySQL全文搜索][MySQL 1]或建立自己的索引解决方案。

## 5.索引和使用相同的列类型的联接 ##

如果您的应用程序包含许多JOIN查询，则需要确保您加入的列在两个表上都被索引。这会影响MySQL在内部优化连接操作的方式。

而且，连接的列也必须是相同的类型。例如，如果您将DECIMAL列加入到另一个表的INT列中，则MySQL将无法使用至少一个索引。即使字符编码也需要是字符串类型列的相同类型。

## 6.不要按兰德（） ##

这是一开始听起来很酷的技巧之一，许多新手程序员都陷入了这个陷阱。一旦你开始在查询中使用它，你可能不会意识到你可以创建什么样的可怕的瓶颈。

如果你真的需要结果中的随机行，有更好的方法来做到这一点。当然，这需要额外的代码，但是随着数据的增长，您将会避免一个瓶颈，而这个瓶颈会呈指数级地恶化。问题是，在排序它之前，MySQL将不得不对表中的每一行执行RAND（）操作（这需要处理能力），并给你一行。

因此，您选择一个小于结果数的随机数，并将其用作LIMIT子句中的偏移量。

## 7.避免SELECT \* ##

从表中读取的数据越多，查询将变得越慢。这会增加磁盘操作的时间。另外，当数据库服务器与Web服务器分开时，由于必须在服务器之间传输数据，因此网络延迟也会更长。

总是指定在执行SELECT时需要哪些列是个好习惯。

## 8.几乎总是有一个ID字段 ##

在每个表中都有一个id列，即PRIMARY KEY，AUTO\_INCREMENT和INT的其中一个风格。另外最好是UNSIGNED，因为这个值不能是负数。

即使您的用户表具有唯一的用户名字段，也不要将其作为主键。作为主键的VARCHAR字段较慢。而且，通过在内部引用所有用户的id，你的代码将会有更好的结构。

MySQL引擎本身也有幕后操作，在内部使用主键字段。哪个变得更重要，数据库设置就越复杂。（集群，分区等）。

规则的一个可能的例外是“关联表”，用于2个表之间的多对多关联类型。例如，“posts\_tags”表包含两列：post\_id，tag\_id，用于名为“post”和“tags”的两个表之间的关系。这些表可以有一个包含两个id字段的PRIMARY键。

## 9.在VARCHAR上使用ENUM ##

[ENUM][]型柱非常快速和紧凑。它们在内部存储类似于TINYINT，但它们可以包含并显示字符串值。这使得他们成为某些领域的完美人选。

如果您有一个只包含几种不同类型值的字段，请使用ENUM而不是VARCHAR。例如，它可能是一个名为“status”的列，只包含“active”，“inactive”，“pending”，“expired”等值。

甚至有一种方法可以从MySQL本身获得关于如何重组表的一个“建议”。当你有一个VARCHAR字段，它实际上可以建议你改变列的类型为ENUM。这使用PROCEDURE ANALYZE（）调用完成。这使我们能够：

## 10.使用PROCEDURE ANALYZE（）获取建议 ##

[PROCEDURE ANALYZE（）][PROCEDURE ANALYZE]将让MySQL分析表结构和表中的实际数据，为您提供一些建议。只有在表格中存在实际数据时才有用，因为这在决策中扮演着重要的角色。

例如，如果您为主键创建了一个INT字段，但是没有太多的行，则可能会建议您使用MEDIUMINT。或者，如果您正在使用VARCHAR字段，则可能会收到将其转换为ENUM的建议（如果只有很少的唯一值）。

您也可以通过单击phpmyadmin中的“建议表结构”链接来运行该表。

![suggestions.jpg][]

请记住这些只是建议。如果你的桌子变得更大，他们甚至可能不是正确的建议。这个决定最终是你的。

## 11.如果可以的话，使用NOT NULL ##

除非你有一个非常具体的理由来使用NULL值，你应该总是把你的列设置为NOT NULL。

首先，问问自己空字符串值与空值是否有区别（对于INT字段：0与NULL）。如果没有理由同时拥有，则不需要NULL字段。（你知道Oracle认为NULL和空字符串是相同的吗？）

NULL列需要额外的空间，它们会增加比较语句的复杂性。只要你可以避免他们。但是，我知道有些人可能有非常具体的理由有NULL值，这并不总是一件坏事。

从MySQL文档：

> “NULL列在行中需要额外的空间来记录它们的值是否为NULL。对于MyISAM表，每个NULL列需要多一位，四舍五入到最近的字节。

## 12.准备好的陈述 ##

出于性能和安全原因，使用预准备语句有多个好处。

Prepared Statements将默认过滤与其绑定的变量，这对保护您的应用程序免受SQL注入攻击非常有用。你当然也可以手动过滤你的变量，但是这些方法更容易被程序员误解和遗忘。这在使用某种框架或ORM时不是什么问题。

由于我们关注的是绩效，我还应该提到这方面的好处。当您的应用程序中多次使用相同的查询时，这些好处更加显着。您可以为同一个准备好的语句分配不同的值，但是MySQL只需解析一次。

最新版本的MySQL以本地二进制形式传输准备好的语句，这些语句更高效，还可以帮助减少网络延迟。

有一段时间，许多程序员习惯于避免准备好陈述，这只是一个重要原因。他们没有被MySQL查询缓存缓存。但是从5.1版本开始，也支持查询缓存。

要在PHP中使用准备好的语句，请检查[mysqli扩展名][mysqli]或使用[PDO][]等数据库抽象层。

<table style="border-collapse:collapse; width:850px; margin:0px!important; background:none!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; outline:0px!important; overflow:visible!important; padding:5px 0px!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; font-size:14px!important; min-height:auto!important"> 
 <tbody style="background:none!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:5px 0px!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important"> 
  <tr style="background:none!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:5px 0px!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important"> 
   <td style="border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; font-size:14px!important; min-height:auto!important; color:rgb(175,175,175)!important"> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">01</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">02</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">03</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">04</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">05</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">06</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">07</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">08</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">09</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">10</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">11</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">12</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">13</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">14</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">15</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">16</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">17</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">18</span> 
    </div> 
    <div style="border-width:0px 1px 0px 0px!important; border-top-style:initial!important; border-right-style:solid!important; border-bottom-style:initial!important; border-left-style:initial!important; border-top-color:initial!important; border-right-color:rgb(228,228,228)!important; border-bottom-color:initial!important; border-left-color:initial!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 0.5em 0px 1em!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
     <span style="vertical-align:inherit">19</span> 
    </div> </td> 
   <td style="width:814px; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; font-size:14px!important; min-height:auto!important"> 
    <div style="background:none!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:5px 0px!important; position:relative!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important"> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(0,130,0)!important">// create a prepared statement</code> 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-weight:bold!important; min-height:auto!important; color:rgb(0,102,153)!important">if</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important"> (</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(170,119,0)!important">$stmt</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important"> = </code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(170,119,0)!important">$mysqli</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">-&gt;prepare(</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:blue!important">"SELECT username FROM user WHERE state=?"</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">)) { </code> 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important">
       &nbsp; 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(0,130,0)!important">// bind parameters</code> 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(170,119,0)!important">$stmt</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">-&gt;bind_param(</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:blue!important">"s"</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">, </code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(170,119,0)!important">$state</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">);</code> 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important">
       &nbsp; 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(0,130,0)!important">// execute</code> 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(170,119,0)!important">$stmt</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">-&gt;execute();</code> 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important">
       &nbsp; 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(0,130,0)!important">// bind result variables</code> 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(170,119,0)!important">$stmt</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">-&gt;bind_result(</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(170,119,0)!important">$username</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">);</code> 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important">
       &nbsp; 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(0,130,0)!important">// fetch value</code> 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(170,119,0)!important">$stmt</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">-&gt;fetch();</code> 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important">
       &nbsp; 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">printf(</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:blue!important">"%s is from %s\n"</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">, </code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(170,119,0)!important">$username</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">, </code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(170,119,0)!important">$state</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">);</code> 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important">
       &nbsp; 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; color:rgb(170,119,0)!important">$stmt</code> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">-&gt;close();</code> 
     </div> 
     <div style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px 1em!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="font-family:&quot;Source Code Pro&quot;,Monaco,monospace!important; border:0px!important; padding:5px 0px!important; background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:normal!important; margin:0px!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important">}</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

## 13.无缓冲的查询 ##

通常，当您从脚本执行查询时，它将等待该查询的执行完成，然后才能继续。您可以通过使用无缓冲的查询来改变它。

在PHP文档中有一个很好的解释：[mysql\_unbuffered\_query（）][mysql_unbuffered_query]函数：

> “mysql\_unbuffered\_query（）将SQL查询查询发送到MySQL，而不会像mysql\_query（）那样自动获取和缓冲结果行。这样可以节省大量的内存，使用SQL查询生成大的结果集，您可以开始处理结果集在第一行被取回之后立即执行，因为您不必等待完整的SQL查询执行完毕。

但是，它具有一定的局限性。您必须读取所有行或调用[mysql\_free\_result（）][mysql_free_result]才能执行另一个查询。此外，您不允许在结果集上使用[mysql\_num\_rows（）][mysql_num_rows]或[mysql\_data\_seek（）][mysql_data_seek]。

## 14.将 IP地址存储为UNSIGNED INT ##

许多程序员将创建一个VARCHAR（15）字段，而不会意识到它们实际上可以将IP地址存储为整数值。有了INT，你只需要下降到4个字节的空间，而且有一个固定大小的字段。

您必须确保您的列是UNSIGNED INT，因为IP地址使用整个范围的32位无符号整数。

在你的查询中，你可以使用[INET\_ATON（）][INET_ATON]将IP转换为一个整数，而[INET\_NTOA（）则][INET_NTOA]可以使用[INET\_NTOA（）][INET_ATON]。PHP中也有类似的函数，称为[ip2long（）][ip2long]和[long2ip（）][long2ip]。

## 15.固定长度（静态）表更快 ##

当表中的每一列都是“固定长度”时，表格也被认为是[“静态”或“固定长度”][Link 1]。不是固定长度的列类型的例子是：VARCHAR，TEXT，BLOB。如果只包含这些类型的列中的一个，则表格不再是固定长度的，而是必须由MySQL引擎以不同的方式处理。

固定长度的表可以提高性能，因为MySQL引擎查找记录的速度更快。当它想要读取表格中的特定行时，可以快速计算出它的位置。如果行大小不固定，则每次需要查找时，都需要查询主键索引。

它们也更容易缓存，并且在崩溃后更容易重建。但他们也可以占用更多的空间。例如，如果将VARCHAR（20）字段转换为CHAR（20）字段，则无论它处于什么位置，它总是需要20个字节的空间。

通过使用“垂直分区”技术，您可以将可变长度列分隔到单独的表中。这使我们能够：

## 16.垂直分区 ##

垂直分区是出于优化原因垂直分解表结构的行为。

示例1：您可能有一个包含家庭地址的用户表，这些表不经常读取。您可以选择拆分表格并将地址信息存储在单独的表格中。这样你的主要用户表就会缩小。如你所知，较小的表格执行速度更快。

示例2：表中有一个“last\_login”字段。每次用户登录到网站时都会更新。但是表上的每个更新都会导致该表的查询缓存被刷新。您可以将该字段放入另一个表中，以将对用户表的更新保持在最低限度。

但是你也需要确保在分区之后你不需要经常加入这两个表，否则你的性能可能会下降。

## 17.拆分Big DELETE或INSERT查询 ##

如果您需要在实时网站上执行大的DELETE或INSERT查询，则需要注意不要打扰网络流量。当执行这样一个大的查询时，它可以锁定你的表，并停止你的web应用程序。

Apache运行许多并行进程/线程。因此，当脚本尽快完成执行时，它的工作效率最高，因此服务器不会经历太多开放的连接和进程，而且会消耗资源，特别是内存。

如果您最终锁定表的时间过长（如30秒或更长时间），则在高流量的网站上，您将导致进程和查询堆积，这可能需要很长时间才能清除，甚至导致网页崩溃服务器。

如果你有某种需要删除大量行的维护脚本，只需使用LIMIT子句以较小的批量执行，以避免这种拥塞。

## 18.小列更快 ##

使用数据库引擎，磁盘可能是最重要的瓶颈。保持更小更紧凑的东西通常对性能有帮助，以减少磁盘传输量。

MySQL文档具有所有数据类型的[存储要求][Link 2]列表。

如果一个表预计只有很少的行，那么没有理由将主键设置为INT，而不是MEDIUMINT，SMALLINT，甚至在某些情况下是TINYINT。如果您不需要时间组件，请使用DATE而不是DATETIME。

只要确保你留下合理的空间来增长，或者你最终可能会像[Slashdot][]一样。

## 19.选择正确的存储引擎 ##

MySQL中两个主要的存储引擎是MyISAM和InnoDB。每个人都有自己的优点和缺点。

MyISAM适用于读取繁重的应用程序，但在写入很多时不能很好地扩展。即使您更新一行的一个字段，整个表也会被锁定，并且在查询完成之前，其他任何进程都不能读取。MyISAM在计算SELECT COUNT（\*）类型的查询时速度非常快。

InnoDB往往是一个更复杂的存储引擎，对于大多数小型应用程序，它可能比MyISAM慢。但它支持基于行的锁定，这种锁定更好。它还支持一些更高级的功能，如交易。

*  [MyISAM存储引擎][MyISAM]
 *  [InnoDB存储引擎][InnoDB]

## 20.使用对象关系映射器 ##

通过使用ORM（对象关系映射器），您可以获得一定的性能优势。ORM所能做的一切，都可以手动编码。但这可能意味着太多的额外工作，需要高水平的专业知识。

ORM对于“懒加载”非常有用。这意味着它们只能在需要的时候获取值。但是，您需要小心，否则最终可能会创建许多可以降低性能的迷你查询。

ORM还可以将查询分批处理到事务中，这比将单个查询发送到数据库要快得多。

目前我最喜欢的PHP的ORM是[教义][Link 3]。我写了一篇关于如何[使用CodeIgniter安装Doctrine][CodeIgniter_Doctrine]的文章。

## 21.小心持久连接 ##

持久连接意味着减少重新连接到MySQL的开销。当创建一个持久连接时，即使脚本运行完成，它仍然保持打开状态。由于Apache重用了它的子进程，所以下次进程运行一个新脚本时，它将重用相同的MySQL连接。

*  [PHP中的mysql\_pconnect（）][PHP_mysql_pconnect]

理论上听起来不错。但是从我个人的经验（以及其他许多方面）来看，这个功能是不值得的。存在连接限制，内存问题等严重问题。

Apache运行非常平行，并创建了许多子进程。这是持续性连接在这种环境下不能很好地工作的主要原因。在考虑使用mysql\_pconnect（）函数之前，请咨询您的系统管理员。

[MySQL]: http://codecanyon.net/search?utf8=%E2%9C%93&term=mysql&referrer=search&view=grid&sort=&date=&price_min=&price_max=&sales=&rating_min=
[Envato_MySQL]: https://cms-assets.tutsplus.com/uploads/users/48/posts/14447/image/MySQL.png
[EXPLAIN]: http://dev.mysql.com/doc/refman/5.0/en/explain.html
[unoptimized_explain.jpg]: https://cdn.tutsplus.com/net/uploads/legacy/500_mysql/unoptimized_explain.jpg
[optimized_explain.jpg]: https://cdn.tutsplus.com/net/uploads/legacy/500_mysql/optimized_explain.jpg
[search_index.jpg]: https://cdn.tutsplus.com/net/uploads/legacy/500_mysql/search_index.jpg
[MySQL 1]: http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html
[ENUM]: http://dev.mysql.com/doc/refman/5.0/en/enum.html
[PROCEDURE ANALYZE]: http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html
[suggestions.jpg]: https://cdn.tutsplus.com/net/uploads/legacy/500_mysql/suggestions.jpg
[mysqli]: http://php.net/manual/en/book.mysqli.php
[PDO]: http://us.php.net/manual/en/book.pdo.php
[mysql_unbuffered_query]: http://php.net/manual/en/function.mysql-unbuffered-query.php
[mysql_free_result]: http://us2.php.net/manual/en/function.mysql-free-result.php
[mysql_num_rows]: http://us2.php.net/manual/en/function.mysql-num-rows.php
[mysql_data_seek]: http://us2.php.net/manual/en/function.mysql-data-seek.php
[INET_ATON]: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton
[INET_NTOA]: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-ntoa
[ip2long]: http://php.net/manual/en/function.ip2long.php
[long2ip]: http://us.php.net/manual/en/function.long2ip.php
[Link 1]: http://dev.mysql.com/doc/refman/5.1/en/static-format.html
[Link 2]: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
[Slashdot]: http://news.slashdot.org/article.pl?sid=06/11/09/1534204
[MyISAM]: http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html
[InnoDB]: http://dev.mysql.com/doc/refman/5.1/en/innodb.html
[Link 3]: http://www.doctrine-project.org/
[CodeIgniter_Doctrine]: http://www.phpandstuff.com/articles/codeigniter-doctrine-from-scratch-day-1-install-and-setup
[PHP_mysql_pconnect]: http://php.net/manual/en/function.mysql-pconnect.php