mysql之面试问题总结

问题1、char 与varchar的区别？

       varchar是变长而char的长度是固定的。如果你的内容是固定的大小，char性能更好。

      char[4] 与varchar[4]   存储字母a a占一个字符  varchar长度为4但是占此时为一个字符长度   而char则占4个字符

问题2、truncate 和delete 区别是什么？

     delete 命令是从表中删除一行或者多行，truncate是从表中永远的删除每一行，truncate是将整个表删除后重新创建当前表结构

问题3、什么是触发器、mysql中都有哪些触发器。

    触发器是一行代码  ，当触发了某个事件的时候自动触发的这一段代码。

    mysql中有6中触发器

    1）before insert

    2) after insert

    3) before update

    4) after update

    5)before delete

     6)after delete

问题4、float 和double区别

   float可以存储至多8位十进制数，并且内存占4字节

    double 至多可以18位十进制数，并且内存占8字节

问题5、mysql获取当前日期的函数

       select current_date();

问题6、now() 与sysdate()区别

     select now() 执行语句前就获取时间    select sysdate() 在执行过程中获取时间

问题7、如何查找第n高的工资

    select distin(salary) from employee order by salary desc limit n-1,1

问题8、请写出下面mysql数据类型表达的意义int（0） char(16) varchar(16) datetime text

     int表示int类型长度为0  char(16) 表示固定长度字符串  长度为16 varchar(16)表示可变长度字符串，长度为16  datetime表示时间类型   text表示字符串类型 能存储大字符串，最多存65535字节数据

   1）   整数类型，包括tinyint  smallint mediumint int bigint 分别1字节  2字节  3字节  4字节  8字节整数。任何整数类型都可以加上unsigned属性，表示数据类型无符号，也就是非负数

    长度：整数类型可以被指定长度，比如int(11) 表示长度为11的int 类型。

   2） 实数类型  包括double  float decimal 

        decimal 可以用来存储比bigint还打的整型，能存储精确的小数

        float 和double 是由取值范围的，并支持使用标准的浮点进行近似计算，计算时float 和double 相比decimal效率更高一些，decimal可以理解为用字符串进行处理

    3）字符串类型 包括 varchar char text blob 

          varchar 用于存储长度可变的字符串，它比定长的数据类型更节省空间

          varchar使用额外1或2个字节存储字符创长度，varchar 存储长度超过设置长度后会被截断

          char 是定长的需要使用空格惊醒填充，适合存储短字符串，或者所有值都近似一个长度

          char 存储内容超过设置长度，内容同样会被截断

        使用策略：

         对于经常变更的数据来说，char比varchar更好，因为char不容易产生碎片。对于非常短的列char比varchar存储空间上更有效率。

         使用的时候注意分配需要的空间，更长的类排序的时候会消耗更多内存。

         尽量避免使用text blob类型，查询时会使用临时表，导致严重的性能开销。

     4)枚举类型，不能重复的数据存储一个预定义的集合。

         有时候可以使用enum代替常用的字符串类型

         enum存储非常紧凑，会把列表值严肃哦到一个或两个字节

       5）日期和时间类型  尽量使用timestamp 空间效力高于datetime  用整数保存时间戳通常不方便处理 如果需要存储微秒，可以使用bigint存储

   常见操作

   mysql 常见的连接和关闭;mysql -u -p -h -P

     -u 用户名

    -p 密码

    -h  主机

    -P 端口

   进入mysql命令后   G   c q  s h d

   G 打印结果垂直显示

   c 取消当前mysql命令

   q 退出mysql连接

    s 显示服务器状态

    h 帮助信息

    d 改变执行符

mysql 存储引擎

   1 、 innoDB存储引擎

     1） 默认的存储引擎，最重要最广泛的存储引擎，性能非常优秀

     2）数据存储在共享空间，可以通过配置分开，也就是多个表和索引都存储在一个表空间中，可以通过配置文件改变此配置。

    3）对主键查询性能高于其他存储引擎

    4）内部做了很多优化，从磁盘读取数据时会自动构建hash索引，插入数据时自动构建插入缓冲区

    5）通过一些机制和工具支持真正的热备份

     6）支持崩溃后的安全恢复

     7）支持行级锁

     8）支持外键

    2 MyISAM 存储引擎

• 拥有全文索引  压缩 空间函数
• 不支持事务和行级锁、不支持崩溃后的安全恢复
• 表存储在两个文件  myd 和myl
• 设计简单，获取整个表多少条数据性能很高
• 全文索引不是很常用  
•  不如使用elasticsearch或luncene

    3 其他引擎

      Archive  Blackhole csv  memory

      在大多数场景下建议使用innodb引擎

