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

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

一: 索引

  • 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,非主键列是直接依赖于主键,还是直接依赖于非主键列。

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

上一篇:mysql修改密码


下一篇:SQL Server 索引碎片整理