如何对数据库进行优化-网站排名进行优化

SQL 1. 分页

限制条款

# 在所有的查询结果中，返回前5行记录。
SELECT prod_name FROM products LIMIT 5;
# 在所有的查询结果中，从第5行开始，返回5行记录。
SELECT prod_name FROM products LIMIT 5,5;

优化限制分页：

当偏移量很大时，比如LIMIT 90000,20，这样的查询。 MySQL需要查询90020条记录，然后只返回最后20条记录，前面的90000条记录被丢弃。

SELECT * FROM pms_spu_info ORDER BY id DESC LIMIT 800000,10;  # 0.766s
# 利用索引覆盖， 查询800001条索引树的id 然后 利用索引从第800001位置开始查， 查10条数据就欧克
SELECT * FROM pms_spu_info  
WHERE id > (SELECT id FROM pms_spu_info  LIMIT 800000, 1)
ORDER BY ID DESC
LIMIT 10;  # 0.234
# 使用BETWEEN  AND 更快
SELECT * FROM pms_spu_info  
WHERE id BETWEEN 1000021 AND 1000030 #0.031

需要注意的一点是LIMIT的offset不能太大

2.功能

常用的聚合函数有COUNT()、AVG()、SUM()、MAX()、MIN()
LEFT RIGHT INNER 表关联： LEFT JOIN ON   
DISTINCT 去重
GROUP BY 分组， 多行变多列用GROUP BY
UNION 拼接， 多列变多行用 UNION    UNION效率高于 UNION ALL， UNION ALL不会合并重复的记录行

3. WHERE 和 HAVING 有什么区别？

WHERE 是约束语句。 使用 WHERE 来约束来自数据库的数据。 WHERE 在返回结果之前起作用。 不能在 WHERE 中使用聚合函数。

HAVING是过滤语句，是在查询返回结果集后，对查询结果进行的过滤操作。 HAVING中只能使用分组字段和聚合函数

从性能的角度来看，如果在HAVING子句中使用了分组字段作为过滤条件，应该换成WHERE子句。 因为WHERE可以在进行分组操作和计算聚合函数之前过滤掉不需要的数据，所以性能会更好。

4.SQL注入的理解

SQL注入的原理是将SQL代码伪装成输入参数，传递给服务器解析执行的一种攻击方式。 也就是说，在服务器端发起的一些请求参数中植入了一些SQL代码。 服务器端在执行SQL操作时，会拼接相应的参数，同时拼接SQL注入攻击的一些“SQL”，从而导致执行一些意想不到的操作。

例子：

有一个登录功能，客户发送用户名密码ls和123456，我们可以执行这条SQL语句：

SELECT * FROM user WHERE username = 'ls' AND password = '123456

在 SQL 中，# 和 – 之后的字符串将被视为注释。 如果我们使用'or 1=1#作为用户名参数，那么服务器构造的SQL语句如下：

SELECT * FROM user WHERE username = ' ' or 1=1 # AND password = '123456

而#会忽略后面的语句，而1=1是常量条件，所以这条SQL会查询所有登录的用户

如果输入 ' or 1=1;delete * from users; #，会删除整张表，非常危险

SQL注入的解决方法

严格的参数校验，不应该校验的特殊字符 SQL预编译，变量使用占位符作为占位符，当绑定的参数传递给MySQL服务器时，MySQL服务器对参数进行编译，即填充到对应的过程中占位符，转义操作就完成了。Index 1.说说对index的理解

索引是一种数据结构，它将数据库表中一个或多个列中的数据进行排序，并单独存储在磁盘上，包括指向数据库表中记录的指针。 可以加快数据检索。

索引在存储引擎中实现。 底层数据结构包括B+树和Hash表。 MyISAM和InnoDB存储引擎索引是B+树，Memory是Hash表索引。

加入索引有很多好处，例如：

通过创建唯一索引，可以保证数据库中每一行数据的唯一性，可以大大加快查询速度，加快表与表之间的连接。 当使用分组和排序子句进行数据查询时，可以显着减少查询中分组和排序的时间。

添加索引也有缺点：索引也需要占用磁盘空间，创建和维护索引都需要时间，而且数据量越大越费时

2.指标分类

主键索引、唯一索引、普通索引、复合索引、全文索引

3、如何判断是否添加索引，添加索引后是否使用？

是否要添加索引：

如果唯一性是某个数据本身的特征，则指定一个唯一性索引。 唯一索引保证了数据的唯一性和完整性，不会有空值，可以提高查询速度

