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

召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 3056|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能( f9 d  m+ k' U$ x+ `  O
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成  h. a; M' [! E  G
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
0 J/ q' b1 f4 G9 r) ?& m在InnoDB中,只有事务提交后才会解锁
* S% Y9 N2 G# a& e6 N/ h  s
' p: z# ^* {) _+ T5 T" _, t; x/ i索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只
; B0 J6 y6 h+ E能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
+ H: \$ U3 _/ j$ a  {$ T- u8 ^& M
4 s" h& C1 z2 mB-TREE1 }6 w3 Y2 t4 b. w# V) a
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
# J8 d8 k3 k5 g, \使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。
: f) ~5 ?6 h' ]2 `4 Q0 J! j

  1. : y) w# m1 ]2 \1 Y$ g' U4 V1 e
  2. CREATE TABLE People(
    ) a; D5 f6 }. H0 g- z7 N
  3. last_name varchar(50)   not  null
    3 O0 q% k' d- u1 v
  4.           first_name  varchar(50)     not   null
    % K, u) L) m7 y6 D: S
  5.           dob  date      not    null1 k& l9 _2 F# O: |' u4 |+ o( D
  6.       gende       enum('m','f')    not    null5 ?1 C3 b) H/ \" O* J( Y
  7.         key(last_name,first_name,dob)
复制代码
匹配全名
, N  z, t1 [$ m3 K8 J7 \全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。6 F* H* k: {- X4 D/ Z  [4 c( Z. h
的人。
2 `- |' Q  n& X1 r" K匹配最左前缀
1 H" a) f9 h; ZB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
& Q" b0 V# g, m) N% ]4 I匹配列前缀
" }  x. @2 c& }, {$ X可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。- F9 ^+ Q, r0 S+ d, q/ |
匹配范围值9 ]& C* |8 u+ `! y4 {
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.) m4 B3 J( u+ K8 }# B- t
精确匹配一部分并且匹配某个范围中的另一部分
! X2 V; A7 a3 s6 V$ B这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
* s  _+ r) k* m4 j列并且对first name列进行了范囤查询。( X/ y* F/ s) K
name) `. ^: m0 V& v2 g' ]" _
只访问索引的查询
4 V5 ]0 Z3 x! o7 G. T" Q4 y" uB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
* X5 W9 V0 c% ~9 K8 l+ h# J" a* u. A$ x8 @" y0 Z
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,6 Y, }0 m( y& s0 m9 Y
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
% P3 h- e, r& V* c找方式也可以同等地应用于ORDER BY。' \  F. e; S" n: |* ?6 ?5 u9 L
* T% y" T" T( h9 F4 I
下面是B-Tree索引的一些局限:
  d! d: s# u0 c9 j! Z6 P/ _: p4 D/ O9 g8 o  R
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,  [. d/ h% f: S4 @% A$ ~0 X7 T) J
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓
' C7 Y7 M: \0 v. @1 T4 s' N氏以特定字符结尾的人。" }4 b; Q; ]1 y- r8 J0 o

$ C! I. L. p  N) Y8 D$ x2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定8 f( L& X0 o9 `8 P1 l
义first_name列的值,MySQL就只能使用索引的第一列。) ?. M9 z/ p; a( ?2 C4 u5 B9 H& R
. j1 v5 N) h7 w
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
* d  {2 t- Z* L: m; S4 r# s范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
. @* I+ j* S0 ]8 r不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。* O6 E2 v7 S9 Q

* e! Z. O/ K4 b* P$ L哈希索引,空间索引和全文索引等,暂时没有设计
2 k/ F0 W- N/ o* ^% T- Y4 J
; M0 M5 H, o- s4 J高性能索引策略, I. r3 j; \# p& y# y- A
( E6 w2 y! P* }6 B
1,隔离列,意思就是不要对查询条件中列进行计算等操作
$ ^! E, u/ ~- N/ ~! e7 W2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引# g+ u: T  t/ n# ]/ \
Select count(distinct 列) /count(*) from table;# s+ \6 y" l6 O9 r" e
看看这个值时多少,如0.0312
$ t. }8 z: ~. W3 N! E; ~9 r那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算! x. q. V% _0 ^- Z+ e. Y+ V9 F
,这对于大表很有用。
# r" z+ F  L# J) RSelect  count(distinct left(列,3)) /count(*)  as  sel1,* a& @# O' J0 X" F( S
count(distinct left(列,4)) /count(*)  as  sel1  ,  @! A  v' T3 ^- I( f% n0 N
count(distinct left(列,5)) /count(*)  as  sel1,
( g# I4 F+ V4 X- E, ^ count(distinct left(列,6)) /count(*)  as  sel1,
- x2 B# c+ r" i, x6 n count(distinct left(列,7)) /count(*)  as  sel1   from table;3 ^% o7 n( q# i. b+ @  L
找到接近0.0312即可。
0 o% ]1 k* Y: n9 \( ~. {
% Q* q2 Y) }% ^$ B9 j0 e) o5 lAlter table table_name add key (列(7)). ^* w  Q) P* G0 c
3,覆盖索引  G# k8 V! }3 I
包含或者覆盖所有满足查询的数据索引叫做覆盖索引& f, o, }0 O# G
explain时,extra中的会显示using index1 m/ ~1 E$ f! q5 L0 _5 a
这里一个重要的原则是: R# T) o- G5 C7 o, |
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
4 b4 _* V! u2 i如select id from table_name;6 f3 f& \% S6 T- S  D; P7 L4 |$ D- x