MySQL锁机制

     共享锁和排他锁 就是读锁和写锁

     共享锁，不阻塞，多用户可以同时读一个资源，互不干扰

    排他锁，一个写锁会阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源

锁的粒度

    表锁，系统开销小，会锁定整张表 myisam使用表锁

    行锁，最大程度支持并发处理，但是带来最大锁开销，innodb使用行锁

MySql 事务处理

   mysql 提供事务处理表引擎，也就是innodb

   服务器层不管理事务，由下层的引擎实现，所以同一个事务中，使用多种引擎是不靠谱的

   需要注意，在非事务表上执行事务操作，mysql不会发出提示，也不会报错。

存储过程

 为以后使用保存的一条或多条mysql语句的集合  因为可以在存储过程加上业务逻辑和流程

  可以在存储过程中创建表，更新数据  删除数据等

  使用策略：

    可以通过把sql语句封装在容易使用的单元中，简化复杂的操作

    可以保证数据的一致性

    可以简化对变动的管理

触发器

      提供给程序管理员和数据分析员来保证数据完整性一种方法，它是与表相关的特殊存储过程

使用场景：

•     可以通过数据库中的相关表实现级联更改
•     实时监控某正表中的某个字段的更改而需要作出相应处理
•     例如可以生成某些业务编号
•     注意不要滥用，否则会造成数据库及应用程序维护困难

问题 9：说明innodb 和MyISAM的区别

     innodb 支持事务  myisam不支持事务

     innodb 数据存储在共享表空间，myisam存储在文件中

     innodb 支持行级锁   myisam支持表锁

     innodb 支持崩溃后的恢复， myisam不支持

     innodb 支持外键   myisam不支持

     innodb 不支持全文索引，myisam支持全文索引

问题 10 innodb引擎特性

    插入缓存

    二次写

   自适应哈希索引

   预读

 问题11 请列举3个以上表引擎

   memory  myisam  innodb

 问题 12  请说明varchar 和text 的区别

   varchar 可以指定字符数  text 不能指定

   内部存储varchar是存入实际字符数+1个字节（n<=255）或2个字节（n>255），text是实际字符数+2个字节

  text 类型不能有默认值。

  varchar 可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快与text，在创建索引的情况下，text索引几乎不起作用。

   查询text需要创建临时表

问题12  varchar(50) 中50含义

  最多存放50个字符 varchar（50）和（200） 存储hello所占用空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length 计算col长度（memory引擎也一样）

问题 13 int(20) 中20的含义

  指显示字符的长度，不影响内部存储，只是定义了zerofill时，前面补多少个0

问题14 简述mysql中索引， 主键，唯一索引，联合索引的区别，对数据库的性能有什么影响？

   考察知识：

    mysql 索引的创建原则

    mysql索引的注意事项

    mysql索引的原理

索引基础

   索引类似于数据目录，要想找到一本书的某个特定主题，需要先查找书的目录，定位对应页码

  存储引擎使用类似的方式进行数据查询，先去索引当中找到对应的指，然后根据匹配的索引找到对应的数据行。

索引的类型

    普通索引：最基本的索引，没有任何约束限制

    唯一索引：和普通索引类似，具有唯一性

    主键索引：特殊的唯一索引，不允许有空值

 索引的区别

   一个表只能有一个主键索引，但是可以有多个唯一索引

•   主键索引一定是唯一索引，唯一索引不一定是主键索引
•  主键可以与外键构成参照完整性约束，防止数据不一致
• 联合索引：将多个列组合在一起创建索引，可以覆盖多个列
• 外键索引：只有innodb类型的表才可以使用外键索引，保证数据的一致性、完整性、和实现级联操作
• 全文索引：mysql自带全文索引只能用于myisam，并且只能对英文进行全文检索

  mysql索引创建原则

     最适合创建索引的列出现在where或on子句中的列，或链接子句中的列而不是出现在select 关键字后面的列

     索引类的基数越大，应该制定一个前缀长度，可以节省大量的索引空间

    根据情况创建联合索引，联合索引可以提高查询效率

    避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率

    主键尽可能选择较短的数据类型，可以有效的减少磁盘占用提高查询效率

   。。。

  mysql 索引原理

   mysql索引是用一种叫聚簇索引的数据结构实现的

   聚簇索引是一种数据存储方式，他实际上是在同一个结构中保存了B+树索引和数据行，InnoDB表是按照聚簇索引组织的

   注：树是一种数据结构，是一个n叉排序树，每个节点通常有多个孩子，一颗B+树包含根节点、内部节点和叶子节点。根节点可能是叶子节点，也可能是一个包含两个或两个以上

 孩子节点的节点。

