MySQL索引底层实现原理
索引的本质
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。
索引的创建
CREATE INDEX indexName ON mytable(username(length));
查看索引
show index from tableName;
删除索引
DROP index indexName on tableName
##唯一索引 唯一索引与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
CREATE unique index indexName on tableName(username(length));
测试样例:
mysql> show index from test2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test2 | 1 | myIndex | 1 | name | A | 0 | 10 | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set
阅读次数: 本文累计被阅读 1000000 次