常用的索引分为下面几种: key:普通索引 unique key:唯一索引 primary key:主键索引 full text:全文索引 实例操作： 创建表t8添加索引: mysql> create table t8 ( id int, name char(10), email char(20),key name(name),unique key(email) ); Query OK, 0 rows affected (0.01 sec) mysql> desc t8; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(10) | YES | MUL | NULL | | | email | char(20) | YES | UNI | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 插入数据: mysql> insert into t8 values ("1","lisi","lisi@email.com") -> ; Query OK, 1 row affected (0.01 sec) 查询数据: mysql> select * from t8; +------+------+----------------+ | id | name | email | +------+------+----------------+ | 1 | lisi | lisi@email.com | +------+------+----------------+ 1 row in set (0.00 sec) #unique key唯一性,lisi@email.com邮箱已经存在将不允许相同. mysql> insert into t8 values ("2","lisi","lisi@email.com") -> ; ERROR 1062 (23000): Duplicate entry 'lisi@email.com' for key 'email' #创建表t9，主键key。 mysql> create table t9 ( id int, name char(10), email char(20),primary key (id),key id (name),unique key(email) ); Query OK, 0 rows affected (0.04 sec) mysql> desc t9; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | char(10) | YES | MUL | NULL | | | email | char(20) | YES | UNI | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) ##索引的长度:建索引时，可以只索引列的前一部分的内容,比如前10个字符. 如 key id(email(10)) #索引email长度为10个字符，并不是只允许表中email列插入长度为10个字符,而是说索引引用时只引用emial列前面10个字符. mysql> create table t10 ( id int, name char(10), email char(20),primary key (id),key id (email(10)),unique key(email) ); Query OK, 0 rows affected (0.02 sec) mysql> desc t10; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | char(10) | YES | | NULL | | | email | char(20) | YES | UNI | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) #插入超过10个字符长度的email，发现也是可以的. mysql> insert into t10 values (1,"lisi","sqweqeqeq@email.com"); Query OK, 1 row affected (0.01 sec) 多列索引:把2个或者多列的值，看成一个整体，然后建立索引. mysql> create table t11 ( -> xing char(2), -> ming char(10), -> key xm(xing,ming) -> ); Query OK, 0 rows affected (0.02 sec) mysql> desc t11; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | xing | char(2) | YES | MUL | NULL | | | ming | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into t11 values ("李","世民"); Query OK, 1 row affected (0.00 sec) mysql> select * from t11; +------+--------+ | xing | ming | +------+--------+ | 李 | 世民 | +------+--------+ 1 row in set (0.00 sec) mysql> show index from t11 \G; *************************** 1. row *************************** Table: t11 Non_unique: 1 Key_name: xm Seq_in_index: 1 Column_name: xing Collation: A Cardinality: 1 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: t11 Non_unique: 1 Key_name: xm Seq_in_index: 2 Column_name: ming Collation: A Cardinality: 1 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec) ERROR: No query specified #多列索引的查询。 mysql> select * from t11 where xing="李" and ming="世民"; +------+--------+ | xing | ming | +------+--------+ | 李 | 世民 | +------+--------+ 1 row in set (0.01 sec) 查看查询所能使用的索引：explain mysql> explain select * from t11 where xing="李" and ming="世民"; +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | 1 | SIMPLE | t11 | ref | xm | xm | 38 | const,const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select * from t11 where xing="李"; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | t11 | ref | xm | xm | 7 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select * from t11 where ming="世民"; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | t11 | index | NULL | xm | 38 | NULL | 1 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select * from t11 where ming="世民" and xing="李"; +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | 1 | SIMPLE | t11 | ref | xm | xm | 38 | const,const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ 1 row in set (0.00 sec) 冗余索引:在某个列上，可能存在多个索引.比如xm(xing,ming) m(ming).目的在于加快查询速度. mysql> create table t12 ( xing char(2), ming char(10), key xm(xing,ming),key m(ming) ); Query OK, 0 rows affected (0.02 sec) mysql> show index from t12; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t12 | 1 | xm | 1 | xing | A | 0 | NULL | NULL | YES | BTREE | | | | t12 | 1 | xm | 2 | ming | A | 0 | NULL | NULL | YES | BTREE | | | | t12 | 1 | m | 1 | ming | A | 0 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) mysql> show index from t12 \G; *************************** 1. row *************************** Table: t12 Non_unique: 1 Key_name: xm Seq_in_index: 1 Column_name: xing Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: t12 Non_unique: 1 Key_name: xm Seq_in_index: 2 Column_name: ming Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: t12 Non_unique: 1 Key_name: m Seq_in_index: 1 Column_name: ming Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 3 rows in set (0.00 sec) ERROR: No query specified #删除索引. 语法： alter table 表名 drop index 索引名 drop index 索引名 on 表名 #删除索引m mysql> alter table t12 drop index m; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop index xm on t12; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t12 \G; Empty set (0.00 sec) ERROR: No query specified 添加索引 alter table 表名 add [index/unique] 索引名; mysql> alter table t12 add index xm(xing,ming); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t12 add unique m(ming); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t12; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t12 | CREATE TABLE `t12` ( `xing` char(2) DEFAULT NULL, `ming` char(10) DEFAULT NULL, UNIQUE KEY `m` (`ming`), KEY `xm` (`xing`,`ming`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 添加主键索引 mysql> alter table t12 add primary key(xing); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 删除主键索引 mysql> alter table t12 drop primary key; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
VPS岛 的文章和资源来自互联网，仅作为参考资料，如果有侵犯版权的资源请尽快联系站长，我们会在24h内删除有争议的资源。丨 转载请注明mysql 索引的概念和操作！
