12.为表的字段创建索引
数据库索引就象书的目录一样，如果在字段上建立了索引，那么以索引列为查询条件时 可以加快查询数据的速度 查询数据库，按主键查询是最快的，每个表只能有一个主键列，但是可以有多个普通索 引列，主键列要求列的所有内容必须唯一，而普通索引列不要求内容必须唯一 主键就类似我们在学校学习时的学号一样，班级里是唯一的，整个表的每一条记录的 键值在表内都是唯一的，用来唯一标识一条记录。
12.1 为表创建主键索引的方法
mysql> use oldboy Database changed mysql> create table student( -> id int(4) not null AUTO_INCREMENT, -> name char(20) not null, -> age tinyint(2) not null default '0', -> dept varchar(16) default null, ->primary key(id), ->KEY index_name(name) ->);
提示：
primary key(id) <-主键
KEY index_name(name) <-name 字段普通索引
只有 int 类型且为 primary key 才可以使用 auto_increment
12.2 查看 student 表的结构
mysql> show create table student\G; *************************** 1. row *************************** Table: student Create Table: CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified mysql> describe student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | MUL | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
12.3 怎么删除一个表的主键
mysql> alter table student drop primary key;
提示:如果一个表中的 primary key 设置了 AUTO_INCREMENT（自动增加）的话，就删不掉
12.4 利用 alter 命令修改 id 列为自增主键列
mysql> alter table student change id id int primary key auto_increment;
12.5 建表后利用 alter 增加普通索引
删除建表时创建的 index_name 索引 mysql> select database(); +------------+ | database() +------------+ | oldboy | +------------+ 1 row in set (0.00 sec) mysql> show create table student; +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- --------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- --------------------------------+ | student | CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- --------------------------------+ 1 row in set (0.00 sec) 删除普通索引 mysql> alter table student drop index index_name; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table student; +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ---+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ---+ | student | CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ---+ 1 row in set (0.00 sec) 创建普通索引 mysql> use oldboy Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> alter table student add index index_name (name); Query OK, 0 rows affected (0.47 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table student; +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- --------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- --------------------------------+ | student | CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) 提示:访问数据量很大的时候，不适合建立普通索引，会影响 用户访问，尽量选择业务低估时建立索引 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- --------------------------------+ 1 row in set (0.00 sec)
12.6 对表字段的前 n 个字符创建普通索引
当遇到表中比较大的列时，列内容的前 n 个字符在所有内容中已接近唯一时，这时可以对列的前 n 个字符建立索引，而无需对整个列建立本索引，这样可以节省创建索引占用的系统空间，以及降低读取和更新维护索引消耗的系统资源。
对字段前 n 个字符创建普通索引的语法：
create index index_name on student(name(8));条件列前 N 个字符创建索引
下面来实战演示：
mysql> select database(); +------------+ | database() | +------------+ | oldboy | +------------+ 1 row in set (0.00 sec) 为 dept 列前八个字符创建普通索引 mysql> describe student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | MUL | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> create index index_name_dept on student (dept(8)); Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | MUL | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | MUL | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> show create table student; +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------+ | student | CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`), KEY `index_name_dept` (`dept`(8)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------+ 1 row in set (0.00 sec)
另外一种建立表后创建普通索引的方法：
mysql> alter table student add index index_name_dept (dept(8));
12.7 为表的多个字段创建联合索引
如何查询数据的条件时多列时，我们可以为多个查询的列创建联合索引，甚至，可以为多列的前 n 个字符列创建联合索引，实战演示如下:
mysql> create index index_name_and_dept on student (name,dept); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table student; +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------+ | student | CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`), KEY `index_name_dept` (`dept`(8)), KEY `index_name_and_dept` (`name`,`dept`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
12.8 为表的多个字段的前 n 个字符创建联合索引
mysql> create index index_name_and_dept on student (name(10),dept(10)); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table student; +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ----+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ----+ | student | CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`), KEY `index_name_dept` (`dept`(8)), KEY `index_name_and_dept` (`name`(10),`dept`(10)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ----+ 1 row in set (0.00 sec)
提示:按条件列查询数据时，联合索引是有前缀生效特性的
index(a,b,c)仅 a,ab,abc 三个查询条件列可以走索引，b,bc,ac,c 等无法使用索引了
尽量把最常用作为查询条件的列，放在第一位置
12.9 主键也可以联合多列做索引
*************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '', `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, `max_questions` int(11) unsigned NOT NULL DEFAULT '0', `max_updates` int(11) unsigned NOT NULL DEFAULT '0', `max_connections` int(11) unsigned NOT NULL DEFAULT '0', `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0', `plugin` char(64) COLLATE utf8_bin DEFAULT '', `authentication_string` text COLLATE utf8_bin, PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' 1 row in set (0.00 sec) ERROR: No query specified
12.10 统计一个字段列的唯一值个数
-
mysql> select user from mysql.user; +-----------+ | user | +-----------+ | root | | blog | | oldgirl | | wordpress | | oldgirl | | oldboy | | oldgirl | | root | +-----------+ 8 rows in set (0.00 sec) mysql> select count(distinct user) from mysql.user; +----------------------+ | count(distinct user) | +----------------------+ | 5 | +----------------------+ 1 row in set (0.06 sec) 提示：尽量在唯一值多的大表上建立索引
12.11 创建唯一索引（非主键）
mysql> show create table student ; +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- --------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- --------------------------------------+ | student | CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_age` (`age`), KEY `index_name` (`name`), KEY `index_name_dept` (`dept`(8)), KEY `index_name_and_dept` (`name`(10),`dept`(10)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+---------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec)
12.12 索引列的创建及生效条件
问题一：既然索引可以加快查询速度，那么就给所有的列建索引吧？
解答:因为索引不但占用系统空间，而且更新数据时还需要维护索引数据的，因此，索引是一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建立索引，更新频繁，读取比较少的表要少建立索引
问题二：需要在哪些列上创建索引了?
解答：select user,host from mysql.user where password=...,索引一定要创建在 where 后的条件列上，而不是 select 后的选择数据的列上，另外，我们要尽量选择在唯一值多的大表上的列建立索引，例如:男女性别列唯一值，不适合建立索引
