索引使用存在的问题:
- 回表
- 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
强制使用辅助索引。