Go语言视频零基础入门到精通

admin

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. 1:hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,
3. 　　以主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置.
5. 2: 无法对范围查询进行优化.
7. 3: 无法利用前缀索引. 比如 在btree中, field列的值“hellopworld”,并加索引
9. 查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引)
11. 　　因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机
13. 4: 排序也无法优化.
15. 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不能用
  


 
 
 
 可以理解为下图:
　　索引是按照索引定义的顺序来进行使用，也就是右边的索引使用的前提是左边的索引查询必须使用等号(能唯一确定一个值)，如果是>,<或者like "xxx"的话找到的是一个区间，所以后面的索引无法使用。
&nbsp;
&nbsp;
&nbsp;
&nbsp;
为便于理解, 假设ABC各10米长的木板, 河面宽30米.
　　　　全值索引则木板长10米(使用=则木板长十米)
　　　　Like,左前缀及范围查询, 则木板长6米(使用范围查询相当于将模板截断)

自己拼接一下,能否过河对岸,就知道索引能否利用上.
如上例中, where a=3 and b>10, and c=7,
　　　　A　板长10米,A列索引发挥作用
　　　　A板正常接B板, B板索引发挥作用
　　　　B板短了,接不到C板, C列的索引不发挥作用.
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
思考题:假设某个表有一个联合索引（c1,c2,c3,c4）一下——只能使用该联合索引的c1,c2,c3部分
A where c1=x and c2=x and c4>x and c3=x　　　　c1,c2,c3,c4都用上了(mysql会在不影响语义的情况下将语句优化可以理解为where c1=x and c2=x&nbsp; and c3=x and c4>x)
B where c1=x and c2=x and c4=x order by c3
C where c1=x and c4= x group by c3,c2
D where c1=x and c5=x order by c2,c3
E where c1=x and c2=x and c5=? order by c2,c3
&nbsp;
&nbsp;
多列索引测试解决上面问题:

1. CREATE TABLE t5 (
2. c1 CHAR(1) NOT NULL DEFAULT "a",
3. c2 CHAR(1) NOT NULL DEFAULT "b",
4. c3 CHAR(1) NOT NULL DEFAULT "c",
5. c4 CHAR(1) NOT NULL DEFAULT "d",
6. c5 CHAR(1) NOT NULL DEFAULT "e",
7. INDEX c1234(c1,c2,c3,c4)
8. );

复制代码

&nbsp;
&nbsp;
&nbsp;3行数据:

1. mysql> select * from t5;
2. +----+----+----+----+----+
3. | c1 | c2 | c3 | c4 | c5 |
4. +----+----+----+----+----+
5. | A  | B  | C  | D  | E  |
6. | a  | A  | C  | D  | E  |
7. | b  | b  | c  | d  | e  |
8. +----+----+----+----+----+
9. 3 rows in set (0.00 sec)

复制代码

&nbsp;
&nbsp;
&nbsp;
A选项验证:(4列索引都用上)

1. mysql> explain select * from t5 where  c1="a" and c2="b" and c4>"D" and c3="c" \
2. G
3. *************************** 1. row ***************************
4.            id: 1
5.   select_type: SIMPLE
6.         table: t5
7.    partitions: NULL
8.          type: range
9. possible_keys: c1234
10.           key: c1234
11.       key_len: 12
12.           ref: NULL
13.          rows: 1
14.      filtered: 100.00
15.         Extra: Using index condition
16. 1 row in set, 1 warning (0.00 sec)

复制代码

&nbsp;
&nbsp;
B选项验证:(c1 c2使用索引，c3在c2确定的情况下本身是有序的，所以使用了c3索引进行排序，总的是c1,c2使用了索引。)

1. mysql> explain select * from t5 where c1="a" and c2="b" and c4="d" order by c3 \
2. G
3. *************************** 1. row ***************************
4.            id: 1
5.   select_type: SIMPLE
6.         table: t5
7.    partitions: NULL
8.          type: ref
9. possible_keys: c1234
10.           key: c1234
11.       key_len: 6
12.           ref: const,const
13.          rows: 1
14.      filtered: 33.33
15.         Extra: Using index condition   
16. 1 row in set, 1 warning (0.00 sec)

复制代码

