MySQl

定义

DML(data manipulation language):
它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
DDL(data definition language):
DDL比DML要多,主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用
DCL(Data Control Language):
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。在默认状态下,只有sysadmin, dbcreator, db_owner或db_securityadmin等人员才有权力执行DCL

join 中 on 与 where

数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。
在使用 left join 时,onwhere 条件的区别如下:
1、 on 条件是在生成临时表时使用的条件,它不管 on 中的条件是否为真,都会返回左边表中的记录。
2、where 条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有 left join 的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

left join 与 inner join

1、对于left join,不管on后面跟什么条件,左表的数据全部查出来,因此要想过滤需把条件放到where后面。
2、对于inner join,满足on后面的条件表的数据才能查出,可以起到过滤作用。也可以把条件放到where后面。

count

1.count(1)与count(*)得到的结果一致,包含null值。
2.count(字段)不计算null值
3.count(null)结果恒为0

delete别名

delete a from test

where语句exists与in

如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in。

find_in_set()

如果一条记录的一个字段中有由","分割的多个参数,则可以用find_in_set()查询这条记录中这个字段里的某个参数。

SELECT * FROM 表名 WHERE FIND_IN_SET(要查的内容, 要查的字段);

replace into

replace into 跟 insert 功能类似,不同点在于:replace into 首先尝试插入数据到表中, 1. 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据。 2. 否则,直接插入新数据。

返回1/0

select exists (select ... from ...)
1 true 0 false

B+B-树

二叉查找树

左子树的键值小于根的键值,右子树的键值大于根的键值。

平衡二叉树(AVL)

是一种二叉树,满足任何节点的两个子树的高度最大差为1

B-Tree(平衡多叉查找树)

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

指向磁盘块的指针 | 主键(挂数据)| 指向磁盘块的指针

B+Tree

1、节点上只存储键值,不存储数据,这样一来,在有限的节点空间(页空间)内就可以存放更多的键值、指针;

2、所有数据都放在叶子节点中,所有叶子节点之间有链指针(双向循环列表),便于范围查找,也便于排序。

InnoDB的索引使用的是B+Tree结构,那么为什么InnoDB的主键最好要搞成有序的?
InnoDB中主键索引是聚集索引,所有数据都存在主键索引所在的聚集索引的B+Tree结构的叶子节点中。如果每次插入的主键是大小随机的话,每次数据进来找到的叶子节点的位置是随机的,这样的话,有些叶子节点所在页本来就排满了,结果又来了一条数据,就势必要引起页分裂,所以导致性能下降;但是如果主键是有序的话,每次进行都找到当前叶子前面的位置,一个一个叶子按顺序排满一个页再排一个页,就不会又页分裂的问题了。所以自增主键对于InnoDB这种使用B+Tree索引的存储引擎来说,性能更好。

mysql 索引

聚簇索引:将数据存储和索引放在一起、并且是按照一定的顺序组织的,找到索引也就找到了数据,数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻的存放在磁盘上的。

非聚簇索引:叶子节点不存储数据,存储的是数据行地址,也就是说根据索引查找到数据行的位置再去磁盘查找数据。

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。具体细节依赖于其实现方式。

聚簇索引和非聚簇索引

MySQL数据库中innodb存储引擎,B+树索引可以分为聚簇索引(也称聚集索引,clustered index)和辅助索引(有时也称非聚簇索引或二级索引,secondary index,non-clustered index)。这两种索引内部都是B+树,聚集索引的叶子节点存放着一整行的数据。

Innodb通过主键聚集数据,如果没有定义主键,innodb会选择非空的唯一索引代替。如果没有这样的索引,innodb会隐式的定义一个主键来作为聚簇索引。

在聚簇索引之上创建的索引称为辅助索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置,而是主键值。

MyISM使用的是非聚簇索引,没有聚簇索引。

mysql 锁

Innodb

Innodb分为主键索引,唯一索引,普通索引,非索引。当然主键索引也是一种唯一索引。

首先理解一个概念,锁是在事务开始且语句执行时添加,事务结束后释放。

在 InnoDB 事务中,对记录加锁的基本单位是 next-key 锁,但是会因为一些条件会降级成间隙锁,或者记录锁。加锁的位置准确的说,锁是加在索引上的而非行上。

比如,在 update 语句的 where 条件使用了唯一索引,那么 next-key 锁会降级成记录锁,也就是只会给一行记录加锁。

隐式锁转换

InnoDb 在插入记录时,是不加锁的。如果事务 A 插入记录且未提交,这时事务 B 尝试对这条记录加锁,事务 B 会先去判断记录上保存的事务 id 是否活跃,如果活跃的话,那么就帮助事务 A 去建立一个锁对象,然后自身进入等待事务 A 状态,这就是所谓的隐式锁转换为显式锁。

执行 insert 语句,对要操作的页加 RW-X-LATCH,然后判断是否有和插入意向锁冲突的锁,如果有,加插入意向锁,进入锁等待;如果没有,直接写数据,不加任何锁,结束后释放 RW-X-LATCH;

加锁操作保证原子性涉及硬件支持。

加表锁是给所有记录加上 next-key 锁(记录锁 + 间隙锁),相当于把整个表锁住了。

where 语句中

唯一索引 普通索引 非索引
update 行锁 行锁 表锁
select for update 行锁 行锁 表锁
like 表锁 表锁 表锁
!= 表锁 表锁 表锁

for update仅适用于InnoDB,且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时,通过“for update”语句,MySQL会对查询结果集中每行数据都添加排他锁,其他线程对该记录的更新与删除操作都会阻塞。

自增锁

自增主键不回滚。

sql_safe_updates

我们可以将 MySQL 里的 sql_safe_updates 参数设置为 1,开启安全更新模式。

当 sql_safe_updates 设置为 1 时,update 语句必须满足如下条件之一才能执行成功:

  • 使用 where,并且 where 条件中必须有索引列。
  • 使用 limit。
  • 同时使用 where 和 limit,此时 where 条件中可以没有索引列。

delete 语句必须满足如下条件之一才能执行成功:

  • 使用 where,并且 where 条件中必须有索引列。
  • 同时使用 where 和 limit,此时 where 条件中可以没有索引列。

如果 where 条件带上了索引列,但是优化器最终扫描选择的是全表,而不是索引的话,我们可以使用 force index([index_name]) 可以告诉优化器使用哪个索引,以此避免有几率锁全表带来的隐患。

posted @ 2022-04-16 16:10  云轻YK  阅读(48)  评论(0)    收藏  举报