索引：是存储引擎用于快速查找记录的一种数据结构

缺点：

如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果，例如性别，学历等等
会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件
建立索引会占用磁盘空间的索引文件，空间不值钱，放心整
对于非常小（多小？？？）的表，大部分情况下简单的全表扫描更高效

索引类型

普通索引

这是最基本的索引，它没有任何限制，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。

唯一索引

普通索引允许被索引的数据列包含重复的值。而唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引

它是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。

与之类似的，外键索引（如果不是必要，最好不用，外键个数也不能太多）

如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

联合索引 - 最左前缀

建表的时候，name 为 varchar(20)，建立索引时可以指定为 10 ，因为大部分 name 都是 10 以内，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度 `ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

A、B、C 建立联合索引，其实相当于建立了如下索引：A，B，C、A，B、A

这两条查询会用到索引：

SELECT * FROM users WHREE A = "admin" AND B = "shuxiaoyuan"
SELECT * FROM users WHREE A = "admin"

这两个查询不会用到索引：

SELECT * FROM users WHREE C = 20 AND B = "shuxiaoyuan"
SELECT * FROM users WHREE B = "shuxiaoyuan"

住：为什么不将他们三个分别建立一个索引呢？虽然你建立了三个，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引，这样效率会远远低于我们的组合索引

全文索引

全文索引是MyISAM的一个特殊索引类型，它查找的是文本中的关键词，主要用于全文检索。

空间(R-Tree)索引

MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。

Hash索引

哈希索引基于哈希表实现，只有精确索引所有列的查询才有效。

建立索引的时机

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL的 B-Tree 只对 <，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE才会使用索引。

例如：SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州';

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

索引的正确使用和设计

参考链接：浅谈MySQL的B树索引与索引优化

优先使用自增key作为主键

自增一般是 int 等整数型，key比较紧凑，这样m可以非常大，而且索引占用空间小。最极端的例子，如果使用50B的varchar（包括长度），那么m = 4 * 1024 / 54m = 75.85 ~= 76，深度最大 log(76/2)(10^7) = 4.43 ~= 5，再加上cache缺失、字符串比较的成本，时间成本增加较大。同时，key由4B增长到50B，整棵索引树的空间占用增长也是极为恐怖的（如果二级索引使用主键定位数据行，则空间增长更加严重）。

自增的性质使得新数据行的插入请求必然落到索引树的最右侧，发生节点分裂的频率较低，理想情况下，索引树可以达到“满”的状态。索引树满，一方面层高更低，一方面删除节点时发生节点合并的频率也较低。

尽量使用数字型（简单的数据类型）

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了

尽量不要让字段的默认值为NULL

在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。索引不会包含有NULL值的列，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时尽量不要让字段的默认值为NULL，应该指定列为NOT NULL，除非你想存储NULL。你应该用0、一个特殊的值或者一个空串代替空值。

前缀索引和索引选择性

对串列进行索引，如果可能应该指定一个前缀长度。

使用唯一索引

考虑某列中值的分布。索引的列的基数越大，索引的效果越好。例如对性别设置索引就没有意义

使用组合索引代替多个列索引

一个多列索引（组合索引）与多个列索引MySQL在解析执行上是不一样的（上文有说），如果在 explain 中看到有索引合并（即MySQL为多个列索引合并优化），应该好好检查一下查询的表和结构是不是已经最优。

注意重复/冗余的索引、不使用的索引

MySQL允许在相同的列上创建多个索引，无论是有意还是无意的。大多数情况下不需要使用冗余索引。

大文本搜索，尽量使用全文索引

如果对大的文本进行搜索，使用全文索引而不要用使用 like ' %…% '

能扩展就不要新建索引

如果已有索引(a, b)，则不需要再建立索引(a)，但是如果有必要，则仍然需考虑建立索引(b)。

like语句不要以通配符开头，会放弃索引

不要在列上进行运算

# 无法使用索引
select actor_id from sakila.actor where actor_id+1=5;
select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10;

尽量不要使用 `NOT IN` `<>` `!=`操作

应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描，对于 not in，可以用not exists或者（外联结+判断为空）来代替；很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换： select num from a where exists(select 1 from b where num=a.num) 对于<>，用其它相同功能的操作运算代替，如a<>0改为 a>0 or a<0

选择区分度搞的列作索引

如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。当索引列有大量数据重复时,查询可能不会去利用索引，如一表中有字段sex，male、female 几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

这个好理解，比如性别，只有男女，这个就不适合建立索引

区分度的公式是 count(distinct 索引字段) / count(*)，表示字段不重复的比例，比例越大区分度越好。唯一键的区分度是1，而一些状态、性别字段可能在大数据面前的区分度趋近于0。

这个值很难确定，一般需要join的字段要求是0.1以上，即平均1条扫描10条记录。

or条件

用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到，应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：假设num1有索引，num2没有索引，查询语句 select id from t where num1=10 or num2=20 会 放弃使用索引，可以改为这样查询： select id from t where num1=10 union all select id from t where num2=20，这样虽然 num2 没有使用索引，但至少 num1 会使用索引，提高效率

`最左前缀` 原则

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是最好的。

使用索引排序时，ORDER BY也要遵守“最左前缀”原则

避免取所有字段 *

任何地方都不要使用 select * from table，用具体的字段列表代替 *，不要返回用不到的任何字段

如果列类型是字符串

那么一定记得在 where 条件中把字符常量值用引号引起来，否则的话即便这个列上有索引，MySQL 也不会用到的，因为MySQL 默认把输入的常量值进行转换以后才进行检索。例如：

实现原理

哈希索引

B 树索引

B+ 树索引

物理存储

聚簇索引

聚簇索引的主键索引的叶子结点存储的是键值对应的数据本身；辅助索引的叶子结点存储的是键值对应的数据的主键键值。

引用百度百科：聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。

主键索引存储方式：
对应的辅助索引为：（键名称，大概的意思）：

主键索引和辅助索引检索过程

非聚簇索引

非聚簇索引的主键索引和辅助索引几乎是一样的，只是主索引重复重复，替换空值，他们的叶子结点都存储指向键值对应的数据的物理地址。

百度百科：非聚簇索引，索引的一种。索引分为聚簇索引和非聚簇索引两种。建立索引的目的是加快对表中记录的查找或排序。索引顺序与数据物理排列顺序无关。非聚簇索引，叶级页指向表中的记录，记录的物理顺序与逻辑顺序没有必然的联系。非聚簇索引则更像书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。

首先，主键索引和辅助索引的叶子结点都存储着键值对应的数据的物理地址，这说明无论是主键索引还是辅助索引都能够通过直接获得数据，而不需要像聚簇索引那样在检索辅助索引时还可以绕过一圈。

同时还说明一个点，叶子结点存储的是物理地址，那么表示数据实际上是存在另一个地方的，并不是存储在B +树的结点中。这说明非聚簇索引的数据表和索引表是分开存储的。

非聚簇索引的检索过程

索引性能测试与索引优化

只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快——不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了 MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

EXPLAIN

在不确定应该在哪些数据列上创建索引的时候，人们从EXPLAIN SELECT 命令那里往往可以获得一些帮助。这其实只是简单地给一条普通的 SELECT 命令加一个 EXPLAIN 关键字作为前缀而已。有了这个关键字，MySQL 将不是去执行那条SELECT 命令，而是去对它进行分析。MySQL 将以表格的形式把查询的执行过程和用到的索引(如果有的话)等信息列出来。

查看索引使用情况：

如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描， Handler_read_rnd_next 的值较高，则通常说明表索引不正确或写入的查询没有利用索引。

索引