强制sql走某个索引 mysql强制索引有什么坏处

转载

mob64ca140761a4 2024-04-18 15:19:58

文章标签 强制sql走某个索引 mysql 数据库数据子树 文章分类 数据仓库大数据

在工作当中，我们经常遇到慢sql需要优化的场景，往往的，我们第一反应就是：加个索引！确实是，加个索引确实要比原先快很多，但是如果不懂底层原理而一味的去加索引，往往会适得其反，不是长久之计。

数据库的分类

强制sql走某个索引 mysql强制索引有什么坏处_子树

关系型数据库

优点：查询功能强，数据一致性高，数据安全性高，支持二级索引
缺点：性能方面稍逊与MongoDB，特别是百万级别以上的数据，很容易出现查询慢的现象

非关系型数据库（NoSQL not only sql）

优点：性能高，扩张性强，模式灵活，在高并发场景表现得尤为突出
缺点：数据的一致性，数据的安全性，查询的复杂性问题上和关系型数据库还存在一定差距

数据库索引的全面认识

1.索引的本质

1. 索引是什么

强制sql走某个索引 mysql强制索引有什么坏处_数据_02

通过百度百科，我们可以知道，索引其实就是一种存储结构，这里面存储了数据库中所有数据的 “目录”

强制sql走某个索引 mysql强制索引有什么坏处_子树_03

数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，要从 1000 万行数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据。那样效率就太低了。

但如果，我们给数据库表加一个目录，根据这个目录去查询想要的数据，就像查中华词典一样，通过拼音或者笔画去查询想要的数据，那效率就大大的提升了。

2.如何去创建索引

基本语法

添加普通索引：（也叫非唯一索引，是最普通的索引，没有任何的限制）
ALTER TABLE <表名> ADD INDEX <索引名> ( <字段名，联合索引，多个字段用“,”分开> ) USING BTREE;

添加唯一索引：（唯一索引要求键值不能重复）
ALTER TABLE <表名> ADD UNIQUE <索引名> ( <字段名，联合索引，多个字段用“,”分开> );

添加主键索引：（主键索引是一 种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空）
ALTER TABLE <表名> ADD PRIMARY KEY (<字段名>);

添加全文索引：（针对比较大的数据，比如我们存放的是消息内容，有几KB的数据的这种情况，如果要解决 like 查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如 char、varchar、text）
MyISAM 和 InnoDB 支持全文索引。
ALTER TABLE <表名> ADD FULLTEXT <索引名> (<字段名>);
全文索引的使用
SELECT * FROM <表名> WHERE MATCH(<字段名>) against ('明天天气怎么样' IN NATURAL LANGUAGE MODE);

删除索引
DROP INDEX <索引名> ON <表名>;

查询表中索引信息
SHOW INDEX FROM <表名>;
可以通过添加 \g 来格式化输出信息。
例：show index from table_name \g

注：添加全文索引的时候，有一个参数需要注意下：WITH PARSER ngram
详细含义点这里

2.索引存储模型的推演

2.1二分查找

之前刚学习java的时候，有一道经典的题目：用最少的次数，查询到一个数组中的某一个值。当时学的就是先将数组排序，接着使用二分查找（折半查找），使用递归查找到给定值。

有序数组
二分查找会把查找的候选数据缩小一半，这样效率提升了一倍。如果让我设计一个数据库索引的话，以我现有的知识水平，我可能会选择有序的数组作为数据库索引的存储结构。但是问题来了，使用有序数组做索引的话，查询效率可能比较高，但是更新的效率就很低了，因为要维护数组的index角标。所以有序数组只适合存储静态的数据。
单链表
为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链
表，它的查找效率还是不够高
BST(Binary Search Tree) 二叉查找树诞生了。

2.2 二叉查找树（BST Binary Search Tree)

二叉查找树的特点：

左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。

如图

左子树的节点 < 父节点

右子树的节点 > 父节点

强制sql走某个索引 mysql强制索引有什么坏处_子树_04

二叉树的优点：

二叉查找树兼顾了有序数组和链表的优点：二叉查找树既能够实现快速查找，又能够实现快速插入。

二叉树的缺点：

它的查找耗时是和这棵树的深度相关的，在最坏的情况下时间复杂度会退化成O(n)。

什么情况是最坏的情况呢？

强制sql走某个索引 mysql强制索引有什么坏处_强制sql走某个索引_05

可以看到，我们的二叉树变成了一个链表结构(我们把这种树叫做“斜树”),这种情况下不能达到加快检索速度的目的，和顺序查找效率是没有区别的。如果我要查6，那么这需要遍历所有元素来找出最大值。这是一项线性时间的操作，或称O(n)时间

造成它倾斜的原因是什么呢？
因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。
所以，我们有没有左右子树深度相差不是那么大，更加平衡的树呢？
这个就是平衡二叉树，叫做 Balanced binary search trees，或者 AVL 树（AVL 是
发明这个数据结构的人的名字）。