【JDBC】笔记（1）— JDBC概述

数据库98

1、JDBC是什么？
Java DataBase Connectivity（Java语言连接数据库）

2、JDBC的本质是什么？
JDBC是SUN公司制定的一套 接口（实质）
java.sql.*; (这个软件包下有很多接口)

接口都有调用者和实现者。
面向接口调用、面向接口写实现类，这都属于面向接口编程。

思考：为什么SUN制定一套JDBC接口呢？
因为每一个数据库的底层实现原理都不一样。
Oracle数据库有自己的原理。
MySQL数据库也有自己的原理。
MS SqlServer数据库也有自己的原理。
....

每一个数据库产品都有自己独特的实现原理。

3、JDBC开发前的准备工作，先从官网下载对应的驱动jar包，然后将其配置到环境变量classpath当中。

以上的配置是针对于文本编辑器的方式，开发IDEA有自己的配置方式；

4、JDBC编程六步（ 重点）

第一步：注册驱动（作用：告诉Java程序，即将要连接的是哪个品牌的数据库）

第二步：获取连接（表示JVM的进程和数据库进程之间的通道打开了，这属于进程之间的通信，重量级的，使用完之后一定要关闭通道。）

第三步：获取数据库操作对象（专门执行sql语句的对象）

第四步：执行SQL语句（DQL DML....）

第五步：处理查询结果集（ 只有当第四步执行的是select语句的时候，才有这第五步处理查询结果集）

第六步：释放资源（使用完资源之后一定要关闭资源。Java和数据库属于进程间的通信，开启之后一定要关闭。）

5.辅助理解图示：

Original: https://www.cnblogs.com/Burning-youth/p/15745778.html
Author: 猿头猿脑的王狗蛋
Title: 【JDBC】笔记（1）--- JDBC概述

相关阅读1

Title: 精心整理16条MySQL使用规范，减少80%问题，推荐分享给团队

上篇文章介绍了如何创建合适的MySQL索引，今天再一块学一下如何更规范、更合理的使用MySQL？

合理规范的使用MySQL，可以大大减少开发工作量和线上问题，并提升SQL查询性能。

我精心总结了这16条MySQL规约，分享给大家，欢迎评论指正。

1. 禁止使用select *

阿里开发规范中，有这么一句话：

【JDBC】笔记（1）— JDBC概述

select * 会查询表中所有字段，如果表中的字段有更改，必须修改SQL语句，不然就会执行错误。

查询出非必要的字段，徒增磁盘IO和网络延迟。

2. 用小表驱动大表

关联查询的时候，先用小表查到结果，再用结果去大表查询，可以大大减少连接次数。

比如我们要查询某个部门下的员工，由于部门数量远远小于员工数量。我们可以把部门表当作驱动表，员工表当作被驱动表。

查询SQL类似这样：

select * from department
inner join employee
on department.id=employee.department_id
where department_name='部门1';

3. join关联表不宜过多

join关联表禁止超过3张，join关联过多，不但会增加查询时间，降低查询性能，还会产生临时表缓存结果数据，推荐拆成多条小SQL执行。

另外关联字段的类型一定要保持一致，并且在每张表都要建立关联字段的索引。

4. 禁止使用左模糊或者全模糊查询

当我们在SQL查询使用左模糊或者全模糊匹配的时候，类似下面这样：

# 左模糊查询
select * from user where name='%一灯';
# 全模糊查询
select * from user where name='%一灯%';

根据B+树的特性，即使我们在name字段上建立了索引，查询的时候也是无法用到索引的。

5. 索引访问类型至少达到range级别

索引访问类型常见的有这几个级别，从上到下，性能由好到差。

【JDBC】笔记（1）— JDBC概述

要求SQL索引访问类型至少要达到 range级别，最好到 const级别。

6. 更优雅的使用联合索引

由于联合索引有最左匹配原则，所以需要优先把区分度高的字段放在最左边第一列。

比如要统计用户表中生日字段和性别字段区分度，可以这样统计：

select
    count(distinct birthday)/count(*),
    count(distinct gender)/count(*)
from user;

【JDBC】笔记（1）— JDBC概述

值越大，区分度越高。

出道面试题，下面这条SQL该怎么创建联合索引：

select a from table_name where b=1 order by c;

SQL中用到abc三个字段，创建联合索引的顺序是 （b,c,a）。

