Java核心基础(2)——MySQL

  • Z时代
  • 2024-01-10
  • 分类：综合

java

索引

什么是索引？

索引是帮助MySQL高效获取数据的排好序的数据结构。

索引可选的数据结构

二叉树

不适合作为索引的原因：

当数据单边增长时，如select * from T where T.value=4;


查找value = 4要从根节点经过4步，与无索引效果相同，效率低下，不起作用，在某些场景不符合要求。

红黑树

红黑树性质：

  • 节点是黑色或黑色

  • 根节点是黑色

  • 每个叶子节点是黑色

  • 每个红色节点的两个子节点一定是黑色

  • 任意节点到叶子节点的路径都包含相同数量的黑色节点

  • 通过旋转、变色维持平衡

不适合作为索引的原因：

红黑树通过旋转、变色维持平衡，操作步骤繁琐，数据量很大时，红黑树效率很低。

有了二叉平衡树为什么还要发明红黑树？

红黑树是一种弱平衡树，没有二叉平衡树要求严格，旋转次数少，更适合添加和删除，AVL适合查找。

哈希表

哈希表特点：

唯一键值对，搜索很快，哈希码对应唯一数据。

哈希表可用于索引，但不可用于范围查找。

如select * from t where t.age > 18;，搜索年龄大于18的数据，范围查找，此时哈希索引失效。

B+树

索引的最佳选择

B+树特性：

  • 非叶子节点不存储data，只存储索引（会冗余），可以放更多索引。

  • 叶子节点包含所有索引字段

  • 叶子节点用双向指针连接，提高区间访问的性能，方便范围查找

如：


假设查找索引元素30：

  1. 根节点一般常驻内存，内存I/O速度极快，根节点load至内存，在内存中查找，发现30存在于15~56中。

  2. 将根节点存储的15~56子树节点地址加载，找到对应的磁盘内容，load至内存，查找30。

  3. 发现30在2049之间，根据2049子树地址，加载磁盘数据，子树load到内存，查找30，找到30对应的数据。共两次磁盘读写，速度较快。

使用B+树作为索引的优点：

  • B+树广度扩展，导致树的层次较少，少于4次可查到目标

  • 内存查找，速度很快，避免了不必要的磁盘I/O

既然内存I/O很快，为何不将索引全部放入根节点，直接加载至内存？

根节点结构：


存在问题：

  • 数据量很大时，会导致需要留大量空间给B+树根节点，浪费空间

  • 受限于I/O上限，查找第一步，无法将所有大体积根节点加载入内存

所以，MySQL默认分配16KB给每一层树节点

默认16KB，索引占8B，指向下一节点的地址存放指针占6B，每一层共可存放 16×1024÷(8+6) = 1170 组元素。

假设深度为3，叶子节点每个元素占1KB，则有叶子元素16个。

所以，最大可存放共 1170×1170×16 个元素，数量较多，而且总层次少，查找迅速。

可用SHOW GLOBAL STATUS LIKE \'Innodb_page_size\';查到当前页大小。

B树

B树与B+树区别

  • B树每一个节点元素都存放对应的data，B+树仅在叶子节点存放data（中间节点仅用于存放索引）

  • B+树叶子节点链表相连，便于查找，B树则需要不断遍历

形如：

若查找18和21，则先找到18，再返回20，再遍历到21；若是B+树查找18和21，直接使用链表连接即可。

B树优点：

  • 若经常访问的元素靠近根节点，查找会很快，因为遍历到节点，直接可以取出data；而B+树需要遍历到叶子节点，才能取出data

  • B树无冗余索引，B+树有

综合对比，B+树更适合作为索引，B树不适合

常见MySQL数据引擎

数据引擎用于数据库表，表级别，不同的表可用不同的数据引擎

MyISAM：索引文件和数据文件是分离的

在MySQL安装目录中（data/数据库名/），假设以MyISAM建表 test ，会在目录中存在 test.frm（表结构文件），test.MYD（MyISAM建表数据文件），test.MYI（MyISAM建表索引文件），并使用B+树建立索引。

