召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2803|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
, h% }! y; Z* _# L. M  o有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成7 h5 t: @' ^1 W* v0 H! W$ O
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列+ B. G% R4 \4 l. O/ Q! Q  u3 ]
在InnoDB中,只有事务提交后才会解锁
. D& X6 F8 p9 t8 U6 c4 W1 u, `7 m2 h4 d5 s* @9 I8 G
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只$ ^$ i6 X$ o$ k( t( d% Q
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
% T" r4 h- ?. l7 {; T4 X" X7 L& t/ D; o4 S7 G% I& _$ O! [
B-TREE7 H0 ~6 U* ^) I1 A  w  {, W: r
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找2 X' E+ |4 I% n( _! K
使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。
3 N4 h# M8 w0 z

  1. : q; L# o1 }7 N! j& M7 y
  2. CREATE TABLE People(
    - r. D; Q: L. T' ^
  3. last_name varchar(50)   not  null
      Q& x: @! X( m2 s8 G
  4.           first_name  varchar(50)     not   null3 @' k/ @5 \( ?+ {' U' w
  5.           dob  date      not    null: j" H2 A  l& ^: t
  6.       gende       enum('m','f')    not    null
    / A# L$ m0 w3 ?7 ?8 J
  7.         key(last_name,first_name,dob)
复制代码
匹配全名! D4 L4 f& Y3 _
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
' ^8 J$ c$ Z( u) f( L的人。2 k& D2 x$ C! ]! P
匹配最左前缀) d& W! l1 f! y/ J
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。5 W, p6 I/ h4 P7 U- u" y
匹配列前缀
  P2 t& k1 O% m. j可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。7 G$ v% l% G1 L  D
匹配范围值, `- s4 W1 X8 x7 G, e
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.$ W9 X! C+ S4 r2 }, K( p
精确匹配一部分并且匹配某个范围中的另一部分$ K& \( \% n/ \& x: Z* i
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
+ J) [, F: ~1 {1 ]" B) }列并且对first name列进行了范囤查询。
: L5 V7 }. D# h/ G' |name
6 N3 \+ Q9 l# M5 ]$ O只访问索引的查询
: e: L; i: ?& {1 w, V  GB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。1 Y0 I) j0 h1 r2 [9 \6 k4 @/ c% i
% q- x1 i8 x2 t' V0 M% f
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,4 A$ [. D4 Y( T! y2 ]( U
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查( h( x& N7 K; ~+ `4 a, }
找方式也可以同等地应用于ORDER BY。; L0 \5 d( G: v2 ?, q2 h

) t. @0 @7 W- ]& ?& ^下面是B-Tree索引的一些局限:
* }3 n+ T- L! t, P& B! n+ u
: R8 `' b4 g8 Q( j* w$ {# q$ U4 n1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
% x. q+ N: t6 B也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓  d& `' |& m" y$ G2 g/ R9 s
氏以特定字符结尾的人。4 P+ `+ A" ~3 b- R3 ]# N) ]2 ]: c
5 y4 v. H, Y* {1 u" v  x
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定/ u- k! J# l8 Y& t) u( Y
义first_name列的值,MySQL就只能使用索引的第一列。2 o8 A% G9 f8 S$ W% d" E
. }3 O# Z! z7 P6 S/ Z2 W5 H
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
: z2 s/ c6 j' N$ m2 I* W范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而- f& ~8 N) r5 K
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
' {  d% v1 G! X, V3 k: i1 O
9 l0 v1 w3 w; ~# Q. F哈希索引,空间索引和全文索引等,暂时没有设计
; I0 C, [( `9 H; ?4 A: ?. s+ ~6 Y& j
高性能索引策略
1 [! a# }* p" [) v8 @/ S( B. r3 A
: }( }- v3 ?- e* v3 G% o: l; d1,隔离列,意思就是不要对查询条件中列进行计算等操作
# h. }5 @$ z) D  L# l2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引/ }; L: i! w% W
Select count(distinct 列) /count(*) from table;. {0 B1 \$ K7 O) g
看看这个值时多少,如0.0312
3 F* \8 k' O0 x' K" _' C9 |1 F% x: h那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算: K3 g# B- `2 ?- p
,这对于大表很有用。
! V# E( S* g2 J( ZSelect  count(distinct left(列,3)) /count(*)  as  sel1,
1 K- R7 {! ~1 E, v# g9 V& M count(distinct left(列,4)) /count(*)  as  sel1  ,! o8 l+ ^4 r' [) X
count(distinct left(列,5)) /count(*)  as  sel1," B! V: ?4 _3 x+ i( v2 Q
count(distinct left(列,6)) /count(*)  as  sel1,
1 q+ b. d- G5 ~0 a. e6 f+ U count(distinct left(列,7)) /count(*)  as  sel1   from table;# V* E+ I/ a: ^4 v
找到接近0.0312即可。
* Q+ C6 A1 |# y1 z5 o- q& n8 K. W0 ^9 V& o7 Y
Alter table table_name add key (列(7)): X; ~' O0 R0 z% u
3,覆盖索引
8 m5 u9 _3 x1 s% y包含或者覆盖所有满足查询的数据索引叫做覆盖索引
8 a5 u2 o: c2 B( W* x( V6 L9 yexplain时,extra中的会显示using index
/ U2 B3 z# B) w! e9 x. ?这里一个重要的原则是6 W9 O" Q0 n) G  x% {" f' A
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
2 Z+ q1 E5 ~7 `% D. G- [7 c如select id from table_name;0 I' X- o$ Y  W3 ^

! i+ D: h, I" {; z4 R很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不9 q& {0 d# ~0 ]: M+ A: f
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
. G# F1 m/ q9 m; b3 Z0 e# u& B- T
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';
    , o" ?! O; b; T: c2 ?; \
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:
& T$ T, w' U6 K9 k* N1,6 E2 H) `# O/ f. r, i
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。% k/ w2 _% O9 G
2,$ D; {' r* I3 z! F
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。/ ?$ D' ~1 T1 d: y9 T4 P! }+ G
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:
+ u7 ~6 g  c- X
! W, _. t$ N0 d4 k% h* |2 T4,为排序使用索引扫描
# ~& I0 D3 c6 M0 F/ N5 V0 s' ]mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。  o. e& @' E& e* y9 [5 `
explain输出type为index,表示mysql会扫描索引
( ]# {' g2 r3 }( W- Z9 C" T* e& W, U& `6 G" o4 X. ^/ u  b5 `
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.+ U# j3 p( ^' E7 a

7 Y& m4 N1 N; yMySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
0 r' g; ~8 d3 j6 s6 X$ ?+ D  @  C9 }9 Y+ C4 c+ r2 D2 y9 Q8 D2 v) P
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
+ F1 T1 q8 Y, }6 J- {  _5 ]; q  I: t- T6 N
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。1 s: z3 x, s' |! u" ^5 C

  Y' F0 R3 T6 U使用join可能情况会有不同' ?  a/ @: A$ f) |- e& |

& x) d2 D/ [/ |& T5,压缩索引(myisam)
# c1 d5 K9 y# w+ ?6,多余和重复索引(应该避免)
6 k: O! _7 D) b0 K0 w
2 S% Q4 l2 G: y8 Z/ O2 G多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)* J, ]8 o3 q2 a$ }* d' P4 n0 ^6 K' i
上有索引,那么另外一个列(A)上的
$ h# T( }6 b- |# I索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
7 O2 ~& V# B% q- R: ^: D然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。8 F6 ]) X& h' f& Q" [" A$ o- P. W
( P- _( v$ s0 Z3 j& L1 K
要点:
; G& ]9 n' N9 @) _2 A# ?4 s在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.  Y$ `+ x. W0 ]1 D, U

7 \5 @( O- \( E+ o' @即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
/ U' [0 Y0 e1 |# C
& q; M1 L/ K( v" a9 d' M& P: B2 r) k8 Q" x6 P0 j
  ^+ B1 p  B) U; m
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,
/ a0 R" j  H- u$ `
9 p- \* j1 H( L% M3 a, V0 _拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引
6 {5 ?) e" |$ q) s: p+ x0 G. ?7 t# f( {; J

. T/ Q1 C6 ?1 m8 n2 w" _0 t# P3 U+ N3 Q+ N
一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索2 h% q9 f" \3 O; E& H1 j

! }, m' \4 C$ D2 C引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。
1 x! S6 u( ?8 D( y) ^, A' ~* U: ^6 N3 B0 g4 L1 R+ F# i

, O9 x: z# t. N# N! N4 V0 a1 w/ d
! T- q5 e- K+ i7 i一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。! o$ P. ?, j3 H0 r1 m/ `0 x
7 f! l3 _* f0 S2 s9 \
例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理9 D: C8 S- e/ C9 F7 _1 h  C1 e
: p8 S" x' V, ?. \1 p
这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式' Q3 [5 A. w+ M9 D
' Z( l" Z$ ?0 E

' a  a& W- Z) ]* f/ }3 d$ b9 C. q/ ]
避免多个范围条件,只能对其中一个使用索引
2 v* [8 n! ?% V* k  i/ d# s" B8 p# w! d% z* o
" H( N; T$ B3 H* z
  Z! A8 [  I3 J
索引和表维护4 t: P  q! ?! M* C  ]$ V
$ N- x) U! w$ I9 w) R
表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.
6 q3 {) o- Z* [0 c
8 ~$ i5 f: I1 F/ {6 c" Z# echeck table table_name;
$ w2 p* O2 f4 x  H; `. Yrepair table table_name;
0 F% D0 _4 m0 S. v! IShow index from table_name;检查索引的基数性2 U* h& b) R/ s) C

* C3 v" A& A8 P0 v主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量3 j+ `* d8 S/ Y1 V7 ]& ]$ w
0 P  g  v4 D" e0 c" f
1 u( Q0 p: o; B& G; Q  x) d7 @

7 @2 `8 I# e( ?9 R2 k" F1 @& eB-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。
8 A  ^2 N6 n# f: g2 T
" ?2 \# F. l. n+ Y! I; h表数据也能变成碎片化。两种类型:: N) Y8 P. Z: {5 L, ~1 T0 K

& N4 Z. H4 _/ O- T. D1,行碎片$ f6 j  T* a* S; S, w0 h
  n& M" a7 ]" O9 T% v, v
当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
& W$ `: U4 \& l& ?8 G; ^7 N
$ b& Y' K) z; H! g5 f; f7 U
; i6 i8 q8 \" w6 b- O* b- P: R& }& L
2,内部行碎片
. _0 w9 w; q  s' x# y! b; b  n
0 k3 z7 l; n/ R, k0 ^当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和
$ n% c8 [9 r! t" `
' q1 r8 g& A2 H% t5 D聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。/ g6 t  y$ K* A& c  {4 H0 B2 Y
" }, x) @+ L8 {6 N( m+ i* b' J- y. R1 P
1 I; ^7 R9 T# t5 D# \
( V7 v  Y5 d- {" h$ l& i
为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。5 p; {' D% p7 \4 i3 R3 T% k

3 T7 r) J4 w# Z: k- l$ k1 w$ q
5 a, O: T5 R7 {& n# `! v) a
! C# p4 H5 \+ q" J6 i5 E, fALTER TABLE <table> ENGINE=<engine>
- C5 D/ \  @8 Y3 k; z4 o7 Y& v
7 p/ E0 ^& t& g3 f; x: e+ h7 U& X  V( T& ?

3 Y# J& G) P/ X' [加速ALTER TABLE5 Q( J6 p; m2 \0 D3 N; E- I
, I7 n0 G" S) N; n; q" ~
7 ^, N' b- X+ ^6 p; d% \
+ F  u& P+ R4 N6 w  Z- ]( p
MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需
1 y* i1 Y5 l& {" V, W
7 A5 ]( ^/ y% d5 s7 A要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,
4 M' X: x; Y3 h
$ a' r1 N7 L) P) D# h  I而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。
  O' a- s  s: w% j) w) _% O' n
+ g" o# I6 Z1 @8 ?  ~传统:
; K# x) H$ V7 |3 ^3 O
  O" a+ ]4 l$ }3 J& G9 |* aALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;
  e# b! S1 n+ H* P+ a理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更
3 `; C; A" _+ O9 Z& l/ W& L& i! Q2 B改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。: _4 n6 H' H9 D  M/ \4 o! Z' j* K( M

' L" t5 f7 M7 O; \. y. b" U0 I变化:
/ W& a4 P5 I- B8 A1 V, l2 h' r/ w8 S! ^1 k- O
ALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;
/ g5 H8 ^* ]6 b$ ]: J% {! O6 b这个命令更改了.frm文件并且没有改动表。它非常快。+ H7 F. t% j7 M, r# [' K
还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2026-1-30 17:25 , Processed in 0.038869 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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