在经常查询、排序或分组的列上构建索引。 如果有多个列需要排序，可以建立复合索引

用外键定义的数据必须被索引

添加索引会有用吗？索引失败

不确定，

使用复合索引时，不要遵循最左前缀原则对索引列进行计算、函数和类型转换。 mysql在使用not equal (!= or) Like wildcard开头时可能不会使用索引： %abc string without single quotes （发生了索引列的隐式转换） 如果条件中有or，只要一个字段没有索引，其他字段如果有索引就不会被使用

如果一个字段没有索引，会导致全表扫描+索引扫描+结果聚合； 因此，效率不如直接全表扫描； dbms会直接放弃索引； 全表扫描比数据少的索引快，不会用到索引 4 。 哪些字段不适合做索引？经常更新的字段。 Where 条件中未使用的字段。 数据相对较少的表。 不能有效区分数据的列不适合做索引。 例如，计算中涉及性别的列不适合建立索引。 五、索引实现原理

MyISAM 引擎使用 B+ 树作为索引结构。 叶节点的数据字段存储数据记录的地址。 主索引和辅助索引的结构没有区别。

InnoDB引擎也是使用B+树作为索引结构，但是InnoDB数据文本本身就是一个索引文件，表数据本身就是一个B+树组织的索引结构。 叶子节点存储完整的数据记录，这个索引的键就是主键。 换句话说，InnoDB的主键索引是一个聚集索引，其他索引也存储了节点上主键的值。 所以如果辅助索引不满足索引覆盖的条件，就得回表查询。 InnoBD 要求表必须有一个主键。 如果没有明确指定，MySQL会自动选择一个能唯一标识数据的列作为主键。 如果没有这样的列，将自动生成一个隐式字段作为该表的主键。 该字段的长度为 6 个字节，类型为长整型（BIGINT）

6、MySQL为什么要使用B+树

B+树是由B树和索引顺序访问方式演化而来的，非常适合存储。

B树是一种自平衡树如何对数据库进行优化，它维护有序的数据，并允许在对数时间内进行增删改查。 可以有多个子节点，树高低，减少磁盘IO次数。

B 树的内部节点和叶节点同时存储键和值。 将经常访问的数据放在靠近根节点的位置，可以大大提高数据查询效率。

B+树的内部节点都是key，不是value。 这样读可以读到内存中更多的key，可以让B+树更低。

B+树的叶子节点存储key和value，查询效率稳定。 所有关键字查询都必须走一条从根节点到叶节点的路径，并且每条关键字路径的长度是相同的。

B+树的叶子节点之间用链连接。 这样，在进行范围遍历或者全数据遍历时，只需要找到最小的节点，然后遍历链序即可。

7.什么是联合索引存储结构？

联合索引存储结构也是B+数据，每个节点存储的键值个数大于等于2。只有在查询中用到这些字段的最左边时，才使用索引。 即遵循最左匹配原则，

事务 1. 谈谈您对数据库事务的理解？

一个事务可以由一条或一组SQL语句组成，事务中的操作要么执行，要么不执行。 事务是数据库并发控制的基本单位，执行结果不一定非得从一种状态到另一种一致状态。

事务需要遵循ACID属性。原子性、一致性、隔离性、持久性

2、如何实现ACID特性？

原子性实现原理：undo log

原子性要么全部成功，要么都不成功。 实现原子性的关键是能够撤消已经执行的 SQL 语句。 InnoDB 依靠undo log 来实现回滚。 当数据库被修改时，InnoDB会生成对应的undo log，insert操作会生成delete undo log。 更新对应于相反更新的撤销日志

如果事务执行失败或调用rollback，导致事务回滚，则利用undo log中的信息将数据回滚到修改前的状态。

持久化实现原理：redo log、Buffer Pool、double write buffer

如果每次写入数据都需要进行磁盘IO，效率很低，所以InnoDB提供了一个缓存Buffer Pool，Buffer Pool中一些数据页在磁盘上的映射作为访问数据库的缓冲. 如果数据库要读取数据，会先从Read from the Buffer Pool中读取，如果没有，则从磁盘中读取并放入Buffer Pool中。 写入数据时，会先写入Buffer Pool，然后Buffer Pool会周期性的刷新到磁盘（脏页）

Buffer Pool大大提高了读写数据的效率，但是如果MySQL突然崩溃，Buffer Pool中的数据还没有来得及刷新到磁盘，导致数据丢失，所以引入了redo log。 修改数据时，除了修改Buffer Pool中的数据外，还会将操作记录在redo log中。 提交事务时，刷新磁盘并持久化重做日志。 如果MySQL宕机，重启时读取redo log中的数据，恢复数据。 MySQL先写redo log，再写Buffer Pool。

