MySQL----索引

为什么要使用索引

当表中的数据量到达几十万甚至上百万的时候，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。但是索引也是需要存储成索引文件的，因此对索引的使用也会涉及磁盘I/0操作。如果索引创建过多，使用不当，会造成SQL查询时，进行大量无用的磁盘I/O操作，降低了SQL的查询效率，适得其反，所以要掌握良好的索引创建原则。

索引对于良好的性能非常关键。数据量越大时，索引对性能的影响也越重要，好的索引可以将查询性能提高几个数量级。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但是在数据量逐渐增大时，糟糕的索引会使MySQL的性能急剧的下降。

索引分类

索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结果。索引的核心是提高查询的速度!

从物理上（聚集&非聚集）

从逻辑上

普通索引

没有任何限制条件，可以给任何类型的字段创建普通索引，可以给（创建新表&已创建表），数量是不限的（但是不同的存储引擎对索引的数量也有限制），一张表的一次查询只能使用一个索引，具体指的是where后面的字段；

唯一性索引

使用UNIQUE修饰的字段，值不能够重复，主键索引就隶属于唯一性索引；

CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• CREATE UNIQUE INDEX: 用于创建唯一索引的关键字组合。
• index_name: 指定要创建的唯一索引的名称。索引名称在表中必须是唯一的。
• table_name: 指定要在哪个表上创建唯一索引。
• (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

3. 主键索引：使用Primary Key修饰的字段会自动创建索引(MyISAM不会自动创建， InnoDB会自动创建)；
   ALTER TABLE table_name ADD PRIMARY KEY (column);

4. 单列索引:在一个字段上创建索引；

5. 多列索引:在表的多个字段上创建索引（uid+cid，多列索引必须使用到第一个列，才能用到多列索引，否则索引用不上)；

6. 全文索引:使用FULLTEXT参数可以设置全文索引，只支持CHAR，VARCHAR和TEXT类型的字段上，常用于数据量较大的字符串类型上，可以提高查询速度（线上项目支持专门的搜索功能，给后台服务器增加专门的搜索引擎支持快速高校的搜索 elasticsearch 简称es）。
   ALTER TABLE table_name ADD FULLTEXT (column);

索引的优缺点

优点: 提高查询效率
缺点: 索引并非越多越好，使用索引需要使用磁盘I/O，并且索引也是一种数据结构，对某一列建立索引，mysql会对这一列进行排序的，当索引过多时，我们在增加或者删除元素时，不仅仅会涉及到数据的改动还会造成索引文件的改动。所以过多的索引会导致CPU使用率居高不下，由于数据的改变，会造成索引文件的改动，过多的磁盘I/0造成CPU负荷太重。

创建索引时，需要注意的点

• 尽量确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

• 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

• 索引虽然能够提高查询性能，索引需要占用额外的存储空间。对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能。过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引。

什么时候要使用索引？

• 主键自动建立唯一索引；
• 经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
• 作为排序的列要建立索引；
• 查询中与其他表关联的字段，外键关系建立索引
• 高并发条件下倾向组合索引；
• 用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

什么时候不要使用索引？

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

索引失效的情况：

• 在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
• 在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
• LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
• 在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
• 在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。（经erwkjrfhjwkdb同学提醒，不等于，包括<符号、>符号和！，如果占总记录的比例很小的话，也不会失效）
• 在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
• 字符串不加单引号会导致索引失效。即类型涉及到类型强转的时候。
• 在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
• 如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。
• 尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；