关与Mysql

傷城~ 2022-03-15 18:20 195阅读 0赞

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

数据库的行锁基于索引

[https://zhuanlan.zhihu.com/p/52678870][https_zhuanlan.zhihu.com_p_52678870]

[https_zhuanlan.zhihu.com_p_52678870]: https://zhuanlan.zhihu.com/p/52678870