mysql总结（二）

1. mysql explain是什么？有什么作用？描述下type

   EXPLAIN是一个关键字，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的，分析查询语句或是表结构的性能瓶颈。

   作用：

   ①表的读取顺序

   ②数据读取操作的操作类型

   ③哪些索引可以使用

   ④哪些索引被实际使用

   ⑤表之间的引用

   ⑥每张表有多少行被优化器查询

   类型；显示查询使用了何种类型，从最好到最差依次是：

   const>eq_ref>ref>range>index>ALL

2. mysql中有哪几种锁？表锁、行锁、页锁区别？

   ①表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

   ②行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

   ③页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

3. 悲观锁 乐观锁 的区别

   ①悲观锁是当线程拿到资源时，就对资源上锁，并在提交后，才释放锁资源，其他线程才能使用资源。

   ②乐观锁是当线程拿到资源时，上乐观锁，在提交之前，其他的锁也可以操作这个资源，当有冲突的时候，并发机制会保留前一个提交，打回后一个提交，让后一个线程重新获取资源后，再操作，然后提交。和git上传代码一样，两个线程都不是直接获取资源本身，而是先获取资源的两个copy版本，然后在这两个copy版本上修改。根据版本号判断是否冲突。

   ③悲观锁和乐观锁在并发量低的时候，性能差不多，但是在并发量高的时候， 乐观锁的性能远远优于悲观锁。

4. 什么是索引？

   在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

   数据页：是包含已经添加到数据库的表中的用户数据 数据库已经 data的结构。

5. 索引的种类有哪些，描述索引的优缺点?

   唯一索引：在创建唯一索引时要不能给具有相同的索引值。

   主键索引：在我们给一个字段设置主键的时候，它就会自动创建主键索引，用来确保每一个值都是唯一的。

   聚集索引：我们在表中添加数据的顺序，与我们创建的索引键值相同，而且一个表中只能有一个聚集索引。

   普通索引：它的结构主要以B+树和哈希索引为主，主要是对数据表中的数据进行精确查找。

   全文索引：它的作用是搜索数据表中的字段是不是包含我们搜索的关键字，就像搜索引擎中的模糊查询。

   使用索引的优点：

   ①提高数据的搜索速度

   ②加快表与表之间的连接速度

   ③在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。

   使用索引的缺点:

   ①在我们建立数据库的时候，需要花费时间去建立和维护索引，而且随着数据量的增加，需要维护它的时间也会增加。

   ②在创建索引的时候会占用存储空间。

   ③在我们需要修改表中的数据时，索引还需要进行动态的维护，所以对数据库的维护带来了一定的麻烦。

6. 设计索引的原则？

   ①在经常需要搜索的列上,可以加快搜索的速度

   ②在作为主键的列上

   ③在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度

   ④在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的

   ⑤在经常需要order by,group by,distinct 列上创建索引,这样查询可以利用索引的排序,加快排序查询时间

   ⑥在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度

7. 什么情况下索引会失效？

   ①条件中有or

   ②like查询是以%开头

   ③列类型是字符串时，没有用引号引用起来

   ④mysql估计使用全表扫描要比使用索引快

8. 数据库优化方案？

   数据分区

   对于海量的数据查询优化,一种重要方式是如何有效的存储并降低需要处理的数据规模,所以我们呢可以对海量数据进行分区.例如,针对年份存储的数据,我们可以按照年进行分区,不同数据库有不同的分区方式,但处理机制却大体相同.例如SQLserver的数据分区将不同的数据存于不同的文件组中,而不同的文件存在不同的磁盘分区下,这样吧数据分区,减少磁盘IO和系统负荷.

   索引

   索引一般可以加速数据的检索数据,加速表之间的连接,对表建索引包括在主键上建立聚簇索引,将聚合索引建立在日期列上, 索引的优点很多,但是对于索引的建立,还需要考虑实际情况,而不能对每个列都建索引.如果表结构很大,你要考虑到建立索引和维护索引的开销,索引本身也占用物理空间,动态修改表也要动态维护索引,如果这些开销大过索引带来的速度优化,那就得不偿失.

   缓存机制

   当数据量增加时,一般的处理工具都考虑缓存问题,缓存大小的设置也关系到数据处理的表现.列如,

   处理2亿条数据聚合操作室,缓存设置为100000条/buffer合理

   加大虚存

   由于系统资源有限,而处理的数据量非常大,当内存不足时,适量增加虚存来解决

   分批处理

   由于处理信息量巨大,可以对海量的数据进行分批(类似云计算MapReduce),然后再对处理后的数据进行合并操作,分而治之,这样有利于处理小数据.

   使用临时表和中间表

   数据量增加时,处理中要考虑提前汇总,这样做的目的是化整为零,大表变小表,分块处理完之后再利用一定的规则进行合并,处理过程中的临时表的使用和中间结果的保存都非常重要.如果对海量的数据,大表处理不了,只能拆分为多个小表.如果处理过程中需要多步汇总操作,则按汇总步骤一步一步来.

   优化查询语句

   查询语句的性能对查询效率的影响非常大,尽量早的缩小查询范围

   使用视图

   视图是表中的逻辑表现,不占用物理地址,对于海量数据,可以按一定的规则分散到各个基本表中,查询过程基于视图进行.

   使用存储过程

   在存储过程中尽量使用SQL自带的返回参数,而非自定义的返回参数,减少不必要的参数,避免数据冗余

   用排序来取代非顺序存储

   磁盘上的机械手臂的来回移动使得非顺序磁盘存取变成了最慢的操作,但是在SQL语句中这个现象被隐藏了,这样就使得查询中进行了大量的非顺序页查询,降低了查询速度.

   使用采样数据进行数据挖掘

   基于海量数据的数据挖掘方兴未艾,面对超海量数据,一般的挖掘算法往往采用数据抽样的方式进行处理,这样误差不会很大,大大的提高处理效率和处理的成功率.一般采样时应注意数据的完整性,防止过大的偏差.

   引用自：https://www.cnblogs.com/xiongchao0823/p/11650624.html

