数据库

使用索引有哪些需要注意的地方？

创建索引的的字段尽量小，最好是数值，比如整形int等；
对于频繁修改的字段，尽量不要创建索引，维护索引的成本很高，而且更容易产生索引碎片；
定期的索引维护，如索引碎片的修复等；
不要建立或维护不必要的重复索引，会增加修改数据（新增、修改、删除数据）的成本；
使用唯一性高的字段创建索引，切不可在性别这样的低唯一性的字段上创建索引；
在SQL语句中，尽量不要在Where条件中使用函数、运算符或表达式计算，会造成索引无法正常使用；
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描；
应尽量避免在 where 子句中使用!=或<>操作符，否则将导致引擎放弃使用索引而进行全表扫描；

引碎片是如何产生的？有什么危害？又该如何处理？

索引在使用一段时间后（主要是新增、修改、删除数据，如果该页已经存储满了，就要进行页的拆分，频繁的拆分，会产生较多的索引碎片）会产生索引碎片。
索引碎片会严重印象数据的查询效率，如果碎片太多，索引可能不会被使用。
碎片的处理方式主要有两种：
1. 第一种是预防：设置页的填充因子 意思就是在页上设置一段空白区域，在新增数据的时候，可以使用这段空白区域，可以一定的避免页的拆分，从而减少索引碎片的产生。填充因子就是用来描述这种页中填充数据的一个比例，一般默认是100%填充的。如果我们修改填充因子为80%，那么页在存储数据时，就会剩余20%的剩余空间，这样在下次插入的时候就不会拆分页了。那么是不是我们可以把填充因子设置低一点，留更多的剩余空间，不是很好嘛？当然也不好，填充因子设置的低，会需要分配更多的存储空间，叶子节点的深度会增加，这样是会影响查询效率的，因此，这是要根据实际情况而定的。那么一般我们是怎么设置填充因子的呢，主要根据表的读写比例而定的。如果读的多，填充因子可以设置高一点，如100%，读写各一半，可以 80 ~ 90%；修改多可以设置50 ~ 70%。
2. 第二种是索引修复：定期对索引进行检查、维护，写一段SQL检查索引的碎片比例，如果碎片过多，进行碎片修复或重建，定期执行即可。具体可以参考本文末尾的相关参考资料。

锁的目的是什么？

主要解决多个用户同时对数据库的并发操作时会带来以下数据不一致的问题：

丢失更新，同时修改一条数据
读脏，A修改了数据后，B读取后A又取消了修改，B读脏
不可重复读，A用户读取数据,随后B用户读取该数据并修改,此时A用户再读取数据时发现前后两次的值不一致
还有一种是幻读，这个情况好像不多。

并发控制的主要方法是封锁,锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致

锁的粒度有哪些？

数据库锁：锁定整个数据库，这通常发生在整个数据库模式改变的时候。
表锁：锁定整个表，这包含了与该表相关联的所有数据相关的对象，包括实际的数据行(每一行)以及与该表相关联的所有索引中的键。
区段锁：锁定整个区段，因为一个区段由8页组成，所以区段锁定是指锁定控制了区段、控制了该区段内8个数据或索引页以及这8页中的所有数据行。
页锁：锁定该页中的所有数据或索引键。
行或行标识符：虽然从技术上，锁是放在行标识符上的，但是本质上，它锁定了整个数据行。

什么是事务？什么是锁？

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上个节点。为了确保要么执行，要么不执行，就可以使用事务。要将所有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。
锁是实现事务的关键，锁可以保证事务的完整性和并发性。

视图的作用，视图可以更改么？

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。
视图的目的在于简化检索，保护数据，并不用于更新。

什么是触发器(trigger)？触发器有什么作用？

触发器是数据库中由一定时间触发的特殊的存储过程，他不是由程序掉用也不是手工启动的。触发器的执行可以由对一个表的insert,delete, update等操作来触发，触发器经常用于加强数据的完整性约束和业务规则等等。

SQL里面IN比较快还是EXISTS比较快？

这个题不能一概而论，要根据具体情况来看。IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。

