【面试】MySQL

Some problems：

什么是MySQL？

MySQL是一种关系型数据库，在java企业级开发中非常常用，由于MySQL是免费开源的，并且扩展方便。阿里巴巴数据库系统也大量用到了MySQL，因此它的稳定性是有保障的。MySQL默认端口是3306。

 

存储引擎：

查看MySQL所提供的所有存储引擎：show engines;

 

从上图我们可以看出MySQL默认的引擎是InnoDB，且只有InnoDB是事务性存储引擎，也就是说只有InnoDB支持事物。

 

InnoDB和MyISAM的区别？

MyISAM是MySQL5.5版本之前的默认数据库引擎，虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事物和行级锁，而且最大的缺陷就是崩溃后无法恢复。不过5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL5.5版本后默认的存储引擎是InnoDB。

大多数时候我们使用的都是InnoDB存储引擎，但是在某些情况下使用MyISAM也是合适的，比如读密集的情况下（如果不介意MyISAM崩溃恢复的话）。

两者对比：

1）是否支持行级锁：MyISAM只支持表级锁，而InnoDB既支持表级锁又支持行级锁，默认为行级锁；

2）是否支持事物和崩溃后的恢复：MyISAM强调的是性能，每次查询具有原子性，其执行速度比InnoDB更快，但不支持事物。InnoDB提供支持事物、外部键等高级数据库功能。具有事物（Transaction）、回滚（rollback）和崩溃修复能力（crash recovery capabilities）的事物安全（transaction-safe（ACID））型表；

3）是否支持外键：MyISAM不支持，InnoDB支持；

4）是否支持MVCC（Multi-Version Concurrency Control）：仅InnoDB支持。应对高并发事物，MVCC比单存的加锁更高效；MVCC只在read commited和repeatable read两个隔离级别下工作；MVCC可以使用乐观锁和悲观锁来实现；各数据库中MVCC实现并不统一。

MySQL-InnoDB-MVCC多版本并发控制 ： https://segmentfault.com/a/1190000012650596

一般情况下我们选择InnoDB都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事物支持，也不在乎崩溃后的恢复问题的话，MyISAM是一个不错的选择。

【MVCC（Multi-Version Concurrency Control）：多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事物内存。

如果有人从数据库中读取数据的同时，有另外的人写入数据，有可能读数据的人会看到【半写】或者不一致的数据。有很多种方法来解决这个问题，叫做并发控制方法。最简单的方法是加锁，通过加锁，让所有读者等待写者工作完成，但是这样效率会很差。MVCC使用了一种不同的手段，每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写者写操作造成的变化在写操作之前（或者数据库事物提交之前）对于其他读者来说是不可见的。

当一个MVCC数据库需要更新一条数据记录的时候，它不会直接用新数据覆盖旧数据，而是将旧数据标记为过时，并在别处增加新版本的数据。这时就会有存储多个版本的数据，但只有一个是最新的。这种方式允许读者读取在他读之前已经存在的数据，即使这些在读的过程中半路被别人修改了、删除了，也对正在读的用户没有影响。这种多版本的方式避免了修改删除操作在内存和磁盘存储结构造成的空洞的开销，但是需要系统周期性整理以删除老的、过时的数据。对于面向文档的数据库来说，这种方式允许系统将整个文档写到磁盘的一块连续区域上，当需要更新的时候，直接重写一个版本，而不是对文档的某些比特位、分片切除，或者维护一个链式的、非连续的数据库结构。】

 

索引：

MySQL索引使用的数据结构主要有BTree索引和哈希索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

MySQL的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

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

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

 

什么是事物？

事物是逻辑上的一组操作，要么都执行，要么都不执行。

事物最经典也最经常被说的例子就是转账了。假如小宇要给小美转账1000，这个转账会涉及到两个关键操作：将小宇的余额减少1000，将小美的余额增加1000。万一在这两个操作之间突然出现错误，比如银行系统崩溃，导致小宇余额减少而小美余额没有增加，这就不对了。事物就是要保证这两个操作要么都成功，要么都失败。

事物的四大特性：ACID

