开发者社区> 铭铭erom> 正文

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

简介: mysql常识
+关注继续查看
  1. 数据库常见索引类型:

    1、B-Tree索引
    2、哈希索引
    3、空间数据索引(R-Tree)
    4、全文索引
  2. 数据库并发问题:
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来重建表并优化填充页面。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Mysql总结_02_mysql数据库忘记密码时如何修改
1.从cmd进入mysql的bin下,输入命令  mysqld --skip-grant-tables  回车      注:(输入命令前,确保在任务管理器中已没有mysql的进程在运行,可输入命令:net stop mysql  来关闭mysql服务,切忌此命令结尾没有分号) mysqld --skip-grant-tables的作用:跳过了mysql的用户验证   2.重开一个新的命令行,输入命令:mysql,连上数据库。
855 0
java中三种主流数据库数据库(sqlserver,db2,oracle)的jdbc连接总结
原文http://www.cnblogs.com/PatrickChen/archive/2008/12/08/1350395.html 其实java开发中连接数据库的主要问题不是如何写代码,而是找驱动包。
1086 0
重新定义数据库的时刻,阿里云数据库专家带你了解POLARDB
POLARDB是阿里云ApsaraDB数据库团队研发的基于云计算架构的下一代关系型数据库,其最大的特色是计算节点与存储节点分离,借助优秀的RDMA网络以及最新的块存储技术。POLARDB不但满足了公有云计算环境下用户业务快速弹性扩展的刚性需求,同时也满足了互联网环境下用户对数据库服务器高可用的需求。
3245 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
20893 0
回顾十年数据库流行度,哪款是你的最爱
从数据库流行度总体走势再到不同类型数据库的流行度变化情况
1449 0
怎么设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程
10629 0
+关注
铭铭erom
目标:顶级互联网公司
35
文章
1
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载