mysql基础（2）--索引

1.创建普通索引

mysql> create table book(  bookid int not null,
    -> bookname varchar(255) not null,
    -> year_publication year not null,
    -> index(year_publication)
    -> );
Query OK, 0 rows affected (0.10 sec)

查看索引

mysql> explain select * from book where year_publication=1990\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication
          key: year_publication
      key_len: 1
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

 

2.创建唯一索引

mysql> create table t1(
    -> id int not null,
    -> name char(30) not null,
    -> unique index (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

 

3.创建单列索引

mysql> create table t2(
    -> id int not null,
    -> name char(30) not null,
    -> index SingleIdx(name(20))
    -> );
Query OK, 0 rows affected (0.01 sec)

4.创建组合索引

index multiIdx(id,name);

 

5.创建全文索引

fulltext index fullTexIdx(info);

 

6.添加索引

mysql> create index bknameidx on book(bookname); 
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0