mysql在开发中运用的比较多,所以很有必要了解一番。
mysql的存储引擎常用有innodb与myisam,他们区别有哪些呢:
1 在事务上:myisam不支持事务,innodb支持事务,这是很大区别。同时myisam不支持外键、是表级锁,因此对于高并发请求处理效率有点低。innodb支持行锁,只是在where是主键是有效的,非主键为表锁,行锁是基于索引的。
2 在存储结构上:myisam在磁盘上存储生成三个文件:第一个文件的名字以表名字开始,扩展名是文件类型: .frm .myi .myd
3 myisam支持全文索引。保存表数据的行数 select count(*) from table 会直接取出值;innodb不支持全文索引,可用使用sphinx插件,效果更佳。
注意:count(*) count(1) count(列名)区别:
在没有主键情况下count(1)比count(*)效率高,都是返回表的行数 count(列名)如果值为null则会忽略。
4 innodb主键定义了,那么这个主键就是聚集索引
这里记录下:聚集索引与非聚集索引的区别:
聚集索引就是数据行的物理地址与数据列值逻辑地址一致,在innodb聚集索引叶子节点保存的是数据文件,完整的数据记录,非聚集索引是主键的值;在myisam中聚集索引和非聚集索引叶子节点的data域存储的都是指向数据文件的指针,一个表中聚集索引只能有一个,非聚集索引可有多个
如有一张表table id name addr三个字段(id聚集索引 name主聚集索引),那么
select id from table / select id,name 则通过非聚集索引直接返回结果,不需要二次查询,
select name,addr from table 就需要二次查询获取原数据行的addr值,解决二次查询可以使用组合索引
mysql的事务处理:
在数据库事务处理是会出现哪些意外呢:
1 更新丢失:
两个事务同时获取相同数据,都要更新一个字段x,x=100
事务A:读取x=100,然后x=x+100
事务B:读取X=100,然后x=x+200,那么先提交的事务会被后提交事务覆盖。
2 脏读:
一个事务读取另一个事务未提交的数据。
事务A:查询 x=100(其实是200,只是事务B还未提交)
事务B: x=100, x=x+100
3 不可重复读:
一个事务同一个查询执行两次出现不同结果
事务A:第一次查询:x=100,第二次查询x=200(在同一事务两次相同查询结果不一样)
事务B:x=100,x=x+100
4 幻读:
一个事务第一次查询返回10条结果,可能第二次查询可能变成20条
不可重复读和幻读似乎是一样的,都是在查询的时候存在另一个事务也在操作,那么有什么区别呢:
不可重复读是对数据行的变换,这一行某个字段被修改了;幻读,是整张表增加了数据行,新增了一条数据。
所以对于这些问题,解决的办法分别加上行级锁、表锁
sql语法和优化:
复制表:
1 create table A like B;(不会赋值表数据)
2 create table a as (select * from B) (不会赋值主键和索引)
3 create table A like B;insert into A (select * from B)
删除表
delete from A (可以回滚,表空间、索引文件不变)
truncate table A(表空间、索引占用空间恢复初始大小)
drop table A(删除占用表空间)
sql优化:
1 建表的字段长度和符合实际存储数据长度,不宜过长浪费空间,比如邮编字段定义为char(6)
2 减少子查询用join代替
3 少用select * ,列出我们实际要查询出来的字段
4 在频繁where、order by的字段上建立索引(就是存储一列或多列的数据结构)
5 防止索引失效:隐式类型转换 、like'%name%'(最左匹配原则,这里name前面是%,模糊导致索引失效)、not in 、in、where后面加函数运算 、<>(导致全表扫描,索引失效)
注意: group by后面条件筛选用having 。where是聚合之前进行筛选,having实在分组之后筛选
mysql中时间字符串与时间戳转换:
字符串转时间戳:select UNIX_TIMESTAMP('2019-08-06 10:24:23') from dual
时间戳转字符串:select FROM_UNIXTIME(1565058263,'%Y-%m-%d %H:%i:%s') from dual
数据库的行锁基于索引