网站建设的调查问卷,网页链接加密,小型企业网站排名前十,上海网站制作的自学数据库-MYSQL 一.表和视图1.表1.1 表创建1.2 索引1.2.1 这里是废话,不感兴趣的可以直接更具目录的跳过这里的内容1.2.1.1 索引是什么1.2.1.2 相关数据结构#xff1a;二叉树、红黑树、B-Tree、BTree、Hash…①普通索引②唯一索引③全文索引④组合索引 1.3 表数据操作(更新… 自学数据库-MYSQL 一.表和视图1.表1.1 表创建1.2 索引1.2.1 这里是废话,不感兴趣的可以直接更具目录的跳过这里的内容1.2.1.1 索引是什么1.2.1.2 相关数据结构二叉树、红黑树、B-Tree、BTree、Hash…①普通索引②唯一索引③全文索引④组合索引 1.3 表数据操作(更新中)1.3.1 增(更新中)1.3.2 删(更新中)1.3.3 改(更新中)1.3.4 查(更新中)1.3.4.1 查询语句(更新中) 2.视图(更新中)2.1 视图创建(更新中) 二.存储过程/函数/触发器1.存储过程1.1 存储创建(语法) 2.函数2.1 函数创建(语法)3.1 触发器创建(语法)1.创建只有一个执行语句的触发器2.创建有多个执行语句的触发器3.查看触发器4.实例/注意 三.存储引擎1.InnoDB① 特点② 文件③ 逻辑存储结构 2.MyISAM① 特点② 文件 3.Memory① 特点② 文件 4.三种引擎区别及特点问题: 一.表和视图
维度表视图物理存在用于存储数据的物理结构基于表或多个表的查询结果集不具有独立的物理存在,是一个或多个表的逻辑表现它不包含数据只包含对表中数据的引用和操作规则。数据操作是数据的物理存储单元可以进行数据的增、删、改、查等操作只是提供了一个查看和操作表数据的特定角度和规则安全性它公开了所有数据用户的访问没有限制用户只能访问其被授权访问的表的部分数据这大大提高了数据的安全性抽象程度提供更底层、更直接的数据访问。视图是表的抽象它隐藏了表中的细节只展示用户关心的信息复杂查询只能执行单个表的简单查询可以在一个查询中组合多个表甚至可以使用复杂的SQL语句临时性和永久性是数据库中的永久性结构可以随时创建或删除没有永久性命名冲突–视图是逻辑结构可以创建具有相同名称但基于不同表的视图执行过程数据库引擎直接对表进行操作没有额外的计算成本数据库需要先执行视图的查询语句然后再对查询结果进行操作这增加了额外的计算成本索引优化通过创建索引来提高查询效率而视图则无法享受这种优化缺乏如表中的索引优化尤其是在使用histogram时优化效果更为明显数据存储和查询效率1.存储实际数据1.视图不存储数据只是一个查询结果的展示 2.需要进行额外的计算和查询操作从而影响性能简化操作-------------可以简化复杂的查询语句提高查询的可读性和维护性。通过使用视图可以隐藏复杂的查询逻辑使查询语句更加简洁提高安全性-------------通过视图可以对底层表进行权限控制只暴露需要的数据给用户从而提高数据的安全性降低耦合-------------如果需要修改原表的结构通过修改视图的定义即可而不需要修改应用程序这样可以降低应用程序与数据库之间的耦合度
1.表
1.1 表创建
CREATE TABLE table_name (column1 datatype [NOT NULL] [DEFAULT default_value],column2 datatype [NOT NULL] [DEFAULT default_value],...columnN datatype [NOT NULL] [DEFAULT default_value],PRIMARY KEY (column1, column2, ... columnN),UNIQUE KEY unique_key_name (column1, column2, ... columnN),FOREIGN KEY (column1, column2, ... columnN)REFERENCES parent_table (column1, column2, ... columnN)ON DELETE CASCADE | ON UPDATE CASCADE,INDEX index_name (column1, column2, ... columnN),...
) ENGINEstorage_engine;名称解释table_name是你想创建的表的名称column1, column2, …, columnN是表的列名称datatype是每列的数据类型NOT NULL表示列不能有NULL值。DEFAULT default_value设置列的默认值。PRIMARY KEY是你想创建的表的名称UNIQUE KEY是你想创建的表的名称FOREIGN KEY是你想创建的表的名称ON DELETE CASCADEON UPDATE CASCADEINDEX是你想创建的表的名称ENGINE指定存储引擎如InnoDB、MyISAM等。
CREATE TABLE sys_user (id INT NOT NULL AUTO_INCREMENT,username VARCHAR(50) NOT NULL comment 账号,password VARCHAR(50) NOT NULL comment 密码,email VARCHAR(100) NOT NULL comment 邮箱,created_at TIMESTAMP DEFAULT current_timestamp comment 创建时间,PRIMARY KEY (id),UNIQUE KEY unique_username (username),INDEX idx_email (email)
) ENGINEInnoDB comment 用户基础表 charset utf8mb3;1.2 索引
1.2.1 这里是废话,不感兴趣的可以直接更具目录的跳过这里的内容
1.2.1.1 索引是什么 1.索引是一种数据结构用来帮助提升查询和检索数据速度。可以理解为一本书的目录帮助定位数据位置。 2.索引是一个文件它要占用物理空间。 索引概述索引index是帮助MySQL高效获取数据的数据结构有序。在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。 1.2.1.2 相关数据结构二叉树、红黑树、B-Tree、BTree、Hash…
二叉树缺点
顺序插入时会形成一个链表查询性能大大降低。大数据量情况下层级较深检索速度慢。
红黑树:
大数据量情况下层级较深检索速度慢。红黑树是一颗自平衡二叉树那这样即使是顺序插入数据最终形成的数据结构也是一颗平衡的二叉树解决顺序插入形成链表的问题。但红黑树仍存在”大数据量情况下层级较深检索速度慢“所以在MySQL的索引结构中并没有选择二叉树或者红黑树而选择的是BTree
B-Tree多路平衡查找树也叫B树:
5阶的B树每一个节点最多存储4个key对应5个指针一旦节点存储的key数量到达5就会裂变中间元素向上分裂在B树中非叶子节点和叶子节点都会存放数据
BTree 相对于B-Tree区别
所有的数据都会出现在叶子节点叶子节点形成一个单向链表。非叶子节点不存储具体数据、只起到索引数据的作用具体的数据都是在叶子节点存放的 MySQL索引数据结构对经典的BTree进行了优化。在原BTree的基础上增加一个指向相邻叶子节点的链表指针就形成了带有顺序指针的BTree提高区间访问的性能利于排序。 B 树的叶子节点之间是用「双向链表」进行连接既能向右遍历、也能向左遍历B 树点节点内容是数据页数据页里存放了用户的记录以及各种信息每个数据页默认大小是 16 KB
Hash索引: 特点 1.Hash索引只能用于对等比较(in)不支持范围查询(between,,,…) 2.无法利用索引完成排序操作 3.查询效率高通常只需要一次检索就可以了效率通常要高于Btree索引 采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中。如果两个(或多个)键值映射到一个相同的槽位上他们就产生了hash冲突也称为hash碰撞可以通过链表来解决。 Hash索引只能用于对等比较(in)不支持范围查询(between,,,…)无法利用索引完成排序操作查询效率高通常只需要一次检索就可以了效率通常要高于Btree索引 1.磁盘读写的最小单位是扇区扇区的大小只有 512B 大小操作系统一次会读写多个扇区所以操作系统的最小读写单位是块Block。Linux 中的块大小为 4KB也就是一次磁盘 I/O 操作会直接读写 8 个扇区。 2.由于数据库的索引是保存到磁盘上的因此当我们通过索引查找某行数据的时候就需要先从磁盘读取索引到内存再通过索引从磁盘中找到某行数据然后读入到内存也就是说查询过程中会发生多次磁盘 I/O而磁盘 I/O 次数越多所消耗的时间也就越大。所以我们希望索引的数据结构能在尽可能少的磁盘的 I/O 操作中完成查询工作因为磁盘 I/O 操作越少所消耗的时间也就越小。 3.由于树是存储在磁盘中的访问每个节点都对应一次磁盘 I/O 操作假设一个节点的大小「小于」操作系统的最小读写单位块的大小也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数所以树的高度越高就会影响查询性能。 ①普通索引
(1).新建表中添加索引
create table t_dept
(no int not null primary key,name varchar(20) null,sex varchar(2) null,info varchar(20) null,index index_no (no)
)(2).在已建表中添加索引
create index index_name on t_dept (name);(3).以修改表的方式添加索引
alter table t_dept add index index_name(name);②唯一索引
(1).新建表中添加索引
create table t_dept
(no int not null primary key,name varchar(20) null,sex varchar(2) null,info varchar(20) null,unique index index_no (no)
)(2).在已建表中添加索引
create unique index index_name on t_dept(name);(3).以修改表的方式添加索引
alter table t_dept add unique index index_name(name);③全文索引
(1).新建表中添加索引
create table t_dept
(no int not null primary key,name varchar(20) null,sex varchar(2) null,info varchar(20) null,fulltext index index_no (no)
)(2).在已建表中添加索引
create fulltext index index_name on t_dept(name);(3).以修改表的方式添加索引
alter table t_dept add fulltext index_name(name);④组合索引
(1).新建表中添加索引
create table t_dept
(no int not null primary key,name varchar(20) null,sex varchar(2) null,info varchar(20) null,key index_no_name (no, name)
)(2).在已建表中添加索引
create index index_name_no on t_dept(name,no)(3).以修改表的方式添加索引
alter table t_dept add index index_name_no(name,no);1.3 表数据操作(更新中)
1.3.1 增(更新中)
1.3.2 删(更新中)
1.3.3 改(更新中)
1.3.4 查(更新中)
1.3.4.1 查询语句(更新中)
2.视图(更新中)
2.1 视图创建(更新中)
二.存储过程/函数/触发器
1.存储过程
1.1 存储创建(语法)
2.函数
2.1 函数创建(语法)
①创建函数
CREATE FUNCTION function_name (parameter_name datatype, ...)
RETURNS return_datatype
BEGIN-- 函数逻辑RETURN value;
END;function_name函数名称。parameter_name输入参数的名称可以有多个参数。datatype输入参数的数据类型。return_datatype函数返回值的数据类型。RETURN value指定函数的返回值。
实例:
CREATE FUNCTION add_numbers(num1 INT, num2 INT)
RETURNS INT
BEGINRETURN num1 num2;
END;数据转换 函数常用于数据转换操作。例如将日期格式转换为特定格式或将字符串转换为大写。
CREATE FUNCTION to_uppercase(str VARCHAR(255))
RETURNS VARCHAR(255)
BEGINRETURN UPPER(str);
END;SELECT to_uppercase(hello world);计算与统计 函数可以用于各种计算和统计操作。例如计算复利、求平均值等。
CREATE FUNCTION calculate_compound_interest(principal DECIMAL(10, 2), rate DECIMAL(5, 2), years INT)
RETURNS DECIMAL(10, 2)
BEGINRETURN principal * POWER(1 rate / 100, years);
END;SELECT calculate_compound_interest(1000, 5, 10) AS future_value;条件逻辑 函数可以包含条件逻辑根据输入参数的不同返回不同的结果。例如返回某个数的正负号。
## 创建一个判断正负号的函数
CREATE FUNCTION sign_of_number(num INT)
RETURNS VARCHAR(10)
BEGINIF num 0 THENRETURN Positive;ELSEIF num 0 THENRETURN Negative;ELSERETURN Zero;END IF;
END;SELECT sign_of_number(-5);动态 SQL 构建 函数还可以用于构建和执行动态 SQL 语句。例如根据输入的表名和列名动态生成查询语句。
## 创建一个函数动态查询某个表中的行数
CREATE FUNCTION get_row_count(table_name VARCHAR(255))
RETURNS INT
BEGINSET sql CONCAT(SELECT COUNT(*) FROM , table_name);PREPARE stmt FROM sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;RETURN sql;
END;
SELECT get_row_count(employees);CREATE FUNCTION get_employee_count(department INT)
RETURNS INT
BEGINRETURN (SELECT COUNT(*) FROM employees WHERE department department);
END;CREATE FUNCTION get_employee_count(department INT)
RETURNS INT
BEGINRETURN (SELECT COUNT(*) FROM employees WHERE department department);
END;CREATE FUNCTION calculate_total_salary(department INT)
RETURNS DECIMAL(10,2)
BEGINDECLARE count INT;DECLARE salary DECIMAL(10,2);SET count (SELECT COUNT(*) FROM employees WHERE department department);SET salary (SELECT SUM(salary) FROM employees WHERE department department);RETURN (salary / count);
END;也可以在函数中使用其他语句和控制结构如 IF/THEN/ELSE、WHILE、FOR 等。上面面是一个复杂的示例该函数根据部门的员工数量计算工资总额 在这个示例中我们首先声明了两个变量 count 和 salary然后使用 SELECT 语句从表中检索数据。最后我们返回 salary / count 的结果即每个员工的平均工资。 CREATE FUNCTION 是用来创建函数的语句。get_employee_count 是函数的名称。(department INT) 是输入参数列表这里我们定义了一个名为 department 的整数类型参数。RETURNS INT 指定了函数的返回类型是整数。BEGIN 和 END 之间的代码是函数体。RETURN (SELECT COUNT(*) FROM employees WHERE department department); 是返回语句它返回一个值。在这个例子中我们返回 employees 表中与输入参数 department 匹配的行数。
3.1 触发器创建(语法)
1.创建只有一个执行语句的触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name
FOR EACH ROW trigger_stmt其中 trigger_name标识触发器名称用户自行指定trigger_time标识触发时机可以指定为before或aftertrigger_event标识触发事件包括INSERT、 UPDATE和 DELETEtbl_name标识建立触发器的表名即在哪张表上建立触发器trigger_stmt是触发器执行语句。 例
CREATE TABLE account (acct_num INT,amount DECIMAL (10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET sum sum NEW.amount;首先创建一个account表表中有两个字段分别为 acct_num字段(定义为int类型)amount字段定义成浮点类型)﹔其次创建一个名为 ins_sum的触发器触发的条件是向数据表account插入数据之前对新插入的amount字段值进行求和计算。 SET sum0;
INSERT INTO account VALUES(1,1.00),(2,2.00) ;
SELECT sum;首先创建一个account表在向表account插入数据之前计算所有新插入的account 表的amount值之和触发器的名称为ins_sum条件是在向表插入数据之前触发。 2.创建有多个执行语句的触发器
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl name FOR EACH ROW
BEGIN语句执行列表END其中 trigger_name标识触发器的名称用户自行指定trigger_time标识触发时机可以指定为before或afterrigger_event标识触发事件包括INSERT、UPDATE 和 DELETEtbl_name标识建立触发器的表名即在哪张表上建立触发器触发器程序可以使用BEGIN和END作为开始和结束中间包含多条语句。 关键字 :NEW 和:OLD使用方法和意义new 只出现在insert和update时old只出现在update和delete时。在insert时new表示新插入的行数据update时new表示要替换的新数据、old表示要被更改的原来的数据行delete时old表示要被删除的数据。 CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);CREATE TABLE test4(a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,b4 INT DEFAULT 0
);DELIMITER //
CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW
BEGININSERT INTO test2 SET a2 NEW.a1;DELETE FROM test3 WHERE a3 NEW.a1;UPDATE test4 SET b4 b41 WHERE a4 NEW.a1;
END //INSERT INTO test3(a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL),(NULL), (NULL), (NULL), (NULL);
INSERT INTO test4(a4) VALUES
(0), (0),(0),(0), (0),
(0), (0),(0),(0), (0);上面的代码是创建了一个名为testref的触发器这个触发器的触发条件是在向表test1插入数据前执行触发器的语句具体执行的代码如下 INSERT INTO test1 VALUES(1), (3), (1), (7), (1), (8),(4),(4);那么4个表中的数据如下
SELECT * FROM test1;
SELECT * FROM test2;
SELECT * FROM test3;
SELECT * FROM test4;执行结果显示在向表test1插入记录的时候test2、 test3、 test4 都发生了变化。从这个例子看INSERT触发了触发器向test2中插入了test1 中的值删除了test3 中相同的内容, 同时更新了test4 中的b4,即与插入的值相同的个数。 3.查看触发器 查看触发器是指查看数据库中已存在的触发器的定义、状态和语法信息等。可以通过命令 来查看已经创建的触发器。本节将介绍两种查看触发器的方法分别是 SHOW TRIGGERS 和在triggers表中查看触发器信息。 ## SHOW TRIGGERS语句查看触发器信息
SHOW TRIGGERS;
4.实例/注意
创建一个在account表插入记录之后更新myevent数据表的触发器代码如下
CREATE TRIGGER trig_insert AFTER INSERT ON account
FOR EACH ROW INSERT INTO myevent VALUES (2, after insert);上面的代码创建了一个trig. _insert 的触发器在向表account插入数据之后会向表myevent 插入一组数据代码执行如下
INSERT INTO account VALUES (1, 1.00), (2, 2.00);SELECT * FROM myevent;从执行的结果来看是创建了一个名称为trig_insert 的触发器它是在向account 插入记 录之后进行触发执行的操作是向表myevent 插入一条记录。 使用触发器时须特别注意。 1.在使用触发器的时候需要注意对于相同的表相同的事件只能创建一一个触发器比如对 表account创建了一个BEFOREINSERT触发器,那么如果对表account再次创建一个BEFORE INSERT触发器MySQL将会报错此时只可以在表account.上创建AFTER INSERT或者 BEFOREUPDATE类型的触发器。灵活地运用触发器将为操作省去很多麻烦。 及时删除不再需要的触发器。 1.触发器定义之后每次执行触发事件都会激活触发器并执行触发器中的语句。如果需求 发生变化而触发器没有进行相应的改变或者删除则触发器仍然会执行旧的语句,从而会影 响新的数据的完整性。因此要将不再使用的触发器及时删除。 请根据实际情况合理的使用触发器,没有必要使用触发器可以直接使用代码进行逻辑处理 ,一般中小型项目使用的比较少 三.存储引擎
存储引擎区别InnoDB是mysql5.5之后是默认的引擎它支持事务、外键、表级锁和行级锁MyISAM是早期的引擎它不支持事务、只有表级锁、也没有外键用的不多Memory主要把数据存储在内存支持表级锁没有外键和事务用的也不多
查询当前数据库支持的存储引擎
show engines;1.InnoDB 介绍: nnoDB是一种兼顾高可靠性和高性能的通用存储引擎在 MySQL 5.5 之后InnoDB是默认的 MySQL 存储引擎。 ① 特点
DML操作遵循ACID模型支持事务行级锁提高并发访问性能支持外键FOREIGN KEY约束保证数据的完整性和正确性
② 文件
xxx.frmxxx代表的是表名与表相关的元数据信息都存放在frm文件包括表结构的定义信息等。xxx.ibdInnoDB DATA表数据和索引的文件innoDB引擎的每张表都会对应这样一个表空间文件存储该表的表结构frm-早期的 、sdi-新版的、数据和索引。该表的索引(B树)的每个非叶子节点存储索引叶子节点存储索引和索引对应的数据
参数innodb_file_per_table
show variables like innodb_file_per_table;如果该参数开启代表对于InnoDB引擎的表每一张表都对应一个ibd文件。 我们直接打开MySQL的数据存放目录 C:\ProgramData\MySQL\MySQL Server 8.0\Data 这个目录下有很多文件夹不同的文件夹代表不同的数据库我们直接打开jw对应某个数据库文件夹。 可以看到里面有很多的ibd文件每一个ibd文件就对应一张表比如我们有一张表 account就有这样的一个account.ibd文 件而在这个ibd文件中不仅存放表结构、数据还会存放该表对应的索引信息。 而该文件是基于二进制存储的不能直接基于 记事本打开我们可以使用mysql提供的一个指令 ibd2sdi 通过该指令就可以从ibd文件中提取sdi信息而sdi数据字典信息中 就包含该表的表结构。 ③ 逻辑存储结构
表空间 : InnoDB存储引擎逻辑结构的最高层ibd文件其实就是表空间文件在表空间中可以包含多个Segment段。段 : 表空间是由各个段组成的 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理都是引擎自身完成不需要人为对其控制一个段中包含多个区。区 : 区是表空间的单元结构每个区的大小为1M。 默认情况下 InnoDB存储引擎页大小为16K 即一个区中一共有64个连续的页。页 : 页是组成区的最小单元页也是InnoDB 存储引擎磁盘管理的最小单元每个页的大小默认为 16KB。为了保证页的连续性InnoDB 存储引擎每次从磁盘申请 4-5 个区。InnoDB 存储引擎是面向行的也就是说数据是按行进行存放的在每一行中除了定义表时所指定的字段以外还包含两个隐藏字段(后面会详细介绍)。
2.MyISAM 介绍: MyISAM是MySQL早期的默认存储引擎。 ① 特点
不支持事务不支持外键支持表锁不支持行锁访问速度快
② 文件
xxx.sdi存储表结构信息xxx.MYD: MyISAM DATA用于存储MyISAM表的数据xxx.MYI: MyISAM INDEX用于存储MyISAM表的索引相关信息
3.Memory 介绍: Memory引擎的表数据时存储在内存中的由于受到硬件问题、或断电问题的影响只能将这些表作为临时表或缓存使用。 ① 特点
内存存放hash索引默认
② 文件
xxx.sdi存储表结构信息【数据存放在内存中xxx.sdi存放在D:\SoftwareInstall\mysql-5.7.42-winx64\data\databaseName\xxx.sdi】
4.三种引擎区别及特点
特点InnoDBMyISAMMemory存储限制64TB有有事务安全支持--锁机制行锁表锁表锁Btree索引支持支持支持Hash索引--支持全文索引支持(5.6版本之后)支持-空间使用高低N/A内存使用高低中等批量插入速度低高高支持外键支持--
问题:
InnoDB引擎与MyISAM引擎的区别? 1.InnoDB引擎, 支持事务, 而MyISAM不支持。 2.InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。 3.InnoDB引擎, 支持外键, 而MyISAM是不支持的。 存储引擎支持在MySQL中支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能hash索引是InnoDB存储引擎根据BTree索引在指定条件下自动构建的。 存储引擎选择? 1.InnoDB: 是Mysql的默认存储引擎支持事务、外键。如果应用对事务的完整性有比较高的要求在并发条件下要求数据的一致性数据操作除了插入和查询之外还包含很多的更新、删除操作那么InnoDB存储引擎是比较合适的选择。存储业务系统中对于事务、数据完整性要求较高的核心数据 2.MyISAM 如果应用是以读操作和插入操作为主只有很少的更新和删除操作并且对事务的完整性、并发性要求不是很高那么选择这个存储引擎是非常合适的。 存储业务系统的非核心事务【MYISAM索引和数据是分开的而且其索引是压缩的可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能这可以极大地优化LIKE查询的效率。】业务系统中的日志、电商系统中的足迹/评论【被NoSQL–MongoDB替代】 3.MEMORY将所有数据保存在内存中访问速度快通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制太大的表无法缓存在内存中而且无法保障数据的安全性。【被NoSQL–Redis替代】 为什么InnoDB存储引擎选择使用BTree索引结构? 1.相对于二叉树层级更少搜索效率高 2.对于B-tree无论是叶子节点还是非叶子节点都会保存数据这样导致一页中存储的键值减少指针跟着减少要同样保存大量数据只能增加树的高度导致性能降低B 树的非叶子节点不存放实际的记录数据仅存放索引因此数据量相同的情况下相比存储即存索引又存记录的 B 树B树的非叶子节点可以存放更多的索引因此 B 树可以比 B 树更「矮胖」查询底层节点的磁盘 I/O次数会更少 3.B 树有大量的冗余节点所有非叶子节点都是冗余索引这些冗余索引让 B 树在插入、删除的效率都更高比如删除根节点的时候不会像 B 树那样会发生复杂的树的变化B 树叶子节点之间用链表连接了起来有利于范围查询而 B 树要实现范围查询因此只能通过树的遍历来完成范围查询这会涉及多个节点的磁盘 I/O 操作范围查询效率不如 B 树 4.相对Hash索引Btree支持范围匹配及排序操作 索引是否越多越好为什么 不是。索引是建立在原数据上的数据结构所以不论在查询还是更新维护、一定会带来开销。 比如一本书有 100 页我构建了 50 页的目录你觉查询起来还会方便吗 1.数据量小的表不需要建立索引建立索引反而会增加额外开销。 2.数据变更后索引也需要更新更多的索引意味着更多的维护成本。 3.索引是放在磁盘的更能的索引也意味着更多的存储空间。 4.数据重复且分布平均的字短没必要建立索引比如性别 索引什么时候会失效
范围查询 大于小于 联合索引的最左匹配原则出现范围查询(,)范围查询右侧的列索引失效即范围查询的字段可以用到联合索引但是在范围查询字段后面的字段无法用到联合索引。mysql 会一直向右匹配直到遇到索引搜索键使用、就停止匹配。一旦权重最高的索引搜索键使用、范围查询那么其它、搜索键都无法用作索引。即索引最多使用一个、的范围列因此如果查询条件中有两个、范围列则无法全用到索引。
-- 当范围查询使用 或 时走联合索引了但是索引的长度为49就说明范围查询右边的status字段是没有走索引的
explain select * from user where profession 软件工程 and age 30 and status 0;-- 使用 或 走联合索引了索引的长度为54就说明所有的字段都是走索引的
explain select * from user where profession 软件工程 and age 30 and status 0;like %xx 模糊查询
当使用LIKE操作符进行模糊查询并且搜索键值以通配符%开头如like ‘%abc’则索引失效直接全表扫描。这是因为以%开头的模式匹配意味着匹配的字符串可以在任何位置这使得索引无法有效定位数据若只是以%结尾索引不会失效
-- 索引生效
explain select * from user where profession like 软件%;
-- 失效
explain select * from user where profession like %工程;
-- 失效
explain select * from user where profession like %工%;3.对索引列进行运算
当我们在查询条件中对索引列进行函数或表达式计算会导致索引失效而进行全表扫描。比如
select * from user where YEAR(birthday) 1999;
explain select * from user where length(name)2;4.or 条件索引问题 用or分割开的条件 如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到。 当or连接的条件左右两侧字段都有索引时索引才会生效
-- age没有索引or连接 索引失效、全表扫描
explain select * from user where id 10 or age 23;5.数据类型不一致隐式转换导致索引失效 当列是字符串类型传入条件 必须用引号引起来不然报错或索引失效字符串不加引号 索引会失效。
explain select * from t_user where id_no 1002;表里的 id_no 是 varchar 类型
6.! 问题 普通索引使用 !索引失效主键索引没影响。 where语句中索引列使用了负向查询可能会导致索引失效。负向查询包括NOT、!、、NOT IN、NOT LIKE等。
-- 索引生效
explain select * from deviceinfo where device_id 0x719d7986;
-- 索引失效
explain select * from deviceinfo where device_id ! 0x719d7986;-- 索引生效
explain select * from deviceinfo where id 32619;
-- 索引生效
explain select * from deviceinfo where id ! 32619;
7.联合索引 违背 最左前缀法则 如果索引了多列联合索引要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将会部分失效(后面的字段索引失效)。
