召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2982|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
" F8 T6 O6 N* S4 |6 }5 m' G# v有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
/ u3 A  X6 C  V+ t- H3 ]一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
0 h) V4 v) _  C$ X6 z2 \在InnoDB中,只有事务提交后才会解锁
9 X; ~/ [* t0 E) }0 \; g6 _* d
  ~+ O* L7 ~2 q5 `( w1 i索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只  }+ s! G* X  f+ ]  R# \
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。# F( J; v3 ~" _6 Y3 _8 d
' B1 R9 P, u$ B" P: n  D, P
B-TREE: v$ s9 A) ?7 k% g3 P) I( u# H
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找2 b$ @' G* C* L. g3 O7 z1 V# p* L0 z* ^
使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。
% t- K- t9 H6 D/ F  o2 A

  1. . B4 @9 @8 X" H/ f7 t
  2. CREATE TABLE People(
    6 Y6 F: P$ W/ s0 n# p7 q7 m2 X
  3. last_name varchar(50)   not  null
    ( j* a5 v: `0 q2 d& `: h; n. t
  4.           first_name  varchar(50)     not   null
    2 |$ T8 E% Y8 Z: Z/ M& H
  5.           dob  date      not    null
    7 ^9 h3 d% H0 k( G. D
  6.       gende       enum('m','f')    not    null+ o3 i$ q1 \$ f! |: o8 W
  7.         key(last_name,first_name,dob)
复制代码
匹配全名& T9 T: M% c4 _# ^3 a0 f
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。, F, G1 q( @+ R* T( p( |( o
的人。0 e: R9 R7 L" g- A. X6 I
匹配最左前缀
* L/ {* y/ d3 j, c3 K( e1 oB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
: m$ V% |9 \& ^2 `3 m匹配列前缀2 i" |0 `/ V3 D9 W: \- O2 u1 c
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
7 {- V0 }3 v* m6 Z匹配范围值
2 L1 E' E% C2 {- l0 N- P这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.2 W) o( H0 Q; m$ p
精确匹配一部分并且匹配某个范围中的另一部分2 A3 r+ i- e/ H  l" [3 O
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last3 `" m5 v" l0 w9 j8 O2 B
列并且对first name列进行了范囤查询。7 f0 k, r3 R3 A! ^; x3 V4 T3 j5 |0 A
name
1 ]0 M! ~0 y% v. b0 G  `0 Y只访问索引的查询$ P2 j0 `" P* d. o% U$ C, N  D
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
# e; X) p) k+ ]$ z$ s( _. S& d5 a  L
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,; y  }  E/ W8 ?1 T3 A0 O
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查0 m/ R  a3 v  n$ d
找方式也可以同等地应用于ORDER BY。, U: l/ t/ s0 `

