3.4、删除表
4、总结
4.1、操作数据库
4.2、常用数据类型
4.3、操作表
5、综合练习
二、MySQL表的增删改查基础
1、CRUD
2、新增（Create）
2.1、单行数据 + 全列插入
2.2、多行数据 + 指定列插入
3、查询（Retrieve）
3.1、全列查询
3.2、查询字段为表达式
3.3、别名
3.4、去重：DISTINCT
3.5、排序：ORDER BY
3.6、条件查询：WHERE
3.7、分页查询：LIMIT
4、修改（Update）
5、删除（Delete）
三、MySQL表的增删改查进阶
1、数据库约束
1.1、约束类型
1.2、NULL约束
1.3、UNIQUE：唯一约束
1.4、DEFAULT：默认值约束
1.5、PRIMARY KEY：主键约束
1.6、FOREIGN KEY：外键约束
1.7、CHECK约束（了解）
2、表的设计
2.1、三大范式
3，新增
4、查询
4.1、聚合查询
4.2、联合查询
四、MySQL索引与事务
1、MySQL索引
1.1、概念
1.2、作用
1.3、使用场景
1.4、使用
1.5、MySQL面试 6 问
2、事务
2.1、什么是事务？
2.2、redo log 与 undo log介绍
2.3、事务的实现
五、MySQL中JDBC编程
1、数据库编程的必备条件
2、Java的数据库编程：JDBC
3、JDBC工作原理
4，环境搭建
5、MySQL中的JDBC编程
5.1，五步流程
5.2、添加信息
5.3、查询信息
5.4、删除信息
MySQL是程序员必须要掌握的，其实语法结构相比于其他的_数据结构、Java、C++_来说应该算是简单的，只要多多练习，拿下它应该不成问题。本文是将我学过的面试笔试重点的知识进行归纳总结，如果你认为写的不错的话，求点赞，求收藏，感谢！！！
一、MySQL数据库基础
============
1，数据库的操作
1.1、显示当前数据库
show databases;
1.2 、创建数据库
语法：
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,
create_specification] …]
示例：
创建名为 db_test1 的数据库
create database db_test1;
如果系统没有 db_test2 的数据库，则创建一个名叫 db_test2 的数据库，如果有则不创建
create database if not db_test1;
1.3、使用数据库
进行表的操作前的重要的一步
use 数据库名;
1.4、删除数据库
说明: 数据库删除以后，内部看不到对应的数据库，里边的表和数据全部被删除
DROP DATABASE [IF EXISTS] db_name;
2、常用数据类型
2.1、数值类型
分为整型和浮点型：
数值类型可以指定为无符号（unsigned），表示不取负数。1字节（bytes）= 8bit。 对于整型类型的范围： 1. 有符号范围：-2（类型字节数*8-1）到2（类型字节数*8-1）-1，如int是4字节，就 是-231到231-1 2. 无符号范围：0到2（类型字节数*8）-1，如int就是232-1。
2.2、字符串类型
2.3、日期类型
3、表的操作
use db_test;
3.1、创建表
语法：
CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
);
示例：
create table stu_test(
id int,
name varchar(20) comment ‘姓名’,
password varchar(20) comment ‘密码’,
age int,
sex varchar(1),
birthday timestamp,
amout decimal(13,2),
resume text
);
3.2、查看表结构
desc 表名;
3.4、删除表
语法格式：
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] …
示例：
– 删除 stu_test 表
drop table stu_test;
– 如果存在 stu_test 表，则删除 stu_test 表
drop table if exists stu_test;
4、总结
4.1、操作数据库
– 显示
show databases;
– 创建
create database xxx;
– 使用
use xxx;
– 删除
drop database xxx;
4.2、常用数据类型
INT：整型
DECIMAL(M, D)：浮点数类型
VARCHAR(SIZE)：字符串类型
TIMESTAMP：日期类型
4.3、操作表
– 查看
show 表;
– 创建
create table 表名(
字段1 类型1,
字段2 类型2,
…
);
– 删除
drop talbe 表名;
5、综合练习
有一个商店的数据，记录客户及购物情况，有以下三个表组成：
商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供 应商provider)
客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证 card_id)
购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)
create database shop;
use shop;
create table goods(
good_id int comment ‘商品编号’,
good_name varchar(30) comment ‘商品名称’,
unitprice int comment ‘单价’,
category varchar(30) comment ‘商品类别’,
provider varchar(64) comment ‘供应商名称’
);
create table if not exists customer
(
customer_id int comment ‘客户编号’,
name varchar(32) comment ‘客户姓名’,
address varchar(256) comment ‘客户地址’,
email varchar(64) comment ‘电子邮箱’,
sex bit comment ‘性别’,
card_id varchar(18) comment ‘身份证’
);
create table if not exists purchase
(
order_id int comment ‘订单号’,
customer_id int comment ‘客户编号’,
goods_id int comment ‘商品编号’,
nums int comment ‘购买数量’
);
二、MySQL表的增删改查基础
===================
1、CRUD
**注释：**在SQL中可以使用“–空格+描述”来表示注释说明
_CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)_四个单词的首字母缩写。
2、新增（Create）
语法：
INSERT [INTO] table_name
[(column [, column] …)]
VALUES (value_list) [, (value_list)] …
value_list: value, [, value] …
案例：
create table student(
id int,
sn int comment ‘学号’,
name varchar(20) comment ‘姓名’,
qq_mail varchar(20) comment ‘QQ邮箱’
);
2.1、单行数据 + 全列插入
insert into student values(1,1000,‘擦洗’,NUll);
insert into student values(2,1001,‘好还是’, ‘111111’);
2.2、多行数据 + 指定列插入
insert into student(id,sn,name) values
(3,1002,‘李白’),
(4,1003,‘曹操’);
3、查询（Retrieve）
语法：
SELECT
[DISTINCT] {* | {column [, column] …}
[FROM table_name]
[WHERE …]
[ORDER BY column [ASC | DESC], …]
LIMIT …
案例：
create table exam_result(
id int,
name varchar(20),
chinese decimal(3,1),
math decimal(3,1),
english decimal(3,1)
);
insert into exam_result values
(1,‘唐三藏’, 67, 98, 56),
(2,‘孙悟空’, 87.5, 78, 77),
(3,‘猪悟能’, 88, 98.5, 90),
(4,‘曹孟德’, 82, 84, 67),
(5,‘刘玄德’, 55.5, 85, 45),
(6,‘孙权’, 70, 73, 78.5),
(7,‘宋公明’, 75, 65, 30);
3.1、全列查询
select * from exam_result;
select id,name,chinese from exam_result;
select chinese,math,english from exam_result;
3.2、查询字段为表达式
select id,name,10 from exam_result;
select id,name,english + 10 from exam_result;
3.3、别名
语法：
SELECT column [AS] alias_name […] FROM table_name;
select id,name,chinese+math+english as total from exam_result;
3.4、去重：DISTINCT
select distinct math from exam_result;
3.5、排序：ORDER BY
语法：
– ASC 为升序（从小到大）
– DESC 为降序（从大到小）
– 默认为 ASC
SELECT … FROM table_name [WHERE …]
ORDER BY column [ASC|DESC], […];
1. 没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序
2. NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面
select name,chinese from exam_result order by chinese;
select name,chinese from exam_result order by chinese desc;
3. 使用表达式及别名排序
select name,chinese+math+english total from exam_result order by total desc;
select name,chinese+math+english from exam_result order by chinese+math+english desc;
4. 可以对多个字段进行排序，排序优先级随书写顺序
– 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
select name,math,english,chinese from exam_result order by math desc,chinese,english;
3.6、条件查询：WHERE
比较运算符：
逻辑运算符：
注：
1. WHERE条件可以使用表达式，但不能使用别名。
2. AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分
基本查询：
– 查询英语不及格的同学及英语成绩 ( < 60 )
select name, english from exam_result where english < 60;
– 查询语文成绩好于英语成绩的同学
select name,chinese,english from exam_result where chinese > english;
– 查询总分在 200 分以下的同学
select name,chinese+math+english total from exam_result where chinese+math+english < 200;
AND与OR：
select * from exam_result where chinese > 80 and english > 80;
select * from exam_result where chinese > 80 or english > 80;
select * from exam_result where chinese > 80 or math > 70 and english > 80;
select * from exam_result where (chinese > 80 or math > 70) and english > 80;
范围查询：
1. BETWEEN … AND …
– 查询语文成绩在 [80, 90] 分的同学及语文成绩
select name,chinese from exam_result where chinese between 80 and 90;
2. IN
– 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
select name,math from exam_result where math in(58,59,98,99);
模糊查询：LIKE
– 匹配到孙悟空、孙权
select name from exam_result where name like ‘孙%’;
–_匹配严格的一个任意字符 匹配到孙权
select name from exam_result where name like ‘孙_’;
3.7、分页查询：LIMIT
语法：
– 起始下标为 0
– 从 0 开始，筛选 n 条结果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n;
– 从 s 开始，筛选 n 条结果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT s, n;
– 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n OFFSET s;
案例：按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页
– 第 1 页
select id,name,math,english,chinese from exam_result order by id limit 3 offset 0;
– 第 2 页
select id,name,math,english,chinese from exam_result order by id limit 3 offset 3;
4、修改（Update）
语法：
UPDATE table_name SET column = expr [, column = expr …]
[WHERE …] [ORDER BY …] [LIMIT …]
案例：
update exam_result set math = 80 where name = ‘孙悟空’;
– 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
update exam_result set math = 60,chinese = 70 where name = ‘曹孟德’;
5、删除（Delete）
语法：
DELETE FROM table_name [WHERE …] [ORDER BY …] [LIMIT …]
– 删除孙悟空同学的考试成绩
delete from exam_result where name = ‘孙悟空’;
三、MySQL表的增删改查进阶
===================
1、数据库约束
1.1、约束类型
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
DEFAULT - 规定没有给列赋值时的默认值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标 识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略 CHECK子句。
1.2、NULL约束
创建表时，可以指定某列不为空：
drop table if exists student;
create table student(
id int not NULL,
sn int,
name varchar(20),
qq_mail varchar(10)
);
1.3、UNIQUE：唯一约束
指定sn列为唯一的、不重复的：
drop table if exists student;
create table student(
id int not NULL,
sn int unique,
name varchar(20),
qq_mail varchar(10)
);
1.4、DEFAULT：默认值约束
指定插入数据时，name列为空，默认值unkown：
drop table if exists student;
create table student(
id int not NULL,
sn int unique,
name varchar(20) default ‘unkown’,
qq_mail varchar(10)
);
1.5、PRIMARY KEY：主键约束
指定id列为主键：
drop table if exists student;
create table student(
id int not NULL primary key,
sn int unique,
name varchar(20) default ‘unkown’,
qq_mail varchar(10)
);
对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大 值+1。
– 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
id int primary key auto_increment,
1.6、FOREIGN KEY：外键约束
外键用于关联其他表的主键或唯一键，语法：
foreign key (字段名) references 主表(列)
创建班级表classes，id为主键：
– 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识
drop table if exists classes;
create table classes(
id int primary key auto_increment,
name varchar(20),
desc varchar(100)
);
创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键， classes_id为外键，关联班级表id
drop table if exists student;
create table student(
id int primary key auto_increment,
sn int unique,
name varchar(20) default ‘unkown’,
qq_mail varchar(20),
classes_id int,
foreign key (classes_id) references classes(id)
);
1.7、CHECK约束（了解）
MySQL使用时不报错，但忽略该约束：
drop table if exists test_user;
create table test_user(
id int,
name varchar(20),
sex varchar(1),
check(sex = ‘男’ or sex = ‘女’)
);
2、表的设计
2.1、三大范式
一对一
一对多
多对多
创建课程表
drop table if exists test_user;
create table test_user(
id int,
name varchar(20),
sex varchar(1),
check(sex = ‘男’ or sex = ‘女’)
);
创建学生课程中间表，考试成绩表
drop table if exists score(
id int primary key auto_increment,
score decimal(3,1),
student_id,
course_id,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);
3，新增
插入查询结果
语法：
insert into table_name column select…
案例：创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的 学生数据复制进来，可以复制的字段为name、qq_mail
– 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
id INT primary key auto_increment,
name VARCHAR(20) comment ‘姓名’,
age INT comment ‘年龄’,
email VARCHAR(20) comment ‘邮箱’,
sex varchar(1) comment ‘性别’,
mobile varchar(20) comment ‘手机号’
);
–把学生表中的所有数据复制到用户表
insert into test_user(name,email) select name,qq_mail from student;
4、查询
4.1、聚合查询
聚合函数
常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：
COUNT
– 统计班级共有多少同学
select count(*) from student;
select count(0) from student;
– 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果
select count(qq_mail) from student;
SUM
– 统计数学成绩总分
select sum(math) from exam_result;
– 不及格 < 60 的总分，没有结果，返回 NULL
select sum(math) from exam_result where math < 60;
AVG
– 统计平均总分
select avg(chinese + math + english) 平均总分 from exam_result;
MAX
– 返回英语最高分
select max(english) from exam_result;
MIN
– 返回 > 70 分以上的数学最低分
select min(math) from exam_result where math > 70;
①GROUP BY子句
SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查 询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函 数中。
select column1, sum(column2), … from table group by column1,column3;
准备测试表及数据：职员表，有id（主键）、name（姓名）、role（角色）、salary（薪水）
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);
insert into emp(name, role, salary) values
(‘李白’,‘服务员’, 1000.20),
(‘杜甫’,‘游戏陪玩’, 2000.99),
(‘孙悟空’,‘游戏角色’, 999.11),
(‘猪无能’,‘游戏角色’, 333.5),
(‘沙和尚’,‘游戏角色’, 700.33),
(‘隔壁老王’,‘董事长’, 12000.66);
查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;
②HAVING
GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING显示平均工资低于1500的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary) < 1500;
4.2、联合查询
实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：
测试示例：
insert into classes(name, desc) values
(‘计算机系2019级1班’, ‘学习了计算机原理、C和Java语言、数据结构和算法’),
(‘中文系2019级3班’,‘学习了中国传统文学’),
(‘自动化2019级5班’,‘学习了机械自动化’);
create table classes(
name varchar(20),
desc varchar(200)
);
create table student(
id int,
sn varchar(6),
name varchar(20),
qq_mail varchar(20),
classes_id int
);
insert into student(id,sn, name, qq_mail, classes_id) values
(1,‘09982’,‘黑旋风李逵’,‘xuanfeng@qq.com’,1),
(2,‘00835’,‘菩提老祖’,null,1),
(3,‘00391’,‘白素贞’,null,1),
(4,‘00031’,‘许仙’,‘xuxian@qq.com’,1),
(5,‘00054’,‘不想毕业’,null,1),
(6,‘51234’,‘好好说话’,‘say@qq.com’,2),
(7,‘83223’,‘tellme’,null,2),
(8,‘09527’,‘老外学中文’,‘foreigner@qq.com’,2);
create table course(
id int primary key auto_increment,
name varchar(20)
);
insert into course(id,name) values
(1,‘Java’),(2,‘中国传统文化’),(3,‘计算机原理’),(4,‘语文’),(5,‘高阶数学’),(6,‘英文’);
create table score(
score decimal(3,1),
student_id int,
course_id int
);
insert into score(score, student_id, course_id) values
– 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
– 菩提老祖
(60, 2, 1),(59.5, 2, 5),
– 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
– 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
– 不想毕业
(81, 5, 1),(37, 5, 5),
– 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
– tellme
(80, 7, 2),(92, 7, 6);
内连接
语法：
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
案例：
（1）查询“许仙”同学的 成绩
select sco.score from student stu inner join score sco on stu.id=sco.student_id
and stu.name=‘许仙’;
– 或者
select sco.score from student stu, score sco where stu.id=sco.student_id and
stu.name=‘许仙’;
初学者建议一步一步来
select student.id,student.name ,score.student_id,score.score from student,score;
select student.id,student.name ,score.student_id,score.score from
student,score where student.id = score.student_id;
select student.id,student.name ,score.student_id,score.score from
student,score where student.id = score.student_id and student.name = ‘许仙’;
（2）查询所有同学的总成绩，及同学的个人信息：
SELECT
stu.sn,
stu.NAME,
stu.qq_mail,
sum( sco.score )
FROM
student stu
JOIN score sco ON stu.id = sco.student_id
GROUP BY
sco.student_id;
select student.id ,student.name,score.student_id,score.score from student,score;
select student.id ,student.name,score.student_id,score.score from student,score
where student.id = score.student_id;
select student.id ,student.name,score.student_id,score.score from student,score
where student.id = score.student_id group by student.id;
select student.id ,student.name,sum(score.score) from student,score
where student.id = score.student_id group by student.id;
（3）查询所有同学的成绩，及同学的个人信息
select student.id,student.name,course.id,course.name,score.student_id,score.score from
student,course,score;
select student.id,student.name,course.id,course.name,score.student_id,score.score from
student,course,score where student.id = score.student_id;
select student.id,student.name,course.id,course.name,score.student_id,score.score from
student,course,score where student.id = score.student_id and course.id = score.course_id;
select student.name,course.name,score.score from
student,course,score where student.id = score.student_id and course.id = score.course_id;
自连接
显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
select * from course where name = ‘java’;
select * from course where name = ‘计算机原理’;
select s1.student_id,s1.score,s1.course_id,s2.student_id,s2.course_id
from score s1,score s2;
select s1.student_id,s1.score,s1.course_id,s2.student_id,s2.course_id
from score s1,score s2 where s1.student_id = s2.student_id;
select s1.student_id,s1.score,s1.course_id,s2.student_id,s2.course_id
from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id
= 3 and s2.course_id = 1;
select s1.student_id,s1.score,s1.course_id,s2.student_id,s2.course_id
from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id
= 3 and s2.course_id = 1 and s1.score > s2.score;
子查询
查询与“不想毕业” 同学的同班同学
select classes_id from student where name = ‘不想毕业’;
select name from student where classes_id = (select classes_id from student where name = ‘不想毕业’);
查询“语文”或“英文”课程的成绩信息
select id from course where name = ‘语文’ or name = ‘英文’;
select * from score where course_id in (select id from course where name = ‘语文’ or name = ‘英文’);
合并查询
查询id小于3，或者名字为“英文”的课程
select * from course where id < 3 or name = ‘英文’;
select * from course where id < 3 union select * from course where name = ‘英文’;
四、MySQL索引与事务
================
1、MySQL索引
1.1、概念
索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引， 并指定索引的类型，各类索引有各自的数据结构实现。索引是关系数据库中对某一列或多个列的值进行预排序的数据结构，在 MySQL 中也被称为 Key。
通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。
注意事项：有序性是因为一切二分法查找法都要求数据已经是排好顺序的。如果把索引看做 key（虽然 key 数据也是来自于表单中一行记录的某些字段值），那么 value 在 MyISAM 中就是记录的在存储文件中的地址，而在 InnoDB 中 value 直接就是对应的一行数据。
假设我们有一张数据表 workers(员工表)，该表有三个字段（列）,分别是name、age 和address。假设表works有上万行数据，现在需要从这个表中查找出所有名字是‘ZhangSan’的雇员信息，你会快速的写出SQL语句：
select name,age,address from workers where name=‘ZhangSan’
如果数据库还没有索引这个东西，一旦我们运行这个SQL查询，查找名字为ZhangSan的雇员的过程中，究竟会发生什么？数据库不得不在workes表中的每一行查找并确定雇员的名字（name）是否为‘ZhangSan’。
由于我们想要得到每一个名字为ZhangSan的雇员信息，在查询到第一个符合条件的行后，不能止查询，因为可能还有其他符合条件的行，所以必须一行一行的查找直到最后一行——这就意味数据库不得不检查上万行数据才能找到所有名字为ZhangSan的雇员。这就是所谓的全表扫描，显然这种模式效率太慢。
使用索引的全部意义就是：通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单（定义真特么拗口）。大白话意思是索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
一个索引是存储的表中一个特定列的值数据结构。索引是在表的列上创建。要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中。请牢记这一点：索引是一种数据结构。
1.2、作用
数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。索引所起的作用类似书籍目录，可用于快速定位、检索数据。索引对于提高数据库的性能有很大的帮助。
1.3、使用场景
索引的本质实际上还是存储在磁盘上的数据结构，它可以有的存储结构：
- 二叉搜索树；
- 红黑树；
- Hash 表；
- B-Tree
- B+Tree
其中 MySQL 的 InnoDB 支持 B+Tree 以及 Hash 表，下面会具体分析各个数据结构的区别。
哈希索引
哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效生。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code)，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
在 MySQL 中，只有 Memory 引擎显式支持哈希索引。这也是 Memory 引擎表的默认索引类型，Memory 引擎同时也支持 B-Tree 索引。值得一提的是，Memory 引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。
下面来看一个例子。假设有如下表：
create table tes(
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
KEY USING HASH(fname)
) ENGINE = MEMORY;
然后再填入相关数据后，表格有如下数据：
假设索引使用假想的哈希函数 f()，它返回下面的值(都是示例数据，非真实数据) :
-
f(‘a’) = 23
-
f(‘b’) = 74
-
f(‘p’) = 87
-
f(‘v’) = 24
则哈希索引的数据结构如下：
注意每个槽的编号是顺序的，但是数据行不是。
下面使用 hash 索引字段进行查询，有：
select lname from tes where fname = ‘p’;
其分为如下的步骤：
MSQL 先计算 ‘p’ 的哈希值；根据哈希值进行寻找对应的地址指针，意味 hash 槽是有序的，因此查询效率很高；读取对应指针上的数据是否为 ‘p’，是则返回，因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：
哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列 (A,B) 上建立哈希索引，如果查询只有数据列 A，则无法使用该索引。
哈希索引只支持等值比较查询，包括 =、IN()、<=>；不支持任何范围查询，例如 WHEREprice>100。
访问哈希索引的数据非常快，除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。
TREE
要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：
数据量较大，且经常对这些列进行条件查询。
该数据库表的插入操作，及对这些列的修改操作频率较低。
索引会占用额外的磁盘空间。
在没有GUI工具的情况下，可以使用以下命令查看索引：
上述ad_article表中有两个索引，Key_name中有显示:
-PRIMARY主键索引，Seq_in_index索引序号为1，从1开始，Collation为“A”表示升序（或NULL无分类），对应字段是id
-idx_cid是自建索引，由cid、available、id三个字段组成，分别对应序号1,2,3Index_type=BTREE这块内容很多人不懂其意思，其实通过GUI工具创建索引时也会有BTREE 的显示，先着重了解一下。
在计算机数据结构（不懂数据结构的自行充电）体系中，为了加速查找的速度，常见的数据结构有两种：
-Hash哈希结构，例如Java中的HashMap，这种数据组织结构可以让查询/插入/修改/删除的平均时间复杂度都为O(1);
-Tree 树 结构 ， 这种数据组织结构可以让查询/插入/修改/删除的平均时间复杂度都为O(log(n));不管读还是写，Hash这种类型比Tree树这种类型都要更快一些，那为什么MySQL的开发者既使用Hash类型做为索引，又使用了BTREE呢？
确实用HASH索引更快，因为每次都只查询一条信息（重名的雇员姓名也才几条而已），但实际上业务对于SQL的应用场景是：
-orderby 需要排个序
-groupby 还要分个组
-还要比较大小 大于或小于等等
这种情况下如果继续用HASH类型做索引结构，其时间复杂度会从O(1)直接退化为O(n)，相当于全表扫描了，而Tree的特性保证了不管是哪种操作，依然能够保持O(log(n))的高效率。那MySQL中的BTREE和TREE又有啥联系与区别呢？先来看看传统的二叉树：
二叉树是大家熟知的一种树，用它来做索引行不行，可以是可以，但有几个问题：
-如果索引数据很多，树的层次会很高（只有左右两个子节点），数据量大时查询还是会慢
-二叉树每个节点只存储一个记录，一次查询在树上找的时候花费磁盘IO次数较多
所以它并不适合直接拿来做索引存储，算法设计人员在二叉树的基础之上进行了变种，引入了
BTREE
BTREE
如上图可知BTREE有以下特点：
-不再是二叉搜索，而是N叉搜索，树的高度会降低，查询快
-叶子节点，非叶子节点，都可以存储数据，且可以存储多个数据
-通过中序遍历，可以访问树上所有节点
BTREE被作为实现索引的数据结构被创造出来，是因为它能够完美的利用“局部性原理”，其设计逻辑是这样的：
-内存读写快，磁盘读写慢，而且慢很多
-磁盘预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载一些看起
来是冗余的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘读写，提高效率（通常，一页数据是4K）
-局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO效能
B+TREE
早先的MySQL就是使用的BTREE做为索引的数据结构，随着时间推移，B树发生了较多的变种，其中最常见的就是B+TREE变种，现在MySQL用的就是这种，示意如下：
B+TREE改进点及优势所在：
-仍然是N叉树，层级小，非叶子节点不再存储数据，数据只存储在同一层的叶子节点上，B+树从根到每一个节点的路径长度一样，而B树不是这样
-叶子之间，增加了链表(图中红色箭头指向)，获取所有节点，不再需要中序遍历，使用链表的next节点就可以快速访问到
-范围查找方面，当定位min与max之后，中间叶子节点，就是结果集，不用中序回溯（范围查询在SQL中用得很多，这是B+树比B树最大的优势）
-叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储
-非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引
可以来初步计算一下：假设key、子树节点指针均占用4B，则B树一个节点占用4 + 4 = 8B，一页页面大小4KB，则N = 4 * 1024 / 8B = 512，一个512叉的B树，1000w的数据，深度最大 log(512/2)(10^7) 约等于4。对比二叉树如AVL的深度为log(2)(10^7) 约为24，相差了5倍以上。假如一个节点大小是4KB，一个KEY有8字节，一页可以存4000/8=500个KEY，根据N叉树特点,就算一层500叉节点，则：
第一层树：1个节点，1*500KEY , 大小4K
第二层树：500节点 500*500=25万个KEY，500*4K=2M
最后
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。
深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。
因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Android开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！
如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！
由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！
条信息（重名的雇员姓名也才几条而已），但实际上业务对于SQL的应用场景是：
-orderby 需要排个序
-groupby 还要分个组
-还要比较大小 大于或小于等等
这种情况下如果继续用HASH类型做索引结构，其时间复杂度会从O(1)直接退化为O(n)，相当于全表扫描了，而Tree的特性保证了不管是哪种操作，依然能够保持O(log(n))的高效率。那MySQL中的BTREE和TREE又有啥联系与区别呢？先来看看传统的二叉树：
二叉树是大家熟知的一种树，用它来做索引行不行，可以是可以，但有几个问题：
-如果索引数据很多，树的层次会很高（只有左右两个子节点），数据量大时查询还是会慢
-二叉树每个节点只存储一个记录，一次查询在树上找的时候花费磁盘IO次数较多
所以它并不适合直接拿来做索引存储，算法设计人员在二叉树的基础之上进行了变种，引入了
BTREE
BTREE
如上图可知BTREE有以下特点：
-不再是二叉搜索，而是N叉搜索，树的高度会降低，查询快
-叶子节点，非叶子节点，都可以存储数据，且可以存储多个数据
-通过中序遍历，可以访问树上所有节点
BTREE被作为实现索引的数据结构被创造出来，是因为它能够完美的利用“局部性原理”，其设计逻辑是这样的：
-内存读写快，磁盘读写慢，而且慢很多
-磁盘预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载一些看起
来是冗余的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘读写，提高效率（通常，一页数据是4K）
-局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO效能
B+TREE
早先的MySQL就是使用的BTREE做为索引的数据结构，随着时间推移，B树发生了较多的变种，其中最常见的就是B+TREE变种，现在MySQL用的就是这种，示意如下：
B+TREE改进点及优势所在：
-仍然是N叉树，层级小，非叶子节点不再存储数据，数据只存储在同一层的叶子节点上，B+树从根到每一个节点的路径长度一样，而B树不是这样
-叶子之间，增加了链表(图中红色箭头指向)，获取所有节点，不再需要中序遍历，使用链表的next节点就可以快速访问到
-范围查找方面，当定位min与max之后，中间叶子节点，就是结果集，不用中序回溯（范围查询在SQL中用得很多，这是B+树比B树最大的优势）
-叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储
-非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引
可以来初步计算一下：假设key、子树节点指针均占用4B，则B树一个节点占用4 + 4 = 8B，一页页面大小4KB，则N = 4 * 1024 / 8B = 512，一个512叉的B树，1000w的数据，深度最大 log(512/2)(10^7) 约等于4。对比二叉树如AVL的深度为log(2)(10^7) 约为24，相差了5倍以上。假如一个节点大小是4KB，一个KEY有8字节，一页可以存4000/8=500个KEY，根据N叉树特点,就算一层500叉节点，则：
第一层树：1个节点，1*500KEY , 大小4K
第二层树：500节点 500*500=25万个KEY，500*4K=2M
最后
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。
深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。
因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
[外链图片转存中…(img-Uf6tyc3A-1715543501188)]
[外链图片转存中…(img-mL2JuGEV-1715543501189)]
[外链图片转存中…(img-OqIzpfAI-1715543501189)]
既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Android开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！
如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！
由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！
3464
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
