索引失效的几种情况-什么情况下索引会失效 ? SQL 优化经验和技巧

什么情况下索引会失效 ?

1).违反最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引:

2).范围查询右边的列，不能使用索引。

3).不要在索引列上进行运算操作，索引将失效。

4).字符串不加单引号，造成索引失效。（由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。）

5).以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

在数据库操作中，索引失效的情况主要包括以下几种：

了解这些情况有助于在设计查询和索引时做出更好的决策，从而提高查询性能。

表的设计优化(参考阿里开发手册《嵩山版》)

比如设置合适的数值( int )，要根据实际情况选择

比如设置合适的字符串类型(char和)char定长效率高，可变长度，效率稍低

SQL语句优化

语句务必指明字段名称(避免直接使用*)

SQL语句要避免造成索引失效的写法

尽量用union all代替union union会多一次过滤，效率低

避免在where子句中对字段进行表达式操作

Join优化 能用inner join 就不用left join right join，如必须使用 一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join或 right join，不会重新调整顺序

主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构,读写分离解决的是索引失效的几种情况，数据库的写入，影响了查询的效率。

表的设计优化，数据类型的选择

索引优化，索引创建原则

sql语句优化，避免索引失效，避免使用*

主从复制、读写分离，不让数据的写入，影响读操作

分库分表

SQL 优化是提高数据库性能的关键步骤。以下是一些常见的 SQL 优化经验和技巧：

1：索引优化

2：查询优化

3：表结构优化

4：事务管理

5：统计信息更新

1. 索引优化示例

假设有一个用户表 users索引失效的几种情况，包含字段 id, , email, 。

CREATE TABLE users (    id INT PRIMARY KEY,    username VARCHAR(50),    email VARCHAR(100),    created_at DATETIME);
-- 创建复合索引CREATE INDEX idx_username_email ON users(username, email);

查询示例：

-- 使用复合索引SELECT * FROM users WHERE username = 'john' AND email = 'john@example.com';
-- 避免索引失效SELECT * FROM users WHERE username = 'john' AND email LIKE '%example%';

2. 查询优化示例

-- 避免使用 SELECT *SELECT id, username, email FROM users WHERE created_at > '2023-01-01';
-- 使用 EXISTS 替代 INSELECT u.id, u.username FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- 子查询优化-- 原始查询SELECT u.id, u.username FROM users u WHERE u.id IN (SELECT o.user_id FROM orders o WHERE o.status = 'completed');
-- 优化后的查询SELECT u.id, u.username FROM users u JOIN (SELECT DISTINCT user_id FROM orders WHERE status = 'completed') o ON u.id = o.user_id;

3. 表结构优化示例

假设有一个订单表 ，包含字段 id, , , , 。

CREATE TABLE orders (    id INT PRIMARY KEY,    user_id INT,    product_id INT,    status VARCHAR(20),    created_at DATETIME);
-- 分区表CREATE TABLE orders_partitioned (    id INT PRIMARY KEY,    user_id INT,    product_id INT,    status VARCHAR(20),    created_at DATETIME) PARTITION BY RANGE (YEAR(created_at)) (    PARTITION p0 VALUES LESS THAN (2020),    PARTITION p1 VALUES LESS THAN (2021),    PARTITION p2 VALUES LESS THAN (2022),    PARTITION p3 VALUES LESS THAN (2023));

4. 事务管理示例

// Java 代码示例@Autowiredprivate JdbcTemplate jdbcTemplate;
@Transactionalpublic void processOrder(int orderId) {    // 更新订单状态    jdbcTemplate.update("UPDATE orders SET status = 'processing' WHERE id = ?", orderId);        // 执行其他业务逻辑    // ...        // 提交事务}

5. 统计信息更新示例

-- MySQLANALYZE TABLE users;
-- PostgreSQLVACUUM ANALYZE users;

通过以上示例和技巧，可以在实际开发中有效地优化 SQL 查询，提高数据库性能。

admin会员

打赏 收藏 海报 链接