• 欢迎访问VPS岛网站,国外VPS,国内VPS,国外服务器,国内服务器,服务器主机,测评及优惠码,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站 QQ群

mysql 索引的概念和操作

mysql技术 青衫解衣 19次浏览 已收录 0个评论
常用的索引分为下面几种:
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 索引的概念和操作
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址