mysql 全文检索查总数_mysql的索引

最新推荐文章于 2023-07-02 00:12:50 发布

weixin_39895481

最新推荐文章于 2023-07-02 00:12:50 发布

阅读量401

点赞数

文章标签： mysql 全文检索查总数

本文链接：https://blog.csdn.net/weixin_39895481/article/details/113535583

版权

本文详细探讨了MySQL中的索引原理及其优化，包括为何需要索引、回表概念、索引类型（如B+树和Hash索引）、聚簇与非聚簇索引、最左前缀匹配原则等。还提到了如何选择和设计高区分度的索引，以及如何避免索引失效的情况。通过理解这些概念，可以帮助提升查询速度，降低磁盘IO，优化数据库性能。

摘要由CSDN通过智能技术生成

微信公众号： 孔小黑

小黑提示：大约 2700 字欢迎关注孔小黑^[1]

首先请各位大哥看一下 mysql的大致的架构图：

然后，大哥看一下mysql的优化成本与操作关系：

先认下怂，由于小黑目前太菜，只是了解innodb，so，小黑以innodb存储引擎为例，不展开mylsam引擎，部分内容可能有mylsam与innodb的对比，但主要还是以innodb为主，请各位大哥体谅见谅。

3W分析法开始

1-为什么需要索引

为了提高查询速度，面对日益增长的数据量，想要提高用户体验，就必须缩短用户响应时间，而数据又是必须存储的，成本又得尽可能的低，所以只能在软件层面下功夫，软件层面又当属数据结构，工程师向我们小时候的那么厚的一大本字典学习，模仿着推出了索引。

就像我们用新华字典查某个汉字时，可以根据前面的拼音或者笔画目录索引页先定位，然后再去查那个字，这样就缩短了查这个汉字的时间，否则需要每页都挨个比对，十分缓慢，可能查一个字就得一天

一言以蔽之，为了规避全量IO。

2-回表

先根据辅助索引去查出主键索引(聚集索引)一般都是主键id，再根据id查完整数据，走了两次b+树查询。innodb这么干是为了降低数据的冗余，所以只保留了id一份与实际数据的对应关系，其它的索引只保存于主键key的对应关系

向表中插入数据时候，一定会有一个索引数据。如果无主键，选择默认的6字节，row_id.

所以，主键长度越小，辅助(普通)索引的的叶子节点就越小，普通索引占用的空间间也就越小。

3-索引是什么

索引也是文件，存储在磁盘中，所以cpu在读取索引的时候就会产生磁盘io。

索引优化查询速度本质上就是利用空间换时间。

所以为了减少磁盘io，就得想方设法减少磁盘io的次数。

4-索引覆盖。

跟回表相反，能够直接从索引中获取需要的所有列，不需要回表查询。

比如user表，有id，name，age 3个字段，id是主键，name建立索引，

当select id,name where name='xiaohei' 的时候，就不需要回表，因为第一次用name去查辅助索引的结果就包含了需要的id和name两列，所以只查询一次就可以了，这样大大减少了查询时间，在实际开发业务过程中可以用用这个办法。

5-索引下推

在5.6版本以前是存储引擎只查询一个条件，然后server端负责过滤，

