MySQL - 索引

作者: SheHuan | 来源:发表于2022-08-10 22:07 被阅读0次

MySQL 体系结构

连接层：最上层是一些客户端和连接服务，主要进行连接处理、授权认证、以及相关的安全方案等，服务器也会对安全接入的每个客户端验证其所具有的操作权限
服务层：第二层结构主要完成大部分核心服务功能，比如SQL接口、SQL的分析优化、缓存的查询、部分内置函数的执行，所有夸存储引擎的功能也在这一层实现
引擎层：存储引擎真正负责了MySQL中数据的存储和提取，服务器通过API和存储引擎通信，
存储层：主要是将数据存储在文件系统之上，并完成和存储引擎的交互

存储引擎

存储引擎就是存储数据、建立索引、更新、查询数据等技术的实现方式，存储引擎是基于表的。MySQL5.5版本开始默认的存储引擎是InnoDB，之前版本是MyISAM。

建表时指定存储引擎：create table 表名(......) engine = InnoDB;
查看数据库支持的存储引擎：show engines;

engines
InnoDB：
1、一种兼顾高可靠性和高性能的存储引擎；MySQL5.5版本开始默认的存储引擎。
2、支持事务，DML 操作遵循事务的 ACID 模型；支持行级锁，提高了并发访问性；支持外键约束，保证了数据的完整性和正确性。
3、InnoDB 引擎的每张表都对应一个表名.ibd的表空间文件，用来存储表的结构（sdi）、数据、索引。
4、innodb_file_per_table，该参数值默认为ON，会给每张表创建一个单独的表空间文件。可以查看该参数的值：show variables like 'innodb_file_per_table';
5、从 ibd 文件中提取表结构，在命令行窗口执行：ibd2sdi 表名.ibd
6、InnoDB 的逻辑存储结构，按照从大到小的包含关系为：Tablespace（表空间）、Segment（段）、Extent（区，大小1M）、Page（页，大小16k）、Row（行，表中的每一行数据）。
7、如果需要支持事务、外键，对增删改查都有比较多的需求，对事务完整性要求高，在高并发下要求事务的一致性，可以选择该引擎。
MyISAM
1、MySQL5.5版本之前默认存储引擎。
2、不支持事务，不支持外键。
3、支持表锁，不支持行锁。
4、访问速度快。
5、每张表对应三个文件表名.sdi（存储表结构信息）、表名.MYD（存储数据）、表名.MYI（存储索引）。
6、如果是以读和插入操作为主，只有很少的更新和删除操作，并对事务完整性和并发要求不高可以使用这个存储引擎。
7、可以用 MongoDB 替代。
Memory
1、表数据存储在内存中，访问速度快，但不能存储太大的表，由于会受到硬件故障、断电问题的响应，只能将这些表作为临时表或者缓存使用。
2、支持 hash 索引，支持表锁。
3、每张表对应一个文件表名.sdi（存储表结构信息）。
4、可以用 Redis 替代

索引

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

优缺点

1、优点：提高数据检索效率，降低数据库的 IO 成本；通过索引列对数据进行排序，降低数据排序成本，降低 CPU 消耗。
2、缺点：索引也要占磁盘空间，虽然大大提高了数据检索效率，但也会降低表中数据的更新效率（Insert、Update、Delete），因为此时需要维护索引结构。

索引实现方式

