存储引擎、索引(MySQL笔记第四期)

p.s.这是萌新自己自学总结的笔记，如果想学习得更透彻的话还是请去看大佬的讲解

存储引擎概念

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式，存储引擎是基于表的，而不是基于库的，所以一个库里的多张表可能使用不同的存储引擎，所以存储引擎也被称为表类型

-- 查询建表语句(查询表用的引擎，默认为InnoDB)
show create table account;

在创建表时，指定存储引擎

CREATE TABLE 表名(字段1  字段1类型[COMMENT 字段1注释],字段2  字段2类型[COMMENT 字段2注释],字段3  字段3类型[COMMENT 字段3注释],字段n  字段n类型[COMMENT 字段n注释]
)ENGINE=INNODB [COMMENT 表注释];

查看当前数据库支持的引擎：SHOW ENGINES;

InnoDB存储引擎

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后InnoDB时默认的MySQL存储引擎
特点：
DML操作遵循ACID模型，支持事务
行级锁，提高并发访问性能
支持外键约束，保证数据的完整性和准确性

MyISAM存储引擎

MyISAM是MySQL早期的默认储存引擎
特点
不支持事务、外键
支持表锁，不支持行锁
访问速度快

Memory存储引擎

Memory引擎的表数据是存储在内存中的，由于受到硬件问题，或断电问题的影响，只能将这些表作为临时表或者缓存表使用
特点
内存存放
hash索引(默认)

存储引擎的选择

在选择存储引擎的时候，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合
.
InnoDB:是MySQL的默认存储引擎，支持事务、外键，如果应用对于事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那InnoDB存储引擎是很适合的操作
MyISAM:如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事物的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的
Memory:将所有数据保存在内存中，访问速度快，通常用于临时表及缓存，Memory的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性
看看大佬讲InnoDB和MyISAM
看看大佬讲Memory

索引

索引是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构

---

---

三种索引

B+Tree特点：数据都在叶子节点上；叶子节点会形成一个单向链表
.
.
MySQL索引数据结构对经典的B+Tree进行了优化，在原B+Tree的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，即

.
.
.
Hash索引就是采用一定的哈希算法，将键值换算为新的hash值，映射到对应的槽位上，然后存储在哈希表中
如果两个或多个键值映射到了一个相同的槽位上，它们就产生了hash冲突(也成为hash碰撞)，可以通过链表来解决
，
Hash索引特点
Hash索引只能用来对等比较(=、in),不支持范围查询(between、>、<、…)
无法用索引完成排序操作(无序)
查询效率高，通常一次检索即可，效率通常高于B+Tree索引
，
在MySQL中，支持Hash索引的是Memory引擎，而InnoDB具有自适应Hash功能，Hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的

InnoDB引擎选择B+Tree索引结构的原因
相较于二叉树，层级更少，搜索效率更高
对于B-Tree，无论是叶子节点还是非叶子节点都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
相对于Hash索引，B+Tree索引结构支持范围匹配和排序条件

索引分类

聚集索引选取规则
如果存在主键，那么主键索引就是聚集索引
如果不存在主键，那么将使用第一个唯一索引作为聚集索引
如果没有主键，也没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

如果执行select * from ueser where name = 'Arm';则会执行回表查询·：即先通过二级索引找到对应的主键，再通过聚集索引和找到的主键来找到这一行的数据

语法(创建/查看/删除)

创建索引:CREATE [ UNIQUE | FULLTEXT ] INDEX 索引名 ON 表名 (要添加索引的字段名,...);
单列索引：一个索引关联了一个字段
联合索引：一个索引关联的多个字段

查看索引：SHOW INDEX FROM 表名;

删除索引：DROP INDEX 索引名 ON 表名;

举例

