Mysql 数据库第十七节索引基础

mac2024-04-18 36

Mysql 索引基础

索引概述：

1.索引是一种特殊的文件，（innoDB）数据表上的索引是表空间的一个组成部分。他们包含着对数据表里所有记录的引用指针，

合理的使用索引的 Mysql 数据库：兰博基尼

没有使用索引的 Mysql 数据库：人力三轮车

对于没有索引的表：单表查询可能几十万条数据就已经到达瓶颈，而通常大型网站单日所产生的数据量就会达到几十万甚至几百万条，所以没有索引的数据库查询数据会变得非常缓慢

2.使用索引的有点

大大提高数据检索的效率，降低数据库的 IO 成本

对于分组和排序语句进行数据检索，可以减少分组和排序所消耗的时间，降低 CPU 成本

3.使用索引的缺点

索引文件会占用物理空间，除了数据表需要占用空间以外。没一个索引还会占用一定的物理空间

当对标的数据进行操作， insert, update， delete 的时候，索引也要动态的维护，这样就会降低数据的维护速度

4.索引的分类

索引是在储存引擎中实现的，也就是说不同的引擎会使用不同的索引

MyISAM 和 InnoDB 存储引擎：只支持 BTREE 索引，也就是说默认使用 BTREE, 不能够更换

MEMORY / HEAP 存储引擎：支持 BTREE 和 HASH 索引两种

1. 索引我们分为四类来讲

*单列索引：（普通索引，唯一索引，主键索引），一个表可以有多个单列索引

*组合索引：一个索引可以包含多列

*全文索引：只有在 MyISM 引擎上才能使用，只能在 CHAR VARCHAR TEXT 类型字段上才使用

*空间索引：作用于空间数据类型（不常用）

单列索引：

1. 普通索引

Mysql 中普通索引类型，没有什么限制。允许在定义索引的列中插入重复值和空值；纯粹为了查询数据更快一点

2. 唯一索引 unique

索引列中的值必须是唯一的，但是允许为 null 值

3. 主键索引 primary key

是一种特殊的索引，不允许有空值

分析表：用来查看所有是否又被使用，并且输出其中的索引信息

# emp 表名； eno = 1001 字段名使用 EXPLAIN SELECT * FROM emp WHERE eno = 1001 ;

id: SELECT 标识符，这里是 SELECT的查询序列号，也就是一条语句中，SELECT 是第几次出现的。在此语句中只有一次。所有是1select_type: 所使用 SELECT 查询类型， SIMPLE 表示为简单的 SELECT, 不使用 UNION 或者子查询，就是简单的 SELECTtable : 数据表的名字，若有多个表，按被读取的先后排列type: 指定本数据表和其他数据表之间的关联关系。该表中所有符合检索值的记录都会被取出来和从上一个表中取出的数据联合possible_key: Mysql 在搜索数据记录的时候可以选用各个索引，该表中没有索引key: 实际使用的索引key_len: 显示了 Mysql 使用索引的长度（也就是使用的索引个数），当 key 字段为 NULL 时，索引的长度也就是 NULLref : 给出关联关系中另一个表中的关联列的名称，常亮（const）,这里没有使用使用关联索引是 NULLrows: Mysql 在执行这个查询的时候预计会从这个数据表里独处数据行的个数extra: 提供了与关联操作的相关信息，没有则什么都不写

索引的创建：

#创建索引 CREATE INDEX 索引名称 ON 表名 (列名1|列名1); #删除索引 DROP INDEX 索引名称 ON 表名; DROP INDEX eno_index ON emp; CREATE INDEX eno_index ON emp (eno);

1.什么情况下下需要建立索引

主键自动建立唯一索引

频繁作为查询条件的字段应该创建索引

查询中与其他表关联的字段，外键关系建立索引

频繁更新的字段不适合建立索引，应为每次更新的不只是数据，还有更新索引

WHERE 条件里用不到的字段不创建索引

查询中排序的字段需要建立索引，大大提高排序的效率

查询中统计或者分组的字段需要建立索引

2.大多数情况下索引能大幅提高查询效率，但是：

数据的变更（增删改）都需要维护索引，因此更多的索引意味着更多的维护成本

更多的索引意味着也需要跟多的空间（一本 100 页的书，你还要去找50页的目录？）

过小的表，建立索引可能会更慢（读个两夜的宣传手册，你还要去看目录）

3. 什么样的字段不适合建立索引

一般来说：列的值唯一性太小，（如，性别，星期，），不太适合建立索引，（怎么叫太小？一般来说同值的数据量超过表的 15% ，那么久没必要建立索引了）

太长的列, 可以选择只建立部分索引，例如（只取前十位做索引）

更新非常频繁的数据不适宜做索引

补充：　　1、事务隔离级别为读提交时，写数据只会锁住相应的行　　2、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。　　3、事务隔离级别为串行化时，读写数据都会锁住整张表　　 4、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

索引结构：

BTREE 索引：

又叫做 B+ 数索引，是一个平衡的多叉树，从根节点到每个叶节点的高度差不超过 1，而且同层级别的节点间有指针相互连接

关于二叉树更详细的介绍

HASH 索引：

哈希索引就是采用一系列哈希算法，把键值算成哈希值，检索时不需要类似 B+树那样，从根节点到叶节点逐级查找，只需要一次哈希算法即可立即定位到数据。类似于 JAVA 中的 HashMap 取数据。但是 Hash 索引只适用于 MEMORY / HEAP 存储引擎

最新回复(0)

Mysql 数据库 第十七节 索引基础