假设根据col1建立索引，则test.MYI中会有如下B+树索引：

使用查询语句 select * from test where test.col1=30; ，执行过程：

InnoDB：索引聚集

特点：

  • 表数据文件本身就接B+树组成一个索引结构文件，不分开建立索引文件和数据文件

  • 聚集索引叶子系欸但包含完整数据记录

在MySQL安装目录中（data/数据库名/），假设以InnoDB建表 test ，会在目录中存在test.frm（表结构文件）和test.ibd（存放建表数据和索引）

假设如下为test.ibd中的主键索引：

聚集（聚簇）索引与非聚集（稀疏）索引

聚集索引的效率更高。

聚集索引

叶节点包含完整数据记录，数据、索引存放于同一文件（InnoDB）。

非聚集索引

叶节点包含数据地址，数据存放在别的文件，索引与数据分离（MyISAM）。

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

为什么必须有主键

InnoDB必须需要一个主键来组建索引，以存放主键联系其他字段。

如果不建主键，MySQL会自动找一列不重复字段建立唯一索引，视为主键建立B+树；

如果找不到唯一字段，MySQL会建立一个隐藏链，实现唯一索引，所以最好自己建立好主键。

为何使用整型自增主键

B+树需要查找元素，比较各元素大小，使用自增整型方便比较大小，不会出错，且占用空间小，整型的利用率更高。

B+树底部叶子节点使用双向链表相连

主键自增，在建表时，会自动在链表后端添加结点，不会破坏之前结构。

为什么MySQL非主键索引结构叶子节点存储的是主键值？

为了保持一致性并节省空间。

如非主键索引 col3：

非主键索引找到数据中的主键字段后，再去主键索引中取出完整数据。仅主键索引存放完整字段，节省空间。

联合索引的底层数据结构长什么样？

创建联合索引语句：CREATE INDEX \'index_name\' ON t(a, b, c);

B+树依次递增，那联合索引如何排序？

逐字段排序，先2比较第一个字段大小，若相等则比较第二个，若相等则比较第三个...

如联合索引a, b, c：

如上图，①②③a字段相等，则比较b字段大小，排序。

如④⑤a,b字段相等，则比较c字段，排序。

最左前缀原则

若使用联合索引，select时一定要从最左边字段开始索引并不可跳过索引中的列，如

KEY \'idx_name_age_position\' (\'name\', \'age\', \'position\') USING BTREE;

  • SELECT * FROM t_employees WHERE age=22 AND position=\'manager\'; 不会使用索引，因为age、position跳过了最左索引name；

  • SELECT * FROM t_employees WHERE position=\'manager\'; 同理，不使用索引，因为跳过了position左侧索引name和age；

  • SELECT * FROM t_employees WHERE name=\'lilei\' AND age=22; 使用索引，因为包含最左前缀，且无中间列被跳过；

  • SELECT * FROM t_employees WHERE age=22 AND name=\'lilei\'; 也使用索引，顺序不重要，MySQL底层会调整。

为何使用最左前缀原则

联合索引(a, b, c)存储在B+树时，根据a，b，c排序，先根据a字段排序，a相同时，再根据b，a、b都相同时，再根据c。

若最左前缀a不重复，按a排序后，对应的b是无序的。

若最左前缀a重复，按b排序后，对应的c是无序的。

若不使用最左前缀，跳过底层优先排序的a字段，直接根据b、c查找，会导致无法依据联合索引查找排序，因为b、c存储不完全有序，无法在B+树中找到对应位置。

若跳过优先排序的a字段，直接根据b查找，会发现b字段在b+树中是无序排列的，无法查找。

只有最左索引在b+树中是绝对有序排列的。

