mysql杂谈

1：char类型字段的最大长度为255个字节。当保存为char值时，mysql会在它们的右边填充空格，以达到指定的长度。

2：mysql中避免插入重复记录的方法？
mysql存在主键冲突或者唯一键冲突的情况下，根据插入策略不同，一般有以下三种避免方法：insert ignore、replace into、insert on duplicate key update。注意：除非表中有primary key或unique索引，否则，使用以上三个语句没有意义，与使用单纯的insert into相同。
a：insert ignore会忽略数据库中已经存在的数据(根据主键或唯一键进行判断)。如果数据库中没有数据，就插入新的数据，否则就不插入。
b：replace into如果发现表中已经有此行数据，则先删除此行数据，然后插入新的数据，否则，直接插入新数据。replace into执行完后会返回一个数，来指示受影响的行的数目，该数目是被删除和被插入的行数的和。
c：insert on duplicate key update,有重复值时更新，否则插入。如果有新记录被插入，则受影响行的值是1，如果原有记录被更新，则受影响的行是2，如果记录被更新前后的值是一样的，则受影响的行是0。

总结：insert ignore能忽略重复数据，只插入不重复的数据。replace into和insert on duplicate key update都是替换原有的重复数据。区别在于replace into是删除原有的行后在插入新行，如果有自增id，会导致自增id的改变。
insert on duplicate key update在遇到重复行时，会直接更新原有的行。

3：什么是binlog？
binlog日志用于记录所有更新了数据或者已经潜在更新了数据的所有语句。语句以事件的形式保存，它描述了数据的更改。它一般用于数据备份以及主从复制。其中最重要的是sync_binlog这个参数，它直接影响mysql的性能和完整性。等于0时，当事物提交后，mysql仅仅是将binlog_cache中的数据写入binlog文件，但不会同步到磁盘，而让文件系统自行决定什么时候来做同步，这个性能是最好的。等于n时，表示在进行n次事物提交以后，mysql将执行一次磁盘同步指令，通知文件系统将binlog文件缓存刷新到磁盘。它的默认值是0，性能最好，但风险也是最大的，一旦系统崩溃，在文件系统缓存中的binlog信息都会丢失。

4：字符类型和数字类型之间的性能有差别吗？
两者的主要差别在于，字符类型有字符集的概念，每次从存储端到展现端之间都有一个字符集编码的过程，这一过程主要消耗的是cpu资源。

5：timestamp占4个字节，表示的时间到2038年；datetime占8个字节，表示的时间到9999年。

6：mysql_install_db脚本的目的是生成新的授权表，它不覆盖已有的mysql授权表，并且它不影响其他数据。

7：数据库管理软件中最大的性能瓶颈是磁盘io。对于面向交易的处理系统，其特点是并发量大，整体数据量比较多，但每次访问的数据量又比较少，且访问的数据比较离散，活跃数据占总体数据的比例不是很大。

8：数据库的锁定机制简单来说就是数据库为了保证数据的一致性而使各种资源在被并发访问时变得有序所设计的一种规则。
行级锁定：行级锁定的最大特点就是锁定对象的颗粒度很小，所以发生锁定资源争用的概率很小，能够给与应用程序尽可能大的并发处理能力。但是由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也很多，带来的消耗自然也就更大，而且行级锁也很容易发生死锁。
表级锁定：表级锁定的锁定颗粒度很大，一次会将整个表锁定，所以发生资源争用的概率也很大，会导致并发处理能力降低。

9：当query无法使用索引时，innodb会放弃使用行级锁定而改用表级锁定，会造成并发性能的降低。因为无法使用索引可能会导致全表扫描，所以就无法锁定行。

10：对于myisam，由于锁定级别是不能改变的，所以我们需要尽可能的让锁定时间变短，而缩短锁定时间的唯一办法就是让我们的query的执行时间尽可能的短。
a：尽量减少大的复杂的query，将复杂的query拆分成几个小的query分布进行。
b：尽可能建立高效的索引，让数据检索更迅速。
c：尽量让myisam存储引擎的表只存放必要的信息并选择合适的字段类型。

11：在mysql中存在两种排序算法：一种是mysql4.1之前的老算法，实现方式是先取出需要排序字段数据的指针信息，在排序区中进行排序，完成排序之后，再次通过行指针信息取出所需要的行，也就是说这种排序算法需要访问两次数据。第二种排序算法是mysql4.1之后改进的算法，一次性将所需要排序的行全部取出，在排序区中进行排序，排序完成后直接返回给客户端。改进后的算法只需要访问一次数据，减少了大量的随机io，极大的提高了带有排序语句query的效率。但是这种改进后的算法，需要一次性取出并缓存的数据比第一种算法要多很多，如果我们将不需要的数据也取出来，就会极大的浪费排序过程中所需要的内存，所以在query中仅仅取出我们所需要的数据是非常有必要的。