create table worker(name char(3) comment '姓名',phone char(11) comment '手机号',age tinyint unsigned comment '年龄',profession varchar(4) comment '职称',gender char(1) comment '性别',email varchar(16) comment '邮箱地址'
);insert into worker values ('张三',123456,18,'经理','男','1122@123.com'),('李四',698547,20,'员工','女','2265@125.com'),('王五',642851,19,'员工','女','123365@123.com'),('张三',125462,25,'保安','男','1652@125.com');show index from worker;-- 为name字段创建索引(可重复)
create index index_worker_name on worker(name);-- 为phone创建唯一索引
create unique index index_worker_phone on worker(phone);-- 为age、profession、gender创建联合索引
create index idx_worker_age_pro_ged on worker(age,profession,gender);-- 为email创建合适的索引来提高查询效率
create index idx_worker_email on worker(email);-- 删除索引
drop index idx_worker_email on worker;

性能分析工具

SQL执行频率

通过SQL执行频率，我们可以更好地进行SQL优化，从而提高数据库性能

MySQL客户端连接成功后，通过show [session|global] status命令可以提供服务器状态指令。通过如下指令，可以查看当前数据库增删改查语句的访问频次
SHOW GLOBAL STATUS LIKE 'Com_______';

慢查询日志

通过慢查询日志，我们可以查清楚哪些语句执行效率较低，从而对语句进行更细致的优化，最终提高数据库性能

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位：秒，默认10秒)的所有SQL语句的日志
大佬内容

profile详情

有些SQL语句业务简单，但执行时间较长，这条SQL语句效率相对较低，需要优化

