mysql引擎、索引、事务、锁、sql注入及java操作
()数据库索引的数据结构 --》提高数据检索速度
常用: hash索引、B-树或B+树
不使用红黑树原因:高度h深,逻辑上很近的节点物理上可能很远,无法利用局部性。
B-树:
(1)所有叶节点具有相同的深度,等于h
(2)Key和指针相互间隔,两端为指针
在B-Tree中按key检索数据:从根节点进行二分查找,如果找到则返回对应节点的data,否则在相应区间递归进行查找。
B+Tree:(MySql)
只有叶节点存储data,还增加了指向相邻叶子节点的指针。
索引结构的例子:
()索引失效情况6
答:1.or语句前后没有同时使用索引(视引擎)
2.不满足联合索引的最左前缀
3.模糊查询,like查询以%开头
4.列类型是字符串,使用时没有用引号括起来
5.mysql估计使用全表扫描比索引快(如小表)
6.在索引列上做运算或函数
()索引应用场景
答:1.经常需要搜索的列上
2.排序字段可以建索引
3.分组字段可以建索引,分组前提是排序
聚簇索引:数据行的顺序与索引顺序一致。一张表只能有一个聚簇索引。(范
围查找)
非聚簇索引:数据行的顺序与索引顺序无关。
Mysql事务隔离级别:
(1)Read Uncommitted(读取未提交内容)
不添加共享锁。所以其它transaction B可以在transaction A对记录的读取过程中修改同一记录,可能会导致A读取的数据是一个被破坏的或者说不完整不正确的数据。
另外,在transaction A中可以读取到transaction B(未提交)中修改的数据。比如transaction B对R记录修改了,但未提交。此时,在transaction A中读取R记录,读出的是被B修改过的数据。
(2)Read Committed(读取提交内容)
在transaction A中读取数据时对记录添加共享锁,但读取结束立即释放。其它transaction B对这个记录的试图修改会一直等待直到A中的读取过程结束,而不需要整个transaction A的结束。所以,在transaction A的不同阶段对同一记录的读取结果可能是不同的。
(3)Repeatable Read(可重复读)--Innodb默认
对于读出的记录,添加共享锁直到transaction A结束。其它transaction B对这个记录的试图修改会一直等待直到transaction A结束。
(4)Serializable
隔离级别 脏读 不可重复读 幻读
未提交读 可能 可能 可能
已提交读 不可能 可能 可能
可重复读 不可能 不可能 可能
可串行化 不可能 不可能 不可能
脏读:事务A读取到事务B未提交的数据。
幻读:(新增或删除)事务A修改表中所有行,事务B向表中添加一行,当事务A操作时发现表中有未修改的数据行。
()Mysql数据库引擎
答:1.Innodb引擎 --支持行锁、表锁 采用两阶段*协议
支持事务和行级锁,没有保存表的行数,当SELECT COUNT(*) FROM TABLE时需要扫描全表。 对于Innodb,所有的操作都是事务。
按主键建立主索引,叶子节点数据域存储的是实际的数据,因此表文件本身就是主索引(聚簇索引)。
辅助索引叶子节点存储的是相应记录主键的值,辅助索引需要检索两次索引:首先检索辅助索引获得主键,用主键到主索引中获得记录。(辅助索引引用主键,过长主键会令辅助索引变大)
只有通过索引检索数据Innodb才使用行级锁,否则使用表级锁,行锁是针对索引加的锁,不是针对记录加的锁,具有相同索引键的记录都会被锁定。
行锁--》乐观锁,每行记录增加一个版本号
问题:Innodb处理死锁
答:回滚持有最少行级写锁的事务。
2.MyISAM引擎 --表级锁
不支持事务和行级锁,存储了表的行数,SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。支持压缩表
数据域存储的是数据的地址(非聚簇索引)。
MyISAM不会出现死锁(锁扩张阶段已经锁住所有表-from后的表)
表级锁:开销小,粒度大,不会出现死锁。可以自动和人为加表锁。
行级锁:开销大,粒度小,会出现死锁。
()数据库死锁
答:1.申请锁的顺序一致(即操作表的顺序一致)
()数据库内连接、左外连接、右外连接、全连接
内连接-->两表交集(相当于where语句)--只显示符合条件的列
左外连接--->左表的完全集,右表匹配则有值,不匹配为null
右外连接-->右表的完全集,左表匹配则有值,不匹配则为null
全连接-->左右连接的并集
笛卡尔积:select * from t1,t2(或t1cross join t2)
()having与where区别
答:都是用来筛选的,having筛选分组group by,其他用where.
Sql命令:
查询前k条记录:select * from tablename limit 0,k;(k读取记录个数)
查询后k条记录:select * from tablename order by id desc limit 0,k
()乐观锁、悲观锁
答:乐观锁假设不会发生并发冲突,操作数据时不加锁,提交操作时检查是否发生冲突。乐观锁实现:使用版本号(CAS)。为数据增加版本号,读取数据时,将版本号一同读出,更新数据时,版本号+1,当提交更新时,如果版本号大于数据库当前版本号,予以更新,否则冲突。
悲观锁假设会发生并发冲突,操作数据前加锁,提交操作后释放锁。悲观锁实现:依赖数据库提供的锁机制。
数据库保证数据的一致性:事务、乐观锁、悲观锁
乐观锁:适合写入不频繁的场景
悲观锁:适合写入频繁的场景
()数据库锁
答:排它锁(写锁)、共享锁(读锁)。
如果某个数据加了写锁,其他事务无法写和读。
如果数据加了读锁,其他事务可以读,但无法写。
()两阶段*协议
答:1.对任何数据进行读写之前,必须获得该数据的*(锁扩张)
2.在释放一个锁之后,事务不能再获得任何其他*(锁收缩)
()数据库三范式
答:1.属性不可分
2.非主属性必须完全依赖于码,如果码的子集能唯一确定一个非
主属性的话,就不符合第二范式
3.消除第二范式中的传递依赖
()mysql如何定位查询效率较低的sql语句?
答:慢查询日志。开启slow_query_log选项,当sql查询语句执行时间超过指定值就会被记录下来。
()MySQL临时表(表结构和数据都在内存中)
答:数据超过临时表设定的最大值,自动转化为磁盘表。
(a)外部临时表
通过create temporary table创建,只对当前用户可见,连接关闭时,该临时表自动销毁。
(b)内部临时表
MySQL自动创建存储某些操作的中间结果,用户不可见。
()游标
答:mysql服务器端提供的只读的、单向的游标,在存储过程中使用,游标指向的对象存储在临时表中。不支持客户端游标。
()索引为什么一部分放在内存,一部分放在硬盘上?
答:一般索引文件太大,不可能全部存储在内存,往往以索引文件的形式存储在硬盘上。
()sql执行计划
答:解释select语句。Explain select语句
Select_type:1.simple 简单查询
Select * from student;
2.primary/union 组合查询
Select ……union select……
3.primary/subquery 子查询
Where列表包含子查询
4.primary/derived 衍生
Form列表包含子查询
Type:all全表扫描
Index 使用索引
()解决Mysql连接失效:
答:默认当一个连接空闲时间超过8小时,Mysql自动断开该连接。
解决:1.增大Mysql连接超时时间
2.定期使用连接
()数据库连接过多原因
答:数据库的当前连接数超过设置的最大连接数。
()drop删除表的结构和数据,delete、truncate只删除表的数据不删除表结构。Drop、truncate操作立即生效,不能回滚,不触发触发器,delete事务提交之后才生效,会触发触发器。
事务?
答:
1)原子性(日志)
事务是一个不可分割的工作单元,事务中的操作要么都发生,要么都不发生。
2)一致性(日志)
事务必须是数据库从一个一致性状态变换到另一个一致性状态。如:A账户100,B账户100,A向B汇款后,保证A,B账户总额为200元。
3)隔离性(锁)
一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能相互干扰。
4)持久性(日志)
一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响。
打开事务:connection.setAutoCommit(false);
提交事务:connection.commit()
回滚事务:connection.rollback()
事务操作需要保证是同一个连接。
日志记录了事务对数据库所做的更新,如果某个事务执行的过程中发生错误,可以根据日志撤销对数据库所做的更新。
日志记录了所有对数据库的写操作,如果日志满了,只能执行读操作,不能执行写操作。
java连接mysql步骤:
1)加载驱动程序
Class.forName(“com.mysql.jdbc.Driver”)
类com.mysql.jdbc.Driver中包含有在DriverManager中注册驱动程序的静态代码块
2)连接到数据库
Connection =DriverManager.getConnection(url,user,password)
3)通过连接创建Statement对象
Statement =conn.createStatement();
4)通过Statement对象执行SQL语句
ResultSet =state.executeQuery(sql)
int = state.executeUpdate(sql)
Statement:
1)使用statement每次需要拼写sql语句,容易出错。
2)存在sql注入
PreparedStatement(statement的子接口):
1)提高性能(sql语句只编译一次)
数据库缓存执行的sql语句,当相同的sql语句再次传送到数据库时,不需要再次编译,使用statement时每次的sql语句都不同,无法匹配缓存。
2)防止sql注入
使用:
1.String sql = “insert into table values(?,?,?)”;
2.PreparedStatement ps=connection.prepareStatement(sql)
3.设置占位符?的值
4.ps.executeQuery()或ps.executeUpdate()
Sql注入及解决办法:
1.替换单引号,如把出现的单引号改成双引号
Select * from table where username=”’”+input1+”’ and password=’”+input2+”’”;
如果输入:input1: ’ or ‘1’=’1
input2: ‘ or ‘1’=’1 存在sql注入,替换后不存在
2.删除用户输入的连字符--
如果输入:input1:用户名’--,密码被注释掉,只要知道用户名即可。
3.将用户名、密码加密,和数据库中的比较,防止用户构造sql。
()java中java.util.Date与java.sql.Date的区别?
答:java.sql.Date是java.util.Date的子类,java.sql.Date针对sql语句使用,只包含日期没有时间部分,只能得到年月日,屏蔽掉时分秒。(重写父类获得时分秒的函数,使其抛出异常)。
转化:java.sql.Date d=new java.sql.Date(new java.util.Date().getTime()),
参数为毫秒数。