mysql索引和事务控制

介绍

• 加快查询速度

• 减低数据库的io成本

• 加快表与表的连接

• 减少分组的排列时间

• 原理依据：

  • 表的主键，外键必须有索引
  • 数量超过300行的表应该有索引
  • 经常与其他表连接
  • 唯一性太差的不适合当索引
  • 经常出现在where上的字段，应该建立索引

• 影响：在表中有大量数据的前提下，创建索引速度会很慢
  在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低

分类

  vim /etc/my.cnf
  default-cahracter-set=utf8
  systemctl restat mariadb
  mysql
  create database imployee_salary default charset utf8;
  use imployee_salary;
  create table IT_salary (岗位类别 char(20) not null, 姓名 char(20) not null, 年龄 int, 员工ID int not null, 学历 char(6), 年限 int, 薪资 int, primary key(员工ID));
  insert into IT_salary(岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values('Windows工程师','蓝凌',19,012,'中专',2,3500);
  insert into IT_salary(岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values('Linux工程师','姜纹',32,013,'本科',8,15000);
  insert into IT_salary(岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values('Java软件工程师','关园',38,014,'大专',10,16000);
  insert into IT_salary(岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values('硬件驱动工程师','罗中昆',29,015,'大专',9,16500);

• 普通索引：没有唯一性的限制
  格式：create index <索引名字> on 表名<字段>

  create index salary_index on IT_salary(薪资);
  MariaDB [imployee_salary]> show index from IT_salary\G;
  *************************** 2. row ***************************
          Table: IT_salary
   Non_unique: 1
     Key_name: salary_index
   Seq_in_index: 1
    Column_name: 薪资
      Collation: A
    Cardinality: 4
       Sub_part: NULL
         Packed: NULL
           Null: YES
     Index_type: BTREE
      Comment: 
  Index_comment: 
  

• 唯一性索引：所有列的所有值只能出现一次
  格式：create unique index <索引名字> on 表名（字段）;

  create unique index salary_unique_index on IT_salary(姓名);
  show index from IT_salary\G;
  *************************** 3. row ***************************
          Table: IT_salary
     Non_unique: 1
       Key_name: salary_index
   Seq_in_index: 1
    Column_name: 薪资
      Collation: A
    Cardinality: 4
       Sub_part: NULL
         Packed: NULL
           Null: YES
     Index_type: BTREE
        Comment: 
  Index_comment: 
  

• 主键：特殊的唯一性索引，必须指定为“PRIMARY KEY”，具有唯一性的同时不能为空
  格式：create yable 表名(...), primary key(字段);
  alter table 表名 add primary key(字段);

  create table IT_salary (岗位类别 char(20) not null, 姓名 char(20) not null, 年龄 int, 员工ID int not null, 学历 char(6), 年限 int, 薪资 int, primary key(员工ID));
  alter table IT_salary add primary key(员工ID);
  *************************** 1. row ***************************
          Table: IT_salary
     Non_unique: 0
       Key_name: PRIMARY
   Seq_in_index: 1
    Column_name: 员工ID
      Collation: A
    Cardinality: 4
       Sub_part: NULL
         Packed: NULL
           Null: 
     Index_type: BTREE
        Comment: 
  Index_comment: 
  

• 全文索引：类型为FULLTEXT，可以在VARCHARvarchar （(N)中的N指的是该字段最多能存储多少个字符，任何都可以称之为字符）或者TEXT（用于存储可以从1字节到4GB长度的文本字符串）类型上创建，小型网站上

• 删除索引
  格式：drop index <索引名称> on 表名;
  alter table 表名 drop index<索引名称>;
  alter table 表名 drop primary key;

  drop index salary_index on IT_salary;
  alter table IT_salary drop index salary_unipue_index;
  show kets from IT_salary;
  

• 单列索引与多列索引：字面意思

数据库事务

• 事务是一种机制，一个操作序列，一个不可分割的工作逻辑单元，最小的控制单元
• 原子性（atomictry）：是一个完整的操作，不可分割。
• 一致性（consistency）：事务开始前结束后，数据处于一致状态，过程中可不处于一致状态
• 隔离性（lsolation）：对数据修改的所有事务都是彼此隔离的
• 持久性（duracility）：一旦事务提交，就会永久保留在数据库中

事务的操作

默认情况下mysql的事务是自动提交的。

• 事务处理命令控制
  begin(开始一个事务)，commit（提交一个事务），rollback（回滚一个事务（撤销））
  mysql事务操作必须基于innodb存储引擎

  create databases auth;
  use auth;
  create table users(name char(20),age int,primary key(name)) engine=innodb
  begin
  insert into users values('lisi'.'123123');
  commit
  

• 使用set命令控制
  set autocommit=0 禁止自动提交
  set autocommit=1 开启自动提交