MySQL索引

数据库
数据库
发布日期:   2022-02-10

一、索引介绍

1、简介

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

可以得到索引的本质：索引是数据结构。

你可以简单理解为“排好序的快速查找数据结构”。

2、优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

3、缺点

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，

都会调整因为更新所带来的键值变化后的索引信息。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

二、索引结构

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，

这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

img

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

二叉树弊端之一：二叉树很可能会发生两边不平衡的情况。

B-TREE: (B:balance) 会自动根据两边的情况自动调节，使两端无限趋近于平衡状态。可以使性能最稳定。(myisam使用的方式)

B-TREE弊端：(插入/修改操作多时，B-TREE会不断调整平衡，消耗性能)从侧面说明了索引不是越多越好。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，

复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index，不支持范围查询)等。

image-20220206162231345

我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

1、Myisam普通索引（BTree）

img

【初始化介绍】

一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），

如磁盘块1包含数据项17和35，包含指针P1、P2、P3，

P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。

非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

【查找过程】

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

2、innodb的普通索引（B+Tree）

img

B+TREE 第二级的 数据并不能直接取出来，只作索引使用。在内存有限的情况下，查询效率高于 B-TREE

B-TREE 第二级可以直接取出来，树形结构比较重，在内存无限大的时候有优势。

  • 所有数据都会出现在叶子节点
  • 叶子节点形成单向链表（普通B+树），双向链表（mysql 中的 B+树）

3、B树和B+树的区别

B+Tree与B-Tree 的区别：结论在内存有限的情况下，B+TREE 永远比 B-TREE好。无限内存则后者方便

1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。(一次查询可能进行两次i/o操作)

2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。

思考：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

  1. B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

  1. B+树的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

4、hash索引

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

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

image-20220206164620338

Hash索引特点

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

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

5、为什么三层B+树能存储2或4千万数据

在Innodb存储引擎里面，最小存储单元是页，而一个页的大小默认是16KB。

也即代表B+树的每个节点可以存16KB数据，这里我们假设我们的一行数据大小是1K，那么我们一个节点就可以存16行数据。注意：我们真正的数据都是存在叶子节点的，所以这里是指叶子节点可以存放16行数据。

非叶子节点存放的是主键值与指针，所以这里假设主键类型为bigint，占用8Byte，指针可以设置为占用6Byte，总共就为14Byte，这样就可以算出一个节点大概可以存放多少个指针了（指针指向下一层节点），大概为 16KB/14Byte=1170 个。

由此，可以推算出，2层B+树的话，可以存放 1170*16=18720 行数据。3层B+树的话，可以存放 1170*1170*16=21902400 行数据，也就差不多2000w条数据了。

如果主键类型为 int ，那么只占 4 个字节，每个子节点存储的数据条数就是 16KB/10Byte=1600个，3层B+树可存 1600*1600*16=4096w

image-20220206213230038

6、深分页为什么慢，如何优化

如果 t_user 表有一亿行数据

select * from t_user where age > 1 limit 50000000,10

（1）执行流程分析

执行该sql时，首先会经过 MySQL 的 server 层：

  • 语法解析：解析sql语法是否正确
  • 预处理：表和字段是否存在
  • 优化sql

然后会来到引擎层查找数据，此时不会直接定位到第五千万条数据，而是从0开始通过io加载到内存，直到 5,000,010

然后因为没有覆盖索引，所以这 5,000,010 条数据都会进行回表查询

（2）优化

优化步骤1

select * from t_user t
left join (select id from t_user where age > 1 limit 50000000,10) temp on t.id = temp.id

当sql修改为以上时，子查询采用覆盖索引，避免了5,000,010都回表，最终只需要回表10条数据即可

此时还存在一个问题，那就是还需要将5,000,010条数据都加载到内存

优化步骤2

select * from t_user
left join (select id from t_user where age > 1 and id > 上一页maxid limit 50000000,10)  temp on t.id = temp.id

此时子查询也不会进行回表了

但此时也有缺点：

  • 主键必须自增
  • 分页查询必须连续页查，不允许跳页查询，因为需要上一个最大id

最后

业务允许的情况下尽量限制深分页，比如淘宝，只限制查询100页的内容

三、聚簇索引

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