12：尽可能避免复杂的join和子查询，因为我们的query语句所涉及的表越多，所需要锁定的资源就越多，所阻塞的其他线程也就越多，相反，如果我们将比较复杂的query语句拆分成多个较为简单的query语句，然后分布执行，每次锁定的资源也就少很多，所阻塞的其他线程也就少一些，同时被其他线程阻塞的概率也较小。

13：mysql innodb 一般按照每张表的主键构造一颗b+树，存放在表空间，其中每张表的行记录就是这颗b+树的的叶子节点，存放在表空间的数据段中，这就意味着数据行在b+树中的存储是有序的。因此，mysql通过b+树查找算法能够加快数据的访问，避免扫描整个表。

14：myisam的索引和数据是分开存储的，myisam通过key buffer把索引先缓存到内存中，当需要通过索引访问数据时，先在内存中查找索引，然后通过索引找到数据在磁盘上的对应数据，这也就是索引不在key buffer命中时速度慢的原因。而innodb的数据和索引是存放在一起的。

15：聚族索引的优点：聚族索引把索引和数据都保存在同一颗b+树数据结构中，并且同时将相关的索引列和数据行保存在一起，这就意味着当你访问同一数据叶(行)的不同字段时已经把叶加载到了buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。不同于myisam引擎，他将索引和数据分开存放，这样索引对应的是磁盘位置，不得不通过磁盘位置访问磁盘数据。

16：聚族索引的缺点：
1：主键被更新时会导致分页情况的发生，在分页的过程中被移动的数据可能造成内存碎片。
2：次要索引访问数据始终需要二次查找，而不是一次。次要索引叶子节点存储的是它索引的数据对应的主键值而不是行的物理位置；次要索引首先通过自己的叶子节点找到主键值，再通过主键值找到数据页，然后通过数据页就可以找到数据行。

17：索引的弊端：索引能极大的提高数据检索的效率，也能够改善排序分组操作的性能。但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据，也就是说如果我们更新了带有索引字段的信息，那么索引也需要相应的更新，这样就会带来额外的io消耗，而且索引还需要占用存储空间。

18：索引创建的场景：
a：较频繁作为查询条件的字段应该创建索引。
b：唯一性太差的字段不适合单独创建索引，比如，值为春夏秋冬等的字段。
c：更新非常频繁的字段不适合创建索引。

19：join的实现原理：
在mysql中。只有一种join算法，就是nested loop join。实际上就是通过驱动表的结果集作为循环的基础条件，然后一条一条的通过该结果集中的数据作为过滤检索条件到下一个表中查询数据，然后合并结果，如果还有第三个表参与join，则通过前两个表的join结果集作为循环基础数据到第三个表中查询数据，如此往复。

20：join语句的优化：
a：尽可能减少join语句循环的总次数。就是让驱动表的结果集尽可能小。因为驱动表的结果集越大，就意味着需要循环的次数越多。比如a表有10条记录，b表有20条记录，如果a表作为驱动表，则只需要循环10次，如果b表作为驱动表就需要循环20次。
b：优化优化内存循环。不仅仅是在数据库的join中应该这么做，实际上我们在优化程序语言的时候也有类似的优化原则，内层循环在循环中执行的次数是最多的，每次循环节约很小的资源，在整个循环中就能节约很多的资源。那么我们该怎么优化内存循环呢？就是在被驱动表join条件字段上建立索引。
c：当无法保证被驱动表的join条件字段被索引并且内存资源充足的前提下尽量把join buffer设置的大一点。

21：group by同样也需要排序操作，它与order by相比只是多了排序之后的分组操作。

22：max_connections：整个mysql允许的最大连接数。

23：back_log：在mysql的连接请求等待队列中允许存放的最大连接请求数。连接请求等待队列实际上是指当某一个时刻客户端的连接请求数量过大的时候，mysql主线程没办法及时给每一个新的连接请求分配连接线程的时候，还没有分配到连接线程的所有请求将存放在一个等待队列中，这个队列就是连接请求队列。