9. 什么是存储过程？有什么优缺点？

   存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集、一个函数，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

   优点

   ①重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。

   ②减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。

   ③安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。

   简单讲：

   1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

   2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

   3.存储过程可以重复使用,可减少数据库开发人员的工作量

   4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

   有一点需要注意的是，一些网上盛传的所谓的存储过程要比sql语句执行更快的说法，实际上是个误解，并没有根据，包括微软内部的人也不认可这一点，所以不能作为正式的优点，希望大家能够认识到这一点。

   缺点

   1：调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。

   2：移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。

   3：重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。

   4： 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

10. 什么是视图，视图有什么作用？

    视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

    ①简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

    ②安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

    ③数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

11. Btree和Hash区别

    ①btree可以用作范围查询，比如>,>=,<,<=和between，除去通配符开头查询。而hash只能用作对等查询

    ②hash一次定位数据，btree总是从根到叶子节点，所以hash检索效率高

    ③hash不支持使用索引排序

    ④hash不支持模糊查询以及最左前缀匹配

    ⑤hash一定要回表查询数据，btree的聚簇索引可以不用回表索引

    ⑥hash等值查询效率不一定比btree高。当哈希冲突很大，就会影响效率

    面试场景设想：

    面试官：如果一张表内有1亿条数据，而且数据还在不断的增长，问你如何提高表的查询效率？

    答：可以考虑分表，分表的话，有垂直分表和水平分表，垂直分表是把字段按照数据冷热进行拆分，水平分表是按照业务逻辑进行划分，比如时间+地区。

    面试官：那数据进行分表后，你怎么对表数据进行合并查询？

    答：union/union all 引出这两个关键字的区别，可以通过日志查看详情，引出mybatis如何查看日志信息，以及通过时间查看日志信息的时候，${year} 和 #{year}的区别。

    面试官：如果表的查询效率还是低？你要怎么办？

    答：为查询条件建立索引。（什么是索引、索引的目的、索引优缺点）？

    面试官：如果加了索引，查询效率还是慢？

    答：explain MYSQL查询分析性能工具。

    面试官：如果加了索引、explain，但还是慢怎么办？

    答：数据库相关层面的优化（包含硬件+软件）

    面试官：查询还是有些慢怎么办？

    答：要尝试使用存储过程==JAVA中的方法（一组业务逻辑)，只有多表关联才会一直慢下去。

    面试官：存储过程是什么？优缺点？

    答：一个函数。（SQL很复杂的时候，需要业务逻辑）。

    优点： 预编译。减少网络传输 多表关联SQL语句 在数据库编写一个函数（存储过程）

    缺点：维护性相对来说变差一些。移值性相对较差一些。

一个用户对数据库发送一个请求到响应的过程（未使用存储过程）：

1、JDBC发送一个SQL连接 select * from 表名 left join 表1 ON … WHERE… GROUPBY …HAVING… ORDER BY …

2、数据库解析 SQL字符串

3、SQL字符串解析成SQL对象（需要大量算法来完成）

4、序列化操作： 硬盘数据读取到内存中

5、返回对象给前端。

用户对数据库发送一个请求到响应的过程（使用存储过程）：

1、而是一个存储过程名称 proc_user_page()

2、和3、都忽略了。

4、硬盘数据读取到内存中

5、返回对象给前端

分页功能：

​ 真分页：每次分页请求都会走数据库。

​ 假分页：在数据库查询一次把数据都查出来。分页的过程在前端完成。