MySQL数据库知识整理归纳

数据库创建需要考虑的内容

1.配置文件的创建

如下一个基础的配置文件:

2.存储引擎的选择

存储引擎是mysql数据库的核心,现在常用的两个是InnoDB和MyISAM
InnoDB存储引擎支持事务,行锁设计,支持外键,InnoDB可以通过MVCC来获得高并发性,对于表存储在表空间中(即.ibd文件,有索引和表数据)。
MyISAM存储引擎不支持事务,表锁和全文索引(对一个文件,首先定义一个词库,然后列出每个词中出现的频率与位置),表又MYI(索引文件),MYD(数据文件)
可以根据需要选择合适的存储引擎。

3.数据库表的设计

(1)注意遵从范式,不能太高也不能太低,太高会产生诸多数据的冗余,太低会使得管理起来很困难,耦合度太大
如下是1NF,2NF,3NF,BCNF区别
1NF:每个属性是不可分割的,每一列都是不可分割的数据项
2NF:消除非主属性对候选键的部分依赖,例如(学号,课程名称)->(姓名,年龄,成绩,学分)就不属于2NF
3NF:消除非主属性对候选键的传递依赖,例如(学号)->(姓名,年龄,所在学院,学院地点,学院电话)不属于3NF
BCNF:消除每个属性对候选键的传递依赖(仓库ID,存储物品ID,管理员ID)->(数量)   存在(仓库)->(管理员ID)   (管理员ID)->(仓库ID)
(2)数据库中还有一些功能辅助
约束:比如主键,外键,唯一等
触发器:在insert,delete,update之前或之后自动调用SQL命令或者存储过程
视图:是一个虚表,可以当做表用。
存储过程:一个经过编译好的SQL语句集合,直接调用就好。

4.创建高性能索引

(1)只有独立的列才能使用索引,例如select actor_id from sakila.actor where actor_id +1 =5,此时mysql无法解析这个方程式,无法使用索引
(2)索引的选择性(不重复索引值与数据表的记录总数的比值,比如1000条记录,不同的有900条,还有100条是空的或者重复的)越高则查询效率越高
(3)在使用前缀索引时,如果前缀的选择性能够接近0.031(即取前缀后存在不同个记录条数/总的记录条数),基本上就可用了(前缀索引的创建alter table * add key(id(7)))
(4)有时候在多个列上建立独立的单列索引并不能提高MySQL的查询性能,多列索引就是使用表上的多个单列索引来定位指定的行。创建多列索引时要注意索引的顺序:一般将选择性最高的列放到最前列,这样开始就能够最快地过滤出需要的行。(多列索引是如何查找的?)
对于使用多个单列索引的方法主要使用union
(5)聚簇索引:在InnoDB中实际上就是在同一个结构中保存了B-Tree索引和数据行,叶子页存放数据行,节点页只包含了索引列。一个表中只能有一个聚簇索引
(什么时候只使用聚簇索引,什么时候只使用二级索引)
(6)覆盖索引:与聚簇索引有点不同的是,它的叶子中包含的是所需要的字段的值(不只是主键),而不是全行数据,覆盖索引只需要对访问,而不仅仅在where中
(7)在where中使用like时,如果查询是以通配符开头的like查询,存储引擎就无法使用索引,索引只能做最左前缀匹配的like比较


除此之外还可以对SQL语句做一些优化。

MySQL数据库中必备的知识

1.MVCC多版本并发控制

主要是针对想InnoDB这些事务性存储引擎而是用的,它不是设置简单的行级锁,还实现了多版本并发控制
方法:通过在每行的记录后面保存两个隐藏列:行的创建时间,行的过期时间(存储的系统版本号),当开始一个事务,系统版本号递增然后与这两个隐藏行比较。
例如:
select:如果事务的版本号大于或等于行的创建时间,而且小于行的过期时间(包括过期时间为定义),则可以返回结果
insert:为新插入的每一行保存当前事务的版本号
delete:将行的过期时间设置成事务的版本号
update:将新插入的行保存当前事务的版本号,以前的行更新他的过期时间为当前事务的版本号

2.锁与锁的问题

锁的使用是为了实现并发控制,在服务器层存在表锁,存储引擎层存在行级锁。锁分为共享锁和排他锁,共享锁是给查询操作使用的,排他锁是更新,修改时使用的。
服务器为alter table之类的语句默认使用表锁,忽略具体存储引擎使用的锁机制。
行级锁在存储引擎层上,一般可以不设置。
锁的粒度越大并发程度越低
对于MyISAM引擎使用表锁,不能使用行级锁。
对于InnoDB则可以使用行级锁,同时为了支持在不同粒度上进行加锁操作,InnoDB存储引擎支持意向锁,它是表级锁,如果要给一个节点加锁,首先要给这个节点的上层加意向锁。