24：在mysql中，为了尽可能提高客户端请求创建连接线程这个过程的性能，实现了一个thread cache池，将空闲的连接线程存放在其中，而不是完成后就销毁，当有新的连接请求的时候，mysql会首先检查Thread cache池中是否存在空闲的连接线程，如果有，则直接使用，没有，则重新创建。
对应的字段在mysql中叫thread_cache_size。当系统最初启动的时候，并不会马上就创建thread_cache_size所设置的值，而是随着连接线程的创建及使用，慢慢的将用完的连接线程存放在其中。当存放的连接线程达到了thread_cache_size的值之后，mysql就不会在继续保存用完的连接线程了。如果我们的应用程序使用的是短连接，thread_cache_size的功效是非常明显的，因为在短连接的数据库中，数据库连接的创建和销毁是非常频繁的，如果每次都需要让mysql新建和销毁相应的连接线程，那么资源的消耗会非常大。

25：由于多线程的实现机制，为了尽可能的提高性能，在mysql中，每个线程都是独立的打开自己需要的表文件描述符。当然，针对不同的存储引擎可能有不同的处理方式，对于myisam表，每一个客户端线程打开任何一个myisam表的数据文件都需要打开一个文件描述符，但如果是索引文件，则可以多个线程共享同一个索引文件描述符。对于innodb存储引擎，如果我们使用的是共享表空间来存储数据，那么需要打开的文件描述符就比较少，而如果使用的独享表空间的方式来存储数据，同样需要打开很多个表文件描述符。

26：table cache：主要是cache打开的所有表文件描述符，当有新请求的时候不用在重新打开。使用结束的时候也不用立即关闭。

27：join_buffer_size：为每一个join请求分配的缓存。每个请求都会创建自己独立的buffer，而不是整个系统共享。

28：sort_buffer_size：对数据排序时使用的buffer。每个请求都会创建自己独立的buffer，而不是整个系统共享。

29：myisam存储引擎，它仅仅缓存索引数据，并不会缓存实际的表数据信息到内存中，而是将这一工作交给了文件系统缓存。就是说，当我们通过索引查找数据时，会先去key_cache中读取索引，有，则直接通过key_cache中的索引从磁盘中读取数据，无，则从磁盘中读取索引，然后通过索引读取数据，同时将索引存入key_cache中。

30：key_buffer_size：索引缓存大小。

31：由于myisam存储引擎表级锁定的机制以及读写互斥的问题，其并发写的性能一直是一个让人头疼的问题，一般来说，我们能做的也就只有以下几点：
a：打开concurrent_insert的功能。

32：innodb存储引擎和myisam存储引擎最大区别主要有四点：
a：缓存的机制不同，innodb会同时缓存数据和索引，而myisam只会缓存索引数据。也就是说，在查找数据时，myisam会先在内存中找到索引，然后通过索引在去磁盘上查询数据。
b：事务支持。innodb支持事务，而myisam不支持。
c：锁定实现不同。innodb是行级锁定，myisam是表级锁定。
d：数据存储方式不同。myisam会把索引和数据分开存放，而innodb是将他们存储在一起。

33：磁盘的顺序读写就是将数据顺序的写入连续的物理空间，而随机读写则相反，数据需要根据各自的特定位置被写入各个位置，也就是被写入了并不连续的空间，对于磁盘来说，写入连续的位置最大的好处就是磁头所做的寻址动作很少，而磁盘操作中最耗费时间的就是磁头的寻址。所以，顺序读写要比随机读写性能高很多。

33：实际上，innodb在修改数据的时候只是修改缓存中的数据，并不是在一个事务提交的时候就将缓存中被修改的数据同步到磁盘，而是将修改的信息记录到相应的事务日志中。我们的应用所修改的buffer pool中的数据都很随机，如果每次修改之后都将buffer pool中的数据同步到磁盘，那么磁盘就只能一直忙于频繁的随机读写操作。而事务日志在创建之初就是申请的连续的物理空间，而且每次写入都是紧接着之前的日志数据顺序的往后写入，基本上都是一个顺序的写入过程，所以，日志的写入操作远比同步buffer pool中被修改的数据要更快。而且，事务日志本身也有buffer(log_buffer)，每次事务日志的写入并不是直接写入到文件，而是先存储到log_buffer中，然后在一定的事件触发下才会同步到文件。事务日志文件的大小与innodb的整体io性能有非常大的关系，理论上来讲，日志文件越大，则buffer pool所做的刷新动作也就越少，性能也就越高。事务日志的作用主要有两个：一个是上面所提到的提高系统整体的io性能，另外一个就是当系统宕机之后的数据恢复。innodb事务日志文件记录了我们每一次对数据库中的数据及索引所做的修改以及与修改相关的信息，同时还记录了checkpoint与日志序列号。当mysql从宕机之后再次启动，innodb会通过比较事务日志中所记录的checkpoint信息和各个数据文件中的checkpoint信息，找到最后一次checkpoint所对应的日志序列号，然后通过事务日志中所记录的变更记录，将从宕机之前最后一次的checkpoint往后的所有变更重新应用一次，这样就找回了因为系统宕机而造成的所有数据丢失。当然，对于log buffer中未来得及同步到日志文件的数据就无法再次找回了。