原子性：事物是最小的执行单位，不允许分割。事物的原子性要确保操作要么全部完成，要么全部不起作用；

一致性：执行事物前后，数据保持一致，多个事物对同一个数据读取的结果是想同的；

隔离性：并发访问数据库时，一个用户的事物不被其他用户所干扰，各并发事物之间数据库是独立的；

持久性：一个事物被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

 

并发事物带来哪些问题？

在典型的应用程序中，多个事物并发运行，经常会操作相同的数据库来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题：

1）脏读：当一个事物正在访问数据并对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事物也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事物读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的；

2）丢失数据：指在一个事物读取一个数据时，另外一个事物也访问了该数据，那么在第一个事物中修改了这个数据后，第二个事物也修改了这个数据，这样第一个事物内的修改结果就被丢失，因此称为数据丢失。例如事物1读取某表中的数据A=20，事物2也读取A=20，事物1修改A=A-1，事物2也修改A=A-1，最终结果A=19，事物1 的修改被丢失；

3）不可重复读：指在一个事物内多次读同一个数据。在这个事物还没结束时，另一个事物也访问该数据。那么，在第一个事物中的两次读数据之间，由于第二个事物的修改，导致第一个事物两次读取的结果可能不太一样。这就发生了在一个事物内两次读到的数据是不一样的情况，因此称为不可重复读；

4）幻读：幻读与不可重复读类似。它发生在一个事物T1读取了几行数据，接着另一个并发事物T2插入了一些数据时。在随后的查询中，第一个事物T1就发现多了一些原本不存在的数据，就好像幻觉一样，所以称为幻读。

不可重复读与幻读的区别：

不可重复读的重点是修改，比如多读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除，比如多次读取一条记录发现记录增多或减少了。

 

事物的隔离级别有哪些？MySQL的默认隔离级别是什么？

SQL标准定义了四个隔离级别：

READ-UNCOMMITTED（读取未提交）：最低的隔离级别，允许读取尚未提交的数据，可能会导致脏读、幻读，不可重复读；

READ-COMMITTED（读取已提交）：允许读取并发事物已经提交的数据，可以阻止脏读、但是幻读或不可重复读仍有可能发生；

REPEATABLE-READ（可重复读）：对同一字段的多次读取结果都是一致的，除非数据是被本身事物自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生；

SERIALIZABLE（可串行化）：最高的隔离级别，完全服从ACID的隔离级别。所有的事物依次逐个执行，这样事物之间就完全不可能产生干扰，也就是说，该级别可以防止脏读，不可重复读以及幻读。

MySQL InnoDB存储引擎默认支持的隔离级别是REPEATABLE-READ（可重复读）。

这里需要注意的是：与SQL标准不同的地方在于InnoDB存储引擎在**REPEATABLE-READ（可重复读）事物隔离级别下使用的是Next-Key Lock锁算法，因此可以避免幻读的产生，这与其他数据库系统（SQL-Server）是不同的。所以InnoDB存储引擎默认支持的隔离级别是REPEATABLE-READ（可重复读），已经可以保证事物的隔离性要求，即达到了SQL标准的SERIALIZABLE（可串行化）隔离级别。

因为隔离级别越低，事物请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED（读取提交内容）。

InnoDB存储引擎在分布式事物的情况下一般会用到SERIALIZABLE（可串行化）隔离级别。

 

锁机制与InnoDB锁算法：

MyISAM和InnoDB存储引擎使用的锁：

　　MyISAM采用表级锁（table-level locking）；

　　InnoDB支持行级锁（row-level locking）和表级锁，默认行级锁。

表级锁和行级锁对比：

表级锁：MySQL中锁定 粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和InnoDB引擎都支持表级锁；

行级锁：MySQL中锁定 粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减小数据库操作的冲突，其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

参考：MySQL锁机制  https://blog.csdn.net/qq_34337272/article/details/80611486

InnoDB存储引擎锁算法有三种：

　　Record lock：单个行记录上的锁；

　　Gap lock:间隙锁，锁定一个范围，不包括记录本身；

　　Next-key lock：record+gap 锁定一个范围，包含记录本身。

相关知识点：

1）InnoDB对于行的查询使用next-key lock；

2）next-key lock为了解决幻读问题；

3）当查询的索引含有唯一属性时，将next-key lock降级为 record；

4）Gap锁设计的目的是为了阻止多个事物将记录插到同一范围内，而这可能会产生幻读；

5）有两种方式可以显示关闭Gap锁：（除了外键约束和唯一性检查外，其余情况均使用record lock）A：将事物隔离级别设置为RC；B：将参数innodb_locks_unsafe_for_binlog设置为1。

 

大表优化：

当MySQL单表记录数过大时，数据库的CURD性能会明显下降，一些常见的优化措施如下：

1）限定数据的范围：

务必禁止不带任何限定数据范围条件的查询语句。比如：当用户查询历史订单的时候，限定一个月的范围内；

2）读写分离：

经典的数据库拆分方案，主库负责写，从库负责读；

3）垂直分区：

根据数据库里面数据表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分为两个单独的表，甚至放到单独的库做分库。

简单来说，垂直拆分是指数据表 列的拆分，把一张列比较多的表拆分为多张表。

垂直拆分的优点：可以使得列数据变小，在查询时减少Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起join操作，可以在应用层进行join来解决。此外，垂直分区会让事物变得更加复杂。

4）水平分区：

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

需要注意一点的是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。

水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事物难以解决，跨节点join性能较差，逻辑复杂。

补充一下数据库分片的两种常见方案：

1）客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当当网的Sharding-JDBC、阿里的TDDL是两种比较常见的实现；

2）中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在谈的Mycat，360的Atlas，网易的DDB等等都是这种架构的实现。

参考：MySQL大表优化方案： https://segmentfault.com/a/1190000006158186

 

一条SQL语句在MySQL中是如何执行的？

https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485097&idx=1&sn=84c89da477b1338bdf3e9fcd65514ac1&chksm=cea24962f9d5c074d8d3ff1ab04ee8f0d6486e3d015cfd783503685986485c11738ccb542ba7&token=79317275&lang=zh_CN#rd

 

MySQL高性能优化建议？

https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485117&idx=1&sn=92361755b7c3de488b415ec4c5f46d73&chksm=cea24976f9d5c060babe50c3747616cce63df5d50947903a262704988143c2eeb4069ae45420&token=79317275&lang=zh_CN#rd

MySQL高性能分析包括 数据库命名规范，数据库基本设计规范，数据库表字段设计规范，索引设计规范，SQL编写规范，数据库操作行为规范等六个方面。

数据库命名规范：

• 数据库对象名使用小写并用下划线分割；
• 数据库对象名禁止使用MySQL保留关键字；
• 数据库对象名尽量做到见名知意且长度不要超过32个字符；
• 临时表使用tmp为前缀且带日期，备份表使用bak为前缀且带日期；

数据库基本设计规范：

• 没有特殊要求所有表使用Innodb存储引擎；
• 数据库和表的字符集统一使用UTF8，兼容性更好；
• 所有表和字段都需要添加注释，从一开始就进行数据字典的维护；
• 单表数据尽量控制在500W以内；【数据量过大会造成修改表结构，备份，恢复都有很大的问题，可以用历史数据归档，分库分表等手段控制】
• 谨慎使用MySQL分区表；【分区表在物理上表现为多个文件，逻辑上为一个表，跨分区查询效率可能更低】
• 尽量做到冷热数据分离，减小表的宽度；【减少磁盘IO，保证热数据的内存缓存命中率】
• 禁止在表中建立预留字段；
• 谨慎在数据库中存储图片，文件等大的二进制数据；
• 禁止在线上做数据库压力测试；
• 禁止开发环境，测试环境直接连接生产数据库。

数据库表设计规范：

• 选择符合存储需要的最小的数据类型；【例如IP存储，可以选择使用inet_aton方法转换成整型数据存储，inet_ntoa整型转IP】
• 避免使用text，blob数据类型；【建议将blob或者text列分离到单独的扩展表中】
• 避免使用ENUM类型；【ENUM类型的order by操作效率低，需要额外操作】
• 尽可能将所有的列定义为not null；【1.索引null列需要额外的空间来保存；2.进行比较和计算时null值需要做特别的处理】
• 存储时间尽量使用TIMESTAMP和DATETIME；
• 与财务相关的金额数据类型使用decimal。