对于锁产生的问题
通过锁来实现事务的隔离性,使得事务并发工作,但是会产生三个问题:丢失更新,脏读,不可重复读
丢失更新:指两个用户同时更新同一数据,则其中一个更新就会丢失
脏读:指两个事务运行过程中,一个事务能够读到另一个事务未提交的数据,可以通过将隔离级别设置成提交读来解决。
不可重复读:一个事务两次读取数据库,但是在期间执行的事务修改了它的数据,导致两次读的数据不一致。可以通过设置重复读隔离级别来解决。

脏读和不可重复读的区别是:脏读读的是未提交的数据,而不可重复读读的是提交了的数据。

3.MySQL一些文件

.pid文件:当MySQL实例启动时,会将自己的进程ID写入pid文件中,该文件由pid_file参数控制
.frm是存储数据表的框架结构(innodb也有),表结构文件,还存放视图的定义,如果创建一个v_a视图,则对应产生一个v_a.frm文件
MyISAM存储引擎中:
.MYD是MyISAM表的数据文件的扩展名
.MYI是MyISAM表的索引的扩展名


InnoDB存储引擎文件:
表空间文件:默认下为ibdata1的文件,通过设置innodb_data_file_path,会将所有基于InnoDB存储引擎的表的数据都记录到该文件内,如果设置innodb_file_per_table则将每个基于InnoDB存储引擎的表单独产生一个表空间即.ibd(只存放表的数据,索引,和插入缓冲,其他的还是在默认文件中)
重做日志文件:ib_logfile0和ib_logfile1,记录了对于InnoDB存储引擎的事务日志,可以利用这个文件来恢复数

mysql的日志文件分为四种:
错误日志:对mysql的启动、运行、关闭过程进行记录(参数为log_error)
慢查询日志:能为SQL语句的优化带来很好的帮助,设置一个阀值(long_query_time),将运行时间超过阀值的记录到该日志文件中,慢查询日志还可以记录没有使用索引的语句(通过设置log_queries_not_using_indexes)
查询日志:存放所有多MySQL数据库请求的信息,无论正确与否,默认文件名为:主机名.log
二进制日志:记录对数据库执行更改的所有操作,不包括select和show这样的操作,他具有恢复和复制功能,通过log-bin来启动二进制日志,默认下不会启动

二进制日志需要用mysqlbinlog工具来查找日志文件,其他日志文件使用cat,head,tail来查看
(注意,此处的二进制日志记录所有与Mysql有关的日志记录,但是InnoDB存储引擎的重做日志只记录有关其本身的日志,二进制日志文件是在事务提交前记录,,而在事务进行中,不断有重做日志条目被写入重做日志文件)

4.备份与恢复

“热”备份不需要任何的服务停机时间
还原:意味着从备份文件中获取数据
恢复:意味着当某些异常发生后对一个系统或其部分的拯救。
存储引擎的崩溃恢复要求数据和日志文件一致
尽量在关闭mysql后做备份是最简单最安全的

在众多的备份方法中,一个最大的问题就是会使用flush tables with read lock操作会导致mysql关闭并锁住所有的表,将MyISAM的数据文件刷新到磁盘上

备份方式有逻辑备份和物理备份
逻辑备份:使用命令导出
优点:灵活恢复非常简单。
缺点:逻辑备份在某些场景下比数据库文件本身更大,有出现浮点表示文件、软件Bug等都会导致问题,逻辑备份需要使用更多的CPU周期来执行这些语句。

物理备份:直接复制原始文件的物理备份
优点:只要将需要的文件复制到其他地方即可,易跨平台、操作系统和MySQL版本。
缺点:InnoDB的原始文件通常比相应的逻辑备份要大得多,InnoDB的表空间包含很多未使用的空间。

备份的内容包括:非显著数据,代码(例如mysql服务器存储的一些触发器和存储过程的代码,针对单个数据库备份不会包括触发器和存储过程的备份),复制配置(例如二进制日志,重中继日志,入职索引文件盒.info文件),服务器配置,选定的操作系统文件

增量备份和差异备份
差异备份:是对自上次全备份后所有的改变的部分而做的备份。
增量备份:是自从任意类型的上次备份后所有修改做的备份。

5.InnoDB的索引与算法

InnoDB存储引擎有两种索引:B+树索引和哈希索引
B+树索引:分为聚集索引和辅助聚集索引。聚集索引就是按照每张表的主键构造一颗B+树,叶节点存放行的所有记录;辅助索引中叶节点除了包含建立辅助索引的那些键值外,还包括书签(即聚集索引键,便于查询这些键以外的其他信息)

注意:对于外键InnoDB会自动对其加一个索引

哈希索引:InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引,不能人为干预是否在一张表中生成哈希索引。但是哈希索引只能用来搜索等值的查询,对于范围查找是不能使用的。

6.事务

事务必须满足四个特性ACID:原子性,一致性,隔离性,持久性
事务的四个隔离级别:未提交读,提交读,重复读,可串行化
阅读更多
个人分类: mysql
想对作者说点什么? 我来说一句

数据库Mysql基础知识总结

2013年08月02日 9KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