SQL性能优化系列——索引概览[四]

2019年8月12日23:31:19 发表评论 18
摘要

1.索引种类?
2.什么情况需要创建索引?

索引的本质是帮我们快速定位想要查找的数据。

索引是万能的?

索引(Index),好比一本书的目录,可以快速定位与查找特定值,从而加快数据查询效率。

索引就是帮助数据库管理系统高效获取数据的数据结构。

如果不使用索引,就必须从第一条记录开始扫描,直到把所有数据表都扫描完才能找到目标数据。但是创建更多的索引就行了吗?

索引不是万能的,某些情况使用索引反而会降低效率。

索引的价值是帮我们从海量数据中查找目标数据,如果数据量少,使用索引与否效率区别不大。(1000行的数据量就很少,不需要创建索引。)另外,数据重复度大,比如高于10%时,也不需要对该字段使用索引,比如前面举的性别这个例子,就不需要创建索引,因为想要在100万行数据中查找其中的50万行(比如性别为男的数据),一旦创建了索引,就需要先访问50万次索引,然后再访问50万次数据表,这样加起来的开销比不使用索引可能还大。没有数据支撑的空谈都是耍流氓,下面做两个实验:

实验1:数据行数少的情况下,索引效率咋样?

heros_without_index表,数据共69条就不列进来了,下面只是数据结构:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for heros_without_index
-- ----------------------------
DROP TABLE IF EXISTS `heros_without_index`;
CREATE TABLE `heros_without_index`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `hp_max` float NULL DEFAULT NULL,
  `hp_growth` float NULL DEFAULT NULL,
  `hp_start` float NULL DEFAULT NULL,
  `mp_max` float NULL DEFAULT NULL,
  `mp_growth` float NULL DEFAULT NULL,
  `mp_start` float NULL DEFAULT NULL,
  `attack_max` float NULL DEFAULT NULL,
  `attack_growth` float NULL DEFAULT NULL,
  `attack_start` float NULL DEFAULT NULL,
  `defense_max` float NULL DEFAULT NULL,
  `defense_growth` float NULL DEFAULT NULL,
  `defense_start` float NULL DEFAULT NULL,
  `hp_5s_max` float NULL DEFAULT NULL,
  `hp_5s_growth` float NULL DEFAULT NULL,
  `hp_5s_start` float NULL DEFAULT NULL,
  `mp_5s_max` float NULL DEFAULT NULL,
  `mp_5s_growth` float NULL DEFAULT NULL,
  `mp_5s_start` float NULL DEFAULT NULL,
  `attack_speed_max` float NULL DEFAULT NULL,
  `attack_range` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_main` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_assist` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `birthdate` date NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10069 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

对name进行条件查询:

SELECT id, name, hp_max, mp_max FROM heros_without_index WHERE name = '刘禅'

对name字段建立索引后,heros_with_index表,数据完全相同,69条,数据结构如下:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for heros_with_index
-- ----------------------------
DROP TABLE IF EXISTS `heros_with_index`;
CREATE TABLE `heros_with_index`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `hp_max` float NULL DEFAULT NULL,
  `hp_growth` float NULL DEFAULT NULL,
  `hp_start` float NULL DEFAULT NULL,
  `mp_max` float NULL DEFAULT NULL,
  `mp_growth` float NULL DEFAULT NULL,
  `mp_start` float NULL DEFAULT NULL,
  `attack_max` float NULL DEFAULT NULL,
  `attack_growth` float NULL DEFAULT NULL,
  `attack_start` float NULL DEFAULT NULL,
  `defense_max` float NULL DEFAULT NULL,
  `defense_growth` float NULL DEFAULT NULL,
  `defense_start` float NULL DEFAULT NULL,
  `hp_5s_max` float NULL DEFAULT NULL,
  `hp_5s_growth` float NULL DEFAULT NULL,
  `hp_5s_start` float NULL DEFAULT NULL,
  `mp_5s_max` float NULL DEFAULT NULL,
  `mp_5s_growth` float NULL DEFAULT NULL,
  `mp_5s_start` float NULL DEFAULT NULL,
  `attack_speed_max` float NULL DEFAULT NULL,
  `attack_range` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_main` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_assist` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `birthdate` date NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10069 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

