mysql

压缩包下载地址

1. 数据库的三范式

1NF:字段不可分;
2NF:有主键,非主键字段依赖主键;
3NF:非主键字段不能相互依赖;
解释:
1NF:原子性 字段不可再分,否则就不是关系数据库;
2NF:唯一性 一个表只说明一个事物;
3NF:每列都与主键有直接关系,不存在传递依赖;

2. 数据库基本类型

数值型:Tinyint,Smallint,Int,Bigint,Float,Double,Decimal
日期型:Date,Datime,Timestamp,Time,Year
字符串型:Char,Varchar,Text,Tinytext,Mediumtext,Longtext

3. 数据库引擎

对比项MyISAMInnoDB
事物不支持支持
表锁行锁
表空间
全文索引支持不支持

4. 数据库事务(acid)

原子性:构成事务的的所有操作必须是一个逻辑单元,要么全部执行,要么全部不执行。
一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则。
隔离性:并发事务之间不会相互影响。
持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。事务执行成功后必须全部写入磁盘。
事务隔离性的实现原理
数据库事务会导致脏读、不可重复读、幻读等问题。

  1. 脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
  2. 不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。
  3. 幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。
事务隔离级别脏读不可重复读幻读
read-uncommitted(读未提交)
read-committed(读已提交)
repeatable-read(读重复提交)
serializable(串行化)

1、悲观锁(更新多,查询少时用)
如果我们采用悲观锁。就是我们在操作数据库时采用悲观的态度,认为别人会在此时并发访问数据库。我们在查询语句中select * from account where name=‘aaa’ for update; 等于加了排它锁。当A查询余额的时候,select money from account where name=‘aaa’ forupdate; 增加了排它锁,B查询账户余额的时候, select money from account wherename=‘aaa’ forupdate; 也要求对数据库加排它锁,因为A已经拿到了排它锁,导致B不能加锁,所以B只有等待A执行完毕,释放掉锁以后才能继续操作。

2、乐观锁(更新少,查询多时用)
如果我们采用乐观锁,就是我们在操作数据库的时候会认为没有其它用户并发访问,但是乐观锁也不是完全乐观的,乐观锁是采用版本号的方式进行控制的。在数据库表中有一列版本号。从数据库中查询的时候,将版本号也查询过来,在进行更新操作的时候,将版本号加1,查询条件的版本号还是查询过来的版本号。比如,A执行查询操作的时候,selectmoney,version from account where name=‘aaa’; 假设此时查询到的版本号为0,A在进行更新操作的时候 update account set money=money+100, version=version+1where name=‘aaa’ and version=0; 未提交时B来查询,查询到的版本号依然是 0,B也执行更新操作update account set money=money+100,version=version+1 wherename=‘aaa’ and version=0;现在A提交了事务,B再提交事务的时候发现版本号为 0的记录没有了,所以就避免了数据丢失的问题。不过这种情况也导致了多个用户更新操作时,只有一个用户的更新被执行。

3、行级锁(为某一条记录加锁)
如果想对数据库中的某条记录加行级锁,那么 where 条件后面必须为索引列。否则 for update加的都是表级锁。行级锁就是只对要访问的当前行加锁,其他用户访问其它行记录的时候可以访问。 select * from account where id=1 for update;

4、表级锁(为一张表加锁)
在查询语句后增加 for update 时,where 条件后不是索引列,那么此时都是表级锁。select * from account where name=‘aaa’ for update;

数据库索引

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。目的:加快对表中记录的查找或排序,提高系统的性能。

  • 索引的分类

  • 主键索引(PRIMARY KEY)
    唯一标识,不可重复,主键只能有一个

  • 唯一索引(UNIQUE KEY)
    避免重复的列出现,唯一索引可以重复,一个表中可以多个字段设置为唯一索引

  • 常规索引(KEY/INDEX)
    默认 使用KEY或者INDEX来定义

  • 全文索引 (FullTest)
    快速定位数据,只有特定数据库引擎下支持(MyISAM)

