索引

索引

• 索引：是特殊数据结构，定义在查找时作为查找条件的字段，在MySQL又称为键key，索引通过存储引擎实现

• 优点： 索引可以降低服务需要扫描的数据量，减少了IO次数 索引可以帮助服务器避免排序和使用临时表 索引可以帮助将随机I/O转为顺序I/O

• 缺点： 占用额外空间，影响插入速度

• 索引类型：
• B+ TREE、HASH、R TREE
• 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
• 主键索引、二级（辅助）索引
• 稠密索引、稀疏索引：是否索引了每一个数据项
• 简单索引、组合索引 左前缀索引：取前面的字符做索引 覆盖索引：从索引中即可取出要查询的数据，性能高

二叉树

红黑树

B-TREE索引

B+TREE索引

• B+Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据

• 可以使用B+Tree索引的查询类型： 全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30 匹配最左前缀：即只使用索引的第一列，如：姓wang 匹配列前缀：只匹配一列值开头部分，如：姓以w开头的 匹配范围值：如：姓ma和姓wang之间 精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的 只访问索引的查询

• B+Tree索引的限制：
• 如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
• 不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列

• 特别提示：
• 索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
• 为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

Hash索引

• Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好

• Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持

• 适用场景：只支持等值比较查询，包括=, <=>, IN()

• 不适合使用hash索引的场景 不适用于顺序查询：索引存储顺序的不是值的顺序 不支持模糊匹配 不支持范围查询 不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效

索引

• 空间数据索引R-Tree（ Geospatial indexing ） MyISAM支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多InnoDB从MySQL5.7之后也开始支持

• 全文索引(FULLTEXT) 在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎InnoDB从MySQL 5.6之后也开始支持

聚簇和非聚簇索引

聚簇和非聚簇索引，主键和二级索引

• 冗余和重复索引： 冗余索引：（A），（A，B），避免浪费磁盘空间，是允许的，没有意义 重复索引：已经有索引，再次建立索引

• 索引优化策略：
• 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧
• 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估索引选择性：不重复的索引值和数据表的记录总数的比值
• 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
• 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

索引优化建议

• 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引

• 尽量使用短索引，如果可以，应该制定一个前缀长度

• 对于经常在where子句使用的列，最好设置索引

• 对于有多个列where或者order by子句，应该建立复合索引

• 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引

• 尽量不要在列上进行运算（函数操作和表达式操作）

• 尽量不要使用not in和<>操作

SQL语句性能优化

• 查询时，能不要_就不用_，尽量写全字段名

• 大部分情况连接效率远大于子查询

• 多表连接时，尽量小表驱动大表，即小表 join 大表

• 在有大量记录的表分页时使用limit（限制每次返回多少条记录，性能会好一点，不会一次返回所有的记录，导致特别消耗资源）

• 对于经常使用的查询，可以开启缓存

• 多使用explain和profile分析查询语句

• 查看慢查询日志，找出执行时间长的sql语句优化

管理索引

创建索引：

删除索引：

查看索引：

优化表空间：

查看索引的使用

EXPLAIN

• 通过EXPLAIN来分析索引的有效性

• EXPLAIN SELECT clause 获取查询执行计划信息，用来查看查询优化器如何执行查询

• 输出信息说明： 参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

• id: 当前查询语句中，每个SELECT语句的编号 复杂类型的查询有三种： 1、简单子查询 2、用于FROM中的子查询 3、联合查询：UNION 注意：UNION查询的分析结果会出现一个额外匿名临时表

• select\_type： 简单查询为SIMPLE 复杂查询： SUBQUERY 简单子查询 PRIMARY 最外面的SELECT DERIVED 用于FROM中的子查询 UNION UNION语句的第一个之后的SELECT语句 UNION RESULT 匿名临时表

• table：SELECT语句关联到的表

• type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高
• ALL: 全表扫描
• index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描
• range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点
• ref: 根据索引返回表中匹配某单个值的所有行
• eq\_ref：仅返回一个行，但与需要额外与某个参考值做比较
• const, system: 直接返回单个行

• possible\_keys：查询可能会用到的索引

• key: 查询中使用到的索引

• key\_len: 在索引使用的字节数

• ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值

• rows：MySQL估计为找所有的目标行而需要读取的行数

• Extra：额外信息 Using index：MySQL将会使用覆盖索引，以避免访问表 Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary：MySQL对结果排序时会使用临时表 Using filesort：对结果使用一个外部索引排序

小结