mysql面试-01

1、为什么索引可以加速查询？

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据

二、索引的优缺点

优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

三、索引的分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；
2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；
3、普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；
4、全文索引：用大文本对象的列构建的索引（下一部分会讲解）

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；
5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；
*遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。

*在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；
表示使用col1的前4个字符和col2的前3个字符作为索引

四、索引的使用策略
 

什么时候要使用索引？

• 主键自动建立唯一索引；
• 经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
• 作为排序的列要建立索引；
• 查询中与其他表关联的字段，外键关系建立索引
• 高并发条件下倾向组合索引；
• 用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

什么时候不要使用索引？

• 经常增删改的列不要建立索引；
• 有大量重复的列不建立索引；
• 表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

索引失效的情况：

• 在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
• 在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
• LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
• 在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
• 在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。（经erwkjrfhjwkdb同学提醒，不等于，包括&lt;符号、>符号和！，如果占总记录的比例很小的话，也不会失效）
• 在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
• 字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致失败，应该改为WHERE email='99999'。
• 在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
• 如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。
• 尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；

五、InnoDB与MyISAM的区别?

MyISAM与InnoDB两者之间区别与选择，详细总结，性能对比

MyISAM与InnoDB两者之间区别与选择，详细总结，性能对比 - 杨少侠-ing - 博客园

http://blog.csdn.net/wjtlht928/article/details/46641865

mysql5.8和5.7的区别

	innoDB	MyISAM
5.7	底层.frm和.idb
5.8	只有.idb 结构也集成到这个文件里面了

 

 

 

六、Limit原理

Mysql 分页语句 Limit原理_helloxiaozhe的博客-CSDN博客_mysql分页原理

6.1、Limit的效率高？

　　常说的Limit的执行效率高，是对于一种特定条件下来说的：即数据库的数量很大，但是只需要查询一部分数据的情况。
　　高效率的原理是：避免全表扫描，提高查询效率。

　　比如：每个用户的email是唯一的，如果用户使用email作为用户名登陆的话，就需要查询出email对应的一条记录。
　　SELECT * FROM t_user WHERE email=?;
　　上面的语句实现了查询email对应的一条用户信息，但是由于email这一列没有加索引，会导致全表扫描，效率会很低。
　　SELECT * FROM t_user WHERE email=? LIMIT 1;
　　加上LIMIT 1，只要找到了对应的一条记录，就不会继续向下扫描了，效率会大大提高。

6.2、Limit的效率低？

　　在一种情况下，使用limit效率低，那就是：只使用limit来查询语句，并且偏移量特别大的情况

　　做以下实验：
      语句1：
         　　select * from table limit 150000,1000;
　　语句2:
         　　select * from table while id>=150000 limit 1000;
　　语句1为0.2077秒；语句2为0.0063秒
　　两条语句的时间比是：语句1/语句2＝32.968
  
　　比较以上的数据时，我们可以发现采用where...limit....性能基本稳定，受偏移量和行数的影响不大，而单纯采用limit的话，受偏移量的影响很大，当偏移量大到一定值后性能开始大幅下降。不过在数据量不大的情况下，两者的区别不大。

　　所以应当先使用where等查询语句，配合limit使用，效率才高

　　ps：在sql语句中，limit关键字是最后才用到的。以下条件的出现顺序一般是：where->group by->having-order by->limit

例子：      

  方案一1)、
        select * from cyclopedia where ID >= (select max(ID) from ( select ID from cyclopedia order by ID limit 90001 ) as tmp) limit 100;
        方案二2)、
       select * from cyclopedia where ID>=(select max(ID) from (select ID from cyclopedia order by ID limit 90000,1) as tmp) limit 100;

       结论一：
        同样是取90000条后100条记录,第1句快还是第2句快?
        第1句是先取了前90001条记录,取其中最大一个ID值作为起始标识,然后利用它可以快速定位下100条记录
        第2句择是仅仅取90000条记录后1条,然后取ID值作起始标识定位下100条记录
        第1句执行结果.100 rows in set (0.23) sec
        第2句执行结果.100 rows in set (0.19) sec
        很明显第2句胜出.看来limit好像并不完全像我之前想象的那样做全表扫描返回limit offset+length条记录,这样看来limit比起MS-SQL的Top性能还是要提高不少的.