MySQL--06(索引)
数据量很大时，查询慢的问题；
数据量巨大时，索引无效
索引定义：是一个排好序的，便于快速查找的，数据结构。
主键是一种特殊的索引，
index
pid : parent id ->pid
可以在任何字段上创建索引，但不是每个字段都适合做索引；
查看索引
show index from 表名；
创建索引的命令
create index idx_索引名 on 表名（字段名（索引长度））
索引类型
主键、唯一索引、普通索引、联合（复合索引）、全文索引
添加唯一索引命令
alter table 表名 add unique index idx_索引名(要索引的字段（长度）)
ERROR 1062 (23000):** Duplicate** entry '湖南省永州市' for key 'idx_address'
删除索引
drop index 索引名称 on 表名;
MariaDB [books]> drop index idx_address on student; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [books]> show index from student; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | student | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | | | student | 1 | idx_name | 1 | name | A | 6 | 10 | NULL | | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
Key_name：索引名称
MariaDB [books]> show index from student; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | student | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | | | student | 1 | idx_name | 1 | name | A | 6 | 10 | NULL | | BTREE | | | | student | 1 | idx_fuhe | 1 | name | A | 6 | NULL | NULL | | BTREE | | | | student | 1 | idx_fuhe | 2 | address | A | 6 | NULL | NULL | YES | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)