数据库MySQL

2021-02-22  本文已影响0人  向梦而来

[TOC]

数据库MySQL

数据库的三范式是什么？什么是反模式？

作用：数据库范式可以避免数据冗余，减少数据库的空间，并且减轻维护数据完整性的麻烦。

第一范式（1NF）

第一范式，强调属性的原子性约束，要求属性具有原子性，不可再分解。

举个例子，活动表（活动编码，活动名称，活动地址），假设这个场景中，活动地址可以细分为国家、省份、城市、市区、位置，那么就没有达到第一范式。

第二范式（2NF）

第二范式，强调记录的唯一性约束，表必须有一个主键，并且没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

举个例子，版本表（版本编码，版本名称，产品编码，产品名称），其中主键是（版本编码，产品编码），这个场景中，数据库设计并不符合第二范式，因为产品名称只依赖于产品编码。存在部分依赖。所以，为了使其满足第二范式，可以改造成两个表：版本表（版本编码，产品编码）和产品表（产品编码，产品名称）。

第三范式（3NF）

第三范式，强调属性冗余性的约束，即非主键列必须直接依赖于主键。

举个例子，订单表（订单编码，顾客编码，顾客名称），其中主键是（订单编码），这个场景中，顾客编码、顾客名称都完全依赖于主键，因此符合第二范式，但是顾客名称依赖于顾客编码，从而间接依赖于主键，所以不能满足第三范式。为了使其满足第三范式，可以拆分两个表：订单表（订单编码，顾客编码）和顾客表（顾客编码，顾客名称），拆分后的数据库设计，就可以完全满足第三范式的要求了。

值得注意的是，第二范式的侧重点是非主键列是否完全依赖于主键，还是依赖于主键的一部分。第三范式的侧重点是非主键列是直接依赖于主键，还是直接依赖于非主键列。

反模式

范式可以避免数据冗余，减少数据库的空间，减轻维护数据完整性的麻烦。
然而，通过数据库范式化设计，将导致数据库业务涉及的表变多，并且可能需要将涉及的业务表进行多表连接查询，这样将导致性能变差，且不利于分库分表。因此，出于性能优先的考量，可能在数据库的结构中需要使用反模式的设计，即空间换取时间，采取数据冗余的方式避免表之间的关联查询。至于数据一致性问题，因为难以满足数据强一致性，一般情况下，使存储数据尽可能达到用户一致，保证系统经过一段较短的时间的自我恢复和修正，数据最终达到一致。

MySQL 有哪些数据类型？

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

MySQL支持所有标准SQL数值数据类型。包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

字符串类型

字符串类型指CHAR（定长字符串）、VARCHAR（变长字符串）、BINARY、VARBINARY、BLOB（二进制大对象）、TEXT、ENUM和SET。
4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT

MySQL数据类型常见问题

1、MySQL 中 varchar 与 char 的区别？varchar(50) 中的 50 代表的涵义？

2、int(11) 中的 11 代表什么涵义？

int(11) 中的 11 ，不影响字段存储的范围，只影响展示效果，当数字不足11位时，前面会用0补齐.

3、金额(金钱)相关的数据，选择什么数据类型？

4、一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，再把 MySQL 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15？

一般情况下，我们创建的表的类型是 InnoDB ，如果新增一条记录（不重启 MySQL 的情况下），这条记录的 ID 是18 ；但是如果重启 MySQL 的话，这条记录的 ID 是 15 。因为 InnoDB 表只把自增主键的最大 ID 记录到内存中，所以重启数据库或者对表 OPTIMIZE 操作，都会使最大 ID 丢失。
但是，如果我们使用表的类型是 MyISAM ，那么这条记录的 ID 就是 18 。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里面，重启 MYSQL 后，自增主键的最大 ID 也不会丢失。
最后，还可以跟面试官装个 x ，生产数据，不建议进行物理删除记录。

5、表中有大字段 X(例如：text 类型)，且字段 X 不会经常更新，以读为为主，请问您是选择拆成子表，还是继续放一起?写出您这样选择的理由

实际场景下，例如说商品表数据量比较大的情况下，会将商品描述单独存储到一个表中。即，使用拆的方案。

MySQL 有哪些存储引擎？

InnoDB
MyISAM
MRG_MYISAM
MEMORY
CSV
ARCHIVE
BLACKHOLE
PERFORMANCE_SCHEMA
FEDERATED
....................

如何选择合适的存储引擎？

提供几个选择标准，然后按照标准，选择对应的存储引擎即可。一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。
1.是否需要支持事务。
2.对索引和缓存的支持。
3.是否需要使用热备。
4.崩溃恢复，能否接受崩溃。
5.存储的限制。

为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢？

对于 SELECT COUNT(*) FROM table 语句，在没有 WHERE 条件的情况下，InnoDB 比 MyISAM 可能会慢很多，尤其在大表的情况下。因为，InnoDB 是去实时统计结果，会全表扫描；而 MyISAM 内部维持了一个计数器，预存了结果，所以直接返回即可。

