数据库优化

一、应用程序与数据库结合使用的三种方式

1、 python：调用存储过程
mysql：编写存储过程

2、 python：编写纯生sql
mysql:啥也不干

3、 python：ORM-》纯生sql
mysql：啥也不敢

二、事物

举例说明：

“A向B汇钱100”

1.读出A账号余额（500）。
2.A账号扣钱操作（500-100）。
3.结果写回A账号（400）。
4.读出B账号余额（500）。
5.B账号做加法操作（500+100）。
6.结果写回B账号（600）。

1、什么是事物？

事物是单个逻辑工作单元执行的一系列操作，可以被看作一个单元的一系列sql语句的集合。要么完全执行，要么全部不执行。

2、如果没有事物会有什么后果？

如果不对数据库进行并发控制，可能会产生脏读、非重复读、幻想读、丢失修改的异常情况。

脏读：事物A读取了事物B更新的数据，然后B回滚，那么A读到的数据就是脏数据。
不可重复读：事物A多次读取同一数据，事物B在事物A多次读取的过程中，对数据做了更新并提交，导致事物A多次读取的结果不一致。
幻读：A将数据库的某一字段进行修改，此时B在这时候插入了一条数据，当A发现这条数据没被改过来，像发生了幻觉一样。

3、事物的特性是什么？

原子性：保证1-6要么全部执行，要么全部不执行，如果中间异常了，回滚。
一致性：转账前和转账后，AB账户钱的总额相等。
隔离性：在转账过程中，只要事物还没有提交，查询A或B账户的时候，两个账户钱不会变化。
持久性：一但转账成功，事物提交，两个账户里面的钱就会真的发生变化，并且永久保存。

4、Mysql默认的隔离级别

Mysql默认隔离级别是 Repeatable read。
最高数据可隔离级别是 Serializable 序列化，在该级别下，事物串行化顺序执行，可以避免脏读，不可重复读与幻读。但是效率低，比较耗数据库性能，一般不用。

三、事物的隔离是怎么实现的

事物是基于锁实现的，按照锁的粒度分为以下3个

1、行级锁：只针对当前操作的行进行加锁。其加锁粒度最小，但开销最大。行级锁分为共享锁和排他锁。

特点：开销大，加锁慢；会出现死锁；锁定力度最小，发生锁冲突的概率最低，并发度也最高。

2、表级锁：是Mysql锁定粒度最大的一种，表示对当前的整张表加锁，他实现简单，资源消耗较少，被大部分Mysql引擎支持。最长使用的INNODB和MYISAM都支持表级锁。表级锁分为表共享读锁（共享锁）和表独占写锁（排他锁）。

特点：开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突的概率高，并发度最低。

3、页级锁：页级锁是位于行级锁和表级锁中间的一种锁，采取一种折中的方法。

特点：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度位于表锁和行锁之间，并发度一般。

四、死锁

1、什么是死锁？

死锁是两个或多个事物在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

2、常见解决死锁的方法：

1.如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁的机会。

2.在同一个事物中，尽可能做到一次锁定所需要的所有资源，减少死锁产生的概率。

3.对于非常容易产生死锁的业务部分，可以尝试升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

如果业务处理不好可以用分布式事务锁或者使用乐观锁。

五、乐观锁、悲观锁

悲观锁：悲观锁指对数据被意外修改持保守态度，依赖数据库原生支持的锁机制来保证当前事务处理的安全性，防止其他并发事务对目标数据的破坏或破坏其他并发事务数据，将在事务开始执行前或执行中申请锁定，执行完后再释放锁定。这对于长事务来讲，可能会严重影响系统的并发处理能力。 自带的数据库事务就是典型的悲观锁

乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。（一般是加一个版本号字段，每次更新时候比较版本号）

六、sql的生命周期，关键字的先后顺序

1、sql的生命周期：

1.应用服务器与数据库服务器建立一个连接
2.数据库进程拿到请求sql
3.解析并生成执行计划，执行
4.读取数据到内存并进行逻辑处理
5.通过步骤一的连接，发送结果到客户端
6.关掉连接，释放资源

2、关键字的执行顺序

