mysql索引、事务以及存储引擎

目录

一、索引

1）索引定义

​2）工作方式

3）创建索引的依据

4）索引类型

1、index普通索引

2、unique唯一索引

3、主键索引

4、多列组合索引

5、全文索引

5）删除索引

6）查看索引

7）索引失效

二、事务

1）事务的概念 

2）事务的ACID特性【☆】

3）查看隔离级别

4）设置隔离级别

5）事务管理操作

 三、存储引擎

1）存储引擎定义

2）MyISAM 和 InnoDB 的区别

3）MySQL 查询数据的执行过程

4）查看存储引擎

5）存储引擎管理操作

6）行锁、表锁、死锁

---

一、索引

1）索引定义

        索引是一个排序的列表，包含索引字段的值和其对应的行记录数据所在的物理地址。（保存索引需要额外的磁盘空间）

作用：加快表的查询速度（主要作用），还可以对字段排序，可以降低数据库的IO成本和排序成本。

副作用：会额外占用磁盘空间；更新包含索引的表会花费更多的时间。

2）工作方式

        没有索引的情况下，要查询某行记录数据时，需要先扫描全表，再根据条件判断定位到某行记录数据的位置；有了索引后，会先通过索引查询到行记录数据所在的物理地址，即可直接访问相应的行记录数据，就像通过书目录的页码快速查找书内容一样。

3）创建索引的依据

1. 表的记录行数较多时（一般超过三五百行时），且读操作多的情况下应该要创建索引；
2. 建议在表的主键字段、外键字段、多表连接使用的公共字段、唯一性较好的字段、不经常更新的字段、where条件字段、分组(group by)字段、排序(order by)字段、短小的字段 上创建索引；
3. 不建议在 唯一性较差的字段、更新太频繁的字段、大文本字段 上创建索引。

4）索引类型

1、index普通索引

普通索引 ：没有唯一性之类的限制

#直接创建（索引长度不指定的话会默认所有字符都创建到索引）
create index 索引名 on 表名(字段(长度));
   
#修改表方式创建
alter table 表名 add index 索引名(字段);     

#创建表使创建索引，但一般不建议
create table 表名 (.... , index 索引名(字段));        

2、unique唯一索引

唯一索引的列的值都唯一存在，允许有空值，组合创建索引，组合的值必须唯一存在。创建唯一键时会自动创建唯一索引

#直接创建
create unique index 索引名 on 表名(字段);

#修改表方式创建
alter table 表名 add unique 索引名(字段);

#创建表时指定
create table 表名 (.... , unique 索引名(字段));

3、主键索引

主键索引 primary key，一个表只能有一个主键，不允许有空值，添加主键会自动创建主键索引，可以多字段组合创建为一个索引

#修改表方式创建
alter table 表名 add primary key(字段1,[字段2]);

#创建表时指定一个字段做索引
create table 表名 (字段1 数据类型 primary key，字段2 数据类型，... );
create table 表名 (字段1 数据类型，字段2 数据类型，... ，primary key(字段1));

