ISAM表

其它数据表的自动编号机制都以ISAM表中的机制为基础。

MyISAM数据表

HEAP数据表

BDB数据表

InnDB数据表

AUTO_INCREMENT是数据列的一种属性，只适用于整数类型数据列。

设置AUTO_INCREMENT属性的数据列应该是一个正数序列，所以应该把该数据列声明为UNSIGNED，这样序列的编号个可增加一倍。

AUTO_INCREMENT数据列必须有唯一索引，以避免序号重复。

AUTO_INCREMENT数据列必须具备NOT NULL属性。

AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127,如加上UNSIGNED，则最大为255。一旦达到上限，AUTO_INCREMENT就会失效。

当进行全表删除时，AUTO_INCREMENT会从1重新开始编号。全表删除的意思是发出以下两条语句时：

delete from table_name;
or
truncate table table_name

这是因为进行全表操作时，MySQL实际是做了这样的优化操作：先把数据表里的所有数据和索引删除，然后重建数据表。如果想删除所有的数据行又想保留序列编号信息，可这样用一个带where的delete命令以抑制MySQL的优化：

delete from table_name where 1;

这将迫使MySQL为每个删除的数据行都做一次条件表达式的求值操作。

强制MySQL不复用已经使用过的序列值的方法是：另外创建一个专门用来生成AUTO_INCREMENT序列的数据表，并做到永远不去删除该表的记录。当需要在主数据表里插入一条记录时，先在那个专门生成序号的表中插入一个NULL值以产生一个编号，然后，在往主数据表里插入数据时，利用LAST_INSERT_ID()函数取得这个编号，并把它赋值给主表的存放序列的数据列。如：

insert into id set id = NULL;
insert into main set main_id = LAST_INSERT_ID();

可用alter命令给一个数据表增加一个具有AUTO_INCREMENT属性的数据列。MySQL会自动生成所有的编号。

要重新排列现有的序列编号，最简单的方法是先删除该列，再重建该，MySQL会重新生连续的编号序列。

在不用AUTO_INCREMENT的情况下生成序列，可利用带参数的LAST_INSERT_ID()函数。如果用一个带参数的LAST_INSERT_ID(expr)去插入或修改一个数据列，紧接着又调用不带参数的LAST_INSERT_ID()函数，则第二次函数调用返回的就是expr的值。下面演示该方法的具体操作：

先创建一个只有一个数据行的数据表：
create table seq_table (id int unsigned not null);
insert into seq_table values (0);
接着用以下操作检索出序列号：
update seq_table set seq = LAST_INSERT_ID( seq + 1 );
select LAST_INSERT_ID();
通过修改seq+1中的常数值，可生成不同步长的序列，如seq+10可生成步长为10的序列。

该方法可用于计数器，在数据表中插入多行以记录不同的计数值。再配合LAST_INSERT_ID()函数的返回值生成不同内容的计数值。这种方法的优点是不用事务或LOCK，UNLOCK表就可生成唯一的序列编号。不会影响其它客户程序的正常表操作。

存放到数据列中的数据类型。

数据值的取值范围。

考虑性能和处理效率。

数据如何进行比较，是否区分大小写。

是否要在数据列上建立索引。

没有索引的表是没有排序的数据集合，如果要查询数据需进行全表扫描。有索引的表是一个在索引列上排序了数据表，可通过索引快速定位记录。在MyISAM和ISAM数据表中，数据行保存在数据文件中，索引保存在索引文件中。BDB与InnoDB数据表把数据与索引放在同一个文件中。

在多表关联查询中，索引的作用就更大。如果没有索引，在最坏的情况下，全表扫描的次数可能是各表数据行的组合个数，可能是一个天文数字。这样的查询是破坏性的，可能会造成数据库瘫痪。

对于使用了MIN()或是MAX()函数的查询，如果相关的数据列上有索引，MySQL能直接找到该最大、最小值的行，根本不用一个一个地去检查数据行。

