文本

大家好，我是战哥。

1.查询SQL。 尽量不要使用*，而是使用特定字段。

1. 反例

SELECT * FROM user

2、正面例子

SELECT id,username,tel FROM user

3、原因

节省资源并减少网络开销。

覆盖索引可以用来减少表返回，提高查询效率。

注意：为了节省时间，下面的示例字段已替换为 *。

2.避免在where子句中使用or来连接条件

1. 反例

SELECT * FROM user WHERE id=1 OR salary=5000

2、正面例子

(1)使用union all

SELECT * FROM user WHERE id=1 
UNION ALL
SELECT * FROM user WHERE salary=5000

(2)分开两条sql写入

SELECT * FROM user WHERE id=1

SELECT * FROM user WHERE salary=5000

3、原因

使用或者可能会使索引失效，导致全表扫描；

对于where or没有索引的情况，假设它使用的是id的索引，但是当达到查询条件时，仍然要扫描全表；

也就是说，整个过程需要三步：全表扫描+索引扫描+合并。 如果一开始就进行全表扫描，则可以一次扫描完成；

虽然MySQL有优化器，但出于效率和成本的考虑，索引在遇到OR条件时仍然可能会失败；

3.尽量使用数值而不是字符串类型。

1、正面例子

主键（id）：键优先使用数字类型int。

性别（sex）：0代表女，1代表男； 数据库没有布尔类型，推荐使用mysql。

2. 原因

因为引擎在处理查询和连接时会将字符串中的每个字符一一比较；

对于数字类型，只需比较一次就足够了；

字符会降低查询和连接性能并增加存储开销；

4.使用代替char

1. 反例

`address` char(100) DEFAULT NULL COMMENT '地址'

2、正面例子

`address` varchar(100) DEFAULT NULL COMMENT '地址'

3、原因

变长字段按照数据内容的实际长度存储，存储空间小，可以节省存储空间；

char按照声明的大小存储，不足时不加空格；

其次，对于查询来说，在相对较小的字段内进行搜索效率更高；

5.技术延伸，char和char有什么区别？

1、char的长度是固定的，但是char的长度是可以改变的。

例如，存储字符串“101”时，对于char(10)来说，意味着你存储的字符将占用10个字节（包括7个空字符）。 在数据库中，是被空格占用的，同样的（10）只占用3个字节的长度，10只是最大值。 当你存储的字符少于10个时，按照实际长度存储。

2、char的效率比char略高。

3、什么时候用char，什么时候用？

Char 和 是一个矛盾的统一体。 两者是互补的。 它们比 char 节省空间，但效率略低于 char。 如果你想获得效率，就必须牺牲一些空间。 这就是我们在数据库设计中所做的。 人们常说“以空间换效率”。

虽然比char节省空间，但是如果频繁修改某一列，并且每次修改的数据长度不同，就会造成“行迁移”现象，从而造成冗余I/O，这是数据库中应该避免的设计。 ，在这种情况下最好使用 char 代替。 char 会自动填充空格，因为输入 char 字段时会自动添加空格，但后面的空格不会被删除。 因此，查询char类型时一定要记得使用trim。 这就是写这篇文章的原因。

如果开发人员详细说明使用 rpad() 技巧将绑定变量转换为某种与 char 字段相当的类型（当然，填充绑定变量比截断修剪数据库列要好，因为应用修剪函数的列可以很容易导致无法使用列上现有的索引），并且可能必须考虑列长度随时间的变化。 如果字段的大小发生变化，应用程序就会受到影响，因为它必须修改字段宽度。

正是由于上述原因，定宽存储空间可能会导致表和相关索引比平时大很多，而且还伴随着绑定变量问题，所以无论什么场合都要避免使用char类型。

6.在where中使用默认值而不是null

1. 反例

SELECT * FROM user WHERE age IS NOT NULL

2、正面例子

SELECT * FROM user WHERE age>0

3、原因

并不意味着如果使用is null或者is not null，索引就不会被使用。 这与MySQL版本和查询成本有关；

如果MySQL优化器发现建立索引的成本高于不建立索引的成本，就会放弃索引。 这些条件！=、、is null、is not null常常被认为使索引无效；

其实是因为一般情况下查询成本高，优化器自动放弃索引；

如果将null值替换为默认值，往往是可以索引的，同时含义也比较清晰；

7.避免在where子句中使用!=或运算符

1. 反例

SELECT * FROM user WHERE salary!=5000

SELECT * FROM user WHERE salary<>5000

2. 原因

使用 != and 可能会使索引无效

应尽量避免在where子句中使用!=或运算符，否则引擎将放弃使用索引并执行全表扫描

要实现业务优先，实在没有办法的话，就只能用它了。 这并不意味着您不能使用它。

8.内连接、左连接、右连接、内连接优先

如果三个连接的结果相同，则优先选择内连接。 如果使用左连接，则左表应尽可能小。

为什么？

9.提高group by语句的效率

1. 反例

先分组，再过滤

