召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 3476|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能9 o3 b9 V' f! s3 {
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成4 s4 Y6 d) X- `
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列3 U: x, c" H; p" M
在InnoDB中,只有事务提交后才会解锁" T  E3 I( z0 y) m
" F+ }) \, a- V. r
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只4 t  t) X8 w, a, C
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。5 G0 Y% r, ]) G0 J" ]( z  A; M

5 Q+ F- \- P) a9 E; y+ iB-TREE8 o0 J' E3 v) _# n9 B  s9 z) E
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找- }& L; P1 o) m8 l2 Q
使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。
5 O$ f4 U  O& s

  1. ( T: v  C; D1 J( U2 f1 M' T4 B; B1 Y
  2. CREATE TABLE People(
      P% ]* K" _/ |% t0 Z
  3. last_name varchar(50)   not  null
    8 T, a% `8 s" f9 s- C" I
  4.           first_name  varchar(50)     not   null
    * P0 U, W- f" s. A( J8 ~
  5.           dob  date      not    null8 b9 \& t, J8 Y
  6.       gende       enum('m','f')    not    null5 G* i4 ^/ u( C- X0 Q' P8 B) [' V
  7.         key(last_name,first_name,dob)
复制代码
匹配全名
+ n+ t* U. J5 L6 Z6 E0 m全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。! Y' N  }% x9 Y  @0 U% E4 z
的人。2 f& ]+ }5 v7 b* r; g9 O
匹配最左前缀
  g3 }" T) l) [4 X" E7 RB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
