MySQL优化班13期课后作业,20180726

设计索引时,都有什么关键注意事项(避开影响SQL效率的坑,或者说如何确保能提升SQL效率)
已邀请:

aaron8219 - Oracle DBA

赞同来自:

我觉得主要有有以下几个方面需要注意:
1. 尽量创建业务无关的自增主键,字段类型可以是int或bigint,原因有以下几点:
1)创建显式主键作为innod引擎的聚集索引,可以避免系统自己去采用唯一索引或rowid来作为聚集索引(Rowid全实例共享,容易产生冲突)
2)选取业务无关的列作为主键,可以减少主键被频繁更新概率
3)主键自增可以提升插入数据的效率,插入前只需找到最后一个位置直接插入即可,无需先进行排序并查找

2. 避免创建重复索引,因为维护索引是有代价的,主要有三个方面原因:
1)插入或更新数据时需要对索引进行排序
2)多余的索引会消耗page空间,实例启动加载表和索引时,需要消耗更多的内存
3)多余的索引在物理上也会占用更多的字节,使表变得庞大

3. 尽可能用联合索引替代单列索引(根据业务),原因:
1)对于一些只查询特定列的查询,会增加覆盖索引命中率,从而减少回表(单列索引作为覆盖索引的概率会低一些)
2)由于最左原则的限制,建立联合索引要注意索引列的先后顺序,需要与业务中常用查询条件中字段的顺序尽量保持一致,否则可能只能用上部分索引+ICP(5.6及以上版本)
 
4. 关于索引长度的限制说明:在行格式为compact或redundant时,单列索引最大长度为767字节
1)对于默认长度很长的字符串列创建索引时,最好匹配前几位字符创建前缀索引,而不是直接给整个字段创建索引
2)OLTP系统中,尽量避免对BLOB和TEXT等类型字段创建索引,如果一定要创建的话,也必须是前缀索引
3)使用前缀索引时会有弊端,第一,无法使用覆盖索引的特性(即会回表,降低查询性能);第二,无法通过索引进行排序(order by,group by)

5. 索引创建使用原则:
1)创建索引的目的就是为了提高检索数据的速度,提高查询性能,任何索引创建了但是无法使用的,都应该及时调整(删除重建)
2)索引未必是越多越好,够用即可,在能够满足业务SQL高效查询的前提下,应该使索引越少越好

Kiwi - 努力成为好的DBA

赞同来自:

简单的总结下:1.选择基数比较大的列做索引
2.尽量选择整形或日期时间型,避免使用长varchar型,text或blob字段
3.选择不会被经常修改的列
4.经常被选择作为查询条件的
5.索引够用就行,不是越多越好

要回复问题请先登录注册