&nbsp;
&nbsp;
将B选项改成按c5排序:(Extra: Using index condition; Using filesort(#表示文件排序,&nbsp; 表明取出来数据之后又在磁盘上进行了排序，上面是利用了c3索引（因为c3就是有序的）)

1. mysql> explain select * from t5 where c1="a" and c2="b" and c4="d" order by c5 \
2. G
3. *************************** 1. row ***************************
4.            id: 1
5.   select_type: SIMPLE
6.         table: t5
7.    partitions: NULL
8.          type: ref
9. possible_keys: c1234
10.           key: c1234
11.       key_len: 6
12.           ref: const,const
13.          rows: 1
14.      filtered: 33.33
15.         Extra: Using index condition; Using filesort
16. 1 row in set, 1 warning (0.00 sec)

复制代码

&nbsp;
&nbsp;
C选项验证:where c1=x and c4= x group by c3,c2
　　一般而言，分组统计首先是按分组字段有序排列(这里的一般是指不使用索引的情况。使用临时表进行排序)

1. mysql> explain select * from t5 where c1="a" and c4="d" group by c3,c2\G
2. *************************** 1. row ***************************
3.            id: 1
4.   select_type: SIMPLE
5.         table: t5
6.    partitions: NULL
7.          type: ref
8. possible_keys: c1234
9.           key: c1234
10.       key_len: 3
11.           ref: const
12.          rows: 2
13.      filtered: 33.33
14. Extra: Using index condition; Using temporary; Using filesort #表示使用临时表排序，且使用文件排序
15. 1 row in set, 1 warning (0.00 sec)

复制代码

&nbsp;
&nbsp;
&nbsp;
　　将上面的分组按c2,c3验证(首先按c2,c3排序，由于c1使用索引，且c2,c3有序，因此不会使用临时表，也不会使用文件排序)

1. mysql> explain select * from t5 where c1="a" and c4="d" group by c2,c3\G
2. *************************** 1. row ***************************
3.            id: 1
4.   select_type: SIMPLE
5.         table: t5
6.    partitions: NULL
7.          type: ref
8. possible_keys: c1234
9.           key: c1234
10.       key_len: 3
11.           ref: const
12.          rows: 2
13.      filtered: 33.33
14.         Extra: Using index condition
15. 1 row in set, 1 warning (0.00 sec)

复制代码

&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
D选项验证:(c1使用了索引，由于c1下面的c2有序,c2下面的c3有序，所以使用c2,c3的索引进行排序，总的来说c1使用了索引)

1. mysql> explain select * from t5 where c1="a" and c5="e" order by c2,c3\G
2. *************************** 1. row ***************************
3.            id: 1
4.   select_type: SIMPLE
5.         table: t5
6.    partitions: NULL
7.          type: ref
8. possible_keys: c1234
9.           key: c1234
10.       key_len: 3
11.           ref: const
12.          rows: 2
13.      filtered: 33.33
14.         Extra: Using index condition; Using where
15. 1 row in set, 1 warning (0.00 sec)

复制代码

&nbsp;
&nbsp;
将上面排序条件换为c3,c2之后验证:(先按c3,再按c2排序，所以取出来之后需要在磁盘排序)

1. mysql> explain select * from t5 where c1="a" and c5="e" order by c3,c2\G
2. *************************** 1. row ***************************
3.            id: 1
4.   select_type: SIMPLE
5.         table: t5
6.    partitions: NULL
7.          type: ref
8. possible_keys: c1234
9.           key: c1234
10.       key_len: 3
11.           ref: const
12.          rows: 2
13.      filtered: 33.33
14.         Extra: Using index condition; Using where; Using filesort
15. 1 row in set, 1 warning (0.00 sec)

复制代码

&nbsp;
&nbsp;
&nbsp;
E选项验证:(此处c2="b"后面按c2排序，可以将c2看为一个常量，也就不影响c3的索引，所以使用c3索引排序，前面使用了c1,c2索引)
　　等价于　　select * from t5 where c1="a" and c2="b" and c5="e" order by c3&nbsp; &nbsp;(因为c2是一个常量，因为c2的值既是固定的,参与排序时并不考虑)

1. mysql> explain select * from t5 where c1="a" and c2="b" and c5="e" order by c2,c
2. 3 \G
3. *************************** 1. row ***************************
4.            id: 1
5.   select_type: SIMPLE
6.         table: t5
7.    partitions: NULL
8.          type: ref
9. possible_keys: c1234
10.           key: c1234
11.       key_len: 6
12.           ref: const,const
13.          rows: 1
14.      filtered: 33.33
15.         Extra: Using index condition; Using where
16. 1 row in set, 1 warning (0.00 sec)

复制代码

&nbsp;
&nbsp;
&nbsp;
至此上面的题目解决。
&nbsp;
&nbsp;
&nbsp;
一道面试题:
有商品表, 有主键,goods_id,&nbsp; 栏目列 cat_id, 价格price
说:在价格列上已经加了索引,但按价格查询还是很慢,
问可能是什么原因,怎么解决?
&nbsp;
答: 在实际场景中,一个电商网站的商品分类很多,直接在所有商品中,按价格查商品,是极少的,一般客户都来到分类下,然后再查.
&nbsp;
改正: 去掉单独的Price列的索引, 加 (cat_id,price)复合索引
再查询.
&nbsp;
&nbsp;
&nbsp;

总结:　　BTree索引必须遵循左前缀匹配，且必须使用=进行查询，也就是精确到一个值
　　1.BTree索引相当于是有序的排列，where查询的时候会根据对应的顺序位置去利用索引进行查询
　　2.多列索引中索引的使用规则遵循左前缀匹配原则，也就是一个索引能不能用上关键看其上一个索引能不能精确的定位(使用"="查询的可以精确，使用区间查询和模糊查询不能精确)
　　3.索引对于排序的作用:排序的字段能否使用上索引是看其前面的查询数据能不能精确的定位到一个值，如果其前面的索引都是使用等号查询，则索引会用于排序，如果索引不能用于排序则会使用文件排序，也就是在磁盘上进行排序。
　　　　例如:对于上面的index(c1,c2,c3,c4)

1. mysql> explain select * from t5 where c1="a" order by  c2,c3\G
2. *************************** 1. row ***************************
3.            id: 1
4.   select_type: SIMPLE
5.         table: t5
6.    partitions: NULL
7.          type: ref
8. possible_keys: c1234
9.           key: c1234
10.       key_len: 3
11.           ref: const
12.          rows: 2
13.      filtered: 100.00
14.         Extra: Using index condition　　#由于c1使用了索引，c1下的c2是有序的，c2下的c3是有序的，索引排序的时候会根据c2的顺序不停的扫描c2下的c3，也就是c2,c3索引用于排序
15. 1 row in set, 1 warning (0.00 sec)
17. mysql> explain select * from t5 where c1="a" order by  c3\G
18. *************************** 1. row ***************************
19.            id: 1
20.   select_type: SIMPLE
21.         table: t5
22.    partitions: NULL
23.          type: ref
24. possible_keys: c1234
25.           key: c1234
26.       key_len: 3
27.           ref: const
28.          rows: 2
29.      filtered: 100.00
30.         Extra: Using index condition; Using filesort　　#由于c1使用了索引，但是没有使用c2,所以c2,c3索引不能用于排序，也就需要文件排序(磁盘中进行)

复制代码

1. 1 row in set, 1 warning (0.00 sec)

复制代码

&nbsp;
&nbsp;
　　4.索引对于分组的作用:首先明白分组的时候是先按分组的字段进行排序，然后值相同的才属于同一个组，所以索引对于分组的作用类似于对排序的作用。
&nbsp;
　　5.查看一个表的索引:

1. mysql> show index from tblname;
3. mysql> show keys from tblname;

复制代码

&nbsp;
例如:

1. mysql> show keys from t5\G
2. *************************** 1. row ***************************
3.         Table: t5
4.    Non_unique: 1
5.      Key_name: c1234
6. Seq_in_index: 1
7.   Column_name: c1
8.     Collation: A
9.   Cardinality: 1
10.      Sub_part: NULL
11.        Packed: NULL
12.          Null:
13.    Index_type: BTREE
14.       Comment:
15. Index_comment:
16. *************************** 2. row ***************************
17.         Table: t5
18.    Non_unique: 1
19.      Key_name: c1234
20. Seq_in_index: 2
21.   Column_name: c2
22.     Collation: A
23.   Cardinality: 1
24.      Sub_part: NULL
25.        Packed: NULL
26.          Null:
27.    Index_type: BTREE
28.       Comment:
29. Index_comment:
30. *************************** 3. row ***************************
31.         Table: t5
32.    Non_unique: 1
33.      Key_name: c1234
34. Seq_in_index: 3
35.   Column_name: c3
36.     Collation: A
37.   Cardinality: 1
38.      Sub_part: NULL
39.        Packed: NULL
40.          Null:
41.    Index_type: BTREE
42.       Comment:
43. Index_comment:
44. *************************** 4. row ***************************
45.         Table: t5
46.    Non_unique: 1
47.      Key_name: c1234
48. Seq_in_index: 4
49.   Column_name: c4
50.     Collation: A
51.   Cardinality: 1
52.      Sub_part: NULL
53.        Packed: NULL
54.          Null:
55.    Index_type: BTREE
56.       Comment:
57. Index_comment:
58. 4 rows in set (0.00 sec)

复制代码

&nbsp;