索引加快ORDER BY 和 GROUP BY子句的操作。

当在数值型数据列上查询数据，而该列有索引，索引能使MySQL根本不用去读取数据行，直接从索引取值。

索引需占用磁盘空间。

索引会减慢在索引数据列上的插入、删除和修改操作。

索引应该创建在搜索、排序、分组等操作所涉及的数据列上。也就是说，在where子句，关联检索中的from子句、order by或group by子句中出现过的数据列最适合用来创建索引。

尽量使用唯一索引，它能使索引发挥最好的效能。

尽量用比较短的值进行索引。当对字符串进行索引时，应该指定一个前缀长度，比如对字符串的前10位或20位的字符进行排序，而不用把整个字符串几十个字符用来索引排序。这样能减少磁盘I/O，提高处理速度。最重要的一点是，键值越短，索引缓冲区里容纳的键值也就越多，而MySQL同时保存在内存里的索引越多，索引缓冲区的命中率也就越高。当然，只对数据列第一个字符进行索引是没什么意义的。

充分利用最左前缀。所谓最左前缀也就是在复合索引中最边的索引列。如复合索引(a,b,c) ，其中a就是最左前缀。它是使用率最高的索引，需认真选择。

不要建太多索引，索引是会消耗系统资源的，要适可而止。

索引主要用于<、<=、=、>=、>、BETWEEN等的比较操作中，所以索引应该建立在与这样操作相关的数据列上。

利用慢查询日志来找出性能差的查询，通过mysqldumpslow可查看该日志。针对性能差的查询可利用索引来加快查询速度。

尽量对同类型的数据列进行比较。如：VARCHAR(5)和VARCHAR(5)是同类型的，CHAR(5)和VARCHAR(5)是不同类型的。

尽量让索引的数据列在比较表达式中单独出现，不要把它包含在函数或复杂表达式。否则索引会不起作用。

尽量不要在LIKE模式的开头使用通配符。如：%string%。

对于MyISAM和BDB数据表，用ANALYZE TABLE语句让服务器对索引键值的分布进行分析，为优化程序提供更有价值的信息。另一个方法是用myisamchk --analyze(适用于MyISAM表)或isamchk --analyze(适用于ISAM表)命令。

用EXPLAIN语句来分析查询语句的执行效率。检查查询所使用的索引是不是能够迅速地排除不符合条件的数据行，如果不是，可以试着用STRAIGHT_JOIN强制各有关数据表按指定顺序进行关联。

尝试查询的不同写法，比较运行情况。

不要滥用MySQL的类型自动转换功能。自动转换会减慢查询的速度并会使有关的索引失效。

尽量选用尺寸较小的数据列。这样能节约磁盘空间和加快查询速度。如果较短的数据列上建有索引，则索引的处理速度会进一步提高。

针对数据列类型，尽量选择最适用的数据表类型。如固定长度数据列在MyISAM或ISAM数据表中的速度是最快的，所以在这样数据表中尽量使用char类型而不是varchar类型来保存字符串数据。对于InnoDB数据表类型，由于varchar类型可有效减少占用空间，从而减少磁盘I/O，所以使用varchar类型是有利的。对于BDB类型数据表，使用定长和不定长列类型的区别就不大，可任选一种。

尽量把数据列声明为NOT NULL，以节约存储空间和加快处理速度。

对于取值范围有限的数据列，考虑使用ENUM数据列类型。ENUM数据列类型在MySQL中的处理速度是很快。

使用PROCEDURE ANALYSE()语句来分析数据表，它会对数据列的声明提出建议，我们可根据建议进行修改。

select * from table_name PROCEDURE ANALYSE();
select * from table_name PROCEDURE ANALYSE(16,256);    #(16,256)含义是：如果某列的不同取值在16个以上或长度超过256字节，就不提出使用ENUM的建议。

