中山 网站建设,核酸收费?,ai制作海报,个人网站做哪种类型由于MySQL是作为存储层部署在业务系统的最后端#xff0c;所有的业务数据最终都要入库落盘#xff0c;但随着一个项目在线上运行的时间越来越久#xff0c;数据库中的数据量自然会越来越多#xff0c;而数据体积出现增长后#xff0c;当需要从表查询一些数据时#xff0c… 由于MySQL是作为存储层部署在业务系统的最后端所有的业务数据最终都要入库落盘但随着一个项目在线上运行的时间越来越久数据库中的数据量自然会越来越多而数据体积出现增长后当需要从表查询一些数据时效率会越发低下。在正常情况下表的查询性能和数据量是成反比的也就是数据越多查询越慢。 这是什么原因导致的呢由于MySQL默认的查询方式导致的举个例子~ SELECT * FROM zz_student;
----------------------------------
| student_id | name | sex | height |
----------------------------------
| 1 | 竹子 | 男 | 185cm |
| 2 | 熊猫 | 女 | 170cm |
| 3 | 子竹 | 男 | 182cm |
| 4 | 棕熊 | 男 | 187cm |
| 5 | 黑豹 | 男 | 177cm |
| 6 | 脑斧 | 男 | 178cm |
| 7 | 兔纸 | 女 | 165cm |
----------------------------------SELECT * FROM zz_student WHERE name 脑斧;上面给出了一张学生表其中有七位学生信息而此时要查询姓名为「脑斧」的学生信息时MySQL底层是如何检索数据的呢会触发磁盘IO对表中的数据进行逐条读取并判断也就是说在这里想要查找到符合要求的数据至少要经过六次磁盘IO才能检索到目标暂且先不考虑局部性读取原理与随机IO。
那假设这个表中有1000W条数据呢要查的目标数据位于表的900W行以后怎么办岂不是要触发几百万次磁盘IO才能检索到数据啊如果真的这样去干其效率大家可想而知。 在这种情况下又该如何去提升数据库的查询性能呢因为查询往往都是一个业务系统中最频繁的操作一般项目的写/读请求比例都遵循三七定律也就是30%的请求会涉及到写库操作另外70%则属于查库类型的操作。 在思考如何提升查询性能前咱们不妨先回想一下小时候的场景小时候由于刚接触汉字很多字都不认识所以通常每个人小时候都会拥有一本「新华字典」但一本字典那么厚我们是一页页去翻的吗并不是字典中有目录索引我们可以根据音节、偏旁等方式查找不认识的字。 在「新华字典」中一页页翻找某个汉字就类似于我们前面给出的全表扫描方式效率特别特别低而通过目录索引则能够在很短的时间内找到目标汉字。 既然字典中都存在目录索引页能帮助小时候的我们快速检索汉字那这个思想能否应用到数据库中来呢答案是当然可以并且MySQL也提供了索引机制索引是数据库中的核心组件之一一张表中建立了合适的索引后往往在面对海量数据查询时能够事半功倍接下来一起聊一聊MySQL的索引。 索引机制会分为上、中、下三篇进行阐述大致内容如下 《上篇索引初识篇》主要讲解索引的概述、分类、使用与管理等 《中篇索引应用篇》主要阐述索引优劣分析、建立索引的原则、索引失效的场景、如何正确的使用索引、索引优化机制等 《下篇索引原理篇》则主要讲述索引的底层实现、BTree、Hash数据结构、聚簇索引和非聚簇索引实现、索引查询原理、索引管理实现等 一、MySQL索引机制概述 对于MySQL索引机制的作用经过上述「新华字典」的案例后可得知索引就是用来帮助表快速检索目标数据的。此时先来简单回顾一下MySQL中索引是如何使用的呢首先需要创建索引MySQL可以通过CREATE、ALTER、DDL三种方式创建一个索引。
1.1、MySQL索引的创建方式
①使用CREATE语句创建 CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);这种创建方式可以给一张已存在的表结构添加索引其中需要指定几个值
indexName当前创建的索引创建成功后叫啥名字。tableName要在哪张表上创建一个索引这里指定表名。columnName要为表中的哪个字段创建索引这里指定字段名。length如果字段存储的值过长选用值的前多少个字符创建索引。ASC|DESC指定索引的排序方式ASC是升序DESC是降序默认ASC。
当然上述语句中的INDEX也可更改为KEY作用都是创建一个普通索引而对于其他的索引类型这点在后续的索引分类中再聊。
②使用ALTER语句创建
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);这里的参数都相同所以不再重复赘述。
③建表时DDL语句中创建
CREATE TABLE tableName( columnName1 INT(8) NOT NULL, columnName2 ....,.....,INDEX [indexName] (columnName(length))
);这种方式就比较适合在库表设计时已经确定了索引项的情况下建立。
1.2、查询、删除、指定索引
但不管通过哪种方式建立索引本质上创建的索引都是相同的当索引创建完成后可通过SHOW INDEX FROM tableName;这条命令查询一个表中拥有的索引如下 CREATE TABLE zz_user (user_id int(8) NOT NULL AUTO_INCREMENT,user_name varchar(255) NULL DEFAULT ,user_sex varchar(255) NULL DEFAULT ,user_phone varchar(255) NULL DEFAULT ,PRIMARY KEY (user_id) USING BTREE
)ENGINE InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT Compact;在上述的建表SQL中为user_id创建了一个主键索引然后来查一下当前表的索引信息
简单的概述一下查询后每个字段的含义
①Table当前索引属于那张表。②Non_unique目前索引是否属于唯一索引0代表是的1代表不是。③Key_name当前索引的名字。④Seq_in_index如果当前是联合索引目前字段在联合索引中排第几个。⑤Column_name当前索引是位于哪个字段上建立的。⑥Collation字段值以什么方式存储在索引中A表示有序存储NULL表无序。⑦Cardinality当前索引的散列程度也就是索引中存储了多少个不同的值。⑧Sub_part当前索引使用了字段值的多少个字符建立NULL表示全部。⑨Packed表示索引在存储字段值时以什么方式压缩NULL表示未压缩⑩Null当前作为索引字段的值中是否存在NULL值YES表示存在。⑪Index_type当前索引的结构BTREE, FULLTEXT, HASH, RTREE。⑫Comment创建索引时是否对索引有备注信息。
这条命令在后续排除问题、性能调优时会有不小的作用比如可以通过分析其中的Cardinality字段值如果该值少于数据的实际行数那目前索引有可能失效对于这些后续排查篇和SQL优化篇再聊。 OK~到这里了解了一下索引相关的创建、查询命令接着再看看删除、强制使用命令。 在MySQL中并未提供修改索引的命令也就说当你建错了索引只能先删再重新建立一次删除索引的语句如下 DROP INDEX indexName ON tableName;当然当建立了一条索引后也可以强制性的为SELECT语句指定索引如下
SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....;FORCE INDEX关键字可以为一条查询语句强制指定走哪个索引查询但要牢记的是如果当前的查询SQL压根不会走指定的索引字段哪这种方式是行不通的这个关键字的用法是一条查询语句在有多个索引可以检索数据时显式指定一个索引减少优化器选择索引的耗时。 但要注意如果你对于你整个业务系统十分熟悉那可以这样干。但如果不熟悉的话还是交给优化器来自行选择否则会适得其反 1.3、数据库索引的本质 前面一直在聊创建、查看、删除、指定等一些索引的基本操作但索引本质上在数据库中是什么呢大家都知道数据库是基于磁盘工作的所有的数据都会放到磁盘上存储而索引也是数据的一种因此与表数据相同最终创建出的索引也会在磁盘生成本地文件。 不过索引文件在磁盘中究竟以何种方式存储这是由索引的数据结构来决定的。同时由于索引机制最终是由存储引擎实现因此不同存储引擎下的索引文件其保存在本地的格式也并不相同。 在这里有一个点需要注意建立索引的工作在表数据越少时越好如果你想要给一张百万、千万条数据级别的表新创建一个索引那创建的耗时也不短这是为什么呢 因为刚刚聊过索引本质上和表是一样的都是磁盘中的文件那也就代表着创建一个索引并不像单纯的给一张表加个约束那么简单而是会基于原有的表数据重新在磁盘中创建新的本地索引文件。假设表中有一千万条数据那创建索引时就需要将索引字段上的1000W个值全部拷贝到本地索引文件中同时做好排序并与表数据产生映射关系。 OK~至此就对MySQL提供的索引机制做了简单回顾下面再来说说数据库中“多样化”的索引类型。