B+tree索引
1、默认的索引类型，基于 B+tree 实现。该网址可以模拟 B+tree 的构建过程：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
2、MySQL 索引数据结构对经典的 B+tree 进行了优化，在原基础上，增加了一个指向相邻叶子节点的链表指针，即最下边一层的叶子节点变成了双向循环链表，提高了区间访问的性能。
3、相对于二叉树，层级更少，搜索效率更高。
4、在 B-tree 中，无论叶子节点还是非叶子节点都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要保存和 B+tree 同样的数据只能增加树的高度，导致性能降低。
5、InnoDB、MyISAM、Memory 支持。
Hash索引
1、采用一定的 hash 算法，将键值换算成 hash 值，映射到对应的槽位上，然后存储到 hash 表，如果两个或多个键值映射到同一个槽位上，则会产生 hash 冲突，此时通过链表将 hash 值串起来。
2、hash 索引只支持对等比较（=、in），不支持范围查询（between、>、<...）。
3、无法利用索引进行排序操作，但查询效率一般高于 B+Tree，通常只需要一次检索。
4、Memory、InnoDB 支持。
R-tree（空间索引）：MyISAM 中的一个特殊索引，主要用于地理空间数据类型，使用较少。
Full-text（全文索引）：一种通过建立倒排索引实现快速查询的方式，类似于 Elasticsearch。（InnoDB5.6+、MyISAM）

索引分类

主键索引：primary，针对表中主键创建的索引，每张表默认只有一个。
唯一索引：unique，避免表中某一列的值重复。
常规索引：快速定位特定数据。
全文索引：fulltext，查找文本中的关键字，而不是进行比较。
在 InnoDB 中，根据索引的存储形式，索引又分为以下两种：
1、聚集索引（Clustered Index）：行将数据与索引存储到一起，索引结构的叶子节点保存了行数据以及索引字段的值。每张表必须有，且只能有一个。
2、二级索引（Secondary Index）：行将数据与索引分开存储，索引结构的叶子节点保存了行数据的主键的值以及索引字段的值。使用二级索引找到行数据的主键后，再用主键去聚集索引中查找对应的行数据（回表查询）。每张表可以有多个。
3、如果存在主键，主键索引就是聚集索引；如果不存在主键，则将第一个唯一索引作为聚集索引；如果没有主键索引或者唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。其它的索引就是二级索引。

索引语法

创建索引：create [unique | fulltext] index index_name on table_name (col_name,...);索引名称一般为idx_表名_字段名，不指定索引类型就是常规索引，col_name指定多个则是创建联合索引。默认索引按照字段值升序排列（asc），也可以在创建索引时显示指定......(col_name1 asc, col_name2 desc)
查看索引：show index from table_name;
删除索引：drop index index_name on table_name;

SQL 性能分析

SQL 执行次数
客户端连接到服务器后，可查看CRUD等操作的执行次数show global status like 'Com_______';
慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL 语句日志。通过如下方式开启慢查询日志：

#启用慢查询日志
slow_query_log = 1
#指定慢查询日志文件的路径和名字
slow_query_log_file = slow.log
#SQL语句运行时间超过该值才会被记录
long_query_time = 10

profile
1、查看 MySQL 是否支持 profile 操作：select @@have_profiling;
2、查看 profile 是否打开：select @@profiling;
3、打开 profile：set profiling = 1;
4、执行一系列的业务 SQL 后，可以查看每一条 SQL 的耗时的情况show profiles;

查看指定query_id对应 SQL 各个阶段的耗时情况show profile for query 5;
如果需要查看 cpu 使用情况可以使用show profile cpu for query 5;
explain
explain 或 desc 命令获取 MySQL 如何执行 select 语句的信息，包括在 select 语句执行过程中如何连接表和连接的顺序。可直接在查询语句前添加explain：

结果中各个字段的含义：
1、id：表示查询中执行 select 字句或者操作表的顺序，id 相同时执行顺序从上到下，id 不同时值越大越先执行。
2、select type：表示当前查询语句的类型（simple：不使用表连接或子查询；primary：主查询，即外层的查询；union：union中第二个或者后面的查询；subquery：select/where后边的子查询）
3、type：连接的类型，性能由好到坏依次为：null（不查询表，直接查询指定值）、system（只有一条数据的系统表）、const（查询索引字段，并且最多只有一行匹配）、eq_ref（主键、唯一索引）、ref（非唯一索引）、range（索引的范围查询）、index（查询索引中全部的数据）、all（查询没有索引的列，全表扫描）。
4、possible_keys：查询时可能用到的索引
5、key：实际用到的索引，没有使用索引则为 null
6、key_len：索引长度，在不损失精度的情况下，越短越好
7、ref：表示索引的哪一列被使用了，如果可能的话，是一个常数
8、rows：MySQL 认为需要执行查询的行数（预估值）
9、filtered：查询返回的行数占读取行数的百分比，值越大越好

