MySQL 之存储引擎

MySQL存储引擎 （基于表）：对文件的存取机制

MyISAM

B+树索引（多路树，多路查找）数据 索引分离
  1、支持全文索引，不支持外键，不支持事务，表锁
  2、非聚集索引
  3、 .frm .myi  .myd
（ MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址）
 （MyISAM的索引文件仅仅保存数据记录的地址）
 （在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，
        只是主索引要求key是唯一的，而辅助索引的key可以重复）

InnoDB

B+树索引  把索引当成数据的一份存储
  1、不支持全文索引，支持外键，支持事务，行锁
  2、聚集索引（叶子节点包含了完整的数据记录）
     （因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），
       如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，
       如果不存在这种列，则mysql自动为InnoDB表生成一个隐含字段作为主键，
       这个字段长度为6个字节，类型为长整形。）
  （聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：
      首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录）
     

InnoDB索引和MyISAM索引的区别：
一是主索引的区别：InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
                  而MyISAM的辅助索引和主索引没有多大区别。

MEMORY

内存，存储临时表，varchar当char, 不支持 TEXT 和 BLOB；

ARCHIVE

归档  10:1  日志数据  insert 和 select；

索引

MyISM
 1.数据和索引分离开，非聚集索引
             叶子节点上  数据的地址
  2.辅助索引
         select   1
InnoDB
    1.索引当成数据的一部分存储，聚集索引（叶子节点包含了完整的数据记录）
         叶子节点上  数据
     2.辅助索引
         叶子节点   主索引的索引值
         select   1    辅助索引 +1  主索引

索引分类（根据字段的属性）
    普通索引
    唯一索引
    主键索引
    组合索引      最左前缀原则
     1.主键      主键索引 ==> 主索引
     2.唯一键  唯一索引 ==> 主索引
     3.添加一个隐藏字段  6个字节 autoincreament 主索引

索引的优化
  SQL和索引的优化
1、慢查询日志
  MySQL可以设置慢查询日志，当SQL执行时间超过设定阈值，SQL就会被记录到慢查询当中，通过查看日志，
  用explain分析SQL执行计划来判定效率低下的原因。 
  SQL执行超过阈值时间参数可以设置：long_query_time（单位：秒）
  查看SQL：show variables like ‘long%’;
  慢查询日志存放在MySQL路径下，默认名称是：host_name_show.log
2、查看磁盘I/O读写的数据量
3、show status
    MySQL提供show status 命令，查看MySQL运行参数，
    可以查看select、insert、delete、update语句执行频率，慢查询次数，事务的提交和回滚的次数。
    
 不建立索引： 不经常查询，数据量过大或过少，查询的频率远远大于修改;
               （索引值不能包含空）

事务

事务：一组特定SQL的集合；
    （逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败） 
事务作用：保证在一个事务中多次SQL操作要么全都成功,要么全都失败.
 特性：ACID
 A   原子性：指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 
 B   一致性：事务前后数据的完整性必须保持一致
 I   隔离性：多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，
             多个并发事务之间数据要相互隔离。
 D   持久性：指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，
             接下来即使数据库发生故障也不应该对其有任何影响。

隔离性：
1、没有隔离性产生的影响：
      1.脏读：事务读取了其他事务执行过程的结果；
       2.不可重复读（update）：事务读取了其他事务执行前后的结果；
       3.幻读（insert delete）：事务读取了其他事务执行前后的结果；
 2、隔离级别
       1.未提交读 ：脏读、不可重复度、幻读；
       2.已提交读：不可重复读、幻读；
       3.可重复读：幻读，InnoDB;
       4.可串行化  

日志先行

  redo log  重做日志  事务将要执行的操作；
  undo log 未做日志  事务执行过程中每个状态点；

锁机制

1.MyISAM   表锁
    insert delete updata select 先加锁
    粒度大
    1.读锁    共享读锁   不兼容写锁   兼容读锁
    2.写锁    独占写锁   不兼容读写锁
2.InnoDB   行锁
    insert delete updata  先加锁
     select  不加锁
     粒度小
      1. 读锁   共享锁   读锁共享
      2. 写锁   排它锁   排斥除本身以外的其他锁      

 悲观锁：版本号
 乐观锁：字段标识
 意向锁：解决死锁
 间隙锁：事务中 隔离性  幻读
 触发器：当一个事件到来，触发器触发，去执行其他的操作；
        一张表中同类型的触发器只能出现一个；

