召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 3472|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
& V+ t6 _, A$ C1 c8 p有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
* ~# t* N" v# H一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
( x. c0 y: j" t4 r& G  ~: J9 N. S在InnoDB中,只有事务提交后才会解锁: ]! m5 B* Q# {

( B1 U( D; F5 A. [/ W: k/ M2 [索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只
- s  M5 G+ H4 o& H能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
" D: @! \( J: J" D1 b" B$ G! J
' Q; H; D; Y6 ~  PB-TREE
) w) F+ E$ ?: s% J& t能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
7 @" @+ ?  z' Y7 }& Q1 R+ e/ C使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。) d) z/ x- P- C6 `" r" u% R" p
  1. 5 d7 E# ~) _  q. ~& p
  2. CREATE TABLE People(
    0 [" n& a' Q* Y' I6 i! P
  3. last_name varchar(50)   not  null# L6 L. m; e/ J6 j: d9 }
  4.           first_name  varchar(50)     not   null
    ) k8 ?2 }9 I* c2 s; i6 n# }
  5.           dob  date      not    null' S* I, q2 ?6 O" O) W
  6.       gende       enum('m','f')    not    null
    ) ]$ ]& j8 K. M/ F. _1 W) Y
  7.         key(last_name,first_name,dob)
复制代码
匹配全名
4 }, N% \8 z! S  I! C全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。. p& p, i: B  t- W4 `
的人。" H9 ~! ^9 b7 `2 ?% C# ^
匹配最左前缀3 ^6 l# w/ b9 z+ N$ G( f& Q- L. v
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。- v3 ?% a' ^# e5 H) ]: c
匹配列前缀* \! R2 U6 j/ ]6 J  @
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
1 l! L( `: G2 a0 U3 i匹配范围值
0 r( c: g( Y( J) O$ _; ^$ x* d; }这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.
: ~6 u5 \. O7 E7 o精确匹配一部分并且匹配某个范围中的另一部分  o$ L. Q* k  Q' S2 Z
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
& w3 g1 }1 p0 t, v& @列并且对first name列进行了范囤查询。
2 D! W9 V7 \/ \) \, [5 Xname9 E- x1 E+ p: R7 k+ {, E
只访问索引的查询6 ~# M8 j# g% Z9 E3 l' X
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。7 P; \% b. N% Y8 I5 ~- u# i

8 r3 M) S2 `( h5 r" ^* m由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,+ c% s$ {+ E; U4 U8 D2 G2 p' m
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
* q9 @. F. `; J' m: e找方式也可以同等地应用于ORDER BY。
% |+ w: e- l, M3 q- x! p# x# S3 y5 r- |# g$ u7 [! _
下面是B-Tree索引的一些局限:
$ M/ c- ]6 ^7 \( a9 G) p" j6 Z" m$ p% m4 s8 }# S
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,( X6 w1 O( O! I! _/ o4 i( ]
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓  i7 E, s' V4 N4 w" }
氏以特定字符结尾的人。( L8 \2 U6 _) q( P  h: N2 S
% j+ O- L8 i8 s5 A/ t, b! d
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定4 U! d3 H5 j4 M; [- {: P
义first_name列的值,MySQL就只能使用索引的第一列。& X' Q. V8 U8 T. n. U5 f  B+ R& @1 k, A
; ]3 e" Y5 w9 k
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
+ S; V2 H6 L. d8 _6 L) |范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
+ X6 ]! U! k4 ]3 |/ H) a: l# u不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
. J1 E) P9 f2 o& p; N% M& ]1 }4 `9 H! Q& s8 }
哈希索引,空间索引和全文索引等,暂时没有设计& M" p3 ^+ r* F( L

0 a  S4 {$ Q1 p/ F; \) d6 z高性能索引策略  c1 X/ Z4 |; K. i- H

! B" U* Y2 h4 Z' O! R1,隔离列,意思就是不要对查询条件中列进行计算等操作
$ Q* n! ]4 W. e. D9 h: p2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
7 T3 R. h- M5 {$ @7 h( V0 h8 vSelect count(distinct 列) /count(*) from table;
# q. |  u7 b$ u5 \看看这个值时多少,如0.0312
5 K: Z3 U5 A  r那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算2 o& m  A+ v# X0 D: R1 |5 F
,这对于大表很有用。% M. e6 m0 L9 w1 V% R9 n# B  J
Select  count(distinct left(列,3)) /count(*)  as  sel1,( J' d) y  N- B
count(distinct left(列,4)) /count(*)  as  sel1  ,
+ I3 @. J+ m" p5 S3 u count(distinct left(列,5)) /count(*)  as  sel1,' ?. E( v3 K' Y6 N9 v: P
count(distinct left(列,6)) /count(*)  as  sel1,
' Y* P$ T$ {. ]% Z. A count(distinct left(列,7)) /count(*)  as  sel1   from table;: u! W, ?1 W# w6 q5 i  W
找到接近0.0312即可。
1 k: }- @. C% l3 y: J4 b% K/ D" B+ W9 n% v
Alter table table_name add key (列(7))
; \2 v3 N( e$ E1 v' v' h3,覆盖索引
' I6 R9 |6 r  @' e  ?7 a0 b/ b包含或者覆盖所有满足查询的数据索引叫做覆盖索引
4 {/ L; v4 t. l& t* }, ?- Z" lexplain时,extra中的会显示using index% F1 D. G  z; s+ T* Z, b
这里一个重要的原则是; T  U- `3 |. ?' ?/ O; J3 H1 X' f
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
8 v. C$ ~) X. B: m. N' g/ W: b2 D. }+ d如select id from table_name;
4 p5 d7 m2 W& e; n- I' y2 a, v6 S/ h  b; k/ C
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
0 N. V) B! f* }. t0 }2 l* |$ M一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。6 h4 \% I; L0 s: X/ W7 ~# T2 O- I

! }4 p) ]/ ?( c; c' D$ i: S
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';% {: @% T5 B( A2 p! A* y% Q
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:
7 z( k8 w+ }3 x8 A9 ?7 L4 s1,& f. N& d% `' K: l# }& O
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。. _" I" H% a2 k8 Q, T; P: h+ M
2,
5 C( V1 A' Q7 i( tMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。% o+ [' c7 e$ m
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:2 m6 S8 q& N- @; i/ z+ o
( Q* T% i+ k0 C. P
4,为排序使用索引扫描; ?( f3 u) M% M; S% Y- v
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
$ R& W* |5 d, a8 c  \- ~explain输出type为index,表示mysql会扫描索引
2 C; Q0 V' {- `2 `! f7 V5 w6 l5 ?
5 m" A: l4 b5 p/ G扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.' b/ N& e) f$ Z
$ F% l3 V9 ~7 f7 h2 l' V
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
$ m& W' p! L* Y0 A& ?& ]; S
6 a, N# Q# i. ]; @' W" i. c按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
- g$ W( H0 V0 `* J" \# c" h, s( `* l' h3 q
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。+ t' }7 ]6 D7 b

& I! H6 g9 G! _使用join可能情况会有不同
8 r: C! J+ R7 ]8 V+ {' B8 v* {( @0 ^* {
5,压缩索引(myisam)# [2 u6 {; c0 z
6,多余和重复索引(应该避免)
! Y6 l# z0 x4 w8 [9 f0 B. m% R% c# A; U5 u  c
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)7 l- ~' \' R# K. ~$ j
上有索引,那么另外一个列(A)上的
- v" x+ F5 W2 e: R0 l索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)( @+ l% r5 S* U* l2 P8 @$ B1 P. k
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
. M) e; h0 }% M; Y$ K/ T& o- _/ {% F- A) q/ \
要点:
; u7 W7 \/ P) [2 Z/ f; C在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.$ }7 z8 B1 k/ W
" x- a4 B; w: |$ {0 W3 f2 i
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
6 u, {# ?# K% d; V1 }0 k+ e9 c
3 o! _- B8 U$ [, Q& z% g; ^2 s& V# ?! B& `! Z4 c* i" L

( _' d& k3 l- E, L7 [
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,) Q4 z1 ]# g) C; W4 i

3 m+ w4 z4 C5 i拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引
9 c' ]- l( F3 _. w5 K$ U5 a/ n$ v% J/ b. s0 `

; O, `0 N' D3 U7 w1 |5 m- x& W- h7 t0 o" Y* q$ W
一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索
4 J7 U0 V* X' v* H: Y0 j6 L# f& L- A' ^$ H1 H0 N" p, l" b
引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。/ T& f! o1 h& D4 T1 \

( e0 F9 c: @& I& ^7 e8 k0 J! W  H
3 i' j5 }0 x7 B0 V: T; x+ v% @( M  }* j9 \1 l6 Q- C5 k
一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。
! {- X2 d5 r# p. R5 B: [  s7 ^4 Z/ Q. I( b
例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理* \! h3 u& i- b3 ^3 W2 o0 p% H

( e3 U2 o6 Z8 d( e  o这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式
7 n- h  A! K4 p9 i8 M# R
& a$ g- J  ^4 ^3 G7 i/ s
, Y+ x$ F8 T, N% L3 U! N
3 n: l! @+ J3 F6 d" s/ q# I( Y+ L避免多个范围条件,只能对其中一个使用索引6 m8 X& }) x( w/ y. r: S7 `( ]

/ h. S* N9 n+ p% H8 S
# g4 s( b. O) u3 ]
8 {; f+ U9 ]& E8 r& ^% H5 M/ {索引和表维护0 c; u3 W! \- ~+ Z

. J" z" B2 {$ ^% K9 a& B表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.
, R0 {# ^5 B3 `. ~# F: K
* m0 u) {# S6 scheck table table_name;: c1 }7 o8 p1 Z( p5 B( H3 L- \1 U
repair table table_name;' F3 X# k% b& k* @* o1 U2 M; Q
Show index from table_name;检查索引的基数性# }. c8 v; d" k1 Z! `+ j' y  Q
: m" ^9 N% |3 ^/ t/ b2 v7 u" E
主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量
: l0 K' `& }+ Z2 r6 h8 j
* C% m: Y, e4 \
5 i+ A; c$ G" J3 X  T. N4 m9 N0 x2 H. R6 ]
B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。, [" @" \: M; y+ l! C) x* z& H% |

- V& U, x3 E' \9 S4 _8 _9 X/ l' W6 u: L表数据也能变成碎片化。两种类型:
! E4 D$ f2 |+ b. }  ~, b9 X# C$ P) F
1,行碎片6 a  j! q3 R# p& a
$ M. ^9 B# H  K# y; `+ x0 y/ `
当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
: C" O4 x9 Z, t; s' x" m: Y- ]' m0 i1 c% I5 `- _6 K) |
3 N9 L4 j. }: s
8 _$ W7 F+ h# o% X* A  W
2,内部行碎片" T3 d* a' a9 j5 Q

  }( G# j0 j/ }6 u' X当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和
' L2 g8 e- `, Q- i# g; E( x5 |5 {5 W
% _# c/ G/ |/ O9 L; o聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。
  }# N, U/ b$ q! o1 F& R4 H% ?5 n- q+ e9 k; c) V+ |

7 L7 J+ e5 d9 y
9 h) C" `6 E' i* P: g5 |* }为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。# c. ?1 h+ Q9 z# M2 A' t* b
: u8 ^7 G( h& ^1 A4 d6 T
; d$ K8 O$ x& d
# a# }$ ?/ u4 F% `
ALTER TABLE <table> ENGINE=<engine>9 w2 a% p* _* M. m3 Z! s+ x

3 k9 k1 C1 G* }. }$ \/ {' m% e4 h8 \! Q6 j

& K4 {3 n: K: W加速ALTER TABLE/ E- d* Q9 S5 n' G
- L4 Y+ X1 w4 d3 x* H5 A. \

4 L: c; o9 \, A( e% I! q$ v; i. {5 S. @( p
MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需
6 Y- v& a: N2 N1 s! T! U6 M& F7 k' p, j# W/ |, c( K! D
要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,# r3 t4 \1 A: x- {3 Q7 [9 U
% `" m1 C* c; J0 N+ W/ H
而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。/ ]& Q' i! v: g+ L" a6 ?' w

& L+ U# }$ K: X* T% I# @& c/ K传统:
3 m- _+ ]& e0 T; A* f, k2 i* Z- f1 b! Q) a) s5 X( V6 _
ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;
9 o6 n. X0 Y/ r- b5 ]! T理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更( s9 k+ `. C$ d" ^3 [
改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。6 }9 m& Z) k1 D0 V' d- H: e! f
' r" @) m" @4 v" a( e4 [
变化:+ ?% J1 ^  K- d* F

1 }: B! S  [: [9 s$ y5 A: |ALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;
: z/ v$ y* m: @* O这个命令更改了.frm文件并且没有改动表。它非常快。9 g6 @8 k* U6 p3 U/ h
还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2026-11-18 19:57 , Processed in 0.047008 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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