Mysql面试题

一、常用mysql知识

（一）查询语句优化

1.避免非操作符的使用，NOT、<>数据库系统不会使用索引。 
2.避免对查询的列的操作，任何对列操作都可能导致全表扫描，包括数据库函数、技术表达式等。尽可能将操作移至等式右边 
3.避免不必要的类型转换 
4.增加查询的范围限制 
5.合理使用IN和EXIST 
6.尽量去掉<> 
7.去掉WHERE字句中的IS NULL和IS NOT NULL，不会使用索引而进行全表搜索。 
8.尽量不要使用前导模糊查询，前导模糊查询不能利用索引。 
9.SELECT字句中避免使用‘*’ 
10.规范所有的SQL关键之的书写，不要大小写混用 



(二) exists与in

   1.区别：in是把外表和内表作为hash连接，而exist是对外表做loop循环，每次loop循环再对内表进行查询。如果两个表大小相当，in或exists都可以，如果一大一小，子查询大表用exists，子查询小表用in 

   2.not in 与not exist:如查询语句使用了not in那么内外表都进行全表扫描，没用到索引，而not exists子查询依然能用到表上索引，所以无论哪个表大，用not exists都比not in要快。



(三) 事物隔离级别



1) 幻读

   事物1读取一条指定where数据之后，事物2插入一条符合where条件的数据，事物1使用相同条件再次查询，结果集就可以看到事物2插入的数据，这条数据是幻读数据



2) 不可重复读

   事物1读取一条记录，事物2修改1刚刚读取的，事物1再次查询，发现与第一次读取记录不同



3) 脏读

   事物1更新一条记录还没提交，事物2读取更新后的数据，然后事物1回滚，事物2读取的行无效，也就是脏数据。



4) 事务隔离级别

   READ UNCOMMITTED 幻读、不可重复读和脏读都允许 

   READ COMMITTED 允许幻读、不可重复读，脏读不允许 

   REPEATABLE READ允许幻读，不允许不可重复读和脏读 

   SERIALIZABLE 幻读、不可重复读和脏读都不允许。 

   SQL标准所定义的默认事物隔离级别是SERIALIZABLE，但oracle默认使用READ COMMITTED



(四)索引优缺点



优点

   创建唯一索引，可以保证数据库表中每一行数据唯一性 

   加快数据检索速度 

   加速表与表之间连接 

   减少查询中分组和排序的时间 

   在查询过程中使用优化隐藏器，提高系统性能



缺点

   创建索引和维护索引需要耗费时间 

   索引需要占物流空间 

   表中数据增删改时需要动态维护 

   不合适的创建索引不会提高性能 

 

   主键也是一个特殊的所有。数据表中只允许一个主键，但是可以有多个索引。



(五)Mysql优化

   在MySQL中可以使用EXPLAIN查看SQL执行计划 





1、参数说明

select_type

   1.SIMPLE， 表示简单查询，其中不包含连接查询和子查询。 

   2.PRIMARY，表示主查询，或者是最外面的查询语句。 

   3.UNION 表示连接查询的第2个或后面的查询语句。 

   4.DEPENDENT UNION UNION中的第二个或后面的SELECT语句，取决于外面的查询。 

   5.UNION RESULT 连接查询的结果。 

   6.SUBQUERY 子查询中的第1个SELECT语句。 

   7.DEPENDENT SUBQUERY 子查询中的第1个SELECT语句，取决于外面的查询。 

   8.DERIVED SELECT(FROM 子句的子查询)。



table

   表示查询的表



type

   以下参数是最佳类型到最差类型 

   1.system 表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计。 

   2.const 数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于PRIMARY KEY或者UNIQUE索引的查询，可理解为const是最优化的 

   3.eq_ref 对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY”。eq_ref可以用于使用=比较带索引的列 

   4.ref 查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。 

   5.ref_or_null   该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。



以上5种情况都是很理想的索引使用情况

   6.index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素 

   7.unique_subquery 是一个索引查找函数,可以完全替换子查询,效率更高 

   8.index_subquery 可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引value IN (SELECT key_column FROM single_table WHERE some_expr) 

   9.range 只检索给定范围的行,使用一个索引来选择行。 

   10.index 该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。 

   11.ALL 对于每个来自于先前的表的行组合,进行完整的表扫描



possible_keys

   指出MySQL能使用哪个索引在该表中找到行。如果该列为NULL，说明没有使用索引，可以对该列创建索引来提高性能。



key

   显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL 

   可以强制使用索引或者忽略索引：IGNORE INDEX(age).USE INDEX(age)



key_len

   显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。 

注意：key_len是确定了MySQL将实际使用的索引长度。



ref

   显示使用哪个列或常数与key一起从表中选择行 





rows

   显示MySQL认为它执行查询时必须检查的行数。



Extra

   该列包含MySQL解决查询的详细信息



2、使用索引查询需要注意

使用LIKE关键字的查询

   1.在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引才会生效



使用联合索引的查询

   MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。



使用OR关键字的查询

   查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效



子查询优化

   执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。



3、数据库结构优化

将字段很多的表分解成多个表

   对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。 

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。



增加中间表

   对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。



增加冗余字段

   设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。 

   注意：冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题



4、插入数据的优化

   插入数据时，影响插入速度的主要是索引、唯一性校验、一次插入的数据条数等 

   MySQL中常用的存储引擎有，MyISAM和InnoDB两者的区别： 

   MyISAM 是非事务安全型的，InnoDB事务安全型的。 

   MyISAM锁粒度是表级 ，InnoDB支持行级。 

   MyISAM支持全文类型索引 ，InnoDB不支持全文索引。 

   MyISAM 相对简单，效率上优于InnoDB，小型系统考虑使用 

   MyISAM表是报错成文件的形式 ，跨平台的数据转移中使用会省去不少麻烦 

   MyISAM ，InnoDB更加安全，保证数据不会丢失的情况下，切换非事务表到事物表。 

   MyISAM管理非事务，如果应用中需要执行大量SELECT查询，那么更好的选择是MYISAM ，InnoDB用于事物处理应用程序，具有众多性，包括ACID，如应用中需要执行大量的INSERT或UPDATE操作，则应使用INNoDB

MyISAM

   禁用索引，禁用唯一性检查，批量插入数据。当需要批量导入数据时，使用LOAD DATA INFILE语句比INSERT语句插入速度快很多



InnoDB

   禁用唯一性检查，禁用外键检查，禁止自动提交



(七) 服务器优化



1、优化服务器硬件

   主要通过使用高性能硬件设备提高效率

2、优化MySQL的参数

   MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中 

   key_buffer_size:表示索引缓冲区的大小 

   table_cache:表示同时打开的表的个数 

   query_cache_size:表示查询缓冲区的大小 

   sort_buffer_size 表示排序缓存区的大小 

   read_buffer_size:表示每个线程连续扫描时为扫描每个表分配的缓冲区的大小 

   read_rnd_buffer_size:表示为每个线程保留的缓冲区的大小 

   innodb_buffer_pool_size:表示InnoDB类型的表和索引的最大缓存 

   max_connections:表示数据库的最大连接数 

   innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件 

   back_log:表示在mysql暂时停止回答新请求之前的短时间内，多少个请求可以放在堆栈中。 

   interactive_timeout:表示服务器在关闭连接前等待行动的秒数。 

   sort_buffer_size: 表示每个需要进行排序的线程分配的缓冲区的大小。 

   thread_cache_size 表示可以复用的线程的数量 

   wait_timeout 表示服务器在关闭一个连接时等待行动的秒数。默认数值时28800