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 时，on 和 where 条件的区别如下：
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]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。