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 (联机分析处理)

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

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

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

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

发表评论

登录后才能评论

评论列表(0条)

保存