索引相关规范：

• 单张表索引个数一般不超过5个；
• 禁止给表中的每一列都建立单独的索引；
• 索引尽量选择出现在select，update，delete语句中where从句中的列，或者order by，group by,distinct中的字段
• 索引的顺序遵从最左匹配原则；
• 避免建议冗余索引和重复索引；
• 对于频繁的查询优先考虑使用覆盖索引；
• 索引上禁止函数操作或表达式操作，会导致索引失效；
• 对于模糊查询，要去掉前面的%，%_%会导致索引失效；

SQL编写规范：

• 建议使用预编译语句进行数据库操作；【预编译可以减少SQL编译时间，还可以解决动态SQL带来的SQL注入问题】
• 避免数据类型的隐式转换；【隐式转换会导致索引失效】
• 避免使用join关联太多的表；
• 在明显不会有太多重复值时使用union all代替union；【union会对结果集进行去重操作】
• 拆分复杂的大SQL为多个小SQL。

数据库操作行为规范；

• 百万数据级别的批量写操作，要分批操作；
• 对于大表使用pt-online-schema-change修改表结构；【相当于建立一个与原表相同的新表，在新表上进行表结构修改，然后将原表数据复制过来，删除原表】
• 对于连接数据库账号，遵循权限最小原则。

 

一条SQL语句执行很慢的原因？

https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485185&idx=1&sn=66ef08b4ab6af5757792223a83fc0d45&chksm=cea248caf9d5c1dc72ec8a281ec16aa3ec3e8066dbb252e27362438a26c33fbe842b0e0adf47&token=79317275&lang=zh_CN#rd

一条SQL执行很慢，是每次执行都很慢呢？还是偶尔执行很慢？这个问题得分以下两种情况来讨论。

第一，大多数情况下是正常的，只是偶尔出现很慢的情况。

第二，在数据量不变的情况下，这条SQL一直很慢。

针对偶尔很慢的情况：

偶尔很慢情况一般SQL的书写是没什么问题的，那会是什么原因呢？

1.数据库在刷脏页

当我们插入或更新一条数据的时候，数据库会在内存中把对应字段更新了，但是更新之后不会立刻同步持久化到磁盘上去，而是把这些更新的记录写到redo log日志中去，等到空闲的时候，再通过redo log日志将最新记录同步到磁盘上去。

刷脏页有以下四种情形：【后两项不需要太关注】

• redo log写满了：如果更新很频繁的话，redo log很快就被写满了，这时数据库只好暂停其他操作，专心同步数据到磁盘，这时我们的SQL操作就变得很慢了；
• 内存不够了：如果一次查询较多的内容，恰好所查的数据页不在内存中，此时需要申请内存。若内存不足就需要淘汰一部分内存数据页；
• MySQL认为系统空闲的时候；
• MySQL正常关闭的时候。

2.拿不到锁

这个就比较好理解了，我们执行的这条SQL涉及到的表或者涉及到的某一个列别人正在用，并且加锁了，所以就只能慢慢等别人释放锁了。

判断是否真的在等待锁命令：show processlist;

针对一直这么慢的情况：

1.没用到索引：

• 字段上没有索引；【全表扫描】
• 字段有索引，但是没有用索引；【索引上有函数计算，算术计算导致索引失效了】
• 数据库自己选错索引了；

针对数据库自己选错索引的一些补充：

有SQL如下：【c不是主键索引】

select * from t where 100 < c and c < 100000;

我们知道主键索引和非主键索引是有区别的，主键索引存放的值是字段的值，而非主键索引存放的是主键的值。也就是所，如果走c这个字段的索引的话，先会查询主键的值，再根据主键的值走主键索引，查询到整行数据返回。

所以，数据库在执行语句的时候会进行预测，究竟是走c索引扫描的行数少，还是扫描全表的行数少呢？

那么系统是怎样预测的呢？

