印象笔记mysql

• 第30题：SQL慢查询的优化方案，索引和表的优化方案。
  慢查询优化： 不要select *星。join效率大于子查询。命令explain 和 profile 分析 查询语句。 慢查询日志。小表join大表。千万级别查询分页用limit。经常用的sql开启缓存。 索引优化： 列中有NULL不建索引。尽量使用短索引。经常在where子句使用的列建立索引。多个列在where或者order by ，建立复合索引。like语句，以%百分号和_下划线开头不用索引，以百分号%结尾用索引。不要在列进行运算，函数或者表达式。不要使用not in和 不等于 <> 表的优化： 列尽可能用NOT NULL。字段长度固定。 拆大表，分成小表。

• 第六题.MySQL事务是什么？四大特性，四大隔离级别。 数据库事务是指数据库执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。 事务四个特性，ACID特性：原子性，持久性，隔离性，一致性。 原子性(Atomicity)： 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。 一致性(Consistency)：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态是指数据库中的数据应满足完整性约束。除此之外，一致性还有另外一层语义，就是事务的中间状态不能被观察到(这层语义也有说应该属于原子性)。 隔离性(Isolation)：多个事务并发执行时，一个事务的执行不应影响其他事务的执行，如同只有这一个操作在被数据库所执行一样。 持久性(Durability)：已被提交的事务对数据库的修改应该永久保存在数据库中。在事务结束时，此操作将不可逆转。 隔离级别： 1。：出现脏读，不可重复读，幻读。 解决：修改事务的隔离级别。set session transaction isolation level read committed. ：出现不可重复读，幻读。 解决：修改事务的隔离级别。set session transaction isolation level repeatable read. ：出现幻读，解决方案：间隙锁。 repeatable-read隔离级别下，使用了MVCC机制，select操作不会更新版本号，不会出现不可重复读。 。遵守事务ACID特性。 默认隔离级别：RR。 什么是间隙锁？间隙锁是一个在索引记录之间的间隙上的锁。 间隙锁的作用：保证某个间隙内的数据在锁定情况下不会发生任何变化。 间隙锁的目的防止幻读， 如何实现间隙锁：（1）防止间隙内有新数据被插入。（2）防止已存在的数据，更新成间隙内的数据。 innodb自动使用间隙锁的条件：（1）必须在RR级别下。（2）检索条件必须有索引。 间隙锁语句：select * for update；update *； 什么是脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。 什么是不可重复读：事务A多次读取同一数据，事务B在事务A多次读取过程中，对数据做了更新并提交，导致A事务读取同一数据时，结果不一致。 什么是幻读：事务A对所有数据做更新，事务B插入一行，事务A发现有一条数据没有更新。

• 第9题：如何防止sql注入，了解哪些加密算法，rsa过程说下。 防止SQL注入的方式： 1.开启配置文件中的magic_quotes_gpc 和 magic_quotes_runtime设置。my.cnf 2.执行sql语句时使用addslashes进行sql语句转换。 3.Sql语句书写尽量不要省略双引号和单引号。 4.过滤掉sql语句中的一些关键词：update、insert、delete、select、 * 。 5.提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的。

4 分库分表，分片规则hash和取余数。

5 数据库万级变成亿级，怎么处理。 一张表数据量增长到亿级，需要一张表数据分散到多张表。 Mycat，分库分表中间件，一张表的数据分散到多张表/主机上。

从千万的数据到亿级的数据，会面临哪些技术挑战？你的技术解决思路？ 一张表数据量增长到亿级，需要一张表数据分散到多张表。 Mycat，分库分表中间件，一张表的数据分散到多张表/主机上。

B-Tree索引，myisam和innodb中索引的区别。 myisam索引的叶子节点存储数据记录的地址，非聚集索引。 innodb索引的叶子节点记录完整的数据记录而非数据记录的地址，聚集索引。 聚集索引的顺序就是数据的物理存储顺序。

数据库隔离级别，每层级别分别用什么方法实现，三级封锁协议,共享锁排它锁，mvcc多版本并发控制协议，间隙锁。 三级封锁协议：封锁实现并发控制的非常重要的技术，基本的封锁有两种：排他锁X和共享锁S。 1级封锁协议：事务修改数据前加X锁，直到事务结束才释放。 2级封锁协议：1级封锁协议基础上，读数据加S锁，直到读完数据才释放。 3级封锁协议：1级封锁协议基础上，读数据加S锁，直到事务结束才释放。 共享锁与非共享锁、一个事务锁住了一条数据，另一个事务能查吗？ 共享锁：可以查。排他锁：不可查。

【mysql】第3题：mysql死锁，怎么解决，如果不要求执行顺序，死锁怎么解决？14-67 什么是死锁？两个或者俩以上进程为了争夺资源相互等待。 让不同的session加锁有顺序。 【锁的算法】：NextKey锁-innodb默认，间隙锁，记录锁。NextKey锁=间隙锁+记录锁。 innodb预防死锁的策略：

第5题：数据库索引，主键和唯一索引有什么区别？4个 1.主键索引不可空，唯一索引可空。 2.主键索引包含了唯一索引，但唯一索引不一定是主键索引。 3.一个表只有一个主键索引，但是可以有多个唯一索引。 4.主键索引可以引用为外键，但是唯一索引不可以。

• 行锁和表锁的区别。 表锁： 开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低 行锁： 开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

• B+索引和hash索引的区别。6N Hash索引非常高效可以一次定位，B+索引从根节点到叶节点需要多次IO访问。 1.Hash索引只能准确查询如=，IN，<=>不能范围查找。 2.Hash索引不能全表扫描。 3.Hash索引无法排序。 4.Hash索引在部分索引查询时无法被利用。 5.Hash索引遇到大量Hash值相等情况下不比B+索引快。

• mysql limit 分页如何保证可靠性。 语法：limit offset length。数据大时效率低。 select * from table limit (totalPage-1)*pageSize,pageSize; 如何优化，order by XXX limit offset length。order by字段有索引。 优化后，select * from table order by column limit (totalPage)*pageSize, pageSize;

• mycat分库分表，读写分离的实现。 Mycat：分布式数据库系统，可以看作是数据库代理，核心功能是分库分表。mycat定义路由规则来实现分库分表，如hash，取模，范围查找等。 mycat原理：拦截用户的SQL，将用户查询分析后发给后段真实的数据库，返回结果适当处理后最终返回给用户。

• Mysql如何加共享锁和排他锁。 数据库的共享锁：多个不同的事务，对同一个资源共享同一个锁。sql 加lock in share mode。 数据库的排他锁：select *** for update。 读取时共享锁，删改时排他锁。

【Mysql】数据库崩溃时事务的恢复机制。14-76 OK 事务相关日志：redo日志和undo日志。 redo日志：要执行的事务写到redo日志，每条SQL执行时写到redo日志缓冲区，事务commit前缓冲区的SQL刷新到磁盘/日志先行。 undo日志：undo日志用于事务异常时的回滚。记录事务执行前的数据。 undo日志会产生redo日志。

• Mysql Hash索引适用情况，举例。 Innodb的Hash索引是根据B+树自建的。查询条件精确匹配hash索引中的所有列时才用到hash索引。 某些索引值使用非常频繁时在B+树索引上创建一个hash索引，所以hash索引不适合重复值多的列上。

第20题：数据库的索引原理。 通过不断的缩小想要获得数据的范围来帅选出最终想要的数据，同时把随机事件变成顺序事件。

第21题：非聚簇索引和聚簇索引。 聚集索引？数据的物理顺序与列值的逻辑顺序相同。一个表只能有一个聚集索引。innodb使用聚集索引。 非聚集索引：数据的物理顺序与列值的逻辑顺序不通。一个表可以有多个非聚集索引，Myisum使用非聚集索引。

第22题；索引的使用注意事项。 使用OR 联合索引的最左匹配原则 like语句以%或_开头 字符串字段用引号 where条件语句不要用函数或表达式

第23题：联合索引，或复合索引。 一张表中多个列组成联合索引，如，列1，列2，列3，生效方式满足最左匹配原则。 比如联合索引（a,b,c）等于建立了索引a,ab,abc，查询条件的列越多，过滤出来的数据越少。

第24题：从底层解释最左匹配原则。 索引是一棵B+T树，联合索引也是B+树。 非叶子节点存储的是第一个关键字的索引，叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序。

第11题：Mysql要加上nextkey锁，语句该怎么写。 什么是next key锁？next key 锁包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身。innodb默认加锁方式是next key 锁。 select * from news where number=4 for update ; 语法：select *** for update; 与间隙锁一样，select语句需要命中索引。 next-key锁锁定的范围为间隙锁+记录锁，即区间(2，4)和(4， 5)加间隙锁，同时number=4的记录加记录锁。

【mysql】mysql同步机制原理，有哪几种同步方法。 原理：基于binlog，主服务器将更新数据sql写入本地binlog，从服务器的IO线程读取主服务器的binlog，从服务器的SQL线程把读取的SQL在从服务器回放，即保持主从服务器数据一致。 几种同步方法： 异步复制：写入从服务器成功与否不关心。 半同步复制：至少一台从服务器写入成功响应后主服务器响应客户端成功。

「Mysql」聚集索引和非聚集索引知道吗？什么情况用聚集索引什么情况用非聚集索引。 聚集索引的场景：范围查询，小数目的不同值， 非聚集索引的场景：大数目的不同值，频繁更新的列，

B树与B+树区别，优缺点？ B树的非叶子节点存储数据也存储索引数据，B+树的非叶子节点只存储索引数据，叶子节点存储具体数据。 B+树：读到内存的索引数据多，减少磁盘IO，查询效率稳定，数据遍历方便。存储的数据相对少。

第14题：为什么MyISAM查询性能好？ InnoDB维护的东西多。 1）数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少。 2）innodb寻址要映射到块，再到行，MYISAM记录的直接是文件的OFFSET，定位比INNODB要快。 3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护 MVCC多版本并发控制 。

第10题：mysql索引为B+树，为啥不是平衡二叉树或者红黑树？ 平衡二叉树的左右子树高度差不超过1，插入和删除只要不满足条件，通过旋转保持平衡，而旋转非常耗时，这种维护平衡的代价大。 红黑树：相同节点情况下，B+树的高度远小于红黑树，而B+树的查询时间分为CPU计算时间和磁盘存取时间，当中磁盘存取时间又远大于CPU计算时间所以B+树的操作效率取决于磁盘的访问次数，所以相同条件下高度相对于红黑树小的B+树效率更高。

第2题：一个表一千个列值为true和false，写sql 查询 有300个列值为true的行。 select * from table where col1=true limit 300;

【mysql】第9题：数据库备份和恢复如何实现？主从复制怎么做的，什么时候主从复制不一致？如何解决： 主从不一致如何解决： 半同步复制：mysql5.5后支持，至少一台从服务器同步成功后，主服务器返回客户端更新成功。 数据库中间层：otter（跨机房）或者canal。写路由到主库，读路由到从库。记录所有写key，在主从同步时间窗口内读写都路由到主库。 缓存记录写key：要更新的数据先写入cache/Redis，cache/Redis失效时间为主从同步时间（根据经验设置），后更新主库数据。

【mysql】Mysql有几种join方式？底层原理？14-77 inner join：左右表匹配的行。 left join：全部左表，右表匹配的行。 right join： join底层原理：Nested-Loop Join嵌套循环算法。有三个变种：Simple Nested-Loop Join，Index Nested-Loop Join，Block Nested-Loop Join。 Simple Nested-Loop Join：驱动表m*匹配表n，开销大。 Index Nested-Loop Join/索引嵌套：要求匹配表有索引，根据匹配字段在索引查询，再进行嵌套查询。 Block Nested-Loop Join：如果有索引用Index Nested-Loop Join查询，如果没有索引用Block Nested-Loop Join。驱动表要匹配的列先缓存到join buffer缓冲区，然后批量与匹配表匹配/join_buffer_size=256K。

第4题：数据库和redis如何保持一致性？ 方案1:延时双删策略，删缓存，写库，休眠，删缓存。 方案2:异步更新机制，基于消息队列和数据库binlog的后台脚本执行。开源框架：canal，otter，来订阅binlog。

• 第15题：分库分表后，id主键如何生成？分布式系统的全局ID如何生成。 uuid：性能高，本地生成，太长不易存储。 mysql：强依赖DB，性能瓶颈。 redis：利用Redis的原子操作。 zookeeper：性能瓶颈。 雪花算法：将64位分成多段表示机器，时间等，强依赖机器时钟。

• 第12题：Union和Union All的区别？ union作用：连接2个以上select语句。 union不显示重复行，union all 显示重复行。

• 第9题：MySQL数据主从同步是如何实现的？ Master：将数据更新语句写入binlog日志里。 Slave：SQL线程和IO线程，IO线程从Master服务器的binlog读取数据，SQL线程回放数据库更新操作。

第10题：MySQL索引的实现，innodb的索引，b+树索引是怎么实现的，为什么用b+树做索引节点，一个节点存了多少数据，怎么规定大小，与磁盘页对应。 B+树的索引实现：非叶子节点只存储搜索方向的数据，不存储真是数据，叶子节点存储真实数据。 IO次数取决于树的高度H。 假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。