show profiles能够在SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看见当前MySQL是否支持profile操作：SELECT @@have_profiling;
profiling默认关闭，可以通过SET语句在session/global级别开启profiing;
show profiles;:查看每一条SQL语句的耗时基本情况
show profile for query query _id;:查看指定query _id的SQL语句的耗时情况
show profile cpu for query query _id;`:查看指定query _id的SQL语句CPU的使用情况

explain执行计划

explain或desc命令获取MySQL如何执行SELECT语句的信息，包括SELECT语句执行过程中表如何连接以及连接的顺序，从而评判SQL语句的性能
语法：在任意的SELECT语句前面加上EXPIAIN或DESC

explain执行计划各字段含义
.
id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)
.
select_type：表示select的类型常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
.
type：表示连接类型，性能由好到差的连接类型为：NULL(不访问表查询)、system(访问系统表查询)、const(根据主键或者唯一索引查询)、eq_ref、ref(根据非唯一索引查询)、range、index(用了索引)、all(全盘查询)
.
possible_key：显示可能应用在这张表上的索引，一个或多个
.
key：实际使用的索引，如果为NULL，则没有使用索引
.
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，再不损失精确度的情况下，长度越短越好
.
rows：MySQL认为必须要执行查询的行数，在InnoDB引擎的表中是一个估计值，可能并不总是准确的
.
filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

索引的使用规则(及失效情况)

最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是从查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，则索引部分失效(后面的字段索引失效)

举例

-- 索引长度为26
explain select * from worker where age=18 and profession='经理' and gender='男';-- 索引长度为21
explain select * from worker where age=18 and profession='经理' ;-- 索引长度为2
explain select * from worker where age=18;-- 未遵循最左前缀法则，索引全部失效
explain select * from worker where  profession='经理' and gender='男';-- 未遵循最左前缀法则，索引部分失效(索引长度为2)
explain select * from worker where age=18 and  gender='男';-- 索引长度为26(即最左前缀法则只看索引是否存在，与SQL语句中索引位置无关)
explain select * from worker where profession='经理' and gender='男' and age=18;

范围查询失效情况

联合索引中出现范围查询(>,<)时范围查询右侧的索引会失效(可使用>=,<=规避问题)
举例

-- 索引长度为2
explain select * from worker where age>17 and profession='经理' and gender='男';-- 索引长度为2(与SQL语句中索引位置无关)
explain select * from worker where profession='经理' and gender='男' and age>17;-- 索引长度为26
explain select * from worker where age>=17 and profession='经理' and gender='男';

索引列运算

不要再索引列上进行运算操作，否则索引会失效
举例

-- 索引长度为67
explain select * from worker where email='1122@123.com';-- 索引失效(因为对索引列进行了运算)
explain select * from worker where substring(email,2,2) = 12;

字符串不加引号

字符串类型字段使用时不加引号，索引将失效
举例

-- 索引失效
explain select * from worker where phone=123456;

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效。
举例

-- 索引长度为45
explain select * from worker where phone like '12____';-- 索引长度为45
explain select * from worker where phone like '12__56';-- 索引失效
explain select * from worker where phone like '____56';-- 索引失效
explain select * from worker where phone like '%34__';

or连接的条件

用or分割开的条件，所有条件中的列都需要有索引，否则涉及的索引都不会用到(即失效)
举例

-- 删除索引
drop index idx_worker_email on worker;-- ------------------------------------------------------------------------- 索引失效
explain select * from worker where phone='123456' or email='1122@123.com';-- 索引失效
explain select * from worker where email='1122@123.com' or phone='123456';-- 索引长度分别为45，13
explain select * from worker where phone='123456' or name='张三';

数据分布的影响

如果MySQL评估使用索引比全表更慢，则不使用索引
举例

-- 索引失效，走的是全盘扫描
explain select * from worker where age>=0;-- 索引失效，走的是全盘扫描
explain select * from worker where phone is not null;

SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示 来达到优化操作的目的

关于索引的**(即当字段的索引有多种的时候，人为地让数据库使用特定的索引)**

use index()：告诉数据库应该使用哪种索引(有可能不听你的)

ingore index()：告诉数据库应该忽略哪种索引

force index()：告诉数据库必须使用哪种索引

举例

explain select * from worker use index(idx_worker_age) where age=18;explain select * from worker ignore index(idx_worker_age) where age=18;explain select * from worker ignore index(index_worker_name) where name='张三';explain select * from worker force index(idx_worker_age) where age=18;

覆盖索引

尽量使用覆盖索引(查询使用了索引，并且所需返回的列，在该索引中已经能全部找到)，减少select *的使用
举例

-- Extra显示Using index condition
explain select * from worker where age = 18 and profession = '张三' and gender = '男';-- Extra显示Using where; Using index
explain select age,profession,gender from worker where age = 18 and profession = '张三' and gender = '男';-- Extra显示Using index condition
explain select age,profession,gender,name from worker where age = 18 and profession = '张三' and gender = '男';

Extra显示Using index condition：查找使用了索引，但需要回表查询数据
Extra显示Using where; Using index：查找使用了索引，由于需要的数据都能在索引列中找到，所以不需要回表查询数据
覆盖索引更详细解析

前缀索引

当字段类型为字符串(varchar、text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时会浪费大量的磁盘IO，影响查询效率.此时可以只将字符串的一部分进行前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

创建语法：创建索引:CREATE [ UNIQUE | FULLTEXT ] INDEX 索引名 ON 表名 (字段(n));(n表示前缀的长度)

前缀长度可以根据索引的选择性来决定；而选择性时是指不重复的索引值(基数)和数据表的记录总数的比值。索引的选择性越高查询效率越高
唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

举例

-- 由于phone字段的是唯一索引，因此选择性为1
select count(distinct phone) / count(*) from worker;-- 选择性为1
select count(distinct substring(phone,1,3)) / count(*) from worker;-- 选择性为0.75
select count(distinct substring(phone,1,2)) / count(*) from worker;

由于前缀索引的长度以及选择性是呈反比关系，所以在使用时要进行取舍

单列索引/联合索引

单列索引：即一个索引只包含了一个列
联合索引：即一个索引只包含了多个列

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议联合索引，而非单列索引

多条件联合查询时，MySQL优化器会评估哪个字段的查询效率更高，会选择该索引完成本次查询

索引设计原则

针对于数据量较大(>一百万)，且查询比较频繁的表建立索引(索引是用来方便查询用的)
.
针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引
.
尽量选择区分度高(即重复度低)的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率就越高
.
如果是字符串类型的字段，字符的长度较长，可以针对于字段的特点，建立前缀索引
.
尽量使用联合索引、减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表查询，提高查询效率
.
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率
.
如果索引列不能存储NULL值，请在创造表的时候使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好的确定哪个索引最有效地用于查询