1.创建表时,创建普通索引
create table 表名 属性名 数据类型,属性名 数据类型 ,index|key [索引名] (属性名 (长度)ASC | DESC)
for example; create table t_dept(
deptno int,
dname varchar(20),
loc varchar(30),
index index_deptno(deptno)
);
desc 表名 查看表结构
show create table 表名 \G 查看具体建表语句
explain select * from 表名 查询索引 --------------------------------------
-------------------------------------
2.在已经存在的表上创建普通索引
create index 索引名 on 表名 (属性名 (长度) ASC|DESC)
for example : create index index_deptno on t_dept(deptno);
mysql> create index index_dept on student(dept(8));
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| 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 | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
-----------------------------------------------------------
3.通过sql语句alter table 创建普通索引
alter table 表名 add index|key 索引名(属性名)
mysql> alter table student add index index_name(name);
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| 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.删除普通索引
mysql > alter table student drop index index_name; //查看索引帮助 help alter table
mysql > drop index ind_name_dept on student;
5.查看表中索引
mysql> show index from student \G;
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
6.创建联合索引
mysql >create index ind_name_dept on student(name,dept);
mysql > create index ind_name_dept on student(name(8),dept(10)); //对name列的前八个字符,对dept列的前10个字符创建索引。
*************************** 3. row ***************************
Table: student
Non_unique: 1
Key_name: index_dept
Seq_in_index: 1
Column_name: dept
Collation: A
Cardinality: 0
Sub_part: 8
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)
提示:按条件列查询数据时,联合索引是有前缀生效特性的。b,bc,ac,c等无法使用索引了。
index(a,b,c)仅a,ab,abc三个查询条件列可以走索引。