6 [6 N3 d5 B' S0 Q4 j很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
7 w$ A& |0 @; A% Y1 O8 m一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
( ^! r$ F7 H. [* [8 i* L% R( y9 ?8 s3 Y3 W- C" R
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';
    3 R+ f+ k* [0 t( v
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:
0 d( _$ e+ U3 M1," {3 j$ p8 }6 g! k) Z% w! B$ B
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。# W+ Y  h4 a, H) ^4 A: K; L
2,
: ~5 v4 `$ [# S- a& tMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。) p3 Q1 ^% c8 h3 O! l1 L2 E
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:# X* o5 ~5 E. Z) \) |8 b
1 B* ~1 M* h& i5 c& M; k
4,为排序使用索引扫描" R8 ]$ {. x! C. [" ]" `
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。, c# j- X$ _* O! C' E
explain输出type为index,表示mysql会扫描索引
1 |% S, ^- O6 Y+ z6 {- |9 r, i) Z2 x$ [' B: r4 c' z
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载." C4 r6 J1 F9 v% a4 A1 g! |; O8 f
! c" L. q6 u+ o' c, V  L
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
. g6 p! M8 Z6 ^% @6 M; ?, J8 K+ l9 H& j
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
+ o6 G+ W( L* O! R' L
, U( M: V( w$ a$ P6 X! E5 D4 sORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。8 \) y6 ?) C8 I1 d) o' @( ~/ t

2 {; t, G- [3 [* @' Q使用join可能情况会有不同
+ `7 v# M( B  S% f1 L1 @
' |6 F) D% S* ?7 W5,压缩索引(myisam)9 j6 a; C/ B/ K0 e( ^7 d1 L
6,多余和重复索引(应该避免)$ i! o6 C6 T! |9 k, V
* I& Z) [( P$ F, |9 i" F( ^$ N) q
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B), |, C9 |. {- t/ h6 ^9 ?  _
上有索引,那么另外一个列(A)上的$ W/ ~( G( y/ l* b. i9 g
索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
' Y$ U6 H4 T. ^" k% a然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
' v6 `. Y& V; E' y$ o) k7 k
' j) x* j$ o  M! H1 t/ C要点:9 V9 t+ _  N: q  y  Q+ [$ r" ]9 {
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
( S( V/ L' M1 S7 q5 }
3 z$ A2 F$ ^  D; `即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
/ q7 U9 k0 ]! y/ `" n5 f* @- Z% A* N, K: s9 Q  ?( c; b7 L

  `6 N' \8 ~6 R- w3 x  I4 S4 c" z5 t7 S8 |8 L! l+ l1 ?4 Y
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,+ B# o( p  i' C
4 b/ o0 J5 Q9 a% ~& n6 E3 B. l; G8 f
拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引) A  x0 q. V+ E& F+ M* @) R# {
; m. U6 F: Z: M4 q' K
8 o& ?; j# a' {1 W4 \) w4 Y
0 E/ H4 V/ N+ w+ E% \
一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索
8 x6 C9 }- M8 O
; H- {7 z' s( X/ {引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。
2 p# e3 y6 R$ E- R3 U0 {
& Z4 R5 g1 m- e9 U5 K" M: m, U% g5 q8 n, X3 A5 f
# n- b) o  @2 |8 f1 L' q7 m
一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。
) k/ |5 W8 D4 @, R' {* O, Q$ }  H9 ]8 d
4 Q! P/ y& r" {" i例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理. i6 g, B- Z$ R6 ?% ^+ f5 p
5 E( Y& a0 {) I) _
这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式
) A! s) {. [4 }" x% ?: S( e( ~5 J+ U) N) G7 `( Y' W: T8 f

* [/ |7 h. d) Z' z3 ^+ {  ?0 P& E. O/ S0 d
避免多个范围条件,只能对其中一个使用索引
& Y0 W+ f' O  p
$ E! \# `, \" r$ k3 \- F$ n+ ~0 A7 v* b

6 |( C, f# {5 ~* q6 k2 Z索引和表维护+ ?# D- s' g" p) h: |4 O! Y
+ G8 L" O( I5 A. O* x& N$ r
表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.
( D9 n7 r; z- G* }1 T, l0 n" Z3 v+ I+ p* m# C$ \3 Z
check table table_name;
; m7 m7 u5 Z( o7 c: Crepair table table_name;; H2 l* y7 Z0 K0 j
Show index from table_name;检查索引的基数性
1 @- i9 r% B& E" [4 c  q% _3 A  v7 L
主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量" S4 w( w% s! F3 v

1 W) J5 s8 p* r& x0 C% l( X: I+ E% T/ Q3 m! d

; V; w) N7 z. E1 x4 Q6 uB-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。
) `2 R% {- l! ^8 f7 H2 \$ L/ ?, [% `: O
表数据也能变成碎片化。两种类型:1 O1 }/ Q3 r8 ^0 f) E/ q
2 s" a1 s4 `: M5 L3 p
1,行碎片1 l  N5 e! J5 l. D
  |" z- b# I- Z
当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
) k7 p$ H( w5 \( |' x" t& c0 z  ]/ U# j8 W9 k, c: V. h5 E

; t- O) T3 j0 r3 L0 o/ O: Q0 x, }( F
2,内部行碎片- U* j/ r) p2 {
7 l( e' G" a8 e4 Y6 t& G
当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和. D! A7 K9 `6 g( o: g: y
) ?# F$ A% y+ M; y) V- J5 ?
聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。- j3 y& A8 ?7 @! K" c2 H

" K- l2 G& m' ]7 Y: z- {
& O- Q5 |& Y' k+ g) e9 p2 a$ e
2 G# L/ a1 P5 X5 f为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。) n, P5 e6 K9 ^* ~1 x" ?+ u+ `
% ^2 G  U( y2 M' e' ^' s
3 J, g7 l! F' r  U2 Y; t: a
2 I1 z5 Y" @/ ?+ T& d3 J- I
ALTER TABLE <table> ENGINE=<engine>
- |  z% l* T5 T! V5 {+ B7 D6 e6 p$ ?1 _  Z$ O0 H

' A) F/ p: u; t' x& T% g* a1 _) d
9 M. M6 ~5 y+ c+ Y! {: `加速ALTER TABLE7 ?" D" L" o( Q" k

2 P5 G% z% j; U2 j6 W; V
% ~8 S+ A) E1 r1 U
5 |! s9 \/ F9 g. ]% y' v9 e* KMySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需# K' {+ V0 E7 i' T( q( [7 w3 ?7 F5 z
, ?. ^3 F$ ?! @# K3 s
要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,
( R1 ~! b+ L( s* h1 p% \9 V; w" T1 J, H" `$ K5 h1 X
而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。
- X) A5 |  e  o) J; C. ]) M. n* s
传统:
# y, P8 U: L( M# G
3 k9 V3 {& \. X  b7 JALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;+ x' o6 v- [* @
理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更3 d& P" S1 u( ?- k* m( v0 a/ s  Z
改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。3 k3 }  j2 e; O( a" [
3 U; w7 x7 {; S7 q- L6 F. L
变化:" N$ z* N, E' [/ N. q

% z3 m: a8 h6 x# RALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;7 z1 d8 s$ I: g+ M( ~
这个命令更改了.frm文件并且没有改动表。它非常快。" i  Q9 D0 l) o7 T
还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2026-6-8 07:28 , Processed in 0.035939 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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