MySQL强制利用索引的两种方式及优化索引，利用MySQL存储过程创建测试数据。
一、MySQL强制利用索引的两种方式1、利用 FORCE INDEX 语句：
explain
select
*
from
tbl_test force index (index_item_code)
where
(item_code between 1 and 1000) and (random between 50000 and 1000000)
order by
random
limit 1; 利用 FORCE INDEX（索引名称）走索引：
https://i-blog.csdnimg.cn/blog_migrate/f96a92a356ffeb0ee899f2721eb39520.png
2、利用 USE INDEX 语句：
explain
select
*
from
tbl_test USE index (index_item_code)
where
(item_code between 1 and 1000) and (random between 50000 and 1000000)
order by
random
limit 1; 利用 USE INDEX（索引名称）走索引：
https://i-blog.csdnimg.cn/blog_migrate/320e5578f086cd724212c354b29362ba.png
FORCE INDEX 或 USE INDEX 的区别？
[*]FORCE INDEX ：这个语句指示MySQL强制查询利用特定的索引。它会忽略优化器的选择,无论索引的选择性怎样，都会利用指定的索引。这意味着纵然利用了不太恰当的索引，MySQL也会强制利用它。这大概会导致性能降落，由于不恰当的索引大概会导致查询变慢。
[*]USE INDEX ：这个语句也允许你指定要利用的索引，但它与"FORCE INDEX"不同的是，它只是暗示MySQL在大概的情况下利用指定的索引。如果MySQL以为其他索引更恰当查询，它仍然可以选择其他索引。这样可以保存肯定的机动性，让MySQL根据实际情况选择最佳的索引。
总的来说，"FORCE INDEX"是强制利用指定索引，而"USE INDEX"是暗示利用指定索引，但MySQL仍然可以根据优化器的判定选择其他索引。实际利用时，应根据详细情况进行评估选择。
二、详细实现数据如下
1、创建一张数据表及索引：
CREATE TABLE `tbl_test` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(100) NOT NULL COMMENT '姓名',
`item_code` bigint NOT NULL COMMENT '子项编号',
`order_code` varchar(100) NOT NULL COMMENT '订单编号',
`id_card` varchar(30) NOT NULL COMMENT '身份证',
`goods_number` bigint NOT NULL COMMENT '商品数量',
`amount` decimal(6,2) NOT NULL COMMENT '金额',
`create_time` datetime NOT NULL COMMENT '创建时间',
`random` bigint NOT NULL COMMENT '数据数',
PRIMARY KEY (`id`),
KEY `index_item_code` (`item_code`),
KEY `index_id_card` (`id_card`),
KEY `index_random` (`random`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; 注：表创建完成后，利用如下命令新增索引：
-- 查看tbl_test表中全部的索引信息
show index from tbl_test; https://i-blog.csdnimg.cn/blog_migrate/a70645d0bc3228d4b775ae54b183c145.png
添加索引：
-- 在tbl_test表中，goods_number列上创建索引
CREATE INDEX index_goods_number ON tbl_test (goods_number); https://i-blog.csdnimg.cn/blog_migrate/baee279f0c8e847c24f3d070d13e0a7d.png
删除索引：
-- 在tbl_test表中，删除名称为 index_goods_number 的索引
ALTER TABLE tbl_test DROP INDEX index_goods_number; https://i-blog.csdnimg.cn/blog_migrate/5e5b0ef21dd52d95c96d99d3bcc7352b.png
2、创建存储过程：
-- 创建存储过程
create procedure insert_data() begin declare i INT default 1;
while i <= 100000 DO
insert into test.tbl_test (
name,
item_code,
order_code,
id_card,
goods_number,
amount,
create_time,
random)
values (
CONCAT("test", i),
i,
CONCAT("order", i),
FLOOR(RAND() * 10000000000000),
i,
ROUND(RAND() * 100, 2),
NOW(),
FLOOR(RAND() * 1000000)
);
set
i = i + 1;
end while;
end
-- 结束 实行完成后，可在此处查看：
https://i-blog.csdnimg.cn/blog_migrate/53dfbfb21efef5712d471015ec8ba1d0.png
然后，调用存储过程：
-- 调用储存过程
CALL insert_data(); 实行完后，数据信息如下：
https://i-blog.csdnimg.cn/blog_migrate/8f2220fc7e030c200f9fc34b7ba7a83c.png
这里，可以通过存储过程的实行时间，看看慢SQL的定位方式
三、慢SQL的发现
1、实行show variables like '%general%'; 命令，查看日志功能是否开启
https://i-blog.csdnimg.cn/blog_migrate/ce73c8970f797be4bd91afd69c901c88.png
主要利用命令如下：
[*]set global general_log=on; 这个语句将全局变量 general_log 的值设置为 "on"，表示启用了全局查询日志。启用后，MySQL服务器将记录所有的查询语句到查询日志文件中，包罗 SELECT、INSERT、UPDATE、DELETE 等操作。
[*]set global general_log=off; 这个语句将全局变量 general_log 的值设置为 "off"，表示禁用了全局查询日志。禁用后，MySQL服务器将停止记录查询日志，不再将查询语句写入查询日志文件。
通过修改全局变量 general_log 的值，可以控制全局查询日志的开启和关闭。
2、查看当前慢查询日志的开启情况
-- 查看当前慢查询日志的开启情况
show variables like '%quer%'; 实行信息如下：
https://i-blog.csdnimg.cn/blog_migrate/b23a0b12dea2d2d6e23266ac9278b0a8.png
设置信息解析：
[*]binlog_rows_query_log_events：该属性设置为"OFF"，表示不记录二进制日志中的查询变乱。
[*]ft_query_expansion_limit：该属性设置为20，表示在全文搜索查询中，扩展查询的限制为最多20个词。
[*]have_query_cache：该属性设置为"NO"，表示当前MySQL服务器未启用查询缓存功能。
[*]log_queries_not_using_indexes：该属性设置为"OFF"，表示不记录未利用索引的查询语句。
[*]log_throttle_queries_not_using_indexes：该属性设置为0，表示未利用索引的查询语句不会被限制。
[*]long_query_time：该属性设置为10.000000，表示实行时间凌驾10秒的查询将被以为是慢查询。
[*]query_alloc_block_size：该属性设置为8192，表示分配给查询内存块的大小为8KB。
[*]query_prealloc_size：该属性设置为8192，表示预分配给查询的内存大小为8KB。
[*]slow_query_log：该属性设置为"ON"，表示慢查询日志功能已启用。
[*]slow_query_log_file：该属性设置为"DESKTOP-0R9IERO-slow.log"，表示慢查询日志文件的名称为"DESKTOP-0R9IERO-slow.log"。
我们通过解析，还是默认设置慢日志阀值为10秒 （设置命令：set global long_query_time = 10）
通过slow_query_log_file的值，我们找到慢SQL文件DESKTOP-0R9IERO-slow.log，我这里在本地C盘：C:\ProgramData\MySQL\MySQL Server 8.0\Data 目次下：
慢SQL日志信息，查看存储过程的实行情况：
https://i-blog.csdnimg.cn/blog_migrate/414b15cae70c3532eaae2a2cccc71993.png
四、索引的优化
1、EXPLAIN 是一个在 MySQL 中用于查询实行筹划的命令。它可以资助您了解查询语句的实行方式、优化和性能。
EXPLAIN SELECT * FROM table_name WHERE column = 'value'; 以下是 EXPLAIN 命令的一些关键信息：
[*]id：表示查询的标识符，如果查询包含子查询，每个子查询都有一个唯一的标识符。
[*]select_type：表示查询的范例，常见的范例包罗 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
[*]table：表示查询涉及的表名。
[*]type：表示访问表的方式，常见的范例有 ALL（全表扫描）、INDEX（索引扫描）、RANGE（范围扫描）等。
[*]possible_keys：表示大概应用到查询中的索引。
[*]key：表示实际利用的索引。
[*]key_len：表示索引字段的长度。
[*]ref：表示与索引比较的列或常数。
[*]rows：表示估计需要扫描的行数。
[*]Extra：提供其他额外的信息，如是否利用了临时表、排序方式等。
通太过析 EXPLAIN 的输出，您可以获得以下信息：
[*]查询是否有效利用了索引。
[*]查询的实行顺序和方式。
[*]哪些表被访问以及访问方式。
[*]估计扫描的行数和数据访问的成本。
这些信息可以资助您优化查询语句、调整索引和改进性能。
2、常见索引优化1：条件字段函数操作
当前表中已创建索引：
https://i-blog.csdnimg.cn/blog_migrate/610ba9885aa5e81598d333340999e30a.png
函数作用在条件列上，索引失效：
https://i-blog.csdnimg.cn/blog_migrate/35b6a9a24804b11b0bfae92f223e1345.png
修改后：
https://i-blog.csdnimg.cn/blog_migrate/a4ef692a725bd39833646b96fb153fb2.png
2、常见索引优化2：隐式范例转换
当前id_card字段在数据库中是 varchar 范例，直接以数值范例查询，导致索引失效：
https://i-blog.csdnimg.cn/blog_migrate/441cc45937d01b4caa69962a0f32c7f5.png
修改后，如下：
-- 使用如下写法：
explain select * from tbl_test where id_card = '2674841548013'
-- 或者：
-- CAST(267484154801 AS CHAR) 将数值类型的 2674841548013 转换为与 id_card 列的数据类型（ varchar ）匹配的字符类型。
-- 通过这样做，确保了 id_card 和值之间的比较使用匹配的数据类型，使索引能够有效使用。
explain select * from tbl_test where id_card = CAST(2674841548013 AS CHAR) https://i-blog.csdnimg.cn/blog_migrate/5eb11c7bc7dab7262482cfaa2dc7673a.png
失效的原因跟案例一范例，数据范例隐式转换，对于优化器来说，这个语句相当于：
select * from tbl_test whereCAST(id_card AS signed int) = 66778899; 这样，在WHERE 子句中利用函数、表达式或算术，索引列错误利用，导致索引失效。
3、常见索引优化3：隐式字符编码转换
当利用不同的字符集进行隐式编码转换时，大概会导致索引失效。这是由于MySQL在进行索引查找时，会利用字符集的排序规则进行比较。如果字符集不同，排序规则也会不同，从而导致索引无法精确利用。
例如下面的例子，因字符集utf8mb4和utf8隐式字符编码转换而导致索引失效的情况：
假设有一个表my_table，其中有一个名为column的列，该列利用utf8mb4字符集，而且创建了索引。
CREATE TABLE my_table (
id INT PRIMARY KEY,
column VARCHAR(255) CHARACTER SET utf8mb4
) ENGINE=InnoDB;
CREATE INDEX idx_column ON my_table (column);
然后，我们向表中插入一些数据：
INSERT INTO my_table (id, column) VALUES (1, 'abc');
INSERT INTO my_table (id, column) VALUES (2, 'def');
现在，如果我们利用不同字符集的查询语句进行隐式编码转换，大概会导致索引失效。例如，以下查询利用了utf8字符集的字符串进行查询，这与表中的utf8mb4字符集不同：
SELECT * FROM my_table WHERE column = 'ghi';
在这个情况下，由于字符集不同，MySQL无法精确利用索引，从而进行全表扫描。这会导致查询性能降落，由于全表扫描比利用索引更耗时。要避免这种情况，可以确保查询语句中的字符集与表中的字符集一致，或者显式地进行字符编码转换。
五、MySQL索引失效原因的大抵汇总
1、前导模糊查询不能利用索引，比如查询语句是LIKE '%XX'或LIKE '%XX%'，而'A%'就可以正常利用索引。
2、如果MySQL估计利用全表扫描要比利用索引快，则不利用索引。
3、OR前后存在非索引的列，索引失效。如果想利用OR，又想让索引生效，只能将OR条件中的每个列都加上索引。
4、平凡索引的不等于不会走索引，如果是主键，则还是会走索引；如果是主键或索引是整数范例，则还是会走索引。
5、is null可以利用索引，is not null无法利用索引。
6、在设计表时设置NOT NULL约束最好，比如将INT范例的默认值设为0，将字符串默认值设为''。
7、如果在查询条件中对索引列利用了任何操作（盘算，函数），或者进行了范例转换，大概会导致索引失效。
8、如果在复合索引中，查询条件没有遵循最左匹配原则，那么索引大概也不会生效。
9、如果MySQL优化器以为全表扫描的速度快于利用索引，它大概会选择全表扫描而不利用索引。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]