用OPTIMIZE TABLE语句对容易出现碎片的数据表进行整理。包含可变长数据列的数据表都会产生碎片，从而占用多余的磁盘空间和影响查询速度。所以要定期运行OPTIMIZE TABLE语句以防止数据表查询性能降低。但该语句只对MyISAM数据表有效。对各种数据表通用的碎片整理方法是这样的：先用工具程序mysqldump导出数据表，再删除数据表后重建，如：

$ mysqldump --opt db_name table_name > dump.sql
$ mysql db_name < dump.sql

把非结构化和变化大的数据放在BLOB数据列里，定期用OPTIMIZE TABLE命令优化。

人为地给数据表增加一个数据列，以充当索引。做法是这样的，先根据数据表里的其它数据列计算出一个散列值，并保存在一个数据列里，然后通过搜索散列值来检索数据行。注意，该技巧只适用于精确匹配型查询。散列值在大于，小于等的操作中不起作用。散列值可以MD5()(适用于3.23及以上版本)，SHA1()(适用于4.0.1及以上版本)，CRC32()(适用于4.1及以上版本)等函数生成。使用散列值支检索BLOB和TEXT值的做法比直接检索BLOB和TEXT本身的做法快。

尽量避免对大尺寸的BLOB值进行检索。如果要检索都应该通过它的上面提到散列值先进行筛选。而不应该盲目地在网络中传送大量BLOB值。

如果把BLOB值剥离到另外一个数据表里去，可实现数据表中其它数据列转变成固定长度数据列的话。就即可减少数据表碎片，又可使在原始表中的select *查询不会把大尺寸的BLOB值不必要地通过网络传送。

使用LOAD DATA语句要比INSERT语句的加载速度快。

LOAD DATA比LOAD DATA LOCAL语句的效率高。前者可由服务器直接从本地磁盘读取加载数据，后者需由客户程序去读取文件并通过网络传送到服务器。

如果一定要用INSERT语句，应尽量在一条语句中插入多个数据行。

如果必须使用多条INSERT语句，则应尽量把它们集中在一起放到一个事务中进行处理，而不是在自动提交模式下执行它们：如：

BEGIN;
INSERT INTO table_name values (...);
INSERT INTO table_name values (...);
INSERT INTO table_name values (...);
...
COMMIT;

对于不支持事务的表，应对表进行写锁定，然后在表锁定期间对表进行INSERT操作，如：

LOCK TABLES table_name WRITE;
INSERT INTO table_name ...;
INSERT INTO table_name ...;
INSERT INTO table_name ...;
...
UNLOCK TABLES;

利用客户/服务器通信协议中的压缩功能以减少网络传输的数据量。但该压缩会消耗大量的系统资源，所以小心使用。

尽量让MySQL插入默认值。不要在INSERT中写太多值，以减少网络传输量和服务器端的语法分析时间。

对于MyISAM和ISAM数据表，如果需加载大量数据，应先建立一个没索引的表，加载数据后再创建索引。该方法不适用于InnoDB或BDB数据表。

使用ALTER TABLE语句的DISABLE KEYS和ENABLE KEYS命令，如：

ALTER TABLE table_name DISABLE KEYS;
ALTER TABLE table_name ENABLE KEYS;

使用myisamchk或isamchk工具。如：

$ myisamchk --keys-used=0 table_name                   #禁止
$ myisamchk --recover --quick --key-used=n table_name  #激活
n是用来表明需要激活索引的位掩码，第0位对应第一个索引，如果有三个索引，n值就是7(二进制111)。索引编号可以下命令确定：
$ myisamchk --description table_name

写入请求将按它们到达服务器的顺序进行处理；

写操作的优先级要高于读操作。

LOW_PRIORITY会使写操作的优先级降低到读操作以下，也就是说读操作会阻塞该级别的写操作，SELECT的HIGH_PRIORITY有类似的作用。