分类 含义 特点
聚集索引(Clustered Index) 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 必须有,而且只有一个
二级索引(Secondary Index) 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个

聚集索引选取规则:

➢如果存在主键，主键索引就是聚集索引。

➢ 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

➢ 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。（每张表有且只有一个）

术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。

聚簇索引的好处：

  • 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

聚簇索引的限制：

  • 对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。

  • 由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。

  • 为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。（参考聚簇索引的好处。）

    这里说明了主键索引为何采用自增的方式：1、业务需求，有序。2、能使用到聚簇索引

image-20220206211642859

回表查询

以上图的表为例，当执行 select * from t_user where name = 'lee'; 时，因为 name 字段为二级索引，所以会先在下面的 B+ 树中查询，获得 id = 80，在通过 id 到聚簇索引中查询到行数据。这个过程叫做回表查询。

四、索引的分类

分类 含义 特点 关键字
主键索引 针对于表中主键创建的索引 默认自动创建, 只能有一个 PRIMARY
唯一索引 避免同一个表中某数据列中的值重复 可以有多个 UNIQUE
常规索引 快速定位特定数据 可以有多个
全文索引 全文索引查找的是文本中的关键词，而不是比较索引中的值 可以有多个 FULLTEXT

索引又可分为单列索引和复合索引

1、主键索引

设定为主键后数据库会自动建立索引（只能有一个字段），innodb为聚簇索引

#随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
      PRIMARY KEY(id) 
);
#unsigned (无符号的)
#使用  AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。


CREATE TABLE customer2 (
    id INT(10) UNSIGNED,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
      PRIMARY KEY(id) 
);

#单独建主键索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);  

#删除建主键索引：
ALTER TABLE customer drop PRIMARY KEY ;  

#修改建主键索引：
#必须先删除掉(drop)原索引，再新建(add)索引

2、唯一索引

索引列的值必须唯一（当为字段添加唯一约束时，会自动添加唯一索引），但允许有空值

#随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);
#建立 唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。  

#单独建唯一索引：
CREATE UNIQUEINDEX idx_customer_no ON customer(customer_no); 

#删除索引：
DROP INDEX idx_customer_no on customer ;

3、普通索引

#随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);

4、全文索引

在当我们进行模糊查询的时候，一般都是使用 like 关键字来实现，在数据量小的情况下效率确实可以。有的人会说了，数据量大的时候我加索引啊。确实可以加索引，而且 like name% 是可以使用到索引的，但是当你要 like %name% 这样使用就会导致索引无效。在某些情况下就造成了局限性。

在数据量大的情况下，全文索引的效率会比like高，在MySql5.6版本以前，只有MyISAM索引支持全文索引，在这之后Innodb也支持。

5、其他分类方式

（1）、单列索引

即一个索引只包含单个列，一个表可以有多个单列索引

索引建立成哪种索引类型？

根据数据引擎类型自动选择的索引类型

除开 innodb 引擎主键默认为聚簇索引 外。 innodb 的索引都采用的 B+TREE

myisam 则都采用的 B-TREE索引

#随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
      PRIMARY KEY(id),
      KEY (customer_name)  
);
#随表一起建立的索引 索引名同 列名(customer_name)

#单独建单值索引：
CREATE  INDEX idx_customer_name ON customer(customer_name); 

#删除索引：
DROP INDEX idx_customer_name ;

（2）、复合索引

即一个索引包含多个列

在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)

当表的行数远大于索引列的数目时可以使用复合索引

#随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);

#单独建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 

#删除索引：
DROP INDEX idx_no_name  on customer ;

6、基本语法

创建语句

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name(index_col_name,...);
#创建
ALTER mytable ADD  [UNIQUE]  INDEX [indexName] ON (columnname(length)) 

#有四种方式来添加数据表的索引：
#（1）该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);

#（2）这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);

#（3）添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);

#（4）该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

删除语句

DROP INDEX [indexName] ON mytable;

查看语句

SHOW INDEX FROM table_name

五、索引创建场景

1、需要创建索引的场景

（1）主键自动建立唯一索引

（2）频繁作为查询条件的字段应该创建索引(where 后面的语句)

（3）查询中与其它表关联的字段，外键关系建立索引

