mysql时间戳索引_mysql索引

索引(index)是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定

有这样的一个student 表

mysql> select * from student;

+----+-----+--------+---------------+------+-------+-----------+

| id | age | name | register_date | sex | grade | ManagerId |

+----+-----+--------+---------------+------+-------+-----------+

| 1 | 32 | alex | 2018-10-17 | NULL | 88 | 3 |

| 4 | 34 | clause | 2018-08-22 | NULL | 98 | NULL |

| 5 | 44 | alex | 2018-08-22 | NULL | 68 | NULL |

| 7 | 23 | VVVV | 2018-10-29 | NULL | 80 | NULL |

| 8 | 23 | asd | 2018-10-29 | NULL | 82 | NULL |

| 9 | 44 | alex | 2019-01-02 | NULL | NULL | NULL |

| 18 | 100 | asd | NULL | NULL | NULL | NULL |

| 19 | 22 | alex | NULL | NULL | NULL | NULL |

| 20 | 102 | alex | NULL | NULL | NULL | NULL |

+----+-----+--------+---------------+------+-------+-----------+

9 rows in set (0.00 sec)

为这样的一个表的name加一个索引

create index ind_sd1 on student(name)

查询其中名字为asd的信息

mysql> EXPLAIN select * from student where name='asd';+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+

| 1 | SIMPLE | student | NULL | ref | ind_sd1 | ind_sd1 | 32 | const | 2 | 100.00 | NULL |

+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.01 sec)

当建立索引时，遍历次数为2

取消索引，查询其遍历次数

mysql>drop index ind_sd1 on student;

Query OK,0 rows affected (0.32sec)

Records:0 Duplicates: 0 Warnings: 0mysql> EXPLAIN select * from student where name='asd';+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 12.50 | Using where |

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

可以看出取消索引后，遍历次数为8.

因此加入索引可以大大提高数据库的检索效率！！！

创建索引的代码为：

CREATE INDEX 索引名 ON 表名(列名);

查看索引代码：

SHOW INDEX FROM 表名;

删除索引的代码：

DROP INDEX 索引名 ON 表名;

创建索引报错问题：

对以上的student表格创建唯一索引会报错

mysql>create unique index ind_sd1 on student(name);

ERROR1062 (23000): Duplicate entry 'alex' for key 'ind_sd1'

试试创建双索引：

mysql>create index ind_sd1 on student(name,age);

Query OK,0 rows affected (0.62sec)

Records:0 Duplicates: 0 Warnings: 0

当创建了唯一性索引，插入和该列相同的记录，系统会报错。

如果已有数据重复，创建唯一性索引也会报错

但是，如果创建了一个复合的唯一性索引，则只要两列不完全相同，数据可以成功插入