Mysql 8.4数据库如何使用查看索引和使用索引

查看表结构

show create table JOB;

 或者使用

desc test1;

 

查看表下的索引

SHOW INDEXES in JOB;

或者

show indexes from JOB;

 删除索引

DROP INDEX IDX_JOB  ON JOB;

 

创建索引

 create index IDX_JOB_MAX_SALARY on JOB(MAX_SALARY);

 

查询执行计划

explain select * from JOB where MAX_SALARY>'4000';

EXPLAIN 输出字段

type 访问类型（性能从优到劣）

优化目标：至少达到 range 级别，理想是 ref 或以上

 Extra 字段常见值解析

Using index    Using where    不用考虑，都使用了索引

Using index condition   使用了索引下推，也是不用担心的

出现 Using temporary   Using filesort   Using join buffer    是需要重视的，也是需要优化的

 Using index condition   --表是使用了索引下推

 

 

 

索引下推在MySQL 5.6引入，是针对索引的查询优化  具体的信息，可以参考链接
--https://mp.weixin.qq.com/s/8BKqO6SAmuoDzhjGKHT1WA

 

管理以及查找索引

 

查看当前数据库中所有表的索引

-- 查看当前数据库所有表的索引
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    INDEX_TYPE,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS INDEX_COLUMNS,
    NON_UNIQUE,
    CARDINALITY
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = DATABASE()
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME, INDEX_NAME;

 

如果在优化中感觉有更合适的索引，可以使用 FORCE INDEX (索引名) 或者USE INDEX (索引名) 改变优化器使用的索引

注意事项：

1. 性能影响：强制使用不合适的索引可能会导致查询性能下降。因此，在使用 FORCE INDEX 之前，最好使用 EXPLAIN 命令分析查询执行计划，确保选择的索引是合适的。

2. 索引选择：FORCE INDEX 比 USE INDEX 更强力，MySQL 会更倾向于使用指定的索引。而 USE INDEX 只是建议 MySQL 考虑使用这些索引，优化器最终可能仍然决定不使用。

3. 版本差异：不同 MySQL 版本对索引提示的支持可能略有不同，请确保你的 MySQL 版本支持 FORCE INDEX。

4. 谨慎使用：强制索引应该谨慎使用，并在生产环境前充分测试其性能影响。

5. 替代方案：在大多数情况下，最好让 MySQL 优化器根据统计信息和查询特性自行选择最佳的索引。只有在特定情况下（例如，当优化器选择的索引不如你预期的索引有效时），才考虑使用 FORCE INDEX。

 

统计信息

 

1. 使用ANALYZE TABLE命令

ANALYZE TABLE命令用于更新表的统计信息，例如键分布。这对于优化器在制定查询计划时非常重要。

ANALYZE TABLE table_name;

如果你想要分析多个表，可以这样做：

ANALYZE TABLE table1, table2, table3;

 

2. 使用OPTIMIZE TABLE命令

虽然OPTIMIZE TABLE主要用于回收未使用的空间并重新组织数据表，但它也会更新表的统计信息。在某些情况下，这可以作为搜集统计信息的一种方式。

OPTIMIZE TABLE table_name;

 

3. 使用SHOW TABLE STATUS命令

SHOW TABLE STATUS命令提供了关于表的各种状态信息，包括行的数量、平均行长度、数据长度、索引长度等。这些信息虽然不完全等同于统计信息，但可以用来评估表的当前状态。

SHOW TABLE STATUS FROM database_name LIKE 'table_pattern';

4. 查看索引使用情况

你可以通过查看INFORMATION_SCHEMA中的TABLES和STATISTICS表来获取关于索引使用情况的信息。这可以帮助你了解哪些索引正在被使用，哪些可能没有被有效使用。

SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';

 

以下行为会自动触发统计信息的收集

1.第一次打开表的时候
2.表修改的行超过1/6或者20亿条时
3.当有新的记录插入时
4.执行show index from tablename或者执行show table stauts、查询information_schema.tables\statistics 时