这道题还涉及到另一个知识点，SQL执行的顺序：

from > on > join > where > group by > having > select > distinct > order by > limit

7. 注意避免深分页

MySQL深分页的时候，查询性能较差。

select * from user where name='一灯' limit 10000,10;

我们可以采用子查询的方式进行优化：

select * from user
where id in (
  select id from user
  where name='一灯'
  limit 10000,10
);

这样可以减少非聚簇索引回表查询的次数。

8. 单表字段不要超过30个

当单表字段数量过多的时候，加载大量数据也会拖慢查询性能。

如果字段超过30个，不用看，肯定是表设计的不合理。

这时候，可以拆成多张表，用垂直分表的方式，进行冷热字段分离。

9. 枚举字段不要使用字符类型

字符类型会占用更多的存储空间，当我们想要存储枚举值或者表示是否的时候，可以采用 tinyint数值类型，最好采用无符号整数 unsigned tinyint。

10. 小数类型禁止使用float和double

在存储和计算的时候， float 和 double 都存在精度损失的问题，无法得到正确的结果。

所以在涉及到存储小数的时候，必须使用 decimal类型。

11. 所有字段必须设置默认值且不允许为null

字段允许为null，会占用额外的存储空间。

索引并不会索引null值，所以查询null值的时候无法用到索引。

当数值类型允许为null，返回给映射实体类的时候还可能会报空指针异常。

12. 必须创建主键，最好是有序数值类型

如果我们自己没有给表设置主键，InnoDB会自动增加一列隐藏的主键，我们无法使用到，并且也占用的更多的存储空间，所以建表的时候，必须设置主键。

有序数值更适合做主键，插入数据的时候，由于是有序的，不会频繁调整B+树结构，性能更好。

13. 快速判断是否存在某条记录

一般我们判断表中是否存在某条记录的时候，会使用count函数，然后判断返回值是否大于1。

select count(*) from user where name='一灯';

InnoDB存储引擎并没有像MyIsAm那样缓存表的总行数，每次查询都是实时计算的，耗时较长。

我们可以采用limit加快查询效率：

select id from user where name='一灯' limit 1;

limit 1表示匹配到一条就返回，查询效率更好，结果集只返回id，还可以用到覆盖索引。

14. in条件中数量不宜过多

in条件中数量不要超过1000个，不然耗时会非常长，可以拆成多批次查询。

15. 禁止创建预留字段

无法通过预留字段的名称判断这个字段是干嘛用的。

预留字段的类型不一定合适。

无法为预留字段创建合适的索引。

16. 单表索引数不要超过5个

创建适当的索引可以提高查询效率，但是过多的索引，不但占用更多存储空间，还会拖慢更新SQL的性能。

所以，索引好用，适度即可。

知识点总结：

【JDBC】笔记（1）— JDBC概述

文章持续更新，可以微信搜一搜「 一灯架构 」第一时间阅读更多技术干货。

Original: https://www.cnblogs.com/yidengjiagou/p/16545435.html
Author: 一灯架构
Title: 精心整理16条MySQL使用规范，减少80%问题，推荐分享给团队

相关阅读2

Title: 动手实验查看MySQL索引的B+树的高度

一：文中几个概念

h：统称索引的高度；
h1：主键索引的高度；
h2：辅助索引的高度；
k：非叶子节点扇区个数。

二：索引结构

【JDBC】笔记（1）— JDBC概述
叶子节点其实是双向链表，而叶子节点内的行数据是单向链表，该图未体现。
【JDBC】笔记（1）— JDBC概述
磁盘块其实是页，用操作系统中的术语来表达而已。
InnoDB中使用的是B+树聚集索引，主键索引叶子节点有整行的数据，辅助索引有主键值（用于回表查询）和索引值。

2.1 页的概念

Mysql的InnoDB是以页为存储单位的，每个B+Tree的节点都是一个页的大小，默认一页的大小是16K（与操作系统数据读取相关）。

【JDBC】笔记（1）— JDBC概述
数据页（即叶子节点）

【JDBC】笔记（1）— JDBC概述

2.2 索引高度h与页面I/O数的关系

每次查询都要访问到叶子结点，其访问的页面数正好就是索引的高度h。例如，一次主键上的点查询SELECT * FROM USER WHERE id=1，那么要查询h1个页面才能找到叶子结点里的行数据，也即进行h1次页面I/O。（另外，二级索引基本都加载在内存里了，这里我们暂忽略这种情况。）

