SQL的索引学习部分总结

索引
索引是数据结构，可以帮助mysql高效获取数据。是排好序的快速查找数据结构。
索引一般是以BTREE实现的，每个节点保存着索引键值，指向表中的物理数据。而且索引本身也很大，一般不会放在内存中。

*- 优点：1.提高了数据检索的效率，降低数据库的IO成本；2.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

• 缺点：1.索引实际上也是一张表，保存了主键和索引字段，并指向实体表的记录，所以索引列占用空间较大；2.降低了更新表的速度，因为每个修改表都会更新索引表；3.优化耗时*

索引的分类

• 单值索引：一个索引只包含单个列，一个表可以有多个单值索引
• 唯一索引：索引列的值必须唯一，但允许控制
• 复合索引：一个索引包含多个列

创建索引：
单值：

唯一：

复合：

删除索引：

查看索引：

建立索引的抉择：

需要建立索引的情况：

• 主键自动建立的唯一索引
• 频繁作为查询条件的字段应该创建
• 查询中与其他表关联的字段，外键关系建立索引
• 分组用到的字段

不需要建立索引的情况：

• 频繁更新的字段
• where条件中用不到的字段
• 表记录太少
• 数据重复分布均匀的字段

性能分析

mysql Query Optimizer:
mysql中专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求query提供他认为最优的执行计划。

常见瓶颈：
CPU、io、硬件

explain


字段解释：

• id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。有三种情况：1.id相同，执行顺序从上到下；2.id不同，id值越大，优先级越高，越先被查询；3.相同又不同，仍然是越大优先级越高，相同的按顺序

• select_type:给出mysql认为的查询类型，有6种

  • SIMPLE：简单select查询，查询中不包含子查询或者UNION
  • PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为此
  • SUBQUERY：在select或者where列表中包含了子查询
  • DERIVED：在from列表中包含的子查询被标记为此，mysql会递归执行 这些子查询，把结果放在临时表中
  • UNION:若第二个select出现在UNION之后，则被标记为UNION；若UNION包含子from子句的子查询中，外层select将被标记为：DERIVED
  • UNION RESULT:从UNION结果中使用select

• table：显示这一行数据是属于哪张表的

• type：显示查询使用了何种类型，评价语句好坏。最好到最坏：system>const>eq_ref>ref>range>index>ALL
  - system:表中只有一行记录，等于系统表
  - const：表示索引一次就找到
  - eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
  - ref:非唯一索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问
  - range:只检索给定范围的行，使用一个索引来选择行
  - index:只遍历索引树
  - ALL:全表扫描

• possible_keys：显示可能用在这张表中的索引，一个或多个。

• key:实际用到的索引。如果为NULL，则没有使用索引

• key_len:表示索引中使用的字节数，显示索引字段的最大可能长度，并非实际使用长度。

• ref:显示索引的哪一列被使用

• rows:根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数

• Extra:额外信息。
  - Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”
  - Using temporary：使用临时表保存中间结果，mysql在对查询结果排序时使用临时表
  - USING index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错

注意：拿左连接来说，索引应该加在右表上，因为由左连接的特性决定，结果集左边的表内容都是全有的。