召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2665|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能+ g/ @% E$ U: j8 @3 k! |2 G
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成. ]# W' W0 L  g2 Q- Z' k6 J# {1 ]
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列  D& ?- c" `  b
在InnoDB中,只有事务提交后才会解锁& Z2 K# P2 `- G
3 @+ ~6 ^: Z' p' Q. }. s
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只
$ V+ s4 S6 L! i1 E! W能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
) C- w2 w# {. ]' @+ M
2 b/ L% T( r) e$ dB-TREE
. B; {/ T0 H! K6 \能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
( ], ]4 L+ I. t, L使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。2 s- l8 F9 x* K" o
  1. : z3 {' I  V6 Z! y8 y$ N( f: j
  2. CREATE TABLE People(/ a; l+ A+ d6 \4 ?1 M
  3. last_name varchar(50)   not  null
    ( x4 m# ^$ T$ w/ i% M
  4.           first_name  varchar(50)     not   null9 S6 X1 j% h& o; T: d2 S
  5.           dob  date      not    null
    ) S% j! c# u4 F; e, y
  6.       gende       enum('m','f')    not    null4 l4 ]. l* y1 }* T
  7.         key(last_name,first_name,dob)
复制代码
匹配全名( E5 r' |, Z! _
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。) l! V7 c( M; o
的人。' L' Y: W4 Q0 T# @2 ]- ], W5 P2 t
匹配最左前缀8 h- d- K" _/ ?
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
+ ]( t) S9 z- I& p匹配列前缀
2 \6 b. p& l1 ^4 m5 @可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。0 s% }/ r, r! r6 `9 C. O
匹配范围值
& }+ T0 X+ \1 K9 A! A& |这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.& b1 t* {* n! _3 x* C% g3 G6 F
精确匹配一部分并且匹配某个范围中的另一部分0 c5 M$ Z# v) [
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
* ?3 n" T* C) d) ^. o列并且对first name列进行了范囤查询。
- P. u& ~! G) x6 Z' jname
( ^" p7 _3 X( ]/ ~% B; `! q% _) A- [只访问索引的查询' h" e: }8 T9 U; \; u+ Z- j
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
, G4 D3 F7 H2 K  d! Q3 [0 S( s. g# C6 F. G
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,
2 X  Q( c. r: D6 i) p如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
& g, k* P% K5 n  W, z找方式也可以同等地应用于ORDER BY。
% ~3 ^" }! `% s5 T; z- z' v' x
" ]9 p# o) f( m, a' K下面是B-Tree索引的一些局限:
% k3 y6 M4 F9 W8 p( _. u4 U
' ]" N9 m  `! A" H2 ?4 c1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,& V' e/ ~9 o3 V- ?; X
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓
' o# g, h; m* S7 [" O氏以特定字符结尾的人。, N, |1 ^8 d! y
8 w7 }  q2 O' o; T1 O3 p4 P
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定0 R! e; y/ B' T5 F1 ]
义first_name列的值,MySQL就只能使用索引的第一列。
. F4 M! O) o6 Z; x$ ?
: b; J8 i# y, i3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
& D" A' S/ u- `1 ~0 C0 C范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
2 s% Y0 E1 ]( o不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。$ ^  p( U& y  i
9 l: g  [% S% V* E
哈希索引,空间索引和全文索引等,暂时没有设计7 A* `& x0 C; F! R8 _
* G; w. U5 {+ c8 z7 w) q
高性能索引策略
1 q, a* h2 z/ ~* b0 Y( z
/ l$ K/ [, u$ f0 j0 y8 H/ ?1,隔离列,意思就是不要对查询条件中列进行计算等操作6 K  \1 f1 J/ I  r) L5 b
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引. g" B( s0 o# K6 b/ E
Select count(distinct 列) /count(*) from table;) N5 c* M+ F; q4 R9 Y
看看这个值时多少,如0.0312- @2 [! r; U. g: S2 j
那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算- u+ ~9 G3 ]1 B6 K9 l
,这对于大表很有用。
; g, n9 l2 ]! U2 v2 e( q! oSelect  count(distinct left(列,3)) /count(*)  as  sel1,
7 v2 u4 e* @: j( b! B$ @ count(distinct left(列,4)) /count(*)  as  sel1  ,$ Q1 ?6 x3 u* D: ~! R8 Z
count(distinct left(列,5)) /count(*)  as  sel1,0 Q6 ]* M+ i( O) u
count(distinct left(列,6)) /count(*)  as  sel1,
  `0 `. d$ a9 S0 S+ O" }- u count(distinct left(列,7)) /count(*)  as  sel1   from table;
" M! {6 v! h7 ~+ R找到接近0.0312即可。
  w4 j! @$ H8 g) v# P% Q% u( e5 L4 j
Alter table table_name add key (列(7))
- p) b# l8 |+ |: Z, Z3,覆盖索引
& D/ r2 J& L0 k* q# y* b, h包含或者覆盖所有满足查询的数据索引叫做覆盖索引
- ]( s2 o2 C* o- f3 X( e' Zexplain时,extra中的会显示using index
! A5 R2 K& S- X6 k" H5 W  u- ?2 c, K这里一个重要的原则是
: j4 c3 J3 y8 I* Lselect后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列3 o+ l! [$ w7 F
如select id from table_name;6 m1 t: i3 v! N) g

) ?6 h6 h9 r) u* e4 M很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不  i; S1 P* u) o* \2 o( f6 m
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
6 }- `$ d8 P! b& o0 Z) P
7 c5 g. G0 o- ~( L3 q
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';
    % T0 J; w! ]/ |' J' A3 R# _
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:) h4 _( o6 \& R0 X4 i
1,, O7 `1 G: r1 {
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
6 k) j! \) G" [' g2,
3 t1 z& {  {0 z7 r  Z: fMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。& q6 B% s; x5 z5 |
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:
" L( {: K7 N4 c  N0 ~- m# Z2 P" U/ P2 t
; }& w3 z7 c2 d9 V6 b3 G1 [1 \" }1 i4,为排序使用索引扫描; _1 Z: @- g7 L0 e  z4 `0 @
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。2 q2 Y4 p9 u$ e  \. X- u+ r
explain输出type为index,表示mysql会扫描索引4 d7 L& }6 \9 e  i- R2 V8 z( Q! M: y' j

. O# [0 l+ _6 Q2 p扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.( I7 f) O( Z2 t0 Q: N
2 J6 y& Z$ q) y# V
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。6 k8 Q/ b3 q5 [) o: f

  E' g1 D' p& Z- p7 R& O1 A" x2 d按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。" p  U; w; E, I) J5 U6 {

% ]+ O0 h8 \# `ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。) f# l4 G1 B2 `' b

$ g; G5 m* Y5 j$ t0 I使用join可能情况会有不同
7 P/ G3 j- K1 e! |* M" J- i9 P8 e0 j7 b6 ~: n! _
5,压缩索引(myisam)' P+ z1 a1 M  D# C
6,多余和重复索引(应该避免)2 w( `. L  i) [; {' s
. I0 }( [8 V  I; u0 w# E, E( l7 S. l: m
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)" K1 n7 l" z. z9 I9 d4 b. M
上有索引,那么另外一个列(A)上的
% E0 n0 Y: X. M索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
4 O7 C' k" l9 j8 \% K) d% {然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。' E0 q; I" }9 i% i5 w6 H8 F+ z

8 m* t! T$ A2 m要点:
9 G3 ^; p' D0 H在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.) s, _; X& p3 K: w6 P! m

# |8 \0 @( P  p8 x6 O( o. F0 N2 F" e( M即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行# B2 L6 W% \/ b( X
, D$ J: ?  o. `& V$ K
6 s. u1 f1 R' _
. X! K; g* z& }9 b. x+ l- j$ u) |
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,
/ c6 k2 p9 u: h. A1 x3 L8 m
/ j; _2 h7 v0 i2 Q) [* n3 S; E8 T拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引) i7 l. a5 t, L7 i* Y& r
! w, @. g1 T8 |- p) o/ F

