MySQL索引个人总结

1. 索引

索引一般以文件形式存在磁盘中（也可以存于内存中），存储的索引的原理大致概括为以空间换时间，数据库在未添加索引的时候进行查询默认的是进行全量搜索，也就是进行全局扫描，有多少条数据就要进行多少次查询，然后找到相匹配的数据就把他放到结果集中，直到全表扫描完。而建立索引之后，会将建立索引的KEY值放在一个n叉树上（BTree）。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表，每次以索引进行条件查询时，会去树上根据key值直接进行搜索。

2. 索引的优点

• 建立索引的列可以保证行的唯一性，生成唯一的rowId
• 建立索引可以有效缩短数据的检索时间
• 建立索引可以加快表与表之间的连接
• 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

3. 索引的缺点

• 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大
• 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）
• 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

3. 如何设计索引

MySQL改善性能最好的方式，就是通过数据库中合理地使用索引，换句话说，索引是提高 MySQL 数据库查询性能的主要手段。在下面的章节中，介绍了索引类型、强制索引、全文索引。

• 单列索引：单列索引是最基本的索引，它没有任何 限制
• 复合索引：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要
• 唯一索引：唯一索引和单列索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许有空值。对于多个字段，唯一索引规定列值的组合必须唯一
• 主键索引：主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。此外， CREATE INDEX 不能创建主键索引，需要使用 ALTER TABLE 代替
• 聚簇索引：聚簇索引是一种数据存储结构，InnoDB在主键的索引的叶子节点中直接保存了数据行，而不是像二级索引那样只是保存了索引列的值和所指向行的主键值。由于这个特性，一个表只能有一个聚簇索引。如果一个表没有定义主键也没有定义具有唯一索引的列，那么InnoDB会生成一个隐藏列，并且在此列设为聚簇索引列。

4. 索引使用的注意事项

MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度，在索引的使用过程中，存在一些使用细节和注意事项。

• 不要在列上使用函数，这将导致索引失效而进行全表扫描
• 还有一个建议，不要在列上进行运算，这也将导致索引失效而进行全表扫描。(+，-，*，/，! 等)
• 应该尽量避免在 where 子句中使用 != 或 not in 或 <> 操作符，因为这几个操作符都会导致索引失效而进行全表扫描。
• 应该尽量避免在 where 子句中使用 or 来连接条件，因为这会导致索引失效而进行全表扫描。（or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到）
• MySQL 只能使用一个索引，会从多个索引中选择一个限制最为严格的索引，因此，为多个列创建单列索引，并不能提高 MySQL 的查询性能，为了提高性能，可以使用复合索引。
• 复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。
• 索引覆盖：如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。
• 查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。（Redis 的 SortedSet 有序集合保存时间，或者通过缓存方式缓存查询结果从而提高性能）
• 只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。（在数据库设计时，除非有一个很特别的原因使用 NULL 值，不然尽量不要让字段的默认值为 NULL）
• 当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。（例如数据库字段是字符串，然而匹配的是整数类型，从而发生隐式转换）
• 排序的索引问题：mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引
• 使用短索引：对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
• 在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了 索引也不会使用
• 如果like是以%开始，可以命中索引，如果条件是LIKE ‘%abc’，MYSQL将不使用索引。