1、 FROM：对 FROM 子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表 VT1。
2、 ON：对 VT1 应用 ON 筛选器，只有那些使为真才被插入到 TV2。
3、 OUTER (JOIN):如果指定了 OUTER JOIN(相对于 CROSS JOIN 或 INNER JOIN)，保留表中未找到
匹配的行将作为外部行添加到 VT2，生成 TV3。如果 FROM 子句包含两个以上的表，则对上一个联接生成的
结果表和下一个表重复执行步骤 1 到步骤 3，直到处理完所有的表位置。
4、 WHERE：对 TV3 应用 WHERE 筛选器，只有使为 true 的行才插入 TV4。
5、 GROUP BY：按 GROUP BY 子句中的列列表对 TV4 中的行进行分组，生成 TV5。
6、 CUTE|ROLLUP：把超组插入 VT5，生成 VT6。
7、 HAVING：对 VT6 应用 HAVING 筛选器，只有使为 true 的组插入到 VT7。
8、 SELECT：处理 SELECT 列表，产生 VT8。
9、 DISTINCT：将重复的行从 VT8 中删除，产品 VT9。
10、 ORDER BY：将 VT9 中的行按 ORDER BY 子句中的列列表顺序，生成一个游标(VC10)。
11、 TOP：从 VC10 的开始处选择指定数量或比例的行，生成表 TV11，并返回给调用者。

七、什么是数据库连接池

从上一个sql生命周期题目，可以看到其中的连接在里面发挥着重大作用，但频繁的创建和销毁，非常浪费系统资源。由于数据库更适合长连接，也就有个连接池，能对连接复用，维护连接对象、分配、管理、释放，也可以避免创建大量的连接对DB引发的各种问题；另外通过请求排队，也缓解对DB的冲击。

八、索引（键）

建索引：create index 索引名 on 表名（字段名）;

1、什么是数据库索引？

索引是数据库表中一列或多列的值进行排序的一种结构。类似于书的目录页和书的正文页之间的关系。索引是一个文件，他是要占据物理空间的。

2、索引有哪几种类型？

主键索引
唯一索引
普通索引
全文索引

3、什么是最左前缀原则？

顾名思义，就是最左边的优先，在创建多列索引时，根据业务需求，where子句中使用最频繁的一列放在最左边。

九、索引算法

1、BTree
Btree是最常用的数据库索引算法，也是mysql的默认算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，

2、Hash
Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

十、索引设计的原则：

1、适合索引的列是出现在where子句中的列，或者连接子句中指定的列

2、基数较小的类，索引效果较差，没有必要在此列建立索引

3、使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间

4、不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

十一、如何定位及优化sql语句的性能问题？

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划。

我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。

而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

十二、覆盖索引

覆盖索引：索引的数据覆盖了需要查询的所有数据

十三、in和exists的区别

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

十四、sql优化，分库分表

1、某个表有近千万数据，CRUD比较慢，如何优化？

数据千万级别之多，占用的存储空间也比较大，可想而知它不会存储在一块连续的物理空间上，而是链式存储在多个碎片的物理空间上。可能对于长字符串的比较，就用更多的时间查找与比较，这就导致用更多的时间。

可以做表拆分，减少单表字段数量，优化表结构。
在保证主键有效的情况下，检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致。

2、分库分表了是怎么做的？

垂直分表

也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的， 数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。 一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。
垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Producet一个库，订单Order一个库。 切分后，要放在多个服务器上，而不是一个服务器上。为什么？ 我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前， 全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上， 随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。 所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。
数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。 数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。 数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

水平分表

针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。
水平分库分表
将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。
水平分库分表切分规则

RANGE从
0到10000一个表，10001到20000一个表；
HASH取模
一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。
地理区域
比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。
时间
按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

3、分表分库了有什么问题？

事务支持
分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

跨库join
只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。
分库分表方案产品

跨节点的count,order by,group by以及聚合函数问题
这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

数据迁移，容量规划，扩容等问题
来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。

ID问题
一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由.
一些常见的主键生成策略

UUID
使用UUID作主键是最简单的方案，但是缺点也是非常明显的。由于UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。
Twitter的分布式自增ID算法Snowflake
在分布式系统中，需要生成全局UID的场合还是比较多的，twitter的snowflake解决了这种需求，实现也还是很简单的，除去配置信息，核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。

跨分片的排序分页
般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。如下

4、有用到中间件么?他们的原理知道么？