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

随机推荐

  • 萱晴是哪个国家的品牌?

    【导读】:萱晴XuanQing广州市丽微雅化妆品有限公司,是一家主要从事美容护肤产品的开发、销售等一体的化妆品公司。公司拥有2个自主品牌,主要的销售渠道是电子商...萱晴是中国品牌。萱晴XuanQin

    2022-08-19
    0 0 0
  • CRE品牌的中文名是什么?

    【导读】:CRE的中文名是中铁快运,中铁快运是中国的品牌。中再资源环境股份有限公司是中华全国供销合作总社旗下中国再生资源开发有限公司的控股公司,是股票在上海证券交易所挂牌交易的环保科技型上市公司,股.

    2022-08-19
    0 0 0
  • 蝴蝶花舍是哪个国家的品牌?

    【导读】:蝴蝶花舍河南曌茶茶业有限公司,是中国天曌控股有限公司旗下的(以下简称曌茶茶业)立足茶树种植、茶叶生产、加工、销售、科研行业,集电子商务、酒店...蝴蝶花舍是中国品牌。蝴蝶花舍河南曌茶茶业有限

    2022-08-19
    0 0 0
  • 神舟鸟是哪个国家的品牌?

    【导读】:神舟鸟神舟鸟隶属于平乡县巨鑫儿童玩具有限公司,公司国外先进的经营和设计理念,已经在全国各大城市拥有经销商,产品因质优价好,品质高档,设计新颖...神舟鸟是中国品牌。神舟鸟神舟鸟隶属于平乡县巨

    2022-08-19
    0 0 0
  • 啊哈是哪个国家的品牌?

    【导读】:啊哈兰溪市城市猎人摩托车配件有限公司是凭借的强大摩托车头盔等产品生产和研发能力,拥有完整、科学的质量管理体系。兰溪市城市猎人摩托车配件有限公...啊哈是中国品牌。啊哈兰溪市城市猎人摩托车配件

    2022-08-19
    0 0 0
  • 华喜缘是哪个国家的品牌?

    【导读】:华喜缘华喜缘是中国品牌。华喜缘徐州恒枫家具有限公司是致力于松木家具设计、生产、销售的生产厂家,公司2005年成立于徐州市贾汪区,以满洲里上乘的松木材料和精湛的工艺技术生产出来的华喜缘松木家具

    2022-08-19
    0 0 0
  • 霍之宝是哪个国家的品牌?

    【导读】:霍之宝合肥峭壁商贸有限公司创始人家族是霍山县享有盛誉的石斛种植世家,是霍山石斛悬崖峭壁移植仿野生种植的首创者,更是霍山石斛手工枫斗制作的传承...霍之宝是中国品牌。霍之宝合肥峭壁商贸有限公司

    2022-08-19
    0 0 0
  • Ecostore是哪个国家的品牌?

    【导读】:Ecostore1993年,在新西兰北岛的原生态小镇Mamaki Eco-village,麦尔肯·兰特(Malcolm Rands)与梅兰妮·兰特(Melanie Rands)夫妇创立了ec

    2022-08-19
    0 0 0
  • 三海瓜园是哪个国家的品牌?

    【导读】:三海瓜园三海瓜园成立于1997年,是集科研、基地、保鲜、加工、销售为一体的农业产业化企业。现有1万吨气调库、1万亩基地、3条加工生产线、4个产品系列...三海瓜园是中国品牌。三海瓜园三海瓜园

    2022-08-19
    0 0 0

发表评论

登录后才能评论

评论列表(0条)

保存