A 表关联 B 表：A join B  。
on 后面的连接条件 既 A 表查询 B 表的条件。所以 B 表被关联的字段建立索引能大大提高查询效率
因为在 join 中，join 左边的表会用每一个字段去遍历 B 表的所有的关联数据，相当于一个查询操作

（4）单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)

（5）查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

（6）查询中统计或者分组字段

2、不需要创建索引的场景

（1）表记录太少

（2）经常增删改的表

Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

（3）Where条件里用不到的字段不创建索引，索引建多了影响 增删改 的效率

（4）数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。

注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。如：性别

六、SQL性能分析

1、SQL执行频率

可通过以下命令查看数据库的增删改查语句的执行频率。（Com后面有七个下划线）

SHOW [session|global] status like 'Com_______';
  • session：查询范围为本次会话
  • global：查询范围为全局

执行结果如下：

image-20220207165822601

2、慢查询日志

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

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

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

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 。

/var/lib/mysql/localhost-slow.log

image-20220207175342082

3、profile详情

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

select @@have_profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：

SET profiling=1;

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

# 查看每一条sql的耗时情况
show profiles;
# 查看指定的 query_id (从上一条sql的结果中获取)的 SQL 语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定 query_id 的SQL语句的CPU占用情况
show profile cpu for query query_id;

image-20220208110923866

4、explain执行计划

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

语法：直接在 select 查询语句前加上 explain / desc 关键字

EXPLAIN 执行计划各字段含义：

Id

select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。

select_type

表示 SELECT 的类型，常见的取值有

  • SIMPLE（简单表，即不使用表连接或者子查询）

  • PRIMARY（主查询，即外层的查询，最外面的select,在有子查询的语句中，最外面的select查询就是primary）

  • UNION（UNION 中的第二个或者后面的查询语句）、

SUBQUERY（SELECT/WHERE之后包含了子查询）等

type（重点关注）

表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。

  • ALL 全表（性能最差）
  • index 基于索引的全表，只遍历索引树就能获取数据，不需要回表
  • range 范围 （< > in）
  • ref 非唯一索引单值查询
  • eq_ref 联表查询的情况，按联表的主键或唯一键联合查询。
  • const 使用主键或者唯一索引等值查询，结果只有一行数据

possible_key（重点关注）

显示可能应用在这张表上的索引，一个或多个。

Key（重点关注）

实际使用的索引，如果为NULL，则没有使用索引。

Key_len（重点关注）

表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。 key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

rows（重点关注）

MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

预估扫描多少行记录

filtered

表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

Extra：额外信息

    • Using where 索引回表
    • Using index 索引直接满足条件
    • Using filesort 需要排序
    • Using temprorary 使用到临时表

注意：

  • index全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：

    • 如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra 列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据。
    • 按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index。

七、索引的使用

1、验证索引效率

在未建立索引之前，执行SQL语句，查看SQL的耗时。（使用profile详情）

针对执行sql的条件字段建立索引

然后再次执行相同的SQL语句，再次查看SQL的耗时。

2、索引失效情况

（1）最左前缀原则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

  • 在查询语句的条件中不必按顺序（均为 and 的情况）

（2）范围查询

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

  • 业务允许的情况下，用大于等于和小于等于替代大于和小于，（>=、<= 不会导致索引失效）
  • in、exists、between 走索引
  • not in ：主键列走索引，非主键列不走索引
  • not exists：不走索引

这里需要说明的是使用>，<， in 以及 not in 走不走索引，实际和 Mysql 的版本以及表中的数据量有关系，在 8.0 之后的版本是走索引的。当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。

（3）or 连接条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

必须是 or 前后的条件列中都含有索引，涉及的索引才会被使用。

（4）数据分布影响

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

-- 因为 age 字段的值都是大于等于1的，所以该语句会被评估为使用索引更慢，从而不走索引
select * from user where age >= '1';

-- 该语句就会走索引
select * from user where age >= '18';

is null 和 is not null 走不走索引

前置条件 user 表的 name 字段大部分不为 null ，remark 字段大部分为null

-- 走索引
select * from user where name is null;
-- 不走索引
select * from user where name is not null;

-- 不走索引
select * from user where remark is null;
-- 走索引
select * from user where remark is not null;

（5）表编码不一致

