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

WHERE  customerid  NOT IN (SELECT customerid   FROM   salesinfo)

如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

SELECT  *  FROM  customerinfo

LEFT  JOIN  salesinfo  ON   customerinfo.customerid =salesinfo.customerid

WHERE  salesinfo.customerid   IS NULL

连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

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

MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

SELECT   name,phone  FROM  client UNION

SELECT  name,birthdate  FROM  author  UNION

SELECT  name,supplier FROM product

4、事务

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

BEGIN;
  INSERT   INTO   salesinfo   SET   customerid=14;
  UPDATE   inventory   SET   quantity =11   WHERE   item='book';COMMIT;

事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5、锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCK TABLE inventory WRITE SELECT quantity  FROM   inventory   WHERE Item='book';

...

UPDATE   inventory   SET   Quantity=11   WHERE  Item='book';UNLOCKTABLES

这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。

6、使用外键

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

例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的customerid映射到salesinfo表中customerid，任何一条没有合法customerid的记录都不会被更新或插入到salesinfo中。

CREATE  TABLE   customerinfo( customerid   int primary key) engine = innodb;CREATE  TABLE   salesinfo( salesid int not null,customerid  int not null, primary key(customerid,salesid),foreign key(customerid)  references  customerinfo(customerid) on delete cascade)engine = innodb;

注意例子中的参数“on delete cascade”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATE TABLE语句中加上engine=INNODB。如例中所示。

7、使用索引

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

那该对哪些字段建立索引呢？

一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况

例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

8、优化的查询语句

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

下面是应该注意的几个方面。

a、 首先，最好是在相同类型的字段间进行比较的操作

在MySQL3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。

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

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

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

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

SELECT  *  FROM  books  WHERE  name  like   "MySQL%"

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

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

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

1. 
   

2. 先分析了MYSQL数据库的优化方法，然后结合实际问题nat123上SQL分机端口连接不上的问题提供了解决办法。

   MySQL模式优化方法:

   保持你的数据库整理性。

   旧数据归档 – 删除多余的行返回或搜索查询。

    将您的数据加上索引。

   不要过度使用索引，比较与查询。

   

3. 压缩文字和BLOB数据类型 – 以节省空间和减少磁盘读取次数.

   UTF 8和UTF16都低于latin1执行效率.

   有节制地使用触发器.

   冗余数据保持到最低限度 – 不重复不必要的数据.

   使用链接表，而不是扩展行.

   

4. 注意数据类型，在您的真实数据中，尽可能使用最小的一个.

   如果其他数据经常被用于查询时，而BLOB / TEXT数据不是，就把BLOB / T数据从其他数据分离出来.

   检查和经常优化表.

   经常重写InnoDB表优化.

   有时，当添加列时删除索引，然后在添加回来索引，这样就会更快.

   

5. 下面以实际案例对SQL问题进行解决方法分析：

   管家婆网络版多地点登录的版本是固定端口、多端口应用，修改默认访问连接端口都可能会导致外网连接不可用。

   如果外网访问连接内网管家婆，一般的NAT映射是会改变访问端口的，导致应用不可连接，使用nat123全端口映射好可简单解决这问题。

   使用nat123全端口映射，实现外网访问内网多端口、固定端口应用方法：

   在内网安装nat123软件。

   启用nat123，添加映射，选择全端口映射模式。

   

6. 在外网访问时，安装并启用nat123访问者，将需要访问的端口添加到访问列表，即可正常访问。

   

、选取最适用的字段属性

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

WHERE  customerid  NOT IN (SELECT customerid   FROM   salesinfo)

如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

SELECT  *  FROM  customerinfo

LEFT  JOIN  salesinfo  ON   customerinfo.customerid =salesinfo.customerid

WHERE  salesinfo.customerid   IS NULL

连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

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

MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

SELECT   name,phone  FROM  client UNION

SELECT  name,birthdate  FROM  author  UNION

SELECT  name,supplier FROM product

4、事务

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

BEGIN;
  INSERT   INTO   salesinfo   SET   customerid=14;
  UPDATE   inventory   SET   quantity =11   WHERE   item='book';COMMIT;

事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5、锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCK TABLE inventory WRITE SELECT quantity  FROM   inventory   WHERE Item='book';

...

UPDATE   inventory   SET   Quantity=11   WHERE  Item='book';UNLOCKTABLES

这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。

6、使用外键

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

例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的customerid映射到salesinfo表中customerid，任何一条没有合法customerid的记录都不会被更新或插入到salesinfo中。

CREATE  TABLE   customerinfo( customerid   int primary key) engine = innodb;CREATE  TABLE   salesinfo( salesid int not null,customerid  int not null, primary key(customerid,salesid),foreign key(customerid)  references  customerinfo(customerid) on delete cascade)engine = innodb;

注意例子中的参数“on delete cascade”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATE TABLE语句中加上engine=INNODB。如例中所示。

7、使用索引

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

那该对哪些字段建立索引呢？

一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况

例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

8、优化的查询语句

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

下面是应该注意的几个方面。

