Mysql 索引、实务及存储引擎

前言

一：索引

1.1什么是索引（index）

1. 数据库中的索引与书籍中的目录类似

• 在一本书中，无须阅读整本书，利用目录就可以快速査找所需信息
• 书中的目录是一个词语列表，其中注明了包含各个词的页码

2. 数据库索引

• 在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据
• 数据库中的索引是某个表中一列或者若干列值的集合，以及物理标识这些值的数据页的逻辑指针清单

1.2索引的作用

1. 设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率
2. 特别是当表很大时，或者查询涉及到多个表时，使用索引可使查询加快成干倍
3. 可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本
4. 通过创建唯一性索引保证数据表数据的唯一性
5. 可以加快表与表之间的连接
6. 在使用分组和排序时，可大大减少分组和排序时间

1.3索引的优缺点

1. 优点

• 可以快速的找到所需要的的资源

2. 缺点

• 占用空间

1.4索引的分类

1. 普通索引

• 最基本的索引类型，而且没有唯一性的限制

2. 唯一索引

• 这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一

3. 主键索引

• 主键是一种唯一性索引，但它必须指定为“ PRIMARY KEY“

4. 全文索引

• MySQL从32323版开始支持全文索引和全文检索。在 MySQL中全文索引的索引类型为 FULLTEXT，全文索引可以在 ARCHAR或者TEXT类型的列上创建

5. 单索引与多索引

• 索引可以是单列上创建的索引，也可以是在多列上创建的索引

1.5索引创建的依据

1. 表的主键、外键必须有索引
2. 数据量超过300行的表应该有索引
3. 经常与其他表进行连接的表，在连接字段上应该建立索引
4. 唯一性太差的字段不适合建立索引
5. 更新太频繁地字段不适合创建索引
6. 经常出现在 Where子句中的字段，特别是大表的字段，应该建立索引
7. 索引应该建在选择性高的字段上
8. 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

1.6创建不同类型的索引

1. 创建普通索引

语法：
create index <索引的名字> on tablename(表名)；
例如：
create index sy on bm;
'//一张表同一列可以做多个索引'

 
 

 
 

2. 创建唯一索引

语法：
create unique index <索引的名字> on tablename(表名);
例如：
create unique index sy_unique on bm;

 
 

 
 

3. 创建全文索引

语法：
create fulltext index <索引的名字> on tablename（表名）;
例如：
create fulltext index sy on bm(addr);
'//全文索引：往往用于字符串，而不适用于数值类型'

 
 

 
 

4. 创建主键索引

语法：
create table tablename([...],primary key(列的列表))；
alter table tablename add primary key(列的列表)；
例如：
alter table bm add primary key;
'//创建主键索引的两种方式，创建表的时候指定主键索引和后期增加主键索引'

 
 

 
 

1.7如何查看索引？

语法：
show index from tablename;
show keys from tablename;
例如：
show index from bm;
show keys from bm;

 
 

 
 

二：事务

2.1什么是事务

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

2.2事务的特点

1. 原子性(Atomicity)

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

2. 一致性（Consistency）

• 当事务完成时，数据必须处于一致状态！
  • 在事务开始之前，数据库中存储的数据处于一致状态；
  • 在正在进行的事务中，数据可能处于不一致的状态；
  • 当事务成功完成时，数据必须再次回到已知的一致状态

3. 隔离性（Isolation）

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

4. 持久性（Durability）

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

2.3控制事务的命令

1. 使用事务处理命令控制事务

• bebin：开始一个事务
• commit：提交一个事务
• rollback：回滚一个事务

2. 使用set命令控制

• set autocommit=0：禁止自动提交
• set autocommit=1：开启自动提交

2.4事务的存档与回滚

1. 存档

格式：
savepoint <存档点名称>;
例如：
savepoint a;

 
 

 
 

2. 回滚

格式：
rollback  to <存档点名称>;
例如：
rollback to a;		'//回到s1的存档点'
rollback;	'//回到begin'
'//rollback只能向前回滚，无法向后回滚'

 
 

 
 

注意：
事务一旦rollback（回滚）就等同于commit（提交），但是如果设置：set autocommit=0（禁止自动提交），这里使用rollback并不会回滚，要结束当前事务必须commit（提交）事务，才可以结束事务。

三：存储引擎

3.1什么是存储引擎

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

3.2目前MySQL常用的两种存储引擎

