召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 1429|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能0 r" _9 E9 i( F1 u# O1 {
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
& }, h4 T2 b& K' N& o一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列# u. }8 i; ]" }: ?+ O* A) S
在InnoDB中,只有事务提交后才会解锁
2 c1 o: G% G& h
/ [4 ^- b6 t0 v2 u; O索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只* t' Z8 X; C1 ]9 }
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。; C/ c( D& N! R7 l

3 f7 W7 T! j5 n& w3 Y3 ?8 e$ a2 wB-TREE
; ]& R! M, g8 U0 `! n( t  S能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找: O6 E: S3 }, r9 M0 j
使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。' j7 \: V& A2 ?

  1. ; n1 z( h$ \, G8 S
  2. CREATE TABLE People(! }. R4 S5 I  A/ c
  3. last_name varchar(50)   not  null
    4 r- ?& v; I5 c/ ~6 @4 N
  4.           first_name  varchar(50)     not   null0 v' u  q6 c4 }& |6 I& u* }$ ]1 I0 H
  5.           dob  date      not    null
    - s- s2 b' W% U
  6.       gende       enum('m','f')    not    null
    9 F: S$ g  a+ H
  7.         key(last_name,first_name,dob)
复制代码
匹配全名
2 u; d. A# G# P4 _; `% l, ~& ~全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
& Y5 r& n) G: c2 V( t的人。$ i$ o$ w1 a$ U% K4 g# T
匹配最左前缀0 k, \8 B- b1 N5 Y$ @2 C, G
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。5 S% I  A! Z/ E0 C! K8 g
匹配列前缀5 u9 m+ G: ]5 {, d
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
$ w# u. x( N. J7 e9 X# y; {匹配范围值# P6 `. ^! y  H! o/ R
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.
# U) L* Z) e8 L0 c5 c  u! |精确匹配一部分并且匹配某个范围中的另一部分
+ D# m9 @; \+ N. J8 n. ^这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
+ l* u& A" h& d列并且对first name列进行了范囤查询。
. Z7 V8 U6 W' `+ [! V8 t. Cname# X2 z! X0 ~* ^0 a
只访问索引的查询
+ n# ~) B' p: \' [5 {4 l0 d' h* dB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
3 B: i  z- ^# k% l" M& s2 k9 A: k
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,) o5 ]" B" @) W
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
! D- q3 f% `4 p3 J找方式也可以同等地应用于ORDER BY。& K1 u1 T8 o8 y( `& w, Z: \