INSERT语句中的DELAYED修饰会使插入操作被放入一个“延迟插入”队列。并返回状态信息给客户，使客户程序可在新数据行还没插入到数据表之前继续执行后面的操作。如果一直有客户读该数据表，新数据行会一直待在队列中，直到数据表没有读操作时，服务器才会把队列中的数据行真正插入到数据表中。该语句可用在以下场合，在一个有冗长查询的数据表中插入数据，而你又不想被阻塞，你就可发出INSERT DELAYED语句，把插入操作放入服务器“延迟插入”队列，你无需等待就马上可进行接下来的操作。

当一个数据表里从未进行过删除操作或刚刚对它进行过碎片整理的情况下，用INSERT语句插入的数据行只会被添加到数据表的末尾，而不会插入到数据表的中间位置。这样，对于MyISAM表，MySQL允许在有其它客户正在读操作的时间进行写操作。我们称之这并发插入。要使用该技巧，需注意以下两个问题：

MyISAM和ISAM数据表的检索速度最快，但如果在检索和修改操作较多的场合，会出锁竞争的问题，造成等待时间延长。

BDB和InnoDB数据表能在有大量修改操作的环境下提供很好的并发性，从而提供更好的性能。

MyISAM和ISAM数据表由于进行表级锁定，所以不会出现死锁现象，BDB和InnoDB数据表则存在死锁的可能性。

内存里的数据要比磁盘上的数据访问起来快；

站数据尽可能长时间地留在内存里能减少磁盘读写活动的工作量；

让索引信息留在内存里要比让数据记录的内容留在内存里更重要。

增加服务器的缓存区容量，以便数据在内存在停留的时间长一点，以减少磁盘I/0。下面介绍几个重要的缓冲区：

禁用用不着的数据表处理程序。如服务器是从源码创建，就可彻底禁用ISAM，InnoDB和BDB数据表。

权限表里的权限关系应尽可能简单，当然了，是要在保证安全的前提下。

在从源码创建服务器时，尽量使用静态库而不是共享库来完成其配置工作。静态库的执行速度更快，但如果要加载用户定义函数(UDF)的话，就不能使用静态库，因为UDF机制必须依赖动态库才能实现。

对于数据库服务器，内存是最重要的一个影响性能因素。通过加大内存，数据库服务器可把更多的数据保存在缓冲区，可大大减少磁盘I/O，从而提升数据库的整体性能。

配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。

合理分布磁盘I/O，应把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。

配置多处理器，MySQL是多线程的数据库，多处理器可同时执行多个线程。

在UNIX/Linux系统上，如果用源码编译安装，数据目录的位置默认是在/usr/local/mysql/var中；

在UNIX/Linux系统上，如果用二进制发行版安装，数据目录的位置默认是在/usr/local/mysql/data中；

在WINDOWS系统上，数据目录的位置默认是在c:/mysql/data中；

% mysqladmin variables

如果在一台机器上同时运行多个服务器，则可根据端口的不时来查询每个服务器的数据目录，如：

% mysqladmin --host=127.0.0.1 --port=port_number variables

如果--host是localhost，系统则会用一个UNIX套接字去连接数据库服务器，这时要使用--socket选项，所以查询语句变成：

% mysqladmin --host=localhost --socket=/path/to/socket variables

mysql> SHOW VARIABLES LIKE 'datadir';

在windows NT平台上可以使用“.”作为一条命名管道连接的主机名，用--socket选项给出命名管道的名字，如：

c: mysqladmin --host=. --socket=pipe_name variables

配置文件的中[mysqld]段中的datadir=/path/to/datadir设置也可查询到数据目录。

在mysqld程序的帮助信息里也有程序编译时默认的数据目录信息，可用以下命令显示：

% mysqld --help

服务器的配置文件，my.cnf；

服务器的进程ID(PID)文件；

服务器的日志文件和状态文件，这些文件对管理数据库有重要的价值；

DES密钥文件或服务器的SSL证书与密钥文件。

ISAM数据表是最原始的数据表，有三个文件，分别是：

.frm，存放数据表的结构定义；

.ISD，数据文件，存放数据表中的各个数据行的内空；

.ISM，索引文件，存放数据表的所有索引信息。

