索引

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' and TABLE_NAME like '%table_name%';

索引失效的场景

假设场景

CREATE TABLE test
(
id           bigint unsigned     NOT NULL AUTO_INCREMENT COMMENT '数据ID，主键自增',
age          bigint unsigned     NOT NULL DEFAULT ' ' COMMENT '年龄',
name         varchar(100)        NOT NULL DEFAULT '0' COMMENT '名称',
createTime   char(14)            NOT NULL DEFAULT ' ' COMMENT '创建时间，yyyyMMddHHmmss',
createUserId bigint unsigned     NOT NULL DEFAULT '0' COMMENT '创建用户ID',
updateTime   char(17)            NOT NULL DEFAULT ' ' COMMENT '修改时间，yyyyMMddHHmmssSSS',
updateUserId bigint unsigned     NOT NULL DEFAULT '0' COMMENT '修改用户ID',
deleted      tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否逻辑删除。 [0:否, 1:是]',
PRIMARY KEY (id),
INDEX          idxAge (age, name),
INDEX idxCreateTime (createTime),
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4 COMMENT ='测试';

• 违反最左前缀原则(联合索引)
  • 解释：只有当查询条件使用到索引最左边的字段 a时，索引才会被使用。

  • explain select * from test where name="1"

• 在索引列使用函数运算

  • explain select * from test where substr(name,1,3)="123";

• 索引列使用范围查询
  • 数据量大时会失效
  • is not null会失效

    • explain select * from test where age is not null;

  • not in会失效

    • explain select * from test where age is not null;

  • not exists 会失效
• 使用or可能会失效
  • 使用or连接到非索引列会失效
• 索引使用通配符查询，且通配符在前面

  • explain select * from test where createTime like '%123%'

• 触发隐式转换——索引是字符类型，且没有加双引号，可能会失效，

  • explain select * from test where createTime =123

• 如果查询结果集中的列数超过了索引列数，则索引 可能 失效。这是因为 MySQL 在查询结果集时，需要从索引中提取额外的列值，这会降低索引的使用效率。
• 两列比较

  • explain select * from test where age >id

• 不使用select * 可以走覆盖索引

  • explain select id from test where name="1"

• 使用ordre by(使用id的话会走索引)

  • explain select * from test order by age

查询速度排序（从快到慢）：

const>eq_ref>ref>range>index>ALL

const 常量级

查询条件是一个常量时，MySQL 可以快速确定只有一行数据匹配。最快的查询类型(一般命中唯一索引和主键)

select id form test where id=1

eq_ref 索引查询(比ref快)

explain
select *
from test a
         left join test2 b on a.id = b.tId
where a.id in (1,2);

使用唯一主键查询

ref 索引查询

select id from test where age=1

使用非唯一主键查询，返回单独一行数据

range索引范围查询

select id from test where age in(1,2)

常见于 '<', '<=', '>', '>=', 'between' ,in等范围行函数和操作符.

index全索引查询（和all的区别在于index是遍历了所有查询索引树）

select id from test 

ALL全表查询（最慢的查询，未使用到索引）

select * from test

执行方式

大部分情况下为串行

• 若版本 >= 8.0.14：默认支持 InnoDB 并行扫描，通过 innodb_parallel_read_threads 配置。
• 若版本 >= 8.0.27：可实验性启用并行查询（需手动配置）。
• 并行复制：所有 5.7+ 版本均支持，但需配置 slave_parallel_workers > 0。

多个sql 的情况下

• MySQL 默认支持 多线程处理多个连接，因此这些 SQL 会并行执行（每个连接分配独立线程）。

• 可能串行化的场景：
  • 全局资源争用：如磁盘 I/O 饱和、InnoDB 缓冲池竞争。
  • 表级锁（仅限 MyISAM）：MyISAM 引擎对写操作默认加表级锁，若两个线程同时写不同 MyISAM 表，仍可能因全局锁调度串行化。
  • 行级锁冲突（InnoDB）：若不同表的操作涉及同一事务或隐式锁（如外键约束），可能间接导致串行化。

单个sql结合多个表时

• MySQL 在大多数情况下（尤其是 8.0 之前版本）会串行执行 JOIN 和子查询，由单个线程处理所有步骤。
• MySQL 8.0+ 并行查询：
  若启用 parallel_query 实验性功能（需配置 parallel_query_threads），优化器可能将大表的扫描或聚合拆分为并行任务。
  -- 查看是否启用并行查询
  SHOW VARIABLES LIKE 'parallel_query%';

• InnoDB 并行索引扫描（8.0.14+）：
  对大表的全索引扫描或范围扫描可能触发多线程并行读取（通过 innodb_parallel_read_threads 配置）。
  -- 查看并行扫描线程数
  SHOW VARIABLES LIKE 'innodb_parallel_read_threads';

不同存储引擎的差异

如何验证是否为并行

 监控线程活动

-- 查看正在执行的线程（观察多个线程是否处理同一查询）
SHOW PROCESSLIST;

-- 通过 Performance Schema 监控并行线程
SELECT * FROM performance_schema.threads 
WHERE NAME LIKE '%parallel%' OR NAME LIKE '%worker%';

EXPLAIN FORMAT=TREE 
SELECT * FROM large_table WHERE col > 100;

未完