快速查找 Mysql 中各项数据 ------ 索引、事务与存储引擎 来帮忙

索引介绍

索引的概念

• 什么是数据库索引？
  1、它是一个排序的列表，存储着索引值和这个值所对应的物理地址
  2、无需对整个表进行扫描，通过物理地址就可以找到所需要的数据
  3、是表中一列或者若干列值排序的方法
  4、设置索引需要额外的磁盘空间

索引的作用

• 为什么要建立索引？
  1、极大的缩短了查找表或多个表所用的时间
  2、极大的提高了查询表的速度
  3、可以降低数据库的IO成本，并且还可以降低数据库的排序成本。（IO：读写；吞吐量）
  4、通过创建唯一性索引保证数据表数据的唯一性
  5、可以加快表与表之间的连接
  6、在使用分组和排序时，可以大大减少分组和排序时间

五种索引类型

• 普通索引：最基本的索引类型，没有唯一性之类的限制

  三种创建方式：create 、alter 、primary key
  创建格式：
  create index 索引名 on 表名 (列名);
  示例：
  create index index_name on info (name);

  alter table 表名 add index 索引名 (列名)
  示例：
  alter table info add index index_addr (addr);

  create table 表名 (id int(4) not null,name varchar(10) not null,primary key (id));

• 唯一性索引：与普通索引基本一样，唯一的区别是索引列的所有值只能出现一次，即必须唯一。

  三种创建方式：create 、alter 、primary key
  创建格式：
  create index index_名 on 表名 (已存在字段类型)

  alter table 表名 add index index_已有字段 (已有字段类型)

  create table 表名 (id int(4) not null,name varchar(10) not null,primary key (id));

• 主键索引：一个表只能有一个主键，不允许有空值，是一种特殊的唯一索引，指定为 “PRIMARY KEY”

  创建格式：
  create table table_字段名 (id int(11) not null,addr varchar(48) not null,primary key (id));

• 组合索引 ：可以是单列上创建的索引，也可以是在多列上创建的索引 ；最左原则，从左往右依次执行。（通两个或两个以上的列才能确定要一行记录）

  创建格式：
  create table 表名（name varchar(9),age int(3),sex tinyint(1),index 表名(name,age,sex));

• 全文索引：索引类型为 fulltext ；可以在 char、varchar 或者 text 类型的列上创建（通常用来查找较长的字段；但对于大容量的数据表，生成全文索引是一个非常消耗时间和硬盘空间的做法，不要使用）

  创建格式：创建表的同时创建全文索引
  create table 表名(id int auto_incremen not null primary key,addr varchar(48),acc text,fulltext(addr,acc)) type=MYISAM;

alter table 表名 add fulltext index index_字段名(字段名);

（扩展）

• 数据库中 NOT NULL 和 ‘’ 的区别:
  null：空对象，但有值，占空间
  ‘’：空字段，真正的啥都没有，不占空间

• 查看数据表结构方式：
  show keys from 表名;

  show index from 表名；

  desc 表名;

• 删除索引的方式：
  drop index index_字段名 on 表名；

  alter table 表名 drop index index_字段名；

创建索引的原则依据1

1、表的主键、外键必须有索引
2、记录数超过300行的表应该要有索引
3、经常与其他表进行连接的表，在连接字段上应该建立索引
4、唯一性太差的字段不适合建立索引
5、更新太频繁地字段不适合创建索引

创建索引的原则依据2

1、经常出现在 where子句中的字段，特别是在大表的字段应该建立索引
2、索引应该建在选择性高的字段上
3、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

事务介绍

事务的概念

1、事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行
2、是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
3、通过事务的整体性以保证数据的一致性
4、适用于多用户同时操作的数据库系统的场景，如银行、保险公司及整卷交易系统等等

事务ACID的四个特点

• 原子性（Atomicity）
  1、事务是一个完整的操作，事务的各元素是不可分的
  2、事务中所有元素必须作为一个整体提交或回滚
  3、如果事务中的任何元素失败，则整个事务将失败

• 一致性（Consistency）
  1、当事务完成时，数据必须处于一致状态
  2、在事务开始前，数据库中存储的数据处于一致的状态
  3、在正在进行的事务中，数据可能处于不一致的状态
  4、当事务成功完成时，数据必须再次回到已知的一致状态

• 隔离性（Isolation）
  1、对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
  2、修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

• 持久性（Durability）
  1、指不管系统是否发生故障，事务处理的结果都是永久的
  2、一旦事务被提交，事务的效果会被永久的保留在数据库中

事务控制语句

• MySQL事务默认是自动提交的，当SQL语句提交时事务便自动提交

• 事务控制语句
  1）begin 或 start transaction ：显示的开启一个事务

  2）commit ：提交事务，使对数据库进行的修改变为永久性的

  3）rollback ：回滚，会结束用户的事务，并撤销正在进行的所有未提交的修改

  4）savepoint identifier ：设置存档点，一个事务可以有多个存档点

  5）release savepoint identifier ：删除存档点，当没有指定的存档点时，执行该语句会抛 出一个异常

  6）rollback to identifier ：回滚到存档点（一旦回到存档点，该存档点就被删除）

  7）set transaction ：设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有 read uncommitted 、read committed 、repeatable read 和 serializable

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

事务控制命令

bebin 和 commit 事务

