(1) 准备环境
- mysql> create table t1(id int,name varchar(50));
- mysql> \d $$
- mysql> create procedure autoinsert_t1()
- begin
- declare i int default 1;
- while(i<200000)do
- insert into school.t1 values(i,'ccc');
- set i=i+1
- ;
- end while;
- END $$
- mysql> \d ;
- mysql> show create procedure autoinsert_t1\G
- mysql> call autoinsert_t1();
(2) 创建索引
1) 创建表时创建索引
创建普通索引: mysql> create table departname(id int,name varchar(50),comment varchar(50),index(name));
创建唯一索引: mysql> create table department2(id int,name varchar(50),comment varchar(50),unique index(name));
创建全文索引: mysql> create table department3(id int,name varchar(50),log text,fulltext index(log)); \ 针对文章给文章内容起索引
创建多列索引: mysql> create table department5(id int,name varchar(50),comment varchar(50),index index_name_comment(name,comment));
\ 创建多列索引建议给索引起个名字
2) 在已存在表上创建索引: create
语法: create [unique|fulltext|spatial] index 索引名 on 表名 (字段 [(长度)] [ASC|DESC])
创建普通索引: mysql> create index index_name on department(name); \ 索引关键字 MUL
创建唯一索引: mysql> create unique index index_id on department(id);
创建全文索引: mysql> create fulltext index index_name on department(name);
创建多列索引: mysql> create index index_name_comment on department(name,comment);
3) 在已存在表上创建索引: alter table
语法: alter table 表名 add [unique|fulltext|spatial] index 索引名 (字段 [(长度)] [ASC|DESC])
创建普通索引: alter table department add index index_name(name);
创建唯一索引: alter table department add unique index index_name(name);
创建全文索引: alter table department add fulltext index index_name(name);
创建多列索引: alter table department add index index_name_comment(name,comment);
(3) 管理索引
1) 查看索引
语法: show create table 表名 \ G
2) 测试索引
语法: explain select * from 表名 where 条件
3) 删除索引
语法: drop index 索引名 on 表名;
mysql> drop index index_name on department6;
注意: 先使用 show create table 表名 \ G 查看索引名字, 例如 KEY index_name (name) index_name 就是索引名字
(4) 测试索引:
未使用索引之前: 注意 key 和 rows
- mysql> explain select * from t1 where id=190000;
- +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
- | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 200186 | 10.00 | Using where |
- +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
创建索引之后:
- mysql> explain select * from t1 where id=190000;
- +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
- | 1 | SIMPLE | t1 | NULL | ref | index_id | index_id | 5 | const | 1 | 100.00 | NULL |
- +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
- 1 row in set, 1 warning (0.00 sec)
使用索引: 查询语句使用 where 条件, 条件使用的列必须是索引
来源: http://www.bubuko.com/infodetail-2571803.html