[大数据]mysql知识点整理

前言

提示：这里可以添加本文要记录的大概内容：

例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了机器学习的基础内容。

提示：以下是本篇文章正文内容，下面案例可供参考

一、介绍

1.什么是存储过程？用什么来调用？

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

2. mysql执行一条sql语句的完整过程？

mysql执行一条sql语句的完整过程，sql语句在mysql中的执行过程MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
SQL 等执行过程分为两类:

1. 对于查询等过程如下：权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
2. 对于更新等语句执行流程如下：分析器----》权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit

3. Mysql的存储引擎,myisam和innodb区别？

1. InnoDB支持事务，MyISAM不支持对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

二、事务

1. 什么是事务？

事务：是一系列的数据库操作，是数据库应用的基本逻辑单位。

2. 事务特性？

1. 原子性（Atomicity）：即不可分割性，事务要么全部被执行，要么就全部不被执行。
2. 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣
   了钱，B却没收到。
3. 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正
   在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
4. 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

3.事务隔离级别?

数据库定义了几种不同的事务隔离级别：

1. READ_UNCOMMITTED（未授权读取）: 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
2. READ_COMMITTED（授权读取）: 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读，仍有可能发生。
3. REPEATABLE_READ（可重复读）: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
4. SERIALIZABLE（串行）: 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别。
5. 事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

4.MVCC了解?

1. MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)(实际上就是保存了数据在某个时间节点的快照。我们每行数实际上隐藏了两列，创建时间版本号，过期(删除)时间版本号，每开始一个新的事务，版本号都会自动递增)
   注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control MVCC最大的好处：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，现阶段几乎所有的RDBMS，都支持了MVCC。
2. LBCC：Lock-Based Concurrency Control，基于锁的并发控制。
3. MVCC：Multi-Version Concurrency Control 基于多版本的并发控制协议。纯粹基于锁的并发机制并发量低，MVCC是在基于锁的并发控制上的改进，主要是在读操作上提高了并发量。

三、索引

1.索引的目的是什么？

1. 快速访问数据表中的特定信息，提高检索速度 创建唯一性索引，保证数据库表中每一行数据的唯一性。 加速表和表之间的连接。
2. 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

2.索引对数据库系统的负面影响是什么？

1. 创建索引和维护索引需要耗费时间，这个时间随着数据量的增
   加而增加；
2. 索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
3. 当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

3.什么情况下不宜建立索引？

1. 数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率。大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
2. 对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等
3. 表记录太少
4. 经常插入、删除、修改的表
5. 经常和主字段一块查询但主字段索引值比较多的表字段

4.主键、外键和索引的区别？

1. 定义：
   1.1 主键–唯一标识一条记录，不能有重复的，不允许为空
   1.2 外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值
   1.3 索引–该字段没有重复值，但可以有一个空值
2. 作用：
   2.1 主键–用来保证数据完整性
   2.2 外键–用来和其他表建立联系用的，保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。
   2.3 索引–是提高查询排序的速度
3. 个数：
   3.1 主键–主键只能有一个
   3.2 外键–一个表可以有多个外键
   3.3 索引–一个表可以有多个唯一索引

5.组合索引 (a,b,c)，支持哪些基于索引的查找？

1. 最左原则：对于联合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。
2. 例如索引是key index (a,b,c). 可以支持a 、 a,b 、ac、 a,b,c 4种组合进行查找，但不支持 b,c进行查找 当最左侧字段是常量引用时，索引就十分有效。

6.什么是覆盖索引和回表？

