【mysql】索引简介

索引是什么

索引是一种数据结构。

优缺点

• 优点
  类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本。
  通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
• 缺点
  实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。
  虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。都会调整因为更新所带来的键值变化后的索引信息。

分类

• 普通索引

ALTER TABLE 'table_name' ADD INDEX index_name('col')

MySQL中最基本的索引类型，允许在定义列中插入重复值和空值。

• 唯一索引

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')

索引列的值必须唯一，但允许有空值。

• 主键索引

ALTER TABLE 'table_name' ADD PRIMARY KEY('col')

一张表的主键也是索引，值必须唯一，而且不允许有空值。

• 复合索引

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')

一个索引包含多个列。

基本语法

• 创建

/*直接添加*/
CREATE [UNIQUE] INDEX indexName ON mytable(username(length)); 
/*修改表结构添加*/
ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName);
/*创建表的时候直接指定*/
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  

• 删除

DROP INDEX [indexName] ON mytable; 

• 查看
  使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

SHOW INDEX FROM table_name; \G

索引结构

MySQL的索引结构可以分为BTree索引、Hash索引、full-text全文索引、R-Tree索引。我们最常用的就是BTree索引。

• Hash索引
  mysql中，只有Memory（Memory表只存在内存中，断电会消失，适用于临时表）存储引擎显示支持Hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B+Tree索引。Hash索引把数据以hash形式组织起来，因此当查找某一条记录的时候，速度非常快。但是因为hash结构，每个键只对应一个值，而且是散列的方式分布。所以它并不支持范围查找和排序等功能。
• BTree索引
  B+Tree是mysql使用最频繁的一个索引数据结构，是Inodb和Myisam存储引擎模式的索引类型。相对Hash索引，B+Tree在查找单条记录的速度比不上Hash索引，但是因为更适合排序等操作，所以它更受欢迎。毕竟不可能只对数据库进行单条记录的操作。

创建索引的情况

①主键自动建立唯一索引

②频繁作为查询条件的字段应该创建索引

③查询中与其他表关联的字段，外键关系建立索引

④WHERE条件里用不到的字段不创建索引

⑤单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)

⑥查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度

⑦查询中统计或者分组字段

不需要创建索引的情况

①表记录太少

②经常增删改的表,频繁更新的字段

提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

③数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。