MySQL—— 各种索引 索引类型、优点、策略

mysql8.0

**

MySQL——索引

**

索引一般来说都是基于存储引擎的。因此了解索引前首先需要了解存储引擎。

什么是索引

• 键（key）

  一般来说，索引是存储引擎用于快速找到记录的数据结构。这也是索引的基本功能。

• 检索

  类似搜索引擎的关键词搜索。全文索引类型较为常用。

• 查看索引

  ## 查看table_name表的索引
  show index from table_name;
  
  ## 创建表语句（其中主键列和非空列都是索引）
  CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(20) NOT NULL,
    `password` varchar(20) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
  ## 此张表使用了InnoDB存储引擎，并标识主键为id列，并设置其为自增。
  ## 其余两列为非空列。这三列都分别创建了索引。
  

索引的类型

在MySQL中，索引是在存储引擎层，而不是服务层实现的。因此各种索引的底层实现一般是不一样的。

• B-Tree索引

  一般没有指明类型的时候，基本都是说的B-Tree索引。大多数MySQL引擎都支持这种索引，但实际上很多存储引擎使用的是B+Tree。这种索引指的是使用B-Tree或B+Tree数据结构来存储数据。

  针对B-Tree和B+Tree的区别，可以详细了解一下。

  ## 虽然都是B-Tree索引，但各种存储引擎的具体实现是不同的。
  NDB 集群存储引擎内部实际上使用了 B-Tree
  InnoDB 和 MyISAM 都使用的是 B+Tree
  
  
  ## 虽然InnoDB和MyISAM都是B+Tree，但性能也各有不同，各有优劣
  - MyISAM使用前缀压缩技术使索引更小，但InnoDB存储原数据格式
  - MyISAM索引通过数据的物理地址引用被索引的行，InnoDB使用主键引用被索引的行
  
  
  ## B-Tree（B+Tree）索引加快访问数据的速度
  因为存储引擎查找数据时不需要进行全表扫描来获取数据。
  1.从索引的根节点开始搜索，根节点的槽中存放了指向子节点的指针
  2.存储引擎根据这些指针向下层查找
  3.通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点
  

• 哈希索引

  哈希索引（hash index）基于哈希表实现，只有精确匹配到索引的所有列的查询才有效。

  针对哈希表感兴趣的可以了解了解。

  在MySQL中，只有memory引擎显式支持哈希索引。哈希索引也是memory默认索引类型，memory也支持B-Tree索引。Memory引擎是支持非唯一哈希索引的，这个是值得注意的地方。

• 空间数据索引

  MyISAM支持空间索引，可以用作地理数据存储。

  开源关系型数据库对这个做的比较好的是PostgreSQL

• 全文索引

  全文索引是一种特殊的索引，它查找的是文本中的关键字，而不是直接比较索引中的值。

  因为匹配方式不一样，因此需要注意很多细节

  如：停用词、词干和复数、布尔搜索等

• 其他索引类别

  很多第三方存储引擎使用了其他的数据结构来存储索引。

  例如TokuDB使用了分形树索引（fractal tree index）

索引的优点

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

但索引并不总是最好的工具。

只有当索引帮助存储引擎快速的查找到记录带来的好处大于其带来的额外工作。
索引才是有效的。

对于非常小的表，大部分情况全表扫描更高效。

对于中到大型表，索引就非常有效。

对于大型表来说，建立和使用索引的代价就随之增长。可以使用分区技术。

索引策略

如何使用各种索引，如何才能把各种索引的优势发挥出来？

• 独立的列

  首先是索引使用的时候，应对于“独立的列”，而不能是表达式，或者是函数参数。

  
  ## 这个查询就无法使用id列的索引
  select id from users where id + 1 = 5;
  
  

• 前缀索引和索引选择性

  有时候又很长的字符串列，这种会使得索引变得大且慢。

  这个时候可以模拟哈希索引解决

  还可以使用索引开始的部分字符，这样可以节约索引空间。

  
  尤其是对于BLOB、TEXT或者很长的VARCHAR类型时，
  MySQL不会允许索引完这些列的完整长度，必须使用前缀索引。
  
  

  那么对于前缀的长度的选择，就需要进行操作了（保证较高的选择性，同时又不能太长）

  索引选择性：
  	可以参考《高性能MySQL》一书中示例来操作
  	
  	使用MySQL官网上的Sakila数据库的city表生成一个city_demo表来进行操作。
  	# 创建数据表
  	create table sakila.city_demo(city varchar(50) not null);
  	insert into sakila.city_demo(city) select city from sakila.city;
  	
  	# 重复下面一条语句五次
  	insert into sakila.city_demo(city) select city from sakila.city_demo;
  	
  	# 更新城市表名称 
  	update sakila.city_demo set city = (select city from sakila.city order by rand() limit 1);
  	
  	
  	
  	针对这张表，如何建立索引？（表内只有一个city字段）
  	- 对完整列创建索引，但索引会比较长
  	- 取一部分作为索引
  	
  	# 查询出现最频繁的前10个城市
  	select count(*) as cnt, city from sakila.city_demo group by city order by cnt desc limit 10;
  	
  	# 取部分索引，先从城市名称前缀为3个字母开始
  	select count(*) as cnt, left(city , 3) as pref from sakila.city_demo group by pref order by cnt desc limit 10;
  	# 由3逐渐递增，最后找到趋近于完整列的选择性7
  	
  	
  	
  	计算合适的前缀长度比较好的就是计算完整列的选择性
  	# 先计算完整列的选择性
  	select count(distinct city)/ count(*) from sakila.city_demo;
  
  	# 计算不同前缀长度的选择性
  	select count(distinct left(city ,3 ))/ count(*) as sel3,
  	    count(distinct left(city ,4 ))/ count(*) as sel4,
  		count(distinct left(city ,5 ))/ count(*) as sel5,
  		count(distinct left(city ,6 ))/ count(*) as sel6,
  		count(distinct left(city ,7 ))/ count(*) as sel7
  	from sakila.city_demo;
  	
  	
  	# 创建前缀索引
  	alter table sakila.city_demo add key (city(7)); 
  

• 多列索引

  针对多列索引来说，是为每个列创建一个单独的索引还是为多个列创建一个索引？

  如果为多个列创建一个索引的话，那么列顺序又是如何呢？

  针对这点，有了最左匹配、索引覆盖、索引下推及explain的使用。

  下一节对此进行具体剖析。

• 聚簇索引

  聚簇索引并不是单独的索引类型，而是数据存储方式。

  InnoDB存储引擎的聚簇索引实际再同一结构中保存了B-Tree索引和数据行。

  
  聚簇索引：数据行实际上存放在索引的叶子页中。
  	
  	表示数据行和相邻的键值紧凑的存储在一起
  	所以一个表只能有一个聚簇索引
  	索引覆盖另算
  
  InnoDB主键为聚簇索引
  MyISAM主键是非聚簇索引
  
  

上一篇 》MySQL引擎

下一篇 》MySQL各种名词