。。。。

问题15：创建mysql联合索引应该注意什么？

      遵循前缀原则

问题16 列值NULL时，查询是否会用到索引

 在mysql里 null值得列也是走索引的，当然，如果计划多列进行索引，就尽量避免把它设置为null，mysql难以优化引用了可空列的查询，它会使索引，索引统计和值更加复杂。

问题 17 myisam 索引和innodb索引的区别

   innodb索引是聚簇索引，myisam索引是非聚簇索引

  innodb主键索引的叶子节点存储着行数据，因为主键索引非常高效

  myisam 索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据

 innodb 非主键索引的叶子节点存储的主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效

问题 18 以下三条sql如何创建索引，只建一条怎么建

    where a =1 and b=1

   where b=1

   where b=1 order by time desc

 以顺序b,a,time建立联合索引，create index table_b_a_time on index_test(b,a,time). 因为最新mysql版本会优化where子句后的列顺序，以匹配联合索引顺序

问题 19 有A(id，sex，par，c1，c2) B(id,age,c1,c2) 两张表，其中A.id 与B.id 关联  现在要求写出一条sql语句，将B中age>50的记录的c1 c2 更新到A表中同一记录中的c1 c2字段

   mysql关联查询语句

   六种关联查询

   交叉查询   cross join 

   内连接 inner join

    外连接 left join /right join

    联合查询 union 与union all

    全连接  full join

   。。。

问题 20  为了记录足球比赛的结果，设计表如下team 参赛队伍表    match 赛程表  其中，match赛程表中的hostTeamID与guestTeamId都和team表的teamId关联，查询2006-6-1到2006-7-1之间举行的所有比赛，并且用一下形式列出：迪拜 2:0 不莱梅 2018-6-21

。。。。

问题 21 union 与union all 的区别

  如果使用union all 不会合并重复的记录行

 效率union高于union all

问题 22 一个6亿的表a 一个3亿的表b 通过外键tid关联，你如何最快的查询出满足条件的50000到50200中的这200条数据记录

  1）如果a表tid是自增长  并且是连续的  B 表的id为索引

     select * from A ,B where a.tid=b.id and a.tid>50000 limit 200

  2)如果 A表的TID不是连续的，那么就需要使用覆盖索引，tID要么是主键，要么是辅助索引，B表id也需要有索引。

   select * from b,(select tid from a limit 50000,200 ) a where b.id =a.tid

问题 23 拷贝表（拷贝数据，源表名：a 目标表名：b）

insert into b（a,b,c） select d,e,f from a