同理，若使用a、c查找，先根据a查找，相同的a数据定位后，理应在b+树中根据b查找，因为a重复时，b+树根据字段b大小进行排序，但由于select语句中无b字段，直接使用c，会导致所查c字段无序，无法查找，所以不能跳过中间字段，即最左前缀原则：

  • 查找需从最左字段开始（a开头）

  • 查找不可跳过中间字段（如ac不可，ab可，abc可，c前必须有b，b前必须有a）

MySQL事务

MySQL事务特性（ACID）

原子性

如转账操作，要么成功，要么失败，没有转账了一半的场景，要么全做，要么全不做的规则称为原子性。

隔离性

如A向B转20元，B向C转20元。两个事务不能相互影响，事务间相互独立。

一致性

数据的一致性，数据总数总是从一个一致性状态转移到另一个一致性状态。

持久性

一旦事务提交，所做修改永久保存。

事务自动提交

使用语句查看 show variables like "autocommit";

如果是on，每次数据操作，MySQL会自动开启事务、操作、提交。

如果是off，需要手动开启事务，使用begin语句；修改后再使用commit语句提交事务或回滚命令rollback。

保存点：

  • 使用savepoint t1设置保存点t1

  • rollback to t1回滚至保存点t1

事务读取可能存在的问题

脏读

事务A读取数据3000，事务B读取并修改数据为4000，未提交，此时事务A再读取数据变成了4000。

即事务可以读取到别的事务未提交的数据。

不可重复读

事务A读取数据3000，事务B读取并修改数据为4000，commit，事务A再读数据变成4000。

即一个事务前后两次或多次读取数据结果数值不一样。

幻读

A事务修改表中所有10条数据，同时B插入了新数据，commit。A事务修改完成后，会发现少了一条新数据没有修改。

即修改不是独立进行，修改前后发现数据条数不一致，就像产生了幻觉一样

隔离性的四种隔离级别

使用set session transcation islation level 隔离级别;修改至特定的隔离级别。

读未提交（READ_UNCOMMITED）

一个事务可以读到其他事务修改还没提交的数据，会出现脏读、不可重复读、幻读。

读已提交（READ_COMMITED）

一个事务只能读到另一个事务已经提交的修改数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，会出现不可重复读、幻读。

可重复读（REPEATABLE_READ）(MySQL默认)

一个事务读取过某条记录后，即使别的事务修改了数据，该事务再次读取也不会改变读到的值，解决了不可重复读。

sql标准下可重复读无法解决幻读，但MySQL做了优化，可解决幻读。

串行化（SERIALIZABLE）

将读与写排队，不允许读-写、写-读并发，将操作串行。

如A在读，若此时B写操作将被阻塞直到A读完；若A写，则B读会被阻塞，直到A写完，不会幻读、不可重复读。

版本链

什么是版本链？

代表某行数据的版本控制，记录被哪些事务修改。

对于使用InnoDB存储引擎的表来说，它的聚集索引记录中都包含两个必要的隐藏列：

  • trx_id：每次对某条记录进行改动，都会把对应的事务id赋值给trx_id隐藏列

  • roll_pointer：每次对某条记录改动，这个隐藏列会存一个指针，指向上一次修改前的信息

如：

ReadView机制

当事务在开始执行的时候，会给每个事务生成一个ReadView，是一个存储当前未提交活跃事务的容器。

其中重要属性：

  • trx_ids：表示在生成ReadView时当前系统中活跃的事务的id对象列表

  • min_trx_id：表示当前系统中活跃的事务中最小的事务id，即trx_ids中的最小值

  • max_trx_id：表示生成ReadView时系统应该生成给下一个事务的id

MVCC

多版本并发控制，用于控制数据库的读，可以并发地读，即在读已提交、可重复读，这两种隔离级别下的事务，使用版本链控制查询与访问，可以使读-写、写-读并发执行，提高性能，并且不需要使用锁。

MVCC中可重复读与读已提交最大的区别：生成ReadView的时机不同

  • 可重复读第一次select时生成生成ReadView的时机不同，后续select直接复用旧的，保证每次读取数据相同

  • 读已提交每次select都重新生成ReadView，无法解决不可重复读问题