综上，查询对应的页面I/O数跟利用的索引有关，主要分为以下几种情况：

  • 点查询：
  • 聚族索引：h1
  • 二级索引：
    • 覆盖索引：h2
    • 回表查询：h2+h1
  • 范围查询：这种情况相对比较复杂，但跟点查询的原理类似，读者可自行分析；
  • 全表查询：B+树的叶子结点是通过链表连接起来的，对于全表查询，需要从头到尾将所有的叶子结点访问一遍。

2.3 索引高度理论计算

索引页（非叶子节点）中可以分割为多个扇区，每个扇区再指向某子节点（某页）。
假设非叶子节点扇区数为k个、高度h、叶子结点的行记录数为n，则叶子结点数为k(h-1)，总记录数为k(h-1)n。
InnoDB每个页面默认16KB，假设主键是4B的int类型。对于非叶子节点，每个主键值后有个页号4B，还有6B的其他数据(参考《MySQL技术内幕：InnoDB存储引擎》)，那么扇区个数k=16KB/(4B+4B+6B)≈1170。
假设每行记录大小为1KB，则每个叶子结点可以容纳的记录数n=16KB/1KB=16。*

在高度h=3时，叶子结点数=1170^2 ≈137W，总记录数=1170^2*16=2190W！！也就是说，InnoDB通过三次索引页面的I/O，即可索引2190W行记录。

同理，在高度h=4时，总行数=1170^3*16≈256亿条！

三、动手查看索引真实高度

【JDBC】笔记（1）— JDBC概述

页的Page Header包含一个PAGE_LEVEL的信息，用于表示当前页所在索引中的高度。默认叶子节点的高度为0，那么Root页（根节点）的PAGE_LEVEL+1就是这棵索引的高度。

【JDBC】笔记（1）— JDBC概述

**怎样得到一张含有所有索引的Root页所在的位置的表呢？在《MySQL技术内幕：InnoDB存储引擎》书中分析过这个页（即ibd文件的第3个页面，从0开始）是聚簇索引的Root页，在《MySQL内核：InnoDB存储引擎 卷1》中也分析，Root页的位置通常是不会更改的。那么其他索引的Root页所在的位置呢？通过下面的SQL语句可以查出表中各索引的Root页信息：

SELECT b.name, a.name, index_id, type, a.space, a.PAGE_NO
FROM information_schema.INNODB_SYS_INDEXES a,
     information_schema.INNODB_SYS_TABLES b
WHERE a.table_id = b.table_id
      AND a.space <> 0;

【JDBC】笔记（1）— JDBC概述

其中就是索引的Root页信息，SPACE可以认为是表的ibd文件，PAGE_NO代表ibd文件中的页面号（从0开始）。有了这些信息就可以方便的定位了，因为PAGE_LEVEL在每个Root页的偏移量64位置处，占用两个字节，这样我们通过hexdump（show global variables like "%datadir%"可以查看MySQL数据文件位置）就可以快速定位到各索引树的高度信息了。例如，我们通过如下命令查看guli/edu_comment表主键索引的高度：

$hexdump -C -s 49216 -n 10 edu_comment.ibd
0000c040  00 01 00 00 00 00 00 00  00 9a                    |..........|
0000c04a

这里，49216表示的是163843+64，即从第3个页内偏移量64位置开始读取10个字节，前两个字节为PAGE_LEVEL，后8个字节是index_id，就是上图中看到的index_id=154（0x9a(十六进制) = 154(十进制)）的主键索引，这里 PAGE_LEVEL为00 01*，那么索引树的高度就为2。

四、插入10w条数据查看索引的高度