对name再查询:

SELECT id, name, hp_max, mp_max FROM heros_with_index WHERE name = '刘禅'

对比两种查询时间:

mysql> use buerguo;
Database changed
mysql> SELECT id, name, hp_max, mp_max FROM heros_without_index WHERE name = '刘禅'
-> ;
+-------+------+--------+--------+
| id | name | hp_max | mp_max |
+-------+------+--------+--------+
| 10015 | 刘禅 | 8581 | 1694 |
+-------+------+--------+--------+
1 row in set (0.00 sec)

mysql> SELECT id, name, hp_max, mp_max FROM heros_with_index WHERE name = '刘禅'
-> ;
+-------+------+--------+--------+
| id | name | hp_max | mp_max |
+-------+------+--------+--------+
| 10015 | 刘禅 | 8581 | 1694 |
+-------+------+--------+--------+
1 row in set (0.00 sec)

mysql>

通过观察,两种查询时间相差无几,装逼失败。。

其实想说的是:理论上数据量少时,创建name字段索引的查询时间要比没有创建索引的查询时间长,也就是说数据量不大的情况下,索引发挥不出作用。

实验2:性别(男或女)字段真的不应该创建索引吗?

如果一个字段的取值少,比如性别这个字段,通常不需要创建索引,但是特殊情况还是需要考虑的,比方说唐僧到女儿国了,100万女施主,男性只有5个(小白龙也算上),男性只是女性20万分之一了。