当下面两张表的编码不一致时，不会使用索引

explain select * 
from cs_goods g 
left join cs_order o on g.id = o.goods_id
limit 10

可通过下面语句查询表的字段类型以及编码

-- 查询所有字段类型及编码
-- SHOW FULL COLUMNS FROM 表名;
SHOW FULL COLUMNS FROM cs_goods;
SHOW FULL COLUMNS FROM cs_order;

-- 查询指定字段类型及编码
-- SHOW FULL COLUMNS FROM 表名 where field = '字段名';
SHOW FULL COLUMNS FROM cs_order where field = 'id';

（6）数据类型不一致

cs_order 表的 goods_id 为 varchar 类型时，下面的查询方式不会使用索引，因为类型不一致

select * 
from cs_order 
where goods_id = 1

只需使用字符串作为条件，或将其转化为字符串即可

-- 字符串作为条件
select * 
from cs_order 
where goods_id = 1;

-- 转化为字符串
explain select * 
from cs_order 
where goods_id = CAST(1 AS CHAR);

案例二

当 cs_goods 表的 id 为 bigint 类型，而 cs_order 表的 goods_id 为 varchar 类型时，字段类型不一致，不会使用索引

explain select * 
from cs_goods g 
left join cs_order o on g.id = o.goods_id
limit 10

需要转化为字符串

explain select * 
from cs_goods g 
left join cs_order o on CAST(g.id AS CHAR) = o.goods_id
limit 10

（7）带有运算

（8）使用函数

（9）左模糊

使用%XXX左模糊查询，因为mysql是最左原则，使用XXX%右模糊查询是可以使用索引的，但是左模糊违背了最左原则所以不行

3、sql 提示

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

场景：当有语句中多个可用索引同时存在时（单列/多列），可手动指定用哪个索引

-- use index 建议mysql使用指定的索引,不一定使用
explain select * from tb_user use index(idx_user_pro) where profrssion='软件工程';

-- ignore index 不使用指定的索引
explain select * from tb_user ignore index(idx_user_pro) where profrssion='软件工程';

-- force index 强制使用指定索引
explain select * from tb_user force index(idx_user_pro) where profrssion='软件工程';

4、覆盖索引

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

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

案例

user 表 name 字段有索引

select id,name from user where name = 'lisi';

上面的 sql 语句不会出现回表查询，即在辅助索引的 B+ 树中就能获取需要的数据（id，name），无需到聚簇索引中回表查询。

select id,name,age from user where name = 'lisi';

上面的 sql 语句需要回表查询，因为 age 字段在辅助索引 name 的 B+ 树中不存在，所以必须到聚簇索引中在查一次

两个sql 语句对比很明显第一句不要回表查询，性能相对较高

可通过 explain 执行计划的 extra 查看到相关信息

  • using index condition ：查找使用了索引，但是需要回表查询数据

  • using where; using index ：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

5、索引下推

索引下推就是指在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数来提高查询效率。

索引下推是一种优化技术，它允许数据库在执行查询时尽早使用索引来筛选和过滤数据，而不是先获取所有数据再筛选。

假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。那么，查询语句是这么写的：

select * from tuser where name like '张%' and age=10 and ismale=1;

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对联合索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

image-20240307171307943

image-20240307171320564

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

总结

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

6、前缀索引

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

语法

create index idx_xxx on table_name(column(n))

n 为前缀索引的长度

如何决定前缀索引的长度

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

如何决定长度：

-- 获取字段的唯一性
select count(distinct email)/count(*) from user;

-- 获取字段指定前缀长度的唯一性，这里为5
select count(distinct substring(email,1,5))/count(*) from user;

通过现有的数据反复尝试第二条 sql，逐渐增加或减少前缀长度，找到一个选择性和字段长度之间的最优解

7、单列索引、联合索引

单列索引：即一个索引只包含单个列。

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

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

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

联合索引的结构

image-20220213191113645

8、索引设计原则

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

八、SQL优化

1、插入数据

insert into t_user (name,age) values ('lisi',18);

当需要批量执行以上sql语句时，可以进行以下优化

（1）批量插入

insert into t_user (user_name,user_age) values ('lisi',18),('wangwu',19),('zhaoliu',20);