什么是索引？

索引，类似于书籍目录，想找到一本书的某个特定的主题，需要先找到书的目录，定位对应的页码。
MySQL 中存储引擎使用类似的方式进行查询，先去索引中查找对应的值，然后根据匹配的索引找到对应的数据行。

1.索引有什么好处？

1.提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
2.降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本。

2.索引有什么坏处？

1.占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。
2.降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

3.索引的使用场景？

1、对非常小的表，大部分情况下全表扫描效率更高。
2、对中大型表，索引非常有效。
3、特大型的表，建立和使用索引的代价随着增长，可以使用分区技术来解决。

4.索引的类型？

索引，都是实现在存储引擎层的。主要有六种类型：
1、普通索引：最基本的索引，没有任何约束。
2、唯一索引：与普通索引类似，但具有唯一性约束。
3、主键索引：特殊的唯一索引，不允许有空值。
4、复合索引：将多个列组合在一起创建索引，可以覆盖多个列。
5、外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。
6、全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎。

5.MySQL 索引的“创建”原则？

1、最适合索引的列是出现在 WHERE 子句中的列，或连接子句中的列，而不是出现在 SELECT 关键字后的列。
2、索引列的基数（不重复的索引值的数量）越大，索引效果越好。
3、根据情况创建复合索引，复合索引可以提高查询效率。因为复合索引的基数会更大。
4、避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
5、主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。
6、对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间。

6.MySQL 索引的“使用”注意事项？

1、应尽量避免在 WHERE 子句中使用 != 或 <> 或 not in操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。column IS NULL 也是不可以使用索引的。
2、应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT id FROM t WHERE num = 10 OR num = 20 。
3、应尽量避免在 WHERE 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
4、应尽量避免在 WHERE 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
5、不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
6、复合索引遵循前缀原则。
7、如果 MySQL 评估使用索引比全表扫描更慢，会放弃使用索引。如果此时想要索引，可以在语句中添加强制索引。
8、列类型是字符串类型，查询时一定要给值加引号，否则索引失效。
9、LIKE 查询，% 不能在前，因为无法使用索引。如果需要模糊匹配，可以使用全文索引。

7.以下三条 SQL 如何建索引，只建一条怎么建？

WHERE a = 1 AND b = 1
WHERE b = 1
WHERE b = 1 ORDER BY time DESC

以顺序 b , a, time 建立复合索引，CREATE INDEX table1_b_a_time ON index_test01(b, a, time)。
对于第一条 SQL ，因为最新 MySQL 版本会优化 WHERE 子句后面的列顺序，以匹配复合索引顺序。

8.想知道一个查询用到了哪个索引，如何查看?

EXPLAIN 显示了 MYSQL 如何使用索引来处理 SELECT 语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。在 SELECT 语句前加上 EXPLAIN 就可以了。

MySQL 索引的原理？

什么是 B-Tree 索引

B-Tree 是为磁盘等外存储设备设计的一种平衡查找树。B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组 [key, data] ，key 为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵 m 阶的 B-Tree 有如下特性：

  1. 每个节点最多有 m 个孩子。
    • 除了根节点和叶子节点外，其它每个节点至少有 Ceil(m/2) 个孩子。
    • 若根节点不是叶子节点，则至少有 2 个孩子。
  2. 所有叶子节点都在同一层，且不包含其它关键字信息。
  3. 每个非叶子节点包含 n 个关键字信息（P0,P1,…Pn, k1,…kn）
    • 关键字的个数 n 满足：ceil(m/2)-1 <= n <= m-1
    • ki(i=1,…n) 为关键字，且关键字升序排序。
    • Pi(i=0,…n) 为指向子树根节点的指针。P(i-1) 指向的子树的所有节点关键字均小于 ki ，但都大于 k(i-1) 。

B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个 3 阶的 B-Tree：

B-Tree 的结构

模拟查找 key 为 29 的过程：

分析上面过程，发现需要 3 次磁盘 I/O 操作，和 3 次内存查找操作。由于内存中的 key 是一个有序表结构，可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。B-Tree 相对于 AVLTree 缩减了节点个数，使每次磁盘 I/O 取到内存的数据都发挥了作用，从而提高了查询效率。

什么是 B+Tree 索引

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用 B+Tree 实现其索引结构。

B+Tree 相对于 B-Tree 有几点不同：

将 B-Tree 优化，由于 B+Tree 的非叶子节点只存储键值信息，假设每个磁盘块能存储 4 个键值及指针信息，则变成 B+Tree 后其结构如下图所示：

B+Tree 的结构

可能上面例子中只有 22 条数据记录，看不出 B+Tree 的优点，下面做一个推算：

B+Tree 有哪些索引类型

在 B+Tree 中，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，需要进过两步：

什么是索引的最左匹配特性？

当 B+Tree 的数据项是复合的数据结构，比如索引 (name, age, sex) 的时候，B+Tree 是按照从左到右的顺序来建立搜索树的。

