召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2016|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能/ K4 u8 v$ e$ ?* U( Y0 v: V
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成5 Z/ C' s" i1 x
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
/ A6 o8 B) t. y: H* H2 b, D3 U在InnoDB中,只有事务提交后才会解锁
5 L2 Y/ V8 E4 k
' d& d  {7 S  v6 C7 W! h* z索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只  K$ ~1 {$ v" {: s1 Y2 z4 ?- j9 n* o
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。4 ~* u' z2 @: n* u, J% ^3 Q8 C

- O! I- [9 Y. j$ PB-TREE- a3 ?( j& b1 ~  L9 H* g0 Y
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
( w- j% Z5 t4 H$ @* t使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。
" u3 N0 g2 M) E0 X, y7 y. U% X

  1. . n9 p' D2 s9 {* c
  2. CREATE TABLE People(% L  l3 g# x$ j' M
  3. last_name varchar(50)   not  null: k+ M7 O, [, I) G3 V8 i
  4.           first_name  varchar(50)     not   null
    8 ^( s/ s7 }( K. d* `; m
  5.           dob  date      not    null
    0 x5 C  u2 G  Z: ~
  6.       gende       enum('m','f')    not    null; g( ?* K+ }) E
  7.         key(last_name,first_name,dob)
复制代码
匹配全名
, |7 F2 [( D$ z5 n4 o- S2 O0 z; s全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
0 F# F0 P+ M" ]$ h5 [& ?6 y9 W的人。# A, x$ C5 T0 T/ U/ B, o8 ?( S4 p
匹配最左前缀$ E2 ^( b" r7 B) a& A( B( c5 P
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。5 K' Q# E* K9 D! D
匹配列前缀0 g/ Q/ |4 |/ a1 r7 X
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。! m  G/ D, ?& i
匹配范围值* j7 n# Y, i% M1 q: w4 h
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.
- O; l# O% w/ F4 A& K8 t精确匹配一部分并且匹配某个范围中的另一部分0 c0 B0 Y7 A7 F
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
3 O, b, Y. o' X! ]9 Y列并且对first name列进行了范囤查询。
$ j: Y4 _  m: g  L# Q: cname' ?! Q8 h( O* ^+ C! v
只访问索引的查询' |) `5 e' |! }
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。7 K7 l; c  r5 h1 x: v! L9 p
& |! v' u. k( l' \% u3 e, R
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,+ B0 I9 H  X( J  \# s! `2 }
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
; o2 w7 g7 E' v$ z# K* B找方式也可以同等地应用于ORDER BY。
( A* Q7 `: K3 p9 V  T/ I& J2 E, x
下面是B-Tree索引的一些局限:
, E1 r' i1 t; q: k$ ?( _2 j5 ~- I7 }! s9 t1 ^
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,& [) m- O4 a/ c- o' S/ j. M
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓6 o* r% H: g; }7 f
氏以特定字符结尾的人。- A! ~& T* G2 N2 `; Y

' ^6 h+ \- |+ f; \$ G2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定+ p' C; F  S& C2 k9 H
义first_name列的值,MySQL就只能使用索引的第一列。+ U% k) d4 l, u% P, q9 q1 J
7 P4 @7 M0 Y0 y" p5 G
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
6 l- z  ^. e. F7 y4 C8 W范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而2 M# n4 {5 u6 d6 Y5 v0 {# d
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。2 o3 Z, C8 f: \! ?' l# q

, g. r. q$ x$ r5 \8 S5 i1 [$ [哈希索引,空间索引和全文索引等,暂时没有设计) a; p& L7 y2 ?, V

9 h" P8 c, R" l, M1 L7 X高性能索引策略
, O. N4 }) S. A4 u* \- i- N, q, [' K/ P5 f  r/ W; y% w( v3 d
1,隔离列,意思就是不要对查询条件中列进行计算等操作# d% B% }) q7 Q4 k$ o5 t
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引* J8 E( j  c. S' l: \+ ~  I5 ?3 [
Select count(distinct 列) /count(*) from table;
' W. ], Z5 h; x  c# d看看这个值时多少,如0.0312
, ~1 e& u2 e  d那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算' w* j0 A$ @$ p, R. V
,这对于大表很有用。/ B+ Z- |! b. m1 L0 {+ q7 O2 x( m( Z
Select  count(distinct left(列,3)) /count(*)  as  sel1,
' w0 Y( o* |, A/ e' o$ r count(distinct left(列,4)) /count(*)  as  sel1  ,
) p2 i, Y4 b0 i; E& u% ]' m count(distinct left(列,5)) /count(*)  as  sel1,8 `7 s6 `$ }0 Z1 ?- h
count(distinct left(列,6)) /count(*)  as  sel1,7 |5 q- b3 q% e8 U" g: N$ i
count(distinct left(列,7)) /count(*)  as  sel1   from table;
" Y* r! N0 @1 R1 c找到接近0.0312即可。7 N, O1 a) v& ^$ f9 Z0 [' W, ~1 S0 `
5 q" W! f* G( Z# ]' u
Alter table table_name add key (列(7))
2 o5 Q4 C8 O& G/ {. C  z3,覆盖索引
6 @) ?( {6 }8 Q包含或者覆盖所有满足查询的数据索引叫做覆盖索引
8 ]# l6 L/ c8 q  J/ z3 gexplain时,extra中的会显示using index
. K  ?. s9 \* A% k9 J0 d; m这里一个重要的原则是
1 x1 E) H( z$ N; Wselect后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
+ P, m7 @1 I( D" {) n' f如select id from table_name;+ Q0 a" @+ i  P$ b* w3 A& D
- \" ~% X/ Z' Q
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不( Z, L7 v, Z0 a5 @/ n/ d
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
7 w# b4 d0 y# g/ W$ z  n. C7 B; l5 L" f
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';
    : V3 J- L2 @) z0 f" s0 b
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:
' D7 r9 h* u7 J; e5 \8 ?; ?1,3 s5 m) y5 F+ S( V& p
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。" o3 a. u! \1 F- Q6 t3 ^( R+ y0 j7 b
2,4 M5 h; n; c/ N
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。% G4 [" e  L% D! r6 s  e+ w
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:4 o- M* o# E& [! y* I
4 F7 @. c: a0 k* ^& k8 ~) }
4,为排序使用索引扫描  y5 w& r9 ]: ]$ ], {; X% }
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。2 n: E# @  S' _1 U
explain输出type为index,表示mysql会扫描索引
; a1 M9 D, B+ E- j( W; f% X% y" h7 M8 t5 ^0 |" q, [; W% x
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.. N5 c0 l$ S; b0 D" p# B
" P+ Z8 ?3 X5 M* T6 }. W1 Z. n" s/ R
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
. y* }$ x% A& @) a$ [% ]2 }9 `) K" a+ X, g) A- P7 C# A
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
2 H9 O5 f+ s" D- K  d- G; U5 y2 P. b* X1 {! @" A5 Z- C
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。  G  N* r* d) m
* P* Q0 R* D1 Y1 w5 E
使用join可能情况会有不同3 }/ u5 Z4 {2 `" F
. u$ {9 M: M' L& L' q' E
5,压缩索引(myisam)2 V5 N) u  h& h" Z0 N
6,多余和重复索引(应该避免); Y6 L5 T/ {/ d! B) I

6 E, ^+ Z) @2 J多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
( i2 D$ s6 R  S: A上有索引,那么另外一个列(A)上的
0 V6 ]1 X$ k$ R  v5 `0 p! {索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
6 z- o& k0 s8 i; s4 G8 {& n, ^3 Q然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
  u/ i2 t+ F% o1 n: h& J: d1 [+ m% z# i. B3 y, J
要点:: `$ I3 c7 f, D7 D
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.' q. x% n. `: J$ n
8 X" ~" h7 J/ [
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
4 Q- S1 _$ [4 y0 W% p1 D! D# t* [: `1 _9 n# N, i& n0 m7 k
. d, Y% \0 m  l% ~" Z* i
. a' M6 |2 e2 N2 P) @* v, S; b
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,
8 |$ ]6 n4 X# x. b8 O; E
. {. m+ ~7 V/ u5 s6 |拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引
& e7 u' C  H# G% M4 [! V  }0 q: x
, Z3 x5 ?. D. Z% K% Y

0 i) ^8 L0 V, {: j# s一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索& Q: }  k8 o" w) K
: T; T# |1 W$ C( z4 `: w
引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。5 z3 ]+ T, }( C+ i9 B4 b: [
+ b8 \1 G/ H2 |5 A, p

. }0 f! k; o) e* s' G3 c, E. _% t; j3 b$ |) x' n
一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。0 V+ l+ g* s7 }' Z- x+ _: g, w
" }+ p8 x4 F0 m7 l3 ?: g. d; h2 ^7 d. a
例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理; n% {' j% B- m. Z

2 V+ v* e- ~0 @0 z8 e这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式& t; T9 |2 s" x
- b) p) p4 D  B) i. Y8 p' D/ |

) W* ~: E9 E, N( t6 `
. h  Y5 c' D7 B( s8 r9 S避免多个范围条件,只能对其中一个使用索引
7 [2 Y5 }: p4 c% u' n" x8 v- r9 R* c4 W; P- e
: t8 G+ E2 J7 J) p9 Z+ {# |9 b; K

1 H: u3 ?4 {  T% A/ z索引和表维护
" k  h0 l2 I& {0 ~  L# H+ t2 k# k9 D: z7 R' O' A) u
表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.
4 G$ V: T' q! o+ M9 x3 h1 ~+ \
5 W% `, K+ Z0 X8 l  ?check table table_name;
& }  K5 `3 Y, Y* d% crepair table table_name;% s# f1 x+ l" q# A6 ~
Show index from table_name;检查索引的基数性
8 I& N& S5 ?3 [, p- ^0 z& d. n8 X: P& n
主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量
% P* m9 _! O; {$ C" a: |
7 a, R9 I) ]" J) e$ }- U4 O
" d$ o6 o! h8 w( N& g5 e
. ]3 y6 @  j$ U, y1 Q) |B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。
3 P& A% K6 o3 f, D6 j1 v
  G' l) ]) @- @# K表数据也能变成碎片化。两种类型:
3 ^5 O" x4 @# K# G3 x* X: d  z- Y9 f* D5 }5 y3 E7 e- [
1,行碎片0 ?/ j. B" E, F- H8 `7 B