但是不建议一条 insert 语句插入超过 1000 条数，如果要插入上万条数据的话，可以拆分为多条 insert 语句

（2）手动事务提交

mysql 中的事务默认是自动提交的，当我们执行完一条插入语句时，会自动提交事务

start TRANSACTION;

insert into t_user (user_name,user_age) values ('lisi',18),('wangwu',19),('zhaoliu',20);

insert into t_user (user_name,user_age) values ('lisi',18),('wangwu',19),('zhaoliu',20);

...

commit;

（3）主键顺序插入

（4）大批量插入

当插入的数据达到 100 万即以上时

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -uroot -p

# 查询本地加载文件开关是否开启
select @@local_infile;

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

# 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';

load指令（最后一条命令）详解：

load data local infile '文件完整路径' into table `要插入到哪张表` fields terminated by '字段之间的分隔符' lines terminated by '多条数据之间的分隔符';

/root/sql.log 的文件内容如下：

1,张三,2022-2-14
2,李四,2022-2-15
3,王五,2022-2-16

主键顺序插入性能高于乱序插入

navicat 可在右击指定表的导入向导选项中进行导入

2、主键优化

（1）页分裂

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

image-20220214205932799

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

插入数据时未发生页分裂

image-20220214210139585

9 为乱序插入，可能发生页分裂

14 为顺序插入，不会发生页分裂

插入数据时，发生页分裂

image-20220214210440016 image-20220214211159220

当插入数据时，发生页分裂较为消耗性能

（2）页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

image-20220214211755162

删除13、14、15、16四条数据时，发生页合并

image-20220214211714038

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

（3）主键设计原则

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

3、order by 优化

order by 可以分为2种方式：

  • Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫filesort排序。

  • Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 Using index，不需要额外排序，操作效率高。

多字段顺序排序

explain select id,age,phone from tb_user order by age,phone

如果不创建 age 和 phone 的联合索引时，explain 的 Extra 显示 Using filesort，未使用索引

create index idx_user_age_phone on tb_user(age,phone);

如果创建 age 和 phone 的联合索引时，explain 的 Extra 显示Using index，使用索引

多字段倒序排序

explain select id,age,phone from tb_user order by age desc,phone desc

Extra 显示Backward index scan;Using index，使用反向扫描索引

排序字段顺序和索引不同

explain select id,age,phone from tb_user order by phone,age

Extar 显示 Using index;Using filesort ，phone 使用了索引，而 age 排序没有用索引，最左前缀法则

一个字段顺序，一个倒序

explain select id,age,phone from tb_user order by age asc,phone desc

Extar 显示 Using index;Using filesort ，此时 age 使用了索引，而phone 没有。

此时可通过创建对应升降序规则的索引，从而避免 Using filesort，创建索引默认是 asc

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

此时在执行上面语句时，不在出现 Using filesort

索引倒序时 B+ 树结构

image-20220216141931717

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

  • 尽量使用覆盖索引。

  • 多字段排序 一个升序一个降序，此时需要注意联合索引在创建时的规则（ ASC / DESC）。

  • 如果不可避免的出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort buffer size 默认 256K。

4、group by 优化

在分组操作时，可以通过索引来提高效率。

分组操作时，索引的使用也是满足最左前缀法则的。

5、limit 优化

一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

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

优化前（19秒）

select * from t_user limit 2000000,10;

优化后（11秒）

select t1.* from t_user t1,(
    select id from t_user order by id limit 2000000,10
) t2
where t1.id = t2.id

其实 select id from t_user order by id limit 2000000,10 ，也差不多11秒

6、count 优化

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；

InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

优化思路：自己计数

用 redis ，数据库存一条数据，redis 计数加一，删一条，redis 减一

count的几种用法

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

用法：count（*）、count（主键）、count（字段）、count（1）

  • count（主键）

InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null) 。

  • count（字段）

没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加 。 （不计数 null ）

有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

  • count（1）

InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。

  • *count（）**

    InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用count(*)。

7、update 优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。

案例：执行以下语句时，加的锁是表锁。因为 name 字段不是索引字段

update t_user set age = 11 where name = '张三';

执行下面语句时，加的是行锁。因为 id 字段是索引字段

update t_user set age = 11 where id = '100';

所以更新尽量用索引字段进行更新

数据库
  目录