【Mysql系列】索引简介

最新推荐文章于 2024-05-21 09:46:31 发布

请叫我阿炜

最新推荐文章于 2024-05-21 09:46:31 发布

阅读量132

点赞数

分类专栏：数据库文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/qq_42875020/article/details/127239801

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

Mysql索引

1、索引概述

索引是帮助Mysql高效获取数据的数据结构、在数据外、数据库系统还维护着满足特定查询算法的数据结构、这些数据结构以某种方式引用数据、这样就可以在这些数据结构上实现高级查找算法。
在这里插入图片描述
为了加快Col2的查找、可以维护一个右边所示的二叉查找树、每个节点分别包含索引键值和一个指向对应数据记录的物理地址指针、这样可以运用二叉查找快速获取到相应数据。
一般来说索引本身不是很大、不可能全部从存储在内存中、因此索引往往以索引文件的形式存储在磁盘上、索引是数据库用来提高性能常用工具。

2、索引优势劣势

优势：

类似于书籍的目录索引、提高数据检索的效率、降低数据库的IO成本
通过索引对数据进行排序、降低数据排序的成本、降低CPU的消耗
劣势
实际上索引也是一张表、该表中保存了主键与索引字段、所以索引列也是要占用空间的
实际索引大大提高了查询效率、同时却也降低更新表的速度、如对表进行INSERT、UPDATE、DELETE、因为更新表时、Mysql不仅要保存数据、还要保存一下索引文件每次更新添加了索引列的字段、都会调整因为更新所带来的键值变化后的索引信息。

3、索引结构

索引是在Mysql的存储引擎中实现的、而不是在服务器层实现的、所以每种存储引擎的索引都不一定完全相同、也不是所有的存储引擎都支持所有的索引类型。Mysql提供了一下四种索引

BTREE索引：最常见的索引类型、大部分都支持b树索引。
HASH索引：只有Memory引擎支持、使用场景简单。
R-tree索引(空间索引)：空间索引是MyISAM引擎的一个特殊索引类型、主要用于地理空间数据类型、使用较少。
Full-text(全文索引)：全文索引也是MyISAM的一个特殊索引类型、主要用于全文索引、InnoDB从Mysql5.6版本开始支持全文索引。
| 索引 | InnoDB引擎 | MyISAM引擎 | Memory引擎 |
|–|–|–|–|
| BTREE索引 | 支持 | 支持 | 支持 |
| HASH索引 | 不支持 | 不支持 | 支持 |
| R-tree索引 | 不支持 | 支持 | 不支持 |
| Full-text | 5.6版本之后支持 | 支持 | 不支持 |

4、索引的分类

单值索引：即一个索引只包含单个列、一个表可以有多个单列索引
唯一索引：索引列的值必须唯一、但允许有空值
负荷索引：即一个索引包含多个列

5、索引语法

索引在创建表的时候、可以同时创建、也可以随时增加新的索引

create database demo default charset=utf8mb4

use demo 

create table `city`(
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
`country_id` int(11) NOT NULL,
PRIMARY KEY(`city_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create TABLE `country`(
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(100) NOT NULL,
PRIMARY KEY(`country_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `city`(`city_id`,`city_name`,`country_id`) values(1,'西安',1)
insert into `city`(`city_id`,`city_name`,`country_id`) values(2,'NewYork',2)
insert into `city`(`city_id`,`city_name`,`country_id`) values(3,'北京',1)
insert into `city`(`city_id`,`city_name`,`country_id`) values(4,'上海',1)

insert into `country` (`country_id`,`country_name`) values (1,'China')
insert into `country` (`country_id`,`country_name`) values (2,'America')
insert into `country` (`country_id`,`country_name`) values (3,'Japan')
insert into `country` (`country_id`,`country_name`) values (4,'UK')

5.1、创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING indextype] ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]

create index idx_city_name on city(city_name)

5.2、删除索引

DROP INDEX index_name on tbl_name;

drop index idx_city_name on city

5.3、ALTER命令

1)、alter table tb_name add primary key (column_list);
该语句添加一个主键、意味着索引值必须是唯一的、且不能为NULL
2)、alter table tb_name add unique index_name (column_list);
这条语句创建索引的值必须是唯一的(除NULL外、NULL可能会出现多次)
3)、alter table tb_name add index index_name (column_list)
添加普通索引、索引值可以出现多次
4)、alter table  tb_name add fulltext index_name(column_list)
该语句指定了索引为FULLTEXT、用于全文索引

6、索引设计原则

索引的设计可以遵循一些已有的原则、创建索引的时候尽量符合这些原则、便于提升索引的使用效率、更高效的使用索引

对查询频次较高、且数据量比较大的表建立索引
索引字段的选择、最佳候选列应当从where子句的条件中提取、如果where子句中的组合比较多、那么应当挑选常用的、过滤效果最好的列的组合。
使用唯一索引、区分度越高、使用索引的效率越高
索引可以有效的提升查询数据的效率、但索引数量不是多多益善、索引越多、维护索引的代价自然也就水涨船高、对于插入、更新、删除等DML操作比较频繁的表来说、索引过多、会引入相当高的维护代价、降低DML操作的效率、增加相应操作时间消耗、另外索引过多的话、Mysql也会犯选择困难病、虽然最终仍然会找到一个可用索引、但会付出选择的代价。
使用短索引、索引创建之后也是使用硬盘来存储、因此提升索引访问I/O效率、也可以提升总体的访问效率、假如构成索引的字段总长度比较短、那么在给定大小的存储块内可以存储更多的索引值、相应的可以有效提升Mysql访问索引的I/O效率
利用最左前缀、N个列组合而成的组合索引、那么相当于创建了N个索引、如果查询时where子句中使用了组成该索引的前几个字段、那么这个条查询SQL可以利用组合索引来提升查询效率。

创建复合索引
CREATE INDEX idx_name_email_status on tb_seller (NAME,email,STATUS)