#创建表时指定多个字段做索引
create table 表名 (字段1 数据类型，字段2 数据类型，... , primary key(字段1,[字段2]);

注意：
create table 表名 (字段1 数据类型 primary key，字段2 数据类型 primary key，...);此命令表示两个字段单独创建索引，是不允许的，会报错

4、多列组合索引

多个字段组合在一起创建为一个索引，要满足最左原则：字段查询顺序要与索引设置的字段顺序保持一致才能生效。

#直接创建
create index 索引名 on 表名(字段2, 字段1, ....);

#修改表方式创建
alter table 表名 add index 索引名(字段2, 字段1, ....);

#查询语句
select * from 表名 where 字段2=XX and 字段1=XX .... ;   
#查询语句使用 and 做逻辑运算符时，字段顺序要与创建的多列索引的字段顺序一致（要满足最左原则）

5、全文索引

#直接创建
create fulltext index 索引名 on 表名(字段);

#修改表方式创建          
alter table 表名 add fulltext 索引名(字段);
          
#创建表时创建全文索引
create table 表名 (.... , fulltext 索引名(字段));

#全文索引查询语句   
select 字段列表 from 表名 where match(字段) against('单词');

       全文索引可以模糊查询关键字查询。只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引，5.7.6版本之前只支持InnoDB和MyISAM引擎。全文索引只支持英文全文索引，不支持中文全文索引。需要使用ngram全文解析器，用来支持中文、日文、韩文全文索引。

#修改配置文件
vim /etc/my.cnf
[mysqld]
ngram_token_size=2       #指定查询的单词的最小字数

#重启mysql
systemctl restart mysql

① 创建全文索引

create fulltext index 索引名 on 表名(字段) WITH PARSER ngram;
alter table 表名 add fulltext 索引名(字段) WITH PARSER ngram;
create table 表名 (.... , fulltext 索引名(字段) WITH PARSER ngram);

② 全文检索模式

#默认使用自然语言模式，不能使用操作符指定+关键词必须出现或-不能出现等复杂查询：

select 字段列表 from 表名 where match(字段) against('单词');    

#BOOLEAN模式可以使用操作符，可以指定+关键词必须出现或者-不能出现等复杂查询：

select 字段列表 from 表名 where match(字段) against('+单词1 -单词2' IN BOOLEAN MODE);     
#使用BOOLEAN模式，必须包含"单词1"，且不能包含"单词2"

select 字段列表 from 表名 where match(字段) against('+单词1 +单词2' IN BOOLEAN MODE);     
#使用BOOLEAN模式，必须同时包含"单词1"和"单词2"

select 字段列表 from 表名 where match(字段) against('单词1 单词2' IN BOOLEAN MODE);       
#使用BOOLEAN模式，要么包含"单词1"，要么包含"单词2"

5）删除索引

#这两种方法都可以用来删除普通索引、唯一索引、全文索引、组合索引

drop index 索引名 on 表名;              
alter table 表名 drop index 索引名;

 #删除主键索引

alter table 表名 drop primary key;    

6）查看索引

show create table 表名;          #查看表结构查看索引信息
show index from 表名;
show keys from 表名;

遇到 select 查询语句执行速度慢该怎么办？
1）升级 CPU 内存 硬盘 硬件性能
2）对 MySQL 配置进行优化：

• max_connections的值需要比默认的151连接数更大的值；
• innodb_buffer_pool_size设置缓冲池，它是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。
• innodb_log_file_size  redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6，你可以一开始就把它这是成4G。

3）对查询语句的结构进行优化，比如将嵌套子查询优化成表连接查询；或连接表时，可以先用where条件对表进行过滤，然后做表连接；
4）进行索引优化：先使用 explain 分析 select 语句（看 key rows type 字段），判断这个查询语句是否正确的使用了索引，再根据查询语句中的 where 使用的条件字段建立相应的单列索引或者多列组合索引（多列组合索引要满足最左原则）

7）索引失效

        我们创建了索引，但不代表查询时使用索引都能失效，这取决于查询语句是否会导致索引失效做了全表扫描。那么，哪些情况应该避免索引失效呢？

• 避免在where条件中对字段进行null判断，如select * from 表名 where 字段 is null；
• 避免在where条件中使用 != 或 <>，如select * from 表名 where 字段 != 值；
• 避免在where条件中对表达式进行操作，如select * from 表名 where age*2 = 30; 可以写为select * from 表名 where age = 30/2;
• 避免在where条件中对字段进行函数操作；
• 避免在like查询中将%放在开头，如select * from 表名 where 字段 like '%值';

二、事务

1）事务的概念 

        事务的定义：事务就是一组数据库操作序列（包含一个或多个SQL操作命令），事务会把所有操作看成一个不可分割的整体向数据库系统提交或撤销所有操作，所有操作要么都执行，要么都不执行。事务是最小的控制单元，适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等通过事务的整体性以保证数据的一致性。

        一个可靠的数据库具备ACID特性，在事务的管理中，原子性是基础，隔离性是手段，一致性是目的，持久性是结果。

2）事务的ACID特性【☆】

• 原子性Atomicity：事务中的所有操作看做是一个不可分割的工作单元，要么都执行，要么都不执行。（案例：A给B转账100元的时候只执行了扣款语句就提交了，此时如果突然断电，A账号已经发生了扣款，B账号却没收到价款，在生活中就会引起纠纷，这种情况就需要事务的原子性来保证）
• 一致性Consistency：保证事务开始前和事务结束后数据的完整和一致。（案例：对银行转帐事务，不管事务成功还是失败，应该保证事务结束后表中和B的存款总额跟事务执行前一致。）
• 隔离性Isolation：多个事务并发操作同一个表数据时，每个事务都有各自独立的数据空间，一个事务的执行不会受到其他事务的干扰。可通过设置隔离级别来解决不同的一致性问题。
• 持久性Durability：事务管理的结果。当事务被提交以后，事务中的命令操作修改的结果会被持久化保存，且不会被回滚。

隔离的运行级别
未提交读	read uncommitted	会出现脏读、不可重复读、幻读	最低级别的隔离
提交读	read committed	不会出现脏读，会出现不可重复读、幻读	生产环境中大量 使用的隔离级别
可重复读	repeatable read	不会出现脏读、不可重复读，有条件的允许幻读（InnoDB存储引擎可以通过多版本并发控制MVCC解决幻读问题）	用的也还挺多，性能会受到影响且会出现死锁问题
串行读	serializable	脏读、不可重复读、幻读的问题都不会出现，相当于标记锁定，不能并发处理事务，会影响数据库的读写效率性能	实际应用中比较少用
不可重复读打个比方：事务1开启后先查询表1的数据为data1，事务2开启后对表1进行了改操作，表1的数据为data2。在事务2操作后,事务1再次查看表1数据，却显示不是data1了；或者是在事务2提交后查看到表1的数据不是data1。简单说就是，在一个事务中即使对表数据没有任何操作，数据依旧发生改变的情况就叫不可重复读。

一致性问题
脏读	比如A在银行atm机上存1000元现金还未提交，但此时A的女朋友登录app查看到账户有1000元
不可重复读	指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。 比如A查询账户余额为0时，B此时存入现金1000元把余额改为了1000，并不知情的A再刷新余额一看突然变成了1000元。
幻读	A对表的全部数据进行了统一的修改，此时B插入了一条数据，在A的视角会疑惑怎么还有一条数据没有修改呢，像出现幻觉一样。
丢失更新	两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果

3）查看隔离级别

#%百分号表示任意长度的任意字符，like模糊匹配，此方式会做全表查询
show global variables like '%isolation%';        
show session variables like '%isolation%';

4）设置隔离级别

#全局级隔离级别，可在所有会话有效，当前会话需要重新登录方可有效
set global transaction isolation level 隔离级别名称;        

#会话级隔离仅在当前会话中立即有效
set session transaction isolation level 隔离级别名称;     

5）事务管理操作

begin;                               #显式的开启一个事务
insert into | update |delete from    #事务性操作
savepoint XX;                        #在事务中创建回滚点
rollback to XX;                      #在事务中回滚到指定的回滚点位置
commit;                              #提交结束事务（永久保存所有操作）
rollback;                            #或回滚结束事务（即撤销，或rollback to 回滚点；）

 

自动提交事务

mysql默认自动提交事务是开启的，操作后自动commit保存我们对表数据的修改。如果关闭此功能，每次修改数据后都需要手动commit;提交才能实现保存效果。

show global/session variables like 'autocommit';         #查看自动提交是否开启

set global/session autocommit = 0/1       #global全局级别，session会话级别，0关闭自动提交，1开启自动提交

 三、存储引擎

1）存储引擎定义

        存储引擎是MySQL数据库的组件，负责执行实际的数据IO操作（数据的存储和提取），工作在文件系统之上，数据库的数据会先传输到存储引擎，再按照存储引擎的存储格式保存到文件系统。MySQL常用的存储引擎：MyISAM、InnoDB等。

2）MyISAM 和 InnoDB 的区别

MyISAM

1. 不支持事务、外键约束；支持全文索引；
2. 只支持表级锁定；适合单独的查询和插入的操作；
3. 读写会相互阻塞；硬件资源占用较小；
4. 数据文件和索引文件是分开存储的，存储成三个文件：表结构文件.frm、数据文件.MYD、索引文件.MYI；
5. 使用场景：适用于不需要事务支持，单独的查询或插入数据的业务场景

InnoDB

1. 支持事务、外键约束；也支持全文索引；
2. 支持行级锁定，但在全表扫描时仍会表级锁定；
3. 读写并发能力较好；缓存能力较好可以减少磁盘IO的压力；
4. 数据文件也是索引文件，存储成：表结构文件.frm、表空间文件.ibd；
5. 使用场景：适用于需要事务支持，数据一致性要求较高，数据会频繁更新，读写并发高的业务场景
    

3）MySQL 查询数据的执行过程

1. 客户端向 MySQL 服务器发送一条查询请求，连接器负责处理连接，并进行身份验证和权限控制。
2. MySQL 先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果；否则使用查询解析器进行SQL语句解析、预处理，再由优化器生成对应的执行计划。
3. MySQL 根据执行计划，调用存储引擎来执行查询。
4. 将结果返回给客户端，同时缓存查询结果。

4）查看存储引擎

show engines;

show create table 表名;

show table status [from 库名] where name = '表名';

5）存储引擎管理操作

#针对已存在的表修改存储引擎
alter table 表名 engine=innodb/myisam;    

#新建表时指定存储引擎
create table 表名 (....) engine=innodb/myisam;   

#设置默认存储引擎
set global/session default_storage_engine=innodb/myisam;     

#修改配置文件修改默认存储引擎
vim /etc/my.cnf
default_storage_engine=INnoDB/MyISAM
#重启mysql生效
systemctl restart mysqld

6）行锁、表锁、死锁

行锁

表锁

结论：InnoDB的行级锁是通过给索引项加锁来实现的。如果对没有索引的字段进行操作会使用全表扫描并表级锁定。

死锁

        两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象，若无外力作用，事务都将无法继续运行。此时称系统处于死锁状态或系统产生了死锁。比如事务1根据索引删除了表A的数据1，事务2根据索引删除了表A的数据2，都未提交事务。此时的数据1和数据2的行内容都各自触发了行锁。然后事务1请求操作数据2，因为行锁无法执行，一直挂着；事务2再来请求操作数据1，互相访问了各自锁定的数据行记录就会触发死锁。

如何避免死锁？
1）设置事务的锁等待超时时间 innodb_lock_wait_timeout

show VARIABLES like 'innodb_lock_wait_timeout';  #查看当前系统是否设置锁等待超时时间
2）设置开启死锁检测功能 innodb_deadlock_detect

show VARIABLES like  'innodb_deadlock_detect';   #查看当前死锁检测是否开启
set global innodb_deadlock_detect = ON;                #设置ON为开启死锁检测，OFF为关闭
3）为表建立合理的索引，减少表锁发生的概率
4）如果业务允许，可以降低隔离级别，比如选用 提交读 Read Committed 隔离级别，从而避免间隙锁导致死锁
5）建议开发人员尽量使用更合理的业务逻辑，比如多表操作时以固定顺序访问表，尽量避免同时锁定多个资源
6）建议开发人员尽量保持事务简短，减少对资源的占用时间和占用范围
7）建议开发人员在读多写少的场景下采用乐观锁机制

补充

间隙锁：事务操作过程中操作语句条件中根据索引字段删选操作的范围内的数据记录都会被上锁，范围之外的不会上锁。

乐观锁：在操作数据时不会上锁，认为别人不会同时修改数据，只会在执行更新的时候判断一下在此期间别人是否修改了数据，如果别人修改了数据则放弃操作，否则执行操作。适用于读多写少的场景。
悲观锁：操作数据时直接把数据锁住，直到操作完成后才会释放锁；上锁期间其他人不能修改数据。一般适用于写多的场景，系统默认使用悲观锁。