MySQL---空间索引、验证索引、索引特点、索引原理

1. 空间索引

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是：

类型	含义	说明
Geometry	空间数据	任何一种空间类型
Point	点	坐标值
LineString	线	有一系列点连接而成
Polygon	多边形	由多条线组成

MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。

创建空间索引的列，必须将其声明为NOT NULL。

create table shop_info (
  id  int  primary key auto_increment comment 'id',
  shop_name varchar(64) not null comment '门店名称',
  geom_point geometry not null comment '经纬度’,
  spatial key geom_index(geom_point)
);

2. 验证索引

索引的最大特点是提高查询速度：

use shop;
 
-- 创建临时表
create  temporary  table tmp_goods_cat
as
select t3.catid   as cat_id_l3,   -- 3级分类id
       t3.catname as cat_name_l3, -- 3级分类名称
       t2.catid   as cat_id_l2,   -- 2级分类id
       t2.catname as cat_name_l2, -- 2级分类名称
       t1.catid   as cat_id_l1,   -- 1级分类id
       t1.catname as cat_name_l1  -- 1级分类名称
from shop.goods_cats t3,
    shop.goods_cats t2,
     shop.goods_cats t1
where t3.parentid = t2.catid
  and t2.parentid = t1.catid；

-- -- 统计分析不同一级商品分类对应的总金额、总笔数
select
  '2023-05-15',
  t1.cat_name_l1 as goods_cat_l1,
  sum(t3.payprice * t3.goodsnum) as total_money,
  count(distinct t3.orderid) as total_cnt
from
  tmp_goods_cat t1
left join goods t2
  on t1.cat_id_l3 = t2.goodscatid
left join order_goods t3
  on t2.goodsid = t3.goodsid
where
  substring(t3.createtime, 1, 10) = '2023-05-15'
group by
  t1.cat_name_l1;

运行时间为： 

 

 添加索引：

-- 创建索引
create unique index idx_goods_cat3 on tmp_goods_cat(cat_id_l3);
create unique index idx_itheima_goods on itheima_goods(goodsid);    
create index idx_itheima__order_goods on itheima_order_goods(goodsid);  

 运行时间为：

3. 索引特点：

索引的优点：

大大加快数据的查询速度；

使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间；

创建唯一索引，能够保证数据库表中每一行数据的唯一性；

在实现数据的参考完整性方面，可以加速表和表之间的连接。

索引的缺点：

创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加；

索引需要占据磁盘空间；

对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度。

创建索引的原则：

更新频繁的列不应设置索引；

数据量小的表不需要使用索引；

重复数据多的字段不应设为索引（比如性别，只有男和女，一般来说：重复的数据超过百分之15就

不该建索引）；

首先应该考虑对where 和 order by 涉及的列上建立索引。

4. 索引的原理

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁

盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几

个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次

数的渐进复杂度。或者说索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

索引的算法（hash算法）：

 

通过字段的值计算hash值，定位数据非常快。

但是不能进行范围查找，因为散列表中的值是无序的，无法进行大小的比较。

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址：

 InnoDB的叶节点的data域存放的是数据，相比MyISAM效率要高一些，但是比较占硬盘内存大小: