召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2682|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
4 R9 G9 W% N) F  _有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
9 m( a! t" F% j. N# _% n) P; o一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
! X: N" M3 d# f: r在InnoDB中,只有事务提交后才会解锁3 t& L: `# g8 ^) r- h/ R

/ F% w0 H" K1 U索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只
, S+ ~  U4 _3 ]5 q5 B4 R能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
1 y, Z! g. [9 o$ o* m4 L$ ]6 X9 |( y6 b9 \8 V
B-TREE! e2 A! L$ J9 y$ V( {/ X# _
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
* @2 R" [9 ?* g使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。) z! u; c* y, p
  1. - L' c; F; G: _! L) X
  2. CREATE TABLE People(
    7 h& k2 `$ ?* E' h& ~3 ^
  3. last_name varchar(50)   not  null  F. F% F/ ]) ?4 `4 |
  4.           first_name  varchar(50)     not   null+ F' z5 E5 G* C
  5.           dob  date      not    null
    . Y: H2 x1 X( q' j# ^% U
  6.       gende       enum('m','f')    not    null
    % S: r" J& X" Y# K, n: X
  7.         key(last_name,first_name,dob)
复制代码
匹配全名; _+ ?5 M/ C. T( h
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。+ p# S2 B( F  X4 ~' B' ?/ w3 V
的人。3 {8 U# h4 o' W% u* y9 t
匹配最左前缀
8 {( i( p9 U) r6 X9 ?+ ?/ p$ JB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。/ }8 u) R, p2 B3 ^
匹配列前缀
6 r) [2 D* c$ E0 |" i& A: H1 L可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。  J' {4 H# L& n$ o/ T: O$ Z# G
匹配范围值
6 o* _( o, L: u- ]7 v' M& F% j这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.' O: n3 c* @. Y3 d4 \! I% V% H& S
精确匹配一部分并且匹配某个范围中的另一部分* R: g! _* E* e0 P/ Y1 T
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
% i7 q5 S: D. Y7 s9 _* b列并且对first name列进行了范囤查询。9 S* [8 {( D/ q0 W& z2 e7 G  U
name
" y- R" w, N# f% |* W/ h只访问索引的查询$ ^5 S8 W. P& \2 P: l1 u
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
" J" s: D9 `- x( W* j% \
# s1 d# S# w) ?' H) a由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,  m7 F! H6 p" z
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查7 o, p- @6 U: W) c2 Y. G% Z9 u2 k2 o
找方式也可以同等地应用于ORDER BY。
7 x! U+ ^6 B6 c" f- Z. T
7 k0 H& i5 u7 n/ H5 H9 E8 V9 U下面是B-Tree索引的一些局限:* Z+ h4 O( ?) A/ o5 ~) ^' Y- E* V' w
1 {; `1 j4 L3 d9 d2 l$ [2 u
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
- ?6 @! l4 d) X! K0 n/ S也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓' ?7 ^7 U! n; l) Q3 d+ L! O
氏以特定字符结尾的人。
2 q; }( X" b; e# q- w3 f  _" _8 m9 d# r, ^% f
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
. F  R  u+ ]: _0 j- E! I& _义first_name列的值,MySQL就只能使用索引的第一列。
6 n/ z# D2 k1 s" E7 d' ?$ C( |
' d- b" ^" b" K( P" G3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是- Z( o- B) f0 `$ J
范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
6 N/ J2 O! @  f不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。- E; _9 w  E. Q" w) a

7 \1 B7 S; s% B% \6 X! t4 e0 X* s哈希索引,空间索引和全文索引等,暂时没有设计- A" b1 ?, C4 m9 @
2 [: q* T9 Q/ h) j2 M+ R
高性能索引策略, d+ C& O$ J3 [7 U5 l3 C+ m

- J" i2 e$ B7 Y" S9 _4 l. |1,隔离列,意思就是不要对查询条件中列进行计算等操作  T! X. Z, H* V
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
( B3 p/ l- k% i% c! M) g  ySelect count(distinct 列) /count(*) from table;0 G# ~  S% @% z* C3 K
看看这个值时多少,如0.0312
" ~( {$ @; ]: h+ l9 m0 }! ], e$ ~那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算
3 ]& Q5 t8 `0 w,这对于大表很有用。+ L5 o$ s+ c  Q7 F
Select  count(distinct left(列,3)) /count(*)  as  sel1,2 F, a! V. J$ T
count(distinct left(列,4)) /count(*)  as  sel1  ,3 R& k+ v. _( }* o  ]4 X0 l
count(distinct left(列,5)) /count(*)  as  sel1,* [4 A; b# u9 |! Q1 i
count(distinct left(列,6)) /count(*)  as  sel1,4 Q. W# t( e8 ]
count(distinct left(列,7)) /count(*)  as  sel1   from table;  @! P6 n+ w* [5 M' [5 G8 t
找到接近0.0312即可。
) M7 Y. o" N5 r  n5 C4 n% ]+ b7 ~$ S: e" d' \  Z
Alter table table_name add key (列(7))2 ^4 N  j5 |5 P3 V, S! j8 `! t
3,覆盖索引
: \7 i( y, f/ v+ L( [% R( K包含或者覆盖所有满足查询的数据索引叫做覆盖索引
( H( y: w' \) \- b2 Oexplain时,extra中的会显示using index
* ^) I( ?: }" K, E% I这里一个重要的原则是
1 a, q; N& n; Z0 Dselect后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列' @0 O+ h  S4 z' a
如select id from table_name;
. Q' }4 n$ Q3 j, v/ @5 i, W4 s' ^5 L  x) }! V3 ~4 `
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不- q# j3 X  u, q/ Q) i3 G8 M3 w
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
0 p1 _6 y4 n6 U/ a
3 j* m9 E1 w5 @9 a& H& A! Y
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';
    0 D0 E2 W' p9 A% K
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:
+ D7 L7 }) d4 n( }  p1,1 D) ^$ ?/ ?% g$ X
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
3 W- [# e% w) K7 r# V2,, S3 x  j5 M9 u! i) P$ P; U
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
  w% t* s! v! y& _& w. h/ U" _有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:3 o8 ^% N% ~5 f2 Q* x4 x+ U3 m
! S8 F3 m& b$ ^8 n. L
4,为排序使用索引扫描. B  u, C% ^) {  S0 E
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。: B1 M7 g( f) w1 {2 W
explain输出type为index,表示mysql会扫描索引8 Y8 `" n3 D8 m# p3 u

4 w- ^" e# A' P  M1 L扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.) G7 Z) j4 \2 t& g" d1 b4 r1 [

' U3 A1 ]9 e" P  t: @' M: nMySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
/ t7 @" M( z+ W. d. a( a2 M8 @" j. a( m/ N& w
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。) `3 Z, v$ P/ E3 j

3 ^. D9 R3 r$ u) RORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。
( |6 D, [" a: H9 _" v7 m( ^+ t
1 s7 m2 h" ]- A6 u! C/ ~* Q/ p使用join可能情况会有不同$ n& _: M8 P- F4 _+ g5 a+ ~
) K& k- }; ]5 f) _
5,压缩索引(myisam)
& w0 n% x7 u' ^3 `# e6,多余和重复索引(应该避免)8 X$ A4 A; i: _# W9 b- I2 i5 Y

, o5 K; \$ F- R8 v: j6 L1 i0 w多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
' b; Y- v5 V% }上有索引,那么另外一个列(A)上的
& n6 o3 M' ^/ d9 l# V索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)3 w# n+ l: t# v) [) @( V; U0 p
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。% I" ?! m* g9 C. p: F# W
6 b. ~5 a. m  }5 F
要点:
) l7 v3 v/ L$ P1 y5 d在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
! B. `* S+ ~6 a3 J/ Y* r) d% q" W0 r: I  g% K4 `
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
2 e2 L. o2 _( K! U
3 j5 p9 U; |9 F2 p
  E7 }: F3 d* r1 J/ d7 A- G3 o0 `1 o4 `( r$ x9 Q3 x, r
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,
* u" g: Q$ y& ?) b5 k( R( A& p- h0 E; c: o5 R! d( r
拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引
% {0 C4 U# j/ T- Q' k% x1 D' H1 A0 d3 n/ O" U  S5 E& d6 h
7 z8 ~+ L4 Q( a" j
  P) p, e7 J$ ~( l& ^' G
一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索0 a$ t. Z3 A5 W" J* F, J! @

9 o; D" `& m. X8 m! F引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。; z& U1 Q7 J9 e% j7 Z) u7 L7 h" f
" g6 n5 O% H! \! l0 U; J

% D9 w% x; b. P3 w& q* U, q3 c& R8 V- }5 |" ~0 w5 S& ^8 q
一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。
) Q8 k: T% d; X7 [1 Y6 |4 G
' M& t" }0 P( q  M例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理
  I  M  \, H! j8 K
2 U) K/ `* A5 `* h这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式
; c5 v# w% w2 p3 r# Q5 [/ p1 |4 W2 `
% L9 H- V6 x; o5 Z

: k2 f7 @6 ~; X* M* [避免多个范围条件,只能对其中一个使用索引' Q% P+ m* _; @$ |6 r* n4 |# @9 a  o
8 v0 c- C! q( b) f! x4 C8 q
9 Y2 o( W. n1 I+ ~- \7 d( K
; y, l. G# |5 _8 B
索引和表维护# y, F+ `( O4 I6 `' m* t

) M: [% u! ?3 l$ z; F9 o表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.2 @( L% a' w6 F8 a& B, ^

: d9 U% q( `& P; {check table table_name;" Z4 u4 V: Z& W; M4 ^! _
repair table table_name;6 u1 O4 k, M! G2 y+ w+ g4 O
Show index from table_name;检查索引的基数性
8 y" }/ U2 Q# y7 Q: N
, ~6 F6 x' m# u2 ]- K3 L+ H4 Z主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量
  ?8 ?/ A. s4 F# G% i
, i4 K; Z& m6 H
3 P1 D$ J4 [1 Q1 ?- C# g0 ?  E' _, B  C: b- o7 `1 H" r( K! e0 h0 `
B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。9 G6 K% I  W! u! i+ l7 \
1 n' Z8 q" ?& F3 a4 D- y
表数据也能变成碎片化。两种类型:. {+ E2 }2 w! A' e# v: ?3 n
/ K) \$ e/ b/ m9 v/ R
1,行碎片
, J* e) p6 t* I" r& {* g
" i& o* q% ?5 u2 h$ x当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
) Y( e6 y# A( e# h
. i! [7 {4 k8 I6 P
: u0 W' M- F6 D
& B  ^% ]7 l% z# ?6 I2,内部行碎片
. f$ n! r/ A' H' `  T1 A
; _3 h1 z' u) z9 ]" \当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和
* m( H! H8 a" j- E* z. e! x
# T- A6 r+ M. R$ H: ~聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。
2 Z6 }) G9 k" {% G
+ I+ \- O  V" H9 T7 v4 x5 t$ s  ^. V1 L- e

0 c8 u- l6 L8 g) [8 F3 l9 r为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。! ]* X+ t5 k" {" L, w9 k+ V
# \) V* L& O! Y- V- ]; U

  U5 Y+ U, T- p8 b8 o6 c) F& D2 X) R6 Z- n( B% P* V' `/ n
ALTER TABLE <table> ENGINE=<engine>
* C6 ?  K- l6 S( l& r; B$ r
0 o5 X/ A  _9 b6 a/ ?
2 o2 D3 l# P+ y
/ l2 x. ^& A; g8 G4 B, `0 Q加速ALTER TABLE
6 ?  L3 ^7 K: k; g! b0 ^+ _+ X2 N% T# s7 X
7 f/ f* [# \; }$ f
6 c. {# ~( j+ U9 C* H& ]
MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需
* ?9 `  G2 u/ ]* E7 r8 \2 }1 e
3 J# {8 ~+ j  W8 F5 ^2 A* ^要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,' ~8 q# w$ U2 N+ N1 \5 X7 z
, M! Z8 A5 T2 W( m/ y3 d- j
而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。* X  {/ L# |" d& l% n* ]5 \; ^/ n( `

1 j) o! ~( b1 U( c! G/ N% u传统:
) X; p+ c4 X$ r- L
5 Q( l% P8 i- l( }8 xALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;
# m/ R6 b5 U  ]; f1 w9 _* p' ]理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更, |$ s0 f+ Z5 q& x# p: x. `9 v+ x
改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。
" E) X# n7 ~# b. H
) @  W" P' w9 l* [& m, A- _: }7 i* y变化:/ E2 I  D9 ]6 d9 D

) t4 {* }" V& D- X$ T$ ]ALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;0 w0 `4 H1 s5 [. D/ k, P; ^4 j
这个命令更改了.frm文件并且没有改动表。它非常快。3 d, Z  O* M$ K* h( w7 g1 T
还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-10-21 00:15 , Processed in 0.036974 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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