34：innodb事务日志刷新方式的参数：innodb_flush_log_at_try_commit，这个参数的主要功能就是让我们告诉系统，在什么情况下该同志文件系统刷新缓存中的数据到磁盘文件。
a：等于0时，表示每隔1秒同步log_buffer中的数据到磁盘文件。
b：等于1时，表示每次事务提交都会同步。这种方式最安全，但效率低。
c：等于2时，表示让操作系统来决定什么时候同步，效率最高，但不安全。

35：读锁是共享的，不会阻塞其他读写操作。写锁是排他的，会阻塞其他读写操作。写锁比读锁有更高的优先级，因此，一个写锁可能会被插入到一个读锁队列的前面。而且服务器会对alter table之类的语句使用表锁，而忽略存储引擎本身的锁机制。

36：事务的特性：
a：原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作，要么全表成功，要么全部失败回滚。
b：一致性。
c：隔离线：通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。
d：持久性：一旦事务提交，则其所做的修改就会永久的保存到数据库中，此时，即使系统崩溃，修改的数据也不会丢失。

37：死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源从而导致恶性循环的现象。也就是说，若无外力作用，死锁将会一直持续下去。

38：在mysql中，行级锁并不是直接锁记录数据，而是锁索引，如果一条sql语句操作了主键索引，mysql就会锁定这条主键索引，如果一条语句操作了非主键索引，mysql会先锁定非主键索引，在锁定主键索引。

39：mysql在执行查询语句前，会自动给涉及的所有表加上读锁，在执行更新操作前，会自动给涉及的表加上写锁。

40：mysql可以为整数类型指定宽度，例如int(1)和int(11)它们并不会限制值的合法范围，只是规定了mysql的一些交互工具用来显示字符的个数，对于存储和计算来说，它们是相同的。

41：char(1)和varchar(1)有什么区别？
它们都只能保存单个字符，但是，varchar要比char多占用一个存储位置，主要是因为使用varchar数据类型时，会多用一个字节来存储长度信息。

42：varchar(20)和varchar(100)有什么区别？
varchar数据类型，硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的，比如你用它们存储hello，在磁盘空间的使用上是一样的，那么使用更短的列有什么优势呢？使用更长的列会消耗更多的内存，因为mysql通常分配固定大小的内存块来保存内部值。比如说varchar(20)会分配20个字节长度的内存，而varchar(200)会分配200个字节长度的内存空间。

43：char字符类型，字段的数据在内存中都是紧挨着存储的，所以char不存在内存碎片的问题，而可变长度的字符数据类型，其存储长度是可变的，当更改前后数据长度不一致时就会产生碎片的问题。

44：索引不能是表达式的一部分，否则将无法使用到索引，如：select * from test where uid+1=5;这无法使用到索引。而且索引字段也不能是函数的一部分，否则也不能使用到索引。

45：如果需要排序的数据量小于排序缓冲区，mysql使用内存进行快速排序操作

46：有时，mysql无法将限制条件从外层，下推到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询优化上。如果希望union的各个子句能够根据limit只取部分结果集，或者希望能够先排好序在合并结果集的话，就需要在union的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后取前20条记录：
(select first_name from a order by first_name limit 20) union all (select first_name from b order by first_name limit 20) limit 20。如果不在两个子句中分别加上limit 20，那么sql语句会将两个表的记录都取出来，放进一个临时表中，然后在从临时表中取出20条记录，这样的话，取出的数据就会多很多。而且，从临时表中取出数据的顺序并不是一定的，所以，如果想获得正确的数据，还需要加上一个全局的order by。

47：最大化和最小化优化：
对于min和max查询，mysql的优化做的并不好，如：select min(id) from test where first_name=‘lbj’;因为，first_name字段上并没有索引，因此mysql会进行全表扫描。我们更改后的sql：select id from test use INDEX(PRIMARY) where first_name=‘lbj’ limit 1;一般情况下，我们通过sql语句告诉服务器我们需要什么数据，由服务器来决定如何最优地获取数据，不过更改后的sql是告诉mysql如何去获取我们需要的数据。

48：一个容易产生的误解是myisam的count()函数非常快，不过这是有前提条件的，即只有没有任何where条件的count()才非常快。