1. MySQL innodb的主键索引是簇集索引，也就是索引的叶子节点存的是整个单条记录的所有字段值，不是主键索引的就是非簇集索引，非簇集索引的叶子节点存的是主键字段的值。回表是什么意思？就是你执行一条sql语句，需要从两个b+索引中去取数据。
   举个例子：
   表tbl有a,b,c三个字段，其中a是主键，b上建了索引，然后编写sql语句
   SELECT * FROM tbl WHERE a=1
   这样不会产生回表，因为所有的数据在a的索引树中均能找到
   SELECT * FROM tbl WHERE b=1
   这样就会产生回表，因为where条件是b字段，那么会去b的索引树里查找数据，但b的索引里面只有a,b两个字段的值，没有c，那么这个查询为了取到c字段，就要取出主键a的值，然后去a的索引树去找c字段的数据。查了两个索树，这就叫回表。
2. 索引覆盖：就是查这个索引能查到你所需要的所有数据，不需根据第一次查询的结果，在进行第二次查询。其实就是不用回表。
3. 怎么避免：不是必须的字段就不要出现在SELECT里面。或者b,c建联合索引。但具体情况要具体分析，索引字段多了，存储和插入数据时的消耗会更大。这是个平衡问题。

7.【索引失效】在哪些情况下会发生针对该列创建了索引，但是在查询的时候并没有使用呢?

1. 没有遵循索引最左原则。
   建立几个复合索引字段，最好就用上几个字段。
2. 最佳左前缀法则
   如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，可以跳过但是尽量不跳过索引中间的列。
3. 不再索引列上做任何操作(计算、函数、（自动or手动）类型转换)，会导致索引失效而转向全表扫描。
4. 存储引擎不能使用索引中范围条件右边的列。（范围之后全失效）若中间索引列用到了范围（>、<、like等），则后面的索引全失效。
5. Mysql在使用不等于(!=、<>)或like的左模糊的时候无法试用索引会导致全表扫描
6. IS NULL和IS NOT NULL也无法使用索引
7. 字符串不加单引号索引失效，因为这里有一个隐式的类型转换操作，更严重会导致行锁变表锁，降低SQL效率
   以上情况,MySQL无法使用索引.

四、锁

1.数据库中，什么是锁？

1. 数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
2. MyISAM和InnoDB存储引擎使用的锁：MyISAM采用表级锁(table-level locking)。InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁
3. 表级锁和行级锁对比：表级锁： Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。行级锁： Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。InnoDB存储引擎的锁的算法有三种：Record lock：单个行记录上的锁Gap lock：间隙锁，锁定一个范围，不包括记录本身.Next-key lock：record+gap 锁定一个范围，包含记录本身

2.什么是读锁，什么是写锁？

1. 读锁：也叫共享锁、S锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S 锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
2. 写锁：又称排他锁、X锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

3.什么是间隙锁？

1. 间隙锁是可重复读级别下才会有的锁，间隙锁可以解决幻读的问题。我们还是以user举例，假设现在user表有几条记录
   当我们执行：
   begin;
   select * from user where age=20 for update;
   begin;
   insert into user(age) values(10); #成功
   insert into user(age) values(11); #失败
   insert into user(age) values(20); #失败
   insert into user(age) values(21); #失败
   insert into user(age) values(30); #失败
   只有10可以插入成功，那么因为表的间隙mysql自动帮我们生成了区间(左开右闭)
   (negative infinity，10],(10,20],(20,30],(30,positive infinity)
   由于20存在记录，所以(10,20]，(20,30]区间都被锁定了无法插入、删除。
   如果查询21呢？就会根据21定位到(20,30)的区间(都是开区间)。

4、如何预防间隙锁？

1. 尽量采用乐观锁，乐观锁是在php等代码层面的锁，就不会锁住数据库资源。
2. 事务中update，where后面的字段尽量带上索引，不然间隙锁的范围很。
3. 尽量不要出现长事务，否则事务中更新订单时间隙锁会被锁很久，另一事务插入订单就会执行很久。
4. update订单表，begin和commit之间的时间不要太长，之间不要写一些慢代码，比如请求第三方接口。
5. 分表能防止不分表情况下整张表被锁住。分表后是锁住众多表中的其中一张。

