跳槽必备之你设计索引的原则是什么?怎么避免索引失效?

跳槽必备之你设计索引的原则是什么？怎么避免索引失效？

1. 设计索引的原则

在设计索引时需要遵守以下原则：

• 索引的选择性越高越好

索引的选择性是指索引中不重复的数据占总数据的比例。当索引选择性越高时，查询效率就越高。所以在设计索引时应该尽可能选择那些选择性高的列进行索引。

• 尽可能使用最左前缀进行索引

最左前缀指的是索引列的最左边的前缀，优先考虑匹配最左前缀的索引，这样可以避免全表扫描。

• 不要对索引列进行计算和函数操作

如果对索引列进行任何计算和函数操作，就会失去索引的优势。所以在设计索引时不要对索引列进行计算和函数操作。

• 尽量避免使用LIKE %XXX%这样的查询

这种查询无法充分利用索引，会导致全表扫描。如果需要模糊查询，可以考虑使用全文索引或者将查询条件改为LIKE XXX%。

1. 避免索引失效

为了避免索引失效，需要考虑以下几点：

• 避免使用NOT IN和<>操作符

这两种操作符会导致索引失效，可以考虑使用IN和=操作符代替。

• 避免使用OR操作符

OR操作符会导致索引失效，可以考虑使用UNION操作符代替。

示例1：

假设有一个用户表，包含id、name、age和gender四个字段。如果要查询name包含"john"和"doe"的记录，应该如何设计索引？

可以创建一个联合索引，将name和其他几个重要的字段一起进行索引。在查询时，使用如下SQL语句：

SELECT * FROM users WHERE name LIKE 'john%' OR name LIKE 'doe%';

这样就可以充分利用索引，避免全表扫描。

示例2：

假设有一个订单表，包含id、customer_id、order_date和total_amount四个字段。如果要查询某个客户在2019年购买的所有订单，应该如何设计索引？

可以创建一个联合索引，将customer_id和order_date两个字段一起进行索引。在查询时，使用如下SQL语句：

SELECT * FROM orders WHERE customer_id=123 AND order_date BETWEEN '2019-01-01' AND '2019-12-31';

这样就可以充分利用索引，避免全表扫描。