delimiter;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do insert into `guli`.`edu_comment` (`id`, `course_id`, `teacher_id`, `member_id`, `nickname`, `avatar`, `content`, `is_deleted`, `gmt_create`, `gmt_modified`) values (i, '1192252213659774977', '1189389726308478977', '1', '小三123', 'ht', '课程很好', 0, '2019-11-13 14:16:08', 14:16:08'); set i="i+1;" end while; end;; delimiter; < code></=100000)do>

经过1分多钟的插入，edu_comment表中的数据已经达到了10w条，再次查看主键索引的高度。

$hexdump -C -s 49216 -n 10 edu_comment.ibd
0000c040  00 02 00 00 00 00 00 00  00 9a          |..........|
0000c04a

可以看到主键索引的高度来到了3层，由于服务器硬盘容量较小，插入了1900w条数据。主键索引在数据量达到3w左右会从2层高度上升到3层（辅助索引会在数据量为数万到数十万时上升到3层高度，因为仅含主键值和索引值，没有整行数据）。根据网上资料，数据量在2000w左右时，树的高度会达到4层，数据库性能下降较为明显，2000w分库分表的由来。

【JDBC】笔记（1）— JDBC概述

$hexdump -C -s 49216 -n 10 edu_comment.ibd
0000c040  00 03 00 00 00 00 00 00  00 9a                    |..........|
0000c04a

主键索引高度来到了4层，主键类型为char(19)。

索引高度h也跟索引字段的数据类型有关。如果是int或short，扇区多，索引效率更好，整个索引看起来属于"矮胖"型；而如果是varchar(32)等，那扇区少，整个索引看起来属于"瘦高"型，索引效率自然要低些。所以我们在字段选取类型时，其类型越简单效率越好。

分页查询效率：

【JDBC】笔记（1）— JDBC概述

参考资料：
[1]MySQL索引的B+树到底有多高？
https://mp.weixin.qq.com/s/VmgpA3fZlv0JxERYB2tt5g
[2]面试官：MYSQL单表数据达2000万性能严重下降，为什么?

https://mp.weixin.qq.com/s/7_Wv3wZX5sOxF17iSM436A
[3]一文搞懂MySQL索引页结构
http://www.cppcns.com/shujuku/mysql/463625.html
[4]再有人问你为什么MySQL用B+树做索引，就把这篇文章发给她
https://mp.weixin.qq.com/s/8nx4yLOg542p_fmqjKDrKw
[5] http://blog.codinglabs.org/articles/theory-of-mysql-index.html

Original: https://www.cnblogs.com/BetterCallSaul/p/MySQL.html
Author: 得失乐与悲与梦儿
Title: 动手实验查看MySQL索引的B+树的高度

相关阅读3

Title: MySQL之SQL语句优化

即优化器利用自身的优化器来对我们写的SQL进行优化，然后再将其放入InnoDB引擎中执行。

移除不必要的括号

select * from x where ((a = 5));

上面的括号很没必要，优化器就会直接去掉。

select * from x where a = 5;

等值传递

select * from x where b = a and a = 5;

同样的，虽然是两列比较，但是a的值只有一个，所以可以优化

select * from x where b = 5 and a = 5;

常量传递

select * from x where a = 5 and b > a;

可以优化为

select * from x where a = 5 and b > 5;

移除没用的条件

select * from x where a < 5 and b > 10 and b > a;

当前两个条件发生时，最后一个条件必然发生，所以可以优化

select * from x where a < 5 and b > 10;

表达式计算

select * from x where -a > -5;

优化器不会对其进行优化，而且这个坏处很多就是不能使用索引了，所以我们尽量让列单独出现，而不是在表达式计算中。

常量表检测

当表中只有一两条数据，或则使用主键或唯一列的索引等值查询的话就会被MySQL优化器视为常量表，直接将SQL语句优化成常量。

select * from table1 join table2 on table1.col1 = table2.col2 where table1 = 'a';

select table1的列都作为常量，table2.* from table2 where table1的常量col1 = table2.col2;

外连接呢，首先连接的顺序是固定的，故驱动表和被驱动表是固定不变的。所以是不能像内连接一样交换驱动表的。

但是呢，有一种情况

select * from table1 left join table2 on table1.col1 = table2.col2 where table2.col2 is not null；

我们设定了table2的列是非空的，这意味着什么，当table1匹配不到时设置table2列为null，但是却不满足搜索条件被过滤掉，所以左连接匹配失败null相当于是失效的。这个语句和内连接是没有区别的，直接将其优化为内连接即可。

所以当在外连接出现时，但是被驱动表 拒绝空值时，此时外连接和内连接是可以互相转换的，而内连接可以通过交换驱动表来优化SQL查询成本。

子查询分类

  • 标量子查询
  • 列子查询
  • 行子查询

  • 表子查询

  • 相关子查询

  • 不相关子查询

标量子查询

不相关标量子查询

select * from x where key1 = (select y.key1 from y where y.primarykey = 1);

对于不相关的标量子查询来说，就是先执行子查询，然后在对外部查询进行查询。

相关子查询

select * from s1 where key1 = (select common_field from s2 where s1.key3 = s2.key3 limit 1);

对于相关的标量子查询

其实和连接的流程差不多。

优化器对于标量的子查询并不需要什么优化，因为对于标量的子查询来说，数据量还算很小的了。

IN子查询优化

select * from x where key1 in (select key3 from y);

对于上述不相关的IN查询来说，如果IN子查询的参数少的话，还可以试着加载到内存，然后让外层查询对很多的条件进行比较。

但是如果子查询数据量一旦大了起来，内存无法全部加载完，或导致外层查询需要比较的参数太多，外层记录需要对于过多条件进行比较，导致索引无法使用，因为每一次都要使用索引，每次都要比较，还不如直接全表扫描。最后导致性能很低。

MySQL对这种in参数过多时，不会将子查询在作为外部的参数，而是直接创建一个临时表来存储子查询的结果。

子查询转物化表materialized_table后，我们还能将物化表和外层查询转换为连接的方式。

select x.* from x inner join materialized_table m on key1 = m.key3;

然后我们就可以用之前计算成本的知识来计算那个作为驱动表更合适了。

只有不相关子查询才能转换为物化表

像上述结果一样，我们将查询结果转换为物化表，然后我们在把物化表转换为连接的方式。

我们为什么不能直接将子查询转换为连接的方式呢？这就是semi-join优化。

我们可以试试将其转换为如下语句

select x.* from x join y on key1 = key3;

三种情况

  • 被驱动表y的行不满足连接条件的，不能加入结果集。
  • 被驱动表y一个key3满足和驱动表x的key1相等且y表key3有且仅有一条，有一条记录加入结果集。
  • 被驱动表y有key3满足连接条件但是一个key3有很多条记录，就会有多条记录加入结果集。

能满足的条件就是y表的key3是主键或唯一列，不然就会出现多条的情况，这条语句就不等于原语句了。

但是此时semi join半连接概念的出现，在半连接的情况下， 对于驱动表x来说，我们只关心被驱动表y是否有记录能够满足连接条件的，而不关心被驱动表y有几条能匹配，最后结果集只保存驱动表x的记录。

实现半连接semi join的方法。PS：semi join半连接只是一个概念。

  • Table pullout （子查询中表上拉）
  • 当子查询的查询列 ( 即select 的列 ) 是主键或唯一列，就是我们上面说的直接join 出来即可，因为不会出现多条的情况
  • DuplicateWeedout execution strategy （重复值消除策略）
  • 我们不是提到上述的我们自己改为join的方法会出现重复的情况吗，因为被驱动表的重复导致驱动表的重复。
  • 我们就直接创建一个临时表，把s1连接的结果记录id (是数据行的id可以这么理解把) 放入临时表中，当该数据行再次被加入时临时表就会抛出主键重复的异常，就不会加入重复行了。
  • LooseScan execution strategy （松散索引扫描）
  • 当子查询列key1有子查询表的索引，这样我们就可以通过索引访问，对于每个值，只访问一行，重复值不再访问，这样来防止出现多条记录。
  • Semi-join Materialization execution strategy (物化表半连接)
  • 将 不相关子查询通过物化表的方式物化为临时表，没有重复行的情形，我们可以直接转换为连接。
  • FirstMatch execution strategy （首次匹配）
  • 取外连接的一条记录，然后和子查询进行一条一条的比较。最原始的方法

semi join使用条件：

  • 该子查询必须是和IN语句组成的布尔表达式，并且在外层的Where和on子句中出现。
  • 外层的搜索条件必须是用and 和in子查询连接的。
  • 子查询是单一的查询，不能union
  • 子查询不能包含group by、having、聚集函数
  • ...

如果不能使用semi join和物化表，我们还可以将in的语句改造成EXISTS语句。

将上述改造为如下语句。

select * from x where exists (select 1 from y where key3 = x.key1)

如果被驱动表key3有索引，就可以使用索引了啊 o(￣▽￣)ｄ。

这个算是下下策了。

Original: https://www.cnblogs.com/duizhangz/p/16306834.html
Author: 大队长11
Title: MySQL之SQL语句优化