5.什么是死锁？

1. 所谓死锁，是指多个进程在运行过程中因争夺资源而造成的一种僵局，当进程处于这种僵持状态时，若无外力作用，它们都将无法再向前推进。因此我们举个例子来描述，如果此时有一个线程A，按照先锁a再获得锁b的的顺序获得锁，而在此同时又有另外一个线程B，按照先锁b再锁a的顺序获得锁。

6.死锁发生的必要条件(缺一不可)?

1. 互斥（Mutual exclusion）：存在这样一种资源，它在某个时刻只能被分配给一个执行绪（也称为线程）使用；
2. 持有（Hold and wait）：当请求的资源已被占用从而导致执行绪阻塞时，资源占用者不但无需释放该资源，而且还可以继续请求更多资源；
3. 不可剥夺（No preemption）：执行绪获得到的互斥资源不可被强行剥夺，换句话说，只有资源占用者自己才能释放资源；
4. 环形等待（Circular wait）：若干执行绪以不同的次序获取互斥资源，从而形成环形等待的局面，想象在由多个执行绪组成的环形链中，每个执行绪都在等待下一个执行绪释放它持有的资源。

7.如何预防死锁？

1. 尽量采用乐观锁，乐观锁是在代码层面的锁，不会锁住数据库资源.
2. 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务； (细化处理逻辑，执行一段逻辑后便回滚或者提交，然后再执行其它逻辑，直到事物执行完毕提交)
3. 设置死锁超时参数为合理范围，超过时间，自动放弃本次操作，避免进程悬挂；
4. 优化程序，检查并避免死锁现象出现；

五、主从

1.主从同步的原理？

1. master提交完事务后，写入binlog
2. slave连接到master，获取binlog
3. master创建dump线程，推送binglog到slave
4. slave启动一个IO线程读取同步过来的master的binlog，记录到relay log中继日志中
5. slave再开启一个sql线程读取relay log事件并在slave执行，完成同步
6. slave记录自己的binglog

2.全同步复制

1. 主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

3. 半同步复制

1. 和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

4.如何保证数据的一致性？

1. 在一个电子商务系统中，正常的应该是订单生成成功后，相应的库存进行减少必须要保证两者的一致性，但有时候因为某些原因，比如程序逻辑问题，并发等问题，导致下单成功而库存没有减少的情况。这种情况我们是不允许发生的，MySQL的中的事务刚好可以解决这一问题，首先得选择数据库的存储引擎为InnoDB的，事务规定了只有下订单完成了，并且相应的库存减少了才允许提交事务，否则就事务回滚，确保数据一致性。

六、mysql集群

1.MHA介绍（最常用或者说用的最多的）？

1. MySQL高可用性环境下故障切换和主从提升的高可用软件.在MySQL故障切换过程中，MHA能做到在10~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。
2. ? MHA还提供在线主库切换的功能，能够安全地切换当前运行的主库到一个新的主库中（将主库提供为主库），大概0.5-2s内完成。
3. MHA由两部分组成：MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

2.MHA优点？

1. 自动故障转移快
2. 主库崩溃不存在数据一致性问题
3. 不需要对当前mysql环境做重大修改
4. 不需要添加额外的服务器（仅一台manager就可管理上百个replication）
5. 性能优秀，可工作在半同步半复制和异步复制，当监控mysql状态时，
6. 需要每隔N秒向master发送ping包(默认3秒)，所以对性能无影响。你可
7. 理解为MHA的性能和简单的主从复制框架性能一样。
8. 支持所有的存储引擎

3.MHA工程流程？

1. 把宕机的master二进制日志保存下来
2. 找到binlog位置点最新的slave
3. 在binlog位置点最新的slave上用relay log修复其它slave
4. 将宕机的master上保存下来的二进制日志恢复到含有最新位置点的slave
5. 将含有最新位置点binlog所在的slave提升为master将其它slave重新指向新提升的master，并开启主从复制。（重构主从）

