召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2805|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
9 q% \2 Q6 ~# p1 L8 d有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成! T* s( z: I7 v. |  j  [
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
7 @! u' U0 j2 D/ ^  y在InnoDB中,只有事务提交后才会解锁
1 `9 u/ k9 }8 n) K+ ], S- ~' Y. q! M4 M2 i5 G$ K% X7 j% d
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只0 u; y& e2 L7 V8 @
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
% c2 x% x0 p2 Z/ ~0 Z, l8 I8 O2 A" E
B-TREE. S( a+ W9 }" z% \/ {( R5 N
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找/ F$ [) V# F  R* a& r7 O
使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。0 N# w4 G! E: o1 g8 W# U  ~' X( u3 E
  1. ! w; k) u& I9 P3 s
  2. CREATE TABLE People(( s; l; l5 V0 f& x5 P* X
  3. last_name varchar(50)   not  null
    # M. @! R) T  R) _" _; J
  4.           first_name  varchar(50)     not   null
    1 n; ?8 F* x& _% b# \
  5.           dob  date      not    null$ |" I" m- a  Y1 e4 Y
  6.       gende       enum('m','f')    not    null! y+ Y, u  T1 |+ b
  7.         key(last_name,first_name,dob)
复制代码
匹配全名; ^' h) {! Q( o7 O
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
7 @% Z% o* }" @) l. c& O3 C) C" \的人。
+ E' k7 l& K! I" d) z4 X匹配最左前缀8 z. U0 v- \# Q$ j
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
7 H2 Q, j- ]% `9 c* c) _" E) r匹配列前缀
& C# U" t% E# q7 H* H+ T可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
6 r: i$ n# ~0 @1 _) U; O5 d! L匹配范围值
0 d6 x& N1 J3 X, S: q. C这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.
9 y, r6 l' t! F  [精确匹配一部分并且匹配某个范围中的另一部分
% r* m3 Z2 Q* s" _( q2 U5 f9 `1 l8 k这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last1 i) d: ^/ p- K9 Z9 F6 P
列并且对first name列进行了范囤查询。& \% {+ ?/ g9 R. ?* E% E
name
4 Q. Q1 g0 ^. v只访问索引的查询* q, D; d! X- ~4 s( g" Y8 ]
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
( [! v6 c% u4 s% F  d  @: v" R( Z% r8 D, _3 G3 u
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,
1 @) e6 N* l, [# m/ P如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查# ?- O$ ], u3 f5 c7 J- h5 _7 E
找方式也可以同等地应用于ORDER BY。
# v- H" ~2 X$ O0 V# P) M! }. k2 k$ l5 d6 k7 d2 M
下面是B-Tree索引的一些局限:
! x9 o# G9 [0 V1 G
% v; o+ s* ?6 e$ t1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,, @) P7 ?1 v0 I2 ?
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓3 e4 e+ S% I/ v7 ?5 \/ c4 V1 X
氏以特定字符结尾的人。/ R/ g) M  j8 y, G' R/ |

) f$ L+ Q( I0 ?( [) W* I- e2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
4 J% |. M* g) P, h义first_name列的值,MySQL就只能使用索引的第一列。
' h9 y* y9 [) d& Y
: h  g- J/ r" k3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
4 H9 s: O5 D. U) @范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
2 t5 n/ m6 h' s8 I: @3 m不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
" u. `* r, w) s; I1 r4 q& _. j3 R9 O  i! ^4 `$ G
哈希索引,空间索引和全文索引等,暂时没有设计( d  x0 Y: `4 y5 e# D
1 ~' w* n6 l. g) j6 F+ ~
高性能索引策略
! L8 v4 l6 r6 T6 w: J5 f1 q, O( R+ ~' C8 @  w8 f3 s& L5 p3 [1 r
1,隔离列,意思就是不要对查询条件中列进行计算等操作
# C" L" h- T! b$ o2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
9 @) _8 F, v4 n6 r) RSelect count(distinct 列) /count(*) from table;1 d4 Q* F; Y! ?& ~( b- z8 O8 _
看看这个值时多少,如0.0312
' i5 m0 Q: n+ l+ B那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算. a' j; f* g% L* y
,这对于大表很有用。
! ?- y) `5 W, K- c9 c7 q( {0 [Select  count(distinct left(列,3)) /count(*)  as  sel1,
; x- z8 [4 b$ }/ j2 [" ?& x( ?" B count(distinct left(列,4)) /count(*)  as  sel1  ,3 b6 W" o' L& f, v" ~. W
count(distinct left(列,5)) /count(*)  as  sel1,
) ^% o( q  b" k' C count(distinct left(列,6)) /count(*)  as  sel1,+ x$ K5 [: B& x$ v4 [
count(distinct left(列,7)) /count(*)  as  sel1   from table;5 r1 F- z9 k, L, O! F5 o5 B
找到接近0.0312即可。2 C2 c" t& {% m6 e9 j) B

7 \" x" R, |$ J& b' K' ]Alter table table_name add key (列(7))4 b4 X8 c! x* E; T, s
3,覆盖索引
. a4 Y( `! H9 O7 s& G包含或者覆盖所有满足查询的数据索引叫做覆盖索引
- M% Q7 K+ s* U! U2 i/ m3 e$ Oexplain时,extra中的会显示using index
  y# O4 A1 E8 s. y" ^这里一个重要的原则是
2 t. n, H. a0 h  P; K7 B" G5 \select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列( r4 p$ H5 _7 K: N+ b
如select id from table_name;
4 b  C7 y0 j1 j8 `2 J7 K' B$ Z8 m+ T9 Z1 W& a
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
  K0 x, X: }! M( ~% b! o一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
8 r+ q1 R1 X: ?, @& T% p! I" B, p8 B1 m  _$ E
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';
    ) p8 E0 @1 J* I( ~
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:9 z# q; R& }6 F5 x( k  c( h
1,+ g  E; `  e8 ~  D
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。, ~0 {3 W6 {- d  F4 O
2,
# H, v% O/ J% U+ q" E! r5 L/ YMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。5 |: G/ g9 m3 b4 |3 s2 \8 w" W' ^& n
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:3 A+ X/ K' k9 h7 m$ s* v8 G
2 X9 Z. o/ i* r! X* _
4,为排序使用索引扫描
: U0 U% T& r  Emysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。6 I6 X/ t" d6 n6 P
explain输出type为index,表示mysql会扫描索引
) _9 ~; G+ o* b7 y" Q9 `1 M: r' @3 o; D0 O' U( W3 D! ^! m6 W
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
- y$ ?9 d: e' F, g, l6 m/ u3 C+ a+ F" ^$ C" h, N% |$ Z# ]+ q
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。' Q2 c1 T4 T5 S, a+ @8 P3 X, K' G7 N

5 \* f' @; v- Q  N. _0 h& s- @按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
+ f: t. `9 B# s  {7 L
8 j, l  O+ d4 t; ~6 ]5 C, F4 bORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。- Z4 b$ ]! S! T% v# Y

) ?8 {1 ^( X% Q! v, H, Z使用join可能情况会有不同
9 G# [- l, ]6 }, Y+ _7 c; V! X& G0 [: C3 i: c6 }! U& @  C9 ^# m
5,压缩索引(myisam)8 f8 O8 T' S# Y" y" o& M) u& S2 c
6,多余和重复索引(应该避免)9 z8 ~( `) e# A
! e  M( x" D5 y8 S0 _7 ?+ M
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)' z/ F# c& v* g& C, k6 M8 N. A
上有索引,那么另外一个列(A)上的
; u9 p" X7 F# g6 @1 `4 a. h索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)' H; w' @0 x# ^
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
4 _+ Q4 q" n2 T& }9 e. ]( L7 K7 k6 o9 }+ x: J  r1 o: B
要点:
: Z0 _' h5 e, l; @8 H/ c在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
$ T7 l% ]/ R: O( `; ]* ?9 l' q  {7 n; I% {; r
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
. D! }8 G3 T2 h# {: j. T8 S" F2 i2 @
% ~( L9 o6 M' O! W! |8 h; Y3 Y! p3 V, J  ^0 r. M
# ^- n  I1 K- y6 {$ g
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,
" B5 }6 w: q0 M: L7 o' Z: v  x3 ]# D4 O" ?
拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引
) r* @, V, q( Y! x2 O+ w4 _8 s
8 d7 u# f: D8 w- U+ c
3 h9 b5 c! C) I
& O8 k( O, z  ?$ H" I0 s, ]一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索
6 _8 S8 G1 p: B) J! f4 q+ c  F1 v& B) L- Z1 a4 R
引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。1 Q( I1 O9 u1 [/ J

; i, Z; d- ~7 ]% M1 i' j8 U9 @* k- l! L4 S% h

, s" T% f  m2 H: t4 `4 U* D一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。
. H, ~( o9 K2 f; i9 z! Q1 R( ?) Y9 [* d1 I; B4 I6 k7 ^8 Q
例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理" t' R7 ?; f& N1 W* z
* R& w& E$ M! p2 K: t, q2 R  u
这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式3 C9 r+ \5 S" l2 W. `0 t

( t+ }& n' j1 S1 p8 @
* x8 w5 r5 H8 M. r. X) f1 M
: `) O: H/ Z% y+ D) U- M" c8 V避免多个范围条件,只能对其中一个使用索引
" q% G( o) {0 _, s) g
* B! u% F  L0 ]+ k5 s: Z8 Z4 v
& ~8 ^) ^8 i4 |  p1 L; s" W; L3 b9 A
索引和表维护
% |! v) O# U* T3 C( T& _$ h! g, r! D$ i4 E, B* c
表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.
. \  h  v2 p& V. J! ~" m" L. s/ q9 U- I; |% j& D9 [
check table table_name;
$ e" o" V/ h) C7 x, e; j3 q! crepair table table_name;7 F) `- K) a: a+ ~/ Z9 i
Show index from table_name;检查索引的基数性
( H: `( u  i/ g3 U0 l
: k4 F) r+ d0 y9 J主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量7 w* J4 j7 @( C

& p3 x' [  I3 S" R5 T0 d+ z$ n' n" o* L; A! [
" I, N7 V# }: O" g: V0 g2 a
B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。7 `0 u9 R) I2 Q" n! j6 ~7 I9 J9 g2 q

5 x+ L- w6 A6 g# c5 l表数据也能变成碎片化。两种类型:
) X$ q0 M$ o/ E6 T2 l9 U) T
$ _) j# c$ j0 T8 |% G/ j1,行碎片' T7 z* r, \/ L7 N/ ^4 Q. f9 ?

" ?; i, d2 {/ I+ G  p当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。  C2 W8 `  y0 y7 l$ j

5 ]- u8 _$ @1 p" r
* f' l' t' T- j1 S( r4 |3 a( M: N+ c
2,内部行碎片: y# E4 d2 ~6 ]  @$ J+ Z

: }4 |- H1 g2 T: z1 ^3 }/ g# q- Q% n6 E当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和! i8 F: c& f" s0 K; Q
7 m$ z! F" c  ]( r' E( o$ e. a* }
聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。! }' b5 s0 m0 [: l