• 第1题：索引失效条件。 1.查询条件有OR，尽量使用IN。 2.多列组合查询，必须使用第一列，否锁索引失效。 3.列的类型为字符串，必须用引号。 4.LIKE查询以%开头。 5.预计全表扫描查询比使用索引快。

分布式锁？ 1.基于数据表。要锁住某个方法或资源时，我们就在该表中增加一条记录，想要释放锁的时候就删除这条记录。几个问题：数据库是单点？搞两个数据库，数据之前双向同步。一旦挂掉快速切换到备库上。没有失效时间？只要做一个定时任务，每隔一定时间把数据库中的超时数据清理一遍。非阻塞的？搞一个while循环，直到insert成功再返回成功。非重入的？在数据库表中加个字段，记录当前获得锁的机器的主机信息和线程信息，那么下次再获取锁的时候先查询数据库，如果当前机器的主机信息和线程信息在数据库可以查到的话，直接把锁分配给他就可以了。2.基于数据库排他锁。查询语句后加for update，数据库会在查询过程中给数据库表增加排他锁（注意，InnoDB引擎在加锁的时候，通过索引进行检索的时候才会使用行级锁，否则会使用表级锁）。使用排他锁来进行分布式锁的lock，那么一个排他锁长时间不提交，就会占用数据库连接。一旦类似的连接变得多了，就可能把数据库连接池撑爆。问题：阻塞锁？ for update语句会在执行成功后立即返回，在执行失败时一直处于阻塞状态，直到成功。锁定之后服务宕机，无法释放？使用这种方式，服务宕机之后数据库会自己把锁释放掉。2，基于缓存实现分布式锁。Redis。问题：没有失效时间？tair的put方法支持传入失效时间，到达时间之后数据会自动删除。非阻塞？while重复执行。非可重入？在一个线程获取到锁之后，把当前主机信息和线程信息保存起来，下次再获取之前先检查自己是不是当前锁的拥有者。优点：性能好，实现起来较为方便。缺点：通过超时时间来控制锁的失效时间并不是十分的靠谱。3.基于Zookeeper实现分布式锁。对某个方法加锁时，在zookeeper上的与该方法对应的指定节点的目录下，生成一个唯一的瞬时有序节点。 判断是否获取锁，只需要判断有序节点中序号最小的一个。 释放锁的时候，节点删除。可以避免服务宕机导致的锁无法释放，而产生的死锁问题。优点：性能好，实现起来较为方便。缺点：超时时间控制锁的失效时间不靠谱。

• 43.分布式事务的解决方案。 数据库事务是指数据库执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。 事务四个特性，ACID特性： 原子性(Atomicity)：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。 一致性(Consistency)：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态是指数据库中的数据应满足完整性约束。除此之外，一致性还有另外一层语义，就是事务的中间状态不能被观察到(这层语义也有说应该属于原子性)。 隔离性(Isolation)：多个事务并发执行时，一个事务的执行不应影响其他事务的执行，如同只有这一个操作在被数据库所执行一样。 持久性(Durability)：已被提交的事务对数据库的修改应该永久保存在数据库中。在事务结束时，此操作将不可逆转。 本地事务：基于单个服务单一数据库资源访问的事务，被称为本地事务。 分布式事务：跨越多个服务的分布式事务。 分布式事务解决方案：1.基于XA协议的两阶段提交，2.消息事务+最终一致性，3.TCC 模型。 重点讲下消息事务。消息事务是基于消息中间件的两阶段提交。消息事务步骤：1、A系统向消息中间件发送一条预备消息。2、消息中间件保存预备消息并返回成功。3、A执行本地事务。4、A发送提交消息给消息中间件。将一个分布式事务拆成一个消息事务（A系统的本地操作+发消息）+B系统的本地操作，其中B系统的操作由消息驱动，只要消息事务成功，那么A操作一定成功，消息也一定发出来了，这时候B会收到消息去执行本地操作，如果本地操作失败，消息会重投，直到B操作成功，这样就变相地实现了A与B的分布式事务。

• 第8题：mysql 索引类别 : 普通索引， 唯一索引：列值唯一可以为空， 主键索引， 复合索引或者联合索引：多个字段索引，where中命中第一个字段才被使用， 全文索引。

• 第9题：什么是覆盖索引。 select的数据，只用索引中就能获取，不必读取数据行，也就是查询列要被所建的索引覆盖。

