mysql 基础概念

Posted by wukaiqiang; tagged with none

mysql

概述

MySQL是一个小型关系型数据库管理系统,开发者为瑞典MySQL AB公司。在2008年1月16号被Sun公司收购。而2009年,SUN又被Oracle收购。MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内。这样就增加了速度并提高了灵活性。MySQL的SQL“结构化查询语言”。SQL是用于访问数据库的最常用标准化语言。MySQL软件采用了GPL(GNU通用公共许可证)。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

体系结构

https://www.processon.com/view/link/60e4f6d65653bb46e90ece0b

数据库的基本概念

事务

事务保证一组原子性的操作,要么全部成功,要么全部失败。一旦失败,回滚之前的所有操作。MySql采用自动提交,如果不是显式的开启一个事务,则每个查询都作为一个事务。

MYSQL 数据库中一个事务具备ACID表示原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability)。

多事务并发可能出现的问题

1.脏读
一个事务读到了另一个未提交事务修改过的数据。
(脏读只在读未提交隔离级别才会出现)
2.不可重复读
一个事务只能读到另一个已经提交的事务修改过的数据,并且其他事务每对该数据进行一次修改并提交后,该事务都能查询得到最新值。
(不可重复读在读未提交和读已提交隔离级别都可能会出现)
3.幻读
一个事务先根据某些条件查询出一些记录,之后另一个事务又向表中插入了符合这些条件的记录,原先的事务再次按照该条件查询时,能把另一个事务插入的记录也读出来。
(幻读在读未提交、读已提交、可重复读隔离级别都可能会出现)

未提交读
在读未提交隔离级别下,事务A可以读取到事务B修改过但未提交的数据。
可能发生脏读、不可重复读和幻读问题。
已提交读
在读已提交隔离级别下,事务B只能在事务A修改过并且已提交后才能读取到事务B修改的数据。
读已提交隔离级别解决了脏读的问题,但可能发生不可重复读和幻读问题
可重复读
在可重复读隔离级别下,事务B只能在事务A修改过数据并提交后,自己也提交事务后,才能读取到事务B修改的数据。
可重复读隔离级别解决了脏读和不可重复读的问题,但可能发生幻读问题。
串行化
最高隔离级别,强制事务串行执行

提问:为什么上了写锁(写操作),别的事务还可以读操作?
因为InnoDB有MVCC机制(多版本并发控制),可以使用快照读,而不会被阻塞。

提问:为什么innodb默认使用可重复读?
因为,binlog和sql执行顺序问题。

数据库通过锁机制来解决并发场景-共享锁(读锁)和排他锁(写锁)。读锁是不阻塞的,多个客户端可以在同一时刻读取同一个资源。写锁是排他的,并且会阻塞其他的读锁和写锁。
要锁定数据需要一定的锁策略来配合。
表锁,锁定整张表,开销最小,但是会加剧锁竞争。
行锁,锁定行级别,开销最大,但是可以最大程度的支持并发。

mysql 存储引擎
InnoDB引擎,最重要,使用最广泛的存储引擎。被用来设计处理大量短期事务,具有高性能和自动崩溃恢复的特性。5.5之后的默认设置
MyISAM引擎,不支持事务和行级锁,崩溃后无法安全恢复。

mysql的优化主要分为

  • 数据备份优化
  • 用户权限优化
  • 部署环境优化
  • 运行性能优化
  • 监控管理优化
  • 数据库设计优化
  • 数据类型优化

尽量使用对应的数据类型。比如,不要用字符串类型保存时间,用整型保存IP
选择更小的数据类型。能用TinyInt不用Int。
标识列(identifier column),建议使用整型,不推荐字符串类型,占用更多空间,而且计算速度比整型慢。
不推荐ORM系统自动生成的Schema,通常具有不注重数据类型,使用很大的VarChar类型,索引利用不合理等问题。
创建完全的独立的汇总表\缓存表,定时生成数据,用于用户耗时时间长的操作。对于精确度要求高的汇总操作,可以采用 历史结果+最新记录的结果 来达到快速查询的目的。

查询优化
响应时间 (服务时间,排队时间)
扫描的行
返回的行

避免查询无关的列,如使用Select * 返回所有的列。
避免查询无关的行
切分查询。将一个对服务器压力较大的任务,分解到一个较长的时间中,并分多次执行。如要删除一万条数据,可以分10次执行,每次执行完成后暂停一段时间,再继续执行。过程中可以释放服务器资源给其他任务。
分解关联查询。将多表关联查询的一次查询,分解成对单表的多次查询。可以减少锁竞争,查询本身的查询效率也比较高。因为MySql的连接和断开都是轻量级的操作,不会由于查询拆分为多次,造成效率问题。
注意count的操作只能统计不为null的列,所以统计总的行数使用count(*)
关联查询延迟关联,可以根据查询条件先缩小各自要查询的范围,再关联
Union查询默认去重,如果不是业务必须,建议使用效率更高的Union All
Limit分页优化。可以根据索引覆盖扫描,再根据索引列关联自身查询其他列。

SELECT
 id,
 NAME,
 age
WHERE
 student s1
INNER JOIN (
 SELECT
   id
 FROM
   student
 ORDER BY
   age
 LIMIT 50,5
) AS s2 ON s1.id = s2.id