mysql数据库：索引详解

面试题：什么是索引？
答：索引是帮助mysql高效获取数据的数据结构；可以得到索引的本质：索引是数据结构；可以理解为“排好序的快速查找数据结构”，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构一般为B树（多路搜索树）；索引会影响where和order by后面的排序结果。
（1）索引的优势和劣势
优势：提高数据检索的效率，降低数据库的IO成本，降了数据排序的成本，降低了CPU的消耗；
劣势：索引实际上也是一张表，会占用内存空间；提高查找速度的同时会降低表的更新速度；如果表的数据量巨大，索引建立会比较复杂。
（2）索引分类：
<1>单值索引：一个索引只包含单个列，一个表可以与多个单值索引
<2>唯一索引：索引列的值必须唯一，但允许有空值
<3>复合索引（用的多）：即一个索引包含了多个列
<4>基本语法：

添加索引语法：

（3）哪些情况适合建立索引：主键自动建立唯一索引，频繁作为查询条件的字段。
（4）哪些情况不适合建立索引：表记录太少（几万条数据），经常增删改的表，数据重复且分布平均的表字段。
（5）mysql的优化方案：
使用Explain：Explain+SQL语句，执行计划包含的信息，输出以下表头

例子：

<1>id：id相同时，执行顺序由上到下；
如果是子查询，ID的序号会增加，ID值越大，优先级越高，越先被执行（可以理解为最内层括号最先执行，依次往外层走）；

如果即有id相同，又有id不同，id值大的优先级一定高；derived2表示id为2的表的衍生虚表；

<2>select-type：查询的类型主要用于区别普通查询，联合查询，子查询等复杂查询。

simple：简单的select查询，查询中不包含子查询或union；
primary：查询中若包含任何复杂的子查询，最外层查询被标记为primary；
subquery：在select或where中包含的子查询；
derived：在from列表中的子查询会被标记为derived（衍生），结果放在临时表中；
union：若第二个select表出现在union之后，则会被标记为union；
union result：从union表获取结果的select；
<3>table：显示这一行数据是哪张表的；
<4>type：显示查询使用了何种类型；

从最好到最差依次是：
system>const>eq_ref>ref>range>index>ALL
（如果显示为ALL，且数据量百万以上，请一定优化）
一般来说，得保证查询至少达到range级别，最好能达到ref；
<5>possible_keys：显示可能被引用在这种表上的索引，一个或多个，只是列出，但不一定实际使用；
<6>key：显示实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引只出现在key列表中；
<7>key_len：所有中使用的字节数，查询索引的长度，在不损失精确性的前提下，越短越好；
<8>ref：显示索引的哪一列被使用了，如果可能的话，是一个常量；
<9>rows：显示找到所查目标记录所需读取的行数，越少越好；
<10>Extra：记录了一些重要的额外信息；
using filesort（文件排序），未按照表内的索引顺序进行搜索，而是自发的使用了另一种顺序，出现时，需要优化；
using temporary：使用了临时表保存中间结果，导致数据库运行缓慢，需要优化；
using index：表示相应的select操作使用了覆盖索引，避免访问了表的数据行，效率不错，是好事；
（6）索引分析：单表：直接加索引；
两表：左连接，索引加在右表；右连接，索引加在左表；
三表：与两表相同，反向加索引；
join语句的优化：
永远用小结果集驱动大结果集；
优先优化内层循环；
（7）索引优化：
先了解一下索引失效，因为会失效，所以需要优化：
索引失效的原因：
<1>全值匹配我最爱
<2>最佳左前缀法则：如果索引了多列，要遵守最佳左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列，如果最左前列的索引没有使用到，则整个索引失效；另外，有头有尾的情况下，中间的索引也不能少；
<3>不在索引列上做任何操作（计算，函数，自动类型转换），会导致索引失效而转向全表扫描
<4>存储引擎不能使用索引中范围条件右边的列，范围之后全失效

<5>尽量使用覆盖索引（索引列和查询列一致），减少select *

<6>mysql在使用不等于（!=或者<>）时无法使用索引会导致全表扫描

<7>is null，is not null也无法使用索引

<8>like以通配符开头（’%abc’）mysql索引失效会导致全表扫描，加在右边可以

面试题：解决like‘%字符串%’时索引失效不被使用的方法？
答：用覆盖索引来解决这个问题，所谓覆盖索引，就是建的索引和查的字段符合一致；
例如：索引创建为name和age：

查询时select的也是name和age：

<9>字符串不加单引号，索引失效

<10>少用or，用它来连接时会索引失效