Mysql
- 1.B树和B+树区别是什么
- 2.高度为3的B+树可以存储多少数据
- 3.Innodb是如何支持范围查找能走索引的
- 4.回表查询&为什么要遵守最左前缀原则才能用到索引
- 5.范围查找导致索引失效原理分析
- 6.覆盖索引的底层原理
- 7.索引扫描底层原理
- 8.order by 为什么会导致索引失效？
- 9. mysql中的数据类型转换有什么需要注意的吗？
- 10.对字段进行操作导致索引失效原理
- 11.Mysql中有哪些存储引擎
- 12.MyIsam和Innodb的区别是什么？
- 13.数据表设计时，字段你会如何选择？
- 14.varchar最多能存储多少数据
- 15.请说下事务的基本特性
- 16.事务并发可能引发什么问题
- 17.简述下Mysql的各种索引
- 18.什么是三星索引
- 19.如何提高Insert的性能
- 20.什么是全局锁、共享锁、排它锁
- 21.谈一谈Mysql中的死锁
- 22.Mysql如何实现读写分离
- 23.Mysql如何实现分库分表
- 24.索引的基本原理
- 25.Mysql聚簇索引和非聚簇索引的区别
- 26.索引的设计原则
- 27.mysql的锁类型有哪些
- 28.mysql的执行计划怎么看
- 29.事务的基本特性和隔离级别
- 30.怎么处理慢查询
- 31.ACID靠什么保证
- 32.什么是MVCC
1.B树和B+树区别是什么
B树
B树：每个节点可以存储多个元素，元素存储是排序的，右边的节点都比左边的大。
B+树
B+树：与B树相比，B+树最明显的区别是B+树的叶子结点有指针。里面的节点也可以存储不止一个元素。B+树的非叶子节点都冗余了一份在叶子节点上面。B+树就相当于B树的升级版。所以
- 一个节点里面有多个元素。
- 元素存储是按有序的，左节点小于右节点。
- 叶子节点之间有指针。
- 非叶子节点上面的元素都冗余了一份在叶子节点上面。换句话说，在B+树上面存储的元素都会在叶子节点上面，而且是指针相连、有序的。
2.高度为3的B+树可以存储多少数据
高度为2的B+树：一页就是一个树的节点，叶子节点就是存储数据，根节点主要存储主键+指针，一页可以存储16k，假设一个表的每条记录大概也就1kb，一页可以存储16kb/1kb。两层的B+树可以存储1638*16=26208条数据。（16kb/10b=1638 int类型4个字节，一个指针6个字节，总共这一对加起来10b，16kb就是一页的数量，这一页存储的是目录。所以可以管理1638页。）
高度为3的B+树：B+树只有叶子节点才存储元素值，所以前两层可以理解为存储的是目录。最顶层多一个节点，同样这个节点可以管理1638个目录，所以是1638*1638*16 == 42928074，4000多万条数据。
3.Innodb是如何支持范围查找能走索引的
select * from t1 where a > 6是先执行a=6的操作，先找到a=6之后，再把a>6之后的所有数据全部返回就可以了。
4.回表查询&为什么要遵守最左前缀原则才能用到索引
创建一个bcd的联合索引 create index idx_t1_bcd on t1(b,c,d)，那么他也会生成一个B+树，那么这个b+树是怎么生成的呢？ 我们主键索引所对应的B+树是按照主键锁对应的元素进行排序，然后生成B+树。那么bcd索引所对应的B+树也是一个道理，也就是把这几条数据按照bcd三个字段进行排序就可以了。但是叶子节点呢？叶子节点如果存放全部数据的话，每次增删改查数据都要对着两个B+树进行操作，相当的麻烦，所有在联合索引的叶子节点上，不存放其他元素的值，只存bcd三个字段的值和主键的索引。是因为我们如果借用bcd所有来查询，查到的元素只有bcd，还需要一个主键，进行回表操作，根据主键值在主键B+树查找完整的信息。
最左前缀原则：where的条件，和你给的索引顺序无关（底层会给你优化排序），但必须包含最左侧的字段。比如这个查询就会使索引失效 select * from t1 where c = 1 and t = 1，没有用到a字段。这种情况就 相当于给的条件是 * 1 1，想要走bcd的索引，然后跟111和322比较，看往左走还是往右走。
这是没办法比较的，因为条件的最左边字段没给。但如果条件是b=1 and d=1是能走的，这就好比 1 * 1，这是可以比较的，数据一定在左边，因为左边的a比右边的a小。
5.范围查找导致索引失效原理分析
假如还是bcd联合索引，这次的查询条件是b>1，索引是会失效，走到是全表扫描。因为查找到b=1后，再去查找后面的数据，要的是完整的数据，所以得拿着存储的主键索引去回表查询，后面的数据过多是很麻烦的，所用的时间还不如全表扫描。
6.覆盖索引的底层原理
explain select b from t1 where b > 1;这个是能够走索引的，因为这个只需要查到b，以bcd为索引的B+树是包含b的值，所以不需要回表查询。这就叫覆盖索引，sql里面所查询的字段正好就在当前sql当前所利用的索引，上面有这个字段，不用回表。
7.索引扫描底层原理
explain select b from t1这个sql也是能够走bcd索引的。如果是走全表扫描，是从主键索引的叶子节点去遍历，把每条数据的b字段给取出来。b字段的值即会存在主键索引当中，还会存在bcd索引的B+树中。如果是在bcd的叶子节点去遍历，遍历的数据没有主键索引的数据完整，但都包含b字段的值，查询的速度肯定是不完整的数据比完整的快，所以走的会是bcd索引
8.order by 为什么会导致索引失效？
explain select * from t order by b,c,d;这个sql是不能走索引的。虽然bcd的B+树是按照bcd进行排序的，我们只需要按顺序拿到这几条数据就行。但是，因为我们要查询的数据是*，bcd存放的数据不完整，需要回表查询，存了多少数据就需要回几次表。
如果是走全表扫描，这个虽然不需要回表，但是有额外的排序操作
但是为什么要走全表扫描而不走bdc索引呢？是因为排序的操作实在内存中进行的，内存的操作比io要快很多。
9. mysql中的数据类型转换有什么需要注意的吗？
在varchar类型中，不是数字的字符如果转换为数字的话会统统转成0，比如'123' = 123 , 'abc' = 0
10.对字段进行操作导致索引失效原理
只要对字段进行的操作都会导致索引失效，比如select * from t1 where a+1 = 1，改b+树，它会该字段，该字段，其他查询就不能继续走这个索引，所以索引的字段万万不能改变。
11.Mysql中有哪些存储引擎
Innodb引擎
Innodb是Mysql默认事务型引擎，也是最重要的、使用最广泛的引擎。
MyISAM引擎
Myisam在Mysql5之前是默认引擎。但是Mysql不支持事务和行锁，而且崩溃后无法安全恢复。同时Myisam对整张表加锁，很容易因为表锁的问题导致性能问题。
Memory引擎
Memory引擎的效率比Myisam要快一个数量级，数据文件是存储在内存中。它的表结构在重启之后会保留，但数据会丢失。
Archive引擎
Archive只支持insert和select操作。会缓存所有的写并利用Zlib对插入的数据进行压缩。所以比MyIsam表的磁盘IO会更少。但是每次进行select都会对全表进行扫描。所以他非常适合日志和数据采集。
CSV引擎
CSV引擎可以将普通的CSV文件作为Mysql的表来处理，但不支持索引。所以他更适合作为数据交换的引擎。
12.MyIsam和Innodb的区别是什么？
- Innodb 支持事务而MyIsam不支持事务。
- Innodb支持外键而MyIsam不支持。
- Innodb是聚簇索引，MyIsam是非聚簇索引。
聚簇索引的文件存放在主键索引的叶子节点上，所以Innodb必须有主键。 - Innodb不保存表的总行数，查询总行数的效率不如Myisam。
- Innodb最小粒度的锁是行锁，而MyIsam是表锁。一个更新语句会锁住整张表，导致其他查询和更新都被阻塞。这也是MYSQL将默认存储引擎从MyIsam改为Innodb的主要原因。
13.数据表设计时，字段你会如何选择？
字段的优先级：整形＞date、time＞enum、char＞varchar＞blob、text
可以选整就不选字符串
尽量避免使用NULL
14.varchar最多能存储多少数据
对于Varchar()类型的列最多可以存储65535个字节。
15.请说下事务的基本特性
事务是以多条sql为一个整体单位对数据库的一次完整操作。事务应该具有四个特性。原子性、隔离性、持久性和一致性。
16.事务并发可能引发什么问题
1.脏读，2.不可重复读，3.幻读。不在这重新讲解每个的概念
17.简述下Mysql的各种索引
- 主键索引，一张表只能有一个主键索引。
- 唯一索引，建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引。
- 普通索引，建立在普通字段上的索引。
- 前缀索引，对字符类型字段的前几个字符或者对二进制类型字段的前几个bytes建立的索引，而不是建立在整个字段的。前缀索引可以建立在类型为char、varchar、binary、varbinary的列上。
- 全文索引，只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。
18.什么是三星索引
对查询而言，三星索引可以是最好的索引，如果查询使用三星索引，一次查询只需要进行一次IO操作，大大减小时间。
一个查询相关的索引行是相邻的或者是至少相距足够近的则获得一星
如果索引的数据排序和查找的排序是一致的，则获得二星
如果索引中的列包含查询中需要的则全列，则获得三星。
19.如何提高Insert的性能
- 合并多个
insert为一行。主要是多条insert合并后，日志量会减少很多，从而减少io操作来提高效率。 - 修改参数
bulk_insert_buffer_size，调最大批量插入的缓存。 - 设置
Innodb_flush_log_at_trx_commit=0
如果参数是0，则log buffer中的数据将以每秒一次的刷新频率写到log file，同时也会触发文件到磁盘的同步。
如果参数是1，在每次事务提交的时候将 log buffer 中的数据都会写入到 log file，同时也会触发文件系统到磁盘的同步
如果参数是2，事务提交会触发 log buffer 到 log file 的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘的同步操作。 - 设置事务提交为 手动提交，因为 MySQL 事务默认是自动提交的，这样每插入一条数据，都会进行一次 commit，所以通过手动提交事务，可以减少创建事务时的消耗。一般可以设置为 1000 条insert 提交一次。
20.什么是全局锁、共享锁、排它锁
全局锁
全局锁就是对整个数据库进行加锁。他的典型使用场景就是做全库逻辑备份。这个命令可以让库进入只读状态，其他任何命令都会阻塞。
共享锁
共享锁又称读锁，是读取操作创建的锁。其他用户可以并发的读取数据，但任何事务都不能对他的数据进行修改，直到释放所有的共享锁。如果事务对读锁进行修改，很容易造成死锁。
排它锁
排它锁，又称写锁。若某个事务在某一行加了排它锁，那么只能这个事务对其进行读写。其他事务、进程都不能对其进行任何操作，包括读操作。
21.谈一谈Mysql中的死锁
死锁是两个或者两个以上的进程或者线程在执行过程中，因为争夺资源而造成的一种相互等待的现象。若无外力操作，我们都无法推进下去。此时系统处于死锁状态。
如何查看死锁
使用命令show engine innodb status查看最近一次出现的死锁。
还可以使用Innodb Lock Monitor 打开监控，每15s输出一次日志。 使用完后建议关闭，会影响性能。
如何处理死锁
- 通过
Innodbblockwait_timeout来设置超时时间，一直等待直到超时。 - 发起死锁检测，发现死锁后，主动回滚到死锁中的某一事务，让其他事务继续执行。
22.Mysql如何实现读写分离
概念：就是将Mysql的数据拷贝成多份，写到其他的mysql服务器上面。原来的Mysql数据库负责写，称为主库；其他的Mysql数据库只负责读，称为从库，因为读多写少。
原理：读写分离原理的核心是依赖于一个日志，叫binlog。它是个二进制文件。如果你对mysql进行任何的数据写入，它都会去写到这个文件，但是查询不会。在主库里面他会启动一个异步线程。这个异步线程会把binlog复制到从库上面，从库收到数据后，它会去写一个叫做realy log的日志，叫中继日志。最后从库会启动一个SQL线程，这个线程就是去执行这个日志里面的insert和update就可以了。这么从库里面的数据就会同步。这样就实现了Mysql的读写分离。
优点：
- 不用担心一台Mysql宕机，
- 项目性能会更高。
缺点：
- 读写分离的机制是异步的，在非常极端的情况下，mysql的主库宕机了，binlog还没来得及复制，那么就会导致数据丢失。但是概率很低，是可以容忍的。
23.Mysql如何实现分库分表
对数据库和数据表进行水平拆分。垂直拆分的关注点在于业务，而水平拆分是将单一的数据表按照某种规则拆分到多个数据库或者多张数据表当中。他的关注点在数据的特点。
比如我们可以将库分为16张表，然后对用户的id进行hash()，将idd尽量的打散，对id进行取模。这就是典型的水平拆分方式。
24.索引的基本原理
索引是来快速的寻找那些具体的特定值的记录。
索引的原理：
- 把创建了索引的列的内容进行排序。
- 对排序的结果生成倒排序。
- 在倒排序内容上拼接数据地址链
- 在查询的时候，先拿到倒排序表内容，再取出数据链，从而拿到具体数据。
25.Mysql聚簇索引和非聚簇索引的区别
聚簇索引和非聚簇索引都是B+树。
- 聚簇索引：将数据存储与索引放到一起、并且是按照一定的顺序组织的，找到索引就找到了数据。数据的物理存放顺序与索引顺序是一致的。
- 非聚簇索引：叶子节点不存放数据、存储的数数据地址。也就是说根据索引查找到数据行的位置再取磁盘查找数据。
优势：
- 查询通过聚簇索引可以直接获得数据，相比非聚簇索引需要第二次查询效率要高。
- 聚簇索引对于范围的效率很高，因为其数据是按照大小排列的
- 聚簇索引非常适合在排序的场合，非聚簇索引不合适。
劣势：
- 维护索引很昂贵，特别是插入新行或者主键被更新导致要分页的时候。
- 表因为使用UUID（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能比全表扫描更慢，所以建议使用int的auto_incurment作为主键。
- 如果主键比较大的话，那么辅助索引将会变得更大，因为辅助索引的叶子存储的是主键值。过长的主键值会导致非叶子节点占用更多额外的空间。
26.索引的设计原则
查询快，占用空间更小。
- 适合索引的列是出现在where子句中的列，或者连接子句中指定的列。
- 基数较小的表，索引效果较差，没必要建立索引。
- 使用短索引，如果对长字符串进行索引，应该指定一个前缀长度，这样能节省大量空间。如果搜索词超过索引前缀长度，则使用索引 排除不匹配的行，然后检查其余行是否可行。
- 不要过度索引。索引需要额外的磁盘空间，并且降低写的操作性能。在修改表内容时，索引会进行更新甚至重构，索引列越多，这个时间越长。所以只保持需要的索引有利于查询。
- 定义有外键的数据列一定要建立索引。
- 更新频繁的字段不适合建立索引。
- 若是不能有效区分数据的列不适合作为索引列（如性别，就男和女，区分度实在太低。）
- 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加ab组合索引，只需要修改原来的索引即可。
- 对于那些查询中很少涉及的列，重复值比较多的列也不建立索引。
10.对于定义为text、image和bit的数据类型的列不要建立索引。
27.mysql的锁类型有哪些
基于锁的属性分类：共享锁，排它锁。
基于锁的粒度分类：行级锁，表级锁，页级锁，记录锁，间隙锁，临建锁。
基于锁的状态分类：意向共享锁，意向排它锁。它只是一个表的状态。
- 共享锁
共享锁又称读锁，当一个事务为数据加上共享锁时，其他事务只能对其进行读操作。共享锁的特性主要是为了支持并发的读取数据，读取数据时其他线程不能修改。
- 排它锁
排它锁又称写锁，当一个事务为数据加上排他锁时，其他请求不能再为数据加任何锁，也不允许其他任何线程修改、读取数据。
- 表锁
表锁是指为整张表上锁，当下一个事务访问改表的时候，必须等待上一个事务释放了锁才能访问。
特点：粒度大，加锁简单，容易冲突。
- 行锁
行锁是指上锁的是表中的某一行或者多行记录。其他事务访问同一张表时，只有被锁住的记录不能访问，其他可以正常访问。
- 记录锁
记录锁也属于行锁的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某条记录。
精确条件命中，并且命中的条件字段是唯一索引
加了记录锁后，数据可以避免数据在查询时被修改的重复读问题，也避免了脏堵。
- 页级锁
页级锁是Mysql中粒度介于行级锁和表级锁的一种锁。表级锁速度快，但是冲突多；行级锁冲突少，但是速度慢。所以折中考虑，一次锁相邻的一组记录。
特点：开销和加锁的事件不快和不慢，会出现死锁；锁的粒度介于表和行之间，并发度一般。
- 间隙锁
属于行锁的一种，间隙锁实在事务加锁后锁住的其实是表的某一区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。
范围查询并且查询未命中记录，查询的条件必须命中索引，间隙锁 只会出现在可重复读的事务级别中
触发条件：防止幻读问题，事务并发的时候，如果没间隙锁，就会出现A的事务两次查询结果条数不一样。
- 临建锁
也属于行锁的一种，并且他是Innodb的行级默认锁法，可以理解为记录锁和间隙锁的组合。临建锁会把查询出来的记录锁住，同时也会把该范围内的所有间隙空间给锁住。
触发条件：范围查询并且命中，查询命中了索引。
–
28.mysql的执行计划怎么看
explain select * from A where X=？ and Y=？通过explain关键字可以查询出本条sql的执行计划，里面有几个参数，分别代表着：
-
id 是一个有顺序的编号，是查询的序号，有几个select就显示几行。id的顺序是按select出现的顺序增长的。id列的值越大 执行优先级越高越优先执行。id的列值相同则从上往下执行。
-
table 查询的表
-
select_type 表示查询中每个select子句的类型。
-
partitions 打印分区表的信息
-
type 优化sql的重要字段，也是我们判断sql性能和优化程度的重要指标。
-
possible_keys
可能会走的索引名 -
key
真正会走的索引名 -
key_len 联合索引名字的索引长度
-
ref 命中的索引的字段名（数据库 表的名字）
-
rows 读取了多少行数据
-
filtered 百分比，读取的行数跟返回的行数的百分比。
-
extra 对mysql结果集进行排序的时候有没有走索引
29.事务的基本特性和隔离级别
30.怎么处理慢查询
在业务系统中，除了使用主键进行查询，其他都会在测试库上测试其耗时，慢查询的统计主要是由运维再做，会在定期将业务中的慢查询反馈给程序员。
慢查询的原因是什么？查询条件没有命中索引？是不是查询了一些不需要的数据列？还是数据量太大？
所以优化也是针对这三个方向来的
- 首先分析sql，看看是否查询了太多多余的列。
- 其次分析sql的执行计划，然后获得索引的情况，之后修改语句或者修改索引，使得语句尽可能的命中索引。
- 如果对语句无法进行优化，可以考虑是不是表的数据量太大，如果是的话可以进行横向或者纵向分表。
31.ACID靠什么保证
A原子性
原子性靠undo log日志来保证，在事务开启后，在日志里记录增、删、修改相反的操作，在事务回滚后执行undo log日志里面的sql。
I隔离性
隔离性由MVCC版本链来保证
D持久性
持久性由内存+redo log日志来保证，mysql修改数据的同时在内存和redo log记录这次操作，宕机时可以有由redo log恢复
开始Innodb会进行redo log的写盘，Innodb事务进入prepare准备状态
如果前面prepare成功，bin log写盘，把事务持久化到bin log中。如果持久化成功，那么Innodb事务则进入commit状态（在redo log里面写一个commit状态）。也就是说，如果redo log有一个commit标志，那么bin log是一定持久化成功的。
C一致性
一致性由其他三大特征来保证。
32.什么是MVCC
MVCC：多版本并发控制。
读取数据时通过一种类似于快照的方式将数据保存再来，这样读锁和写锁就不冲突了，不同的事务session会看到自己特定的版本的数据，版本链。
MVCC在Mysql中只在读已提交和可重复读这两个隔离级别下来工作。读未提交读取到的是最新的数据，是不符合当前事务版本的数据行。而串行化是对读取到行都加锁。
聚簇索引记录中有两个必要的隐藏列：
- trx_id：用来存储 每次对某条聚簇索引记录 进行修改的时候的事务ID。
- roll_pointer：每次对哪条聚簇索引记录有修改的时候，都会把老版本的写入
undo日志中。这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本位置，通过它来获得上一个版本的记录信息。这样就会形成一个版本链。
读已提交和可重复读的区别就在于他们生成ReadView的策略不同。
ReadView：在开始事务时创建readview，ReadView维护当前活动的事务id，即未提交的事务id，排序生成一个数组。
接着我们在访问数据的时候，会获得数据事务中的id（获取的的是最近的事务），然后跟未提交的的readview进行对比。
如果这个事务的id在readview左边（比readview小），可以访问（意味着左边的事务已经提交了）
如果这个事务的id在readview右边（比readview大）或者等于，意味着你的这个id在我创建的之后，说明你的事务还没提交，不可以访问。
读已提交隔离级别下的事务在每次查询时都会生成一个独立的ReadView，而可重复读是在第一次读的生成一个ReadView，之后的读用第一个。
2057
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
