请选择 进入手机版 | 继续访问电脑版

召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2747|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能. w0 d# o/ t3 a7 i
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
+ f; [( ^( ?$ F& A3 Z一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列! X0 s0 z4 N+ a1 d: f
在InnoDB中,只有事务提交后才会解锁9 I# x! S! |9 L  L# J' c" R1 D
/ ]: Q6 N* c$ ?+ L0 \: d& ~
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只8 p9 P% U6 k! t5 |; V# y+ [
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
( @1 `. z5 E: G7 |% k% {4 w
" P2 N- }5 \% [, ]' _& MB-TREE
" W* f/ p4 {& ~/ w4 Q能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找1 {; x% R" f5 {. K) y! ?
使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。$ L) o8 A- S4 n5 v
  1. 8 t- f: n8 @, J5 }1 f: c) ]2 _
  2. CREATE TABLE People(* v" c! r& ]+ r: Q
  3. last_name varchar(50)   not  null$ d. c" G& R  I) E  l
  4.           first_name  varchar(50)     not   null' P- w8 a: N1 L3 R( }
  5.           dob  date      not    null
    # L. ^& H& E4 R) Z, g
  6.       gende       enum('m','f')    not    null
    ( f8 j5 `$ Z6 c3 D2 K4 s
  7.         key(last_name,first_name,dob)
复制代码
匹配全名' \' S! ^1 b; L2 y+ i
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
; L, x# V- a6 j8 i, h; [+ \的人。
( ~7 d4 @& c) F, i+ P1 q匹配最左前缀6 |1 B1 k' t$ h
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。5 ?: W% S3 G! G3 g( y. h' |
匹配列前缀3 @# W9 Q" m# p( p/ _3 J; U) ]
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。. ?0 Q1 K+ l. w$ ]$ ^8 c; d5 w
匹配范围值. B0 l0 |% f8 s
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.* o4 r. u; T  r6 [2 [  Z) D" w
精确匹配一部分并且匹配某个范围中的另一部分9 r: [" n* O( @" r* E
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last3 r: n& R) l  N" ~' a
列并且对first name列进行了范囤查询。9 {3 @1 j$ u2 o: i- f
name
% u8 T5 h/ j" \$ \只访问索引的查询, T' ^: P2 ~9 d5 r2 n
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。/ ~5 ^% ^' L) u7 C% v" s
6 }6 m3 p1 K0 b8 X1 H& {9 L
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,2 U& W8 C+ P) e2 h3 p
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
$ P* j1 m, q/ Y6 H# d! }找方式也可以同等地应用于ORDER BY。
' t2 U( c' \+ f9 M" B7 O
' I1 h" x. b4 U  m! Q* ~下面是B-Tree索引的一些局限:* n) x3 h, A: Q3 ]
& W5 E5 s% a4 G8 r* ~9 a( Y
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,( e6 d$ L( _9 s# E$ _
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓, Q- A! z! N6 r7 Q6 b
氏以特定字符结尾的人。4 K* s+ _7 W6 C4 h& J/ ?
7 l) T: `) H: W6 s7 g
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
8 ~$ N1 m. }8 A1 {义first_name列的值,MySQL就只能使用索引的第一列。5 l) h: E; |0 N

, s( W& T% I, o3 ~( ~) N( g6 Z3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是; a) j5 r  ^! v7 i' P; y/ k
范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
4 S; i$ U! `$ B1 Q不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。2 B2 [- S" D" d. Z
' \$ @) {7 e9 U- e( l) b. C9 n
哈希索引,空间索引和全文索引等,暂时没有设计0 J( r9 j. f) j2 C
8 V' j, t5 g5 Z
高性能索引策略0 l% |' y! z7 S7 n
; z1 d' A9 }: r1 X8 Y
1,隔离列,意思就是不要对查询条件中列进行计算等操作( D2 g( G, S) s  p9 d
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引' }/ r/ _) H; o* p: m
Select count(distinct 列) /count(*) from table;
/ s( A3 c: Z5 Z  N# }看看这个值时多少,如0.0312
% F! U+ p% }; N那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算
0 Y- c8 q" w1 c, M) K( m. x,这对于大表很有用。, z. ]( C) N7 v& R5 Z8 a
Select  count(distinct left(列,3)) /count(*)  as  sel1,
) d3 C& j* J4 f4 L: s count(distinct left(列,4)) /count(*)  as  sel1  ,$ H, a/ m) \$ z' Q
count(distinct left(列,5)) /count(*)  as  sel1,
( r9 u4 x) C( E5 c count(distinct left(列,6)) /count(*)  as  sel1,
9 J2 r. X1 P: P+ } count(distinct left(列,7)) /count(*)  as  sel1   from table;; L4 i; N9 G7 L, Y9 G! I) B2 `
找到接近0.0312即可。; A. i8 Z! f, Q# ]% |

6 l8 \- x; T! ]Alter table table_name add key (列(7))
2 e* r- j0 l$ V0 k6 U; ~3,覆盖索引
+ D0 a# a- z9 |. D8 _4 U# L包含或者覆盖所有满足查询的数据索引叫做覆盖索引
: s. q0 |6 [! i9 H9 Pexplain时,extra中的会显示using index
8 L- ~/ Q+ a; x+ m- g% t5 L这里一个重要的原则是
% ], O. u. K+ E! k/ r8 Y- Jselect后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列, s3 h7 }& I  z2 f/ \& B/ A
如select id from table_name;
0 c: V9 |$ H. x0 \
& D; \( J8 ^! ?# ]很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
# r, g+ f" K" Z- C一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
8 z. o5 U& i4 [) C9 [0 w, K  O" x
2 Z9 {: S0 s. m, f
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';$ a/ a0 D" X/ j! S( h- c4 n
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:/ {" [- K; j1 Y6 U
1,
2 h& C6 l% f5 n1 D没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
  R; p( w; t9 b2 A! ~7 ^' a& \2,
1 k9 U- T: `: W0 w/ a# d. J( [MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
& I8 b6 @6 i5 P+ Y有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:& x' Z% L! Y, k6 A3 Y2 m7 s* |1 v
5 v6 Z% w* T# ^- y) A1 U# ~
4,为排序使用索引扫描
2 d: A, Y2 s( L, E& ~mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。$ B& F) z- v" {5 o5 V5 l+ f1 _
explain输出type为index,表示mysql会扫描索引
9 y* {" |1 F3 T5 ]5 v- K' d# h( b% b# Y& f6 N0 @+ A9 P  d& k
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
2 g4 J9 `- |0 I! ?" O& T: u8 H$ O4 h( F# t3 L4 P$ b
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。, G9 B+ T+ R3 X: j4 c$ p, V

8 j( o  D- G5 j$ X按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
7 i4 `* k* Q6 x4 i, J6 J* W2 L1 R
8 d6 k6 \( d+ `; J& S& J2 tORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。" S# e: N" b+ \0 C# N

0 i3 Y. y  ^$ t5 \使用join可能情况会有不同
' _3 p9 `! c( J- J2 x! h' a3 t+ j+ c6 q1 |! a8 X
5,压缩索引(myisam)% E# g& |7 Y$ s
6,多余和重复索引(应该避免)
# p. e+ h: F* }' z* y8 N$ C% {6 ]; P7 u7 K0 n
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
8 L; T1 k- M; {7 ?4 m4 G7 J上有索引,那么另外一个列(A)上的
$ g+ V0 ]5 l& \3 c索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)8 c; j( V# D2 J0 r
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。, Z2 b. u$ g; `- C4 O
0 Y$ r8 F; ~2 d1 Q  J- P
要点:! I: }: h4 t/ r& L
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
& ^& f1 O3 s3 X# t- J  f  {7 ?7 Y( O0 T4 O, L
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
  T/ A8 }+ n8 s% @5 s' d
; _1 K2 P- O1 {, S0 E: d: R5 e
9 p- b$ a) r! R! A; `; \& G& l, |' L/ J
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,
& S1 o+ y# L: h4 S6 [' a+ W+ D  H$ w6 t3 J# Z
拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引  J5 m+ x3 H0 |# O7 l
! Y% i+ Z" A- p1 W2 C( W; d

; i$ Y9 l5 O4 p0 w3 Q/ s4 u* z  T" b4 H9 q. G3 Z  |5 t9 n4 h
一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索
0 g# r% R4 u, |+ Y+ [$ z. }& y4 l$ J4 G5 L2 g! o
引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。! ]6 M1 b' y3 [1 d9 W& N
7 R) D( I( o2 b
0 Y9 N* X( s* s' k7 R7 W
- T+ z3 P* ^; a" m3 I8 h: v/ C9 C3 J
一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。& I% q. w3 ]% J

5 n2 ]& g0 U& C1 ]4 {: |' D7 @例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理! F: W6 A& y+ ^9 H" u  l5 U

4 m* w  G8 R4 k& y这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式
* `& s0 I$ x: p5 r6 b' I( b! q
# ?8 K  \- y2 T( S! X. ]
% p6 y) A: n3 K, L* R# z
; F; d' ~& c9 h7 p避免多个范围条件,只能对其中一个使用索引4 U8 R+ W1 {: _# t, B6 q
: ]: `4 K5 t1 N6 L0 Q
6 y0 Y! Q/ @, `  p2 c/ o; r2 ^

, W& A; z" A( Y7 y! ^索引和表维护: R9 J: T7 N3 _% |  ~
& F4 U4 r/ c6 r( d  _. k( w/ F; ?/ t4 d, c/ M
表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.8 [3 B# r/ Y1 W5 \  c) H
+ L1 [5 k1 C7 C2 N
check table table_name;
0 H3 H9 C* M; r$ e& i5 Drepair table table_name;
8 t' e8 d6 Z- t- m6 u( M$ @$ }4 pShow index from table_name;检查索引的基数性
) }8 b- N0 c" j6 B4 `( k1 r- s3 b5 S5 l6 ?! s! u, b% Q' h- E
主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量
* P7 d" @# X/ j' X. R$ @. \
1 W+ b  \% V6 e: m, O, c. r  y7 O: M
( Y: ~. c, J6 q$ `) e6 g4 ^) }, v( e( U/ t/ H5 l
B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。( r4 s1 r: U' m( [7 |& t) M0 v
- C+ A) S6 M1 A* x1 f3 O" F4 g
表数据也能变成碎片化。两种类型:+ y% n4 r$ d5 N5 a
1 @7 v$ G0 d. @# i4 T/ L6 p6 I  h& l
1,行碎片( l1 j) i" T- x5 g% @
& n2 f6 N+ }' S- `
当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
" [: ~) U+ t; I" @' @9 D) x
7 J0 X0 c$ a: d+ N: Y
7 F5 S1 D" \2 p, R1 b' M
0 m9 e* c1 _  f- [9 W2,内部行碎片
8 T& v4 q6 O% |0 ^) }* ?, X
- ~2 Y& L& W4 _当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和  p# E& P( d6 E3 k; x

( V3 J+ g* O: ~* x聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。5 w/ C+ O* U% u6 `& h; q

" s' O+ V( J6 Z1 ^- y5 y' ~  _3 m; O! Q' C1 D7 }* J$ M$ O

- m; z0 e: e: p+ H为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。
/ A! t! s; a2 @7 A9 m# T
; G" x3 M( I  Z5 @9 \
7 D- t; d& _$ |7 h+ E" W! q/ O. |. k/ x: J! Y- q: q
ALTER TABLE <table> ENGINE=<engine>% \% P: O- `: E6 D' U. Q) }

+ {6 j9 E( C# S" ?, E* `2 |" ^4 `3 T9 {- t( O4 F1 l
9 o' E4 l; ?: \8 ^
加速ALTER TABLE; z. k* L' |" L# m! }6 o$ x

* ]. k9 ?: y9 L9 \. N5 N
/ `, Q! [* @* Q! N' ]3 k5 I  a6 q4 Y; g9 s' }1 _
MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需" T( ]* `0 X  }( Z) S( r' X

; l/ X- J1 z3 N要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,
: r; t# K1 e  i4 t0 J
5 K4 l5 R, g' a) f4 E而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。. Z# _: t7 F2 u: L2 n" |9 a/ ^. C
1 E% ^- V! q; g5 ?& C" _1 b
传统:) |& r, M) f: `! K6 D. r

0 {2 [& w: l! X. xALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;
8 R0 r, Q# _* T& {6 i; h& h理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更
+ M& L$ }# E4 g" X" ^% K改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。
' W! \0 z. J7 D! n4 }1 u  U/ N
1 Q; ?; [, y: z: V) n3 N6 T1 d9 q9 x变化:) S0 N- N2 l* i2 k
; L; ~# _- Q: s( e# N
ALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;1 V0 {0 h2 r8 ~& A" A5 n
这个命令更改了.frm文件并且没有改动表。它非常快。0 l, `4 ?: _" Z( z5 u/ @
还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-12-16 20:18 , Processed in 0.034811 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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