召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2846|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
9 J5 v. p: y7 U" c有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成+ ]1 X$ ~) U0 K# c. g; J2 l
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列4 C% P5 h. {; S0 F
在InnoDB中,只有事务提交后才会解锁5 m: }, p* `9 V9 K! p+ `

7 V  s8 @- j5 ~$ p9 D; ^索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只: T6 i6 a( X0 A  r0 l/ }2 t; f
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。' \+ D* {% j5 r, o5 l9 U

4 H" Z5 v! r0 A5 W# X' l( cB-TREE
  r- N# k: d" Q( E" _能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
/ @1 Z* f2 a; V5 l$ @8 Z+ t+ [# t使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。- R( t. |) j* i
  1. & Q+ a2 W' y, I7 W9 M. G8 f& W
  2. CREATE TABLE People(8 s4 v$ C, P3 G8 A- r- p; @
  3. last_name varchar(50)   not  null& U" ^* |0 G9 ]9 s% L4 l) J8 R- k
  4.           first_name  varchar(50)     not   null. B, ~. l( D* a  r8 G9 _' I
  5.           dob  date      not    null
    ! E( b( N/ Y$ m4 l9 Y0 P
  6.       gende       enum('m','f')    not    null
    ) e- T/ j. t2 \" g- E/ k( |
  7.         key(last_name,first_name,dob)
复制代码
匹配全名# v; j) g( C! {* _+ Q8 J9 g% m6 a2 R
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
6 F. Z* Z6 g  b: P' t9 j- Q的人。8 ^+ t% w' I) J& o
匹配最左前缀
9 |2 o7 B9 u/ ], e, @) K6 T) AB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。& b/ R! f% D+ Q) B4 W8 o9 Y
匹配列前缀
" x/ B2 L& _' v# ?9 Y5 C; |( \可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
9 }: }* I* H: G匹配范围值
% R/ J& w# h% U( k" Q这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.: p( ^  w0 G  K2 j' e  @% r
精确匹配一部分并且匹配某个范围中的另一部分3 k4 V+ J" W" V. k) M, j
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last# M" Z7 ]5 J9 {% n) B
列并且对first name列进行了范囤查询。
- J: B% p1 y1 \$ J! t. ename
8 _. s# ?6 Y. J: j6 Z& {只访问索引的查询
* m! }; p+ G( [; @+ Q& d. J; WB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。( h# P& o+ S% i9 Q, X( l) m: T5 M
* M. p# _0 o7 B
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,; y8 Q1 b% u* k% a
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
5 [2 t1 }7 n# ]0 ?: o9 w/ s找方式也可以同等地应用于ORDER BY。
& c# `% p  d0 y9 \/ _
* a9 |1 L9 F4 t/ @下面是B-Tree索引的一些局限:
  i9 k5 H: ?! P: e: s( _
* A0 E7 g9 b- \7 b) I- L; `+ c1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,& S' @/ k) F$ V) ~
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓/ y  \3 q) e# V! W0 K
氏以特定字符结尾的人。: \/ p- P+ R; }$ b( ?, v
9 P4 ^  H5 ^) `% [* m5 m1 \
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定. h. u: a, V1 {9 f! Y3 m+ r( F
义first_name列的值,MySQL就只能使用索引的第一列。
$ O2 ~, o  }/ a: a- g) f- ^. {6 u# m0 J9 R3 w
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
. |  U2 ]! W6 h范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
9 r7 @) \% s" ~不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。2 [4 T7 k8 j, t0 }# T+ B5 S

9 n! u& m3 i1 U: r: c2 r5 \  K哈希索引,空间索引和全文索引等,暂时没有设计4 w! D3 ^8 l- u4 ?2 c* R7 `
5 h3 j# S: Z8 ?% L! ]# S
高性能索引策略( _7 I# t6 \$ k6 F

% g" d: S  N* F0 l, ]- F9 s3 P1,隔离列,意思就是不要对查询条件中列进行计算等操作( j  W3 S3 }! z+ C& y
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引- O) `1 f4 [( H4 W
Select count(distinct 列) /count(*) from table;
$ S! D; j% D( W看看这个值时多少,如0.0312
2 w9 R- r" X* y: D1 M那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算
3 l5 ~  @4 z( H,这对于大表很有用。9 S: R5 y/ M; ]# u2 i
Select  count(distinct left(列,3)) /count(*)  as  sel1,0 Y3 i- r$ L, J3 @6 \8 K
count(distinct left(列,4)) /count(*)  as  sel1  ,
- S( y( {, q2 _ count(distinct left(列,5)) /count(*)  as  sel1,
" h6 @1 m5 U' U5 G% } count(distinct left(列,6)) /count(*)  as  sel1,$ a6 z1 S5 K& t
count(distinct left(列,7)) /count(*)  as  sel1   from table;
- c. T# F9 U7 W' {找到接近0.0312即可。
  s3 A3 z# S0 K  \  b/ X9 z7 X- i9 m" K7 g3 T
Alter table table_name add key (列(7))/ C, L4 |) J& X0 I
3,覆盖索引
/ s  Q! D0 K  l包含或者覆盖所有满足查询的数据索引叫做覆盖索引7 g) K4 }) b; p% x% q) D
explain时,extra中的会显示using index
4 L. X6 Q+ Z  r7 A0 l* y. j: @这里一个重要的原则是, t$ l; c2 O2 E$ j
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
+ r! a2 m1 X0 j' u/ g1 v6 g4 [. A如select id from table_name;  E# D; {9 ~' J$ p9 j. Q2 o, s

, ]; d% P8 y1 _1 ^' M* T1 k( ]3 ?很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
" m0 c! B* u5 n% w" B3 b一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
- @3 y$ w+ z# ~7 {) }1 F& e. v8 O' |. W
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';" b' F9 A+ q4 P& {( F" M2 W; A
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:
0 U. z  H2 c2 `3 Y/ B1,
: Y* b9 {9 N0 J  ]  A没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。/ i, g9 b0 U: ?( o7 X
2,+ f* K* ~1 V7 M" O9 ~; ]
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
5 m1 @5 s! W2 C( o9 n5 k+ N! |! B5 S. Z有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:* Y( M- o7 r2 ?  Q% i
" k/ X' [- H0 q  |. x: A4 B& V. `% Z
4,为排序使用索引扫描
$ c$ d) d! C- T9 q( K7 q/ c' \mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
9 L" z$ L, y( _+ lexplain输出type为index,表示mysql会扫描索引4 |- t! q$ g1 n( x+ j5 b9 O7 U3 w

9 W+ j. L* l# F扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
1 ]' I3 b5 N0 p" B: r6 a: H% y
5 c* m$ J/ I3 j3 c: e7 CMySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。* }4 I+ t/ I2 l" \# G% h$ x
3 Z( E7 H% |# R1 _2 u, c  d
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。3 P4 N4 }$ R, B: [1 g" u

$ S# c% C0 X* k2 L6 Y3 KORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。4 Z) c/ q. u$ U3 ]% {- z
2 ~# m2 L: Q* V7 z$ E
使用join可能情况会有不同$ y3 j0 s9 s' z% u; g% L5 m

2 [* E3 X  E) I# w" ^8 y6 X5,压缩索引(myisam)
3 N0 E$ {7 j$ `6 ~6,多余和重复索引(应该避免)
& Q( x; G2 `) v1 k5 S7 r  G: h. H! |. f2 ]
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)( L- i, q0 r5 T0 `$ G# M3 B# f: P
上有索引,那么另外一个列(A)上的
. p% A% R# f! P# B索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
; |3 k, d; O5 d3 J; K然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。) w6 ~# F& [9 ?$ d0 {) V# e
. a4 U; N1 x' q9 Q
要点:
! ^' F" L4 I  L9 I; v6 O在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
0 C$ s( X5 ?; H# W' Q+ A' Q! {) W% Z/ n% a6 i
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
* \& Z9 i. S+ A0 L$ F; h) f
6 a4 |4 X2 Z7 y/ |7 c+ {
/ y  Y3 K# h" Q* H; u1 L& m$ _- a* J3 r% w( o4 |) ?2 A8 I% g* [9 M/ O( D, K
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,$ G! U7 x6 \4 X( ?5 h
7 h7 P1 ~. C1 I% q/ r6 H
拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引
8 j; X/ ~6 M$ p; u  \
  ?8 T6 P) Q4 I- ~( F' j: s
) \) U# q, f+ v
$ i! M& B  x2 K( r' ?一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索
) ?0 K4 j3 @$ Y9 O& J5 B! R* d9 @$ {
引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。% t  g, ]* G- R: {  |6 |$ X4 a

3 v2 e5 ^7 C: [6 y( u; k3 \' M3 m) M2 ]* A# C  n+ [) I7 @1 p& m, |
/ I8 {" x& C8 v; v. d1 I% ]8 M( ^
一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。& R( |5 g9 ]( D2 t7 h+ f

' I/ m8 G$ ?) O0 G+ J. U例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理' Y3 o% @2 `& a+ b; Y& @# S
7 W( h' S5 H% \! D/ }7 g
这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式7 B% ^9 H) O0 l8 {5 \
4 J& v* v8 G1 D7 r9 ^& n$ q. Q

" `2 [* ]  z1 |" @- C
& b& w3 P7 x( e+ A避免多个范围条件,只能对其中一个使用索引/ @+ N, k( a5 q5 ~' j6 W" p

- x/ l2 e9 Z+ G- W1 e9 P2 g+ v' G  H
# z4 E9 e0 T7 \
索引和表维护2 w0 y1 E6 N# W& Z4 [2 R

4 E  x1 q' Z" D2 \! G( g- q" t- C表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.
. W5 p2 f; z4 k) r7 {' Y4 V9 c5 ~
check table table_name;5 ^6 A- d& H3 W
repair table table_name;
$ u% R: L% h' q; JShow index from table_name;检查索引的基数性
2 \! l; F1 X2 q  Y1 b$ T% {8 y, z. C& q: i6 X) u9 \" h
主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量* G. M, j- r3 v) K6 W
) o8 i, X. a0 P+ ^  a* P
- B: A! S. `5 ]9 d* T
' {, z7 e1 O8 s" k
B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。
$ ?$ v* ]6 }) T! o, n7 h. R
8 F* ^2 @8 a% z1 S  `3 {表数据也能变成碎片化。两种类型:
7 y' a4 ~# B, H2 }6 T7 |" P8 d# x) c
1,行碎片
  L2 x6 j) z% j& ^- E  s; L5 K0 c% @( {1 ^+ v* N( y3 m
当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
% q/ y4 ?2 P: c9 T5 X2 b# x& u6 K& A* {1 K4 e7 @9 _3 i0 f6 M2 C

9 \$ f  ?; [# B4 u$ u7 _2 z: ~8 b* Y" c/ I6 F& J7 W$ T% @4 w
2,内部行碎片
1 i- \4 w4 e2 z( ?" ?0 l
9 n% ]) ~+ G% }+ ^9 }' q+ w/ \+ q; D6 a当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和
+ D" n$ Z- h4 M5 f3 E3 Y' a9 c' _$ l7 C! d2 m
聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。- z$ f& Q. z* @. i
/ k- Y. o  ?) m/ g8 q( ?

0 V& r0 [) m4 N, D$ u$ U2 o+ }
! ?5 F8 e1 S, x+ v' f为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。9 T0 J- E+ @* J4 O8 d, k

8 U  v7 c& I/ d. j
7 t# D0 Y, g. C: [) |5 i, Q" o/ H, [8 c6 c* Y9 l5 |% e6 `
ALTER TABLE <table> ENGINE=<engine>& t$ [. ]  D7 B# z  t4 x) M

3 i5 U" n' ~0 ?7 }9 |) z2 j* x( I  E: @: [" P* m

# i) A7 o  c' i# }5 L0 T: n加速ALTER TABLE) F' G9 M! M1 N7 _" G( W+ b/ `$ {

# _. c. |4 D3 F& _( R
% z9 W- _* J5 F2 @2 b7 M/ m* B* J- S+ A( j/ x- F; r: Y- Q
MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需0 i; k" V+ U8 P$ q0 s

! }0 C- ]1 N9 L7 H, M* x要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张," O6 f: h2 N9 H. J7 ?' t- k

& B' O9 ~1 q* }+ t" K而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。( C+ Y, F' G/ }* r
& V2 N: ]7 m$ C  ?" z; _6 @
传统:
. P* e- F' z$ n% {4 ?3 C' ~4 k' t  Y' G
ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;# F  |% B# V: V5 i8 r/ Y) h
理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更* k# y5 m  b& h( B) h
改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。
6 q! V# Q- _- [( ~# O: o# R1 Z2 @& c5 z/ \3 Y  J
变化:
. c: R/ J: h! A) e+ k7 m0 R$ `; b6 S! A: X, M
ALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;, C& H" }& A! M2 q  y. Q
这个命令更改了.frm文件并且没有改动表。它非常快。# P! @( p* R5 C: w6 W" H
还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2026-3-11 01:44 , Processed in 0.038380 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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