a、 首先，最好是在相同类型的字段间进行比较的操作

在MySQL3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。

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

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

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

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

SELECT  *  FROM  books  WHERE  name  like   "MySQL%"

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

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

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

WEB开发者不光要解决程序的效率问题，对数据库的快速访问和相应也是一个大问题。希望本文能对大家掌握MySQL优化技巧有所帮助。 1. 优化你的MySQL查询缓存 在MySQL服务器上进行查询，可以启用高速查询缓存。让数据库引擎在后台悄悄的处理是提高性能的最有效方

　　WEB开发者不光要解决程序的效率问题，对数据库的快速访问和相应也是一个大问题。希望本文能对大家掌握MySQL优化技巧有所帮助。

　　1. 优化你的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'");

　　2. 用EXPLAIN使你的SELECT查询更加清晰

　　使用EXPLAIN关键字是另一个MySQL优化技巧，可以让你了解MySQL正在进行什么样的查询操作，，这可以帮助你发现瓶颈的所在，并显示出查询或表结构在哪里出了问题。

　　EXPLAIN查询的结果，可以告诉你那些索引正在被引用，表是如何被扫描和排序的等等。

　　实现一个SELECT查询(最好是比较复杂的一个，带joins方式的)，在里面添加上你的关键词解释，在这里我们可以使用phpMyAdmin，他会告诉你表中的结果。举例来说，假如当我在执行joins时，正忘记往一个索引中添加列，EXPLAIN能帮助我找到问题的所在。



　　添加索引到group_id field后



　　3. 利用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. }

　　4. 索引中的检索字段

　　索引不仅是主键或唯一键。如果你想搜索表中的任何列，你应该一直指向索引。



　　5. 保证连接的索引是相同的类型

　　如果应用程序中包含多个连接查询，你需要确保你链接的列在两边的表上都被索引。这会影响MySQL如何优化内部联接操作。

　　此外，加入的列，必须是同一类型。例如，你加入一个DECIMAL列，而同时加入另一个表中的int列，MySQL将无法使用其中至少一个指标。即使字符编码必须同为字符串类型。

　　1. // looking for companies in my state