女儿国数据表user_gender:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_gender
-- ----------------------------
DROP TABLE IF EXISTS `user_gender`;
CREATE TABLE `user_gender`  (
  `user_id` int(11) NOT NULL,
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `user_gender` tinyint(1) NOT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

要筛选男性可以使用语句,查询语句如下:(*也就三个字段:user_id,user_name,user_gender)

SELECT * FROM user_gender WHERE user_gender = 1

为了防止装逼失败,直接说结论吧。在未创建索引的情况下,运行效率不高,如果对user_gender字段创建索引呢?

SELECT * FROM user_gender WHERE user_gender = 1

同样的数据,运行结果相同,时间却只是未创建索引时的10分之1,查询效率提升了不止一个档次。

通过上面两个实验想要说明什么呢,其实索引价值就是快速定位,如果想要定位的数据有很多,那么索引就失去了他的使用价值,比如通常情况下性别字段男女比例平衡。不过有时考虑到这个字段中数值分布的情况,在实验2中,性别字段的数值分布非常特殊,男性比例非常少,使用索引查询效率就提升了。因此,不仅要看字段中的数值个数,还要根据数值的分布情况来考虑是否需要创建索引。

索引种类

从功能逻辑上说,索引共有4种:普通索引、唯一索引、主键索引、全文索引。

普通索引是基础的索引,没有任何约束,主要用于提高查询效率。

唯一索引是在普通索引的基础上添加了数据唯一性的约束,在一张表中可以有多个唯一索引。

主键索引是在唯一索引的基础上增加了不为空的约束,也就是NOT NULL+UNIQUE,一张表中最多只有一个主键索引(这是由主键索引的物理实现方式决定的,因为数据存储在文件中只能按一种顺序进行存储,但可以有多个普通索引或唯一索引)。

全文索引用的不多,MySQL中全文索引只支持英文。通常可以采用专门的全文搜索引擎如ES和Solr。

前三种索引是同一类索引,只是对数据的约束性逐渐提升。

按物理实现方式,索引分为两种:聚集索引和非聚集索引。非聚集索引又称为二级索引或辅助索引。

聚集索引可以按主键来排序存储数据,查找行更有效。举例:在一本字典中查找“数”这个字,直接在字典中找拼音“shu”的位置即可。这样找到了索引的位置,在它后面就是想要的数据行。

非聚集索引:数据库系统会有单独的存储空间存放非聚集索引,这些索引项是按照顺序存储的,但索引项指向的内容是随机存储的。即是说系统会进行两次查找,第一次先找到索引,第二次找到索引对应的位置的数据行取出。非聚集索引不会把索引指向的内容直接放到索引后面,而是维护单独的索引表(只维护索引,不维护索引指向的数据),为数据检索提供方便。举例:如果想要找“数”字,按照部首查找方式,先找到偏旁部首,然后这个目录告诉我们“数”字在第多少页,然后我们才去指定的页码找这个字。

(都快忘了字典咋用的了。。)

聚集索引与非聚集索引使用区别:

1.聚集索引叶子节点存储的就是数据记录,非聚集索引的叶子节点存储的是数据位置。非聚集索引不会影响数据表的物理存储顺序。

2.一个表只能有一个聚集索引,因为只能有一种排序存储的方式,但是可以有多个非聚集索引,也就是多个索引目录提供数据检索。

3.使用聚集索引的时候,数据的查询效率高,但如果对数据进行插入,删除,更新等操作,效率会比非聚集索引低。

实验3:使用聚集索引和非聚集索引的查询效率

还以刚才的user_gender表举例,对比一下聚集索引和非聚集索引查询效率。通过建表语句看到,该表设置了user_id为主键也就是聚集索引的字段是user_id。此处查询一下user_id=90001的用户信息:

(把该表中唐僧5人踢出去然后空降了10位精壮的成年男子。。)

对user_id和user_name两种情况的查询结果如下:

mysql> SELECT user_id, user_name, user_gender FROM user_gender WHERE user_id = 900001
-> ;
+---------+----------------+-------------+
| user_id | user_name | user_gender |
+---------+----------------+-------------+
| 900001 | student_890001 | 0 |
+---------+----------------+-------------+
1 row in set (0.00 sec)

mysql> SELECT user_id, user_name, user_gender FROM user_gender WHERE user_name = 'student_890001'
-> ;
+---------+----------------+-------------+
| user_id | user_name | user_gender |
+---------+----------------+-------------+
| 900001 | student_890001 | 0 |
+---------+----------------+-------------+
1 row in set (0.54 sec)

可以通过看执行时间进行对比。

然后通过对user_name字段创建普通索引,再查询,运行时间也降低到0.05.s了。

结论:

1.对where子句的字段建立索引,可以大幅提升查询效率。

2.采用聚集索引进行数据查询,比使用非聚集索引的查询效率高。如果查询次数比较多,尽量使用主键索引进行数据查询。

 

除了业务逻辑和物理实现方式,索引还可以按照字段个数进行划分,分成单一索引和联合索引。索引列为一列时为单一索引,多个列组合在一起创建的索引称之为联合索引。

注意:创建联合索引需要注意创建时的顺序,因为联合索引(x,y,z)和(z,y,x)在使用的时候效率可能会存在差别。

需要说明的是联合索引存在最左匹配原则。也就是按照最左有限的方式进行索引的匹配。比如(x,y,z),如果查询条件是where x=1 and y=2 and z=3,就可以匹配上联合索引;如果查询条件是where y=2,就无法匹配上联合索引。

实验4:联合索引的最左原则

还是以user_gender表为例,把user_id和user_name设置为联合主键,对比查询效率。

SELECT user_id, user_name, user_gender FROM user_gender WHERE user_id = 900001 AND user_name = 'student_890001'

查询用时:0.046s

SELECT user_id, user_name, user_gender FROM user_gender WHERE user_id = 900001

查询用时:0.046s

然后看普通查询:

SELECT user_id, user_name, user_gender FROM user_gender WHERE user_name = 'student_890001'

查询用时:0.943s

可以看到使用联合索引(user_id,user_name)时,在where子句中队联合索引中的字段user_id和user_name进行条件查询,或者只对user_id进行查询,效率基本上是一样的;当对user_name进行条件查询时,效率会低很多,这是因为根据联合索引的最左原则,user_id在user_name的左侧,如果没有使用user_id直接使用user_name进行条件查询,联合索引就会失效。

总结

使用索引可以从海量数据中快速定位数据,不过索引也存在不足:占用存储空间、降低数据库写操作的性能等。如果有多个索引还会增加索引选择的时间。当使用索引时,需要权衡索引的利(提升查询效率)弊(维护索引所需的代价)。

思考

关于联合索引的最左原则指的是什么?使用联合索引时需要注意哪些地方?

(本文完)

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
  • A+
所属分类:SQL

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: