1. 1. 索引类型
  2. 2. Mysql索引原理
    1. 2.1. b+树介绍
    2. 2.2. 查找过程
    3. 2.3. 性质
  3. 3. Mysql索引类型
  4. 4. 索引优点
  5. 5. 建索引的策略
  6. 6. 查询分析器-explain命令

索引类型

B-Tree索引：mysql引擎默认支持这种索引

MyISAM 使用前缀技术使索引更小
InnoDB 按照原数据格式进行存储

B-Tree索引可以快速访问数据，不再需要进行全表扫描获取需要的数据，从索引的根节点开始进行搜索

Mysql索引原理

b+树介绍


如上图，是一颗b+树。浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含
几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

真实的数据存在于叶子节点，即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中

查找过程

在上图中，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高

性质

(1) 索引字段要尽量的小。

通过上面b+树的查找过程，或者通过真实的数据存在于叶子节点这个事实可知，IO次数取决于b+数的高度h。

假设当前数据表的数据量为N，每个磁盘块的数据项的数量是m，则树高h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；

而m = 磁盘块的大小/数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的；如果数据项占的空间越小，数据项的数量m越多，树的高度h越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。

(2) 索引的最左匹配特性

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

Mysql索引类型

  • 主键索引 PRIMARY KEY

    它是一种特殊的唯一索引，不允许有空值,一般是在建表的时候同时创建主键索引

  • 唯一索引 UNIQUE

    唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以在创建表的时候指定，也可以修改表结构，如：
    ALTER TABLE table_name ADD UNIQUE (column);

  • 普通索引 INDEX

    这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以修改表结构，如：
    ALTER TABLE table_name ADD INDEX index_name (column);

  • 组合索引 INDEX

    组合索引，即一个索引包含多个列。可以在创建表的时候指定，也可以修改表结构，如：
    ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);

  • 全文索引 FULLTEXT

    全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。
    可以在创建表的时候指定，也可以修改表结构，如：
    ALTER TABLE table_name ADD FULLTEXT (column);

  • 查看索引 SHOW INDEX FROM table_name;

  • 索引一旦创建后不能修改，如果需要修改，则需要删除重建，删除索引 DROP INDEX index_name ON table_name;

索引优点

  • 索引大大减少了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机IO变成顺序IO

建索引的策略

  1. 最左前缀匹配原则

    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的顺序可以任意调整

    一般情况，在创建多列索引时，where子句中使用最频繁的一列放在最左边

  2. =和in可以乱序

    比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

  3. 尽量选择区分度高的作为索引

    区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

  4. 索引列不能参与计算，保持列“干净”

  5. 尽量的扩展索引，不要新建索引

    比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

查询分析器-explain命令

EXPLAIN SELECT * from trip_coord WHERE city=”cd”\G;
对应的字段说明：

1
2
3
4
5
6
7
8
9
10
11
- id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
- select_type: SELECT 查询的类型.
- table: 查询的是哪个表
- type: join 类型
- possible_keys: 此次查询中可能选用的索引
- key: 此次查询中确切使用到的索引.
- key_len: 索引长度
- ref: 哪个字段或常数与 key 一起被使用
- rows: 显示此查询一共扫描了多少行. 这个是一个估计值
- filtered: 存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比)
- extra: 额外的信息

其中重要的字段：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
select_type:
- SIMPLE, 表示此查询不包含 UNION 查询或子查询
- PRIMARY, 表示此查询是最外层的查询
- UNION, 表示此查询是 UNION 的第二或随后的查询
- DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
- UNION RESULT, UNION 的结果
- SUBQUERY, 子查询中的第一个 SELECT
- DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
- DERIVED：派生表的 SELECT(FROM 子句的子查询)
 
type:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
从左到右，性能由好到坏
- ALL 全表扫描
- index   索引全扫描
- range   索引范围扫描
- ref 非唯一索引扫描
- eq_ref  唯一索引扫描
- const,system    单表最多有一个匹配行
- NULL    不用扫描表或索引
ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
 
key:MySQL 在当前查询时所真正使用到的索引.
 
key_len:
表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到
ref:
显示索引的哪一列被使用了，如果可能的话，是一个常数
rows:
估算 SQL 要查找到结果集需要扫描读取的数据行数,原则上rows越小越好
filtered:
存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比)
Extra:
执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息
主要包括：
- Using Index   表示索引覆盖，不会回表查询
- Using Where   表示进行了回表查询
- Using Index Condition 表示进行了ICP优化
- Using Flesort  表示MySQL需额外排序操作, 不能通过索引顺序达到排序效果