select job, avg（salary） from employee 
group by job
having job ='develop' or job = 'test';

2、正面例子

先过滤，再分组

select job，avg（salary） from employee 
where job ='develop' or job = 'test' 
group by job;

3、原因

您可以在执行语句之前过滤掉不需要的记录。

10. 清表时使用的优​​先级

table 在功能上与不带 where 子句的语句相同：都删除表中的所有行。 但表速度更快，并且使用更少的系统和事务日志资源。

该语句一次删除一行，并在事务日志中为删除的每一行记录一个条目。 表删除数据是通过释放存储表数据的数据页来实现的，只在事务日志中记录该页的释放。 另外，搜索公众号顶级技术背景，回复“API接口”即可获得惊喜大礼包。

table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。 用于标识新行的计数将重置为该列的种子。 如果您想保留身份计数值，请改用。 如果要删除表定义及其数据，请使用 drop table 语句。

对于键约束引用的表intellij idea 重置默认视图，不能使用table，而应使用不带where子句的语句。 由于该表未记录，因此无法激活触发器。

table 不能用于参与索引视图的表。

11、操作或语句，循环中批量添加限制或删除

1.减少写错SQL的成本

清除表数据并不是一件小事。 如果手一抖，一切都没有了，删除数据库然后逃跑？ 如果加上限制，误删除只会丢失部分数据，并且可以通过日志快速恢复。

2. SQL可能更高效

SQL 中添加了限制 1。 如果第一项命中目标，如果没有限制，则继续执行扫描表。

3.避免长时间交易

执行过程中，如果age被索引，MySQL会为所有相关行添加写锁和间隙锁，所有与执行相关的行都会被锁定。 如果删除次数较多，会直接影响相关业务，变得无法使用。

4、数据量大的话，很容易占满CPU。

如果删除大量数据，又不加限制限制记录数，很容易占满CPU，导致删除越来越慢。

5. 锁表

一次删除过多的数据可能会导致锁表和锁等待错误，所以建议批量操作。

12. 联合运算符

UNION 联表后会过滤掉重复记录，因此联表后会对生成的结果集进行排序，删除重复记录，然后返回结果。 在大多数实际应用中，不会产生重复记录。 最常见的是进程表和历史表之间的 UNION。 喜欢：

select username,tel from user
union
select departmentname from department

这条SQL在运行时首先检索两个表的结果，然后使用排序空间进行排序并删除重复记录，最后返回结果集。 如果表数据较大，可以使用磁盘进行排序。 推荐解决方案：使用 UNION ALL 运算符而不是 UNION，因为 UNION ALL 运算只是将两个结果合并后返回。

13.批量插入性能提升

1. 多次提交

INSERT INTO user (id,username) VALUES(1,'哪吒编程');

INSERT INTO user (id,username) VALUES(2,'妲己');

2. 批量提交

INSERT INTO user (id,username) VALUES(1,'哪吒编程'),(2,'妲己');

3、原因

默认情况下intellij idea 重置默认视图，新添加的SQL是有事务控制的，需要每个事务都打开并提交。 然而，批处理需要打开并提交一笔交易。 效率明显提升，达到一定程度，效果显着，平时是看不到的。

14、表连接不宜过多，索引也不宜过多，一般在5个以内。

1、表连接数不宜过多，一般在5个以内

关联表的数量越多，编译时间和开销就越大。

内存中为每个关联生成一个临时表

连接表应拆分为较小的执行，以使其更具可读性。

如果您需要连接许多表来获取数据，则意味着这是一个糟糕的设计。

根据阿里巴巴规范，建议在三表以内查询多表。

2、索引不宜过多，一般在5个以内。

索引越多越好。 虽然它们提高了查询的效率，但会降低插入和更新的效率；

索引可以理解为一张表，可以存储数据，但是它的数据占用空间；

索引表中的数据是排序的，排序也需要时间；

索引可能需要不时地重建。 如果数据量很大，重建会导致记录重新排序，所以建立索引需要慎重考虑，根据具体情况而定；

一个表中的索引最好不要超过5个。 如果太多，则需要考虑是否需要某些索引；

15.避免在索引列上使用内置函数

1. 反例

SELECT * FROM user WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();

2、正面例子

SELECT * FROM user WHERE  birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);

3、原因

当对索引列使用内置函数时，索引将变得无效。

16. 组合索引

排序时，应该按照组合索引中每一列的顺序进行排序，即使只对索引中的一列进行排序，否则排序性能会很差。

create index IDX_USERNAME_TEL on user(deptid,position,createtime);
select username,tel from user where deptid= 1 and position = 'java开发' order by deptid,position,createtime desc;

其实只是查询满足=1和='java开发'条件的记录，并按降序排序，但是按desc写order性能较差。

17. 综合索引最左特征

1.创建复合索引

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)

2、如果满足复合索引最左边的特征，即使只是复合索引的一部分，复合索引也会生效。

SELECT * FROM employee WHERE NAME='哪吒编程'