MyISAM数据表是ISAM数据表的继承者，也有三个文件，分别是：

.frm，结构定义文件；

.MYD，数据文件；

.MYI，索引文件。

MERGE数据表是一个逻辑结构，代表一组结构完全相同的MyISAM数据表构成的集合。它在文件系统中有二个文件，分别是：

.frm，结构定义文件；

.MRG，构成MERGE表的MyISAM数据表清单，每个MyISAM数据表名占一行。也就是说可通过改变该表的内容来改变MERGE数据表的结构。修改前请先刷新缓存(flush tables)，但不建议这样修改MERGE数据表。

BDB数据表用两个文件来表示，分别是：

.frm，结构定义文件；

.db，数据表数据和索引文件

InnoDB由于采用表空间的概念来管理数据表，所以它只有一个与数据表对应.frm文件，同一目录下的其它文件表示为表空间，存储数据表的数据和索引。

HEAP数据表是一个存在于内存中的表，所以它的数据和索引都存在于内存中，文件系统中只有一个.frm文件，以定义结构。

MySQL数据表类型的不同对数据表尺寸的限制：

操作系统对文件的尺寸限制，一般文件系统都对单个文件不得超过2G的限制。该约束会对数据库文件造成限制。InnoDB数据表可通过利用未格式化硬盘作为表空间来绕过该限制。

对于数据和索引分开两个文件存放的数据表，其中任何一个文件达到操作系统文件的最大限制，数据库表也就达到最大尺寸。

包含AUTO_INCREMENT数据列的表受到该数据列类型最大上限值的限制。

由于InnoDB数据表用表空间来管理，一个表空间可同时空纳多个数据表，所以数据表的最大尺寸受系统文件和同一表空间中数据表空间的约束。

在调整MySQL的数据目录时，要先停止服务器，再把数据目录移动到新的位置。接着，我们可选择在原来目录下创建一个符号链接指向新的位置，或者用启动选择--datadir指向新的数据目录。推荐用创建符号链接的方法，因为如果那个数据目录中有my.cnf文件，相应的服务器还能找到它。

数据库只能存在于MySQL数据目录中，所以只能使用符号链接的方法调整它的位置。在Linux系统的操作步骤如：

在windows下的操作方法不些不同，操作方法如下：

要移动数据表，必须满足以下所有条件才行：

在满足以上所有条件后，我们就可把.MYD数据文件和MYI索引文件移到新位置，再在原来位置创建两个同名符号链接指定新的位置。注意，.frm定义文件仍需留在原来的数据库目录中。

如以上条件不能全部满足，最好不要移动数据表文件。否则一旦你运行ALTER TABLE、OPTIMIZE TABLE、REPAIR TABLE语句对移动过的数据表进行优化或修改，这样数据表就会回到原来的位置，使移动操作失效。因为这些命令的执行过程是这样的：它会先在数据目录中创建一个临时数据表并对这个临时数据表进行优化或修改，然后删除原来的数据表(这里是你为了移动数据表而创建的一个符号链接)，再把临时数据表更名为原来的数据表名称。这样一来，你移走的数据表就和这个数据库完全没有关系了。基于以下的不稳定因素，如无特殊必要，不建议移动数据表。

InnoDB表空间是通过在选项文件中使用innodb_data_home_dir和innodb_data_file_path选项列出InnoDB表空间组成文件清单的方法来配置的，所以我们可通过修改这些选项来重新安置InnoDB表空间的组成文件。步骤如下：

状态文件和日志文件的位置可通过选项文件或启动服务器时指定。

User，连接数据库的用户名。

Host，允许连接到数据库服务器的主机名，“%”通配符代表所有主机。

Password，连接密码，已加密。

其它权限列，以“Y”或“N”标识是否有效。

使用mysqladmin程序：

% mysqladmin -h localhost -u root password "password"   #设置在本地以root身分登录的密码
% mysqladmin -h remote -u root password "password"      #设置远程主机以root身分登录的密码