mysql> create database bank;
mysql> use bank;
mysql> create table info (id int(10) not null primary key auto_incrment,money double not null);
mysql> desc info;
mysql> alter table info add column name varchar(10);
mysql> desc info;
mysql> insert into info (name,money) values ('xiaohua',1000.00),('xiaowang',0.00);
mysql> begin;            ##开始事务
mysql> updata info set money=money-200 where name='xiaohua';          ###修改
mysql> select * from info;
mysql> updata info set money=money+200 where name='xiaowang';
mysql> select * from info;
mysql> commit;           ###提交事务
 
总结：从 begin 开始 到 commit 提交事务，中间的过程被视为一个整体，不可被分割。事务以内，操作为未提交；事务以外，事务为默认提交。

rollback 事务

mysql> use bbs；
mysql> create table info (id char(5) not null,name varchar(48) default '',primary key (id));
mysql> insert into info (id,name) values (1,'lisi');
mysql> begin;
mysql> insert into info (id,name) values (2,'zhangsan');
mysql> savepoint a;           ###设置存档点a
mysql> insert into info (id,name) values (10,'zhaoliu');
mysql> savepoint b;          ###设置存档点b
mysql> delete from bbs.info where info.id='zhaoliu';
mysql> rollback to b;          ###回滚到b存档点
mysql> select * from info;
mysql> rollback to a;           ###回滚到a存档点
mysql> select * from info;
mysql> rollback;           ###直接回滚
mysql> select * from info;

总结：一旦回到存档点，该存档点就被删除 ；直接回滚，将回到 begin 状态；事务的结束有二个，一个为commit 提交，一个为 rollback 直接回滚。

set 命令：set autocommit=0 和 set autocommit=1
接上述内容操作

mysql> set autocommit=0;
mysql> insert into info values (20,'xxx');
mysql> select *from info;
mysql> rollback;           ###直接回滚
mysql> select * from info;      ###证明 set autocommit=0 等同于 bebin
mysql> insert into info values (20,'xxx');
mysql> set autocommit=1;
mysql> select * from info;
mysql> rollback;
mysql> select * from info;         ###证明 set autocommit=1 等同于 commit

 
总结： set autocommit=0 等同于 bebin ，只不过暂时它是暂时放置在内存中； set autocommit=1 等同于 commit ，也是默认自动提交

存储引擎介绍

存储引擎的概念1

• MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎

• 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式

• MySQL常用的存储引擎：MyISAM 、InnoDB

存储引擎的概念2

• MySQL数据库中的组件，负责执行实际的数据I/O操作
• MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储

存储引擎 MyISAM的特点介绍

• MyISAM不支持事务，也不支持外键
• 访问速度快
• 对事务完整性没有要求
• MyISAM在磁盘上存储成三个文件
  1）.frm文件存储表定义
  2）数据文件的扩展名为 .MYD（MYData）
  3）索引文件的扩展名为 .MYI（MYIndex）
• 表级锁定形式，数据在更新时锁定整个表
• 数据库在读写过程中相互阻塞
  1）会在数据写入的过程阻塞用户数据的服务
  2）也会在数据读取的过程中阻塞用户的数据写入
• 数据单独写入或者读取，速度过程较快且占用资源相对少
• MyISAM支持的存储格式
  1）静态表
  2）动态表
  3）压缩表

MyISAM适用的生产场景

• 公司业务不需要事务的支持
• 单方面读取或者写入数据比较多的业务
• MyISAM存储引擎数据读写都比较频繁场景不适合
• 使用读写并发访问相对较少的业务
• 对数据业务一致性要求不是非常高的业务
• 服务器硬件资源相对比较差

存储引擎InnoDB特点介绍

• 支持4个事务隔离级别
• 行级锁定，但是全表扫描仍然会是表级锁定
• 读写阻塞与事务隔离级别相关
• 能够非常高效的缓存索引和数据
• 表与主键以簇的方式存储（簇：单元格）
• 支持分区、表空间，类似oracle数据库
• 支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
• 对硬件资源要求还是比较高的场合

InnoDB适用的生产场景

• 业务需要事务的支持
• 行级锁定对高并发有很好的适应能力，但需确保查询时通过索引来完成
• 业务数据更新较为频繁的场景
  1）如 论坛、微博
• 业务数据一致性要求较高
  1）如 银行业务
• 硬件设备结存较大，利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘I/O的压力

企业选择存储引擎的依据

• 需要考虑每个存储引擎提供的核心功能及应用场景

• 支持的字段和数据类型
  1）所有引擎都支持通用的数据类型
  2）但不是所有的引擎都支持其他的字段类型，如：二进制对象

• 锁定类型：不同的存储引擎支持不同级别的锁定：表锁定、行锁定

• 索引的支持
  1）建立索引在搜索和恢复数据库中的数据时能显著提高性能
  2）不同的存取殷勤根本不支持索引

• 事务处理的支持
  1）提高在向表中更新和插入信息期间的可靠性
  2）可根据企业业务是否要支持事务选择存储引擎

修改存储引擎4种方式

1、alter table 修改

alter table table_字段名 engine=指定的引擎

2、修改my.cnf 主配置文件，指定默认存储引擎并重启服务

vi my.chf
default-storage-engine=InnoDB
 
systemctl restart mysqld

3、create table 创建表时指定存储引擎

create table 表名 (字段) engine = 引擎

4、Mysql_concert_table_format 转化存储引擎（老版本才有的，现在一般不用了）

[root@ mysql ]# yum -y install perl-DBI perl-DBD-MySQL
[root@ mysql ]# /usr/local/mysql/bin/mysql_convert_table_format --user=root --password='123123' --sock=/tmp/mysql.sock auth

---

以上关于索引、事务、存储引擎的介绍到此结束，感谢浏览。