MySQL之详解索引

MySQL之详解索引,第1张

文章目录 MySQL索引1.索引是什么?2.索引的优劣势?3.索引的分类3.1 单列索引3.2 组合索引3.3 全文索引(MyISAM,InnoDB5.6以后)3.4 空间索引 4.索引的使用4.1 索引相关语句4.1.1 单列索引之普通索引4.1.2 单列索引之唯一索引4.1.3 单列索引之全文索引4.1.4 组合索引4.1.5 删除索引4.1.6 查看索引 5.索引原理分析5.1 索引的存储结构5.1.1 B树5.1.2 B树和B+树的区别 5.2 聚集索引(InnoDB)5.3 非聚集索引(MyISAM)5.4 主键索引Primary key5.5 辅助索引Secondary key(次要索引) 6.实例6.1 构架实验环境,创建表6.2 添加测试数据:6.3 数据存储过程最左前缀原则索引覆盖索引下推 7.数据库文件存储7.1物理文件(顺序IO)7.2数据文件(随机IO) 二叉树,红黑树,B-tree,B+tree

MySQL索引 1.索引是什么? 索引是帮助MySQL高效获取数据的数据结构。索引往往存储在磁盘上的文件中索引中包括:聚集索引,覆盖索引,组合索引,前缀索引,唯一索引等,默认都是使用B+树结构组织索引 2.索引的优劣势? 优势: 检索:可以提高数据检索的效率,降低数据库的IO成本排序:通过索引列对数据进行排序,降低了CPU的消耗 劣势: 占磁盘空间降低更新表的效率 3.索引的分类 3.1 单列索引 普通索引:没有任何限制。add index唯一索引:索引列中的值必须唯一,允许空值。add unique index主键索引:特殊的唯一索引,不允许空值。PK 3.2 组合索引 在表中的对个字段组合上创建的索引 add index(col1, col2……)遵循最左前缀原则(最左匹配原则) 3.3 全文索引(MyISAM,InnoDB5.6以后) 只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。fulltext优先级最高,先执行存储索引,决定执行一个索引 3.4 空间索引 4.索引的使用 4.1 索引相关语句

主键索引不需要创建,系统会自动生成。

4.1.1 单列索引之普通索引
create index index_name on table(column(length));
alter table table_name add index index_name(column(length));
4.1.2 单列索引之唯一索引
create unique index index_name on table (column(lnegth));
alter table table_name add unique index index_name(column);
4.1.3 单列索引之全文索引
create fulltext index index_name on table(column(length));
alter table table_name add fulltext index_name(column);
4.1.4 组合索引
alter table table_name add index index_name_(title(50),time(10),......);
4.1.5 删除索引
drop index index_name on table;
4.1.6 查看索引
show index from table_name
5.索引原理分析 5.1 索引的存储结构 索引在存储引擎中实现(不同的引擎会只用不同的索引)MyISAM和InnoDB存储引擎:只支持B+tree索引MEMORY/HEAP存储引擎:支持HSAH和BTREE索引

MyISAM采用的是非聚簇索引,InnoDB采用的是聚簇索引

5.1.1 B树 B树的高度一般在2-4,树的高度直接影响IO读写的次数三层树结构----支撑的数据可以达到20G,如果是四层树结构----支撑的数据可以达到几十T 5.1.2 B树和B+树的区别 B树和B+树最大区别在于非叶子节点是否存储数据的问题。

