# mysql索引
---
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

- 索引分单列索引和组合索引。
- 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
- 组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在SQL查询语句的条件(一般作为WHERE子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

1. **MySQL 索引的类型**
| 类型 | 解释 |
| -------- | -------- |
| 普通索引 | 这是最基本的索引，它没有任何限制。 |
| 唯一索引 | 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 |
| 主键索引 | 它是一种特殊的唯一索引，不允许有空值。 |
| 组合索引 | MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。 |

2. **什么时候需要建立数据库索引呢？**

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引（因为在以通配符%和_开头作查询时，MySQL不会使用索引）。

数据库建立索引常用的规则如下：

- 表的主键、外键必须有索引；
- 数据量超过300的表应该有索引；
- 经常与其他表进行连接的表，在连接字段上应该建立索引；
- 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
- 索引应该建在选择性高的字段上；
- 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
- 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
A. 正确选择复合索引中的主列字段，一般是选择性较好的字段；
B. 复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询 是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C. 如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字 段索引；
D. 如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的 字段；
E. 如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索 引；
- 频繁进行数据操作的表，不要建立太多的索引；
- 删除无用的索引，避免对执行计划造成负面影响；
- 小型表肯定不建索引；
- 数据库记录在亿条数据级以上，还是建议使用非关系型数据库；
- 还有些特殊字段的数据库，比如BLOB，CLOB字段肯定也不适合建索引。
- 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整;
- =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式;
- 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录;
- 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
- 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可，当然要考虑原有数据和线上使用情况

3. **建立索引需要注意一些什么呢？**

索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。

因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
- **索引不会包含有NULL值的列**
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
- **使用短索引**
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
- **索引列排序**
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
- **like语句操作**
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
- **不要在列上进行运算**
可能不消息将会导致索引失效而进行全表扫描，因此我们可以改成
- **不使用NOT IN和<>操作**

4. **索引的不足之处**

- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
- 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
- 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

###### tags: `mysql`