问题 24：student(s#,sname,Sage,Ssex) 学生表     curse(C#,Cname，T#) 课程表     sc(S#,C#,score) 成绩表    Teacher（T#,Tname）教师表  查询没有学过叶平老师课的学生学号？

  select student.s# from student where s# not in (select distinct(sc.S#) from sc ,curse,teacher where sc.C#=curse.c# and curse.T#=teacher.T# and teacher.Tname= '叶平' )

     

  问题25 随机取出10条数据

  select * from users order by rand() limit 10

此效率高于上面的效率

  select * from users where id>=((select max(id) from users)-(select min(id) from users))*rand()+(select min(id) from users) limit 10

问题 26 请简述项目优化sql语句执行效率的方法，从哪些方面sql语句性能如何分析？

    延伸考点：

    优化查询过程中的数据访问

    优化长难得查询语句

   优化特定类型的查询语句

如何查找查询速度慢的原因

   记录慢查询日志，分析查询日志，不要直接打开慢查询日志，可以使用pt-query-digest工具进行分析

   使用show profile

    set profiling =1 ;开启，服务器上所有执行语句会记录执行时间，存到临时表中

    show profiles

    show profile for query 临时表 id

   使用show status

   show status 会返回一些计数器，show global status 会查看所有服务器级别的所有计数，有时根据这几计数，可以推测出哪些操作代价较高或者消耗时间多

   show processlist

    观察是否有大量线程处于不正常的状态或特征

使用explain

  分析单条sql语句

  优化查询过程中的数据访问

•     访问数据太多导致查询性能下降
•    确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
•    确认mysql服务器是否在分析大量不必要的数据行
•   避免犯如下sql语句错误
•   查询不需要的数据。解决办法：使用limit解决
•   多表关联返回全部列。解决办法：指定列名
• 总是返回全部列。解决办法：避免使用SELECT *
• 重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
• 是否在扫描额外的记录。解决办法：
• 使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化：
• 使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。
• 改变数据库和表的结构，修改数据表范式
• 重写SQL语句，让优化器可以以更优的方式执行查询。

优化长难的查询语句

• 一个复杂查询还是多个简单查询

• MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多

• 使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。

• 切分查询

• 将一个大的查询分为多个小的相同的查询

• 一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销。

• 分解关联查询，让缓存的效率更高。

• 执行单个查询可以减少锁的竞争。

• 在应用层做关联更容易对数据库进行拆分。

• 查询效率会有大幅提升。

• 较少冗余记录的查询。

优化特定类型的查询语句

• count(*)会忽略所有的列，直接统计所有列数，不要使用count(列名)

• MyISAM中，没有任何where条件的count(*)非常快。

• 当有where条件时，MyISAM的count统计不一定比其它引擎快。

• 可以使用explain查询近似值，用近似值替代count(*)

• 增加汇总表

• 使用缓存

优化关联查询

• 确定ON或者USING子句中是否有索引。

• 确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。

优化子查询

• 用关联查询替代

• 优化GROUP BY和DISTINCT

• 这两种查询据可以使用索引来优化，是最有效的优化方法

• 关联查询中，使用标识列分组的效率更高

• 如果不需要ORDER BY，进行GROUP BY时加ORDER BY NULL，MySQL不会再进行文件排序。

• WITH ROLLUP超级聚合，可以挪到应用程序处理

优化LIMIT分页

• LIMIT偏移量大的时候，查询效率较低

• 可以记录上次查询的最大ID，下次查询时直接根据该ID来查询

优化UNION查询

• UNION ALL的效率高于UNION

优化WHERE子句

解题方法

对于此类考题，先说明如何定位低效SQL语句，然后根据SQL语句可能低效的原因做排查，先从索引着手，如果索引没有问题，考虑以上几个方面，数据访问的问题，长难查询句的问题还是一些特定类型优化的问题，逐一回答。

SQL语句优化的一些方法？

• 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

• 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=

• 3.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

• 4.应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20

• 5.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

• 6.下面的查询也将导致全表扫描：select id from t where name like ‘%李%’若要提高效率，可以考虑全文检索。

• 7. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num

• 8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100应改为:select id from t where num=100*2

• 9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’ ，name以abc开头的id应改为:
select id from t where name like ‘abc%’

• 10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

   

优化长难的查询语句

• 一个复杂查询还是多个简单查询

• MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多

• 使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。

• 切分查询

• 将一个大的查询分为多个小的相同的查询

• 一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销。

• 分解关联查询，让缓存的效率更高。

• 执行单个查询可以减少锁的竞争。

• 在应用层做关联更容易对数据库进行拆分。

• 查询效率会有大幅提升。

• 较少冗余记录的查询。

优化特定类型的查询语句

• count(*)会忽略所有的列，直接统计所有列数，不要使用count(列名)

• MyISAM中，没有任何where条件的count(*)非常快。

• 当有where条件时，MyISAM的count统计不一定比其它引擎快。

• 可以使用explain查询近似值，用近似值替代count(*)

• 增加汇总表

• 使用缓存

优化关联查询

• 确定ON或者USING子句中是否有索引。

• 确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。

优化子查询

• 用关联查询替代

• 优化GROUP BY和DISTINCT

• 这两种查询据可以使用索引来优化，是最有效的优化方法

• 关联查询中，使用标识列分组的效率更高

• 如果不需要ORDER BY，进行GROUP BY时加ORDER BY NULL，MySQL不会再进行文件排序。

• WITH ROLLUP超级聚合，可以挪到应用程序处理

优化LIMIT分页

• LIMIT偏移量大的时候，查询效率较低

• 可以记录上次查询的最大ID，下次查询时直接根据该ID来查询

优化UNION查询

• UNION ALL的效率高于UNION

优化WHERE子句

解题方法

对于此类考题，先说明如何定位低效SQL语句，然后根据SQL语句可能低效的原因做排查，先从索引着手，如果索引没有问题，考虑以上几个方面，数据访问的问题，长难查询句的问题还是一些特定类型优化的问题，逐一回答。

SQL语句优化的一些方法？

• 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

• 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=

• 3.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

• 4.应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20

• 5.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

• 6.下面的查询也将导致全表扫描：select id from t where name like ‘%李%’若要提高效率，可以考虑全文检索。

• 7. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num

• 8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100应改为:select id from t where num=100*2

• 9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’ ，name以abc开头的id应改为:
select id from t where name like ‘abc%’

• 10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

•