数据库中触发器有几种，有哪些事件可以触发

触发器：当一个事件到来，触发器触发，去执行其他的操作；
trigger_name标识触发器名称，用户自行指定；
trigger_time标识触发时机，用before和after替换；
trigger_event标识触发事件，用insert，update和delete替换；
tbl_name标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt是触发器程序体；触发器程序可以使用begin和end作为开始和结束，中间包含多条语句；
触发器的种类
    6种  before insert, after insert,  before update, after update,
         before delete, after delete   insert,update,delete,load, replace;

SQL Server 2000 支持两种类型的触发器：AFTER 触发器和INSTEAD OF 触发器。
    AFTER 触发器即为SQL Server 2000 版本以前所介绍的触发器。
             该类型触发器要求只有执行某一操作（INSERT UPDATE DELETE） 之后，触发器才被触发，
             且只能在表上定义。可以为针对表的同一操作定义多个触发器。对于AFTER 触发器，
             可以定义哪一个触发器被最先触发，哪一个被最后触发，
             通常使用系统过程sp_settriggerorder 来完成此任务。 

INSTEAD OF 触发器表示并不执行其所定义的操作（INSERT、 UPDATE、 DELETE），而仅是执行触发器本身。
             既可在表上定义INSTEAD OF 触发器，也可以在视图上定义INSTEAD OF 触发器，
             但对同一操作只能定义一个INSTEAD OF 触发器。
             这些语句主要是以CREATE、ALTER、DROP等关键字开头的语句。
             DDL触发器的主要作用是执行管理操作，例如审核系统、控制数据库的操作等。 

1.字段类型 date time datetime

1） DATETIME
    显示格式：YYYY-MM-DD HH:MM:SS
    时间范围:[ '1000-01-01 00:00:00'到'9999-12-31 23:59:59'] 
 2） DATE
    显示格式：YYYY-MM-DD
    时间范围：['1000-01-01'到'9999-12-31'] 
 3）TIMESTAMP
    显示格式：YYYY-MM-DD HH:MM:SS
    时间范围:[ '1970-01-01 00:00:00'到'2037-12-31 23:59:59']

2.函数 count sum year

count()四种写法：
  count(*) 包括所有列，相当于统计表的行数，不忽略列值为NULL的记录。
  count(1) 忽略所有列，1表示一个固定值，也可以用count(2)、count(3)代替，不忽略列值为NULL的记录。
  count(列名) 只包括指定列，返回指定列的记录数，会忽略列值为NULL的记录。
  count(distinct 列名) 只包括列名指定列，返回指定列的不同值的记录数，忽略列值为NULL的记录      
执行效率比较:
     若列为主键，count(列名)效率优于count(1)；
     若列不为主键，count(1)效率优于count(列名)；
     若表中存在主键，count(主键列名)效率最优；
     若表中只有一列，则count(*)效率最优；
     若表有多列，且不存在主键，则count(1)效率优于count(*)；
 特例:
     select count(’’) from table;-返回表的记录数
     select count(0) from table;-返回表的记录数
     select count(null) from table;-返回0

sum用法：
     1）sum()函数里面的参数是列名的时候，是计算列名的值的相加，而不是有值项的总数；
     2）sum(条件表达式)，如果记录满足条件表达式就加1，统计满足条件的行数；
  例如：
  1. select name,sum(score < 60) ,avg(score) 
       from result  g
       roup by name having sum(score<60) >=2;   
  2. select time, 
      sum(case when sf="胜" then 1 else 0 end) f2, 
      sum(case when sf="负" then 1 else 0 end) as f3, 
      where tablename
      group by time;

year用法：
      YEAR()函数接受date参数，并返回日期的年份。请参阅YEAR()函数的语法：
      YEAR(date);
      YEAR()函数返回一个指定日期的年份值，范围为1000到9999，如果日期为零，YEAR()函数返回0。
     例如：
      SELECT    
          YEAR(shippeddate) year,     
          COUNT(ordernumber) orderQty
       FROM    orders
       WHERE    shippeddate IS NOT NULL
       GROUP BY YEAR(shippeddate)
       ORDER BY YEAR(shippeddate);