MySQL之Schema与数据类型优化和创建高性能的索引(一)

Schema与数据类型优化

只修改.frm文件

从前面的例子中可以看到修改表的.frm文件是很快的，但MySQL有时候会在没有必要的时候也重建.如果愿意冒一些风险，可以让MySQL做一些其他类型的修改而不用重建表。下面这些操作是有可能不需要重建表的:

• 1.移除(不是增加)一个列的AUTO_INCREMENT属性
• 2.增加、移除，或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的变量，查询将会返回一个空字符串

基本的技术是为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件，像下面这样:

• 1.创建一张有相同结构的空表，并进行所需要的修改(例如增加ENUM常量)
• 2.执行FLUSH TABLES WITH READ LOCK.这将会关闭所有正在使用的表，并且进制任何表被打开
• 3.交换.frm文件
• 4.执行UNLOCK TABLES来释放第2步的读锁
  下面以给sakila.film表的rating列增加一个常量为例来说明。当前列看起来如下

mysql> SHOW COLUMNS FROM film LIKE 'rating';
+--------+------------------------------------+------+-----+---------+-------+
| Field  | Type                               | Null | Key | Default | Extra |
+--------+------------------------------------+------+-----+---------+-------+
| rating | enum('G','PG','PG-13','R','NC-17') | YES  |     | G       |       |
+--------+------------------------------------+------+-----+---------+-------+
1 row in set (0.07 sec)

假设我们需要为那些对电影更加谨慎的父母们增加一个PG-14的电影分级：

mysql> CREATE TABLE film_new LIKE film;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE film_new
    -> MODIFY COLUMN rating ENUM('G','PG','PG-13','R','NC-17','PG-14')
    -> DEFAULT 'G';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)

注意，我们是在常量列表的末尾增加一个新的值。如果把新增的值放在中间，例如PG-13之后，则会导致已经存在的数据的含义被改变:已经存在的R值将变成PG-14,而已经存在的NC-17将变成R，等等。
接下来用操作系统的命令交换.frm文件

/var/lib/mysql/sakila# mv film.frm film_tmp.film
/var/lib/mysql/sakila# mv film_new.frm film.frm
/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm

再回到MySQL 命令行，现在可以解锁表并且看到变更后的效果了:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (18.96 sec)
mysql> SHOW COLUMNS FROM film LIKE 'rating';
+--------+--------------------------------------------+------+-----+---------+-------+
| Field  | Type                                       | Null | Key | Default | Extra |
+--------+--------------------------------------------+------+-----+---------+-------+
| rating | enum('G','PG','PG-13','R','NC-17','PG-14') | YES  |     | G       |       |
+--------+--------------------------------------------+------+-----+---------+-------+
1 row in set (0.09 sec)

最后要做的事删除为完成这个操作而创建的辅助表:

mysql>DROP TABLE film_new;

快速创建MyISAM索引

为了高效地载入数据到MyISAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引:

mysql>ALTER TABLE test.load_data DISABLE KEYS;
mysql>ALTER TABLE test.load_data ENABLE KEYS;

这个技巧能够发挥作用，是因为构建索引的工作被延迟到数据完全载入以后，这个时候已经可以通过排序来构建索引了，这样做会快很多，并且使得索引树的碎片更少、更紧凑。
不幸的是，这个办法对唯一索引无效，因为DISABLE KEYS只对非唯一索引有效。MyISAM会在内存中构造唯一索引，并且为载入的每一行检查唯一性。一旦索引的大小超过了有效内存大小，载入操作就会变得越来越慢。
在现代版本的InnoDB版本中，有一个类似的技巧，这依赖于InnoDB的快速在线索引创建功能。这个技巧是，先删除所有的非唯一索引，然后增加新的列，最后重新创建删除掉的索引。Percona Server可以自动完成这些操作步骤。也可以使用像前面说的ALTER TABLE的骇客方法来加速这个操作，但需要多做一些工作并且承担一定的风险。这对备份中载入数据是很有用的，例如，当已经直到所有的数据都是有效的并且没有必要做唯一性检查就可以这么来操作。
下面是操作步骤:

• 1.用需要的表结构创建一张表，但是不包括索引。
• 2.载入数据到表以后构建.MYD文件
• 3.按照需要的结构创建另外一张空表，这次要包含索引。这回创建需要的.frm和.MYI文件
• 4.获取读锁并刷新表
• 5.重命名第二张表的.frm和MYI文件，让MySQL认为是第一张表的文件
• 6.释放读锁
• 7.使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引

创建高性能的索引

概述。

索引(在MySQL中也叫做"键(key)")是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能。
索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响语法重要。在数据量较小时负载较低时，不恰当的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降(除非特别说明，假设都是硬盘驱动器。固态硬盘驱动器有着完全不同的性能特性)。不过，索引却经常被忽略，有时候甚至被误解，所以在实际案例中经常会遇到由糟糕索引导致的问题。索引优化应该是对查询性能优化是最有效的手段，索引能够轻易将查询性能提高几个数量级，"最优"的索引有时比一个"好的"索引性能要好两个数量级。创建一个真正“最有”的索引经常需要重写查询

索引基础

在MySQL中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。假如要运行下面的查询:

mysql> SELECT first_name FROM actor WHERE actor_id = 5;

如果在actor_id列上建有索引，则MySQL将使用该索引找到actor_id为5的行，也就是说，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。索引可以包含一个或者多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列，创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。

如果使用的是ORM，是否还需要关心索引？

简而言之:是的，仍然需要理解索引，即使是适用对象关系映射(ORM)工具。
ORM工具能够生产符合逻辑的、合法的查询(多数时候)，除非只是生成非常基本的查询(例如仅是根据主键查询)，否则它很难生成适合索引的查询。无论是多个复杂的ORM工具，在精妙和复杂的索引面前都是"浮云"。很多时候，即使是查询优化技术专家也很难兼顾到各种情况，更别说ORM了