' p, i6 C8 s% c下面是B-Tree索引的一些局限:
- d4 o7 I" k) X4 o  a
# S, A6 r1 i8 M, ^) a/ e1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
3 b  |4 s! N2 x3 [" @也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓
; ]  ^" h; s$ _" h) C氏以特定字符结尾的人。
/ l1 q8 I, w* q7 e! Q- N/ ^  Q6 I( s! p4 S, V' M8 ^
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
( _$ Q/ M4 J! l; A: g& B义first_name列的值,MySQL就只能使用索引的第一列。5 g! C- \. U+ w* W' x, E+ I& P
' h4 h( r9 h+ m7 ]5 _, H: _
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是0 J  l! q9 `+ I7 X
范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而5 k+ z) F' ?' i  C" z" z& v6 [& g' s
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
7 f- G% X; ~1 k+ W; u) u9 o& W. U
; \. Y$ H" A& ]9 n0 g9 ^1 \) X哈希索引,空间索引和全文索引等,暂时没有设计  w3 x* G% Z  Z( q

# Q" D7 E" F0 v) M) e& o! }高性能索引策略1 I6 O+ f4 ~: B7 w9 q& V
" t9 B9 n& b2 {0 l
1,隔离列,意思就是不要对查询条件中列进行计算等操作
  D' e! {' F; {2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引, K, e1 e9 A( m; H& V7 }
Select count(distinct 列) /count(*) from table;, s8 b4 ~) r& p$ J
看看这个值时多少,如0.0312
# C/ M9 l5 D9 \那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算
& h% N0 j7 o, S4 I+ G3 M,这对于大表很有用。
2 f. ^; N- S. w7 h3 SSelect  count(distinct left(列,3)) /count(*)  as  sel1,
$ g+ B: \! `: k count(distinct left(列,4)) /count(*)  as  sel1  ,( _1 _1 X# {/ y8 v5 g* n$ G7 @
count(distinct left(列,5)) /count(*)  as  sel1,, s6 u: j" m9 J* g( W1 I4 d4 M
count(distinct left(列,6)) /count(*)  as  sel1,1 D" c2 t+ |  l
count(distinct left(列,7)) /count(*)  as  sel1   from table;
8 e9 ^" s5 F: {9 ~: ?: ]找到接近0.0312即可。
, l+ X& v2 N6 V/ T. a1 h; @. U2 E6 P' l( B' H8 V
Alter table table_name add key (列(7))
. V2 e6 ?/ n& E/ ?3,覆盖索引! d5 Q* d, Z9 a) z' e  J* A7 M" [' e
包含或者覆盖所有满足查询的数据索引叫做覆盖索引
0 H( T4 k) I3 R5 {* ^. g0 Hexplain时,extra中的会显示using index, ~( s8 i" @6 T7 A
这里一个重要的原则是6 o8 C* y" i, p- Y! q# v- ?
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列3 U% M7 [$ S9 X1 [7 T8 P5 `+ u9 F9 A$ C
如select id from table_name;
. W' {; {" h) Z
( {, _6 A$ Z! f/ W2 A9 \" k* v很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
7 _/ [& [8 F# ?4 n8 E8 c一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。0 s5 x) _# ?1 h

) l; b* ^3 C1 R+ L" q
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';/ |. Q: A! ^/ B) c$ l6 K
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:' J; G% X) L- {, V7 i9 H
1,' C, M6 R+ ^* P
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
0 c/ Q' x$ x& O  s2,
0 g) j& `: m% [0 HMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
: E0 N4 _% m+ I+ i5 k7 s0 A有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:
& S5 i) ^. o* s6 S5 _+ \; S1 X
: ]+ |5 H; ]2 b) m  e4,为排序使用索引扫描
+ Y1 A" f7 t- I3 E+ d, b' Gmysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
3 n* d, n3 X: k6 B# L. f# @' uexplain输出type为index,表示mysql会扫描索引2 C) ?; T, y1 |, O9 X6 _

( I( X) E( ]" N" d% N/ r扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
. u3 N7 r  B5 x+ H$ _
6 h3 h* j. K6 {- G% }$ [" |MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
& x/ @% j! M# @9 o" X) N( Z% y/ V" ]  y" j
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
9 j/ v' F2 @6 @' q' e& F6 }
" s: v+ l: \9 {3 T+ M2 oORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。; o% M2 p4 Z& e5 F6 y

3 J/ B; X! S7 h$ j9 S5 r2 a使用join可能情况会有不同+ x% \4 l8 U1 g' G7 z$ F

) O# N6 k* q- i4 Q/ J: ?. W5,压缩索引(myisam)
8 W2 _% f7 P* K: Q6 y, p6,多余和重复索引(应该避免)
. v' A8 ^) Q* j" K- K; C; i# n. G6 j& Q% a; ]
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
' C- W* f' F! ~上有索引,那么另外一个列(A)上的. y; w1 ?' E% @+ j& H3 ]8 \2 ~
索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
- x7 z: O* P0 ~4 J5 U然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。/ u' R4 P/ ^" Q+ @" Z6 ~

- k1 f9 b9 {$ i3 G3 `8 e要点:( U+ |) k1 P& z. Q2 ]
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
0 V6 N  ?' {1 ?2 K1 Q( @% {
2 z+ C8 f4 N+ i即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
& k8 y/ w* _- \* l# b+ d7 A5 _) k6 P5 R$ P

: o( `) E) Y0 @  ?4 F- K4 _8 f2 |! _) a! C6 [  r
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,
) h7 C% C9 h5 M
! N: g- C) L# q) I: S% A拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引
) s- T' {# v3 |8 _7 L6 i, H
$ O2 A$ r+ s; b9 D* M' A) ~* {
: u' R5 l* Q" ~
: F" o0 W  U& A一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索# N# S" `  ]+ t/ ^& Q" S
. t9 @) i3 r. s+ ]( @
引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。9 b. V/ j  ~+ G2 U4 M

2 m6 X4 y$ K8 j5 s
# W: a) G( ?3 a! [; ^/ ]% P; d1 o! h9 w1 P4 z6 z' R& \' J
一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。) Q2 L% m9 ?# o5 x: |  @
' _% C- E% T, L; G! E9 |% I
例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理
; ^" h1 P! Y' B
! A7 ?; w& l& H  ]* o2 L) D这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式
$ D, E- X" E& m, q- {+ c) S) K8 }9 T+ M2 Z9 f

4 F8 b3 C3 L) r0 q' I9 S( w3 ]" S: g% ?. X) }# i
避免多个范围条件,只能对其中一个使用索引3 k, d5 `6 A) I: ?

