mysql索引，引擎选择

mysql索引

• Primary key 主键
  • 数据唯一标识
• Foreign key 外键
  • 关联标识，交给mysql维护表关系，保持数据完整性
    • CASCADE（在父表上update/delete记录时，同步update/delete掉子表的匹配记录）
      • ON DELETE：删除主表时自动删除从表。删除从表，主表不变
      • ON UPDATE：更新主表时自动更新从表。更新从表，主表不变
    • SET NULL（在父表上update/delete记录时，将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)）
      • ON DELETE：删除主表时自动更新从表值为NULL。删除从表，主表不变
      • ON UPDATE：更新主表时自动更新从表值为NULL。更新从表，主表不变
    • NO ACTION（如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作）
      • ON DELETE：从表记录不存在时，主表才可以删除。删除从表，主表不变
      • ON UPDATE：从表记录不存在时，主表才可以更新。更新从表，主表不变
    • RESTRICT（同no action, 都是立即检查外键约束）
    • SET DEFAULT（父表有变更时,子表将外键列设置成一个默认的值 但Innodb目前不支持）
  • 一般数据量大不设外键，自己维护表关系，减少mysql额外开销，提高效率。
• key/index 索引
  • 提高数据查询效率，应用在经常where，group by字段上，以树的形式在数据库中维护，所以减慢增删改速度
• Unique 唯一索引
  • 保证数据不重复

常用数据库引擎

1. InnoDB存储引擎
   – InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持外键完整性约束，InnoDB是默认的MySQL引擎。
   – InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件
2. MyISAM存储引擎
   – MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。
   – 支持大文件存储，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快，使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

数据库引擎选择（创建数据库/表时可指定）

• 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
• 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
• 如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive