Mysql 索引

MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的关系数据库管理系统应用软件之一。本文主要介绍Mysql的索引。对于数据的操作无非增删改查，而在海量数据存储的今天，查询的效率越来越来是被每个人关注的事情。如何优化查询被时刻关注着。

什么是索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单；

为什么要用索引

索引相当于在查字典的时候目录的作用，用户在翻阅字典的时候通过目录快速定位需要查找的资料的位置，索引也是同样的作用；

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

索引的结构

上文提到建立索引，那么建立的索引是什么样的结构，应该用什么的方式去存储这些索引；

mysql索引的数据结构是树，常用的存储引擎innodb采用的是B+Tree，这里对B+Tree及其相关的，通常不同的数据库使用的B+Tree 不是完全一样的；
这是一个结构可视化网站，可以通过插入不同的数据体会一下
这是另一个博主的详解，这里不加叙述

索引存储计算

建立索引不是越多越好，mysql 单表建立索引的上限是 16个，同样索引的建立是需要消耗磁盘空间的,可以在.myi //索引
.frm //结构两个文件中看到自己的数据库索引的占用的位置大小；索引的创建和删除都是需要时间的，过多的无效索引反而会造成不必要的性能浪费；同样 因为复杂的索引，在查询的时候很容易造成索引失效；

通常mysql的查询按照页查询，一页大小16kb，，我们要做的就是充分利用这一页；通过计算主键类型，主键长度，B+树结构下两层或者三层叶子节点上能够存储的索引范围，这通常也是计算分库分表的指标之一；

聚合索引

通常在查一张用户表的时候，我们可能更多的时候需要用用户名、手机号、状态等字段查询，这时候通常的做法会对这些字段都加上索引；
概念可参考这片文章

索引失效

建立索引以后，有效的利用这些索引，才是关键；以下是几种索引会失效的原因

1. 索引无法存储null值；
2. 重复数据较多的列；
3. 前导模糊查询不能利用索引 (like '%XX’或者like ‘%XX%’)；
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引；
5. mysql 认为全表扫描的消耗小于走索引查询的其他的情况；
6. 对于聚合索引失效的情况见下面链接，不再赘述；聚集索引失效

索引使用案列

mysql 8.x中的JSON 索引查询和优化

json查询是8.x中mysql 重点优化的查询方式之一；官方介绍的是可以替代nosql 查询的一种方式；不知道大家在实际使用中使用到的次数，以下只是介绍我在实际使用中测试的一些数据，以及简单的使用案例；
在使用时感受最大的是，插入和修改不是很方便，需要将整个字段整体更新（TXT、VARCHAR）；

JSON数组查询

-- 模拟数据
-- {"BANNER":"201","TAGS":["1","2"]}
select * from table_test where JSON_CONTAINS( extend_info ->> '$."TAGS"',JSON_ARRAY ('1'),'$');

Mybatis-xml

select * from table_test where JSON_CONTAINS( extend_info ->> '$."TAGS"',JSON_ARRAY
    <foreach item="tag" index="index" collection="request.tags.split(',')" open="(" separator="," close=")">
        #{tag}
    </foreach>
,'$')

JSON 模糊查询
mysql

select * from table_test t where extend_info->'$."TAGS"' LIKE '%1%';

Mybatis-xml

select * from table_test t where extend_info->'$."TAGS"' LIKE CONCAT ('%',#{tags},'%');

添加索引
会创建一个tags 的列，其中的数据就是json中 TAGS 包含的数据

alter table cdi_corporation add tags varchar(20) generated always as(extend_info->'$.TAGS') stored;