MySQL 表索引

Linux系统环境

[root@mysql ~]# cat /etc/redhat-release                     #==》系统版本
CentOS release 6.7 (Final)
[root@mysql ~]# uname –r                                    #==》内核版本
2.6.32-573.el6.x86_64
[root@mysql ~]# uname -m                                    #==》系统架构
x86_64
[root@mysql ~]# echo $LANG                                  #==》系统字符集
en_US.UTF-8
[root@mysql ~]# mysql –V                                    #==》MySQL版本
mysql  Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1

什么是索引？

索引就好像书的目录一样，如果在字段上建立了索引，以索引为列查询条件就可以加快查询数据的速度，这是MySQL优化的重要内容之一。

索引优缺点

1、索引可以加快查询速度，但如果修改了索引，更新数据库时还需维护索引数据并且会占用存储空间，索引是一 把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建立索引，更新频繁，读取少表要少建索引。

2、select user,host,from mysq.user where host = …. 索引一定要创建在where后的条件上，而不是select后的选择数据的列。尽量选择在唯一值多的几千行或上万行的表上的列建立索引。

索引分类

    1、普通索引               #==》加速查找

    2、唯一索引
        （1）、主键索引        #==》加速查找+约束列（不为空且唯一）
        （2）、唯一索引        #==》加速查找+约束列（允许为空且唯一）

    3、联合索引
        （1）、联合主键索引
        （2）、联合唯一索引
        （3）、联合普通索引

    4、全文索引               #==》用于搜索大文本时候，效果最好

    5、空间索引

一、建立主键索引的方法

1、在建表时，直接建立主键索引（推荐方法）
提示：PRI为主键的标识，MUL为普通索引标识

mysql> 
#==》auto_increment自动生成列的值
#==》primary key(id),以id字段创建主键   
#==》KEY index_name(name),以name字段创建普通索引
create table student01(
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)
);
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student01        |
+------------------+
1 row in set (0.00 sec)

mysql> desc student01;
+-------+-------------+------+-----+---------+----------------+
| 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> show create table student01\G;
*************************** 1. row ***************************
       Table: student01
Create Table: CREATE TABLE `student01` (
  `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=gbk
1 row in set (0.00 sec)

2、在建表后通过alter命令增加主键索引
提示：主键列不能重复创建，必须先删除原来的主键，如果主键列有AUTO-INCREMENT属性，必须先取消，否则无法执行删除主键操作

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student01        |
+------------------+
1 row in set (0.00 sec)

mysql> desc student01;
+-------+-------------+------+-----+---------+----------------+
| 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> alter table student01 drop primary key;
#==》删除主键报错，说明有AUTO-INCREMENT属性，无法直接删除
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key   
mysql> alter table student01 modify id int;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student01;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI      | 0       |       |
| name | char(20)    | NO   | MUL       | NULL    |       |
| age   | tinyint(2)  | NO    |         | 0       |       |
| dept  | varchar(16) | YES   |             | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table student01 drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student01;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)       | NO    |       | 0       |       |
| name  | char(20)    | NO      | MUL   | NULL    |       |
| age   | tinyint(2)    | NO    |       | 0       |       |
| dept  | varchar(16)   | YES   |       | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table student01 change id id int primary key auto_increment;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student01;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)        | 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> flush privileges;
Query OK, 0 rows affected (0.01 sec)

二、建立普通索引的方法
1、在建表时，直接创建普通索引
提示：在唯一值多的列且大表上建立普通索引查询效率更高快

mysql> 
create table student02(
id int(4) not null,
name char(20) not null,
age tinyint(2) NOT NULL default '0',
dept varchar(16) default NULL,
KEY index_name(name)
);
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student02        |
+------------------+
1 row in set (0.00 sec)

mysql> desc student02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)        | NO    |       | NULL    |       |
| name  | char(20)    | NO    | MUL     | NULL    |       |
| age   | tinyint(2)    | NO    |       | 0       |       |
| dept  | varchar(16)   | YES   |       | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table student02\G;
*************************** 1. row ***************************
       Table: student02
Create Table: CREATE TABLE `student02` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL,
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

ERROR: 
No query specified
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

2、在建表后利用alter增加普通索引
提示：普通索引可以在同一张表中重复创建

mysql> desc student02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | char(20)    | NO   | MUL | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student02 add index index_age(age);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)        | NO    |       | NULL    |       |
| name  | char(20)    | NO      | MUL   | NULL    |       |
| age   | tinyint(2)    | NO    | MUL   | 0       |       |
| dept  | varchar(16)   | YES   |       | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

三、对字段前n个字符创建普通索引
提示：当遇到列表中列的值比较长时，列内容前n个字符所有内容中已经接近唯一时，这时可以对列的前n个字符建立索引，而无需对整个列建立索引，这样可以节省创建索引占用的存储空间，也可以降低读取和更新维护索引消耗的系统资源

mysql> desc student02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)        | NO   |     | NULL    |       |
| name  | char(20)    | NO   |     | NULL    |       |
| age   | tinyint(2)    | NO   |     | 0       |       |
| dept  | varchar(16)   | YES   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student02 add index index_name(name(8));
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)        | NO   |        | NULL    |       |
| name  | char(20)    | NO   | MUL  | NULL    |       |
| age   | tinyint(2)    | NO   |        | 0       |       |
| dept  | varchar(16)    | YES  |       | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show create table student02\G;
*************************** 1. row ***************************
       Table: student02
Create Table: CREATE TABLE `student02` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL,
  KEY `index_name` (`name`(8))
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

ERROR: 
No query specified
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

四、创建唯一索引

mysql> desc student02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)        | NO   |        | NULL    |       |
| name  | char(20)    | NO   | MUL  | NULL    |       |
| age   | tinyint(2)    | NO   |        | 0       |       |
| dept  | varchar(16)   | YES  |            | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student02 add unique index_uniq_age(age);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)        | NO   |        | NULL    |       |
| name  | char(20)    | NO   | MUL      | NULL    |       |
| age   | tinyint(2)    | NO   | PRI        | 0       |       |
| dept  | varchar(16)   | YES  |            | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)