MySql索引笔记
MySQL 索引是什么
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引,单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引,组合索引,即一个索引包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件),实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
上面都在说使用索引的好处,但过多的使用索引将会造成滥用,因此索引也会有它的缺点,虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
MYSQL索引类型
NORMAL:普通索引
FULLTEXT:全文索引
SPATIAL:空间索引(目前还没用过)
UNIQUE:表示唯一的,不能重复的索引,可为NULL
PRIMARY KEY:唯一索引,不能为NULL
KEY:普通非唯一索引
MYSQL索引方法
目前主要有以下几种索引方法:B-Tree,Hash,R-Tree。
B-Tree:
是最常见的索引类型,所有值(被索引的列)都是排过序的,每个叶节点到跟节点距离相等。所以B-Tree适合用来查找某一范围内的数据,而且可以直接支持数据排序(ORDER BY),B-Tree在MyISAM里的形式和Innodb稍有不同,MyISAM表数据文件和索引文件是分离的,索引文件仅保存数据记录的磁盘地址,InnoDB表数据文件本身就是主索引,叶节点data域保存了完整的数据记录。
Hash:
1.仅支持"=","IN"和"<=>"精确查询,不能使用范围查询,由于Hash索引比较的是进行Hash运算之后的Hash值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的Hash算法处理之后的Hash。
2.不支持排序:,由于Hash索引中存放的是经过Hash计算之后的Hash值,而且Hash值的大小关系并不一定和Hash运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算。
3.在任何时候都不能避免表扫描,由于Hash索引比较的是进行Hash运算之后的Hash值,所以即使取满足某个Hash键值的数据的记录条数,也无法从Hash索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。
4.检索效率高,索引的检索可以一次定位,不像B-Tree索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以Hash索引的查询效率要远高于B-Tree索引。
5.只有Memory引擎支持显式的Hash索引,但是它的Hash是nonunique的,冲突太多时也会影响查找性能。Memory引擎默认的索引类型即是Hash索引,虽然它也支持B-Tree索引。
MYSLQ索引创建
1、单列索引
1.1、普通索引,最基本最常用的索引。
1 CREATE INDEX IndexName ON `TableName`(`字段名`(length)); 2 ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length));
如果是字段类型是CHAR,VARCHAR,length可以小于字段的实际长度,如果是BLOB和TEXT类型就必须指定长度。
1.2、唯一索引,与普通索引类似,但是不同的是唯一索引要求所有列的值是唯一的,但是允许有空值。
1 CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length)); 2 ALTER TABLE TableName ADD UNIQUE INDEX IndexName(`字段名`(length));
1.3、主键索引,与唯一索引类似,但是不能有空值。
主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列,一般会设为 int 而且是 AUTO_INCREMENT自增类型的
2、组合索引
一个索引包含2个及以上的字段
1 CREATE INDEX IndexName ON `TableName`(`字段名1`(length),`字段名2`(length),`字段名3`(length)); 2 ALTER TABLE TableName ADD INDEX IndexName(`字段名1`(length),`字段名2`(length),`字段名3`(length));
组合索引(IndexName) 那么它实际包含的是3个索引 (字段1) ,(字段1,字段2),(字段1,字段2,字段3),那么应该如何使用组合索引呢?
查询的时候遵循组合索引的【最左前缀】原则,什么是最左前缀?就是索引where时的条件要按照建立索引的时候字段的排序方式,举例说明一下:
a、不按索引最左列开始查询索引无效,例如index(‘c1’, ‘c2’, ‘c3’) ,查询条件是where ‘c2’ = ‘aaa’或者where `c2` = `aaa` and `c3`=`sss` ,这些情况索引都是无效的,where c1 = 'aaa' and c2 = 'bbb'或者where c1 = 'aaa' and c2 = 'bbb' and c3 = 'ccc'索引有效。
b、查询中某个列有范围查询,则其右边的所有列索引无效,例如index(‘c1’, ‘c2’, ‘c3’) ,查询条件是where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ ,该查询c1和c2索引有效,c3索引无效,因为like是范围查询。
c、跳过某个字段来进行查询其实和第2条一样的原理,索引无效,例如index('c1','c2','c3'),查询条件是where c1 > 1 and c2 = 2 and c3 = 3,索引无效
3、全文索引
文本字段类型是text,如果建立的是普通索引,那么只有对文本的字段内容前面的字符进行索引,其字符大小根据索引建立索引时申明的大小来规定,如果文本中出现多个一样的字符,而且需要查找的话,那么其条件只能是 where column lick '%xxxx%' 这样做会让索引失效,这个时候全文索引就发挥作用了。
1 CREATE FULLTEXT INDEX IndexName ON `TableName`(`字段名`); 2 ALTER TABLE TableName ADD FULLTEXT INDEX IndexName(`字段名`);
未完待续。。。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 25岁的心里话
· 闲置电脑爆改个人服务器(超详细) #公网映射 #Vmware虚拟网络编辑器
· 零经验选手,Compose 一天开发一款小游戏!
· 因为Apifox不支持离线,我果断选择了Apipost!
· 通过 API 将Deepseek响应流式内容输出到前端