1.主键上有默认索引
2.索引一定要注意最左侧匹配原则
3.索引是b+树
4.建议数据检索时,在条件字段添加索引。
5.为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引,有7大原则:
1.选择唯一性索引
2.为经常需要排序、分组和联合操作的字段建立索引
3.为常作为查询条件的字段建立索引
4.限制索引的数目
5.尽量使用数据量少的索引
6.尽量使用前缀来索引
7.删除不再使用或者很少使用的索引
- 经常更新修改的字段不要建立索引(针对mysql说,因为字段更改同时索引就要重新建立,排序,而Orcale好像是有这样的机制字段值更改了,它不立刻建立索引,排序索引,而是根据更改个数,时间段去做平衡索引这件事的)
9、不推荐在同一列建多个索引
10.在进行多表联查时我们使用了left join
第一次在左边的表加了索引
但是没有效果
这是由左连接的特性决定的,left join 是明确左边表的数据肯定都有,从右边表进行查询数据
所以右边是关键,我们一定要加上索引
那么多表查询也是同样的,反正记住一点,左连接加右表,右连接加到左表
小表驱动大表
11.FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
ON: 对虚表VT1进行ON筛选,只有那些符合的行才会被记录在虚表VT2中。
JOIN: 如果指定了OUTER JOIN(比如left join、 right join),那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中,产生虚拟表VT3, rug from子句中包含两个以上的表的话,那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤,一直到处理完所有的表为止。
WHERE: 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
GROUP BY: 根据group by子句中的列,对VT4中的记录进行分组操作,产生VT5.
CUBE | ROLLUP: 对表VT5进行cube或者rollup操作,产生表VT6.
HAVING: 对虚拟表VT6应用having过滤,只有符合的记录才会被 插入到虚拟表VT7中。
SELECT: 执行select操作,选择指定的列,插入到虚拟表VT8中。
DISTINCT: 对VT8中的记录进行去重。产生虚拟表VT9.
ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作,产生虚拟表VT10.
LIMIT:取出指定行的记录,产生虚拟表VT11, 并将结果返回。
12.所以说,联合查的时候,索引还是各表根据条件找各表的索引
13.EXPLAIN select * from ai_alarm where del_mark=1 and alarm_num=1
explain 查看执行情况,包括索引执行情况
14.多列索引在and查询中应用
select * from test where a=? and b=? and c=?;查询效率最高,索引全覆盖。
select * from test where a=? and b=?;索引覆盖a和b。
select * from test where b=? and a=?;经过mysql的查询分析器的优化,索引覆盖a和b。
select * from test where a=?;索引覆盖a。
select * from test where b=? and c=?;没有a列,不走索引,索引失效。
select * from test where c=?;没有a列,不走索引,索引失效。
15.多列索引在范围查询中应用
select * from test where a=? and b between ? and ? and c=?;索引覆盖a和b,因b列是范围查询,因此c列不能走索引。
select * from test where a between ? and ? and b=?;a列走索引,因a列是范围查询,因此b列是无法使用索引。
select * from test where a between ? and ? and b between ? and ? and c=?;a列走索引,因a列是范围查询,b列是范围查询也不能使用索引。
16.多列索引在排序中应用
select * from test where a=? and b=? order by c;a、b、c三列全覆盖索引,查询效率最高。
select * from test where a=? and b between ? and ? order by c;a、b列使用索引查找,因b列是范围查询,因此c列不能使用索引,会出现file sort。
17.总结联合索引的使用在写where条件的顺序无关,mysql查询分析会进行优化而使用索引。但是减轻查询分析器的压力,最好和索引的从左到右的顺序一致。使用等值查询,多列同时查询,索引会一直传递并生效。因此等值查询效率最好。索引查找遵循最左侧原则。但是遇到范围查询列之后的列索引失效。排序也能使用索引,合理使用索引排序,避免出现file sort
18.建一个索引建一个b+树
19.多个单列索引会建多个b+树
20.其实这里其实涉及到了mysql优化器的优化策略!当多条件联合查询时,优化器会评估用哪个条件的索引效率最高!它会选择最佳的索引去使用,也就是说,此处userid 、mobile 、billMonth这三个索引列都能用,只不过优化器判断使用userid这一个索引能最高效完成本次查询,故最终explain展示的key为userid。
21.关于or查询的真相是:
所谓的索引失效指的是:假如or连接的俩个查询条件字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描。我们从or的基本含义出发应该能理解并认可这种说法,没啥问题。
此刻需要注意type类型为index_merge。
我查资料说mysql 5.0 版本之前 使用or只会用到一个索引(即使如上我给userid和mobile都建立的单列索引),但自从5.0版本开始引入了index_merge索引合并优化!也就是说,我们现在可以利用上多个索引去优化or查询了。
index_merge作用:
1、索引合并是把几个索引的范围扫描合并成一个索引。
2、索引合并的时候,会对索引进行并集,交集或者先交集再并集操作,以便合并成一个索引。
3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。