mysql-索引

索引Index是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：数据机构。

索引的目的在于提高查找效率，可类比字典。（可以理解为排好序的快速查找数据结构）

索引会影响where后面的查找速度和order by后面的排序速度

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些结构以某种方式引用（指针的方式指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

为了加快Col2的查找，可以维护一个二叉树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二分查找一定的算法复杂度内获取到相应的数据，从而快速的检索出符合条件的记录。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件的形式存储在磁盘上

我们平时所说的索引，如果没有特殊说明，都是值B树（多路搜索树，并不一定是二叉的）结构索引。其中聚集索引，次要索引，覆盖索引，复合索引，唯一索引默认使用B+树索引。当然除了B+树这种类型的索引外，还有哈希索引(hash index)等。当然除了B+树这种类型的索引之外，还有哈希索引（hash index）等。

优势

• 提高数据检索的效率，降低了数据库的IO成本
• 降低数据的排序成本，降低了CPU的消耗

劣势

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是占用空间的
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行Insert、Update和Delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引的字段，都会调整因为更新所带来的键值变化后的索引信息。
• 索引只是提高效率的一个因素，如果你的mysql有大数据量的表，就需要花时间建立最优秀的索引

mysql索引分类（一张表索引建议不要超过5个）

单值索引:即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引:索引列的值必须唯一，但允许有空值
复合索引:即一个索引包含多个列

基本语法
创建

CREATE [UNIQUE] INDEX indexName ON mytable(columname(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columname(length));

删除

DROP INDEX [indexName] ON mytable;

查看

SHOW INDEX FROM table_name\G

使用ALTER命令

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

添加一个主键，意味着索引必须是唯一的，且不能为NULL

ALTER TABLE table_name ADD UNIQUE  index_name(column_list)

创建的索引必须是唯一的（除了NULL外，NULL可能出现多次）

ALTER TABLE table_name ADD INDEX index_name(column_list)

添加普通索引，索引值可能出现多次

ALTER TABLE table_name ADD FULLTEXT index_name(column_list)

指定索引为FULLTEXT，用于全文索引

mysql索引结构

BTree索引
Hash索引
full-text全文索引
R-Tree索引

哪些情况创建索引

1主键自动建立唯一索引
2频繁作为查询条件的字段应该创建索引
3查询中与其他表管理的字段，外键建立索引
4高并发下倾向创建组合索引
5查询中排序的字段（按所建立的索引按序排序才会提高排序顺序）
6查询中统计或分组的字段

哪些情况不需要创建索引

1表记录太少（300万记录以上建立建索引）
2频繁更新的字段不适合创建索引（mysql不仅要保存数据还要保存索引文件）
3where条件里用不到的字段
4某个数据列包含许多重复的内容/数据重复且分布平均的表字段（如性别）