MySQL数据库应用（7）表索引

一、为表的字段创建索引

　　　　索引就像书的目录一样，如果在字段上建立了索引，那么以索引列为查询条件时可以加快查询速度，这是mysql优化的重要内容之一，后面课程会详细讲到

　　　　1、创建主键索引

　　　　　　　　查询数据库，按主键查询时最快的，每个表只能有一个主键列，但是可以有多个普通索引列。主键列要求列的所有内容必须唯一，而索引列不要求内容必须唯一。、

　　　　　　　　主键就类似我们在学校学习时的学号一样，班级内是唯一的，整个表的每一条记录的主键值在表内都是唯一的，用来唯一标识一条记录。那么，我们该如何建立主键索引和普通索引呢？

　　　　　　　　首先，无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列创建索引，也可以对多了创建索引

　　　　　　　　建立主键索引的方法：

　　　　　　　　1）在建表时，可以增加建立主键索引的语句如下

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)    
  );
　　提示：
　　1、primary key(id)    #主键
　　2、key index_name(name)　　#字段普通索引
　优化：在唯一值多的裂伤建索引查询效率高。

　　　　　　　　建表时附带建立主键语句操作演示：

mysql> drop table student;
Query OK, 0 rows affected (1.47 sec)
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)    
    ->   );
Query OK, 0 rows affected (0.24 sec)

mysql> desc 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)

 　　　　　　　　利用alter命令修改id列为自增主键列

    alter table student change id id int primary ket auto_increment;

 　　　　2、创建普通索引

　　　　　　　　1）在建表时，可以增加普通索引的语句如下：

　　　　　　　　2）建表后利用alter增加普通索引

　　　　　　　　删除建表时创建的index_name索引

mysql> alter table student drop index index_name;
Query OK, 0 rows affected (1.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

　　　　　　　　在name列上添加索引，索引名为index_name，

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

mysql> desc 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)

　　　　

　　　　3、对字段的前N个字符创建普通索引

 　　　　　　　　当遇到表中比较大的列时，列内容的前n个字符在所有内容中已经接近唯一

　　　　　　　　操作实践：

　　　　　　　　在dept系别列上，前8个字符创建索引，此列总共长度为16个。

mysql> create index index_dept on student(dept(8));
Query OK, 0 rows affected (6.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc 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)

　　　　

　　　　4、为表的多个字段创建联合索引

　　　　　　　　如果查询数据的条件是多列时，我们可以为多个查询的列创建联合索引，甚至，可以为多列的前 n个字符创建联合索引，实践演示如下：

mysql> create index ind_name_dept on student(name,dept);
Query OK, 0 rows affected (2.79 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from studentG

省略。。。

*************************** 5. row ***************************
        Table: student
   Non_unique: 1
     Key_name: ind_name_dept
 Seq_in_index: 2
  Column_name: dept
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
5 rows in set (0.00 sec)

　　　　　　　　为多列的前N个字符创建联合索引

mysql> drop index ind_name_dept on student;
Query OK, 0 rows affected (0.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index ind_name_dept on student(name(8),dept(10));
Query OK, 0 rows affected (5.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

提示：按条件列查询数据时，联合索引是有前缀生效特性的。

index(a,b,c) 仅a,ab,abc三个查询条件列可以走索引。b,bc,ac,c等无法使用索引

 　　　　

　　　　5、创建唯一索引（非主键）

mysql> desc 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> drop index index_name on student;
Query OK, 0 rows affected (1.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create unique index uni_inde_name on student(name);
Query OK, 0 rows affected (1.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

　　　　

　　　　6、索引列的创建及生效条件

　　　　　　　　问题1、既然索引可以加快查询速度，那么久给所有的列建索引吧？（和书的目录一样）

　　　　　　　　解答：因为索引不但占用系统空间，更新数据库时还需要维护索引数据的，因此，索引是一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建立索引，更新频繁，读取比较少的表要少建立索引。

　　　　　　　　问题2、需要在哪些列上创建索引呢？

　　　　　　　　解答：select user,host from mysql.user where host=...,索引一定要创建在where后的条件列上，而不是select后的选择数据的列。另外我们要尽量选择在唯一值多的大表上建立索引。

　　　　7、创建索引命令集合小结

创建主键索引：
alter table student change id id int primary key auto_increment;
删除主键索引：
alter table student drop primary key;
创建普通索引：
alter table student add index index_dept(dept);
根据列的前n个字符创建索引
create index index_dept on student(dept(8));
根据多个列创建联合索引
creat index ind_name_dept on student(name,dept);
根据多个列的前n个字符创建联合索引
create index ind_name_dept on student (name(8),dept(10))

创建唯一索引：
creat unique index uni_ind_name on student(name);

删除普通索引：
alter table student drop index_dept;
drop index index_dept on student;　　　　　　　

　　　　基本索引条件：

　　　　1、要在表的列上创建索引。

　　　　2、索引会加快查询素服，但是会影响更新的速度，因为要维护索引。

　　　　3、索引不是越多越好，要在频繁查询的where后的条件列上创建索引。

　　　　4、小表或唯一值极少的列上不建立索引。要在大表以及不同内容多的列上创建索引。