Mysql的那些事儿(部分涉及数据库知识总结)-CSDN博客

数据库常见索引类型：

1、B-Tree索引
2、哈希索引
3、空间数据索引(R-Tree)
4、全文索引

数据库并发问题：

1、Lost Update 更新丢失
    a. 第一类更新丢失，回滚覆盖：撤消一个事务时，在该事务内的写操作要回滚，把其它已提交的事务写入的数据覆盖了。
    b. 第二类更新丢失，提交覆盖：提交一个事务时，写操作依赖于事务内读到的数据，读发生在其他事务提交前，写发生在其他事务提交后，把其他已提交的事务写入的数据覆盖了。
2、Dirty Read 脏读：一个事务读到了另一个未提交的事务写的数据。
3、Non-Repeatable Read 不可重复读：一个事务中两次读同一行数据，可是这两次读到的数据不一样。
4、Phantom Read 幻读：一个事务中两次查询，但第二次查询比第一次查询多了或少了几行或几列数据。

参考：并发问题介绍

3.事务的四个特性

事务四大特性ACID
1、原子性（Atomicity）：一个事务内包含的所有操作要么成功要么失败
2、一致性（Consistency）：指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改变.
3、隔离性（Isolation）：并发事务之间要有隔离性，事务之间的隔离级别是可以设置的
4、持久性（Durability）：指事务如果执行成功后，对数据库所做的更改会持久的保存在数据库里，不会被无缘无故的回滚。

4.数据库事务隔离级别：

1、Read Uncommitted 读未提交:事务读不阻塞其他事务读和写，事务写阻塞其他事务写但不阻塞读。基本不用，会出现脏读，两次读取能读取到其他事务在期间未提交事务的数据。
2、Read Committed 读已提交:事务读不会阻塞其他事务读和写，事务写会阻塞其他事务读和写。一个事务只能看见已经提交事务所做的改变，会出现一个事务内两次select的结果不一样。这个是常用的事务隔离级别，综合考虑了性能和数据问题。
3、Repeatable Read 可重复读:事务读会阻塞其他事务事务写但不阻塞读，事务写会阻塞其他事务读和写。这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。可重复读应该是无法解决提交覆盖的问题。
4、Serializable 串行化：最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁，可能导致大量的超时现象和锁竞争。

参考：事务隔离介绍

5.数据库锁锁的种类：

共享锁：特点不阻止其他session读同一个资源，阻塞update,共享锁可以同时在同一个资源，容易产生死锁。
更新锁：特点不阻止其他session读同一个资源，阻塞update,共享锁和更新锁可以同时在同一个资源上,可以解决死锁
排他锁：其它事务既不能读，又不能改排他锁锁定的资源。（可以去实现悲观锁）
意向锁：
计划锁：DL语句都会加Sch-M锁，该锁不允许任何其它session连接该表。

6.悲观锁和乐观锁
悲观锁：在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作都某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。悲观锁的实现，往往依靠数据库层提供的锁机制。Mysql InnoDB如果想使用悲观锁，需要关闭自动提交属性（autocommit）,这个是Mysql默认的，Mysql里可以通过select…for update的方式开启悲观锁，不过Mysql默认是行锁，而行级锁都是基于索引的，如果sql用不到索引则不会使用行级锁，会把整个表锁住。悲观锁实际上是先取锁再访问，效率低，降低了并行性，而且会会阻塞其他读事务，造成了不必要锁，增加了系统负载。
乐观锁：在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。乐观锁并不需要使用数据库提供的锁机制，一般只需要比对数据版本即可。乐观锁在多个条件巧合下回出现丢失更新的问题。

7、Mysql常用数据库引擎InnoDB,支持事务、行级锁、并发性能更好。MYISAM不支持事务，只有表级锁。

8.项目里DB层面很多未提交的事务，原因是基本上都是因为出现的并发的DML同一行数据导致的，比如说两个并发的update同一行数据，后面的update语句而开启的事务就会等待第一个update执行完毕提交事务才能执行。

9、对于数据库字符集的选择上，能用utf8mb4字符集就用64吧，不然还得过滤偏僻字和emoj表情

10、项目里使用内网域名链接数据库，不要直接使用ip

11、定义字段时禁止使用枚举，使用tinyint代替,因为增加枚举类型和减少都得DDL操作，而且数据枚举实际存储的也是整数

12、禁止在识别度不高的字段上建立索引，因为基本与全表扫描差不多，比如sex字段基本只有0/1，而shop_id的识别度就会很高，建立联合索引的时候，区分度高的放在前面

13、禁止使用属性隐式转换，比如phone字段是varchar类型，但是在用select * from t where phone = 123123会出现无法命中索引的问题。也禁止在where后的字段上作函数或者表达式。

14、update语句禁止不带条件，万一写错有很大风险

15、千万记住后端开发的一点就是，能在服务器上做的运算、排序尽量在项目里做，数据库只做数据的查询、筛选，将数据库的压力转化到机器。

16、mysql语句执行顺序：开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->SELECT子句->ORDER BY子句->LIMIT子句->最终结果

17、任何字段如果为非负数，必须是 unsigned

18、小数类型为 decimal，禁止使用 float 和 double。float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。

19、合理预估数值的大小，枚举、人类年龄用tinyint等，像shopId、skuId等数值如果预估后面可能会超过int，那么请定义成bigint.

20、mysql中InnoDB表为什么要以自增id作为主键？


1、因为InnoDB引擎表是基于B+树的索引组织表(IOT)。而B+树的特点是
  （1）所有关键字都出现在叶子结点的链表中(稠密索引)，且链表中的关键字恰好是有序的;
  （2）不可能在非叶子结点命中;
  （3）非叶子结点相当于是叶子结点的索引(稀疏索引)，叶子结点相当于是存储(关键字)数据的数据层;
  
2、如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

3、数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

4、如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页

5、如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。