mysql索引

* 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.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

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       | 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);
ERROR 1062 (23000): Duplicate entry ‘alex‘ for key ‘ind_sd1‘

试试创建双索引：

mysql> create index ind_sd1 on student(name,age);
Query OK, 0 rows affected (0.62 sec)
Records: 0  Duplicates: 0  Warnings: 0

当创建了唯一性索引，插入和该列相同的记录，系统会报错。 
如果已有数据重复，创建唯一性索引也会报错 
但是，如果创建了一个复合的唯一性索引，则只要两列不完全相同，数据可以成功插入

mysql索引

标签：duplicate   sql   查询   register   nbsp   sim   pre   highlight   warnings