而MySQL 5.6 引入的索引下推优化(index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

10-最左前缀优先匹配原则

比如user表，有id，name，age ,sex 4个字段，id是主键，name建立索引,

name,age建立复合索引，语句如下：

create index idx-name-ageon user(name,age),

顺序就是name,age，这时候存储引擎在建立维护索引的时候就是按照创建索引的时候的顺序维护索引的，也就是先有name,再存age，所以在匹配查找时候是根据建立时候的顺序从左往右进行匹配的。

如图所示，索引项就是根据索引定义时的字段顺序来排序的。当sql需求是查到所有名字是“张三”的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果。如果要查的是所有名字第一个字是“张”的人，SQL语句条件是"where name like ‘张%’"。这时，也能够用上这个索引，查找到第一个符合条件的记录是ID3，然后向后遍历，直到不满足条件为止。也就是说，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

12-hash索引

hash索引

innodb存储引擎支持相应的hash。

hash索引的劣势：

范围查找不合适。

哈希冲突，碰撞(需要足够优秀的哈希算法，足够均匀的三列分布)

需要大量的内存空间。

14-聚簇索引与非聚簇索引。

数据与索引放在一起叫聚簇索引。

比如 innodb 引擎中，数据与引擎都在一个 .idb中，所以innodb中B+树是聚簇索引

myslym：

.MYD 存放数据

.MYI 存放索引

myslym引擎中的B+树是非聚簇索引。

----不同存储引擎：

不同的文件在磁盘上不同的组织形式。

16-前缀索引

使用前缀索引，需要仔细定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。首先，各位大哥可以用下面这个sql语句，算出这个列上有多少个不同的值：然后，依次选取不同长度的前缀来看这个值，比如要看一下4~7个字节的前缀索引，可以用这个语句：

    mysql> 
select count(distinct email) as L from xiaohei001;

    mysql> 
select 
count(distinct left(email,4))as L4, 
count(distinct left(email,5))as L5, 
count(distinct left(email,6))as L6, 
count(distinct left(email,7))as L7, 
from xiaohei001;

18-索引怎么选择？区分度

如果一个列中唯一值，总数<80%，不建议创建索引，比如(男女，性别)，就不适合建立索引。因为重复值太多，区分度太小。

20-局部性原理-磁盘预读

innodb引擎默认每次读取的大小16Kb。---局部性原理。这个数据是在计算机科学界不断实践调试得出来的，没有对错，只有尝试。

时间局部性，，空间局部性。。

磁盘预读：因为读取磁盘需要寻址，磁盘性能比较差，所以每次默认读取4KB

24-为什么最后选择用B+树做索引

其实最开始用的是文件，那时候数据量小，要求低

后来数据量慢慢上升，开始用二叉树，但是二叉树层级高的话，磁盘io次数太多，因为二叉树一个节点就是一个地址，而cpu在读取磁盘的时候采用的是磁盘预读，mysql默认每次读取是16KB，

假如一条记录1kb大小，

第一层只存key，

第2层只存key，

第3层存key+val，

一个key的大小为10个字节，10Byte,所以第一行的个数也就是 16x1000➗10=1600个，

第二行也是1600个，第三行是16kb/1kb=16个

1600 x 1600 x 16 = 40960000

3层B+树，4千多万条记录。

每次读取的时候还是16Kb，3次io，

千万量级的数据表一般3到4层，

但不一定，

一层就是16kb，

加一层就是加一层io，但是并发请求的话，，内存空间占用也是并发的增加，所以层数越低还是越好，最好不要超过4层，，

26-主键为啥自增性能好？

主键id，自增，b+ 连续的插入，减少树的旋转调整次数。

--------------------------------------------------------

一般给整数int创建索引，，因为key越小越好，key越小，16kb一层的key越多，树的高度越小，io次数越少，读取越快

--------------------------------------------------------

但是也要安全考虑，基于业务需要，

自增的时候，索引维护起来简单

叶子节点插入的时候直接append，，

但是不自增的话，就得重建B+树，可能会不断地分裂组合，指针重指，影响比较大，成本比较高。

27-B+树增加节点调整旋转演示demo

https://www.cs.usfca.edu/

28-第一条数据插入的时候，b+树有几层

答案：2层，，因为叶子节点存储数据，非叶子节点不存数据，只存key。

29-索引失效的几种情况：

1、like查询，%放前面，like “aaa%”可以使用索引；

2、非空判断 is not null；

3、or语句(前后都有索引才行，SQL优化要避免写or语句)；

4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

5、在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate

36-验证索引Explain：

explain列的解释table：显示这一行的数据是关于哪张表的type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和allpossible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句key：实际使用的索引。如果为null，则没有使用索引。很少的情况下，mysql会选择优化不足的索引。这种情况下，可以在select语句中使用use index(indexname)来强制使用一个索引或者用ignore index(indexname)来强制mysql忽略索引key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好ref：显示索引的哪一列被使用了，如果可能的话，是一个常数eq_ref:在连接中，mysql在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用rows：mysql认为必须检查的用来返回请求数据的行数extra：关于mysql如何解析查询的额外信息。坏的例子是using temporary和using filesort，意思mysql根本不能使用索引，结果是检索会很慢