面试笔记-数据库

最新推荐文章于 2021-02-02 00:38:44 发布

挨了牛逼的毒打

最新推荐文章于 2021-02-02 00:38:44 发布

阅读量223

点赞数 1

分类专栏：笔记

本文链接：https://blog.csdn.net/yauiel888/article/details/88310611

版权

笔记专栏收录该内容

17 篇文章 0 订阅

订阅专栏

数据库引擎

Mysql常用的存储引擎包括Innodb和Myisam以及memory引擎

Innodb更适合处理大量的高并发的数据，因为其良好的事务日志和故障恢复处理。数据库的大小决定了故障的恢复时间的长短，这会比较快，但是Myisam会需要几个小时

需要外键处理，那你就要选择Innodb，如果需要全文索引，那么MyIsam可能是一个比较好的选择，因为系统内建了这个全文的索引

MyISAM引擎：

不支持事务：MyISAM的引擎不支持事务，所以对事物要求的场景不适合
表级锁定：锁定机制是表级索引，这虽然让锁定的实现成本很小，但是也大大的降低了并发的性能
读写相互阻塞：在读取数据的时候，阻塞的写入数据，并且在写入数据的时候，也会阻塞读取数据
只会缓存索引：可以通过Key_buffer_size来设定缓存数据索引的大小，但是不会缓存数据块，这就增加了和IO的交换读取

使用场景：

不需要事务支持（不支持事务）
数据修改相对比较少（读写相互阻塞）
以读为主的
并发相对比较低（锁定机制）
数据一致性要求不是很高

Innodb引擎：

具有较好的事务支持，具备ACID的特性.
支持行级锁定，支持外键
能够缓存索引和数据，具有非常高效的索引缓存特性。
整个表和主键以cluster的方式进行存储，组成一棵平衡树。
所有的secondry index都会保存主键信息

适用场景：

适用于高并发的大量数据，数据性一致要求特别高的
需要事物支持（较好的事物支持）
行级锁定对高并发有很好的适应能力，但是需要确保查询是通过索引完成的
硬件设备的内存比较大，能较好的将数据的索引和数据块放到内存中，从而提高内存的缓存利用率，减少磁盘的IO

查询优化

Scheme设计与数据类型优化
创建高性能索引
索引相关的数据结构和算法

避免创建临时表，消耗性能

选取最适用的字段属性
使用连接（JOIN）来代替子查询(Sub-Queries)
使用联合(UNION)来代替手动创建的临时表
事务
锁定表
分表技术(水平分割、垂直分割)
添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引];全文索引，主要是针对对文件，文本的检索, 比如文章, 全文索引针对MyISAM有用.
优化的查询语句
任何地方都不要使用select * from user ，使用具体的字段列来代替"*".不要返回用不到的任何字段。
尽量使用表变量来代替临时表，如果表变量包含大量数据，请注意索引非常有限(只有主键索引)
避免频繁创建和删除临时表，以减少系统表资源消耗
如果使用到了临时表，在存储过程的最后务必将所有临时表显示删除，先truncate table，然后 drop table.这样可以避免系统表的较长时间锁定
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过一万行，那么就应该考虑改写了。
所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。
尽量避免大事务操作，提高系统并发能力。尽量避免大事务操作，提高系统并发能力。
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
读写[写: update/delete/add]分离
存储过程 [模块化编程，可以提高速度]
对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
mysql服务器硬件升级
定时的去清除不需要的数据,定时进行碎片整理(MyISAM)
Sql语句本身的优化
- 定位慢查询 show stauts like ‘com_select’ show stauts like ‘com_insert’ …类推 update delete;show status like ‘slow_queries’;
- 默认情况下，mysql认为10秒才是一个慢查询.
- 查询索引 show index(es) from 表名 show keys from 表名
- 索引的代价:占用磁盘空间;对dml操作有影响，变慢
在group by 后面增加 order by null 就可以防止排序.

Mybatis

Mybatis提供了一级缓存的方案来优化在数据库会话间重复查询的问题。实现的方式是每一个SqlSession中都持有了自己的缓存，一种是SESSION级别，即在一个Mybatis会话中执行的所有语句，都会共享这一个缓存。一种是STATEMENT级别，可以理解为缓存只对当前执行的这一个statement有效。

在二级缓存的使用中，一个namespace下的所有操作语句，都影响着同一个Cache，即二级缓存是被多个SqlSession共享着的，是一个全局的变量。
当开启缓存后，数据的查询执行的流程就是二级缓存 -> 一级缓存 -> 数据库。

<setting name="cacheEnabled" value="true"/>

临时表

当工作在非常大的表上时，你可能偶尔需要运行很多查询获得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后在这些表运行查询。