文件系统对大数据页的修改一般不是原子操作。 如果MySQL在flush dirty pages的时候突然crash了，可能会出现物理数据页partial write的情况，属于partial write problem。 此时物理页已经损坏，无法通过redo log解决。 因此，InnoDB 引入了双写缓冲区。 在刷新脏页之前，将页写入磁盘的另一个位置，然后写入。 location，如果数据页损坏，可以通过复制页来恢复页，然后redo log redo

先写redo log再写Buffer Pool。 重做日志记录物理页的修改和追加操作。 顺序IO速度快，事务提交后可以持久化redo log。 Buffer Pool是定时清理的，脏了会出现双写buffer。 一旦宕机，Buffer Pool中的数据没有了，使用redo log恢复数据，如果某个物理页损坏，使用double write buffer中的page进行恢复。

隔离实现原理：

隔离的追求是事务在并发情况下互不干扰。 写操作InnoDB通过加锁保证隔离。读操作InnoDB通过MVCC保证隔离

当事务修改数据时，需要获取相应的锁。 在事务操作过程中，这部分数据是被锁定的。 如果其他事务要修改数据，则必须等待当前事务提交或回滚才能释放锁。

根据锁的粒度，锁可以分为表锁和行锁。 表锁锁表，并发性能差，行锁并发性能好，但是如果要锁的数据很多，表锁可以节省资源。

MyIsam只支持表锁，而InnoDB同时支持表锁和行锁

InnoDB默认的隔离级别是可重复读，可以解决脏读、不可重复读、幻读等问题。 使用了MVCC多版本并发控制协议。 最大的优点是读没有锁，读写不冲突，并发性能好。

MVCC主要基于以下技术和数据结构：

隐藏列：InnoDB 中的每一行数据都有一个隐藏列。 hidden列包含了这行数据的transaction id，undo log的指针等 基于undo log的版本链：每行数据的hidden列包含了undo log的指针，undo log也包含了undo较早版本的日志指针，形成版本链 ReadView：MySQL通过隐藏列和版本链，可以将数据恢复到指定的版本，但具体恢复的版本需要根据ReadView来确定。

ReadView是指事务A在某个时刻对整个事务系统进行了快照，然后在读取数据时将事务id与快照进行比较，从而判断ReadView是否可见。

一致性实现原则：

一致性是事务追求的最终目标。 原子性、持久化、隔离性都是为了保证数据库状态的一致性。 除了数据库层面的保证，应用层面也要保证：

3.事务隔离级别隔离级别脏读不可重复幻读

读取未提交

√

√

√

读提交

X

√

√

可重复读

X

X

√

连载

X

X

X

脏读：事务A读取了事务B未提交的数据，不能重复读：事务A两次读取不一样，因为事务B在中间修改了数据，提交了幻读：事务A根据检查两次数据一定条件下，数据的结果条数不同。不可重复读是指数据发生了变化，幻读是指记录条数发生了变化。 4、MySQL事务隔离级别如何实现

读未提交：无锁

Read committed：通过行锁，未commit时无法读取。 InnoDB底层使用MVCC始终读取最新的快照数据

可重复读：使用Next-Key Lock算法实现行锁，在事务过程中不允许读取已提交的数据。该算法包括间隙锁，会锁定一个范围，解决幻读

序列化：为每个读操作加一个共享锁，读占用锁，不再支持一致性非锁读。

锁 1、你了解数据库中的锁吗？

锁定机制用于管理对共享资源的并发访问。 以 InnoDB 引擎为例。

行锁类型：

共享锁：允许一个事务读取一行数据，read-read共享排它锁：允许一个事务删除或更新一行数据。 如果要获取排他锁，必须等待其他事务释放对该行的共享锁。读写，写与写互斥

锁定粒度：

意向共享锁：事务要获取表中某些行的共享锁，必须先获取意向锁 意向排他锁：事务要获取表中某些行的排他锁，必须先获取有意排他锁

锁定算法：

Record Lock》单行记录上的锁 Gap Lock：间隙锁，锁定一个范围，但不包含记录本身，防止多个事务向同一个范围插入记录，会导致幻读产生Next-Key Lock := Record Lock + Gap Lock，锁定一个范围，包括锁定记录本身

僵局：

死锁是指两个或多个事务在执行过程中由于竞争锁资源而相互等待的现象。如果没有外力，事务将无法继续进行。