有几种不同的join?

Inner join, left join, right join, full join.

Inner join, 就是只要有一个列能够匹配，就简单的返回两个table中所有的对应行。

select columnnames from tablename1 inner join tablename2 on columnname1=columnname2

Left join也就是left outer join。当有一个列能够匹配时就返回左边表中所有的行。

select columnnames from tablename1 left join tablename2 on columnname1=columnname2

Right join也就是right outer join，当有列匹配时，返回右边表格中所有的行。

select columnnames from tablename1 right join tablename2 on columnname1=columnname2

Full join也就是full outer join, 当有匹配时，会返回左边表格和右边表格任意情况下的行组合。

Delete和truncate的区别是什么?

Delete是dml，truncate是ddl。

Delete是用来删除一行或者多行。Truncate是用来删除一个表中的所有行。

我们可以用where跟delete结合使用。Truncate不可以。

Drop跟truncate的区别是什么?

truncate是删除表中所有的行，drop是删除整张表。这两个操作都不可以回撤。

什么是SQL？

SQL（结构化查询语言）是一种设计用于检索和操作数据的数据库。它属于美国国家标准协会（ANSI）的一种标准，可用于执行Select（选择）、Update（更新）、Delete（删除）和Insert（插入）等数据任务。

SQL中的Constraints(约束)是什么？

它可用于设置表中数据类型的限制。在创建或更新表语句时，可以使用约束。一些限制是：

NOT NULL、PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、DEFAULT

Clustered（群集）和Non-Clustered Index（非群集）索引之间有什么区别？

聚集索引——有助于轻松检索数据，并且只有一个聚集索引与一个表一起分配。它会更改记录在数据库中的保存方式。
非聚集索引——与聚集索引相比，非聚集索引很慢。并且在非集群索引的情况下，该表可以具有多个索引，为表创建一个对象，该表是搜索后指向表的一个点。

什么是Aggregate Functions（聚合函数）？

它是一个返回单个值的数学函数。SQL中的聚合函数是：

AVG（）——返回平均值
COUNT（）——返回行数
MAX（）——返回最大值
MIN（）——返回最小值
ROUND（）——基于十进制规范，此函数对数字字段进行舍入
SUM（）——返回总和

主键为什么不推荐有业务含义?

最好是主键是无意义的自增ID，然后另外创建一个业务主键ID，因为任何有业务含义的列都有改变的可能性,主键一旦带上了业务含义，那么主键就有可能发生变更。主键一旦发生变更，该数据在磁盘上的存储位置就会发生变更，有可能会引发页分裂，产生空间碎片。

还有就是，带有业务含义的主键，不一定是顺序自增的。那么就会导致数据的插入顺序，并不能保证后面插入数据的主键一定比前面的数据大。如果出现了，后面插入数据的主键比前面的小，就有可能引发页分裂，产生空间碎片。

货币字段用什么类型?

货币字段一般都用 Decimal类型， float和double是以二进制存储的，数据大的时候，可能存在误差。

索引应该建在那些列上？

在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

什么是存储过程？有哪些优缺点？

存储过程就像我们编程语言中的函数一样，封装了我们的代码(PLSQL、T-SQL)

存储过程的优点

能够将代码封装起来
保存在数据库之中
让编程语言进行调用
存储过程是一个预编译的代码块，执行效率比较高
一个存储过程替代大量T_SQL语句，可以降低网络通信量，提高通信速率

存储过程的缺点：

每个数据库的存储过程语法几乎都不一样，十分难以维护（不通用）
业务逻辑放在数据库上，难以迭代

索引的分类有哪些？

唯一索引：唯一索引不允许两行具有相同的索引值
主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

什么是最左前缀原则？

最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; // 可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

什么是事务？

事务简单来说：一个Session中所进行所有的操作，要么同时成功，要么同时失败

ACID — 数据库事务正确执行的四个基本要素包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易。

举个例子:A向B转账，转账这个流程中如果出现问题，事务可以让数据恢复成原来一样【A账户的钱没变，B账户的钱也没变】。

什么情况下应不建或少建索引?