3、如果左边的字段没有出现，则说明最左边的特征不满足，索引失效。

SELECT * FROM employee WHERE salary=5000

4、使用全部复合索引，name按左侧顺序出现，索引生效。

牛逼啊！接私活必备的 N 个开源项目！赶快收藏

SELECT * FROM employee WHERE NAME='哪吒编程' AND salary=5000

5、虽然违反了最左边的特性，但是MySQL在执行SQL的时候会进行优化，底层会进行反向优化。

SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒编程'

六、原因

复合索引也称为联合索引。 当我们创建一个联合索引，如(k1,k2,k3)时，相当于创建了三个索引(k1)、(k1,k2)和(k1,k2,k3)。 ，这就是最左匹配原则。

联合索引不满足最左原则，索引一般会失败。

18.优化like语句

对于模糊查询，程序员最喜欢的是使用like，但是like很可能会让你的索引失效。

1. 反例

select * from citys where name like '%大连' (不使用索引)

select * from citys where name like '%大连%' (不使用索引)

2、正面例子

select * from citys where name like '大连%' (使用索引) 。

3、原因

19. 用于分析你的SQL执行计划

1. 类型

：表只有一行，基本不用；

const：表最多可以匹配一行数据，在查询主键时更容易被触发；

：对于上一个表中的每个行组合，从该表中读取一行。 这可能是除 const 类型之外最好的连接类型；

ref：对于前一个表中的行的每个组合，将从该表中读取具有匹配索引值的所有行；

range：仅检索给定范围的行，使用索引来选择行。 当使用 =、 、 >、 >=、 > ref > range > index > all 时。

在实际的SQL优化中，最终会达到ref或range级别。

2.额外常用关键词

20.其他一些优化方法

1、设计表格时，为所有表格和字段添加相应的注释。

2. SQL书写格式，保持关键字大小一致并使用缩进。

3.修改或删除重要数据前请先备份。

4.很多时候用“in”代替“in”是一个不错的选择

5、对于where后面的字段，要注意其数据类型的隐式转换。

未使用索引

SELECT * FROM user WHERE NAME=110

(1)因为不加单引号，比较的是字符串和数字，它们的类型不匹配；

（2）MySQL会做隐式类型转换，转换为数值类型然后进行比较；

6.尝试将所有列定义为NOT NULL

NOT NULL 列可以节省空间。 NULL列需要一个额外的字节作为标志来确定它是否为NULL。 NULL列需要注意空指针问题。 在计算和比较NULL列时，需要注意空指针问题。

7. 伪删除设计

8、尽可能统一使用UTF8作为数据库和表的字符集。

(1)可以避免乱码问题；

（2）可以避免不同字符集比较转换导致的索引失败问题；

9. 从表中计数（*）；

这样不带任何条件的计数会导致全表扫描，没有业务意义，所以一定要避免。

10.避免对where中的字段执行表达式操作

(1) SQL解析时，与表达式相关的字段是否进行全表扫描；

（2）字段干净，没有表达式，索引生效；

11.关于临时表

（1）避免频繁创建和删除临时表，以减少系统表资源的消耗；

（2）创建临时表时，如果一次性插入大量数据，可以使用into代替table，避免造成大量日志；

(3)如果数据量不大，为了缓解系统表的资源，应该先创建表，然后；

(4) 如果使用临时表，则必须在存储过程结束时显式删除所有临时表。 先建表，后删除表，这样可以避免系统表的长期锁定；

12、索引不适合建立在重复数据较多的字段上，比如性别。 应该创建索引来对字段进行排序。

13、去重过滤的字段应该少一些。

使用它的语句比不使用它的语句占用更多的 CPU 时间。

查询多个字段时，如果使用，数据库引擎会比较数据，过滤掉重复数据

然而，这个比较和过滤的过程会占用系统资源，比如CPU时间。

14、尽量避免大事务操作，提高系统并发能力

15.所有表必须使用存储引擎

“支持事务，支持行级锁，有更好的恢复能力。” 高并发下性能更好。 因此，在没有特殊要求（即不满足功能如：列存储、存储空间数据等）的情况下，所有的表都必须使用存储引擎。

16.尽量避免使用游标

因为游标的效率较差，如果游标操作的数据超过10000行，就应该考虑重写。

欢迎有需要的同学试试，如果本文对您有帮助，也请帮忙点个 赞 + 在看 啦！❤️ 扩展：快速开发平台
在 GitHub猿 还有更多优质项目系统学习资源，欢迎分享给其他同学吧！

最后，我们整理了一份BAT各大公司的面试真题，供读者参考。 如有需要，可扫描二维码，添加微信备注：“面试”即可获取。
◆◆◆ ◆◆
（放到你的圈子里，你的朋友会感谢你的）

PS：如果您觉得我的分享不错，欢迎点赞、阅读。
如有侵权请联系删除！
                             
TAGS：mysql创建索引   table   sql优化   char函数   数据库视图