SQL语句优化和数据库优化

1.说出一些数据库优化方面的经验?

1.有外键约束的话会影响增删改的性能，如果应用程序可以保证数据库的完整性那就去除外键

2.Sql语句全部大写，特别是列名大写，因为数据库的机制是这样的，sql语句发送到数据库服务器，数据库首先就会把sql编译成大写在执行，如果一开始就编译成大写就不需要了把sql编译成大写这个步骤了

3.如果应用程序可以保证数据库的完整性，可以不需要按照三大范式来设计数据库(不太懂）

4.其实可以不必要创建很多索引，索引可以加快查询速度，但是索引会消耗磁盘空间

5.如果是jdbc的话，使用PreparedStatement不使用Statement，来创建SQl，PreparedStatement的性能比Statement的速度要快，使用PreparedStatement对象SQL语句会预编译在此对象中，PreparedStatement对象可以多次高效的执行

2.怎么优化SQL查询语句吗

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

2.用索引可以提高查询

3.SELECT子句中*避免使用号，尽量全部大写SQL**

4.应尽量避免在 where 子句中对字段进行 is null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，使用 IS NOT NULL

5.where 子句中使用 or 来连接条件，也会导致引擎放弃使用索引而进行全表扫描

6.in 和 not in 也要慎用，否则会导致全表扫描

3.你怎么知道SQL语句性能是高还是低

1.查看SQL的执行时间。 2.使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

4.SQL的执行顺序

1.FROM：将数据从硬盘加载到数据缓冲区，方便对接下来的数据进行操作。 2.WHERE: 从基表或视图中选择满足条件的元组。（不能使用聚合函数） 3.JOIN: 如right left 右连接-------从右边表中读取某个元组，并且找到该元组在左边表中对应的元组或元组集 4.ON:join on实现多表连接查询，推荐该种方式进行多表查询，不使用子查询。 5.GROUP BY：分组，一般和聚合函数一起使用。(聚合函数是将组中的行汇总为单个值的函数。例如， COUNT 、 MIN 和 MAX 都是聚合函数) 6.HAVING：在元组的基础上进行筛选，选出符合条件的元组。（一般与GROUP BY进行连用） 7.SELECT：查询到得所有元组需要罗列的哪些列。 8.DISTINCT：去重的功能 9.UNION：将多个查询结果合并（默认去掉重复的记录）。 10.ORDER BY：进行相应的排序。 11.LIMIT 1：显示输出一条数据记录（元组）

5.为什么要优化数据库？

1.系统的吞吐量瓶颈往往出现在数据库的访问速度上 2.随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢 3.数据是存放在磁盘上的，读写速度无法和内存相比

6.数据库结构优化

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

将字段很多的表分解成多个表 对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

增加中间表 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

增加冗余字段 设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

7.大表怎么优化？分库分表了是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下： 1.限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。 2.读/写分离：经典的数据库拆分方案，主库负责写，从库负责读； 3.缓存：使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；

还有就是通过分库分表的方式进行优化，主要有垂直分区、垂直分表和水平分区、水平分表

7.1.垂直分区

1.根据数据库里面数据表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。 2.简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。如下图所示，这样来说大家应该就更容易理解了。 垂直拆分的优点：可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。 垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

7.2.垂直分表

把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中 适用场景 1.如果一个表中某些列常用，另外一些列不常用 2.可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数

缺点 1.有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差 2.对于应用层来说，逻辑算法增加开发成本 3.管理冗余列，查询所有数据需要join操作

7.3.水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。

水平拆分能够支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨界点Join性能较差，逻辑复杂。

7.4.水平分表

表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数 适用场景 1、表中的数据本身就有独立性，例如表中分表记录各个地区的数据或者不同时期的数据，特别是有些数据常用，有些不常用。 2.需要把数据存放在多个介质上。

水平切分的缺点

1、给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作 2、在许多数据库应用中，这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数

8.索引越多越好吗

索引并不是建立越多越好，把索引比作目录，相当于，一本100页的书，如果有50页目录，书本这么厚，实际内容就那么少，谁还会去翻看，若在面试中被问到，可以根据以下三点来回答：

数据量小的表不需要建立索引,建立会增加额外的索引开销；数据变更需要维护索引,因此更多的索引意味着更多的维护成本；更多的索引意味着也需要更多的空间（索引也是需要空间来存放的）；

关于索引的数量：InnoDB存储引擎允许表有最多### 64个索引。

创建索引的实际例子： 1690786008700

9.索引知识

索引是什么？

索引是一种加速数据存取的数据对象，合理利用索引可以大大减少I/O请求次数，从而提高数据库的访问性能。

什么情况下用索引？

数据量很大，数据分布很广，要查询的数据占总数据的一小部分。列经常在where子句，或者连接条件中出现

什么情况下不能用索引？

数据量很小（表很小），列经常出现在where子句或者连接条件中，表数据频繁更新，oracle会频繁的重新改动索引，这样反而会降低数据库的性能。

索引的作用是什么？

提高查询效率，降低I/O次数，提高数据库性能。

创建索引一定会提高查询效率吗？

不一定，要看创建索引的合理性。

索引越多越好吗？

不是，索引也要占内存空间。过多的索引反而会降低数据库的性能。

补充：索引是什么

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。而且索引是一个文件，它是要占据物理空间的。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。

补充：索引的类型

1.单列索引：一个索引只包含一个列，一个表可以有多个单列索引，但是每个单列索引只能包含一个列。

2.多列索引（也称为复合索引、联合索引）：一个索引可以包含多个列，一个表可以有多个复合索引，复合索引的顺序也很重要。

3.主键索引：数据库表经常有一列或列组合，其值唯一地标识表中的每一行。该列称为表的主键。

4.唯一索引：与主键索引类似，但它没有主键索引的特殊限制。你可以使用唯一索引禁止在索引列中插入重复值。

5.全文索引：它的设计是用来在文本列中查找信息，而不是在字符列中查找信息。（类似搜索引擎）

10.索引下推

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。 MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：

1.存储引擎读取索引记录； 2.根据索引中的主键值，定位并读取完整的行记录； 3.存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程：

1.存储引擎读取索引记录（不是完整的行记录）； 2.判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录； 3.条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）； 4.存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。