跳到主要内容

optimization_index

索引于表记录就好比是目录于书。我们可以根据目录直接找到书中的某一页，也可以根据索引直接找到某一条记录，而不是全表扫描。

前言​

创建在一到多列上创建索引是提高 SELECT 操作的最好的方式。索引就像表中行记录的指针，可以让查询根据 WHERE 子句找到指定的行，并得到这些行的其他的列。所有的数据类型都可以创建索引。 是不是有对所有的列创建索引的冲动？别。不需要的索引会浪费磁盘空间，也会增加MySQL决定使用哪个索引的时间，还会增加插入，更新，删除操作的代价。三思而后行，更方面平衡后使用最佳的索引集合。

MySQL如何使用索引​

要是没有索引，当我们要根据查询条件得到数据就必须遍历整张表，表数据越多，代价越大。使用索引，MySQL可以快速的定位到所操作行的位置，然后将数据给出。 大多数MySQL索引 = {PRIMARY KEY, UNIQUE, INDEX, FULLTEXT}使用B-trees存储，spatial data types 的使用R-trees，MEMORY tables还支持哈希索引。InnoDB使用 inverted lists 来存储 全文索引。

那索引用来执行哪些操作呢？

  • 快速查找匹配 WHERE 子句的行集合
  • 根据条件舍弃行：当有多个索引可供选择，MySQL会使用对应记录条数最少的那个索引
  • 对应多列索引，最左前缀会被使用来查找行记录
  • 执行 join 操作时检索数据：在列有相同的类型和大小时，MySQL可以用索引进行关联。
  • 计算 MIN() | MAX() 的值。要是查找语句中的列都有索引 key_part_n = constant，预处理器可以直接返回一个常量。如： SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;
  • 排序和分组是，最左前缀一般有用。

索引的种类​

主键[Primary Keys]​

主键是最重要的索引。它是关联索引，不能为空。如果表中没有合适的列（集合）用作主键，建议使用一个自增值列作为主键值，这样当用外键关联表时可以用来找对应的行。

外键[Foreign Keys]​

当表中有很多列，而每次查询是却有很多不同的组合，也许将这些列拆分到不同的表效率更高。将那些使用频率低的列单独建表，单独加一自增列做主键，将其放到主表中进行关联。这样还可以提高缓存命中率，因为列越少，记录行数就越多。

单列索引[Column Indexes]​

单列索引是使用的最多的索引，它将列的值的副本存储到某个数据结构，然后实现快速查找相应的行。 不同的存储引擎有不同的最大索引数。

  • 前缀索引：适用于string类型列。只对前n位进行索引（可以不指定），让索引文件更小。比如：CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
  • 全文索引：实现全文检索。只有InnoDB和MyISAM存储引擎支持，只对 char|varchar|text 类型支持。

多列索引[Multiple-Column Indexes]​

一个索引可能最多有16列组成，这个为组合|多列索引。 多列索引匹配是最左前缀匹配。具体的，查询子句中的列序列会依次和创建索引时设置的列集合从左到右匹配，得到最大的列数量。比如创建时列是(c1, c2, c3, c4)，对于（c1，c4）只能匹配到（c1），对于（c1，c2）则是（c1， c2）。

验证索引​

使用 EXPLAIN。

参考文档：MySQL官方文档