Mysql索引优化

索引使用存在的问题：

• 回表
  • innodb默认使用主键索引是聚簇索引，其他索引是非聚簇索引。当使用非聚簇索引查询时，先在B+Tree叶子节点上获得主键id，然后根据主键id再去聚簇索引查询叶子节点的行数据。这两次查询就称作回表。
• 页分裂
  • B+Tree数据存放在叶子节点上，叶子节点大小为16K会自动分裂生成更多叶子节点，这样会导致内存碎片，索引结构不紧凑，查询效率低。

索引优化：

• 建立整型自增主键

  • 如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)

  • 如果我们使用了随机的UUID来作为主键，因为是无序的，如果某个节点达到来16k,插入就需要移动数据，这样事实上在聚簇数据上会容易产生B+Tree的分裂、也容易浪费系统性能。

• 使用覆盖索引

  • 比如要查询a,b,c三个列的数据，可以在a,b,c三列上建立组合索引，可以直接从这三列的组合索引上获得数据，不需要在进行回表查询。

• 使用前缀索引

  • 如果在大字段上建立索引，会导致每个页上存放的索引数少，那么就需要查询多个页，降低了查询效率。可以使用前缀索引，只使用列的部分数据建立索引。

    1 2	-- 设置前缀索引 alter table demo add index name( user_name(4) )

    注意：order by 不能使用前缀索引。也不能把前缀索引作覆盖索引。

• 避免索引失效

  • 复合索引按最左匹配原则
  • 复合索引使用or，如果or前后包含了非复合索引字段，也不能使用复合索引。

特殊情况：

• 当发生回表时候，假如查询的的数据行数很多(超过整张表20%)，mysql优化器会转为使用聚簇索引，如果发现mysql优化的结果不如使用辅助索引来的快，那么可以手动使用命令force index强制使用辅助索引。