$ g4 H  h, e6 y& ~0 S1 ]
# Y  }% h: `5 _& D8 C8 k4 L3 ~8 T4 H) W4 E( K2 U7 o8 i0 n
索引和表维护
" |$ S) K( H5 o9 w( [: m; B; l9 @; n4 b  L9 l" y+ O
表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.; q( L; F" a% [6 s8 d
! X$ B: D# K6 m, f. y
check table table_name;; ^* M! N0 ^0 X, }& Q; l' l
repair table table_name;/ N& a8 s; Y2 j- s
Show index from table_name;检查索引的基数性! G1 b& D, _, w+ X) }; `5 j
5 u! F8 Z* p5 d2 e) |; }
主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量
) i- P- N6 C- p( p) T1 D" C3 m5 c$ ?2 U
* ]6 D( K2 a  ~
: T6 y+ [& @: Z
B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。' _% ^3 v) r( q) X! z

8 J& J7 h' ^2 V5 m表数据也能变成碎片化。两种类型:* {5 V2 y% {: }  O

, a  y, Z  ^9 \$ R- H+ P# f1 n1,行碎片
' r  ~/ F% m: [2 u4 }0 y$ P$ t- p: R3 X
当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
% k5 v0 M7 E5 B& z" X8 c
0 H0 A  b7 `' f% u
7 x/ W+ X; [2 v0 w+ D: \. z8 P& _+ \& b7 E6 a' b
2,内部行碎片; n. S0 \( x% ]4 t

( Q/ p1 j; ?5 N6 W7 y7 Z当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和  d/ a4 A2 q& h# e& ~7 n
( u9 J, X; t6 v4 Y( J% @
聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。
. }) t% i" _; @* ?9 W2 P
1 S0 d0 ?0 t& J/ m) J+ w# V
; t  h% q  X% ~0 _/ i  w( D/ D, P
为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。0 ?) v0 D$ V. X0 r; m

8 k/ y# r. @4 |7 X& j" i, m7 ], T2 ^

% u2 [2 S, M/ M- sALTER TABLE <table> ENGINE=<engine>
" s+ h5 x% q% z- i  [# ]
% W  d% M! g: G9 C: L/ }+ E0 O1 \  T' @+ @% B5 v
& C, w5 \  k& O2 x( U$ Y! w0 T
加速ALTER TABLE. m% Q3 g. f9 n4 Z0 h$ e% z

0 o1 M  O3 e( M+ |# L2 {7 s% O* k- ^+ `, Z1 Q' ]: j) Y  [* o5 }
; a- O, W, S# a) q- }% s
MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需/ b( X1 F- z+ k! i6 J$ T

% U* `5 p; j% S4 T  w7 [要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,
+ Q  i2 K4 w  L+ P- ]/ _- D4 W
5 y: v8 W3 o# Y" b而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。" x7 e8 \) ~' Y1 G$ n8 d+ @' `
# g  ]/ U; X% Q
传统:
) j0 r' f" p. L  q
- [/ b6 x: `9 B0 J$ C- B7 r' uALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;
% R8 l+ ?7 ]5 x9 M* z, r理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更
/ q1 U. B) A) A! H改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。
* x2 \# T# }& I, s# S, S% l
7 ?' c5 `7 d. A" ^变化:5 f3 T: `6 G& C; J. k" D

: o5 u4 c+ {0 Z( T$ N6 sALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;8 V8 @2 x* x* I, w% ^
这个命令更改了.frm文件并且没有改动表。它非常快。- A8 F2 v2 L6 h6 H2 c
还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-6-2 03:10 , Processed in 0.031628 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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