由于B树的其他子节点也存储有数据data,所以在每页中占用了相当一部分内存,而B+树只有主键索引,没有数据data域,每页((4kB,8KB,16KB)存储的主键索引相对来说是比较多的。

5.2 聚集索引(InnoDB) 主键索引(聚集索引)的叶子结点会存储数据行,也就是说数据和索引在一起辅助索引只会存储主键值 5.3 非聚集索引(MyISAM) B+树叶子结点只会存储数据行(数据文件)的指针,简单来说就是数据和索引不在一起非聚集索引包含 主键索引辅助索引 到会存储指针的值 5.4 主键索引Primary key

InnoDB要求表必须有主键(MyISAM可以没有),如果没有,MySQL系统会自动选择一个唯一标识数据记录的列作为主键

MyISAM的索引文件(mdi)仅仅保存数据记录的地址

MyISAM的数据文件(ibd)中记录对应的记录

5.5 辅助索引Secondary key(次要索引) 结构和主键搜索引没有任何区别同样用B+Tree,data域存储相应记录主键的值而不是地址

聚集索引通过主键搜索十分高效,但是辅助索引搜索需要检索两边索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

6.实例

了解了索引的基本概念,下面通过实例说明:

6.1 构架实验环境,创建表
-- 删除t1表
DROP TABLE t1;

-- 创建t1表
CREATE TABLE t1(
	a INT PRIMARY KEY,
	b INT,
	c INT,
	d INT,
	e VARCHAR(20)
)ENGINE = INNODB;

通过创建表,我们要明白,MySQL数据库在创建表的时候会自动添加以(主键)索引,如果没有主键,系统自带rows生成索引。

注意在Oracle数据库中有rownum关键字,查询系统自动生成的序列号

select rownum from table_name;

6.2 添加测试数据:
-- 向t1表中添加数据
insert into t1 values(4,3,1,1,'d');
INSERT INTO t1 VALUES(1,1,1,1,'a');
INSERT INTO t1 VALUES(8,8,8,8,'h');
INSERT INTO t1 VALUES(2,2,2,2,'b');

INSERT INTO t1 VALUES(5,2,3,5,'e');
INSERT INTO t1 VALUES(3,3,2,2,'c');
INSERT INTO t1 VALUES(7,4,5,5,'g');
INSERT INTO t1 VALUES(6,6,4,4,'f');
6.3 数据存储过程

注意在添加数据时系统就会自动在存储时进行排序

一页存储不下时,存储在下一页中

可以通过插入数据时不断select查询观察细节

-- 删除表中所有数据
delete  from t1;

-- 删除指定id的数据
delete from t1 where a in(1,4,8);

存储过程慢慢生成B+树(数据都在叶子节点)

通过explain查看一些sql语句的性能进行简单的查询

-- 通过primary索引查询
EXPLAIN SELECT * FROM t1 WHERE a > 1;

-- 全表查询all
EXPLAIN SELECT * FROM t1 WHERE  b = 1 AND c = 1 AND d = 1 ;

-- 直接主键索引查询,性能高
EXPLAIN SELECT * FROM t1 WHERE a = 8;

-- 通过查看索引,我们发现只有主键查询
SHOW INDEX FROM t1;

所以我们创建其他索引,达到快速查询的要求。

-- 创建bcd组合索引
CREATE INDEX idex_t1_bcd ON t1(b,c,d);

创建组合索引后,执行如下语句:了解key_len

-- 通过组合索引index_t1_bcd查询,但是由于是*所以需要回表查询using where
-- key_len是真正使用的索引大小,此处为15
EXPLAIN SELECT * FROM t1 WHERE  b = 1 AND c = 1 AND d = 1 ;

-- key_len此处为5
EXPLAIN SELECT * FROM t1 WHERE  b = 1;

-- key_len此处为10,e不在index_t1_bcd组合索引的列中,不计算
EXPLAIN SELECT * FROM t1 WHERE  b = 1 AND c = 1 AND e = 1 ;

练习完以上案例后,看如下图,就会明白索引中原来有这么多原理和关键字

最左前缀原则

要运用组合索引时,必须从遵循最左原则(就是最左边的列必须出现在where后的第一个)

-- 全表查询all
EXPLAIN SELECT * FROM t1 WHERE   c = 1 AND d = 1 ;

-- 虽然使用的是index_t1_bcd索引但是由于c > 1是范围,所以key_len在此处是10
EXPLAIN SELECT * FROM t1 WHERE  b = 1 AND c > 1 AND d = 1 ;

了解了以上语句执行后效果,还有除此之外运用组合索引的特殊情况:

-- possible_keys显示此处可能使用index_t1_bcd索引
-- 但是由于b > 1是范围,type是all,系统自动选择全表查询
EXPLAIN SELECT * FROM t1 WHERE   b > 1;

-- 通过组合索引index_t1_bcd查询,key_len此处为5
-- 有人就说了b > 6不是范围吗?怎么能用组合?难道不遵循最左原则?
-- 这是因为b > 6后面只有一个记录,所以系统会选择最优的查询路径
EXPLAIN SELECT * FROM t1 WHERE   b > 6;
索引覆盖

索引覆盖的使用能够减少树的搜索次数,避免了回表

-- 通过组合索引index_t1_bcd查询,key_len此处为5
-- 有人又有疑问了,b > 1不是范围吗?怎么能有组合索引?
-- 这是因为查询的只有b所以通过组合查询比较快。
EXPLAIN SELECT b FROM t1 WHERE   b > 1;

-- 在组合索引中就有id的存储,所以不用回表查询,这就是索引覆盖
EXPLAIN SELECT a,b FROM t1 WHERE   b > 1;
索引下推

MySQL5.6出现,主要用于非主键索引尤其联合索引(组合索引),减少回表查询的次数

-- 在没有索引下推之前,在组合索引中有几条b = 1的记录(忽略d),就会回表查询(d)多少次
-- 对于此sql语句b = 3有两条((4,3,1,1,'d'),(3,3,2,2,'c'))查询过程就需要回表两次
-- 而又索引下推优化后,在组合索引中不会忽略d这个字段,进而判断符合条件的,从而只需要回表查询一次。
-- 索引下推在Extra中会显示Using index condition
-- 虽说需要回表查询,但是经过索引下推后,满足b = 3 and d = 1的记录只有1条
-- 所以row在此案例中应该显示1
EXPLAIN SELECT * FROM t1 WHERE  b = 3 AND d = 1 ;

-- 没有d字段,所以需要将满足b = 3的记录全部回表查询,row显示2
EXPLAIN SELECT * FROM t1 WHERE  b = 3 AND e = 1 ;

以上都是int类型的索引,下面给varchar类型创建索引

CREATE INDEX idx_t1_e ON t1(e);

执行如下sql语句:数据类型转换

-- 返回结果为1
SELECT 1 = 1;

-- 返回结果为0
SELECT 1 = 0;

-- 返回结果为1
SELECT 1 = '1';

EXPLAIN SELECT * FROM t1 WHERE a = 1;

EXPLAIN SELECT * FROM t1 WHERE a = '1';

EXPLAIN SELECT * FROM t1 WHERE e = 1;

EXPLAIN SELECT * FROM t1 WHERE e = '1';
7.数据库文件存储
-- 查看存储引擎
SHOW ENGINES;
7.1物理文件(顺序IO) 日志文件

MySQL通过日志记录数据库操作信息和错误信息。

-- 查看当前数据库中的日志使用信息
SHOW VARIABLES LIKE 'log_%';
错误日志(errorlog)

默认开启。记录了运行中遇到的所有严重的错误以及MySQL每次启动和关闭的详细信息

错误日志名字;hostname.err

查看日志的文件路径:log_error

log_warings:警告信息是否开启

二进制日志(binlog)通用查询日志(general querylog)慢查询日志(slow query)重做日志(redo log)回滚日志(undo log)中继日志(relay log) 7.2数据文件(随机IO) 查看MySQL数据文件:
show variables like '%datadir%';

ibd为InnoDB存储,.MYD,.MYI为MyISAM存储

InnoDB数据文件

.frm文件:主要存放与表相关的数据信息,主要包括表结构的定义信息.ibd:使用独享表空间存储表数据和索引信息,一张表对应一个ibd文件ibdata文件:使用共享表空间存储表数据和索引信息,所有表共同使用一个或者多个ibdata文件

MyISAM数据文件;

.frm文件:主要存放与表相关的数据信息,主要包括表结构的定义信息.myd文件:主要用来存储表数据信息.myi文件:主要用来存储表数据文件中任何索引的数据树
-- 查看总内存大小
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';
二叉树,红黑树,B-tree,B+tree hash表(散列函数)当自增主键时,二叉树没有效果,只会往右存,树的深度太大了,不适合做索引结构红黑树,解决了单边增长的问题,但数据过多时,树的深度也会很深B树是为磁盘等外部存储设备设计的一种平衡查找树 B-treeB+tree非叶子节点不存储数据,只存储索引

据信息,主要包括表结构的定义信息

.myd文件:主要用来存储表数据信息.myi文件:主要用来存储表数据文件中任何索引的数据树
-- 查看总内存大小
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';

关键字:

OLTP (联机事务处理)OLAP (联机分析处理)

以上都是在学习过程中在学习资料中整理的,如有侵权,联系删除。

欢迎分享,转载请注明来源:内存溢出

原文地址:https://outofmemory.cn/sjk/992022.html

(0)
打赏 微信扫一扫微信扫一扫 支付宝扫一扫支付宝扫一扫
上一篇 2022-05-21
下一篇2022-05-21

随机推荐

  • 草薙京技能是什么?

    1、外式·轰斧阳:→+B2、外式·奈落落:(跳跃中)↓+C3、八十八式:↘+D4、百式·鬼燃烧:→↓↘+A或C5、七百七式·独乐屠:←↓↙+B或D6、二百十二式·琴月阳:→↘↓↙←+B或D7、七十五式改:↓↘→B+B或D+D8

  • a1687是苹果什么型号

    品牌型号:iPhone6s plus系统:IOS15.4a1687是苹果是iPhone6s plus的手机型号。不管是港版还是日版美版的a1687都是全网通。iPhone 6s Plus于2015年9月10日发布。采用5.5

    2023-02-03
    100
  • 手机上如何使用U盘 用手机打开U盘的方法

    无需依赖电脑,通过 USB OTG 数据线即可读取 U 盘内的数据。温馨提醒:此功能仅适用于支持 USB OTG 数据线的手机。如果手机采用 Type-C 接口,请购买华为认可的 Micro USB 转 Type-C 接头,或者带 Type

    2023-02-03
    200
  • 汽车电瓶多少伏呀?

    汽车电瓶12伏。电瓶可以在汽车行驶过程中通过发电机进行充电,如果汽车长时间停放很容易导致电瓶电量流失,造成汽车启动不了。汽车电池要经常充电,一些电瓶长久不用会自行放电。汽车电瓶没电怎么办通常家用轿车的电瓶为12V,实际上可能会高一些,一

    2023-02-03
    100
  • 徐州是不是江苏的啊?

    徐州是江苏的。徐州,简称“徐”,古称彭城,是江苏省辖地级市、省域副中心城市,国务院批复确定的国家历史文化名城、全国性综合交通枢纽、淮海经济区中心城市。全市总面积11258平方公里,户籍人口1034.8万,现辖2市(新沂、邳州)、3县(丰

    2023-02-03
    100
  • 成吉思是什么意思?

    成吉思在蒙语中代表的意思是坚强和大海的意思。成吉思汗 [ chéng jí sī hán ]基本释义:成吉思在蒙语中是坚强和大海的意思。汗(hán):可汗的简称,古代鲜卑、柔然、突厥、回纥、蒙古等族对其统治者的称号。成吉思汗(1162—

    2023-02-03
    100
  • 电力中"TT系统"是什么意思?

    TT方式供电系统是指将电气设备的金属外壳直接接地的保护系统,称为保护接地系统,也称TT系统。第一个符号T表示电力系统中性点直接接地;第二个符号T表示负载设备外露不与带电体相接的金属导电部分与大地直接联接,而与系统如何接地无关。TT系统就

    2023-02-03
    100
  • 猫的种类有哪些

    猫的种类有:狸花猫、褴褛猫、威尔士猫、日本短尾猫、卡尔特猫、埃及猫、波米拉猫、彼得秃猫、索马里猫、四川简州猫等。1、狸花猫中国本土犬种,有几千年历史,身体强壮,性格独立,捕猎能力强,是世界认可的唯一一种中国纯种猫。2、褴褛猫褴褛猫是个小

    2023-02-03
    100
  • 猎奇是什么意思

    猎奇的意思是急切地或贪得无厌地搜求新奇和异样的东西。读音:[liè qí]。近义:鬼畜。出处:杨朔《巴厘的火焰》:“西方的游客好猎奇……于是对巴厘印度教抱着奇特的趣味。”例句1、人们喜欢猎奇,这就是科学的种子。2、成长在剑叶心中的菠

    2023-02-03
    100

发表评论

登录后才能评论

评论列表(0条)

    保存