MySQL必会知识点

说说你对与MySQL常见的两种存储引擎：MyISAM与InnoDB的理解

关于二者的对比与总结：
1.count运算上的区别：因为MyISAM缓存有表meta-data（行数等），因此在做COUNT（*）时对于一个结构很好的查询时不需要消耗多少资源的。而对于InnoDB来说，则没有这种缓存。
2.是否支持事务和崩溃后的安全恢复：MyISAM强调的是性能，每次查询具有原子性，其执行行速度比InnoDB类型更快，但是不支持事务。但是InnoDB提供事务的支持，外键等高级数据库功能。具有事务提交（commit）、回滚（rollback）和崩溃修复能力的事务安全（ACID）型表。
3.是否支持外键： MyISAM不支持，而InnoDB支持。

MyISAM更适合读密集的表，而InnoDB更适合写密集的表。在数据库做主从分离的情况下，经常选择MyISAM做为主库的存储引擎。一般来说，如果需要事务支持，并且有较高的并发读取频率(MyISAM的表锁的粒度太大，所以当该表写并发量较高时，要等待的查询就会很多了)，InnoDB是不错的选择。如果你的数据量很大（MyISAM支持压缩特性可以减少磁盘的空间占用），而且不需要支持事务时，MyISAM是最好的选择。

为什么要用 ORM? 和 JDBC 有何不一样?

1
2

orm是一种思想，就是把object转变成数据库中的记录，或者把数据库中的记录转变成object，我们可以用jdbc来实现这种思想，其实，如果我们的项目是严格按照oop方式编写的话，我们的jdbc程序不管是有意还是无意，就已经在实现orm的工作了。
现在有许多orm工具，它们底层调用jdbc来实现了orm工作，我们直接使用这些工具，就省去了直接使用jdbc的繁琐细节，提高了开发效率，现在用的较多的orm工具是hibernate。也听说一些其他orm工具，如toplink,ojb等。

存储过程与触发器的区别？

1
2
3

触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。
触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。
触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

Mysql如何为表字段添加索引？？？

1.添加PRIMARY KEY（主键索引）

ALTER TABLE table_name ADD PRIMARY KEY ( column )

2.添加UNIQUE(唯一索引)

ALTER TABLE table_name ADD UNIQUE ( column )

3.添加INDEX(普通索引)

ALTER TABLE table_name ADD INDEX index_name ( column )

4.添加FULLTEXT(全文索引)

ALTER TABLE table_name ADD FULLTEXT ( column)

5.添加多列索引

ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

为什么要使用索引?

1.通过创建唯一性索引，可以保证数据库中每一行的数据的唯一性
2.可以大大加快数据的检索速度（大大减少了检索的数据量），这也是创建索引的最主要的原因
3.帮助服务器避免排序和临时表
4.将随机IO变为顺序IO
5.可以加快表与表之间的连接，特别是在实现数据的参考完整性方面有特别的意义

索引这么多优点，为什么不对表中的每一个列创建一个索引？

1
2
3

1.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引，那么需要的空间就会更大
3.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

索引是如何提高查询速度的？

1	将无序的数据变成相对有序的数据（就像查目录一样）

使用索引的注意事项

1.在经常需要搜索的列上，可以加快搜索的速度
2.在经常使用在where子句中的列上面创建索引，加快条件的判断速度
3.在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序的查询时间
4.对于中、大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
5.在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度

6.避免where子句中对字段施加函数这回造成无法命中索引
7.在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键
8.将打算加索引的列设置为NOT NULL，否则将导致引擎放弃使用索引而进行全表扫描
9.删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗——MySQL5.7可以通过查询sys库的chema_unused_indexes视图来查询那些索引从未被使用
10.在使用limit offset查询缓存时,可以借助索引来提高性能

MySQL索引主要使用的两个数据结构

1.哈希索引
对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快;其余大部分场景,建议选择BTree索引

2.BTree索引
MySQL的BTree索引使用的是B树中的B+Tree。但对于主要的两种存储（MyISAM和InnoDB）的实现方式是不同的

MyISAM和InnoDB实现BTree索引方式的区别

1.MyISAM
	B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法来搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚集索引”。

2.InnoDB
	其数据文件本身就是索引文件。相比MyISAM索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。 PS：整理自《Java工程师修炼之道》

覆盖索引

1.什么是覆盖索引
	如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

2.覆盖索引使用实例
	现在我创建了索引(username,age)，在查询数据的时候：select username , age from user where username = 'Java' and age = 22。要查询出的列在叶子节点都存在！所以，就不用回表。

选择索引和编写利用这些索引的查询的3个原则

1. 单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机I/O要快很多，不过这一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引，用以提升效率。

2. 按顺序访问范围数据是很快的，这有两个原因。第一，顺序1/0不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUPBY查询也无须再做排序和将行按组进行聚合计算了。

3. 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。

你有没有做MySQL读写分离？如何实现MySQL的读写分离？MySQL主从复制原理的是啥？如何解决mysql主从同步的延时问题？

1、基于主从复制架构；搞一个主库，挂多个从库，然后我们就单单只是写主库，然后主库会自动把数据给同步到从库上去。
2、主库将变更写binlog日志，然后从库连接到主库之后，从库有一个IO线程，将主库的binlog日志拷贝到自己本地，写入一个中继日志中。接着从库中有一个SQL线程会从中继日志读取binlog日志，然后执行binlog日志中的内容，也就是在自己本地再执行一遍SQL，这样就可以保证自己跟主库的数据是一样的。
	这里有一个非常重要的一点，就是从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。所以这就是一个非常重要的点了，由于从库从主库拷贝日志以及串行执行SQL的特点，在搞并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。
	而且这里还有另一个问题，就是如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。
	所以MySQL实际上在这一块有两个机制，一个是半同步复制，用来解决主从数据丢失问题；一个是并行复制，用来解决主从同步延时问题。
	半同步复制（semi-sync复制）：指的是主库写入binlog日志以后，就将会强制此时立即将数据同步到从库，从库将日志写入自己本地的relay log 之后，接着会返回一个ack个主库，主库接收到至少一个从库的ack之后才会认为写操作完成了。
	并行复制：指的是从库开启多个线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。
	
3、show status，Seconds_Behind_Master，你可以看到从库复制主库的数据落后了几ms
	1、分库，将一个主库拆分为4个主库，每个主库的写并发就500/s，此时主从延迟可以忽略不计
	2、打开mysql支持的并行复制，多个库并行复制，如果说某个库的写入并发就是特别高，单库写并发达到了2000/s，并行复制还是没意义。28法则，很多时候比如说，就是少数的几个订单表，写入了2000/s，其他几十个表10/s。
	3、重写代码，写代码时要慎重，插入数据之后，直接就更新，不要查询
	4、如果确实是存在必须先插入，立马要求就查询到，然后立马就要反过来执行一些操作，对这个查询设置直连主库。不推荐这种方法，你这么搞导致读写分离的意义就丧失了