在创建临时表时，如果一次性插入数据量很大，那么可以使用select into 语句来代替create table，避免造成大量log日志，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后 insert.

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

)

如果你声明临时表是一个HEAP表,MySQL也允许你指定在内存中创建它:

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

) TYPE = HEAP

sql

INSERT INTO SELECT语句复制表 SELECT vale1, value2 into Table2 from Table1

要求目标表Table2必须存在

SELECT INTO FROM 语句形式为：SELECT vale1, value2 into Table2 from Table1

要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中

GROUP BY 的 WITH ROLLUP 是用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息；WITH ROLLUP 子句的 GROUP BY 语句时，不能再使用 ORDER BY 语句对结果集进行排序，如果对返回的结果顺序不满意，需要应用程序获得结果后在程序中进行排序

COUNT count(*)自动会优化指定到那一个字段，count(1)对应第一个字段，count(colum) 排除为空

优化：1.%号不放最左边(放在左边无法识别具体字段，索引无法使用)；2.使用复合索引

替换：

SELECT `column` from `table` where locate(‘keyword’, `condition`)>0
SELECT `column` from `table` where position(‘keyword’ IN `condition`)
SELECT `column` from `table` where instr(`condition`, ‘keyword’ )>0

instr(title,‘name’)>0 相当于 title like ‘%name%’

instr(title,‘name’)=1 相当于 title like ‘name%’

instr(title,‘name’)=0 相当于 title not like ‘%name%’

字段

尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
VARCHAR的长度只分配真正需要的空间
使用枚举或整数代替字符串类型
尽量使用TIMESTAMP而非DATETIME，
单表不要有太多字段，建议在20以内
避免使用NULL字段，很难查询优化且占用额外索引空间
用整型来存IP

索引

索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
字符字段只建前缀索引
字符字段最好不要做主键
不用外键，由程序保证约束
尽量不用UNIQUE，由程序保证约束
使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

查询SQL

可通过开启慢查询日志来找出较慢的SQL
不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
不用SELECT *
OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
不用函数和触发器，在应用程序实现
避免%xxx式查询
少用JOIN
使用同类型进行比较，比如用’123’和’123’比，123和123比
尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

升级硬件

Scale up，这个不多说了，根据MySQL是CPU密集型还是I/O密集型，通过提升CPU和内存、使用SSD，都能显著提升MySQL性能

读写分离

也是目前常用的优化，从库读主库写，一般不要采用双主或多主引入很多复杂性，尽量采用文中的其他方案来提高性能。同时目前很多拆分的解决方案同时也兼顾考虑了读写分离

缓存

缓存可以发生在这些层次：

MySQL内部：在系统调优参数介绍了相关设置
数据访问层：比如MyBatis针对SQL语句做缓存，而Hibernate可以精确到单个记录，这里缓存的对象主要是持久化对象Persistence Object
应用服务层：这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略，这里缓存的对象是数据传输对象Data Transfer Object
Web层：针对web页面做缓存
浏览器客户端：用户端的缓存

表分区

MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码
对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引

分区的好处是：

可以让单表存储更多的数据
分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
部分查询能够从查询条件确定只落在少数分区上，速度会很快
分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备
可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
可以备份和恢复单个分区
分区的限制和缺点：
一个表最多只能有1024个分区
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
分区表无法使用外键约束
NULL值会使分区过滤无效
所有分区必须使用相同的存储引擎

分区的类型：

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值

分区适合的场景有：

最适合的场景数据的时间序列性比较强，则可以按时间来分区
如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将热点数据单独放在一个分区，让这个分区的数据能够有机会都缓存在内存中，查询时只访问一个很小的分区表，能够有效使用索引和缓存
另外MySQL有一种早期的简单的分区实现 - 合并表（merge table），限制较多且缺乏优化，不建议使用，应该用新的分区机制来替代

垂直拆分

垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联

垂直拆分的优点是：

可以使得行数据变小，一个数据块(Block)就能存放更多的数据，在查询时就会减少I/O次数(每次查询时读取的Block 就少)
可以达到最大化利用Cache的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起
数据维护简单

缺点是：

主键出现冗余，需要管理冗余列
会引起表连接JOIN操作（增加CPU开销）可以通过在业务服务器上进行join来减少数据库压力
依然存在单表数据量过大的问题（需要水平拆分）
事务处理复杂

drop、truncate和delete的区别

DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

TRUNCATE 和DELETE只删除数据，DROP则删除整个表（结构和数据）

truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚；delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

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

外键

MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种

索引

对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高; select * from table1 where col1=A AND col2=B AND col3=D 如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引；一个查询可以只使用索引中的一部份，但只能是最左侧部分。

索引的缺点：