优点
创建索引可以大大提高系统的性能。

  • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
  • 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
  • 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
  • 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

缺点

  • 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
  • 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
  • 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

数据库优化

1. 确定高负载的 SQL
2. 避免使用 OR 子句

OR 子句在实际执行中会被转换为类似于 UNION 的查询。如果某一个 OR 子句不能利用上索引则会使用全表扫描造成效率低下,应避免使用。

3.避免使用困难的正则表达式
在 SQL 语言中,LIKE 关键字支配通配符匹配,含通配符的表达式被称为正则表达式。有的正则表达式可以自动优化为非匹配的。例如:a LIKE 'L%'可以优化为a>=‘L’ AND a<‘M’,这样就可以用到 a 上的索引。即使没有索引,转换后的比较也更快。再如:a LIKE 'LM_‘可以转化为 a>=‘LM’ AND a<‘LN’ AND a LIKE ‘LM_’。虽然仍然包含着通配符匹配,但大大缩小了匹配的范围。所谓困难的正则表达式是指开头和结尾都为通配符的正则表达式,如’L%’、’%L’, 优化器没办法缩小它们的匹配范围,也不可能用到索引而必须使用全表扫描。因此要尽可能避免这样的正则表达式。如果仅仅是开头为通配符,用户可以在列 a 上建立 REVERSE(a)这样一个函数索引,利用函数索引反转待匹配项从而使用函数索引进行范围扫描。
4.灵活使用伪表(DUAL)
首先可以利用伪表进行科学计算,执行语句 SELECT 34 FROM DUAL,则可以得到结果 12;
5.SELECT 项避免

除非用户确实要选择表中所有列,否则 SELECT *这种写法将让执行器背上沉重的负荷。因为每一列的数据不得不自下往上层层向上传递。不仅仅如此,如果用户查询的是列存储表, 那么列存储所带来的 IO 优势将损耗殆尽。任何时候,用户都要了解表结构和业务需求,小心地选择需要的列并一一给出名称,避免直接用 SELECT *。

6.避免功能相似的重复索引

索引并非越多越好。抛开优化器面对众多索引逐一试探所耗费的时间不谈,如果表上增删改操作频繁,那么索引的维护将会成为大麻烦,尤其是函数索引的计算开销更不能忽略。

7.使用 COUNT(*)统计结果行数

如果对单表查询 COUNT(**)且没有过滤条件,那么 数据库会直接读取相关索引中存储的行数信息,加以回滚段中其他事务插入或删除元组的行数修正,迅速地给出最终结果而避免对实际数据的读取。相比之下,COUNT(列名)会对数据进行读操作,执行效率远低于COUNT(*)。

8.使用 EXPLAIN 来查看执行计划

9.UNION 和 UNION ALL 的选择
UNION 和 UNION ALL 的区别是前者会过滤掉值完全相同的元组,为此 UNION 操作符需要建立 HASH 表缓存所有数据并去除重复,当 HASH 表大小超过了 INI 参数指定的限制时还会做刷盘。因此如果应用场景并不关心重复元组或者不可能出现重复,那么 UNION ALL 无疑优于
UNION。
10.优 化 GROUP BY … HAVING
GROUP BY 最常见的实现有 HASH 分组(HAGR)和排序分组(SAGR)。前者需要缓存中间结果;如果用户在 GROUP BY 的列上建立索引,那么优化器就会判断并可能使用上该索引, 这时的 GROUP BY 就会变为 SAGR。
HAVING 是分组后对结果集进行的过滤,如果过滤条件无关集函数操作,用户可以考虑将过滤条件放在 WHERE 而不是 HAVING 中。DM 优化器会判断并自动转换部分等效于 WHERE 的 HAVING 子句,但显式地给出最佳SQL 语句会让优化器工作得更好。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值