解决死锁最简单的方法就是超时，即超时后一个事务回滚，另一个等待的事务可以继续

InnoDB 使用等待图（waiting graph）的方法来检测死锁。 等待图通过锁信息链表和事务等待链表构建图。 如果这个图中出现环路，就说明发生了死锁。 每个事务在请求锁并发生等待时，都会判断等待图中是否存在环路。 如果有循环，就意味着有死锁，InnoDB通常会回滚undo量最小的事务。

锁升级：

锁升级是指降低锁的粒度。比如1000行锁升级为页锁，或者页锁升级为表锁。 InnoDB 没有锁升级

2、InnoDB中行级锁是如何实现的？

InnoDB 行级锁是通过在索引上锁定索引项来实现的。 如果不使用索引，则加表锁。

3、什么情况下会出现数据库死锁？

优化一、谈谈你对数据库优化的理解

MySQL 数据库优化是多方面的。 其原理是减少系统瓶颈，减少资源占用，提高系统响应速度。

优化文件系统提高磁盘IO速度，优化操作系统调度策略提高MySQL的负载能力，优化表结构、索引、查询语句等使查询响应更快

对于查询，可以通过使用索引或使用连接代替子查询来提高查询速度

对于慢查询，可以通过分析慢查询日志，找出造成慢查询的原因，从而进行相应的优化

对于插入，可以通过禁用索引、禁用检查等方式提高插入速度，插入数据后再启用索引和检查

对于数据库结构，可以通过将字段多的表拆成多张表、增加中间表、增加冗余字段等方式进行优化。

2、如何优化MySQL查询

① 使用指标：

如果不使用索引，查询语句会扫描整张表，数据量大时会很慢。 查询语句可以根据索引快速定位到需要查询的记录，减少需要查询的记录条数。

②优化子查询：

子查询是 SELECT 语句的嵌套。 一个查询结果作为另一个查询语句的条件。 子查询可以完成许多逻辑上需要多个步骤一次完成的 SQL 操作。 使用灵活，但执行效率不高。 执行子查询时，MySQL会先为内层查询创建一个临时表，然后在外层查询语句中从临时表中查询记录。 查询完成后，这些临时表被撤销。 所以子查询比较慢，数据越多越慢。

使用连接查询JOIN代替子查询，无需创建临时表，比子查询更快。 如果在查询中使用索引，性能会更高。

3、如何插入数据更高效？

影响插入速度的主要因素是索引、唯一性检查和一次插入的记录数。 这些情况可以单独优化。

4、表有千万条数据怎么办？

按以下顺序优化：

优化SQL和索引，增加redis缓存读写分离，采用主从复制，或者主从复制垂直拆分，将大系统按照模块耦合划分为多个小系统，如会员库、订单库、支付database, 和 message database 等价拆分，把一个表拆分成不同的数据库存储，或者把一个表拆分成很多小表。 5、你知道MySQL的慢查询优化吗？

优化MySQL慢查询的步骤：

启用慢查询日志

MySQL 中的慢查询日志默认是关闭的。 启动慢查询日志，配置指定的记录阈值。 如果某条查询语句的查询时间超过了这个值，就会将查询记录记录在慢查询日志文件中，用于分析慢查询日志。

分析慢查询日志，使用explain关键字分析sql慢查询语句

常见的慢查询优化：

当索引不起作用时，优化数据库结构：对于字段较多的表，可以将一些使用频率较低的字段分离出来，组成一张新表。

对于需要频繁联合查询的表，可以创建一个中间表，改变联合查询，将关联查询分解到中间表查询上：对每个表进行单表查询，然后在应用程序中关联查询结果到optimize Limit pagination：当offset很大时，offset数据会被丢弃。 优化此类查询的最简单方法是尽可能使用索引覆盖扫描如何对数据库进行优化，而不是查询所有列。 然后根据需要进行关联操作，返回需要的列。 这样做会大大提高效率。 6.说说你对explain的理解

explain + 查询语句，用于分析查询语句。 重点关注type、key、key_len、rows、Extra

类型：

额外的：

7.如何删除百万以上的数据

由于索引需要额外的维护成本，因为索引文件是一个单独的文件，当我们添加、修改或删除数据时，都会对索引文件产生额外的操作。 这些操作会消耗额外的 IO，并且会降低增长。 /更改/删除执行效率。 因此，当我们在数据库中删除百万条数据时，查看MySQL官方手册可知，删除数据的速度与创建索引的数量成正比。 所以当我们要删除百万条数据时，可以先删除索引（此时需要三分钟左右），然后删除无用的数据（这个过程不到两分钟），删除后重新创建索引完成（约十分钟）分钟左右）

