interview-db[通俗易懂]

大家好，又见面了，我是你们的朋友全栈君。1.索引作用和优缺点

索引就一种特殊的查询表，数据库的搜索可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

2.什么字段适合建索引

唯一性比较高,不为空,经常查询

3.常见慢查询优化

1.建索引 2.减少表之间的关联 3.优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引,把数据 量大的表排在前面 4.简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据 5.尽量用PreparedStatement来查询，不要用Statement

4.truncate,delete 和 drop

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。 DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。

TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。 

TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

TRUNCATE,DELETE,DROP 放在一起比较：

TRUNCATE TABLE ：删除内容、释放空间但不删除定义。

DELETE TABLE: 删除内容不删除定义，不释放空间。

DROP TABLE ：删除内容和定义，释放空间。

5.列举几种表连接方式,有什么区别

左连接、右连接、全连接、 内连接：只有两个元素表相匹配的才能在结果集中显示。 左外连接:左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。 右外连接:右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。 全外连接：连接的表中不匹配的数据全部会显示出来。 交叉连接： 笛卡尔效应，显示的结果是链接表数的乘积。

6.索引最左原则

SHOW INDEX FROM employees.titles;

+——–+————+———-+————–+————-+———–+————-+——+————+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |

+——–+————+———-+————–+————-+———–+————-+——+————+

| titles | 0 | PRIMARY | 1 | emp_no | A | NULL | | BTREE |

| titles | 0 | PRIMARY | 2 | title | A | NULL | | BTREE |

| titles | 0 | PRIMARY | 3 | from_date | A | 443308 | | BTREE |

| titles | 1 | emp_no | 1 | emp_no | A | 443308 | | BTREE |

+——–+————+———-+————–+————-+———–+————-+——+————+

6.1,索引是否有问题?

从结果中可以到titles表的主索引为<emp_no, title, from_date>，还有一个辅助索引<emp_no>。为了避免多个索引使事情变复杂（MySQL的SQL优化器在多索引时行为比较复杂），这里我们将辅助索引drop掉：

6.2,全列匹配

全列匹配(此时若没有按照索引顺序时，mysql查询优化器会自动的调整顺序来使用定义好的索引)

6.3,最左前缀匹配

SELECT * FROM employees.titles WHERE emp_no=’10001′;

SELECT * FROM employees.titles WHERE emp_no=’10001′ AND from_date=’1986-06-26′;

上面的sql是否用到了索引.

6.4 上面6.3中的第二个sql可以优化一下吗?

优化方法可以穷举索引第二列,如果第二列数据有限;或者增加辅助索引<emp_no, from_date>.

SELECT * FROM employees.titles

WHERE emp_no=’10001′

AND title IN (‘Senior Engineer’, ‘Staff’, ‘Engineer’, ‘Senior Staff’, ‘Assistant Engineer’, ‘Technique Leader’, ‘Manager’)

AND from_date=’1986-06-26′;

6.5 模糊匹配与索引

SELECT * FROM employees.titles WHERE emp_no=’10001′ AND title LIKE ‘Senior%’;

6.6 范围查询

SELECT * FROM employees.titles WHERE emp_no < ‘10010’ and title=’Senior Engineer’;

(范围查询后面的列将无法使用索引)

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

6.7 查询条件中含有函数或表达式

很不幸，如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引（虽然某些在数学意义上可以使用）。例如

SELECT * FROM employees.titles WHERE emp_no=’10001′ AND left(title, 6)=’Senior’;

SELECT * FROM employees.titles WHERE emp_no – 1=’10000′;

7. exist 和 in

select * from A where id in(select id from B)

A表比B表大的情况,效率较好;如果a表的id有索引,会被利用到.

select a.* from A a where exists(select 1 from B b where a.id=b.id)

exists()适合B表比A表数据大的情况,如果b.id有索引会被利用到.

当A表数据与B表数据一样大时,in与exists效率差不多,可结合索引情况使用.

in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。

in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

8.mysql数据库引擎MyISAM和InnoDB的区别

8.1、存储结构 
每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 
.frm文件存储表定义。 
数据文件的扩展名为.MYD (MYData)。 
索引文件的扩展名是.MYI (MYIndex)。

8.2、存储空间 
MyISAM：可被压缩，存储空间较小。 
InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 
MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小

8.3、事务处理 
MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不支持外键、不提供事务支持。 
InnoDB提供事务支持事务，外部键（foreign key）等高级数据库功能。

SELECT、UPDATE、INSERT、Delete操作 
如果执行大量的SELECT，MyISAM是更好的选择。 
如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。 
DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。而MyISAM则是重新建立表。在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

AUTO_INCREMENT 
MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。 
InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

8.4、表的具体行数 
MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出该值。 
InnoDB：没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了where后，myisam和innodb处理的方式都一样。

8.5、全文索引 
MyISAM：支持 FULLTEXT类型的全文索引。不支持中文。 
InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

8.6、表锁差异 
MyISAM：只支持表级锁，只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁。 
InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表， 例如update table set num=1 where name like “%aaa%”

一般来说： 
MyISAM适合： 
(1)做很多count 的计算； 
(2)插入不频繁，查询非常频繁； 
(3)没有事务。 
InnoDB适合： 
(1)可靠性要求比较高，或者要求事务； 
(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

　两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁。而MyISAM不支持.所以MyISAM往往就容易被人认为只适合在小项目中使用。
　　作为使用MySQL的用户角度出发，Innodb和MyISAM都是比较喜欢的，如果数据库平台要达到需求：99.9%的稳定性，方便的扩展性和高可用性来说的话，MyISAM绝对是首选。
　　原因如下：
　　1、平台上承载的大部分项目是读多写少的项目，而MyISAM的读性能是比Innodb强不少的。
　　2、MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
　　3、经常隔1，2个月就会发生应用开发人员不小心update一个表where写的范围不对，导致这个表没法正常用了，这个时候MyISAM的优越性就体现出来了，随便从当天拷贝的压缩包取出对应表的文件，随便放到一个数据库目录下，然后dump成sql再导回到主库，并把对应的binlog补上。如果是Innodb，恐怕不可能有这么快速度，别和我说让Innodb定期用导出xxx.sql机制备份，因为最小的一个数据库实例的数据量基本都是几十G大小。
　　4、从接触的应用逻辑来说，select count(*) 和order by 是最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的都会锁全表的。
　　5、还有就是经常有很多应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的frm.MYD,MYI的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。
　　6、如果和MyISAM比insert写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，虽然MyISAM可能会逊色Innodb,但是那么高并发的写，从库能否追的上也是一个问题，还不如通过多实例分库分表架构来解决。
　　7、如果是用MyISAM的话，merge引擎可以大大加快应用部门的开发速度，他们只要对这个merge表做一些select count(*)操作，非常适合大项目总量约几亿的rows某一类型(如日志，调查统计)的业务表。
　　当然Innodb也不是绝对不用，用事务的项目就用Innodb的。另外，可能有人会说你MyISAM无法抗太多写操作，但是可以通过架构来弥补。