MySQL(二)索引和SQL优化

MySQL体系结构

• 连接层
  最上层是一些客户端和链接服务，主要完成一些类似于连接处理，授权认证，及相关的安全方案。服务器也会为安全接入的每个客户验证它所具有的操作权限。
• 服务层
  第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行，所有跨存储引擎的功能也在这层实现，如过程，函数等。
• 引擎层
  存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样可以根据自己的需要，来选取合适的存储引擎。
• 存储层
  主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
所以可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

建表时指定存储引擎：

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

查询当前数据库支持的存储引擎

show engines;

创建表 my_myisam , 并指定MyISAM存储引擎

create table my_myisam(id int,name varchar(10)
) engine = MyISAM ;

创建表 my_memory , 指定Memory存储引擎

create table my_memory(id int,name varchar(10)
) engine = Memory ;

存储引擎特点

InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的MySQL 存储引擎。

特点

• DML操作遵循ACID模型，支持事务；
• 行级锁，提高并发访问性能；
• 支持 外键 FOREIGN KEY约束，保证数据的完整性和正确性；

文件
xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。

show variables like 'innodb_file_per_table';

如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。 直接打开MySQL的数据存放目录： D:\java\software\mysql-8.0.11-winx64\Data ， 这个目录下有很多文件夹，不同的文件夹代表不同的数据库，直接打开itcast文件夹如下：

每一个ibd文件就对应一张表，比如：有一张表 account，就有这样的一个account.ibd文件，而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的索引信息。 而该文件是基于二进制存储的，不能直接基于记事本打开，可以使用mysql提供的一个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表的表结构。

逻辑存储结构

• 表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
• 段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
• 区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。
• 页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
• 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。

MyISAM

MyISAM是MySQL早期的默认存储引擎。

特点

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

文件
xxx.sdi：存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引

Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点

• 内存存放
• hash索引（默认）

文件
xxx.sdi：存储表结构信息

区别及特点

InnoDB引擎与MyISAM引擎的区别 ?

• InnoDB引擎, 支持事务, 而MyISAM不支持。
• InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。
• InnoDB引擎, 支持外键, 而MyISAM是不支持的。

存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

• InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
• MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• MEMORY： 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引

索引（index）是帮助MySQL 高效获取数据的有序数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）原始数据， 这样就可以在数据结构上通过高级查找算法快速定位到原始数据。

 select * from user where age = 45;

• 无索引情况：就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。
  
• 有索引情况：如果针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对age这个字段建立一个二叉树的索引结构。此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。
  

优点：

• 提高数据检索的效率，降低数据库的IO成本
• 通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点：

• 索引也要占用空间
• 索引大大提高了查询效率，同时也降低了更新表的速度，如对表进行INSERT,UPDATE,DELETE时，效率降低

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

不同的存储引擎对于索引结构的支持情况：

二叉树

假如说MySQL的索引结构采用二叉树(一个节点下面最多包含两个节点)的数据结构，比较理想的结构如下：

如果主键是顺序插入的，则会形成一个单向链表，结构如下：查找17时，相当于把整张链表遍历一次

如果选择二叉树作为索引结构，会存在以下缺点：

• 顺序插入时，会形成一个链表，查询性能大大降低。
• 因为一个节点下最多只能包含两个子节点，大数据量情况下，层级较深，检索速度慢。

如果选择红黑树，红黑树是一颗自平衡二叉树，即使顺序插入数据，最终形成的数据结构也是一颗平衡的二叉树,结构如下:

由于红黑树也是一颗二叉树，所以也会存在一个缺点：

• 大数据量情况下，层级较深，检索速度慢。

B-Tree

B-Tree是一种多路平衡查找树，相对于二叉树，B树每个节点可以有多个分支即多叉。以一颗最大度数（max-degree）为5(5阶)的b-tree为例，(即B树每个节点最多存储4个key，5个指针)：
树的度数指的是一个节点的子节点个数。

可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~galles/visualization/BTree.html

