网站开发原则一级造价师停考最新消息

网站开发原则,一级造价师停考最新消息,中牟做网站,中文电子商务网站模板1. 什么是索引 一种能帮助mysql提高查询效率的数据结构#xff1a;索引数据结构索引优点#xff1a; 大大提高数据查询速度 索引缺点#xff1a; 维护索引需要耗费数据库资源索引要占用磁盘空间当对表的数据进行增删改的时候#xff0c;因为要维护索引#xff0c;所以速度…1. 什么是索引一种能帮助mysql提高查询效率的数据结构索引数据结构索引优点大大提高数据查询速度索引缺点维护索引需要耗费数据库资源索引要占用磁盘空间当对表的数据进行增删改的时候因为要维护索引所以速度收到影响结合索引的优缺点得出结论数据库表并不是索引加的越多越好而是仅为那些常用的搜索字段建立索引效果才是最佳的2. 索引的分类主键索引PRIMARY KEY设定为逐渐后数据库自动建立索引innodb为聚簇索引主键索引列值不能有空(Null)单值索引又叫单列索引、普通索引即一个索引只包含单个列一个表可以有多个单列索引唯一索引索引列的值必须唯一但允许有空值(Null)但只允许有一个空值(Null)复合索引即一个索引可以包含多个列多个列共同构成一个复合索引eg:SELECT id (name age) INDEX WHERE name AND age;全文索引Full Text MySQL5.7之前只有MYISAM存储引擎支持全文索引全文索引类型为FULLTEXT在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在Char、Varchar上创建。3. 索引的基本操作3.1 主键索引创建-- 建表语句建表时设置主键自动创建主键索引 CREATE TABLE t_user ( id VARCHAR(20) PRIMARY KEY, name VARCHAR(20) ); -- 查看索引 SHOW INDEX FROM t_user;AI写代码sql篇幅限制下面就只能给大家展示小册部分内容了。整理了一份核心面试笔记包括了Java面试、Spring、JVM、MyBatis、Redis、MySQL、并发编程、微服务、Linux、Springboot、SpringCloud、MQ、Kafc需要全套面试笔记及答案【点击此处即可/免费获取】​https://docs.qq.com/doc/DQXdYWE9LZ2ZHZ1ho3.2 单列索引创建(普通索引/单值索引)-- 建表时创建单列索引 -- 这种方式创建单列索引其名称默认为字段名称name CREATE TABLE t_user ( id VARCHAR(20) PRIMARY KEY, name VARCHAR(20) KEY(name) ); -- 建表后创建单列索引 -- 索引名称为name_index 格式--- 字段名称_index CREATE INDEX name_index ON t_user(name) -- 删除单列索引 DROPINDEX 索引名称 ON 表名AI写代码sql3.3 唯一索引创建-- 建表时创建唯一索引 CREATE TABLE t_user2 ( id VARCHAR(20) PRIMARY KEY, name VARCHAR(20), UNIQUE(name) ); -- 建表后创建唯一索引 CREATE UNIQUE INDEX name_index ON t_user2(name);AI写代码sql3.4 复合索引创建-- 建表时创建复合索引 CREATE TABLE t_user3 ( id VARCHAR(20) PRIMARY KEY, name VARCHAR(20), age INT, KEY(name,age) ); -- 建表后创建复合索引 CREATE INDEX name_age_index ON t_user3(name,age); -- 复合索引查询的2个原则 -- 1.最左前缀原则 -- eg: 创建复合索引时字段的顺序为 name,age,birthday -- 在查询时能利用上索引的查询条件为 SELECT * FROM t_user3 WHERE name ? SELECT * FROM t_user3 WHERE name ? AND age ? SELECT * FROM t_user3 WHERE name ? AND birthday ? SELECT * FROM t_user3 WHERE name ? AND age ? AND birthday ? -- 而其他顺序则不满足最左前缀原则 ... WHERE name ? AND birthday ? AND age ? -- 不满足最左前缀原则 ... WHERE name ? AND birthday ? -- 不满足最左前缀原则 ... WHERE birthday ? AND age ? AND name ? -- 不满足最左前缀原则 ... WHERE age ? AND birthday ? -- 不满足最左前缀原则 -- 2.MySQL 引擎在执行查询时为了更好地利用索引在查询过程中会动态调整查询字段的顺序 -- 这时候再来看上面不满足最左前缀原则的四种情况 -- 不满足最左前缀原则但经过动态调整顺序后变为name age birthday 可以利用复合索引 ... WHERE name ? AND birthday ? AND age ? -- 不满足最左前缀原则也不能动态调整因为缺少age字段不可以利用复合索引 ... WHERE name ? AND birthday ? -- 不满足最左前缀原则但经过动态调整顺序后变为name age birthday 可以利用复合索引 ... WHERE birthday ? AND age ? AND name ? -- 不满足最左前缀原则也不能动态调整因为缺少name字段不可以利用复合索引 ... WHERE age ? AND birthday ?AI写代码sql4. MySQL索引的数据结构(BTree)-- 建表 CREATE TABLE t_emp( id INT PRIMARY KEY, name VARCHAR(20), age INT ); -- 插入数据插入时主键无序 INSERT INTO t_emp VALUES(5,d,22); INSERT INTO t_emp VALUES(6,d,22); INSERT INTO t_emp VALUES(7,3,21); INSERT INTO t_emp VALUES(1,a,23); INSERT INTO t_emp VALUES(2,b,26); INSERT INTO t_emp VALUES(3,c,27); INSERT INTO t_emp VALUES(4,a,32); INSERT INTO t_emp VALUES(8,f,53); INSERT INTO t_emp VALUES(9,b,13); -- 查询自动排序有序展示因为主键是有主键索引的因此会自动排序AI写代码sql问题为什么数据插入时未按照主键顺序而查询时却是有序的呢原因MySQL底层为主键自动创建索引一旦创建了索引就会进行排序实际上这些数据在MySQL底层的真正存储结构变成了下面这种方式问题为什么要排序呢因为排序之后查询效率就快了比如查询id 3的数据只需要按照顺序去找即可而如果不排序就如同大海捞针假如100W条数据可能有时候需要随机查询100W次才找到这个数据也可能运气好上来第1次就查询到了该数据不确定性太高4.1 原理分析图上图这种分层树结构查询效率较高因为如果我需要查询id4的数据只需要在页目录中匹配大于3且小于5则去3对应的page2中查找数据这样就不需要从第1页开始检索数据了大大提高了效率从上图可得出在只有2层的结构下1page 可以存储记录总数为1365 * 455 ≈ 62万条而如果再加1层结构来存储page层分页目录数据的分页层PAGE的话那么1PAGE可以存储总page数为1365 * 1365 ≈ 186万条page而1PAGE存储的总记录数为1365 * 1365 * 455 ≈ 8.5 亿条。因此我们平时使用的话2层结构就已经足够了实际上1个页存储的总数据树可能大于理论估计的因为我们分配name字段的VARCHAR(20)占20个字节而实际上可能存储的name数据并没有20个字节可能更小三层结构实例如图4.2 B树结构分析上图4.1 原理分析图中这种索引结构称之为B树数据结构那么什么是B树呢B树和B树区别是什么呢?篇幅限制下面就只能给大家展示小册部分内容了。整理了一份核心面试笔记包括了Java面试、Spring、JVM、MyBatis、Redis、MySQL、并发编程、微服务、Linux、Springboot、SpringCloud、MQ、Kafc需要全套面试笔记及答案【点击此处即可/免费获取】​https://docs.qq.com/doc/DQXdYWE9LZ2ZHZ1ho问题4.2.1 为什么InnoDB底层使用B树做索引而不用B树B树结构图从上面的B树结构图中分析得出B树每个节点中不仅包含数据的key还有data数据。而每个页的存储空间是有限的如果data数据较大时讲会导致每个节点(即一个页16KB)能存储的key的数量较少当存储数据量很大时会造成B树的深度较大增大查询时的磁盘读取I/O次数进而影响查询效率。(树的深度影响I/O读取次数)在上一小节的B树结构图分析中所有数据记录都是按照键值大小顺序存放在同一层的叶子节点上而非叶子节点上只能存储key值信息这样可以大大增加每个节点(即一个页16KB)能存储的key的数量进而可以降低树的高度进而减少磁盘读取I/O次数提高查询效率所以B树和B树的区别就在于B树只有叶子节点存储数据记录B树非叶子节点只存储键值信息B树的非叶子也存数据记录所有节点直接都有一个链指针InnoDB存储引擎中页的大小为16KB一般表的主键类型为INT(占用4个字节) 或 BIGINT(占用8个字节)指针类型也一般占4或8个字节也就是说一个页(B树中的一个节点)中大概可以存储16KB/(8B8B)1000个键值(只是估计值方便计算而已)。也就是说一个深度为3的B树索引可以维护10^3 * 10^3 * 10^3 10亿条记录。实际情况中每个节点可能不能填充满因此在数据库中B树的高度一般是24层**。**MySQL的InnoDB存储引擎在设计时是将根节点常驻在内存中不需要动磁盘I/O**的也就是说**查找某个键值的行记录最多只需要13次I/O操作每查询一层都需要动用一次磁盘I/O5. 聚簇索引和非聚簇索引5.1 聚簇索引和非聚簇索引分析在表中聚簇索引实际上就是指的是主键索引如果表中没有主键的话则MySQL会根据该表生成一个RoleID拿这个RoleId当做聚簇索引聚簇索引将数据存储与索引放到一起索引结构的叶子节点保存了每行的数据。例如4.1小结分析图中的data层一个单位就是聚簇索引存储数据的例子主键id字段就是聚簇索引4.1小结分析图就是基于主键索引(聚簇索引)构成的B树结构聚簇索引不一定是主键索引但是主键索引肯定是聚簇索引非聚簇索引将数据与索引分开存储索引结构的叶子节点指向了数据对应的位置(聚簇索引的值)非聚簇索引检索数据是在自己的 “树” 上进行查找例如我们根据表中的非聚簇索引name字段去查找数据时流程如下图再看一张比较正规的分析图注意在InnoDB中在聚簇索引之上创建的索引称之为辅助索引例如复合索引、单列索引、唯一索引。一个表中只能有1个聚簇索引而其他索引都是辅助索引辅助索引的叶子节点存储的不再是行的物理位置而是主键的值辅助索引访问数据总是需要二次查找的**问题5.1.1 **为什么非聚簇索引(name字段的单列索引)构成的树其叶子节点存储聚簇索引(主键id)而不直接存储行数据的物理地址呢换个方式问非聚簇索引检索数据时检索一次本树再去聚簇索引树中检索一次这样二次检索树结构那么为什么不直接在非聚簇索引树叶子节点中存放行数据物理地址这样只需要检索一次树结构就拿到行数据呢这里画个图方便理解一些​ 从上图得出在做新增数据时因为底层是需要基于主键索引进行排序的那么就可能导致原来某些数据对应的物理地址发生了变化而这时候由于我们的非聚簇索引树的叶子节点直接存储了数据的物理地址所以为了保证能获取到数据还需要同时对非聚簇索引树叶子节点的地址进行一遍更新修改​ 同理如果我们不做插入主键id为4这行记录的操作而是将其删除的话这个流程可以自己思考一下​ 也就是说之所以不在非聚簇索引树的叶子节点直接存放行数据的物理地址是因为存储数据的物理地址会随着数据库表的CRUD操作而不断变更为了保证能获取到数据这时必须要对非聚簇索引树相关叶子节点的地址进行一遍修改而存主键主键不会随着CRUD操作发生变化宁愿多查一次树也不要再修改一次树的结构5.2 MySQL两种引擎中的(非)聚簇索引InnoDB中InnoDB中使用的是聚簇索引将主键组织到一颗B树中而行数据就存储在该B树的叶子节点上若使用WHERE id 4这样的条件查找主键则按照B树的检索算法即可查找对应的叶子节点之后获得对应的行数据若对使用单列索引(非聚簇索引)的name字段进行搜索则需要执行2个步骤第一步在辅助索引B树中检索name到达其对应的叶子节点后获得该字段对应行记录的主键id第二步使用主键id在主索引B树中再次执行一次树的检索最终到达对应的叶子节点并获取到行记录数据聚簇索引默认是主键如果表中没有定义主键InnoDB会选择一个唯一且非空的索引代替主键作为聚簇索引。而如果也没有这样的唯一非空索引那么InnoDB就会隐式定义一个主键类似于Oracle中的RowId来做为聚簇索引。如果已经设置了聚簇索引又希望再单独设置聚簇索引则必须先删除主键然后添加我们想要的聚簇索引最后再恢复主键即可MYISAM中MYISAM使用的是非聚簇索引非聚簇索引的两颗B树看上去没有什么不同节点的结构完全一致只是存储的内容不同主键索引B树的节点存储了主键辅助索引B树存储量辅助键。表数据存储在独立的地方这两颗B树的叶子节点都使用一个地址指针指向真正的表数据对于表数据来说这两个键没有任何差别。由于索引树是独立的通过辅助键检索无需再次检索主键索引树5.3 聚簇索引和非聚簇索引的优/劣势问题5.3.1 使用聚簇索引的优势问题每次使用辅助索引检索都需要经过2次B树查找看上去聚簇索引的效率明显要低于非聚簇索引那么聚簇索引的优势何在呢-- 1.由于行数据和聚簇索引树的叶子节点存储在一起同一页中会有多条行数据首次访问数据页中某条行记录时会把该数据页数据加载到Buffer(缓存器)中当再次访问该数据页中其他记录时不必访问磁盘而直接在内存中完成访问。 -- 注主键id和行数据一起被载入内存找到对应的叶子节点就可以将行数据返回了如果按照主键id来组织数据获取数据效率更快 -- 2.辅助索引的叶子节点存储主键的值而不是行数据的存放地址。这样做的好处是因为叶子节点存放的是主键值其占据的存储空间小于存放行数据物理地址的储存空间AI写代码sql1234问题5.3.2 使用聚簇索引需要注意什么-- 当使用主键为聚簇索引时而不要使用UUID方式因为UUID的值太过离散不适合排序导致索引树调整复杂度增加消耗更多时间和资源。 -- 建议主键最好使用INT/BIGINT类型且为自增这样便于排序且默认会在索引树的末尾增加主键值对索引树的结构影响最小(下面主键自增的问题会解释原因)。而且主键占用的存储空间越大辅助索引中保存的主键值也会跟着增大占用空间且影响IO操作读取数据AI写代码sql123问题5.3.3 为什么主键通常建议使用自增id-- 聚簇索引树存放数据的物理地址(xx1,xx2,xx3,xxx5)与索引顺序(1,2,3,5)是一致的即 -- 1.只要索引是相邻的那么在磁盘上索引对应的行数据存放地址也是相邻的。 -- 2.如果主键是自增那么当插入新数据时只需要按照顺序在磁盘上开辟新物理地址存储新增行数据即可。 -- 3.而如果不是主键自增那么当新插入数据后会对索引进行重新排序(重新调整B树结构)磁盘上的物理存储地址也需要重新分配要存储的行数据AI写代码sql问题5.3.4 什么情况下无法利用索引呢-- 1. 查询语句中使用LIKE关键字这种情况主要是针对于单列索引 -- 在使用LIKE关键字查询时如果匹配字符串的第一个字符为%则索引不会被使用而%不在最左边而是在右边则索引会被使用到 -- eg: SELECT * FROM t_user WHERE name LIKE xx% -- 可以利用上索引,这种情况下可以拿xx到索引树上去匹配 SELECT * FROM t_user WHERE name LIKE %xx% -- 不可以利用上索引 SELECT * FROM t_user WHERE name LIKE %xx -- 不可以利用上索引 -- 2. 查询语句中使用多列索引这种情况主要是针对于聚合索引 -- 多索引是在表的多个字段创建索引只有查询条件中使用了这些字段中的第一个字段索引才会被使用。即最左前缀原则详情查看3.4小结聚合索引中的介绍 -- 3. 查询语句中使用OR关键字 -- 查询条件中有OR关键字时如果OR前后的两个条件列都具有索引则查询中索引将被使用而如果OR前后有一个或2个列不具有索引那么查询中索引将不被使用到AI写代码sql篇幅限制下面就只能给大家展示小册部分内容了。整理了一份核心面试笔记包括了Java面试、Spring、JVM、MyBatis、Redis、MySQL、并发编程、微服务、Linux、Springboot、SpringCloud、MQ、Kafc需要全套面试笔记及答案【点击此处即可/免费获取】​https://docs.qq.com/doc/DQXdYWE9LZ2ZHZ1ho6. 什么是约束以及分类约束:作用是为了保证数据的完整性而实现的摘自一套机制即(约束是针对表中数据记录的)MySQL中的约束非空约束NOT NULL保证某列数据不能存储NULL 值;唯一约束UNIQUE(字段名)保证所约束的字段数据必须是唯一的允许数据是空值(Null)但只允许有一个空值(Null)主键约束PRIMARY KEY(字段名)主键约束 非空约束 唯一约束保证某列数据不能为空且唯一外键约束FOREIGN KEY(字段名)保证一个表中某个字段的数据匹配另一个表中的某个字段可以建立表与表直接的联系自增约束AUTO_INCREMENT保证表中新插入数据时某个字段数据可以依次递增默认约束DEFALUT保证表中新插入数据时如果某个字段未被赋值则会有默认初始化值检查性约束CHECK保证列中的数据必须符合指定的条件示例create table member( id int(10), phone int(15) unsigned zerofill, name varchar(30) not null, constraint uk_name unique(name), constraint pk_id primary key (id), constraint fk_dept_id foreign key (dept_id字段2) references dept(主表1)(dept_id) );AI写代码sql7. MySQL索引和约束的区别索引的作用索引用于快速定位特定数据提高查询效率的。约束的作用约束是为了保证数据的完整性即约束是针对表中数据记录的。总结约束是为了保证表数据的完整性索引是为了提高查询效率两者作用不一样种类也不太一样MySQL索引相关面试题1.MySQL索引分类并对比区别答案参考第2小结2.复合索引查询时字段排列的先后顺序与创建索引时不同能否成功利用索引查询?考察点复合索引的最左前缀原则-- 假设构成复合索引的字段为 name,age,birthday -- 则下面那种情况可以使用成功利用复合索引查询 ... WHERE name ? -- 可以利用 ... WHERE name ? AND age ? -- 可以利用 ... WHERE name ? AND birthday ? -- 可以利用 ... WHERE name ? AND age ? AND birthday ? -- 可以利用 ... WHERE name ? AND birthday ? AND age ? -- 不满足最左前缀原则但经过动态调整后可以利用 ... WHERE birthday ? AND age ? AND name ? -- 不满足最左前缀原则但经过动态调整后可以利用 ... WHERE age ? AND birthday ? -- 不满足最左前缀原则不能动态调整不能利用复合索引AI写代码sql3.MySQL索引的数据结构是什么MySQL索引数据结构: B TreeB树聚簇索引~4.MySQL中索引和约束的区别以及各自种类MySQL索引与约束5.为什么InnoDB底层使用B树做索引而不用B树答案参考: 4.2小结B树结构分析中的问题4.2.16.什么是聚簇索引和非聚簇索引答案参考5.1小结聚簇索引和非聚簇索引分析7.为什么非聚簇索引构成的树的叶子节点存储聚簇索引(或主键)而不直接存储数据的物理地址呢答案参考5.1小结的问题5.1.18.使用聚簇索引需要注意什么答案参考5.3小结的问题5.3.29.为什么主键通常建议使用自增id答案参考5.3小结的问题5.3.310.什么情况下无法利用索引呢答案参考5.3小结的问题5.3.411.聚簇索引相对于非聚簇索引的优势是什么答案参考5.3小结的问题5.3.1