其他 1. 数据库设计的三种范式

第一范式：原子列

第二范式：在第一范式的基础上，非主属性必须完全依赖主属性（消除部分依赖）

第三范式：在第二范式的基础上，非主属性之间不存在相互依赖（消除传递依赖）

2.说说你对MySQL引擎的理解

在 MySQL 5.5 之后，InnoDB 是默认引擎，之前是 MyISAM。

InnoDB 支持事务、外键、MVCC、行锁、聚簇索引

MyISAM专门存储count(*)，一共有三个文件：表定义文件、数据文件、索引文件

3.说说你对MySQL日志的理解

binlog 二进制日志：记录所有修改数据库的操作，并以二进制形式记录在日志文件中，包括每条语句的执行时间、资源消耗、事务信息

redo log redo log：用于实现事务持久化，包括redo log buffer和redo log file。 重做日志保证事务的持久性。

数据库页面修改是随机IO，比较慢，所以引入了pool buffer，查询先检查pool buffer，如果没有查询，检查数据库，然后将对应的page放入pool buffer中。 修改的时候直接修改pool buffer，然后后台定时修改。 将池缓冲区刷新到磁盘称为刷新脏页。 pool buffer中的文件是volatile的，所以引入了redo log。 如果pool buffer中的数据丢失了，可以使用redo log来恢复。 redo log是顺序读写的，速度很快，所以每次修改都是先写入redo log，再写入pool buffer。 页面、事务commit或persist redo log persist redo log persist redo log persist every 1s. 如果刷脏页时出现partial write问题，mysql引入double write解决

undo log rollback log：回滚日志记录事务行为，实现事务原子性，undo log在MVCC中也可以实现快照读取

错误日志、查询日志、慢查询日志

4.谈谈你对MVCC的理解

InnoDB默认的隔离级别是RR，可重复读。 使用MVCC解决脏读、不可重复读、幻读等问题。 MVCC多版本并发控制协议最大的优点就是读不加锁，因此读写性能好，并发性能高。 MVCC的实现主要基于三种技术：隐藏列、undo log版本链、ReadView。

隐藏列：InnoDB 中的每一行数据都有一个隐藏列。 隐藏列包含了这行数据的transaction id指向undo log的指针，每条undo log也会指向一个更早版本的undo log，这样就形成了一个版本链。 通过隐藏列和版本链，MySQL 可以将数据恢复到指定的版本。 但是具体恢复到哪个版本需要ReadView来决定。所谓ReadView就是事务在某个时刻对事务系统进行快照，然后在执行读操作的时候，读取事务id中的事务id数据会和快照做对比，看数据是否对ReadView可见，即对事务A是否可见

5、MySQL主从同步是如何实现的？

Replication是MySQL数据库提供的一种高可用、高性能的解决方案

这种同步不是完全实时的。 复制的工作原理是两个线程从服务器，一个是I/O线程读取主服务器的二进制日志，并保存为中继日志；

另一个是SQL线程，执行relay log。

6、索引覆盖、回表查询、索引下推、索引失效

索引覆盖，只查询索引列，不需要回表

回表，非主键索引，叶子节点存放索引和主键

索引下推：当没有索引下推时，存储引擎取出数据返回给MySQL服务器，由服务器判断是否满足条件。

比如是联合索引(name,age)，查询一个姓张的8岁孩子的所有信息。 MySQL通过索引获取所有张姓人，然后回表查找所有张姓人的1000万条信息。 返回MySQL服务器，服务器去掉800万个age不等于8的条目，这个速度很慢，相当于800万个无效查询回表。

随着指数下推，还是这个联合指数。 找到张姓索引后，直接判断年领是否为8，是则返回表，否则跳过。 相当于只需要回表200万次。

这个例子也可以优化。 之前扫描联合索引1000万行，200万次回表。 添加虚拟列 first_name，并创建联合索引 (first_name, age)

这样查first_name = 'Zhang'和age = 8，只需要扫描联合索引200万行，回表200万次。

索引失效：不满足最左，函数或计算，不等号（可能），前面模糊查询%，发生隐式转换（字符串不加引号，数字加引号），OR不是所有索引，量数据小。

!=可以走索引，但实际情况不一定走。 首先，如果数据量小，就不会使用索引本身，而是全表扫描。 另一点与返回的数据集量有关。 如果返回的数据量大于整个表的20%，则不会使用索引，而是选择全表扫描。 如果不等于这个值，会得到很少的结果，使用索引。