mysql索引

索引概述

  • 建立数据库的目的是管理大量数据,而建立索引的目的就是提高数据检索效率,改善数据库工作性能,提高数据访问速度
  • 索引是数据库管理系统中一个满足特定查找算法(排序)的数据结构,这些数据结构以某种方式引用(指向)数据,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树

  • 一般来说索引本身也很大,不可能全部存储咋内存中,需要占用一定的磁盘空间,因此索引往往以索引文件的形式存储在磁盘上(索引是一个文件,保存在物理空间中)
  • 没有索引的sql语句,会进行全表扫描,从上到下依次查询,直到找到符合条件的数据,效率是非常低的
  • 索引页中包含着索引键值和它所指向该行记录在数据页中的物理位置,叫做行定位符(RID:Row ID)
  • 一定要在“加快查询速度”与“降低修改速度”之间做好平衡

索引的优缺点

优势:

  • 类似于书籍的目录索引,提高数据的检索效率,减低数据的IO压力
  • 通过索引对数据进行排序,减低数据排序的成本,降低CPU的消耗

劣势:

  • 索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所有索引列也是要占用空间的
  • 创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低增/改/删的执行效率

索引的使用场景

  • 对查询频次较高,且数据量比较大的表建立索引,
  • 最左前缀匹配原则,组合索引非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整
  • 利用最左前缀,N个列组合而成的组合索引,那么相当于是创建N个索引,如果where子句中使用了该索引的前几个字段,那么这条查询sql可以利用组合索引来提升效率
  • 使用唯一索引,区分度越高,使用索引的效率越高,若是不能有效区分数据的列不适合做索引列
  • 更新频繁字段不适合创建索引,索引可以有效提升查询数据的效率,但索引数量不是越多越好,索引越多,维护索引的代价就越大,对于insert,update,delete操作较多的表来说,索引过多,会引入相当高的维护代价,减低DML操作的效率,另外索引过多,Mysql选择索引时也会影响效率
  • 使用短索引,索引创建之后也是使用硬盘来存储的,短索引可以在特定大小的空间内存储更多的索引值,相应的可以提升Mysql访问索引的I/O效率,也可以提升整体的访问效率
  • 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可
  • 定义有外键的数据列一定要建立索引
  • 对于定义为text、image和bit的数据类型的列不要建立索引
  • 表中记录太少不适合建立索引

索引结构

索引的数据结构和具体存储引擎的实现有关,在MySQL中使用较多的索引有Hash索引B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引。对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快;其余大部分场景,建议选择BTree索引

索引是在Mysql的存储引擎层中实现的,而不是在服务层,所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型

InnoDB引擎主要支持的BTREE索引

索引的分类

主键索引:数据列不允许重复,不允许为NULL,一个表只能有一个主键。

唯一索引:索引列的值必须唯一,但允许有空值,NULL值可以出现多次

普通索引:基本的索引类型,没有唯一性的限制,允许为NULL值

其中唯一索引和普通索引可以有单列或者多列组成

覆盖索引:就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖

聚集索引和非聚集索引

        从形式上而言,索引分为聚集索引(Clustered Indexes)和非聚集索引(NonClustered Indexes)。聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序,在非聚集索引中,数据库表中记录的物理顺序与索引顺序可以不相同。

        聚集索引相当于书籍脊背上那个特定的编号。如果对一张表建立了聚集索引,其索引页中就包含着建立索引的列的值(下称索引键值),那么表中的记录将按照该索引键值进行排序。比如,我们如果在“姓名”这一字段上建立了聚集索引,则表中的记录将按照姓名进行排列;如果建立了聚集索引的列是数值类型的,那么记录将按照该键值的数值大小来进行排列。

        非聚集索引用于指定数据的逻辑顺序,也就是说,表中的数据并没有按照索引键值指定的顺序排列,而仍然按照插入记录时的顺序存放。其索引页中包含着索引键值和它所指向该行记录在数据页中的物理位置,叫做行定位符(RID:Row ID)。好似书后面的的索引表,索引表中的顺序与实际的页码顺序也是不一致的。而且一本书也许有多个索引。比如主题索引和作者索引。

        SQL Server在默认的情况下建立的索引是非聚集索引,由于非聚集索引不对表中的数据进行重组,而只是存储索引键值并用一个指针指向数据所在的页面。一个表如果没有聚集索引时,理论上可以建立249个非聚集索引。每个非聚集索引提供访问数据的不同排序顺序。

        与非聚集索引相比,聚集索引通常提供更快的数据访问速度。聚集索引更适用于对很少对基表进行增删改操作的情况。聚集索引表记录的排列顺序与索引的排列顺序一致,优点是查询速度快,因为一旦具有第一个索引值的纪录被找到,具有连续索引值的记录也一定物理的紧跟其后(聚合索引存储记录是物理上连续存在的)。聚集索引的缺点是对表进行修改速度较慢,这是为了保持表中的记录的物理顺序与索引的顺序一致,而把记录插入到数据页的相应位置,必须在数据页中进行数据重排,降低了执行速度。非聚集索引指定了表中记录的逻辑顺序,但记录的物理顺序和索引的顺序不一致,聚集索引和非聚集索引都采用了B+树的结构,但非聚集索引的叶子层并不与实际的数据页相重叠,而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。非聚集索引比聚集索引层次多,添加记录不会引起数据顺序的重组。

