一、为表的字段创建索引
索引就像书的目录一样,如果在字段上建立了索引,那么以索引列为查询条件时可以加快查询速度,这是mysql优化的重要内容之一,后面课程会详细讲到
1、创建主键索引
查询数据库,按主键查询时最快的,每个表只能有一个主键列,但是可以有多个普通索引列。主键列要求列的所有内容必须唯一,而索引列不要求内容必须唯一。、
主键就类似我们在学校学习时的学号一样,班级内是唯一的,整个表的每一条记录的主键值在表内都是唯一的,用来唯一标识一条记录。那么,我们该如何建立主键索引和普通索引呢?
首先,无论建立主键索引还是普通索引,都要在表的对应列上创建,可以对单列创建索引,也可以对多了创建索引
建立主键索引的方法:
1)在建表时,可以增加建立主键索引的语句如下
create table student(
idint(4) not nullauto_increment,
namechar(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.47sec)
mysql>create table student(-> id int(4) not nullauto_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.57sec)
Records:0 Duplicates: 0 Warnings: 0mysql>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.94sec)
Records:0 Duplicates: 0 Warnings: 0mysql>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.39sec)
Records:0 Duplicates: 0 Warnings: 0mysql>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.79sec)
Records:0 Duplicates: 0 Warnings: 0mysql> show index fromstudent\G
省略。。。*************************** 5. row ***************************Table: student
Non_unique:1Key_name: ind_name_dept
Seq_in_index:2Column_name: dept
Collation: A
Cardinality:0Sub_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.89sec)
Records:0 Duplicates: 0 Warnings: 0mysql> create index ind_name_dept on student(name(8),dept(10));
Query OK,0 rows affected (5.20sec)
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.00sec)
mysql>drop index index_name on student;
Query OK,0 rows affected (1.04sec)
Records:0 Duplicates: 0 Warnings: 0mysql>create unique index uni_inde_name on student(name);
Query OK,0 rows affected (1.80sec)
Records:0 Duplicates: 0 Warnings: 0mysql>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 idintprimary 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、小表或唯一值极少的列上不建立索引。要在大表以及不同内容多的列上创建索引。