当前位置：首页 > article >正文

SQL优化经验大全（表设计优化，索引优化，索引创建规则、索引失效场景，sql语句优化，主从复制，分库分表）面试题

article 2024/11/6 5:20:00

1.表的设计优化

2.索引优化

2.1 索引创建的规则

2.2 索引失效的场景

3.SQL语句优化

4.主从复制、读写分离

5.分库分表

5.1.怎么判断项目是需要分库还是要分表？

5.2 分库分表有哪些拆分方案？

5.2.1 垂直分库

5.2.2 垂直分表

5.2.3 水平分库

5.2.4 水平分表

5.3 分库分表带来的问题

6.面试回答

SQL可以分为以下5个方面，下面我会挨个阐述：

1.表的设计优化

2.索引优化

3.sql语句优化

4.主从复制、读写分离

5.分库分表

1.表的设计优化

参考了阿里的开发手册（嵩山版）

例如：

①比如设置合适的数值（tinyint，int，bigint），要根据实际情况选择
②比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低

等

2.索引优化

遵循索引创建的规则，避免索引失效的场景

2.1 索引创建的规则

1.针对于数据量较大，且查询比较频繁的表建立索引。单表超过10万数据（增加用户体验）。

2.针对于常作为查询条件（where）、排序（orderby）、分组（groupby）操作的字段建立索引。

3.数据库中大量重复的列不应该建立索引，例如性别。

4.长文本字段使用前缀索引，或者放入es中。

如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引（选取字符串前几个字符）。

5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

6.索引并不是越多越好，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

频繁更新的表不应该建立索引，对于频繁进行插入、更新和删除操作的表，索引会导致额外的维护开销，因为每次数据变更时都需要更新索引，这会影响性能。

7.如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

8.数据库较小的表，不要建立索引，因为可能走全表扫描

2.2 索引失效的场景

1 使用了联合索引却不符合最左前缀匹配原则

举个例子：小鱼对 user 表建立了一个联合索引为 name_age_id 的联合索引。他使用以下 SQL 查询select * from user where age = 10 and id = 1; 这样的写法恰恰不满足最左前缀原则，索引就失效啦。

详细解释：

MySQL中，索引的“最左前缀原则”是指在使用索引进行查询时，索引会从左到右匹配列。如果查询条件中包含了索引的最左列，那么索引就可以被使用；如果没有包含最左列，那么索引可能不会被使用。

在上面例子中，小鱼建立了一个名为name_age_id的联合索引，这意味着索引的顺序是name、age和id。这个索引可以支持以下几种查询条件：

仅name列：WHERE name = '某个值'
name和age列：WHERE name = '某个值' AND age = 某个值
name、age和id列：WHERE name = '某个值' AND age = 某个值 AND id = 某个值

但是，如果查询条件只包含age和id，而不包含name，那么索引可能不会被使用，因为name是索引的最左列。这是因为索引的最左前缀原则要求查询条件必须包含索引的最左列。

2.索引中使用了运算

例如这个 SQL select * from user where id + 3 = 8。这样会导致全表扫描计算 id 的值再进行比较，使得索引失效。

3. 索引上使用了函数也会失效

例如：select * from user where LOWER(name) like 'cong%';。这样也会导致索引失效，索引参与了函数处理，会导致去全表扫描。

4. 以%开头的Like模糊查询

如果仅仅是为有模糊匹配，索引不会失效

5. or的随意使用，必须or的两个字段，都有索引，索引才会生效

user 当前只有一个索引 name 。此时执行以下SQL ：select * from user where name= 'cong' or age = 18; 这也会导致索引失效，因为 age 没有索引。

6. 隐式类型转换，会导致索引失效

例如：字符串不加' ',隐式的类型转换会导致索引失效

不小心将 varchar 类型的 name 条件匹配了 int 类型字段。

SQL 是这样的 select * from user where name = 1;

正确的应该写为select * from user where name = '1';

7.表中两个不同的字段进行比较，索引会失效

例如这样的SQL :select * from user where id > age; ，将 id 跟 age 字段做了比较，索引失效！

8.使用了 is not null

注意使用了 is not null 会直接导致失效，而使用 is null 则不会。因为当使用 is not null 条件时，数据库系统需要扫描整个索引，找出所有不为 null 的值，这可能会导致索引失效。使用 is null条件不会导致索引失效，因为对于索引而言，查找 null 值的行与查找其他任何特定值的行都没有本质区别。在大多数情况下，null 值都会被索引包括在内。

9.使用了 order by，但后面不跟主键

当 order by 后面跟的 不是主键 或者 不是覆盖索引 会导致不走索引。

10.使用了 != 、<>，可能导致索引失效

例如这样的 SQL ：select * from user where name != 'cong' ，

如果name列的值大多数都是'cong'，那么不等于'cong'的记录可能很少，优化器可能会认为使用索引不划算，因此选择全表扫描。这种情况下，索引可能不会生效。