索引的基本原理

 

 

 

索引语法

  • 创建表时建索引
CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);
  • 使用ALTER TABLE命令去增加索引,ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
  • 使用CREATE INDEX命令创建,CREATE INDEX可对表增加普通索引或UNIQUE索引。(但是,不能创建PRIMARY KEY索引)
CREATE INDEX index_name ON table_name (column_list);
  • 根据索引名删除普通索引、唯一索引、全文索引:alter table 表名 drop KEY 索引名

创建索引需要注意点

  • 非空字段:应该指定列为NOT NULL,除非你想存储NULL。在mysql中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值;
  • 取值离散大的字段:(变量各个取值之间的差异程度)的列放到联合索引的前面,可以通过count()函数查看字段的差异值,返回值越大说明字段的唯一值越多字段的离散程度高;
  • 索引字段越小越好:数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高
  • 有索引且查询条件能使用索引时,数据库会先度取索引,根据索引内容和查询条件,查询出定位符(ROWID),再根据ROWID取出需要的数据。由于索引内容通常比全表内容要少很多,因此通过先读索引,能减少I/O,提高查询性能
  • 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

视图

视图(view)是一种虚拟存在的表,视图并不在数据中真实存在,通俗的讲,视图就是一条SELECT语句执行之后的结果集,视图相对于普通的表的优势主要包括以下几列

简单:使用视图的用户完全不需要关心后面对应的表结构,关联条件,筛选条件,对用来说已经是过滤好的结果集

安全:使用视图的用户只能访问他们被允许的结果集,相当于对表的列实现了权限管理

数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列不会影响视图的使用,源表列名改变,则可以通过修改视图来解决,不会造成对访问这的影响

 

 

限时福利1:原价 129 元,最后2天仅需 69 元!后天涨价至98元 限时福利2:购课进答疑群专享柳峰(刘运强)老师答疑服务 限时福利3:购课添加助教领取价值 800 元的编程大礼包 为什么需要掌握高性能的MySQL实战? 由于互联网产品用户量大、高并发请求场景多,因此对MySQL的性能、可用性、扩展性都提出了很高的要求。使用MySQL解决大量数据以及高并发请求已经是程序员的必备技能,也是衡量一个程序员能力和薪资的标准之一。 为了让大家快速系统了解高性能MySQL核心知识全貌,我为你总结了「高性能 MySQL 知识框架图」,帮你梳理学习重点,建议收藏! 【课程设计】 课程分为四大篇章,将为你建立完整的 MySQL 知识体系,同时将重点讲解 MySQL 底层运行原理、数据库的性能调优、高并发、海量业务处理、面试解析等。 一、性能优化篇: 主要包括经典 MySQL 问题剖析、索引底层原理和事务与锁机制。通过深入理解 MySQL索引结构 B+Tree ,学员能够从根本上弄懂为什么有些 SQL 走索引、有些不走索引,从而彻底掌握索引的使用和优化技巧,能够避开很多实战中遇到的“坑”。 二、MySQL 8.0新特性篇: 主要包括窗口函数和通用表表达式。企业中的许多报表统计需求,如果不采用窗口函数,用普通的 SQL 语句是很难实现的。 三、高性能架构篇: 主要包括主从复制和读写分离。在企业的生产环境中,很少采用单台MySQL节点的情况,因为一旦单个节点发生故障,整个系统都不可用,后果往往不堪设想,因此掌握高可用架构的实现是非常有必要的。 四、面试篇: 程序员获得工作的第一步,就是高效的准备面试,面试篇主要从知识点回顾总结的角度出发,结合程序员面试高频MySQL问题精讲精练,帮助程序员吊打面试官,获得心仪的工作机会。
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页