2 @& t4 H. m: R9 r$ k3 g匹配列前缀
, c. C5 [1 P7 w) F- e" o3 o+ ~可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
. n6 @- R1 v' B# k匹配范围值) L' O; v# @4 y9 S
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.
/ L0 Q0 ^. e: p2 a" s: ^+ U0 P精确匹配一部分并且匹配某个范围中的另一部分$ ^- z$ O" j. l) R- x4 P# A
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
) a$ S* K; r+ i" u/ J列并且对first name列进行了范囤查询。6 J' \' {! G: \1 G  O  `
name
( e4 b) ~. O: p只访问索引的查询
7 {; o6 u0 O9 I' \% D( z9 XB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
% l- [6 h& o2 j! p: |
* X# U- o8 ^: ]5 A& L) I由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,
9 A4 D5 A! ~4 Q如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查0 B1 {; G. @9 g" F; a* z
找方式也可以同等地应用于ORDER BY。
8 b) w3 \4 E. C. X5 h) W* S- Z5 @0 _# @: Z
下面是B-Tree索引的一些局限:
* L7 A, r( C$ Z' e, }/ t( w& T% B7 k5 ], {% d% Z1 v' F& f
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,1 L1 ?9 U6 \) ?& x+ A$ N
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓2 E; j! n" k$ r) F
氏以特定字符结尾的人。. q+ q. m" y0 j

% `3 [" T0 l9 i; e7 C2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定3 v, ]' S0 S' @% i* R0 C+ t1 g# j2 g
义first_name列的值,MySQL就只能使用索引的第一列。2 `' w1 M' N4 t. D7 _9 m

. T3 h8 F( N" Y; u% T3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是4 I$ N5 Y% [& `  u
范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
# G' k/ F, L/ {8 s不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
" g  f( s: r8 K: M
% x: L0 X5 R' K% f# A哈希索引,空间索引和全文索引等,暂时没有设计; j# Y2 ^! V* y9 _

" M, m+ J/ l8 r8 n7 I高性能索引策略
7 A* z% \0 h0 p+ q8 a3 X4 ]0 ^* W& c: I( \
1,隔离列,意思就是不要对查询条件中列进行计算等操作
9 @6 k4 D$ Z# X/ g2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
9 R9 A' D! b5 w! Q  R% [: CSelect count(distinct 列) /count(*) from table;. A2 ^% }! h. T% `4 }6 ~7 |7 }
看看这个值时多少,如0.0312( j" D7 D+ k9 D: N& }6 t$ l# A  x
那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算$ ]  q+ i7 }0 l' V8 m& }
,这对于大表很有用。. Z3 F9 i8 r7 p  z/ Z
Select  count(distinct left(列,3)) /count(*)  as  sel1,
: @; x; V5 m( [$ ~/ H: }. j count(distinct left(列,4)) /count(*)  as  sel1  ,: L2 R8 s0 ~" \6 \
count(distinct left(列,5)) /count(*)  as  sel1,
) Z, Z+ B* g$ d% v' f# l count(distinct left(列,6)) /count(*)  as  sel1,
! Q5 v2 v1 i, t) p1 n  V- R* d* ^4 T1 p count(distinct left(列,7)) /count(*)  as  sel1   from table;3 o& P# o6 y" P% l2 o+ z9 C
找到接近0.0312即可。
/ u# f$ ^2 J' f$ y* Z# P/ x" t6 ~4 K' r' u
Alter table table_name add key (列(7))- D3 @+ r2 r+ Y6 I
3,覆盖索引5 Q' H+ q3 {% L! N$ S
包含或者覆盖所有满足查询的数据索引叫做覆盖索引
# m+ n9 \1 _" I. a! b2 z! Nexplain时,extra中的会显示using index
( ?5 L" T$ j5 v这里一个重要的原则是
, N0 }' V. L$ j# }9 _+ W! E% B6 `select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
3 {5 g9 o5 X# E9 b- T如select id from table_name;
; T, A. k* C) D! V1 X- U% ?! Y+ }1 A' H& o" ]
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
3 D/ q& ^+ q* }8 \( C9 H一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。6 o7 r! @& }6 B3 b3 p* {2 ^
' @# o; C: o& X1 g2 b2 Q( L2 R1 x, H
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';; D) {. b1 H& p/ i
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:* s+ U$ [8 K2 N: v$ @3 I- `: f
1,  S& Z% Z" e6 Y4 O) H
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
. ^* z1 b# f) o( d0 k; e$ L4 G2,
, D  [) F; e7 e. uMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。7 d3 S5 b: k2 P" m) i/ ~5 E) i
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:" A% w0 I  A: K! {- O" L1 v
' C! Y# |; _. Q( |4 F; e
4,为排序使用索引扫描
0 h5 Y6 P9 a3 o( wmysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
/ e. [9 ]4 y! K7 Lexplain输出type为index,表示mysql会扫描索引
' D5 z. R8 ?- e$ Y0 f! m) q3 B
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
1 \5 ]0 y3 J+ D. v4 B( L. {) [# L! H3 x2 W
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。6 m  V+ B+ Y% }9 G1 Q, j
" X  {: m0 Z0 ~" X4 [# n
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
9 S- @) H5 V# n3 R0 g8 m) w( b3 S& C6 K5 c6 F, O
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。
) s5 A3 {, D% L' {7 b: w: h- S" M$ z2 E
使用join可能情况会有不同
' ^" I- M- a! x3 W. ?" X) q- ~. {' e$ P0 J3 v& s
5,压缩索引(myisam)
+ F7 p2 |0 F$ ?. G- y0 m1 b$ M6,多余和重复索引(应该避免)
) n9 P4 q: R1 X% L
3 r4 b1 R! D5 G* y) w多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
# C6 }* v+ q8 q2 [% I6 T上有索引,那么另外一个列(A)上的/ R; S0 _  x3 G$ _. Z* g1 v3 X2 D
索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)% w/ k2 j; w5 Z# {
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
1 P" d+ t6 C, J
% d3 O+ }+ e' g要点:3 _+ s7 G" Y. T6 {
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.# T% X' Y( H2 P( N1 Z/ `

5 [2 B) G  x% I4 b1 J即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行. C" W* I# y# j1 z+ h$ L
5 [' H( @4 F2 ~6 e8 K  H, r7 D! l
6 n6 u; G# c4 ^! }8 s% J" e

2 \/ u. H) X& S) J$ w0 w3 D: S
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,
/ \0 g0 |7 {% w+ \$ T$ O" r/ H6 @) ?* d3 x' ~+ |6 a/ f0 C6 P
拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引
: t" g/ F  H8 u6 U
  ^& M* k: e# ?) [% N% r  A* \( q! E" m3 f3 I& t) u, @) G
- h  b4 Z7 s; X7 [
一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索
" n& Q9 O+ ^% [. l( J5 i6 V* m) Z8 G: {  l( D! e' x
引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。6 j. ]( z, R. k: D
) `1 f' @) G$ r& |( c" p

5 p6 Y2 j9 X( |$ x  c1 |6 [
+ f" {  U, k2 L一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。. h% T: H5 K' b
. b; A5 _/ |; t0 M
例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理: H3 }( o% Y) g+ n, ~

0 R* S% `4 {1 b$ z+ H& i5 v这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式1 h7 r5 W4 ~3 y( i% ~+ Q

. o: A( N" i3 Q5 i! c& `1 J0 Q8 p: _8 C5 a* H, d1 {
8 p3 F; k* v* S$ V7 C( V3 v8 }; z% }) s
避免多个范围条件,只能对其中一个使用索引
! b( g' a5 u/ i
" v- L3 J: S. q2 x5 k& O! ]  i3 S4 J. z2 T
6 d" ]4 ]# q8 v7 f) P8 h
索引和表维护8 j; }6 O; s! |/ n
* g4 e; x) E! S0 \. u  n
表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.
5 K% r( @# q7 d# ~8 G0 k. l+ O# ]4 D
check table table_name;5 k4 {! L% M/ X* x5 C4 f
repair table table_name;( [, r: j8 |0 h" m+ l
Show index from table_name;检查索引的基数性
5 `6 y1 I8 i* _& g$ }) w1 E2 U
. |9 p+ U/ F3 H9 S主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量0 W  H+ m0 [( |' h# ~" J( O

# D% @& q" a5 x, y9 e1 M, k* t
- X& I7 P2 r' ~9 @
$ F- x1 z. ], ~  A/ h, NB-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。4 |: B8 m- c- `# B9 ^" q

" A5 d2 h  Y$ L) |5 O0 e表数据也能变成碎片化。两种类型:
9 C3 q6 l) i% Y8 k5 O# ^. s( T; u7 n: e# x8 q! \
1,行碎片
, W' B8 [: x* `  k
" ~( s+ v* e6 p+ `! p当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
: V+ ~, ]8 |0 @  k
: e' [6 D5 [1 A# r& Q$ ]! @9 Q8 A. m6 ?3 f/ T
. C' R, s' l5 v3 m4 a
2,内部行碎片
9 b/ J- O" v7 J1 z
) c5 \" `' @5 U0 Y当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和" W! F# H; v9 n; \2 E8 a& [
, [4 L7 B8 K# d
聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。) x5 m, i8 V! ]9 h, w- j1 e

  t/ v" a: v) `
# M" z0 F1 v" g" L( v% Q: |3 m" n8 }# V# m- y' ?
为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。9 C/ s4 Z% t8 }+ d/ f
1 z5 |+ \, Y1 u1 H- g

( x5 a/ u& b) s- r3 H( Z- l- B1 O5 z; {- Q* l
ALTER TABLE <table> ENGINE=<engine>+ y3 ~7 Y4 m' ^, q
* v( b$ \' J8 p( l/ ^
, B5 @6 X' M6 B+ B8 a$ m

  w8 K* G7 f  z! C9 _加速ALTER TABLE- _7 d1 ^$ ^, c8 e
( i0 p/ }  p5 Q
8 K% I* p/ Q7 Y8 w: _

5 _' C' R. e- P+ X8 z( S  r# {! @MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需
6 V2 G( {/ `2 N8 p0 p9 D8 q' T3 l; D- T; G- l
要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,
; M& f9 o% L2 I( [6 s* k" s! T6 b6 G) H2 e( \
而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。
1 z4 `$ ]) i; U( j% M
7 m; M; Q! g  _传统:
9 m) B/ y, a6 Q7 H% U+ E6 b$ a0 m: f0 v8 I
ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;4 s& G/ B: {1 _. y6 |8 Q0 p
理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更2 u0 \  A* i+ U
改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。3 G" i: h9 g" B$ D

4 {; L/ i  \/ ?变化:9 [& K" T/ s$ a& g
( A2 X  K1 b4 T  A6 @7 Z& k- u
ALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;
8 o, }5 `5 c- V; O4 h9 P# T8 f2 o8 G1 B这个命令更改了.frm文件并且没有改动表。它非常快。
. _7 \: [: M5 d还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2026-11-21 20:27 , Processed in 0.042029 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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