, C9 }! Q) {, \# q  `) a3 l
0 I3 z6 W) i9 u0 f& q) g一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索3 b4 O: r& F% j# k6 }
2 ^; d6 j7 r9 l8 \' t! O" {
引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。
1 j3 V. Y' B: Q6 s( u7 `7 L8 M) A! V8 N* [

' h4 r( a" ]( G) y5 K# C4 O4 t/ ?( O+ p4 l1 m" L+ y4 w5 |
一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。" t4 R5 H* u3 E, q% e
# X+ X/ {( r$ l! h% x
例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理
1 A- W' \8 D% D4 r: V
& ?* J8 ~2 N) ?这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式
: M8 _2 O3 U* X9 {2 Q
7 o* @& }4 c) Q+ q
! J! D6 @; V. B8 @: m; [' f# O$ R8 H! I# V( _/ [- g( o$ w
避免多个范围条件,只能对其中一个使用索引
* _4 o2 E' j$ a/ K% q
/ u0 k& I/ B; n: x# s" z- y' O& U  W& X* G4 M2 e- h2 X
4 O& Z# I1 n) M' m
索引和表维护
9 O( f- K' U# C* r8 u" l/ E% v( f) ?& Q! G$ G
表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.
  _" p0 w) u  z) n9 T2 n
( y' q. N! K4 G" jcheck table table_name;
0 a/ _7 T7 I4 C5 Arepair table table_name;. J1 [# W; I" G& ?1 s: Z
Show index from table_name;检查索引的基数性
+ @  x* F4 _9 I( r# r8 J/ p) g& E* G
  [% g! W. ~& |9 S7 P8 U主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量
8 d% K' L! ^& \1 B: K. l) `  {9 Q/ M! o
- Y: h) M4 X8 i- C  N' A& y: d2 q
$ [& ?# R+ J# T7 a, a# D
B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。. F* n$ n/ V* T- i/ k! N

+ D7 n' x3 I6 a( }表数据也能变成碎片化。两种类型:* y7 o: G' R% E/ N5 M" j

' Y. J- L: [- P+ Y3 L1,行碎片5 a) Y! {5 b  a  r& U- c( c6 @( @/ ?0 l1 S
! h, ]9 v& E$ s: q; y
当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
0 g& D& p: [- Y( C) v
1 W$ X/ c& n* q7 y( [9 y: ^3 q, D+ E+ u" E9 c; P7 }

; w5 z% @3 S( Y% g) Q5 Q2,内部行碎片4 X: Q  t. q' |( J% W6 y

: n: P+ I  N/ D6 `. A当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和* C9 h0 D; _0 A

' G2 V8 j! j' k% L- u- ?/ Q聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。
  s2 X' m2 y3 L4 Y( X# I/ p6 F* X3 G, N4 S1 `  [
1 R3 j5 ]8 u+ ^5 Z1 P; X! h

: I2 X, b- ^/ Y+ Y/ Z7 D& g  J为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。
1 D5 L- \# a2 {2 J. Y1 M$ m9 j6 ^( g# }2 c8 I

* r; `" N- s6 y& d% d, P1 h
+ O+ D4 X1 x4 L2 ^2 x: Y- cALTER TABLE <table> ENGINE=<engine>( A' D/ D1 Q3 r- u

2 U7 L2 N7 n' O" I3 j5 o
* r- `, Z5 }$ V4 l6 u- T0 M% L- Z: {) x; C- K3 \# r: y
加速ALTER TABLE
6 `, c+ }) z9 @# M( S0 }: t8 R& ]
6 ^) i9 ?% J1 f' w3 i8 G- D1 [$ C+ q) B/ z/ H' ^6 S
) @9 Q( ~- P3 h2 [% e, H- g
MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需3 q1 W$ |) c- v9 C% @

* N! v5 E% h$ J' B, I5 V要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,  f. [1 U# X: L9 T

  M5 q0 V1 [! {5 ?: e* R5 v, D而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。
& O  ~3 R( c# U2 s0 i' I! q1 y/ {1 {2 ~$ B& n
传统:) t, u( J: m( j1 ]: K0 ~0 ?5 [

* l( S% a2 u6 M* U. h7 L1 s" \. eALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;
+ A& V0 S- d' L1 @: X9 T理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更7 _- T2 d& l& ]8 c: D# y$ n: z: f
改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。
# D' K- e* l5 i
: x/ Y2 k$ }% K- @: L( j, e5 R变化:
5 T# Y& t; _- N
" V9 j5 [2 ?' _' p8 ~) S/ b3 XALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;
1 h' M7 H: Y% D' s, |这个命令更改了.frm文件并且没有改动表。它非常快。
/ h% r  b; h3 c还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-9-18 01:04 , Processed in 0.038387 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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