电子商务网站规划方案,保安服定制公司,网站建设在哪里进行,建设网站相关法律条文按照数据结构维度划分#xff1a;
BTree 索引#xff1a;MySQL 里默认和最常用的索引类型。只有叶子节点存储 value#xff0c;非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 BTree#xff0c;但二者实现方式不一样#xff08;前面已经介…按照数据结构维度划分
BTree 索引MySQL 里默认和最常用的索引类型。只有叶子节点存储 value非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 BTree但二者实现方式不一样前面已经介绍了。哈希索引类似键值对的形式一次即可定位。RTree 索引一般不会使用仅支持 geometry 数据类型优势在于范围查找效率较低通常使用搜索引擎如 ElasticSearch 代替。全文索引对文本的内容进行分词进行搜索。目前只有 CHAR、VARCHAR TEXT 列上可以创建全文索引。一般不会使用效率较低通常使用搜索引擎如 ElasticSearch 代替。
按照底层存储方式角度划分
聚簇索引聚集索引索引结构和数据一起存放的索引InnoDB 中的主键索引就属于聚簇索引。非聚簇索引非聚集索引索引结构和数据分开存放的索引二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎不管主键还是非主键使用的都是非聚簇索引。
按照应用维度划分
主键索引加速查询 列值唯一不可以有 NULL 表中只有一个。普通索引仅加速查询。唯一索引加速查询 列值唯一可以有 NULL。覆盖索引一个索引包含或者说覆盖所有需要查询的字段的值。联合索引多列值组成一个索引专门用于组合搜索其效率大于索引合并。全文索引对文本的内容进行分词进行搜索。目前只有 CHAR、VARCHAR TEXT 列上可以创建全文索引。一般不会使用效率较低通常使用搜索引擎如 ElasticSearch 代替。
MySQL 8.x 中实现的索引新特性
隐藏索引也称为不可见索引不会被优化器使用但是仍然需要维护通常会软删除和灰度发布的场景中使用。主键不能设置为隐藏包括显式设置或隐式设置。降序索引之前的版本就支持通过 desc 来指定索引为降序但实际上创建的仍然是常规的升序索引。直到 MySQL 8.x 版本才开始真正支持降序索引。另外在 MySQL 8.x 版本中不再对 GROUP BY 语句进行隐式排序。函数索引从 MySQL 8.0.13 版本开始支持在索引中使用函数或者表达式的值也就是在索引中可以包含函数或者表达式。
主键索引(Primary Key)
数据表的主键列使用的就是主键索引。
一张数据表有只能有一个主键并且主键不能为 null不能重复。
在 MySQL 的 InnoDB 的表中当没有显示的指定表的主键时InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段如果有则选择该字段为默认的主键否则 InnoDB 将会自动创建一个 6Byte 的自增主键。 主键索引
二级索引
二级索引Secondary Index的叶子节点存储的数据是主键的值也就是说通过二级索引可以定位主键的位置二级索引又称为辅助索引/非主键索引。
唯一索引普通索引前缀索引等索引都属于二级索引。
PS: 不懂的同学可以暂存疑慢慢往下看后面会有答案的也可以自行搜索。
唯一索引(Unique Key):唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据但是允许数据为 NULL一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性而不是为了查询效率。普通索引(Index):普通索引的唯一作用就是为了快速查询数据一张表允许创建多个普通索引并允许数据重复和 NULL。前缀索引(Prefix):前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引相比普通索引建立的数据更小因为只取前几个字符。全文索引(Full Text):全文索引主要是为了检索大文本数据中的关键字的信息是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引5.6 之后 InnoDB 也支持了全文索引。
二级索引: 二级索引
聚簇索引与非聚簇索引
聚簇索引聚集索引
聚簇索引介绍
聚簇索引Clustered Index即索引结构和数据一起存放的索引并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。
在 MySQL 中InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据对于 InnoDB 引擎表来说该表的索引(B树)的每个非叶子节点存储索引叶子节点存储索引和索引对应的数据。
聚簇索引的优缺点
优点
查询速度非常快聚簇索引的查询速度非常的快因为整个 B树本身就是一颗多叉平衡树叶子节点也都是有序的定位到索引的节点就相当于定位到了数据。相比于非聚簇索引 聚簇索引少了一次读取数据的 IO 操作。对排序查找和范围查找优化聚簇索引对于主键的排序查找和范围查找速度非常快。
缺点
依赖于有序的数据因为 B树是多路平衡树如果索引的数据不是有序的那么就需要在插入时排序如果数据是整型还好否则类似于字符串或 UUID 这种又长又难比较的数据插入或查找的速度肯定比较慢。更新代价大如果对索引列的数据被修改时那么对应的索引也将会被修改而且聚簇索引的叶子节点还存放着数据修改代价肯定是较大的所以对于主键索引来说主键一般都是不可被修改的。
非聚簇索引非聚集索引
非聚簇索引介绍
非聚簇索引(Non-Clustered Index)即索引结构和数据分开存放的索引并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎不管主键还是非主键使用的都是非聚簇索引。
非聚簇索引的叶子节点并不一定存放数据的指针因为二级索引的叶子节点就存放的是主键根据主键再回表查数据。
非聚簇索引的优缺点
优点
更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了非聚簇索引的叶子节点是不存放数据的。
缺点
依赖于有序的数据:跟聚簇索引一样非聚簇索引也依赖于有序的数据可能会二次查询(回表):这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后可能还需要根据指针或主键再到数据文件或表中查询。
这是 MySQL 的表的文件截图: MySQL 表的文件
聚簇索引和非聚簇索引: 聚簇索引和非聚簇索引
非聚簇索引一定回表查询吗(覆盖索引)?
非聚簇索引不一定回表查询。
试想一种情况用户准备使用 SQL 查询用户名而用户名字段正好建立了索引。 SELECT name FROM table WHERE nameguang19;
那么这个索引的 key 本身就是 name查到对应的 name 直接返回就行了无需回表查询。
即使是 MYISAM 也是这样虽然 MYISAM 的主键索引确实需要回表因为它的主键索引的叶子节点存放的是指针。但是如果 SQL 查的就是主键呢? SELECT id FROM table WHERE id1;
主键索引本身的 key 就是主键查到返回就行了。这种情况就称之为覆盖索引了。
覆盖索引和联合索引
覆盖索引
如果一个索引包含或者说覆盖所有需要查询的字段的值我们就称之为 覆盖索引Covering Index 。
在 InnoDB 存储引擎中非主键索引的叶子节点包含的是主键的值。这意味着当使用非主键索引进行查询时数据库会先找到对应的主键值然后再通过主键索引来定位和检索完整的行数据。这个过程被称为“回表”。
覆盖索引即需要查询的字段正好是索引的字段那么直接根据该索引就可以查到数据了而无需回表查询。 如主键索引如果一条 SQL 需要查询主键那么正好根据主键索引就可以查到主键。再如普通索引如果一条 SQL 需要查询 namename 字段正好有索引 那么直接根据这个索引就可以查到数据也无需回表。 覆盖索引
我们这里简单演示一下覆盖索引的效果。
1、创建一个名为 cus_order 的表来实际测试一下这种排序方式。为了测试方便 cus_order 这张表只有 id、score、name这 3 个字段。 CREATE TABLE cus_order (id int(11) unsigned NOT NULL AUTO_INCREMENT,score int(11) NOT NULL,name varchar(11) NOT NULL DEFAULT ,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT100000 DEFAULT CHARSETutf8mb4;
2、定义一个简单的存储过程PROCEDURE来插入 100w 测试数据。 DELIMITER ;;
CREATE DEFINERroot% PROCEDURE BatchinsertDataToCusOder(IN start_num INT,IN max_num INT)
BEGINDECLARE i INT default start_num;WHILE i max_num DOinsert into cus_order(id, score, name)values (i,RAND() * 1000000,CONCAT(user, i));SET i i 1;END WHILE;END;;
DELIMITER ;
存储过程定义完成之后我们执行存储过程即可 CALL BatchinsertDataToCusOder(1, 1000000); # 插入100w的随机数据
等待一会100w 的测试数据就插入完成了
3、创建覆盖索引并使用 EXPLAIN 命令分析。
为了能够对这 100w 数据按照 score 进行排序我们需要执行下面的 SQL 语句。
#降序排序
SELECT score,name FROM cus_order ORDER BY score DESC;
使用 EXPLAIN 命令分析这条 SQL 语句通过 Extra 这一列的 Using filesort 我们发现是没有用到覆盖索引的。 不过这也是理所应当毕竟我们现在还没有创建索引呢
我们这里以 score 和 name 两个字段建立联合索引 ALTER TABLE cus_order ADD INDEX id_score_name(score, name);
创建完成之后再用 EXPLAIN 命令分析再次分析这条 SQL 语句。 通过 Extra 这一列的 Using index 说明这条 SQL 语句成功使用了覆盖索引。
关于 EXPLAIN 命令的详细介绍请看MySQL 执行计划分析这篇文章。
联合索引
使用表中的多个字段创建索引就是 联合索引也叫 组合索引 或 复合索引。
以 score 和 name 两个字段建立联合索引 ALTER TABLE cus_order ADD INDEX id_score_name(score, name);
最左前缀匹配原则
最左前缀匹配原则指的是在使用联合索引时MySQL 会根据索引中的字段顺序从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配那么 MySQL 就会使用索引来过滤数据这样可以提高查询效率。
最左匹配原则会一直向右匹配直到遇到范围查询如 、为止。对于 、、BETWEEN 以及前缀匹配 LIKE 的范围查询不会停止匹配相关阅读联合索引的最左匹配原则全网都在说的一个错误结论。
假设有一个联合索引(column1, column2, column3)其从左到右的所有前缀为(column1)、(column1, column2)、(column1, column2, column3)创建 1 个联合索引相当于创建了 3 个索引包含这些列的所有查询都会走索引而不会全表扫描。
我们在使用联合索引时可以将区分度高的字段放在最左边这也可以过滤更多数据。
我们这里简单演示一下最左前缀匹配的效果。
1、创建一个名为 student 的表这张表只有 id、name、class这 3 个字段。
CREATE TABLE student (id int NOT NULL,name varchar(100) DEFAULT NULL,class varchar(100) DEFAULT NULL,PRIMARY KEY (id),KEY name_class_idx (name,class)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;
2、下面我们分别测试三条不同的 SQL 语句。 # 可以命中索引
SELECT * FROM student WHERE name Anne Henry;
EXPLAIN SELECT * FROM student WHERE name Anne Henry AND class lIrm08RYVk;
# 无法命中索引
SELECT * FROM student WHERE class lIrm08RYVk;
再来看一个常见的面试题如果有索引 联合索引abc查询 a1 AND c1会走索引么c1 呢b1 AND c1呢
先不要往下看答案给自己 3 分钟时间想一想。
查询 a1 AND c1根据最左前缀匹配原则查询可以使用索引的前缀部分。因此该查询仅在 a1 上使用索引然后对结果进行 c1 的过滤。查询 c1 由于查询中不包含最左列 a根据最左前缀匹配原则整个索引都无法被使用。查询b1 AND c1和第二种一样的情况整个索引都不会使用。
MySQL 8.0.13 版本引入了索引跳跃扫描Index Skip Scan简称 ISS它可以在某些索引查询场景下提高查询效率。在没有 ISS 之前不满足最左前缀匹配原则的联合索引查询中会执行全表扫描。而 ISS 允许 MySQL 在某些情况下避免全表扫描即使查询条件不符合最左前缀。不过这个功能比较鸡肋 和 Oracle 中的没法比MySQL 8.0.31 还报告了一个 bugBug #109145 Using index for skip scan cause incorrect result后续版本已经修复。个人建议知道有这个东西就好不需要深究实际项目也不一定能用上。