索引的使用

最左前缀法则：如果使用了联合索引，则会遵循最左前缀法则。最左前缀法则指的是查询从索引的最左边的列开始，如果查询时指定了全部索引列（和书写顺序无关），则全部索引生效。如果跳过某一列，则从该列后边列的索引都会失效（由联合索引顺序决定）。
比如有联合索引：idx_user_pro_age_sta，则如下查询联合索引都会生效：select * from user where profession = '会计' and age = 30 and status = 0;如下查询status = 0查询条件不会走索引：select * from user where profession = '会计' and status = 0;
范围查询：联合索引中，如果出现范围查询（>、<），则范围查询右侧查询的列的索引失效，这种情况尽量使用 >=、<= 来避免问题。
比如有联合索引：idx_user_pro_sta，则如下查询中status = 0查询条件不会走索引：select * from user where profession = '会计' and age > 30 and status = 0;
索引列运算：不要在索引列上进行运算操作，否则索引会失效。
字符串不加引号：如果索引列是字符串类型，查询时指定的值不加引号，则索引会失效。
模糊查询：如果在索引列尾部进行模糊查询（like 'xxx%'），索引不会失效，如果在头部则失效（like '%xxx'、like '%xxx%'）。
or 连接的条件：用or连接的条件，如果or前边条件的列有索引，而后边的列没有索引，那么涉及的索引不会生效。只有两边列都有索引才会生效。
数据分布影响：如果 MySQL 评估使用索引比全表扫描更慢，则不使用索引。
SQL 提示：一个字段既有联合索引又有单独的索引，默认使用联合索引。可以使用 SQL 提示来优化数据库操作，告诉它使用那个索引：
1、user index，建议使用哪个索引
2、force index，强制使用哪个索引
3、ignore index，忽略哪个索引
select * from tb_user use index(idx_user_name) where name = 'zhangsan';
覆盖索引：尽量使用覆盖索引，即查询使用了索引列，并且需要返回的列包含在查询条件的列中，尽量减少使用select *，因为容易产生回表查询。
前缀索引：当字段类型为字符串（varchar、text）时，如果直接给字段建立索引，会导致建立的索引很大，浪费磁盘IO，影响查询效率，此次可以对字符串的一部分前缀建立索引，来节省空间，提高查询效率。
1、create index index_name on table_name (col_name(n)); n表示对前多少个字符建立索引，即前缀长度。
2、前缀长度可以根据索引的选择性来决定，选择性是指不重复的索引值和表中记录数的比值，选择性越高查询效率越高，选择性最大为1。可以使用如下方式计算选择性，进而确定前缀的长度（n）：select count(distinct substring(col_name, 1, n)) / count(*) from table_name;
单列索引与联合索引：如果有多个查询条件，考虑对查询字段建立索引时，建议创建联合索引，而非单列索引，这样可以一定程度避免回表查询。

索引设计原则

针对数据量较大，且查询比较频繁的表建立索引。
针对经常作为查询条件、排序、分组的字段建立索引。
尽量选择区分度高的字段作为索引，尽量建立唯一索引，区分度越高使用索引的效率越高。
如果是字符串类型字段，而且比较长，可以考虑建立前缀索引。
尽量使用联合索引，减少单列索引，联合索引很多时候可以覆盖索引、节省存储空间、避免回表、提高查询效率。
要控制索引数量，索引并不是越多越好，维护索引也需要耗费资源，也会影响增删改的效率。
如果索引列不能存储null值，请在建表时使用not null约束它；当优化器知道每列是否包含null值时，它可以更好的确定那个索引的查询效率高。

SQL 优化

数据插入