3.2.1MyISAM

1. 什么是MyISAM？

• MyISAM存储引擎是 MySQL关系数据库系统5.5版本之前默认的存储引擎，前身是ISAM

• ISAM是一个定义明确且历经时间考验的数据表格管理方法，在设计之时就考虑到数据库被查询的次数要远大于更新的次数
  2. ISAM的特点

• 优点：ISAM执行读取操作的速度很快；不占用大量的内存和存储资源

• 缺点：不支持事务处理；不能够容错
  3. MyISAM的特点

• 不支持事务

• 表级锁定形式，数据在更新时锁定整个表

• 数据库在读写过程中相互阻塞

  • 会在数据写入的过程阻塞用户数据的读取
  • 也会在数据读取的过程中阻塞用户的数据写入

• 可通过key_buffer_size来设置缓存索引，提高访问性能，减少磁盘I/O的压力

  • 但缓存只会缓存索引文件，不会缓存数据

• 釆用 MyISAM存储引擎数据单独写入或读取，速度过程较快且占用资源相对少

• MyISAM存储引擎它不支持外键约束，只支持全文索引

• 每个 MyISAM在磁盘上存储成三个文件，每一个文件的名字以表的名字开始，扩展名指出文件类型

• MyISAM在磁盘上存储的文件

  • .frm文件存储表定义
  • 数据文件的扩展名为.MYD(MYDATA)
  • 索引文件的扩展名是.MYI(MYindex)
    4. 什么场景适合使用MyISAM

• 公司业务不需要事物的支持

• 一般单方面读取数据比较多的业务，或单方面写入数据比较多的业务

• MyISAM存储引擎数据读写都比较频繁的场景不适合

• 使用读写并发访问相对低的业务

• 数据修改相对少的业务

• 对数据业务一致性要求不是很高的业务

• 服务器硬件资源相对比较差

3.2.2InnoDB

1. InnoDB的特点

• 支持事务：支持4个事务隔离级别
• 行级锁定，但是全表扫描仍然会是表级锁定
• 读写阻塞与事务隔离级别相关
• 具有非常高效的缓存特性：能缓存索引，也能缓存数据
• 表与主键以簇的方式存储
• 支持分区、表空间，类似Oracle数据库
• 支持外键约束，5.5以前不支持全文索引，5.5版本以后支持全文索引
• 对硬件资源要求比较高的场合
  2. 什么生产场景适合使用InnoDB
• 业务需要事务的支持
• 业务锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成
• 业务数据更新较为频繁的场景，如：论坛、微博
• 业务数据一致性要求高，如：银行
• 硬件设备内存较大，利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘I/O的压力

3.3生产环境依据什么来选择存储引擎

1. 需要考虑好每个存储引擎提供了哪些不同的核心功能及应用场景
2. 支持的字段和数据类型

• 所有引擎都支持通用的数据类型
• 但不是所有的引擎都支持其他的字段类型

3. 锁定类型：不同的存储引擎支持不同级别的锁定

• 表锁定
• 行锁定

4. 索引的支持

• 建立索引在搜索和恢复数据库中的数据的时候能够显著提高性能
• 不同的存储引擎提供不同的制作索引的技术
• 有些存储引擎根本不支持索引

3.4如何配置存储引擎

1. 在企业中选择好合适的存储引擎之后，进行修改
2. 修改步骤

• 查看数据库可配置的存储引擎
• 查看表正在使用的存储引擎
• 配置存储引擎为选择的类型

3. 使用 show engines 查看系统支持的存储引擎
4. 查看表使用的存储引擎

方法1：show table status from库名 where name=表名；
方法2：show create table表名；'//常用'
例如：
MySQL > show table status from yibiao where name='sjk'\G 
'//使用\G代替分号表示垂直显示结果'

 
 

 
 

3.5如何修改存储引擎

方法一：

alter table 修改；
格式：
alter table table_name engine=引擎；
例如：
MySQL> alter table bm engin=MyISAM;

 
 

 
 

方法二：

修改my.cnf,指定默认存储引擎并重启服务
格式：
default-storage-engine=InnoDB
例如：
vim my.cnf
default-storage-engine=InnoDB

 
 

 
 

方法三：

create table 创建表时指定存储引擎
格式：
create table 表名 （字段） engine=引擎
例如：
MySQL> create table bm（id int）engine=MyISAM;