插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。然后观察一些数据插入过程中，节点的变化情况。

特点：

• 5阶的B树，每一个节点最多存储4个key，对应5个指针。
• 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
• 在B树中，非叶子节点和叶子节点都会存放数据。

B+Tree

B+Tree是B-Tree的变种，以一颗最大度数（max-degree）为4（4阶，3个key，4个指针）的b+tree为例，其结构示意图：

• 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
• 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

B+Tree 与 B-Tree相比，主要有以下三点区别：

• 所有的数据都会出现在叶子节点。
• 叶子节点形成一个单向链表。
• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

通过数据结构可视化的网站来简单演示一下https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。然后观察一些数据插入过程中，节点的变化情况。

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

Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

特点

• Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
• 无法利用索引完成排序操作
• 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

存储引擎支持
在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

为什么InnoDB存储引擎选择使用B+tree索引结构？

• 相对于二叉树，层级更少，搜索效率高；
• 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
• 相对Hash索引，B+tree支持范围匹配及排序操作；

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引和二级索引的具体结构如下：

• 聚集索引的叶子节点下挂的是这一行的数据 。
• 二级索引的叶子节点下挂的是该字段值对应的主键值。

当执行 select* from user where name = ‘Arm’，具体的查找过程如下：

• 由于是根据name字段进行查询，所以先根据name字段的二级索引中进行匹配查找。Lee—>Geek—>Arm，在二级索引中只能查找到 Arm 对应的主键值 10。
• 由于查询返回的数据是*(所有返回字段)，所以还需要根据主键值10，到聚集索引中查找10对应的记录，15—>10最终找到10对应的行row。
• 最终拿到这一行的数据，直接返回即可。

回表查询： 先到二级索引中查找数据，找到主键值，再到聚集索引中根据主键值，这种获取数据的方式就称之为回表查询。

思考：以下两条SQL语句，那个执行效率高? 为什么?
A. select * from user where id = 10 ;
B. select * from user where name = ‘Arm’ ;
备注: id为主键，name字段创建的有索引；

解答：
A 语句的执行性能要高于B 语句。因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。

思考：InnoDB主键索引的B+tree高度为多高呢？

假设:一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。InnoDB存储引擎最小储存单元是页，一页大小就是16k。B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；

如果B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数
假设一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.
非叶子节点内存放多少指针呢？假设主键ID为bigint类型，长度为8字节(int类型的话，一个int就是32位，4字节)，而指针大小是固定的在InnoDB源码中设置为6字节，假设n指主键个数即key的个数，n*8 + (n + 1) * 6 = 16K=16*1024B , 算出n约为 1170,意味着根节点会有1170个key与1171个指针、因此，一棵高度为2的B+树，能存放1171* 16 = 18736条这样的数据记录。

同理一棵高度为3的B+树，能存放1171 * 1171 * 16 = 21939856，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。

索引语法

创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;

查看索引

SHOW INDEX FROM table_name ;

删除索引

DROP INDEX index_name ON table_name ;

数据准备：