系统是根据索引的区分度来判读的，也就是说，一个索引上不同的值越多，区分度越高。我们把区分度也叫做基数。

问题这个基数是怎么来的呢？采样！

既然是采样，那就有可能会出现失误。也就是说c这个基数原本是很大的，但是采样的那部分刚好基数很小，即由于统计的失误，导致系统没有走索引，导致了这条SQL执行很慢。

 

MyISAM和InnoDB的主要区别和应用场景？

主要区别：

• 【事物】MyISAM是非事物安全型的，而InnoDB是事物安全型的，默认开启自动提交，宜合并事物，一同提交，减少数据库多次提交导致的开销，大大提高性能；
• 【锁】MyISAM锁的粒度是表级，而InnoDB支持行级锁；
• 【全文索引】MyISAM支持全文类型索引，而InnoDB不支持全文索引；
• 【查询效率】MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM；
• 【外键】MyISAM不支持外键，InnoDB支持；
• 【count】MyISAM保有表的总行数，InnoDB只能遍历；
• MyISAM 表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储 会省去不少麻烦；
• InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事物表到事物表（alter table tablename type=InnoDB）；
• MyISAM索引和数据分离，InnoDB在一起，MyISAM是非聚簇索引，最多有一个unique的性质，InnoDB的数据文件本身就是主索引文件，这样的索引被称为“聚簇索引”；
• InnoDB提供多版本数据支持，MyISAM不支持；
• 两者都仅支持B+Tree树索引，不支持hash索引。

应用场景：

1）MyISAM管理非事物表。它提供高效存储和检索，以及全文搜索能力。如果应用中需要执行大量的select查询，那么MyISAM是更好的选择；

2）InnoDB用于事物处理应用程序，具有众多特性，包括ACID事物支持。如果应用中需要执行大量的Insert或update操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

https://www.cnblogs.com/y-rong/p/8110596.html

 

 

你经常使用什么样的查询模式？

在写多读少的应用中还是InnoDB插入性能更稳定，在并发情况下也是；如果对读取速度要求比较快的应用还是MyISAM。

 

MySQL出现死锁的原因？

MySQL行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条SQL语句操作了主键索引，那么MySQL就会锁定这个主键索引，如果MySQL操作的是非主键索引，那么MySQL会先锁定这个非主键索引，再去锁定主键索引。

在update和delete操作时MySQL不仅会锁定所有where条件扫描过的索引，还会锁定相邻的键值。

死锁举例分析：

表Test：（ID，State，Time）；主键索引：ID；非主键索引：State

当执行"update state = 1001 where state = 1000;"语句的时候会锁定State索引，由于state索引不是主键索引，所以MySQL还会去请求锁定ID索引；

当另一个SQL语句与上一个SQL几乎同时执行的时候，"update state = 1100 where ID = 1;"对于这个SQL，MySQL会先锁定ID索引，由于此SQL还操作了state字段，所以MySQL还会请求锁定state索引。这时，彼此锁定着对方需要的索引，又都在等待在对方释放锁定，所以出现了“死锁”的情况。

 

MySQL查询优化：

https://www.cnblogs.com/wangning528/p/6388538.html

https://ariyue.iteye.com/blog/553541

MySQL的性能优化包罗甚广：索引优化、查询优化、查询缓存、服务器设置优化、操作系统和硬件优化、应用层面优化（web服务器、缓存）等等。这里我们主要聊聊查询语句的优化。

查询的开销指标：执行时间、检查的行数、返回的行数

建立索引需要注意的几点：

• 索引字段上进行运算会使索引失效（函数操作或表达式操作）；
• 避免使用!=或<>或IS NULL、IS NOT NULL、IN、NOT IN等操作符；
• 合理使用exists，not exists子句；
• 能使用between的就不要使用in；
• 能使用distict的就不要使用group by；
• 必要时可以强制查询优化器使用某个索引；
• 避免使用 %_%，左边的%会使索引失效；
• 能使用union all的就不要使用union（union会排序，很耗费资源）；
• 字段数据类型优化（创建表时避免使用null，可以使用not null）。

 

MySQL中EXPLAIN关键字详解？

 

