mysql索引

---

一、索引类型

1. 普通索引
   最基本的索引类型，基于普通字段建立的索引，没有任何限制。
   创建普通索引：

   • CREATE INDEX <索引的名字> ON tablename (字段名);
   • ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
   • CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名) );

2. 唯一索引
   索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。
   创建唯一索引：

   • CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
   • ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
   • CREATE TABLE tablename ( […], UNIQUE [索引的名字] (字段名) ;

3. 主键索引
   它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。
   创建主键索引:

   • CREATE TABLE tablename ( […], PRIMARY KEY (字段名) );
   • ALTER TABLE tablename ADD PRIMARY KEY (字段名);

4. 复合索引
   以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。
   索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。
   创建组合索引:

   • CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2…);
   • ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2…);
   • CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名1，字段名2…) );

   复合索引使用注意事项：

   • 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
   • 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

5. 全文索引
   查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。
   创建全文索引:

   • CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
   • ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
   • CREATE TABLE tablename ( […], FULLTEXT KEY [索引的名字] (字段名) ;

   全文索引的语法格式，使用 match 和 against 关键字:
   select * from user where match(name) against(‘name1’);

   全文索引使用注意事项：

   • 全文索引必须在字符串、文本字段上建立。
   • 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
   • 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
   • 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*。
     select * from user
     where match(name) against(‘a*’ in boolean mode);

二、索引原理

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

• 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
• 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

MySQL数据库索引采用的是B+Tree结构：

• 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值

• 叶子节点包含了所有的索引值和data数据

• 叶子节点用指针连接，提高区间的访问性能
  

• 聚簇索引
  

• 非聚簇索引
  

三、索引分析与优化

1.EXPLAIN

EXPLAIN 命令的输出内容：

• select_type
  表示查询的类型。常用的值如下:
  • SIMPLE ： 表示查询语句不包含子查询或union
  • PRIMARY：表示此查询是最外层的查询
  • UNION：表示此查询是UNION的第二个或后续的查询
  • EXPLAIN SELECT * from user WHERE id < 3;
  • DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
  • UNION RESULT：UNION的结果
  • SUBQUERY：SELECT子查询语句
  • DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。
• type
  表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强:
  • ALL：表示全表扫描，性能最差。
  • index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
  • range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
  • ref：表示使用非唯一索引进行单值查询。
  • eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
  • const：表示使用主键或唯一索引做等值查询，常量查询。
  • NULL：表示不用访问表，速度最快。
• possible_keys
  表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。
• key
  表示查询时真正使用到的索引，显示的是索引名称。
• rows
  MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。
• key_len
  表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
• Extra
  Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：
  • Using where 表示查询需要通过索引回表查询数据。
  • Using index 表示查询需要通过索引，索引就可以满足所需数据。
  • Using filesort 表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。
  • Using temprorary 查询使用到了临时表，一般出现于去重、分组等操作。