" Q. C/ a6 p% X; p下面是B-Tree索引的一些局限:
# T+ D0 i4 L% N* _# q4 B( k# H' @# E* K9 y5 C
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
) @% m' g# |; }3 i2 p也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓
* j) o5 c) I. A) C3 Y4 n9 P, `& {氏以特定字符结尾的人。
6 j* I, ^" ~0 W+ t! w$ `1 }; ?* _+ q- q: x" g
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定; R+ S! B. G1 W( V; A: V
义first_name列的值,MySQL就只能使用索引的第一列。
1 M* C6 W; z: M+ F+ _
& a2 p* }2 ?" j: z: V# r) ^3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是$ M" V9 m( q8 C1 a; V; W
范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而9 g/ t4 a; V2 l/ W% t' S, \' V- z
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。( y/ t7 V8 A, |$ x1 p  }# c* J
2 w# B; ^6 c1 n# q8 _) d2 }
哈希索引,空间索引和全文索引等,暂时没有设计
6 s! q& Z' p' X* u+ g- G% @5 Y, K* W# n/ Y) z/ I
高性能索引策略
$ {5 M0 _% q2 ~9 y" m
1 V5 G. M( ~7 i1,隔离列,意思就是不要对查询条件中列进行计算等操作( g6 ^8 P; m  R
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
7 M6 A, o; c$ F% Z  N0 F. ISelect count(distinct 列) /count(*) from table;
; K" O) d, B/ {8 K% G, M看看这个值时多少,如0.0312( x) r' f7 m, \5 J# c" h
那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算; P  t& u5 q5 v6 c: P6 @# }
,这对于大表很有用。
- U1 `' o; |3 h7 @, ~1 z7 g5 sSelect  count(distinct left(列,3)) /count(*)  as  sel1,
9 M) y4 k. t7 Z2 Z; Y: h count(distinct left(列,4)) /count(*)  as  sel1  ,
# o) G& q3 U+ a# y1 C+ ]2 R  r count(distinct left(列,5)) /count(*)  as  sel1,
6 T: \5 b+ s) n0 F, g1 [9 x5 z count(distinct left(列,6)) /count(*)  as  sel1,) a6 |- W6 S8 V+ H5 ]
count(distinct left(列,7)) /count(*)  as  sel1   from table;) g% G0 E5 G6 A3 O; D+ N0 @9 n& q
找到接近0.0312即可。9 G% r  |5 c3 b7 V$ ~  T; \
5 g7 e: t3 b' `2 J! W  C, W! P1 w
Alter table table_name add key (列(7))( l# K! o, E; Q* ^/ H! J
3,覆盖索引
3 `& h. ]- h, o9 T* M8 a包含或者覆盖所有满足查询的数据索引叫做覆盖索引  O% o. M; f  X6 O- F6 _0 _
explain时,extra中的会显示using index. K& `! [; `+ r  C
这里一个重要的原则是! S8 l2 Y- L8 b6 q$ P, n* M
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列& T3 e+ e3 O$ [! J2 h) k3 M9 u- A
如select id from table_name;
- ~2 R- n2 q. d1 k$ Z
2 f! [% y# [# R+ a- B1 r2 `很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不- ?* C( ]0 I" d
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
0 k, ~1 E9 T) e3 [$ ?
$ T, G. o: F; s  n7 T  G7 B- d
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';
    ; r/ E. |$ B2 g! O
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:
5 L0 U3 |/ l8 J- N  ]1,
  @/ l( x" o" ]/ v( a没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
3 `$ l+ @& t9 A9 {8 j! _2,+ p" z  [3 x+ k& L8 S
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
, d# S! S5 O& _2 C, l$ }有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:' p4 B* _1 L: c, }$ S  J

! e! p* Q) [- {4 H2 G8 t4 o2 M) X; ]0 o4,为排序使用索引扫描
8 C5 w" O/ X; ?mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。+ v1 T6 j3 a$ g3 C
explain输出type为index,表示mysql会扫描索引: r& R7 Z7 P/ \% J
: O& n$ j3 A$ ]% U* y- r
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.7 f6 `% x+ h9 \  Q! T' X

9 G$ }# k# n7 d* S+ E: sMySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
1 }, i* [5 w' C8 S: X/ c: v, i4 V: p5 r% q3 O/ d+ j; I; j7 K: Y: ~
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
2 N9 U: C1 g6 U3 R( f$ t( k2 |+ \% K) G3 ?
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。
/ {- l9 `9 W" r/ i( @  H
; m) Y6 m6 ?, n使用join可能情况会有不同
0 W/ l( q5 l3 b; h$ h# Q' N" t# S, V9 \" Q5 S8 w% X7 b
5,压缩索引(myisam)4 M" U8 O( x5 l; v' C  r- O
6,多余和重复索引(应该避免)# y# S* H5 W* t0 G- Z
0 B/ B* H) ~: @9 ~. h! N# j# m% N
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
' O2 Z# |( D# i3 o上有索引,那么另外一个列(A)上的; Z2 q' v' c% h; ~# g
索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
$ R1 F8 Y, O8 X4 N然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。3 t/ F, t1 U+ b

5 P5 v7 B4 W" O( J6 T  _要点:
* A/ r& z, h( b$ Z5 {' q$ q在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
9 d: p2 \+ T  j/ D( l+ V3 d5 [/ f6 s2 l0 S" v; B
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行; Z# z9 O+ R# p# ~

2 D2 [; L1 B! U+ E: B" |5 U8 O8 L7 K) K: b
! B+ m- O$ k/ P) N2 l( ?. e
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,0 x! n! o/ Y6 K& d

5 l; |9 k, \$ |; O3 t0 u& L拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引" S. j) I9 O, V
/ u7 h# v' b9 ?
2 E# u- e7 ~8 ?5 h+ Y6 l
/ F* o1 H) I# @, }4 N7 j
一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索
7 N+ v- T& T6 \3 ?( V9 G$ X+ Y9 m8 X8 \& a1 a
引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。% N3 W* r8 a0 [# Z, h% [, I/ {

/ ?5 d, L, _# A, H! w2 V4 ~; U0 Z1 m( L' {3 W+ z( {* H+ }
- d$ T, [5 U" j- s- L- s
一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。
. T5 ?, z) ?: i8 [  k5 t
0 m& X" ^" l: C% T, ~+ w例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理3 }  P; A- `- b% ~1 c) f3 L. e

9 P( m8 A0 M% _5 a& s% C) j& t, b这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式: ^+ r0 n/ v9 Z  H; h

: W9 r) w' N! ?+ K% {1 s
0 d5 j2 @% f. w9 Z4 G9 q# ~4 h& |, X! L
避免多个范围条件,只能对其中一个使用索引  i3 z, X/ r' \! Z
1 H$ z' }  ~# w) \6 t3 D9 `. l6 x5 Y

5 p$ E8 l+ L+ Y% r2 l. w4 g2 E0 }* D2 `- j) v( k  t: L- Q) ~3 x
索引和表维护
6 K+ s# X" r& h0 A9 E' g
! c+ [# f* y$ f) o! x表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片., w* M" u1 D; R/ V- l

4 C: D2 B) @* `& f! R, r: ~check table table_name;# ^% k9 [7 i" g9 U' E/ v
repair table table_name;
$ U: d8 Y" u& ~* e5 p8 }9 d6 U" p) zShow index from table_name;检查索引的基数性) E7 p# S0 o! j) y' Y- ]) T: {2 e

; I% H! F8 X; M0 B& G# v: Y" Q" f  P主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量
: p/ a- a! L4 z2 B! c' K5 b
8 s' x: I6 e: E; X/ z
3 c# n8 z3 `$ b
) x6 i9 _2 {) S$ F- }B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。
/ [; e6 }- a5 e$ {% u
8 Q. [9 j0 T+ n  l8 f, `$ _$ y表数据也能变成碎片化。两种类型:
9 H# g5 I8 [/ \% N
3 ?: o, d% b% ^2 X' _3 h" w! d1,行碎片1 A2 [: ~( r$ @( D0 y+ z
3 H- C' i' o. g1 r2 k8 ^
当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
+ e( D! m* @% I+ Z! |
. t( e5 i: z7 `0 ?) B4 N& n
- e" M8 q+ H: h4 u+ n( l& O& ^8 j0 z$ o$ ]! o+ X
2,内部行碎片  N7 x% c8 a' t2 l2 `) V1 }0 i; L
) p5 ]; ]! Z  I1 Q' [/ O5 _
当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和( `0 R. I0 [" [
' A% @0 M$ |/ C. j5 Y
聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。
2 i  Y# `) m& b3 @
2 u6 _* m, p6 q
( `- Y( i! b1 D# L9 W4 r/ V! {3 X9 U/ t9 a3 X
为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。- H$ ^( ?3 M+ J$ E/ {, R
4 L4 T+ H. y9 b" a' U+ f: C/ m& W' u# r
: U! q+ G( {& `7 k! L6 h

. ~& C9 t* z- _5 ~3 c$ c7 V. |ALTER TABLE <table> ENGINE=<engine>; m9 M* |. l, D2 l3 g4 g6 s$ S% b
% l) ~6 `; x  Z, p( f; g  M
) `; P6 [, U8 g5 d( H* m1 R3 ]' Z/ f" V. m

* L6 X5 l4 u: @3 @8 i, {: U加速ALTER TABLE
9 b: m3 f" V9 J+ k) b
% v3 O5 m0 t" E, T$ \& J
1 ^/ F; p6 H2 r/ l  e6 y  m3 H& S$ E9 N1 i1 N6 e
MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需
5 M& N( v* g1 v
& j* w% k& i+ a& [* E& k: K* A1 i: K要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,
/ E" @4 H2 ^/ x; j! Q: O6 ^% k9 M9 m, Y" T  c
而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。* K/ I% Q9 y! ]
. J! f; l; p7 m  u7 J! j
传统:8 r4 v9 A9 G7 H$ X) V
0 [8 c4 c! y$ c# q# }* E7 v
ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;
/ X) Q9 W/ |. ^7 P9 U& Z$ W理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更0 }6 L; q  J  |+ I9 f! I
改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。+ j: b  T; R2 u% T2 I0 p

& u  R; s/ S2 u, E0 P变化:
3 }, f* Q9 O  p4 H% t( S: ^
% I, m; H' w; X% e" i0 GALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;. P: T3 X) _1 H. \/ `
这个命令更改了.frm文件并且没有改动表。它非常快。. }. w) W& l" E7 j
还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2026-5-5 15:54 , Processed in 0.041216 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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