id：本次select的标识符。在查询中每个select都有一个顺序的值。

 

select_type：select的类型。

SIMPLE：简单的select，不使用UNION或者子查询；

PRIMARY：最外层的select；

UNION：第二层，在select之后使用了UNION；

DEPENDENT RESULT：UNION中的第二个select，依赖于外部子查询；

UNION RESULT：UNION的结果；

SUBQUERY：子查询中的第一个select；

DEPENDENT SUNQUERY：子查询中的第一个select，取决于外面的查询；

DERIVED：导出表的select（from子句的子查询）。

 

table

显示这一行的数据是关于哪个表的。

 

Type：表连接类型。

这是最重要的列，显示连接使用了何种类型。

从最好到最差的连接类型：const，eq_ref，ref，range，index，All

Type告诉我们对表使用的访问方式，主要包含如下类型。

all：全表扫描；

const：读常量，最多只会有一条记录匹配，由于是常量，实际上只需读一次；

eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一索引来访问；

fulltext：进行全文索引检索；

index：与all类型相似，不同的是它只扫描索引树；

index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引；

index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行合并（merge），再读取表数据；

range：索引范围扫描；

ref：join语句中被驱动表索引引用的查询；

ref_or_null：与ref的唯一区别就是在使用索引引用的查询之外再添加一个空值的查询；

system：系统表，表中只有一行数据；

unique_subquery：子查询的返回结果字段组合是主键或唯一约束。

 

possible_keys

显示可能应用在这张表中的索引。如果为空，可能没有索引被用到。

 

key

实际使用的索引。如果为null，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在select语句中使用USE_INDEX(indexname)来强制使用索引或者用IGNORE_INDEX(indexname)来强制MySQL忽略索引。

 

key_len

使用的索引长度，在不损失精确性的情况下，长度越短越好。

 

ref

显示了哪些字段或常量被用来和key配合从表中查询记录出来。

 

rows

MySQL认为必须检查的用来返回请求数据的行数。

 

Extra

关于解析MySQL如何解析查询的额外信息。

最坏的例子是Using temporary和Using filesort，意思是MySQL根本不能使用索引，查询结果会很慢。

Using temporary：MySQL的某些操作中使用了临时表；

Using filesort：Query操作中包含order by操作，且无法利用索引完成排序的时候。

 

关于MySQL的分页关键字limit？

MySQL的分页查询十分简单，但是当数据量大的时候一般的分页就吃不消了。

传统的分页查询：select c1,c2,c3...from table limit n,m;

【注意下，limit中的n取值是从0开始的】

MySQL的limit工作原理就是先读取前面的n条记录，然后抛弃前n条，读后面的m条。所以n越大，偏移量越大，性能就越差。

优化方法：利用表的覆盖索引来加速分页方法。

我们知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），查询会很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再根据索引去查询数据了。

如果我们知道id是主键，即默认的主键索引，那么：

select id from table limit 100000,10;

这个SQL查询就会很快。

如果要查询所有的列，有两种方法：

1）id >= 的形式：

select * from table where id >= (select id from table limit 100000,1) limit 10;

2）利用join：

select * from table a join (select id from table limit 100000,1) b on a.id = b.id;

 

两者都是一个原理，所以效果差不多。

 

MySQL中连接字符串函数，复制字符串，截取字符串函数？

连接字符串：concat(str1,str2...);

返回结果为连接参数产生的字符串。如果任意一个参数为null值，则返回值为null。

例子：

concat('m','y','s','q','l'); 返回mysql;

复制字符串：repeat();

repeat('ab',2); ab表示要复制的字符串，2表示复制的份数，即abab;

 

截取字符串：substring_index(str,delim,count);

str：要分割截取的字符串；

delim：分隔符；

count：位置，若count为正数，那么就是从左开始数，函数返回第count个分隔符左侧的字符串；若count为负数，则从右开始数；count如果为0，则返回空。

例子：

substring_index("aaa_bbb_ccc", "_", 1); 返回aaa;

substring_index("aaa_bbb_ccc", "_", 2); 返回aaa_bbb;

substring_index("aaa_bbb_ccc", "_", -1);返回ccc;