这个是非常重要的性质，即索引的最左匹配特性。

MyISAM 索引实现

MyISAM 索引的实现，和 InnoDB 索引的实现是一样使用 B+Tree ，差别在于 MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

1）主键索引：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图：

主键索引

2）辅助索引：

在 MyISAM 中，主索引和辅助索引在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引，则此索引的结构如下图所示：

辅助索引

MyISAM 的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB 的聚集索引区分。

MyISAM 索引与 InnoDB 索引的区别

什么是事务

事务就是对一系列的数据库操作（比如插入多条数据）进行统一的提交或回滚操作，如果插入成功，那么一起成功，如果中间有一条出现异常，那么回滚之前的所有操作。
这样可以防止出现脏数据，防止数据库数据出现问题。

事务的特性？

事务的特性是ACID


image.png
  1. 原子性 Atomicity ：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
  2. 一致性 Consistency ：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
  3. 隔离性 Isolation ：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  4. 持久性 Durability ：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的并发问题？

实际场景下，事务并不是串行的，所以会带来如下三个问题：
1、脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
3、幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

请说说 MySQL 的四种事务隔离级别？

事务定义了四种事务隔离级别，不同数据库在实现时，产生的并发问题是不同的。
不同的隔离级别有不同的现象，并有不同的锁定/并发机制，隔离级别越高，数据库的并发性就越差。

Innodb 的事务与日志的实现方式

请说说 MySQL 的锁机制？

当多个查询同一时刻进行数据修改时，就会产生并发控制的问题。MySQL 的共享锁和排他锁，就是读锁和写锁。

锁的粒度？

表锁：系统开销最小，会锁定整张表，MyIsam 使用表锁。
行锁：最大程度的支持并发处理，但是也带来了最大的锁开销，InnoDB 使用行锁。

什么是悲观锁？什么是乐观锁？

1.悲观锁

指的是对数据被外界修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。
在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。
悲观锁，就是我们上面看到的共享锁和排他锁。

2.乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。
而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本Version记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。
乐观锁实际就是通过版本号，从而实现 CAS 原子性更新。

什么是死锁？

如果一个资源被锁定，它总会在以后某个时间被释放。而死锁发生在当多个进程访问同一数据库时，其中每个进程拥有的锁都是其他进程所需的，由此造成每个进程都无法继续下去。简单的说，进程 A 等待进程 B 释放他的资源，B 又等待 A 释放他的资源，这样就互相等待就形成死锁。

虽然进程在运行过程中，可能发生死锁，但死锁的发生也必须具备一定的条件，死锁的发生必须具备以下四个必要条件：

如何最大限度地降低死锁

MySQL 中 InnoDB 引擎的行锁是通过加在什么上实现的？为什么是这样子的？？

InnoDB 是基于索引来完成行锁。例如：SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE 。
FOR UPDATE 可以根据条件来完成行锁锁定，并且 id 是有索引键的列,如果 id 不是索引键那么 InnoDB 将完成表锁，并发将无从谈起。

Innodb 的行锁是怎么实现的？

Innodb 的锁的策略为 next-key 锁，即 record lock + gap lock ，是通过在 index 上加 lock 实现的。
如果 index 为 unique index ，则降级为 record lock 行锁。
如果是普通 index ，则为 next-key lock 。
如果没有 index ，则直接锁住全表，即表锁。

MySQL 查询执行顺序？

(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
在SQL语句的执行过程中，每一步都会产生一个虚拟表（Virtual Table，简称VT），用来保存SQL语句的执行结果

当向MySQL发送一个请求的时候，MySQL到底做了些什么呢

image.png

什么是 MVCC ？

多版本并发控制（MVCC），是一种用来解决读（快照读）-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。快照读就是MVCC思想在MySQL的具体非阻塞读功能实现。

MVCC主要是为Repeatable-Read事务隔离级别做的。在此隔离级别下，A、B事务所示的数据相互隔离，互相更新不可见。

什么是MySQL InnoDB下的当前读和快照读?

MySQL 数据库 CPU 飙升到 500% 的话，怎么处理？

编写 SQL 查询语句的考题合集

《10 道 MySQL 查询语句面试题》

聊聊 MySQL SQL 优化？

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0
3.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20
5.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3
6.避免使用通配符。下面的查询也将导致全表扫描：select id from t where name like ‘李%’若要提高效率，可以考虑全文检索。
7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num
8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=1002
9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=’abc’ ，name以abc开头的id应改为:select id from t where name like ‘abc%’
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12.不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(…)
13.很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)
14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了insert 及 update 的 效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储 顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
19.任何地方都不要使用 select * from t ，用具体的字段列表代替“”，不要返回用不到的任何字段。
20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
21.避免频繁创建和删除临时表，以减少系统表资源的消耗。
22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。
29.尽量避免大事务操作，提高系统并发能力。
30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

上一篇 下一篇

猜你喜欢

热点阅读