mysql索引失效场景与mysql优化方式

发布时间:2024年01月11日

索引失效场景

联合索引不满足最左匹配原则

索引列参与了运算,会导致全表扫描,索引失效

索引列参使用了函数

模糊查询时(like语句),模糊匹配的占位符位于条件的左侧

like '%abc',like '%abc%' 都会导致失效,like 'abc%'不会

参数类型与字段类型不匹配,导致类型发生了隐式转换,索引失效

select * from t_user where id_no = 1002

id_no字段类型为varchar,但在SQL语句中使用了int类型,导致全表扫描

查询条件使用or关键字,其中一个字段没有创建索引,则会导致整个查询语句索引失效; or两边为“>”和“<”范围查询时,索引失效

select * from t_user where id = 2 or username = 'Tom2';

select * from t_user where id > 1 or id < 80

两列数据做比较,即便两列都创建了索引,索引也会失效

select * from t_user where id > age

当查询条件为字符串时,使用”<>“或”!=“作为条件查询,有可能不走索引,但也不全是

select * from t_user where id_no <> '1002'

查询条件使用不等进行比较时,需要慎重,普通索引会查询结果集占比较大时索引会失效

select * from t_user where create_time != '2022-02-27 09:56:42'

由于“2022-02-27 09:56:42”是存储过程在同一秒生成的,大量数据是这个时间。执行之后会发现,当查询结果集占比比较小时,会走索引,占比比较大时不会走索引。此处与结果集与总体的占比有关

查询条件使用is null时正常走索引,使用is not null时,不走索引

查询条件使用not in时,如果是主键则走索引,如果是普通索引,则索引失效

查询条件使用not exists时,索引失效

当查询条件涉及到order by、limit等条件时,是否走索引情况比较复杂

如何优化慢sql(索引,语句,表结构,数据量,分库分表,连接池参数,缓冲池参数,I/O参数)

使用索引:创建合适的索引可以大大加快查询速度。在选择要创建的索引时,请考虑查询频率和查询性能。
优化查询:尽可能使用简单的查询语句,并确保使用索引来过滤数据,以减少查询时间。
优化表结构:避免使用大型、不必要的列和表,以及尽可能使用小型数据类型。
使用存储过程和触发器:这些可以优化重复性操作的性能,并提高数据完整性。
使用分区分表:将表分为较小的分区可以提高查询性能,并减少查询时间。
避免使用 SELECT *:只选择需要的列可以减少查询时间和减轻数据库负载。
避免使用子查询:子查询可能会增加查询时间。如果可能,请使用联接操作。
避免使用临时表:创建和使用临时表可能会增加查询时间和占用更多的内存。
使用缓存:对于经常访问的查询结果,可以考虑使用缓存技术,如 redis。

使用主从复制:使用主从复制可以将查询负载分布到多个服务器上,并提高数据库的可伸缩性。
优化数据库设计:优化数据库设计可以提高查询性能和数据完整性,如使用正规化的表设计。
使用连接池:使用连接池可以避免频繁地创建和关闭数据库连接,从而提高数据库性能。
定期进行数据清理:定期清理不需要的数据和记录可以减少数据库的负载,并提高查询性能。

数据库调优

1)缓存大小参数

  • 缓冲池大小是Innodb存储引擎的核心参数之一,InnoDB 存储引擎缓存的数据和索引的大小,一般设置为物理内存的 60%-80%

2)线程池参数

  • 最大连接数(max_connections):允许同时连接到 MySQL 数据库的最大连接数,一般设置为 500-1000
  • 线程池大小(thread_pool_size):连接线程池的大小,一般设置为 CPU 核数的 2 倍
  • 等待连接超时时间(wait_timeout):等待连接的时间超过这个时间将会被强制断开
文章来源:https://blog.csdn.net/qq_36042938/article/details/134770780
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。