1、mysql的InnoDB引擎和MyISAM引擎区别:
MyISAM不支持事务,但是查询效率高内存和空间使用比较低。InnoDB支持事务
MyISAM不支持外键,InnoDB支持
MyISAM是表锁、InnoDB是行锁
MyISAM支持全文索引,索引和数据分开的是两个文件,节点存储的是那一行记录的指针、InnoDB索引和数据文件在一起,节点存储的是那一行记录的数据
2、事务的特点
A(Auto)C(Consistency)I(Isolation)D(Durability):原子性、一致性、隔离性、持久性
如果不考虑事务的隔离性会产生的问题
1、脏读
有A、B两个事务,A事务修改了数据,但是还未提交,这个时候B读取到未提交的数据。如果此时A回滚,则第二个事务B读取到的数据就未脏数据
2、不可重复读
一个事务读取数据库中的数据,一个事务范围内多次查询但是返回了不同的数据值,就是不可重复读,原因是查询间隔,被另一个事务修改并提交了
3、虚读(幻读)
第一个事务对一定范围内的数据进行了批量修改,第二个事务B在这个范围内添加了一条数据,这时候第一个事务就会丢失对新增数据的修改
Mysql的四种事务隔离级别
1、Serializable(串行化) 可避免以上三种问题
2、Repeatable(可重复读) 可避免脏读,不可重复读
3、Read Commited(读已提交) 可避免脏读
4、Read UnCommited(读未提交) 任何情况不能保证
3、内连接,左连接,右连接,全连接,交叉连接(笛卡儿积)
左连接:返回包括左表中的所有记录和右表中连接字段相等的记录
右连接:返回包括右表中的所有记录和坐标中连接字段相等的记录
内链接:只返回两个表中连接字段相等的行
外连接:返回左右表中所有的记录和左右表中相连的字段相等的记录
交叉连接:返回左表中所有行与右表中所有行的组合,也称笛卡儿积
4、索引
不用红黑树:防止高度过高
不用Hash:定位快速,但是对范围查找没办法
B+Tree和BTree的区别:B+树在非叶子结点不保存数据,只在叶子结点保存。而B树在叶子结点和非叶子结点都会保存。这种结构导致你如果用B树来进行查询,会增加磁盘IO的次数,导致性能不如B+树
5、乐观锁、悲观锁
乐观锁:就是认为每次拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下别人在此期间有没有更新这个锁,运用版本号机制
悲观锁:每次拿数据都会上锁
6、SQL优化
原因:性能低,执行时间太长,SQL语句欠佳、索引失效,服务器参数设置不合理
SQL的编写过程:select 。。from 。。 join。。。on 。。。 where 。。。 group by 。。。 having。。。having。。。order by。。。limit
SQL解析过程:from -> join -> on -> where -> group by -> having -> select -> distinct -> order by -> limit
SQL优化,主要优化索引
索引的弊端:索引本身很大,1、少量数据不适合索引 2、频繁更新字段不适合索引 3、很少使用的列 4、索引会降低增删改得到效率(增删改会该表索引的结构)
优化方法:
1、尽量避免全表扫描,首先考虑再where和order by涉及的列上建立索引
2、尽量避免在where子句中对字段进行null值的判断,否则将放弃索引而采用全表扫描
3、尽量避免在where中使用!=或<>操作符,否则将放弃使用索引而进行全表扫描
4、尽量避免在where中使用or来尽享条件连接,如果一个字段没索引,一个有索引,将导致引擎放弃使用索引而进行全表扫描
5、避免使用in或者not in
6、尽量避免在where中使用like,也会导致全秒扫描'%xxx%'
7、尽量避免在where中使用函数操作
8、count(*)也会引起全表扫描
9、索引不是越多越好,因为会引起insert和update的效率下降
10、避免select *
11、如果子查询数据集大,使用exist,如果主查询数据集大,则使用in
12、如果只有一条数据就加上limit 1
13、选择合适的类型,能用tinyint就不用smallint,能用smallint就不用int
7、varchar和char的区别
char:当数据的实际长度比设定长度短的时候,他将按照设定长度储存,不足部分,填补空格,适用于身份证等长度固定的字段
varchar:当数据的实际长度比设定长度短,那么他将按照实际长度存储,而不占用剩余的空间
8、drop,delete,truncate的区别
drop直接删除表;
truncate删除表中的数据,再插入时id又从1开始;
delete删除表中数据
9、MVCC(Multi-Version Concurrency Control)多版本并发控制
最大的好处:读不加锁,写加锁
1、多版本并发控制是用来解决读写冲突的无锁并发控制,也就是为事务分配单向增长的时间戳,为每个修改保存一个版本,版本与事务时间戳关联,读操作只读该事务开始前的数据库快照。这样在操作不用阻塞写操作,写操作不用阻塞读操作的同时,避免了脏读和不可重复读的问题
2、当MVCC数据库更新一条数据的时候,不会直接重写原始的数据,而是修改新创建的数据副本。因此会有多个版本的数据被保存下来。每个事务看到的数据版本依赖于隔离级别的实现。MVCC中最通用的隔离级别就是快照隔离。在快照隔离的情况下,事务只会获取到数据在事务开始前的状态
10、主从复制
原理:
1、主数据库在执行SQL之后会记录二进制log日志文件(bin-log),然后发送给从数据库
2、从数据库从主数据库获取bin-log,存于本地的relay-log中,然后从上次记录的位置起执行SQL语句,一旦遇到错误则停止同步
主服务器流程分析:
首先bin-log日志文件加锁,然后读取更新的操作,读取完毕以后将锁释放掉,最后将读取的记录发送给服务器
服务器流程:
一次主从复制会用到三个线程:Binlog dump线程,Slave I/O线程和Slave SQL线程,其中Binlog dump线程在主服务器上面,剩下的两个线程是在从服务器上面工作的
如何提升Mysql主从复制的效率
1、master:
两个参数可以控制
Binlog_Do_DB:设定哪些数据库需要记录Binlog
Binlog_Ignore_DB:设定哪些数据库不要记录Binlog
2、slave:
6个参数可以控制
Replicate_Do_DB:设定需要复制的数据库,多个用都好分隔
Replicate_Ignore_DB:设定可以忽略的数据库
Replicate_Do_Table:设定需要复制的表
Replicate_Ignore_Table:设定可以忽略的表
Replicate_Wild_Do_Table:功能同Replicate_Do_Table,可以带通配符设置
Replicate_Wild_Ignore_Table:功能同Replicate_Wild_Ignore_Table,可带通配符设置
11、为什么MyISAM比InnoDB快
InnoDB要缓存数据块,而MyISAM只缓存索引块
InnoDB寻址要映射到块,再到行,MyISAM记录的直接是文件的OFFSET,定位比InnoDB要快
InnoDB还需要维护MVCC一致
12、XSS、SQL和CSRF攻击
XSS:通过对网页注入可执行代码且成功地被浏览器执行,达到攻击的目的,预防:做数据校验
SQL注入:预防:输入校验,SQL不要动态拼接,用参数化SQL
CSRF:利用合法身份进行非合法操作。预防:验证Http Refer,使用请求令牌token
13、三大范式
第一范式是要求属性具有原子性,列不可分
第二范式是表要有主键,而其他的属性都要依赖主键
第三范式是所有的依赖都要是直接依赖