请选择 进入手机版 | 继续访问电脑版

召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 767|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能3 g8 D  a* V- J5 z
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
' x5 k1 J; F/ J. M# Q4 s* X一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
+ V, b5 `/ s$ s4 O$ U: S在InnoDB中,只有事务提交后才会解锁5 Q: I7 i# R  o& J2 k
2 A8 }- d# d9 t5 E# x
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只: R3 e' i7 [8 ~: O8 N1 r: U( C1 H5 o
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。% i- x: @, j. F" n) {) w

6 m: w& X) G9 u6 yB-TREE
7 J  j' ?" O3 r2 G% m0 W. {7 x能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
8 l, \' S& F2 Z( M  y, C使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。
* b5 O2 ?5 N/ F+ }" h1 V
  1. 9 T8 C" Q, X: i" Z
  2. CREATE TABLE People(8 F; B1 M/ ]7 X/ h, Y/ D$ N( x2 b
  3. last_name varchar(50)   not  null1 s/ [4 @, }3 W7 @: P( A6 m
  4.           first_name  varchar(50)     not   null) @7 B2 d: l2 T- d. |* j# Z
  5.           dob  date      not    null
    1 L! c! G2 c  j6 {4 r4 _; V: p
  6.       gende       enum('m','f')    not    null$ b  m) a/ a* |
  7.         key(last_name,first_name,dob)
复制代码
匹配全名' H( L; r: f+ w5 A- F
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
$ g1 q& k& K' Y6 s' q的人。# E( G: l: v, }0 s  X: G
匹配最左前缀& a$ d3 h; Y5 ~3 W- K: O, x  Q
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。% {: ]  w; o( W9 l! Q7 b1 b' n& L
匹配列前缀
2 v' x9 a% g+ m( a" {: r可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。/ y" a3 K. z: g6 p
匹配范围值( `- e0 O6 E, p4 K, l* i
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.) I0 z+ `  C5 _) T+ Z! t
精确匹配一部分并且匹配某个范围中的另一部分$ ^8 T2 w( s+ b9 E8 e5 L' W
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
+ j% \/ V. W$ o! m列并且对first name列进行了范囤查询。4 S4 ?, D6 S( Z! \3 Y9 k) L0 n7 {
name- P1 M' a4 X# n" G5 \5 ~4 {
只访问索引的查询. d1 b' b6 T( m$ L0 B8 q, d
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
9 }9 [# P* y  C$ ^; G' h" h1 l
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,6 p# W0 M4 g: r0 T! Q9 k# w- }4 Z5 ^' l
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查7 v* o4 Q; D( w% L
找方式也可以同等地应用于ORDER BY。& j; i! }) [1 L# _
+ D% `- V/ I! A' K( ~! I
下面是B-Tree索引的一些局限:4 t; B/ }( J8 Z2 [

3 j& S: P  G1 D  a. `1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
+ R6 N+ X5 X- x8 J# @也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓
0 D9 u, {& v5 n+ E3 ?. O氏以特定字符结尾的人。
  P/ Q& ?; F4 V5 G' Y
7 b5 [% H$ G/ V5 {2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
$ m* [+ G$ I4 H8 Q" p& \9 e4 V& S: [义first_name列的值,MySQL就只能使用索引的第一列。6 e1 ^4 B3 k" J. o6 u: Q

1 O0 R# {5 h4 V3 h! J; @% y" |, U8 D3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是  ^: g# I& U! ~" L
范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而( t! R) U+ a9 F: j  H
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
( R1 g7 }* F2 Z5 z  k# p: L1 M# q9 B; o  t$ k2 A( p: ~
哈希索引,空间索引和全文索引等,暂时没有设计' j4 z7 T1 v1 z1 b; d

. a8 z5 Z1 }/ i高性能索引策略
( c& X0 L1 u' ?$ L4 e# r/ }2 e: {. z2 e7 O3 C& Q
1,隔离列,意思就是不要对查询条件中列进行计算等操作  z* r* V/ l/ E$ X( K3 |
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
- e' A" U9 u* ?. |4 |) d' G4 SSelect count(distinct 列) /count(*) from table;
' K6 o) a- P5 @- [. r2 H  n$ P看看这个值时多少,如0.03124 I- Q" W0 [  P2 t& ?
那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算$ Q4 L  ^) [+ Z, c$ w$ j% W/ B
,这对于大表很有用。7 M4 r0 y; z( \
Select  count(distinct left(列,3)) /count(*)  as  sel1,5 K( V, L6 {6 }0 w! W3 P
count(distinct left(列,4)) /count(*)  as  sel1  ,9 {$ g, f2 i3 k. o7 ^4 L
count(distinct left(列,5)) /count(*)  as  sel1,
! [0 F( [7 p. @! X& g5 _ count(distinct left(列,6)) /count(*)  as  sel1,
) K; s& X3 e9 ~ count(distinct left(列,7)) /count(*)  as  sel1   from table;
* F. s9 ]- ?8 G- n6 X找到接近0.0312即可。
: M* C3 j; u5 R$ [6 E5 F% B8 Y
2 Z. Z- ^2 f% M3 t# uAlter table table_name add key (列(7))
" e6 A* @' @- {2 ~. Y3,覆盖索引, }: n+ f, c. J' X
包含或者覆盖所有满足查询的数据索引叫做覆盖索引( y: J) ~' [( G1 J3 P: L4 v1 c/ o
explain时,extra中的会显示using index0 @1 M. W8 R; I' j  @' G
这里一个重要的原则是
& t. p* Y2 l6 i& b  wselect后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列0 L- Q6 Z3 y  j# Y- _7 X# o
如select id from table_name;+ J' o/ s9 b6 Y! r' U7 h6 R5 a* E
+ F  m( g; D3 e
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
4 {+ I" k3 J2 s& q5 n# e一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
% n2 W+ n$ R7 ?9 K# d8 S* E( V& P1 h
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';
    ) U6 |! `9 j0 g; ]& s5 O
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:$ x8 x% s, R# O8 N) d& c
1,
$ i, x9 @# T. Y" i/ l没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。% t9 j( \) }& Q9 B8 G4 \
2,4 D1 G( E, `4 {* d7 r! O
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
1 U4 `4 [0 ^+ v4 I! Z' u6 e有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:7 f$ C0 ]2 ~  T7 F

: O& i  ~- ?% g4,为排序使用索引扫描- T3 Q6 [2 W" P% }
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。9 }; _( a# d! S, U
explain输出type为index,表示mysql会扫描索引7 T0 j2 ^% ~; i
5 }0 U: C$ p; Y, e
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.' i: b; |, a% L  S  R+ _
8 O# W: r$ [2 o  M8 |( u/ D0 B
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。, m( a  n8 c4 ]5 u, c9 ?  L+ Q
1 b. I' g* I# k9 z* [/ f
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。+ N9 R" n5 R+ x8 d

* {- y& r' z. ]/ ~% IORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。
. R) W7 V9 d5 u9 m: R, m3 d5 s2 {4 v- z1 i7 g
使用join可能情况会有不同
, S5 r" ?% D* H; W$ @
+ s0 B' q, K6 B& J5,压缩索引(myisam)
+ o  ^. b+ H; }6,多余和重复索引(应该避免)1 |( F! J; Y! _) Z+ @: L" r# a
/ f5 m" j5 d" S" ~" N1 R0 \4 g
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
9 O$ x3 P" Z, h- o$ s- ^上有索引,那么另外一个列(A)上的) Z4 d  G! |) _5 Z
索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)0 d) ]( k) L" _/ f& Q7 _" M: x' O1 x6 o
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
5 _! {9 v, T: z& R" R$ K2 J' C3 M! T5 |* k- ^/ Q
要点:" @) I. h9 v, w! e  L
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.) z0 D6 {  _7 y; R  x" @- N

, O$ X! g' T5 T7 y$ ^, g  _8 a即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行+ Y9 y; S0 `1 l; g
+ W& f4 B" O, ~+ Z4 h1 m

# g/ }* Y* N5 m2 J9 G( W/ |6 ^
" D4 D) C! D7 E( O. Y& X
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,
2 T" |: ~+ b4 A0 A" J& A& W% P7 u. v( H
拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引" h) k3 Q# k0 @/ o$ F

# g* ]5 }% L) [- s+ i8 b1 ?' L- K4 l# v+ m' z+ H

& D; l5 X' e6 g: z+ w一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索
5 k' m. I1 O( r" R- Z/ E0 V4 P' m6 B+ d" R" |6 S& \% G+ f: K+ X; P
引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。+ J% a* j! J+ B, ^

" @5 r) ^# j/ J/ b$ H6 ~9 ^8 b% n( |% k# h/ g

4 a2 _9 x9 J2 r1 S6 H一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。
+ p4 p; \3 D! E. V/ k+ R+ b' I" L) D& u+ N8 B
例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理1 q: H- \1 z$ s

; {% ~# [; v' k/ M1 U& c; y这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式
* C& K1 R& x: x2 E5 F2 W" q
8 c8 a6 q" |7 J- B% G1 {1 C3 A0 v% i

) f! e7 u- m0 O" K1 f避免多个范围条件,只能对其中一个使用索引& c8 c( y1 V! o- I3 T" \6 q

; B$ K; g6 }; B4 R+ {# y1 @3 m
2 _+ B0 Q+ o$ T! C  N7 R( J/ d  F. b- h5 v2 v5 X5 d  g% B
索引和表维护
3 b' Z& ~8 F9 ]. n9 w' x2 M* M
) @$ _. E# _5 {6 C/ s表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.: q0 J6 y3 ^: l* ~5 K1 E2 T
+ |/ F2 Y, [% r2 J' j, Z" E5 T/ Z
check table table_name;: t* l+ _6 N! ?, B% q
repair table table_name;/ Q+ |+ i( L, Y. x
Show index from table_name;检查索引的基数性2 |3 g3 F& s+ n% N1 C+ D& A
, ]' R! S; }& B! [8 m
主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量# [' E; J/ a; [% v1 {: @2 q1 t/ R

/ v: o1 s# H4 d/ F) c$ M' k+ W- N+ Q3 r3 p
: ]: c- g/ z( j+ p
B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。
; w0 B5 e$ R/ i; f+ V! q& c; P! e( x3 I! ]/ b5 W
表数据也能变成碎片化。两种类型:
" T/ S9 C8 f6 B. Q3 D4 y8 @1 N
6 A* Q1 U' u. }1,行碎片3 p/ [( `. Y! e* T3 q
6 z  q0 v0 F  g+ ?6 ?2 ]+ S9 V
当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
" d8 F1 d! l! Q9 F+ o0 ]4 b7 ~6 K
# `$ O/ ?2 W) h0 }" }9 T
2 A1 ^4 k- G1 ]) E
+ |  W$ V) b9 ?% C  S/ u2,内部行碎片' S7 E2 n6 E$ b1 @1 X
* ]' D, H9 f2 p+ e* ^" S
当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和
9 L1 `3 Y* a3 s) k) |: f  ?( x
( n  o  m: h. o7 p4 \6 N. P聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。
6 X, d* z+ [& L/ W. X9 L
; z! s# ^% g, r& V$ c! V3 `% s
5 [2 W. ]" z) p9 e' F3 G2 Q8 c8 Y0 p4 e
为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。
2 I8 e4 z: p$ t/ T9 }  e0 ~( T5 R, c9 p$ J# G+ M( O/ J7 J* U

9 {* L# @# @' ^/ E! u
5 d  D  t0 L: R$ h7 gALTER TABLE <table> ENGINE=<engine>
" M! K3 }6 _* c3 o, y
& A6 v9 x* ?* S# a1 x( w2 k! v& f' W( v9 B: y' G
9 [1 ^6 c# ~' @  g
加速ALTER TABLE
! p7 j3 y9 e2 ?/ a
- C, l- U5 u# i6 D, _# n! ]
% h8 L4 B$ x0 Q
! F* N! s/ F. V% L1 F. n$ FMySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需
/ D9 h  V  a" h' B3 N' C! E! k2 M( [- U0 H: ]0 c  S: U) G+ X
要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,- e) ?$ V  W' \

$ ]6 ?: \! ?4 l而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。' c* V; G- w! k: g, o# s( `
/ z3 {& G9 {& N' k
传统:
. T) `# N/ h: o$ q/ `% I
8 G) _& h( f4 h$ `* M- Y$ g; {ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;
' R; Q' v% p5 `/ `/ i- K; o理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更
4 f! A+ A. Z/ q# Q0 D# P6 W" C0 I改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。8 |- }' d+ L6 W* G
3 Q7 j) Z. s( O7 f9 E7 \
变化:# k* ^% L: n3 _' j, ~
1 L# A- _" V! S! y1 P
ALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;
- n, [9 ~+ J4 f$ p! }这个命令更改了.frm文件并且没有改动表。它非常快。
4 I- J& q3 K  U) V6 A% n还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-3-25 07:12 , Processed in 0.043866 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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