读已提交（READ_COMMITED）中事务如何读取数据

在事务每次读取时会生成ReadView。

情况A

  1. 如A事务（事务号300）读数据，ReadView生成trx_ids列表=[82,200,300]记录当前未提交的活跃事务

  2. A事务会顺着版本链与trx_ids去找最近的非活跃事务提交的数据，再把数据读出来

    A（300）--> 200活跃 --> 82活跃 --> 81非活跃

    读取81事务提交的数据

情况B

  1. 假设此时200号事务提交了，A事务（300）想查数据，生成trx_ids=[82,300]

  2. A顺着版本链与trx_ids找

    A（300） --> 200非活跃

    读取数据

因此在读已提交隔离级别下会出现不可重复读，情况A与情况B，同样的事务300，前后读取到不同事务提交的数据，数据不一致。

可重复读（REPEATED_READ）中事务如何读取数据

仅在事务第一次读取生成ReadView

情况A

  1. 同读已提交，如A事务（事务号300）读数据，ReadView生成trx_ids列表=[82,200,300]记录当前未提交的活跃事务

  2. 顺着版本链与trx_ids去找最近的非活跃事务提交的数据，再把数据读出来

    A（300）--> 200活跃 --> 82活跃 --> 81非活跃

    读取81事务提交的数据

情况B

  1. 若此时事务200提交，且事务A再次读取，不同于读已提交下事务A生成新的ReadView，在可重复读隔离级别下，事务A直接复用第一次读取生成的trx_ids=[82,200,300]

  2. 再次顺着旧的trx_ids找

    A（300）--> 200活跃 --> 82活跃 --> 81非活跃

    读取81事务提交的数据

    而不是300 --> 200非活跃

在可重复读隔离级别下，由于新的读取复用旧的trx_ids，别的事务提交，对A事务并无影响，解决了不可重复读。

MySQL锁

MVCC用于控制数据库的读，锁用于控制数据库的写。

读锁与写锁

读锁

也叫共享锁、Shared Locks、S锁，其他事务可以读，但不能写。

写锁

也叫排他锁，Exclusive Locks、X锁，其他事务不能读取，也不能写。

冲突情况

X锁S锁
X锁冲突冲突
S锁冲突不冲突

  • 若一个资源有一把写锁，其他所有锁都不能加，阻塞排队

  • 若一个资源有一把读锁，还可以加别的读锁

  • 一个资源可以加多个读锁

读写锁与CRUD语句

SELECT

  • 普通的SELECT不加锁，不与写锁、读锁冲突

  • 读锁语句：SELECCT ... LOCK IN SHARE MODE;，将查到的数据加S锁

  • 写锁语句：SELECCT ... FOR UPDATE;，将查到的数据加X锁

DELETE

先加X锁，再执行删除。

UPDATE

  • 如果被更新的列，修改前后存储空间不变，先给记录加X锁，再修改数据

  • 如果被更新的列，修改前后存储空间发生变化，先给记录加X锁，然后删除记录，重新插入新纪录

INSERT

会加隐式锁，来保护这条新插入的数据在提交前不被别的事务访问。

COMMIT与ROLLBACK

事务COMMIT与ROLLBACK结束后，会释放锁。

行锁

  • LOCK_REC_NOT_GAP：单个行记录上的锁

  • LOCK_GAP： 间隙锁，锁定一个范围，不包括记录本身。可以防止同一事务读两次，出现幻读

  • LOCK_ORDINARY： 也是一种间隙锁，锁定一个范围，并锁定记录本身，可以解决幻读，对于行的查询，主要使用本锁

在READ_COMMITED下的行锁

只会对查询出来的数据进行加锁，查询结果未涉及的行数据不会加锁。

如表（A,B,C,D）四条记录，update B、C后，B、C会加锁，AD不加锁。

在REPEATABLE_READ下的行锁

使用间隙锁，当A事务查询数据B、C，加读锁。

此时B事务无法向B、C插入数据，因为B、C相邻的间隙也被上锁。