　　2. $r = mysql_query("SELECT company_name FROM users

　　3. LEFT JOIN companies ON (users.state = companies.state)

　　4. WHERE users.id = $user_id");

　　5.

　　6. // both state columns should be indexed

　　7. // and they both should be the same type and character encoding

　　8. // or MySQL might do full table scans

　　6. 不要使用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");

　　7. 尽量避免SELECT *命令

　　从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。

　　始终指定你需要的列，这是一个非常良好的习惯。

　　1. // not preferred

　　2. $r = mysql_query("SELECT * FROM user WHERE user_id = 1");

　　3. $d = mysql_fetch_assoc($r);

　　4. echo "Welcome {$d['username']}";

　　5. // better:

　　6. $r = mysql_query("SELECT username FROM user WHERE user_id = 1");

　　7. $d = mysql_fetch_assoc($r);

　　8. echo "Welcome {$d['username']}";

　　9. // the differences are more significant with bigger result sets

　　8. 从PROCEDURE ANALYSE()中获得建议

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

MYSQL优化主要分为以下四大方面：

设计：存储引擎，字段类型，范式与逆范式

功能：索引，缓存，分区分表。

架构：主从复制，读写分离，负载均衡。

合理SQL：测试，经验。

MYSQL优化主要分为以下四大方面：

设计：存储引擎，字段类型，范式与逆范式

功能：索引，缓存，分区分表。

架构：主从复制，读写分离，负载均衡。

合理SQL：测试，经验。

一、存储引擎

在创建表的时候我们使用sql语句，Create table tableName () engine=myisam|innodb;

这里就指明了存储引擎是myisam还是innodb。存储引擎是一种用来存储MySQL中对象（记录和索引）的一种特定的结构（文件结构），处于MySQL服务器的最底层，直接存储数据。导致上层的操作，依赖于存储引擎的选择。地位如下图：

网络接口层：与客户端通信，比如传输数据等等。存储引擎层：存储数据的规则，方式。

本质：存储引擎就是特定的数据存储格式（方案）。

可以使用show engines命令来查看当前MySQL支持的存储引擎列表。

1、InnoDB存储引擎介绍

Mysql版本>=5.5 默认的存储引擎，MySQL推荐使用的存储引擎。支持事务，行级锁定，外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。

（1）存储格式

数据，索引集中存储，存储于同一个表空间文件中。

数据：记录行。索引：一种检索机制，也需要一定的空间，就相当于一本字典的目录。

示例： 创建一个test数据库，新建一张student表，选择存储引擎为innodb, 然后打开mysql的data下的test目录，发现有以下3个文件。

其中db.opt存放了数据库的配置信息，比如数据库的字符集还有编码格式。student.frm是表结构文件，仅存储了表的结构、元数据(meta)，包括表结构定义信息等。不论是哪个表引擎都会有一个frm文件。student.ibd是表索引文件，包括了单独一个表的数据及索引内容。

如果往表里插入了新的数据，则在mysql的data目录下会生成ibdata1文件，这个文件是存储了所有innodb表的数据。

关于innodb引擎的详细介绍：

使用innodb引擎时，需要理解独立表空间、共享表空间。

独立表空间：每个表都会生成以独立的文件方式来存储，每个表都一个.frm的描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据及索引内容，默认情况下它的存储在mysql指定的目录下。

独立表空间优缺点

优点：

每个表都有自己独立的表空间；每个表的数据和索引都会存储在各个独立的表空间中；可以实现单表在不同的数据进行迁移；表空间可以回收（除了drop table操作，表空不能自己回收）；drop table 操作自动回收表空间，如果对统计分析或是日值表，删除大量数据后可以通过 ：alter table tablename engin=innodb进行回缩不用的空间；对于使用inodb-plugin的innodb使用truncate table会使用空间收缩。；对于使用独立表空间，不管怎么删除，表空间的碎片都不会太严重。

缺点：

单表增加过大，如超过100G。对于单表增长过大的问题，如果使用共享表空间可以把文件分开，但有同样有一个问题，如果访问的范围过大同样会访问多个文件，一样会比较慢。对于独立表空间也有一个解决办法是：使用分区表，也可以把那个大的表空间移动到别的空间上然后做一个连接。其实从性能上出发，当一个表超过100个G有可能响应也是较慢了，对于独立表空间还容易发现问题早做处理。

共享表空间：某一个数据库所有的表数据，索引文件全部都放在一个文件中，默认这个共享表空间的文件路径在data目录下，默认的文件名为 ibdata1,初始化为10M。

共享表空间优缺点

优点：可以将表空间分成多个文件存放在各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上），数据和文件放在一起方便管理。

缺点：所有的数据和索引存放到一个文件中，将来会是一个很大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对一个表做了大量删除操作后表空间将有大量的空隙，特别是对统计分析、日值系统这类应用最不适合用共享表空间。

如何开启独立表空间？

查看是否开启独产表空间：

mysql> show variables like '%per_table';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF |

+-----------------------+-------+

设置开启：

在my.cnf文件中[mysqld] 节点下添加innodb_file_per_table=1

或者通过命令：set global innodb_file_per_table=1;

注：

innodb_file_per_table值来进行修改即可，但是对于之前使用过的共享表空间则不会影响，除非手动的去进行修改或者是

innodb_file_per_table=1 为使用独占表空间

innodb_file_per_table=0 为使用共享表空间

修改独占空表空间的数据存储位置

innodb_data_home_dir = "C:\mysql\data\"

innodb_log_group_home_dir = "C:\mysql\data\"

innodb_data_file_path=ibdata1:10M:autoextend

innodb_file_per_table=1

参数说明:

这个设置配置一个可扩展大小的尺寸为10MB的单独文件，名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内。【对数据来进行初始化的设置】

innodb_data_home_dir 代表为数据库文件所存放的目录

innodb_log_group_home_dir 为日志存放目录

innodb_file_per_table 是否使用共享以及独占表空间来

以上的几个参数必须在一起加入。

对于参数一些注意的地方

InnoDB不创建目录，所以在启动服务器之前请确认”所配置的路径目录”的确存在。这对你配置的任何日志文件目录来说也是真实的。使用Unix或DOS的mkdir命令来创建任何必需的目录。

通过把innodb_data_home_dir的值原原本本地部署到数据文件名，并在需要的地方添加斜杠或反斜杠，InnoDB为每个数据文件形成目录路径。

如果innodb_data_home_dir选项根本没有在my.cnf中提到，默认值是“dot”目录 ./，这意思是MySQL数据目录。

（2）数据按照主键顺序存储

插入时做排序工作，效率低。

（3）特定功能

事务、外键约束 ： 都是为了维护数据的完整性。

并发性处理：

innodb擅长处理并发的。因为它使用了行级锁定，只该行锁了，其它行没有锁。

行级锁定：row-level locking，实现了行级锁定，在一定情况下，可以选择行级锁来提升并发性。也支持表级锁定，Innodb会自带锁，不需要我们自己设置。

多版本并发控制, MVCC，效果达到无阻塞读操作。

（4）总结：innodb擅长事务、数据的完整性及高并发处理，不擅长快速插入（插入前要排序，消耗时间）和检索。

2.MyISAM存储引擎介绍

MySQL<= 5.5 MySQL默认的存储引擎。

ISAM：Indexed Sequential Access Method(索引顺序存取方法)的缩写，是一种文件系统。

擅长与处理，高速读与写。

（1）存储方式

数据和索引分别存储于不同的文件中。

（2）数据的存储顺序为插入顺序（没有经过排序）

插入速度快，空间占用量小。

（3）功能

a.全文索引支持。（mysql>=5.6时innodb 也支持）

b.数据的压缩存储。.MYD文件的压缩存储。

压缩前，数据是25600KB：

进行压缩：使用工具 myisamPack完成压缩功能：该工具mysql自带

进入到需要压缩表的数据目录，执行压缩指令 myisampack 表名。配置环境变量。

压缩后：

注意，压缩后，需要重新修复索引：

查看结果，发现现在的数据变成12741KB了，比之前的更小了：

压缩优势：节省磁盘空间，减少磁盘IO开销。特点：压缩后的表变成了只读表，不可写。

如果需要更新数据，则需要先解压后更新。利用工具：myisamchk –unpack 表名 进行解压

解压后，变成了原来的25600KB

刷新表的状态：flush table myisam_2

c.并发性：

仅仅支持表级锁定，不支持高并发。

支持并发插入。写操作中的插入操作，不会阻塞读操作（其他操作）

（4）关于Innodb 和myisam的取舍：

Innodb ：数据完整性，并发性处理，擅长更新，删除。

myisam：高速查询及插入。擅长插入和查询。

具体举例：

那么对于微博项目来看，选择哪一个存储引擎呢？

a.微博主要是插入微博和查询微博列表，较为适合MyISAM；

b.微博在更新微博和删除微博，要少的多，较为适合MyISAM；

c.对数据完整性的需求并没有那么强烈，比如用户删除微博，关联的转播和评论并不要求都做相应的行为，较为适合MyISAM；

那么对于记账财务系统，选择哪一款存储引擎呢？

a.财务系统除了读取和插入，经常要进行数据的修改和删除，较为适合InnoDB；

b.在进行财务变更的时候，如果失败需要回滚必须用到事务，较为适合InnoDB；

c.每个用户的财务数据完整性和同步性非常重要，需要外键支持，否则财务将会混乱，较为适合InnoDB。

3.其他存储引擎

（1）Archive：存档型，仅提供插入和查询操作。非常高效阻塞的插入和查询。

（2）Memory：内存型，数据存储于内存中，存储引擎。缓存型存储引擎。

（3）插件式存储引擎：用C和C++开发的存储引擎。

4.锁的概念：当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能互相影响），通过加锁来处理。

操作方面：

读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是阻塞其他客户端的写操作，不阻塞读操作。（并发读）

写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征是阻塞其他客户端的读，写操作。

锁定粒度（范围）：

行级：提升并发性，锁本身开销大

表级：不利于并发性，锁本身开销小。

二、字段类型选择

字段类型应该要满足需求，尽量要满足以下需求。

尽可能小（占用存储空间少）、尽可能定长（占用存储空间固定）、尽可能使用整数。

1.列类型之数值

（1）整型

MySQL数据库支持五种整型类型，包括：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT五种。

整型类型占用空间和取值范围

类型 字节 最小值 最大值

TINYINT 1 有符号：-128 无符号：0 有符号：127 无符号：255

SMALLINT 2有符号：-32768无符号：0有符号：32767无符号：65535

MEDIUMINT 3有符号：-8388608无符号：0有符号：8388607无符号：16777215

INT/INTEGER 4有符号：-2147483648无符号：0有符号：2147483647无符号：4294967295

BIGINT 8 有符号：-9223372036854775808无符号：0 有符号：9223372036854775807无符号：18446744073709551615

五种整型的适用场景：

TINYINT，年龄，包含在0~255之间；

SMALLINT，端口号，包含在0~65535之间；

MEDIUMINT，中小型网站注册会员，1600万够用；

INT，身份证编号，42亿可以用很久；

BIGINT，Twitter微博量，几百亿

（2）浮点型(非精确)

MySQL数据库支持两种浮点类型：FLOAT(单精度)和DOUBLE(双精度)两种

浮点型(非精确)占用空间和取值范围

类型 字节 范围

FLOAT 4 正数范围：1.175494351E-38~3.402823466E+38，负数范围：-3.402823466E+38~-1.175494351E-38

DOUBLE 8 正数范围：1.7976931348623157E-308~2.2250738585072014E+308

负数范围：-2.2250738585072014E+308~-1.7976931348623157E-308

（3）定点型(精确)

浮点型由于内部的存储方式是数值，导致它在一定程度上取得的是近似值而非精确值。如果使用定点型，那么就可以精确取得小数部分，因为它内部存储方式是字符串形式。

定点型(精确)占用空间和取值范围

类型 字节 范围

DECIMAL/NUMERIC M+2 M最大65位，D最大30位。

创建一个定点型格式：DECIMAL(M,D)，表示小数点D位，整数部分M位及M位内。

2.列类型之日期

MySQL数据库中有五个可用的日期时间数据类型，分别为：DATE、DATETIME、TIME、YEAR、TIMESTAMP。

日期时间类型占用空间和取值范围

类型 字节 最小值 最大值

YEAR 1 1901 2155

TIME 3 -838:59:59838:59:59

DATE 4 1000-01-01 9999-12-31

TIMESTAMP 4 1970-01-01 00:00:00 2038-01-19 03:14:07

DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59

TIMESTAMP有几个特点：

a.当更新一条数据的时候，设置此类型根据当前系统更新可自动更新时间；

b.如果插入一条NULL，也会自动插入当前系统时间；

c.创建字段时，系统会自动给一个默认值；

d.会根据当前时区来存储和查询时间，存储时对当前时区进行转换，查询时再转换为当前的时区。

//查看当前时区

SHOW VARIABLES LIKE 'time_zone';

//设置为东九区，查询时间就会加1小时

SET time_zone='+9:00';

DATE占用3个字节，包含年月日，范围和DATETIME一样。DATE长度是0，无法设置。

YEAR占用1个字节，包年年份，长度默认为4位，无法设置。

TIME占用3个字节，包含时分秒，长度0到6之间，用于设置微秒。对于TIME的范围的时是-838到838的原因，是因为TIME类型不但可以保存一天的时，还可以包含时间之间的间隔。

综上考虑：使用datetime，当然也可以使用int（11）来保存时间戳。

关于INT(11)存放时间戳的优点如下：

a.INT占4个字节，DATETIME占8个字节；

b.INT存储索引的空间比DATETIME小，查询快，排序效率高；

c.在计算机时间差等范围问题，比较方便。

3.列类型之字符

字符集校对规则utf8_general_ci表示校对时不区分大小写，相对的cs表示区分大小写。还有一个bin结尾的是字节比较。而general是地区名，这里是通用，utf8表示编码。如果是gbk，可以使用gbk_chinese_ci，如果是utf8则用utf8_general。MySQL提供了多种对字符数据的存储类型，包括：CHAR、VARCHAR、VARBINARY、BLOB、TEXT、ENUM和SET等多种字符类型。

（1）CHAR是保存定长字符串，而VARCHAR则是保存变长字符串。CHAR(5)表示必须保存5个字符，而VARCHAR(5)则表示最大保存字符为5。如果是UTF8编码下，长度为5的CHAR类型，最多可以存储15字节，也就是5个汉字的内容。因为一个汉字占3个字节。

由于CHAR类型是定长，MySQL会根据定义的长度进行分配空间，在处理速度上比VARCHAR快的多，所以适合存储例如手机、身份证这种定长的字符，否则就会造成浪费。那么CHAR类型最大可以插入255个字符，最多可以存储765个字节。

（2）BINARY和VARBINARY是采用二进制存储的，没有字符集概念，意义在于防止字符集的问题导致数据丢失，存储中文会占用两个字符，会乱码，半截会问号。因为是采用二进制存储，在比较字符和排序的时候，都是二进制进行的，所以只有需要操作二进制时才需要使用。

（3）八种适合文本内容的大数据类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOG、BLOB、MEDIUMTEXT、LONGTEXT。

综上：短文本定长用char，变长用varchar，长文本用text

4.列类型之属性

无符号(UNSIGNED)和填充零(ZEROFILL)，还有是否为空、默认值、主键、自动编号。

严格模式

我们使用的是WAMP集成环境，默认安装的情况下，是非严格模式，用于部署阶段。而开发调试阶段，强烈建议使用严格模式，方便开发中调试将问题及时暴露出来。因为在非严格模式下将NULL插入NOTNULL等非法操作都是被运行的。设置严格模式只要打开my.ini文件，在末尾添加一句：

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后，重启服务器即可。检查SQL_MODE状态

SELECT @@global.sql_mode;

三、范式与逆范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

第一范式1NF，原子性

第二范式2NF，消除部分依赖

第三范式3NF，消除传递依赖

1、范式

（1）第一范式：具有原子性，确保每列保持原子性。

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。
（2）第二范式：主键列与非主键列遵循完全函数依赖关系，确保表中的每列都和主键相关。

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
（3）第三范式：非主键列之间没有传递函数依赖关系索引，确保每列都和主键列直接相关,而不是间接相关。

所谓传递函数依赖，指的是如果存在"A→B→C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：

关键字段→非关键字段x→非关键字段y

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

先满足第一范式，再满足第二范式，才能满足第三范式。

2、逆范式

逆范式是指打破范式，通过增加冗余或重复的数据来提高数据库的性能。

示例： 假如有一个商品表Goods：

字段有Goods_id(商品表), goods_name(商品名称), cat_id(所属类别的id)。

还有一个分类表Category：

字段有Cat_id(类别id), cat_name(类别名称)。

现在要查询类别id为3的商品的数量，例如分类列表查询：

分类ID 分类名称 商品数量

3 计算机 567

可以使用下列sql语句：

Select c.*, count(g.goods_id) as goods_count from category as c left join goods as g c.cat_id=g.cat_id group by c.cat_id;

但是，假如商品数量较大，那么就比较耗性能了。这时，我们可以考虑重新设计Category表：增加存当前分类下商品数量的字段。

Cat_id, cat_name, goods_count

每当商品改动时，修改对应分类的数量信息。

再查询分类列表时：Select * from category;

此时额外的消耗，出现在维护该字段的正确性上，保证商品的任何更新都正确的处理该数量才可以。

四、索引

1．索引概述

利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。索引的关键字一定是排序的。索引本质上是表字段的有序子集，它是提高查询速度最有效的方法。一个没有建立任何索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询效率极低、速度也极慢。如果建立索引，那么就好比一本添加的目录，通过目录的指引，迅速翻阅到指定的章节，提升的查询性能，节约了查询资源。

测试查询，添加索引前后比对执行时间：

2．索引种类

从索引的定义方式和用途中来看：主键索引，唯一索引，普通索引，全文索引。

无论任何类型，都是通过建立关键字与位置的对应关系来实现的。索引是通过关键字找对应的记录的地址。

以上类型的差异：对索引关键字的要求不同。

关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）。

普通索引,index：对关键字没有要求。

唯一索引,unique index：要求关键字不能重复。同时增加唯一约束。

主键索引,primary key：要求关键字不能重复，也不能为NULL。同时增加主键约束。

全文索引,fulltext key：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。

关键字含义：可以是某个字段，也可以是某些字段。如果一个索引通过在多个字段上提取的关键字，称之为复合索引。 命令:alter table exp add index (field1, field2);

PS：这里主键索引和唯一索引的区别在于：主键索引不能为空值，唯一索引允许空值；主键索引在一张表内只能创建一个，唯一索引可以创建多个。主键索引肯定是唯一索引，但唯一索引不一定是主键索引。

3.索引操作

(1)创建主键索引

创建一个无符号整型且自动增长的列，然后设置成主键即可。

//通过EXPLAIN语句查看索引状态

EXPLAIN SELECT * FROM think_user WHERE id=1;

(2)创建普通或唯一索引

直接进入navicat设计表的第二栏，选择一个字段(比如user字段)，添加一个Nomral(普通索引)或Unique(唯一索引)。

//通过EXPLAIN语句查看索引状态

EXPLAIN SELECT * FROM think_user WHERE user='蜡笔老新';

//查看表所有索引情况

SHOW INDEX FROM think_user;

（3）使用sql语句的方式建立索引----建表时就创建索引

注意：索引可以起名字，但是主键索引不能起名字，因为一个表仅仅可以有一个主索引，其他索引可以出现多个。名字可以省略，mysql会默认生成，通常使用字段名来充当。

（4）使用sql语句的方式建立索引----更新表时创建索引

注意：如果表中存在数据，数据符合唯一或主键的约束才可能创建成功。auto_increment属性，依赖于一个KEY。

（5）使用sql语句的方式删除索引，auto_increment依赖于KEY。

（6）Explain 执行计划

可以通过在select语句前使用 explain，来获取该查询语句的执行计划，而不是真正执行该语句。

删除索引时，再看执行计划：

从查询的行数可知，有索引时查询会快的多，因为它只需要查找一行，而没有索引时，会造成全表扫描。

注意：select语句才能获取到执行计划。（新版本5.6会扩展其他语句的执行计划的获取）

4.索引原则

如果索引不遵循使用原则，则可能导致索引无效。

（1）列独立

如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧。

第三个语句 empno-1就不是列独立：就不能用索引。类似函数等等。（write_time < unix>

其他两个列独立可以使用：

（2）左原则

Like：匹配模式必须要左边确定不能以通配符开头。

假如业务逻辑上出现: field like ‘%keywork%’;类似查询，需要使用全文索引。

复合索引：一个索引关联多个字段，仅仅针对左边字段有效果。

示例：添加复合索引

对Ename的查询，使用了索引，结果如下：

Empno的查询没有使用索引，结果如下：

（3）OR的使用

必须要保证 OR 两端的条件都存在可以用的索引，该查询才可以使用索引。

为后面的条件增加可以使用的索引后，再查看执行计划：

（4）MySQL智能选择

即使满足了上面说原则，MySQL也能弃用索引：如下图

弃用索引的主要原因：

查询即使使用索引，会导致出现大量的随机IO，相对于从数据记录的第一条遍历到最后一条的顺序IO开销，还要大。

综上归纳：

a、不要过度索引。索引越多，占用空间越大，反而性能变慢；

b.只对WHERE子句中频繁使用的建立索引；

c.尽可能使用唯一索引，重复值越少，索引效果越强；

d.使用短索引，如果char(255)太大，应该给它指定一个前缀长度，大部分情况下前10位或20位值基本是唯一的，那么就不要对整个列进行索引；

e.充分利用左前缀，这是针对复合索引，因为WHERE语句如果有AND并列，只能识别一个索引(获取记录最少的那个)，索引需要使用复合索引，那么应该将WHERE最频繁的放置在左边。

f.索引存在，如果没有满足使用原则，也会导致索引无效：

5.索引的使用场景

（1）索引检索：检索数据时使用索引。

（2）索引排序

如果order by 排序需要的字段上存在索引，则可能使用到索引。

例如，按照ename字段排序查询：

此时，没有任何索引。在ename字段上建立索引后：

不会用到查询检索索引是因为没有用where条件查询，而真实执行时，就会用到排序索引。

Tip：对比以上两个执行计划：

extra位置：

其中：extra额外信息。加了索引后就不用使用文件排序了。

Using filesort，表示使用文件排序（外部排序，内存外部）。

（3）索引覆盖

索引拥有的关键字内容，覆盖了查询所需要的全部数据，此时，就不需要在数据区获取数据，仅仅在索引区即可。覆盖就是直接在索引区获取内容，而不需要在数据区获取。

例如，利用名字检索：

可以在ename字段建立索引：

分析执行：

再增加一个索引：

完成相同的查询：

查询的字段刚好是复合索引包含的字段。所以就使用了复合索引。

说明，不是非要查询用到，才可以索引覆盖，只要满足要求都可以覆盖！

建立索引索引时，不要仅仅考虑where检索，同时考虑其他的使用场景。（在所有的where字段上增加索引，就是不合理的）

6.前缀索引

前缀索引是建立索引关键字一种方案。通常会使用字段的整体作为索引关键字。有时，即使使用字段前部分数据，也可以去识别某些记录。就比如一个班级里，我要找王xx，假如姓王的只有1个人，那么就可以建一个前缀索引，就是王。

语法：

Index `index_name` (`index_field`(N))使用index_name前N个字符建立的索引。

那么N究竟是多少？使用N长度所达到的辨识度，极限接近于使用全部长度的辨识度即可！

先计算最大的辨识度M：

公式：先计算总的记录数m，再求该字段不重复的记录数q，那么M=m/q。然后依次取得前N个字符，N逐步增加，进行对比，直到找到极限接近于M的，那么最后的N就是我们要找的N。

求得辨识度为1.4774.，也就是说一个前缀索引可以对应1.4774条记录。

然后依次取得前N个字符，进行对比，找到极限接近的：

可见，9 时，已经极限接近，提高长度，不能明显提升辨识度，因此可以使用前9个字符：

Tip：前缀索引不能用于索引覆盖！

7.全文索引

该类型的索引特殊在：关键字的创建上。是为了解决 like‘%keyword%’这类查询的匹配问题。（mysql的全文索引几乎不用，因为它不支持中文，我们应该使用sphinx全文索引）。

示例：

假如有一张表，表中有标题和内容两个字段，现在要查询标题或者内容包含 “database” 关键字的记录。

补充：text和varchar的区别是text的数据不存在记录里，一条记录的最大空间是65535.

形成的SQL如下：

Select * from articles where title like ‘?tabase%’ or body like ‘?tabase%’;

此时不能建立普通索引，查询不符合左原则，建立了也使用不了。

此时全文索引就可以发挥其作用了：

直接使用上面的SQL，需要使用特殊的全文索引匹配语法才可以生效： Match() against();

Tip: 该MYSQL提供的全文索引，不能对中文起作用！

使用Match() against() 返回关键字的匹配度（关键字与记录的关联程度）。

停止词 in：

发现in这个词，是不能被全文索引所检索到的。因为in这个词是不可以用在全文索引的关键词里的，没有谁会在一段文本里检索这样一个词。

思考：与 like %in% 是否相同？不同。

原因何在呢？全文索引，索引的的关键字，不是整个字段数据，而是从数据中提取的关键词。

8.索引结构-b-tree介绍

Hash、B-Tree（B树）两种数据结构。指的是mysql存储索引所采用的数据结构。其中，用户所维护的所有的索引结构 B-Tree结构。

B-Tree的结构如下：

每个节点，存储多个关键字。关键字也会对应记录地址

以上设计为了解决一次性磁盘IO开销，可以读取到更多的关键字数量。

每个关键字之间，存在子节点指针：

如果是复合索引：

关键字的排序先排左侧字段，在左侧字段相同的情况下，再排序右侧字段：

9.聚集索引(聚簇索引)

B+Tree(B-Tree的变种)

在innodb的存储引擎上，主键索引是与数据记录存储在一起的（聚簇在一起的）。

带来的问题：

Innodb的其他索引，非主键索引（二级索引）：

关键字对应的不再是记录的地址，而是记录的主键。

可见，检索需要二次检索。先检索到主键ID，再检索记录。

五、查询缓存query_cache

将select的结果，存取起来共二次使用的缓存区域：

MySQL提供的缓存区：

未开启前：

两次查询时间消耗一致。

开启查询缓存，通过变量控制：

开启并设置大小：

再次执行查询：

可见，第二次查询，使用了开启的缓存！

注意事项：查询缓存存在判断是严格依赖于select语句本身的：严格保证SQL一致。

如果查询时包含动态数据，则不能被缓存。

一旦开启查询缓存，MySQL会将所有可以被缓存的select语句都缓存。如果存在不想使用缓存的SQL执行，则可以使用 SQL_NO_CACHE语法提示达到目的：

注意：这里的缓存仅当数据表的记录改变时，缓存才会被删除。而不是依靠过期时间的。

六、分区分表

日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

分区，partition，分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

其实每个分区，就是独立的表。都要存储该分区数据的数据，索引等信息。

创建分区：在创建表时，指定分区的选项：

Create table table_name (定义)

Partition by 分区算法 (参数) 分区选项。

例如：Partition by key (id) partitions 5;

采用key取余算法，根据id的值进行取余，即对5取余，然后分配到5个区里。

分区结果如下：myisam下

Innodb下

Tip：分区与存储引擎无关，是MySQL逻辑层完成的。

可以通过变量查看当前mysql是否支持分区：

1.分区算法

MySQL提供4种分区算法：取余：Key，hash 条件：List，range 。

参与分区的参数字段需要为主键的一部分。

（1）KEY – 取余 ，按照某个字段进行取余

分成5个区，就是对5取余。将id对5取余。

（2）Hash – 取余，按照某个表达式的值进行取余

示例：学生表分区，按照生日的月份，划分到12个表中。

注意：Key，hash都是取余算法，要求分区参数（括号里的），返回的数据必须为整数。

（3）List – 条件 – 列表，需要指定的每个分区数据的存储条件。

示例：按照生日中的月份，分成春夏秋冬四个分区。

List，条件依赖的数据是列表形式。

（4）Range - 条件 – 范围， 条件依赖的数据是一个条件表达式。

逻辑：按照生日的年份分成不同的年龄段。

2.分区的管理与选择

（1）取余：key，hash

增加分区数量： add partition partitions N

减少分区数量： COALESCE partition N

采用取余算法的分区数量的修改，不会导致已有分区数据的丢失，因为会重新分配数据到新的分区。

（2）条件：list，range

添加分区

删除分区：

Drop partition partition_name;

注意：删除条件算法的分区，会导致分区数据丢失。添加分区不会。

（3）选择分区算法

平均分配：就按照主键进行key(primary key)即可(非常常见)

按照某种业务逻辑分区：选择那种最容易被筛选的字段，整数型

3.分表

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。（需要手动分表）

分表是分区之前用的，MYSQL5.1后，就开始用分区代替分表了。分表很少用了。

（1）水平分表

创建结构相同的N个表；

再创建用于管理学生ID的表student_id：(该表是为了提供自增的ID)

PHP客户端逻辑：

Merge，mrg_myisam

是MySQL提供一个可以将多个结构相同的myisam表，合并到一起的存储引擎：

（2）垂直分表

一张表中存在多个字段。这些字段可以分为常用字段和非常用字段，为了提高查表速度，我们可以把这两类字段分开来存储。主要目的，减少每条记录的长度。

通常我们按以下原则进行垂直拆分:把不常用的字段单独放在一张表;把text，blog等大字段拆分出来放在附表中;经常组合查询的列放在一张表中;

例如学生表可以分成：

基础表(Student_base)和额外表(Student_extra)，两张表中记录为1:1的关系。

基础信息表Student_base

Id name age

额外信息表Student_extra

Id 籍贯 政治面貌

七、服务器架构介绍

服务器架构，不仅仅是用一台MySQL

主从复制：

Mysql服务器内部支持复制功能，仅仅需要通过配置完成下面的拓扑结构。一主多从典型结果：主服务器负责写数据。从服务器负责读数据。复制功能mysql会自带。

读写分离，负载均衡：

php不再操作MYSQL数据库服务器，而是去操作读写分离、负载均衡服务器，只要服务器安装了mysql proxy或Ameoba软件就可以实现读写分离和负载均衡，读写分离是指该服务器会判断客户端的操作是读还是写，从而选择操作mysql主服务器还是从服务器。负载均衡算法是指，客户端读操作时，该服务器会根据取余算法去选择一台从服务器。

上面的架构可以提升整体服务器的效率，高性能。

同时，服务器架构需要保证，高可用（稳定），7x24不宕机。因此需要增加一些冗余服务器以便备用。时时检测正在用的服务器。

八、SQL优化

1.对于并发性的SQL

少用（不用）多表操作（子查询，联合查询），而是将复杂的SQL拆分多次执行。如果查询很原子（很小），会增加查询缓存的利用率。

2.大量数据的插入

多条 insert或者Load data into table（从文件里载入数据到表里）

建议，先关闭约束及索引，完成数据插入，再重新生成索引及约束。

针对于myisam，步骤：

Alter table table_name disable keys; 禁用索引约束

大量的插入

Alter table table_name enable keys; 启用

针对innodb，步骤：

Drop index, drop constraint 删除索引及约束，要保留主键

Begin transaction|set autocommit=0; 开启事务，不让他自动提交

[数据本身已经按照主键值排序]

大量的插入

Commit;

Add index, add constraint

3.分页

分页假定Limit offset, size; size = 10;

Limit 的使用，会大大提升无效数据的检索（被跳过），因为是先检索，检索会检索全部，再取得想要的。好的做法是使用条件等过滤方式，将检索到的数据尽可能精确定位到需要的数据上。

4.随机选一些数据，不要使用Order by Rand()

上面的查询，会导致每条记录都执行rand()，成本很高！

建议，通过mt_rand()，先确定的随机主键，再从数据表中获取数据。

九、慢查询日志的使用

定位执行较慢的查询语句方案。

show variables like 'slow_query%'; show variables like '%long_query%';

Slow_query_log = 0|1

Long_query_time = N 超过该时间临界点，就为慢查询。

开启日志

set global slow_query_log=1; set long_query_time=0.5；

执行SQL，查看：