+ i" p6 r! x& _8 L% W  R/ M
; V  p" m6 h. x, `
7 E! ?& C  P& O& h0 a9 Y为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。
6 U7 t$ |  _# G5 D, m. \3 N# U+ {0 W5 T4 l- s0 ]6 H

" Y8 c+ R$ l2 Q( d% k/ m
# ]) P* M+ D4 L) S1 IALTER TABLE <table> ENGINE=<engine>6 E- ~. g; g$ R4 v0 b
2 o/ n, ]1 y: k6 H$ B  c, c* Q

3 f& }' I8 i' F! h% u- ?
- s& d9 r) [5 l4 d/ D加速ALTER TABLE
! l+ g. [# d- L- o9 `2 d. J2 F) I0 r' Z! h4 W/ c7 p4 ~( `

* I$ p# n5 E. K  u1 I$ _
6 D9 H! [" B8 B) X- k3 tMySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需6 u9 l. H: E0 O) V

5 b, E( R& e6 a9 v) b) e, G要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,, N7 F" C: a3 v3 d7 S' g
, o: }7 z% \( s5 j0 C4 Q* J
而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。" }+ ?2 v4 Q( p& c$ V  d1 Z

7 e- d8 K4 P# V( z7 F传统:8 D( {( t! s) x( g
$ E+ S4 |0 s# y2 L
ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;; m/ u" L, z- x4 e
理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更
! |9 Q4 J3 P" h" ]改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。
% `! h7 {% L, n$ R: _; S' O; n
) i9 _& ^+ B- H- g! ^变化:
: _" ]% N2 E, B/ D3 o: B; U4 T/ u! T) i! k' H) U6 `: g
ALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;
# ^/ m  X$ j  L7 v/ o4 v/ P这个命令更改了.frm文件并且没有改动表。它非常快。
0 E. H1 M+ l4 X1 f, U, x+ w+ S还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2026-2-1 12:17 , Processed in 0.043967 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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