49：分区的作用？
a：提升性能：没有分区之前，数据的每次查询面向的是整个数据库和表，分区之后，mysql针对每个分区生成特定的数据文件与索引文件，每次只会检索特定部分的数据，因此速度非常快。究其原因还是因为在分区之后表被指派到了不同的物理驱动器上，这样便可减少物理io的争用。

50：read_buffer_size(顺序读取数据缓冲区使用的内存)：这部分内存主要用于当需要顺序读取数据的时候。如：无法使用索引情况下的全表扫描、全索引扫描等。在这种时候，mysql按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在read_buffer_size中，当buffer空间被写满，或者全部数据读取结束后，再将buffer中的数据返回给调用者，以提高效率。

51：read_rnd_ buffer_size(随机读取数据缓冲区使用内存)：当mysql进行随机读取数据块的时候会利用这个缓冲区暂存数据。如：根据索引信息读取表数据，根据排序后的结果集进行join等。

52：Datetime和Timestamp在mysql内部都以二进制而非字符串的形式存储。但是timestamp占用4个字节，而datetime则使用8个字节，这样造成的结果是timestamp只能存储1970年到2038年之间的时间，而datetime可以存储到9999年之间的时间。另一个重要的差别是时区的改变会对timestamp造成影响，而datetime列不会受到影响。

53：mysql5.6对子查询进行了大幅度的优化，将子查询的结果存入临时表，使得子查询只执行一次，而且优化器还会给子查询产生的派生表添加索引，使得子查询的性能得到了大幅度的提高。

54：undo log的原理很简单，为了满足事务的原子性，在操作任何数据之前，mysql会先将数据备份到一个地方，这个存储数据备份的地方就称为undo log。在进行数据修改的时候，如果出现了错误或者rollback，系统就可以利用undo log中的备份数据恢复到事务开始之前的状态。缺陷：每个事务提交前都会将数据和undo log写入磁盘，这样就会导致大量的磁盘io，因此，性能很低，所以，引入了另外一种机制来实现持久化(redo log)。

55：redo原理：和undo log相反，redo log记录的是新数据的备份，在事务提交前只要将redo log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是redo log已经持久化了，系统可以根据redo log的内容，将所有数据恢复到最新的状态。

56：mysql字符集的问题：对于emoji表情的话，mysql的utf8是不支持的，因为utf8一个字符最多3个字节，而emoji表情一个字符有4个字节。我们需要把utf8修改为utf8mb4，才能支持。从mysql5.5开始，mysql支持utf8mb4,而utf8mb4可以兼容utf8，所以，最好用这个字符编码。

57：float和double在数据库中保存的是近似值，而decimal则以字符串的形式保存数值，它用于保存确切精度的值，例如货币数据。

58：使用explain解析sql执行计划，首先看type列的结果，如果类型是all，表示会进行全表扫描，在看下extra列的结果，
using temporary：表示需要创建临时表以满足需求，通常是因为group by的列没有索引。
useing filesort：表示无法利用索引完成排序。
using where：通常是因为全表扫描或全索引扫描时又加上了where条件，建议添加适当的索引。

59：wait_timeout：指的是mysql在关闭一个非交互连接之前所等待的秒数，即这个连接在空闲多久会被关闭。mysql服务器所支持的最大连接数是有上限的，每个连接的建立都会消耗内存，因此，我们希望在客户端处理完相应的操作后应该断开连接并释放内存。

60：二进制日志：任何引起或可能引起数据库变化的操作都会被写进二进制日志中。作用：主从复制、数据恢复。

60：utf8_bin和utf8_general_ci的区别：
utf8_general_ci中的ci表示大小写不敏感，如：a和A在字符串判断中被当做一样。bin是二进制，a和A会被区别对待。
utf8_general_ci：不区分大小写。
utf8_general_cs：区分大小写。
utf8_bin：字符串中的每个字符用二进制数据编译存储，区分大小写，而且可以存二进制内容。

61：sql关键字不区分大小写，如select和SELECT是相等的。mysql_install_db脚本的目的是生成新的授权表，它不覆盖已有的mysql授权表，并且它不影响已有的mysql授权表和其他数据。

62：mysql使用3个线程来执行主从复制，一个在主服务器上，另外两个在从服务器上，当发出start slave时，从服务器创建一个IO线程，用于连接主服务器，主服务器会创建一个线程将二进制日志中的内容通过IO线程发送给从服务器，从服务器的IO线程读取主服务器发送过来的内容并将该数据拷贝到自己的中继日志中，从服务器会创建一个sql线程，用于读取中继日志中的内容。