• 第10题：b+加树和b树的区别 。 B树非叶子节点，实际记录的指针。 B+加树的叶子节点：实际记录的指针，B+加树的叶子节点通过指针连起来，适合区间扫描和顺序查找。

• 第11题.为什么选用自增量作为主键索引？/自增量-主键索引 主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

• 第13题.MYSQL如何在RR隔离级别下避免幻读问题：间隙锁。/next-key锁：间隙锁+记录锁 什么是间隙锁？间隙锁是一个在索引记录之间的间隙上的锁。 间隙锁的作用：保证某个间隙内的数据在锁定情况下不会发生任何变化。 间隙锁的目的防止幻读，如何实现间隙锁：（1）防止间隙内有新数据被插入。（2）防止已存在的数据，更新成间隙内的数据。 innodb自动使用间隙锁的条件：（1）必须在RR级别下。（2）检索条件必须有索引。 select XXX for update；

• 第14题.MYSQL范式和反范式的区别以及彼此的优缺点。 第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；。 第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性； 。 第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。。 范式化设计优缺点:优点:可以尽量得减少数据冗余，使得更新快，体积小。 缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，索引优化难。 反范式化:优点:可以减少表得关联，可以更好得进行索引优化。 缺点:数据冗余以及数据异常，数据得修改需要更多的成本。

• 第 22题.表锁 行锁 乐观锁 悲观锁的特点和区别。 表级锁： 开销小，加锁快；不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁)；锁定粒度大，发生锁冲突的概率最高,并发度最低。 行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，别人想拿这个数据就会被block。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。 实现：select for update。 乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。 实现：表增加版本字段。 乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。 共享锁/S锁：指的就是对于多个不同的事务，对同一个资源共享同一个锁。共享锁也属于悲观锁的一种，在执行语句后面加上lock in share mode。 排他锁/X锁：select XXX for update。

• 39.mysql常用的存储引擎及区别。 1.InnoDB引擎：灾难恢复性好。支持事务。使用行级锁。支持外键关联。支持热备份。对于InnoDB引擎中的表，其数据的物理组织形式是簇表（Cluster Table），主键索引和数据是在一起的，数据按主键的顺序物理分布。实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取。 2.MyISAM引擎：不支持事务。使用表级锁，并发性差。宕机后，MyISAM表易损坏，灾难恢复性不佳。可以配合锁，实现操作系统下的复制备份、迁移。只缓存索引，数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳。数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能。

Innodb的MVCC机制：14-1 MVCC：多版本并发控制。 为什么MVCC机制，控制并发操作使用锁机制或者MVCC机制，多数情况下，MVCC机制可以代替锁机制，MVCC机制能降低系统开销。 MVCC机制的实现：MVCC机制保存数据在某个时间段的快照，不同的存储引擎实现的MVCC机制不同：乐观性并发控制，悲观型并发控制。 原理：每行记录保存隐藏的2个列：行创建的时间，行删除的时间。（时间系统版本号/事务ID） INSERT：创建时间：当前事务ID，删除时间：未定义。 DELETE：删除的每一行保存当前的事务ID到行删除的时间。 UPDATE：增加一行数据，旧行的删除时间=新行的创建时间=当前事务ID。 SELECT：创建时间<=当前时间 && 删除时间>当前时间/未定义。

高性能Mysql

第一章 Mysql架构 每个客户端连接在服务器进程中拥有一个线程。 锁粒度：共享锁/S锁/读锁，排他锁/X锁/写锁，表锁，行锁 事务的ACID特性 事务的隔离级别 死锁 事务中，混用存储引擎：非事务性存储引擎无法回滚。 显示加锁：lock in share mode；selet XXX for update； 多版本并发控制：MVCC Innodb： MVCC多版本并发控制，默认Repeatable-Read，间隙锁/next-key锁，防止幻读 聚集索引/聚簇索引：查询性能高，不过二级索引/非主键索引中必须包含主键列，主键尽可能小。 第二章 Mysql基准测试 基准测试：针对系统设计的一种压力测试。 基准测试的一个主要问题在于，不是真实压力的测试。 基准测试的俩个策略：针对整个系统的整体测试，单独测试Mysql。 一份生产数据的拷贝 标准的基准测试：TPC-C，www.tpc.org 集成测试工具：ab，http_load，JMeter 单组件式测试工具：mysqlslap，Mysql Benchmark Suite，Super Smack，Database Test Suite，sysbench，。。。 第三章 服务器性能剖析