1:索引类型
1.1 B-tree索引
注: 名叫btree索引,大的方面看,都用的平衡树,但具体的实现上, 各引擎稍有不同,
比如,严格的说,NDB引擎,使用的是T-tree
Myisam,innodb中,默认用B-tree索引
但抽象一下---B-tree系统,可理解为”排好序的快速查找结构”.
1.2 hash索引
在memory表里,默认是hash索引, hash的理论查询时间复杂度为O(1)
疑问: 既然hash的查找如此高效,为什么不都用hash索引?
答:
1:hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,
比主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置.
2: 不法对范围查询进行优化.
3: 无法利用前缀索引. 比如 在btree中, field列的值“hellopworld”,并加索引
查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引)
因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机
4: 排序也无法优化.
5: 必须回行.就是说 通过索引拿到数据位置,必须回到表中取数据
2: btree索引的常见误区
2.1 在where条件常用的列上都加上索引
例: where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品
误: cat_id上,和, price上都加上索引.
错: 只能用上cat_id或Price索引,因为是独立的索引,同时只能用上1个.
2.2 在多列上建立索引后,查询哪个列,索引都将发挥作用
误: 多列索引上,索引发挥作用,需要满足左前缀要求.
以 index(a,b,c) 为例,(注意和顺序有关)
语句
索引是否发挥作用
Where a=3
是,只使用了a列
Where a=3 and b=5
是,使用了a,b列
Where a=3 and b=5 and c=4
是,使用了abc
Where b=3 / where c=4
否
Where a=3 and c=4
a列能发挥索引,c不能
Where a=3 and b>10 and c=7
A能利用,b能利用, C不能利用
同上,where a=3 and b like ‘xxxx%’ and c=7
A能用,B能用,C不能用
innodb聚簇索引
优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)
劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.
C) 聚簇索引的页分裂过程
1: innodb的buffer_page 很强大.
2: 聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值,
(不要用随机字符串或UUID)
否则会造成大量的页分裂与页移动.
高性能索引策略
0:对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.
对于innodb的主键,尽量用整型,而且是递增的整型.
如果是无规律的数据,将会产生的页的分裂,影响速度.
索引覆盖:
索引覆盖是指 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.
这种查询速度非常快,称为”索引覆盖”
1:查询频繁 2:区分度高 3:长度小 4: 尽量能覆盖常用查询字段.
区分度高: 100万用户,性别基本上男/女各为50W, 区分度就低.
索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).
针对列中的值,从左往右截取部分,来建索引
1: 截的越短, 重复度越高,区分度越小, 索引效果越不好
2: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.
索引与排序
排序可能发生2种情况:
1: 对于覆盖索引,直接在索引上查询时,就是有顺序的, using index
2: 先取出数据,形成临时表做filesort(文件排序,但文件可能在磁盘上,也可能在内存中)
我们的争取目标-----取出来的数据本身就是有序的! 利用索引来排序.
比如: goods商品表, (cat_id,shop_price)组成联合索引,
where cat_id=N order by shop_price ,可以利用索引来排序,
select goods_id,cat_id,shop_price from goods order by shop_price;
// using where,按照shop_price索引取出的结果,本身就是有序的.
select goods_id,cat_id,shop_price from goods order by click_count;
// using filesort 用到了文件排序,即取出的结果再次排序
mysql> select goods_id,click_count from goods where goods_id>20 order by goods_id;
+----------+-------------+
| goods_id | click_count |
+----------+-------------+
| 23 | 17 |
| 25 | 0 |
| 26 | 0 |
| 27 | 0 |
| 28 | 0 |
| 29 | 0 |
| 30 | 1 |
+----------+-------------+
7 rows in set (0.00 sec)
mysql> select goods_id,click_count from goods where goods_id>20 order by click_count;
+----------+-------------+
| goods_id | click_count |
+----------+-------------+
| 25 | 0 |
| 26 | 0 |
| 27 | 0 |
| 28 | 0 |
| 29 | 0 |
| 30 | 1 |
| 23 | 17 |
+----------+-------------+
7 rows in set (0.00 sec) mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| Sorting result | 0.000005 |
+----------------------+----------+
17 rows in set (0.00 sec)
mysql> show profile for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| Sorting result | 0.000048 |
+----------------------+----------+
17 rows in set (0.00 sec)
