数据库与高可用第三章索引,事务,存储引擎

一: 索引

• 1.创建普通索引的三种方法

  第一种方法--直接创建
  create index 索引名 on 表名(字段)
  mysql> create index age_index on kgc(age);
  第二种方法--修改表方式创建
  alter table 表名 add index 索引名 (字段)
  mysql> alter table kgc add index name_index (name);
  第三种方法--创建表时创建
  create table 表名 ( ...,index index_name(字段))
  mysql> create table num (id int,index id_index(id));

• 2.创建唯一性索引

  第一种方法--直接创建
  create unique index index_name on table_name(字段);
  mysql> create unique index id_unique on kgc(id);
  第二种方法--修改表方式创建
  alter table table_name add unique index_name(字段)
  mysql> alter table kgc add unique index_age(age);
  第三种方法--创建表时创建
  create table table_name ( ...,unique index index_name(字段))
  mysql> create table accp (id int(4) not null auto_increment,name varchar(10) not null,age int(4) not null,unique index id_index(id)); 
  

• 3.主键索引 非空且唯一

  create table table_name ([...],primary key(字段));
  alter table table_name add primary key(列的列表)；

• 4.组合索引

  create table user ( ...,index user(name,age,sex));

• 5.全文索引

  方法一: 在已经存在的表上创建全文索引
  create fulltext index index_name on table_name(content,tag);
  方法二: 通过SQL语句ALTER TABLE创建全文索引
  alter table table_name add fulltext index index_name(content,tag);

• 6.查看索引的方法

  show index from table_name;
  show keys from table_name;

• 7.删除索引的方法

  drop index_ name ON table_ name;
  alter table table_ name drop index index_name;
  mysql> drop index index_puid on mapping;
  mysql> alter table mapping drop index gameid;

二: 事务

• 数据库的三大范式

• 原子性
  唯一性
  一致性

• 1.事务的控制方法
  手动对事务进行控制的方法
  事务处理命令控制事务

  bebin: 开始一个事务
  commit: 提交一个事务
  rollback: 回滚一个事务
  使用set命令进行控制
  set autocommit=0: 禁止自动提交. (等同于begin)
  set autocommit=1: 开启自动提交. (等同于commit)       

  ?

• 2.回滚

  mysql> savepoint a1;           //设置存档点a1
  mysql> savepoint a2;          //设置存档点a2
  mysql> rollback to a2;        //回滚到存档点a2  ,不提交事务
  mysql> rollback to a1;        //回滚到存档点a1  ,不提交事务
  a1-->a2-->a3;用回滚点回滚,不能跳级回滚若直接回滚到a1则内存中的a2和a3将会丢失
  mysql> rollback;      //提交事务,回到begin位置,事务结束

三：存储引擎

1.MyISAM存储引擎

MyISAM不支持事务，也不支持外键
访问速度快
对事务完整性没有要求

• 表级锁定形式，数据在更新时锁定整个表
• 数据库在读写过程中相互阻塞
• 会在数据写入的过程阻塞用户数据的读取
• 也会在数据读取的过程中阻塞用户的数据写入
• 优点：ISAM执行读取操作的速度很快
• 优点：不占用大量的内存和存储资源
• 缺点：不支持事务处理,也不支持外键
• 缺点：不能够容错

2.InnoDB存储引擎

• 支持事务：支持4个事务隔离级别

• 行级锁定，但是全表扫描仍然会是表级锁定

• 读写阻塞与事务隔离级别相关

• 具有非常高效的缓存特性：能缓存索引，也能缓存数据

• 表与主键以簇的方式存储

• 支持分区、表空间，类似 oracle数据库

• 支持外键约束，5.5以前不支持全文索引，5.5版本以后支持全文索引

• 对硬件资源要求还是比较高的场合

3.存储引擎的配置方法

• 查看表使用的存储引擎
  方法1：show table status from库名 where name=‘表名‘；
  方法2：show create table 表名； ‘//常用‘
• 修改存储引擎
  ◆ 方法一
  alter table 修改；
  格式：
  alter table table_name engine=引擎；
  ◆ 方法二
  vim my.cnf,指定默认存储引擎并重启服务
  格式：
  default-storage-engine=InnoDB
  ◆ 方法三
  create table 创建表时指定存储引擎
  格式：create table 表名 （字段） engine=引擎
  例如：MySQL> create table kgc（id int）engine=MyISAM;
  方法四：5.7版本已经被删除
  Mysql_convert_table_format转化存储引擎
  格式：
  Mysql_convert_table_format --user=root --password=密码 --sock=/tmp/mysql.sock --engine=引擎 库名 表名

4. 数据库三大范式

◆ 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。
◆ 第二范式（2NF）：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
◆ 第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
2NF，非主键列是否完全依赖于主键，还是依赖于主键的一部分；

3NF，非主键列是直接依赖于主键，还是直接依赖于非主键列。

原文：https://blog.51cto.com/14625831/2547965