4.MHA架构图？

七、mysql优化

1.mysql优化顺序有哪些？

1>业务优化，数据库设计 2>数据库索引 3>分表分库(水平分割,垂直分割) 4>读写分离 5>存储过程(模块化编程,可以提高速度) 6>对MySQL配置优化(配置最大并发数my.ini,调整缓存大小) 7>SQL调优 8>选择正确的存储引擎 9>定时清除不需要的数据,定时进行碎片整理 10>热点数据采用Nosql等替代品 11>模糊查询采用es等替代品 12>mysql配置优化13>服务器优化（操作系统和硬件）

2.大表优化有那些？

1. 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。
2. 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
3. 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
4. 垂直分区：根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。
   垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
   垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；
5. 水平分区：保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。水品拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL 并发能力没有什么意义，所以 水品拆分最好分库 。水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨界点Join 性能较差，逻辑复杂

3.什么是水平拆分和垂直拆分？

1. 水平分割：例：QQ的登录表。假设QQ的用户有100亿，如果只有一张表，每个用户登录的时候数据库都要从这100亿中查找，会很慢很慢。如果将这一张表分成100份，每张表有1亿条，就小了很多，比如qq0,qq1,qq1…qq99表。用户登录的时候，可以将用户的id%100，那么会得到0-99的数，查询表的时候，将表名qq跟取模的数连接起来，就构建了表名。比如123456789用户，取模的89，那么就到qq89表查询，查询的时间将会大大缩短。这就是水平分割。
2. 垂直分割：垂直分割指的是：表的记录并不多，但是字段却很长，表占用空间很大，检索表的时候需要执行大量的IO，严重降低了性能。这时需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。例如学生答题表tt：有如下字段：Id name 分数 题目 回答其中题目和回答是比较大的字段，id name 分数比较小。如果我们只想查询id为8的学生的分数：select 分数 from tt where id = 8;虽然知识查询分数，但是题目和回答这两个大字段也是要被扫描的，很消耗性能。但是我们只关心分数，并不想查询题目和回答。这就可以使用垂直分割。我们可以把题目单独放到一张表中，通过id与tt表建立一对一的关系，同样将回答单独放到一张表中。这样我们插叙tt中的分数的时候就不会扫描题目和回答了。

4.什么是表分区？

1. 表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。
2. 分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。
3. 分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

5.MySQL支持的分区类型有哪些？

1. RANGE分区： 这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
2. LIST分区： 这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区，与RANGE的区
   别是，range分区的区间范围值是连续的。
3. HASH分区 ：这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应
   的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
4. KEY分区 ：上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

6.超大分页怎么处理?

1. 数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于 select * from table where age > 20 limit 1000000,10 这种查询其实也是有可以优化的余地的. 这条语句需要 load 1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为 select * from table where id in (select id from table where age > 20 limit 1000000,10) .这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以 select * from table where id > 1000000 limit 10 ,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.
2. 从需求的角度减少这种请求….主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.

7.mysql优化工具及方法？

1. 慢查询日志：记录查询时间慢的语句
2. explan：查看mysql执行计划，寻找其中可优化点
3. profiling：查询资源消耗情况

八、视图

1.什么是表？什么是试图？

1. 基本表是本身独立存在的表，在 SQL 中一个关系就对应一个表。 视图是从一个或几个基本表导出的表。
2. 视图本身不独立存储在数据库中，是一个虚表

2.视图的优点？

1. 视图能够简化用户的操作
2. 视图使用户能以多种角度看待同一数据；
3. 视图为数据库提供了一定程度的逻辑独立性；
4. 视图能够对机密数据提供安全保护。

九、mysql防注入

1.如何防注入？

1. 运用execute代替sql语句连接

总结

提示：这里对文章进行总结：

如果发现知识点中存在问题，欢迎大家留言讨论。