insert 优化：批量插入（每次最多500-1000条）、手动提交事务（避免频繁的开启、提交事务）、按照主键顺序插入
大批量数据插入：如果一次性需要插入大批量数据（万级别），使用 insert 性能会比较低，此时可以使用load指令进行插入：

# 客户端连接服务端时，添加参数 --local-infile
mysql --local-infile -u root -p;
# 设置全局参数 local_infile = 1，开启从本地加载文件导入数据的开关
set global local_infile =1;
# 执行 load 指令加载文件中的数据到表结构中
load data local infile '文件路径' into table 'table_name' fields terminated by ',' lines terminated by '\n';

主键优化

数据的组织方式：在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。
页分裂：页可以为空，也可以填充一半，也可以填充100%，每个页包含了2-N行数据（如果一行数据过多，会行溢出），根据主键排列。按照主键乱序插入时会导致页分裂，即页之间需要重新组合、排序，来保证主键的顺序。
页合并：当删除一行记录时，其实并没有进行物理删除，只是这一行记录被标记为删除，并且它的空间允许被其它记录使用。当一页中删除的记录达到阈值（MERGE_THRESHOLD）时（默认为页的50%），InnoDB 会开始寻找靠近的页看是否可以将两个页合并来优化空间使用。
主键的设计原则：
1、在满足需求的情况下，尽量减小主键长度，减小磁盘IO。
2、插入数据时尽量按主键顺序插入，选择使用 AUTO_INCREMENT 自增主键。
3、尽量不使用 UUID 做主键，或者其它自然主键（身份证号），因为它们都是无序的，在插入数据时会导致页分裂，而且它们的长度都比较长。
4、业务操作时，避免修改主键。

order by 优化

using filesor：通过表的索引或者全表扫描，读取满足条件的行，然后在排序缓冲区完成排序，所有不是通过索引直接返回排序结果的排序都叫 using filesor。
using index：通过有序索引按顺序扫描直接返回数据，称为 using index，这种方式不需要额外操作，效率高。
多字段排序时，会遵循最左前缀法则。
尽量使用覆盖索引。
索引默认按字段值升序排列，如果查询时用索引字段降序排列，则会产生 using filesor，所以查询时字段的排序方式与字段索引的排序方式一致时才会使用 using index。
在大量数据排序时，如果不可避免的出现 using filesor，可以适当增加排序缓冲区大小（sort_buffer_size，默认256k）。

group by 优化

分组操作时可以使用索引字段来提高效率。
分组操作时，如果使用索引字段，那么也会遵循最左前缀法则。

limit 优化

一般分页查询时，可以通过创建覆盖索引、结合子查询，来进行优化以提高效率。

count 优化

MyISAM 引擎会把一张表的总行数保存在磁盘上，当不加 where 添加执行 count(*) 时会直接返回总行数，效率高。
InnoDB 引擎中，执行 count(*) 时会把数据一行一行读出来然后累积计数，执行起来比较耗时。没有特别好的优化策略，可能需要我们自己计数。
count() 是一个聚合函数，对于查询返回的结果集，一行行判断，如果不是 null 累积值加 1，否则不加，最后返回累积结果。
count() 的几种用法：
1、count(主键)：InnoDB 引擎会遍历整张表，把每一行的主键 id 取出，然后按行累加计数。
2、count(字段)：没有 not null 约束时，InnoDB 引擎会遍历整张表，取出每一行的字段值，判断如果不为 null 则累加计数。如果有 not null 约束，InnoDB 引擎会把表中每一行的字段取出直接累加计数。
3、count(1)：InnoDB 引擎会遍历整张表，但不取值，对返回的每一行用 1 替换，直接按行进行累加。1 可以用其它整数替换。
4、count(*)：InnoDB 引擎对此种方式做了优化，会遍历表但不会取出每一行，直接按行进行累加。
5、效率：count(*) >= count(1) > count(主键) > count(字段)

update 优化

InnoDB 的行锁是针对索引加的锁，不是针对整行记录加的锁，所以更新时条件字段需要有索引，否则会从行锁升级为表锁，并发性能会降低，可能阻塞其它更新操作。