create table tb_user(id int primary key auto_increment comment '主键',name varchar(50) not null comment '用户名',phone varchar(11) not null comment '手机号',email varchar(100) comment '邮箱',profession varchar(11) comment '专业',age tinyint unsigned comment '年龄',gender char(1) comment '性别 , 1: 男, 2: 女',status char(1) comment '状态',createtime datetime comment '创建时间'
) comment '系统用户表';INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('吕布', '17799990000', '[e-mail]', '软件工程', 23, '1','6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('曹操', '17799990001', '[e-mail]', '通讯工程', 33,'1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('赵云', '17799990002', '[e-mail]', '英语', 34, '1','2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('孙悟空', '17799990003', '[e-mail]', '工程造价', 54,'1', '0', '2001-07-02 00:00:00');
......
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('姜子牙', '17799990023', '[e-mail]', '工程造价', 29,'1', '4', '2003-05-26 00:00:00');

name字段为姓名字段，该字段的值可能会重复(不能创建唯一索引，只能创建常规索引)，为该字段创建索引。

CREATE INDEX idx_user_name ON tb_user(name);

phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

为profession、age、status创建联合索引。

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

为email建立合适的索引来提升查询效率。

CREATE INDEX idx_email ON tb_user(email);

查看tb_user表的所有的索引数据

show index from tb_user;

SQL性能分析工具

SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

Com_delete: 删除次数，Com_insert: 插入次数，Com_select: 查询次数，Com_update: 更新次数
可以在当前数据库再执行几次查询操作，然后再次查看执行频次，看看 Com_select 参数会不会变化。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，可以查看一下系统变量 slow_query_log。

要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。

systemctl restart mysqld

再次查看开关情况，慢查询日志已经打开了

测试：执行如下SQL语句：

select * from tb_user; -- 这条SQL执行效率比较高, 执行耗时 0.00sec
select count(*) from tb_sku; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时13.35sec

在慢查询日志中，只会记录执行时间超多我们预设时间（2s）的SQL，执行较快的SQL是不会记录的。通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。

profile详情

show profiles 能够在做SQL优化时了解时间都耗费到哪里去了。
通过have_profiling参数，查看当前MySQL是否支持profile操作。

SELECT @@have_profiling ;

当前MySQL是支持 profile操作的，默认开关0是关闭的。可以通过set语句在session/global级别开启profiling：

SET profiling = 1;

开关打开之后所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了，执行如下的SQL语句：

select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_sku;

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序，是否用到索引。

基本语法:

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

Explain 执行计划中各个字段的含义:

type：NULL(查询时不访问任何表) system(访问系统表) const(根据主键和唯一索引访问) eq_ref() ref(使用非唯一性的索引访问) range() index(扫描所有索引) all(全表扫描)

执行sql:

explain select s.*,c.* from student s,course c, student_course sc where s.id=sc.studentz-id and c.id=sc.courseid;

表执行顺序：student，course，student_course

索引使用

联合索引

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

查看tb_user 表所创建的索引：

联合索引涉及到三个字段，顺序分别为：profession，age，status

最左前缀法则指的是，查询时最左变的列即profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';# 索引长度54

explain select * from tb_user where profession = '软件工程' and age = 31;# status索引长度5

explain select * from tb_user where profession = '软件工程'; 
# age索引长度2

explain select * from tb_user where age = 31 and status = '0'; 
# 最左边列不存在，索引全部失效，与存放位置没有关系

explain select * from tb_user where status = '0'; 
# 最左边列不存在，索引全部失效，与存放位置没有关系

explain select * from tb_user where profession = '软件工程' and status = '0';
# 索引跳过了某一列，之后索引都失效

• 范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0'; 
# 索引长度49，status索引失效

规避：使用>=,<=

索引失效情况

• 索引列运算：不要在索引列上进行运算操作，索引将失效

tb_user表中phone字段索引

当根据phone字段进行等值匹配查询时, 索引生效。

explain select * from tb_user where phone = '17799990015'; 

当根据phone字段进行函数运算操作之后，索引失效。

explain select * from tb_user where substring(phone,10,2) = '15';

• 字符串不加引号：字符串类型字段使用时，不加引号，索引将失效。

如果字符串不加单引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效。

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '软件工程' and age = 31 and status = 0;

• 模糊查询：在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字前面加了%，索引将会失效。

explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';

• or连接条件：如果or条件，一侧有索引，一侧没有索引，涉及到的索引都不会被用到。

由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。

explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;

对age字段建立索引，当or连接的条件，左右两侧字段都有索引时，索引才会生效。

create index idx_user_age on tb_user(age);

• 数据分布影响：如果MySQL评估使用索引比全表更慢，则不使用索引。

select * from tb_user where phone >= '17799990000';
select * from tb_user where phone >= '17799990020';

MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;

将表中的profession设为null

update tb_user set profession = null;

再次执行：

一模一样的SQL语句，先后执行了两次，结果查询计划是不一样的，为什么会出现这种现象，这是和数据库的数据分布有关系。查询时MySQL会评估，走索引快，还是全表扫描快，如果全表扫描更快，则放弃索引走全表扫描。 因此，is null 、is not null是否走索引，得具体情况具体分析，并不是固定的。

SQL提示

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

前提：字段profession，有一个单例索引idx_user_pro，一个联合索引idx_user_pro_age_sta

use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

ignore index ： 忽略指定的索引

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

force index ： 强制使用索引

explain select * from tb_user force index(idx_user_pro_age_sta) where profession = '软件工程';

覆盖索引

尽量使用覆盖索引，减少select *。 覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到

执行以下sql：

explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id, profession age,from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

前几条SQL的结果为 Using where; Using Index ; 后两条SQL的结果为: Using index condition 。

在tb_user表中有一个联合索引 idx_user_pro_age_sta，该索引关联了三个字段profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主键id。 所以当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引直接返回数据了。 如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。 而我们如果一直使用select* 查询返回所有字段值，很容易就会造成回表查询（除非是根据主键查询，此时只会扫描聚集索引）。

思考：
一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案:select id,username,password from tb_user where username = ‘itcast’;

答案:
针对于 username, password建立联合索引, sql为: create index idx_user_name_pass on tb_user(username,password);可以避免上述SQL语句在查询的过程中，出现回表查询。

前缀索引

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

基本语法：

create index idx_xxxx on table_name(column(n)) ;

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

select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

示例：为tb_user表的email字段，建立长度为5的前缀索引。

create index idx_email_5 on tb_user(email(5));

前缀索引的查询流程：

具体过程如下:

• 由于是根据email字段进行查询，所以先根据email字段的二级索引中进行匹配查找。daqia—>lvbu6,在二级索引中只能查找到 lvbu6对应的主键值 1。
• 由于查询返回的数据是*(所有返回字段)，所以还需要根据主键值1，到聚集索引中查找1对应的记录，7—>3—>1最终找到1对应的行row。
• 最终拿到这一行的数据，对比email的值是不是传递的值，是查询并返回，之后再查询当前下一个节点的元素。

单列索引与联合索引

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

select id,name,phone from tb_user where phone = "177999990010" and name = "韩信";

在and连接的两个字段 phone、name上都是有单列索引的，但是最终mysql只会选择一个索引，也就是说，只能走一个字段的索引，此时是会回表查询的。

创建phone和name字段的联合索引

create unique index idx_user_phone_name on tb_user(phone,name);

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

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

索引设计原则

• 针对于数据量较大，且查询比较频繁的表建立索引。
• 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。(性别区分度不大)
• 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

SQL优化

插入数据

需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化

insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
.....

优化方案一：批量插入数据500-1000条

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

优化方案二：手动控制事务(默认一条insert语句，自动提交数据)

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

优化方案三：主键顺序插入，性能要高于乱序插入。

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

大批量数据插入

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，可以使用MySQL数据库提供的load指令进行插入。操作如下：

将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–local-infile需要加载本地文件
mysql –-local-infile -u root -p

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

-- 执行load指令将准备好的数据，加载到表结构中每个数据用,分割，每一行数据用/n分割
load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;

在load时，主键顺序插入性能高于乱序插入

主键优化

数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。

行数据，都是存储在聚集索引的叶子节点上的。在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行溢出)，根据主键排列。

主键顺序插入效果

从磁盘中申请页， 主键顺序插入， 第一个页没有满，继续往第一页插入，当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接，当第二页写满了，再往第三页写入。

主键乱序插入效果

加入1#,2#页都已经写满了，存放了如图所示的数据，此时再插入id为50的记录，因为，索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后。

但是47所在的1#页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。将1#页后一半的数据，移动到3#页，然后在3#页，插入50。此时这三个页之间的数据顺序是有问题的

重新设置链表指针：1#的下一个页是3#， 3#的下一个页是2#。

上述的这种现象，称之为 “页分裂”，是比较耗费性能的操作。

删除记录：

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前
或后）看看是否可以将两个页合并以优化空间使用。

删除数据，并将页合并之后，再次插入新的数据21，则直接插入3#页

这个里面所发生的合并页的这个现象，就称之为 “页合并”。

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键设计原则

• 满足业务需求的情况下，尽量降低主键的长度。(二级索引占用空间大)
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
• 业务操作时，避免对主键的修改。

order by优化

MySQL的排序，有两种方式：

• Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
• Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

Using index的性能高，而Using filesort的性能低，在优化排序操作时，尽量要优化为 Using index。

执行age,phone排序SQL：

explain select id,age,phone from tb_user order by age,phone; 
#都没有索引

创建age,phone索引

create index idx_user_age_phone_aa on tb_user(age,phone);

执行一下排序查询：
单独对age升序排：

explain select id,age,phone from tb_user order by age;
# 使用了索引

对age,phone 升序排：

explain select id,age,phone from tb_user order by age, phone ;
# 使用了索引

根据age, phone进行升序排序，就由原来的Using filesort， 变为了 Using index，性能就是比较高的了。

根据age, phone进行降序排序：

explain select id,age,phone from tb_user order by age desc , phone desc ;
# 使用了索引并反向扫描

也出现了 Using index， 但是Extra中出现了 Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。 在MySQL8版本中，支持降序索引，也可以创建降序索引。

对phone,age 升序排：

explain select id,age,phone from tb_user order by phone , age;
# 在索引之外，需要额外进行外部的排序动作

根据phone，age进行升序排序，phone在前，age在后，违背了最左匹配法则，索引失效

根据age升序排, phone降序排：

explain select id,age,phone from tb_user order by age asc,phone desc;
# 在索引之外，需要额外进行外部的排序动作

因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现Using filesort。

创建联合索引(age 升序排序，phone 倒序排序)

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

再次执行如下SQL

explain select id,age,phone from tb_user order by age asc , phone desc ;
# 使用了索引

• 如果根据age和phone创建了联合索引，默认都是升序，当age和phone升序排序，age单字段升序，age和phone降序排序都会走联合索引。其他情况会索引失效。
• 一个升序，一个降序的情况，需要重新建立索引。

order by优化原则:

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
• 尽量使用覆盖索引，不用再回表查询，避免索引排序失效。
• 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)，超过磁盘缓冲区大小，会在磁盘排序，性能低。

