一、Mysql数据库

转载yongfit

 

1、数据库类型有哪些? 

非关系型数据库:存储格式灵活,可以是key-value的形式,也可以是文本文档图片等形式。读写速度快,可以使用磁盘或随机存储器作为载体,具有高扩展性,但是不支持sql语句,无事务处理。

关系型数据库:支持事务、使用表结构,可用复杂查询,但是读写性能差,固定表结构,数据存储不够灵活,对于高并发的读写请求,磁盘io是很大的瓶颈。

2、主从复制原理

主库开启binlog日志,授权用户密码

从库会开启两个线程io线程和sql线程,主库开启dump线程。当主库binlog日志内容更新后,dump线程会告诉io线程,io线程读取binlog日志,并将内容记录到从库的中继日志,sql线程读取中继日志,并执行,达到数据一致的目的。

3、主从方式有几种

两种做法:

binlog日志方式:从节点需要手动指定binlog日志的名称、位置

gtid方式:从节点不需要手动指定binlog日志的名称、位置;会自动锁定

4、mysql的数据备份有几种

逻辑备份:mysqldump,备份sql语句,速度慢,恢复也慢,需要一条一条执行sql语句

物理备份:xtrabackup备份,备份速度慢,文件大,恢复速度快。

物理备份又分为3种备份方式:

完全备份:备份当前的所有数据

增量备份:备份上一次备份到当前时间点的更新的所有数据(上一次备份可以是全备也可以是增量备份),数据占用磁盘空间小,但恢复过程较复杂

差异备份:备份上一次完全备份到当前时间点的所有数据,占用磁盘空间比较大,恢复过程简单

备份策略:周日做全备,周一到周六每天增量备份,结合计划任务定期处理7天前的备份数据(迁移到其他安全的地方)

5、事务的四个特性

原子性:指事务是不可拆分的最小单元

隔离性:数据库允许多个事务同时对数据库的数据进行读写和修改的能力,隔离性为了防止多个事务并发执行由于交叉执行导致的数据不一致。隔离级别分为读未提交、读提交、可重复读、串行化。隔离等级越高,数据越安全,但是消耗的资源就越多。

一致性:事务中的sql语句,要么全部成功,要么全部失败。

持久性:事务结束后,对数据的修改是永久写入到磁盘的,即使系统故障也不会丢失

6、脏读、不可重复读、幻读是什么

脏读:a开启了一个事务并更新了数据,但是未提交,b读取了a更新的数据,但是a此时进行了事务回滚,b读到的就是脏数据

不可重复读:事务a多次读取同一数据,事务b在a多次读取过程中对数据做了更新并提交,导致a在多次读取数据时结果不一致。

幻读:管理员a将学生成绩从具体分数改为abcde等级,但是管理员b在这个时候插入了一条具体分数的记录,当管理员a修改结束后,发现还有一条数据没改过来,就像发生了幻觉一样。

不可重复读和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除,解决不可重复读只需锁住满足条件的行,解决幻读需要进行锁表

7、数据库引擎

innodb:支持事务,支持行锁定

myisam:有较高的查询速度,但是不支持事务

memory:需要很快的读写速度但是对安全性要求不高的话可以使用,但是对表的大小有要求。支持锁表不支持行锁定。

8、mysql的数据类型

整数型、浮点型、文本字符串类型、日期时间类型、枚举型、集合型

9、mysql的约束添加

主键:用于唯一的标识表行的数据,当定义主键约束后,此列不能重复

不能为空:定义not null后,此列的值不能为空

default:默认约束,即使插入数据没有值,都会有默认值

unique:唯一约束,该列不允许重复

自增键

10、mysql主从数据不一致的原因

网络延迟,主从复制是基于binlog的异步复制,通过网络传送binlog文件,网络延迟是大多数引起数据不一致的原因

主从机器的负载不一致:mysql主从复制是主库开启dump线程,从库开启io和sql线程。当任何一台服务器负载过高,导致其中一个线程资源不足,会出现主从不一致的情况

版本不一致

max_allowed_packet设置不一致:主库设置的max_allowed_packet设置的比从库大,一条大的sql语句在主库能执行完毕,;;但在从库上无法执行,导致主从不一致。

解决方法:1、stop slave set global sql_slave_skip_counter=1 跳过这一步错误;2、重新做主从:先锁表,导入数据数据+同步,重新做主从

11、怎么彻底删除数据库一个表

drop table table_name from 库名;

这样删除,表和表中的数据全部丢失。

12、读写分离的好处

分摊服务器的压力,提高系统的处理效率。主要分担主服务器的读取压力,间接提高了主库的写入性能。因为读请求全部发给了从库。

13、mysql的优化

安全方面:修改默认端口号,禁止root用户远程登录,对用户降权,以普通用户运行mysql

性能方面:升级硬件,内存、磁盘、优化sql语句(开启慢查询)、设置索引

参数优化:innodb的buffer参数调大,连接数调大、缓存的参数优化

架构方面:读写分离,一主多从,高可用

 

14、Mysql和Redis的区别?

Mysql是关系型数据库,支持事务,支持标准sql语句,支持行锁定,使用表结构,支持复杂查询,数据存储到磁盘,比较安全,但固定表结构,数据存储方式不够灵活,读写性能差,在高并发的情况下,磁盘i/o是很大的瓶颈

Redis是非关系型数据库,nosql产品--redis,mongodb,memcached。不支持事务,存储格式灵活,可以是key-value形式,也可以是图片,文档,等形式,读写速度快,可以使用磁盘或者随机存储器作为载体,具有高扩展性,但是不支持sql语句。

 

15.你是怎么优化sql语句的?

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null 可以在num上设置默认值0  select id from t where num=0

3. 任何地方都不要使用 select from t ,用具体的字段列表代替“”,不要返回用不到的任何字段。

4.避免频繁创建和删除临时表,以减少系统表资源的消耗。

16.MySQL的同步方式,分别是:

「异步复制」:MySQL 默认的复制即是异步的,主库在执行完客户端提交的事务后会立即将结果返给客户端,并不关心从库是否已经接收并处理。这样就会有一个问题,一旦主库宕机,此时主库上已经提交的事务可能因为网络原因并没有传到从库上,如果此时执行故障转移,强行将从提升为主,可能导致新主上的数据不完整。

「全同步复制」:指当主库执行完一个事务,并且所有的从库都执行了该事务,主库才提交事务并返回结果给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能必然会收到严重的影响。

「半同步复制」:是介于全同步复制与全异步复制之间的一种,主库只需要等待至少一个从库接收到并写到 Relay Log 文件即可,主库不需要等待所有从库给主库返回 ACK。主库收到这个 ACK 以后,才能给客户端返回 “事务完成” 的确认。

 

17. Mysql导致cpu飙升如何处理?

(1)多实例的服务器,先top查看是那一个进程,哪个端口占用CPU多;

(2)show processes list;查看是否由于大量并发,锁引起的负载问题;

(3)否则,查看慢查询,找出执行时间长的sql;explain分析sql是否走索引,sql优化;

(4)再查看是否缓存失效引起,需要查看buffer命中率;

(5)查语句是否有大量的update,查qps