间隙锁可以解决幻读。

REPEATABLE_READ如何解决不可重复读、幻读

如何解决不可重复读

使用MVCC，查询时第一次查询创建ReadView，后续查询直接复用，避免前后查询到的结果不一样。

针对select查询。

如何解决幻读

当有事务A读数据时，加间隙锁，锁住相关记录与记录间间隙，其它事务想插入新数据或操作时，会被阻塞，直到事务A commit后。

针对update更新。

表锁

当事务对表进行ALTER、DROP时，加表锁，其它事务对这个表的CRUD会被阻塞。

加表锁语句：

  • LOCK TABLES t1 READ; 加表级别S锁

  • LOCK TABLES t1 WRITE; 加表级别X锁

注意：

  • 加表锁前会遍历表中有无行锁，如果有行锁，表锁无法被加上

  • 由于全表遍历效率低，在加行锁时，会同时加表意向锁IS、IX

IS：意向共享锁，当为某行数据加S锁时，会为该行所在表加IS

IX：意向排他锁，当为某行数据加X锁时，会为该行所在表加IX

后续加表锁时，若发现表中有意向锁，就不用再遍历所有行看有没有行锁了。

如果表中有意向锁，表锁加不上，如果无意向锁，表锁直接加。

MySQL优化

索引优化

不要建太多索引

因为在增删改操作时，还要同步维护索引，索引过多，导致维护过慢，降低数据库性能。

索引不能包含null

如果索引字段某行数据有null，此行不会被包含进索引。

组合索引中只要有一列有null，这一列对于组合索引就是无效的。

在数据库设计时，不要让默认值为null。

尽量做短索引

对串进行索引时，如varchar(255)，尽可能指定一个前缀长度，即只对该前缀长度B+树排序，可以大部分过滤。

语句：create index index_name on table_name(col1(length));

索引列排序

在where中，如果oredrBy的字段在索引中，将根据索引排序，orderBy不起作用，因此如果默认的排序可行，可以不使用orderBy。

不要包含多个列的排序，如orderBy(\'a\',\'b\',\'c\')，如果需要，最好加上组合索引index(a_b_c)。

like语句

不鼓励使用like语句，有时会索引失效，如：

like %aaa%不走索引

like aaa%走索引

不要在列上做计算

如 select * from users where YEAR(birth) < 2007; ，其中YEAR()是一个计算函数。

该计算会在每行上进行运算，导致索引失效而进行全表扫描。

可改成select * from users where birth < \'2007-01-01\'; 在数据库设计时，让数据更完整，以避免字段计算。

MySQL索引只对<, <=, =, >, >=, between, in以及无通配符的like生效，索引的数量最好不要大于6个。

SQL优化

避免全表扫描

在WHERE和ORDERBY的列上添加索引。

避免判断Null

尽量避免null的判断，否则会导致引擎放弃索引而全表扫描。

避免不等值的判断

不写!=或<>，会导致全表扫描。

避免使用or

OR会使引擎放弃索引，全表扫描。可以使用UNION ALL替代。

慎用in和not in

IN和NOT IN也会导致全表扫描，可以使用表连接查询。

注意模糊查询

注意使用通配符，%abc%会导致全表扫描，abc%不会，可使用Elasticsearch等中间件替代sql模糊查询。

避免字段计算与函数操作

同样会导致全表逐行计算，进行全表扫描。

组合索引注意最左前缀原则

遵循最左前缀，避免查询不走索引。

避免使用in

用EXISTS代替IN。

注意索引效果

索引有时可能效果不好，如表中性别male和female各占一半，索引提升不了效率。

字段设计

尽量使用数字型字段，尽量用varchar替代char，因为varchar不固定长度，char必须长度固定。

尽量不用 *

尽量用具体查询字段，避免使用*，不查询无用字段，会导致查询效率低。

以上是 Java核心基础(2)——MySQL 的全部内容， 来源链接： utcz.com/z/390923.html

回到顶部