group by优化

将 tb_user 表的索引全部删除掉，在没有索引的情况下，执行如下SQL，查询执行计划：

explain select profession , count(*) from tb_user group by profession ;#用到了缓冲区，效率较低

针对于 profession ， age， status 创建一个联合索引

create index idx_user_pro_age_sta on tb_user(profession , age , status);

再执行前面相同的SQL查看执行计划

explain select profession , count(*) from tb_user group by profession ;
# 使用了索引

根据age分组：

explain select profession , count(*) from tb_user group by age ;
#不满足最左前缀法则

根据profession，age分组：

explain select profession , age,count(*) from tb_user group by profession,age ;
# 使用了索引

条件中有profession满足最左前缀法则

explain select age,count(*) from tb_user where profession = "软件工程"group by profession,age ;

通过以下两点进行优化，以提升性能：

• 在分组操作时，可以通过索引来提高效率。
• 分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

count优化

select count(*) from tb_user ;

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。
• InnoDB 引擎执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

如果要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦)。

count的用法：
count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。

• count（主键）InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)。
• count（字段）
  • 没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，判断是否为null，不为null，计数累加。
  • 有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
• count（数字）InnoDB引擎遍历整张表，但不取值，服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加
• count（*）InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)

update优化

update course set name = 'javaEE' where id = 1 ;

在执行更新的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放

update course set name = 'SpringBoot' where name = 'PHP' ; 

当开启多个事务，在执行上述的SQL时，会发现行锁升级为了表锁。 导致该update语句的性能大大降低。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ，更新的条件必须有索引，并且该索引不能失效，否则会从行锁升级为表锁 。