关于mysql索引_关于mysql的索引

我们先来了解MYSQL索引的概念：

索引是一种特殊的文件，他们包含着对数据表里所有记录的引用指针。更通俗地说，数据库索引好比是是一本书前面的目录，能加快数据库的查询速度，我们有了相应的索引后，数据库会直接在索引中查找符合条件的选项。索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了，聚簇索引能提高多行检索的速度，而非聚簇索引对单行的检索很快。

mysql主要有两种结构：B+树和hash

hash索引在mysql中较少用，它以把数据的索引以hash形式组织起来，因此当查找某一条记录的时候，速度非常快。因为是hash结构，每个键只对应一个值，而且是散列的方式分布，所以它并不支持范围查找和排序等功能。

B+树：B+树是mysql使用最频繁的一个索引数据结构，数据结构以平衡树的形式来组织，因为是树形结构，所以更适合用来处理排序，范围查找等功能。相对hash索引，B+树在查找单条索引的速度上虽然比不上hash索引，但是因为更适合排序等操作，所以更受用户的欢迎。

MYSQL索引的类型：普通索引：

这是最基本的索引，它没有任何限制，它有以下几种创建方式：

(1)创建索引：

CREATE INDEX indexName ON mytable(username(length));

(2)修改表结果：

ALTER mytable ADD INDEX [indexName] ON (username(length))

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   );

删除索引：

DROP INDEX index_nameON table

2.唯一索引：

与前面的普通索引类似，不同的是：索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

(1)创建索引：

CREATE UNIQUE INDEX indexname ON mytable(username(length))

(2)修改表结构：

ALTER mytable ADD UNIQUE[indexName] ON(username(length))

(3)创建表的时候直接指定：

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,UNIQUE [indexName](username(length)))

3.主键索引：

这是一种特殊的唯一索引，不允许有空值，一般是在建表的时候，创建主键索引：

CREATE TABLE mytable (ID INT NOT NULL,username VARCHAR(16) NOT NULL,PRIMARY KEY(ID));

这里要注意，一个表只能有一个主键。

4.组合索引：

为了形象对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,city VARCHAR(50) NOT NULL,age INT NOT NULL);

为了进一步榨取mysql的效率，就要考虑建立组合索引，就是将上面的name，city，age建到一个索引里：

ALTER TABLE mytable ADD INDEX name_ciry_age(name(10)，city，age)

我们建表时username长度为16，这里用10，这是因为一般情况下名字的长度不会超过10，这样会加速索引查找速度，还会减少索引文件的大小，提高insert的更新速度。如果分别在username，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会不打一样，远远低于我们的组合索引。

使用索引的注意事项：

1.索引不会包含有NULL值的列：只要列中包含有NULL值，都将不会被包含在mysql索引中，符合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的，所以我们在数据库设计时不要让字段的默认值为NULL。

2.使用短索引：对串列进行索引，如果可能应该指定一个前缀长度。短索引不仅可以提高查询速度，而且可以节省磁盘空间和I/O操作。

3.索引列排序：mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下，不要使用排序操作，尽量不要包含多个列的排序操作，如果需要最好给这些列创建复合索引。

4.不要在列上进行运算，这将导致索引失效而进行全表扫描。

mysql各种索引区别：

普通索引：最基本的索引，没有任何限制；

唯一索引：与普通索引类似，不同的是，索引列的值必须唯一，但允许有空值；

主键索引：它是一种特殊的唯一索引，不允许有空值；

全文索引：仅可用于MYISAM表，针对较大的数据，生成全文索引，很耗时耗空间；

组合索引：为了更高的提高mysql效率可建立组合索引，遵循“最左前缀”原则。