表记录太少
经常插入、删除、修改的表
数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
经常和主字段一块查询但主字段索引值比较多的表字段

维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？

尽可能使用约束，如check,主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。

最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。

游标是什么？

是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

数据库NULL是什么意思?

NULL(空)这个值是数据库世界里一个非常难缠的东西。

NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。假设您的SQL Server数据库里有ANSI_NULLS，当然在默认情况下会有，对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个 UNKNOWN值进行比较，并在逻辑上希望获得一个答案。您必须使用IS NULL操作符。

关系型数据库和非关系型数据库区别？

关系型数据库的优点：

容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；
使用方便：通用的SQL语言使得操作关系型数据库非常方便；
易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；
支持SQL，可用于复杂的查询。
支持事务

关系型数据库的缺点：

为了维护一致性所付出的巨大代价就是其读写性能比较差；
固定的表结构；
不支持高并发读写需求；
不支持海量数据的高效率读写

非关系型数据库优点：

无需经过sql层的解析，读写性能很高
基于键值对，数据没有耦合性，容易扩展
存储数据的格式：nosql的存储格式是key,value形式

非关系型数据库缺点：不提供sql

查询语句的执行先后顺序？

查询中用到的关键词主要包含六个，并且他们的顺序依次为 select--from--where--group by--having--order by

其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行

from:需要从哪个数据表检索数据
where:过滤表中数据的条件
group by:如何将上面过滤出的数据分组
having:对上面已经分组的数据进行过滤的条件
select:查看结果集中的哪个列，或列的计算结果
order by :按照什么样的顺序来查看返回的数据

780135-20210218103805947-1482607422

FROM：对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1
ON:对vt1表应用ON筛选器只有满足为真的行才被插入vt2
OUTER(join)：如果指定了 OUTER JOIN保留表(preserved table)中未找到的行将行作为外部行添加到vt2 生成t3如果from包含两个以上表则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束
WHERE：对vt3应用 WHERE 筛选器只有使为true的行才被插入vt4
GROUP BY：按GROUP BY子句中的列列表对vt4中的行分组生成vt5
CUBE|ROLLUP：把超组(supergroups)插入vt6 生成vt6
HAVING：对vt6应用HAVING筛选器只有使为true的组才插入vt7
SELECT：处理select列表产生vt8
DISTINCT：将重复的行从vt8中去除产生vt9
ORDER BY：将vt9的行按order by子句中的列列表排序生成一个游标vc10
TOP：从vc10的开始处选择指定数量或比例的行生成vt11 并返回调用者

什么情况下会造成死锁？

所谓死锁: 是指两个或两个以上的进程在执行过程中。因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序。

死锁如何解决？

查出的线程杀死 kill

SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;

设置锁的超时时间 Innodb 行锁的等待时间，单位秒。可在会话级别设置，RDS 实例该参数的默认值为 50（秒）。

生产环境不推荐使用过大的 innodb_lock_wait_timeout参数值

该参数支持在会话级别修改，方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间，如下：

set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间，单位秒。

触发器的作用？

触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

在数据库中查询语句速度很慢，如何优化？

建索引
减少表之间的关联
优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引，把数据量大的表排在前面
简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据
尽量用PreparedStatement来查询，不要用Statement

PL/SQL是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL 只有 Oracle 数据库有。 MySQL 目前不支持 PL/SQL 的。

使用SQL命令将教师表teacher中工资salary字段的值增加500，应该使用的命令?

Update teacher set salary=salary+500

delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;

如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

不再需要一张表的时候，用drop

想删除部分数据行时候，用delete，并且带上where子句

保留表而删除所有数据的时候用truncate

有哪几种类型的SQL命令？

SQL命令分为以下类型：

DDL（数据定义语言） - 用于定义数据库的结构。
DCL（数据控制语言） - 用于为用户提供权限。
DML（数据操作语言） - 用于管理数据。
DQL（数据查询语言） - 所有命令都在SQL中，用于检索DQL中的数据。
TCL（事务控制语言） - 用于管理DML所做的更改。

数据库

On this page