在初始设置时，这两条语句都要运行，以确保数据库本地访问和网络访问的安全。

通过set password这条SQL语句设置：

mysql> set password for 'root'@'localhost' = password('password');
mysql> set password for 'root'@'remote' = password('password');

直接修改user权限表：

mysql> use mysql;
mysql> update user set password=password('password') where user='root';
mysql> flush privileges;                            #重载权限表，使修改马上生效

MySQL使用驻留在内存中的权限表拷贝来进行访问控制，当使用mysqladmin和set password设置密码，MySQL会监察到权限表已被修改，它自动重载该表。而用update的方式，MySQL就监察不到变化，需手动用flush privileges命令刷新内存中的权限表，以使它马上生效。

Linux平台：

Linux平台下，每一个进程都需由一个用户来运行，MySQL最好不要以root用户来运行。我们可创建一个mysql用户和mysql组，MySQL服务器程序目录和数据目录由这个用户和组所拥有，其它用户没有任何权限。以mysql用户来运行MySQL服务器。

% mysqld --user=mysql     #即使以root用户执行该命令，MySQL数据库还是会与mysql用户ID关联。

为了使服务器在系统启动时自动以mysql用户运行，需配置my.cnf配置文件 ，把user=mysql包含在[mysqld]段中。

关闭服务器可用% mysql.server stop或% mysqladmin -u root -p shutdown

windows平台：

手动方式：直接运行c:mysqld命令。

作为服务方式：运行c:mysqld-nt --install命令，把mysqld-nt安装为windows的服务，此后，每当windows启动时，它就会自动运行。mysqld-nt是一个支持命名管道的MySQL服务器。运行c:mysqld-nt --remove可把服务删除。手动启动关闭服务的方法是运行c: et start mysql和c: et stop mysql命令。

用 % kill -TERM PID关闭服务器，用-TERM信息可使服务器在关闭前把内存中的数据写入磁盘。如果服务器没有响应，我们可用% kill -9 PID来强制删除进程，但不建议这样做。这时内存中的数据不会写入磁盘，造成数据不完整。如果你是用mysql_safe脚本启动MySQL服务器的，这个脚本会监控服务器的运行情况并在它被终止时重启服务器，所以如需关闭服务器，要先终止该进程，然后再真正终止mysqld进程。

接着用--skip_grant-tables启动服务器。这时MySQL服务器将不使用权限表对连接操作进行验证。你就可在不提供root密码的情况下连接上服务器，并获得root的权限。这样你就可用上面介绍的修改密码的方法重设root用户的密码。注意：连接上服务器后，要马上执行flush privileges命令，使权限表读入内存并生效，以阻止其他的连接。该语句还重新激活grant语句，在MySQL服务器不使用权限表时，grant语句被禁用。

修改完root用户密码后，我们就可关闭服务器并重启使所有配置正常运作。

GRANT语法说明：

GRANT privileges (columns)          #privileges表示授予的权限，columns表示作用的列(可选) 
      ON what                       #设置权限级别，全局级、数据库级、数据表级和数据列级
      TO account                    #权限授予的用户，用"user_name"@"host_name"这种用户名、主机名格式
      IDENTIFIED BY 'password'      #设置用户帐号密码
      REQUIRE encryption requirements       #设置经由SSL连接帐号
      WITH grant or resource management options;   #设置帐号的管理和资源(连接服务器次数或查询次数等)选项

示例：

mysql>grant all on db.* to 'test'@'localhost' identified by 'test';
上例运行后的效果是，test用户只能通过‘test’密码从本机访问db数据库

mysql>grant all on db.* to 'test'@'%' identified by 'test';
上例运行后的效果是，test用户可通过‘test’密码从任意计算机上访问db数据库。‘%’代表任意字符，‘_’代表一个任意字符。主机名部份还可以是IP地址。

	如果没有给定主机部份，则默认为任意主机，也就是'test'和'test'@'%'是等价的。