/ ~5 K/ y3 `7 U  @当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。3 Y1 h' U) U' D7 ^# C

" F; P/ B. F: |  R2 q& H1 i6 u# M& i6 L1 q

: y: P! q1 t* @2,内部行碎片
; x# v; S9 U8 D: R$ e5 v/ X5 c, ~6 j' v* u
当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和' }( H: W& t2 e" ~" H
: i7 Y/ @: u- m
聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。
$ ~1 Q0 S& E' J  U( ~9 ^
/ Q! I" [+ e7 z0 B0 N
# E7 u2 Z5 e# W( S. K
. N% E1 O* u* _$ ^为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。
7 v) b# V: q4 ?2 F, |3 j
/ \" G& a. F/ M+ h; k2 u6 y) z; @9 l5 y$ l, J# F2 V

1 V" n4 b" N  A) _" J. B: H0 rALTER TABLE <table> ENGINE=<engine>
# I, c0 `' A8 U9 O2 D  G+ W& p! }1 C7 m0 u
+ I1 t% C0 W4 N1 L9 H0 o8 V! \
' J8 }2 X+ d9 a: p" G
加速ALTER TABLE
  C9 D. n9 N9 i6 c+ x/ O. k# H( J* r4 [4 R: w& C9 Z

, A$ c: d1 b# ?' Q& V& Q
- ?8 l$ X- y. M1 lMySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需
6 s" l' P4 U- W# ?* U! b4 s* f5 J1 l4 r0 b  f6 V- Y
要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,
- c) Q, |% _# f! L  N  T6 h
& r7 ?2 U" [) J: F9 |( `而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。
& F+ ^. `, `9 p6 U
3 b5 I" d* o( Z; }# D传统:$ m8 _9 `: F' |- q
& I2 D6 m2 h& f5 Y- h; g
ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;
+ r1 D" V. Y6 o7 W  q7 N" }) z4 o理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更
. K: y, k* G: ~5 v9 r. `改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。
& Y) L& B# E( k2 S! a! q9 X: ^+ b
$ J# z. x  V- x# P7 \1 [变化:
$ O: `! Z/ K: k0 L' y8 T. s6 o1 h
( z! k- d' Q/ DALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;6 U- O  O8 _& p4 M; b- N) V
这个命令更改了.frm文件并且没有改动表。它非常快。; l, A& Q# J. W6 J' A9 V3 j
还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|小黑屋|召隆企博汇 ( 粤ICP备14061395号 )

GMT+8, 2025-6-27 09:16 , Processed in 0.032993 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

快速回复 返回顶部 返回列表