召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2922|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能# t+ G; @1 e4 `! O' ]0 T. p$ B
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
2 n/ ?. o1 ^7 j8 L一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
& I& g+ j9 o" w" |3 g在InnoDB中,只有事务提交后才会解锁, e( @/ D" j( z1 B0 g% \# ~3 p

# @$ L; v6 I9 K" u0 s  m2 m: q' R4 t索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只
1 P* d4 R/ f5 J5 V能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
% @( c+ T) d+ T; N
  H$ k. L% [  Q; m2 H- CB-TREE
& }8 \/ _  @9 l: `1 q能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
4 Q$ ?; ]- B2 B& h使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。& B# v9 }1 ^6 d5 {* j

  1. 3 D& f+ u" ~" U: Q" l
  2. CREATE TABLE People(* C* K) A& r- G7 ?; M
  3. last_name varchar(50)   not  null
    ' l% J* P' s: f+ G
  4.           first_name  varchar(50)     not   null
    5 E+ C6 d8 s. T, D# i8 I3 J5 q
  5.           dob  date      not    null
    6 Q: z8 z4 @4 W
  6.       gende       enum('m','f')    not    null7 z& G. V5 f$ n# p8 @
  7.         key(last_name,first_name,dob)
复制代码
匹配全名4 P1 ?, Y0 W" B# c' z( Q$ K
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
7 m, h" ~1 g$ g) y- S  c5 y的人。
  T  U0 C$ ?& {- G: Y匹配最左前缀# R. |$ u0 @( E) c  Y5 P) @. {
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
# E- L. K8 Z- C  b, m- L匹配列前缀4 v: }# D9 q% b1 u# X* @: D
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。3 d9 @$ J, O2 q) \0 [, l2 Z8 F
匹配范围值) p) n2 l7 g% @% w+ L9 N8 t
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.
) w( W. k2 |  N8 U2 q+ }精确匹配一部分并且匹配某个范围中的另一部分
9 {1 A9 w4 e) q7 Q! d) e; w; X4 N/ @这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last8 N1 b6 B3 U) e$ t+ _
列并且对first name列进行了范囤查询。3 e5 V; G+ @# M2 Q8 W( }
name
) e  q4 ^$ C, }9 j$ t只访问索引的查询
$ f3 t! m; y5 V; x5 uB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。  w. Z* G, p1 i  m2 J* W' Y9 W
8 k4 U5 X% D6 ~9 }* L- C
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,
( j% j8 n* Y* s; I! A1 C" F如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
* S7 k7 F. Y" j0 U: ~) O8 J, X找方式也可以同等地应用于ORDER BY。/ |. e4 o( v& O7 s/ i! z
# h' Q  E5 \, D9 |! w+ ]. L
下面是B-Tree索引的一些局限:0 m- |) t# W9 d' H( s# ?
+ k- _* C4 m' F- W
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
+ u' Z. m; M# I5 F8 ?  S也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓
  I! w& J' s6 D6 V: S氏以特定字符结尾的人。
# l  L6 F4 d. L. ]. a
0 [. H. N; F9 I& d! l/ t2 s+ z7 D2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定) h* f# g9 n; _, N! U
义first_name列的值,MySQL就只能使用索引的第一列。
$ P; Z9 Z3 t% X3 b) A7 {  d  b$ ?9 z4 W2 m8 {0 B' H1 L. A* ?
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是- Y- J+ _  n! r  j4 E
范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而- e0 b* k' s6 d) ~1 J
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
2 x; F) l- n& V6 c. x' n6 s$ w+ W/ \4 A. i9 d1 Q8 R7 o
哈希索引,空间索引和全文索引等,暂时没有设计
" E6 |2 d- T1 E  c$ _! c3 p4 ^9 V0 Y) H+ n, o6 b
高性能索引策略
; m4 n- k' `! K3 i9 T8 a/ b
6 S; l& A5 f6 L1 j" w1,隔离列,意思就是不要对查询条件中列进行计算等操作8 c0 I( R% p" {% L
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
0 p5 c, _3 U1 ?8 W3 qSelect count(distinct 列) /count(*) from table;# }/ N4 G" ^+ J5 j
看看这个值时多少,如0.03129 i# Q/ s9 Q, Z) K8 \7 M( g
那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算
/ `9 P3 W8 Y: g,这对于大表很有用。
4 J3 q% J2 r0 w( U& a' T6 }Select  count(distinct left(列,3)) /count(*)  as  sel1,
2 Z% a4 ?* [( o9 Y) f& e% L count(distinct left(列,4)) /count(*)  as  sel1  ,
  M( O) C0 g5 x; v4 E+ u" ~6 z count(distinct left(列,5)) /count(*)  as  sel1,; N. B. x6 @* q
count(distinct left(列,6)) /count(*)  as  sel1,
" r; P: v: Q2 E3 d9 \* z count(distinct left(列,7)) /count(*)  as  sel1   from table;; l- w" F# p# X; w3 m+ o6 z' C
找到接近0.0312即可。  G7 `  w5 `- q! }
* ]5 q4 R+ Z  v# T* X/ n1 _
Alter table table_name add key (列(7))
& [& T! w! G% H9 |3,覆盖索引2 q; F2 f0 f3 u$ U4 S( l! |
包含或者覆盖所有满足查询的数据索引叫做覆盖索引
* O; n5 A! t/ s) _: Wexplain时,extra中的会显示using index
/ O3 E( ^  f; J" M5 }6 J+ ]! o这里一个重要的原则是
. H3 @! \8 N3 v3 h& p- Y; O- _select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
- }: m' X! `% i5 w' q$ _) a如select id from table_name;' A6 m2 ?0 ^+ W0 e- k1 U
2 ?. Z& d( ]6 i* A1 ?
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不2 B' d" C: i0 {0 ~; O6 A
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
+ O3 d1 i: N  b  C* d% f, H, x4 j+ J: I( u, [; Q( ]
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';
    1 P: d  b8 t. }+ T, p3 K, @8 w$ @
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:
+ Q: A. x9 S+ `2 X/ x1,
5 b( ^- Z) ]# ]# J" ?, j没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。% {8 n) O# a; \4 ]
2,
2 @& j& v* [3 a( lMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。$ k7 b* ]- {  a) r+ W
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:
- B' G8 j$ R8 ~6 ~0 ~5 t$ v1 G( x- J8 w" Y
4,为排序使用索引扫描
; W# b* ~& ^( m  Q/ g) hmysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。6 n; h8 d2 [& H
explain输出type为index,表示mysql会扫描索引
, O2 N0 R- Q! {- J1 Z# t
5 B( P6 Q5 V" n扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载., \" l6 Z% A6 e5 Q# V
& |  @  Z: K: F- C
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。; j  l$ b" x+ N, |0 |8 d! X0 K/ |7 V
* W6 w- l& G: C$ ]0 h+ H
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。% I4 _: P. K2 h. X

& @, P  b, ^' u9 k2 F0 Y) c0 aORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。% W9 k3 t- D  Y! z

* }4 H% y5 q# Q! ]$ E$ N% _% A使用join可能情况会有不同
  X/ m+ V  d  t  L$ I
6 `1 C) {# p8 R2 \5,压缩索引(myisam)
6 h' m# C, _" U0 i6,多余和重复索引(应该避免)
9 R4 S' Q; B( ]7 D3 h& k) L3 `* a
" s; ~! X7 ^7 n1 `- B" U多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)" K" r/ ^- g9 c8 r: Z0 g( E
上有索引,那么另外一个列(A)上的
8 t+ q! r; b1 @索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)9 |' T6 M" e$ J6 o
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
5 [, \) H" h$ |$ }/ f" ~
2 ]& |' B6 S4 T) K; ~要点:( l6 n; d8 m7 \$ `( M) l
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.1 Q' S. v6 E' @' d2 h  Q- o

. }$ x* n+ E: @/ D" k6 }0 A即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行$ p' F" Y( b1 o2 k" R- E' y* Y  t
% Z8 w4 v2 Y0 [4 ?- c  s) W
4 r2 k" r3 z7 u; d% A7 L

4 v( T) U1 a5 T# A& s1 Q
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,6 @4 M2 |8 X2 d. f" B2 |

+ p( w% a( K- e9 w$ x9 t拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引! B: j, ]. ?8 S6 v0 e: i$ d7 [4 v
3 T1 |+ d8 ~( |" B  @0 T
" ]# G5 h7 s& l& o- h; J
* ^: t5 N: B6 m7 I& Q' e
一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索
1 E# R% W7 |, R& P/ B* p% d) E, P* L* v9 m) K  I# h1 i: F
引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。- _  q9 A) p! z6 ?/ Y/ k
' ?# N" [( C3 s7 O
3 t( E+ @6 w$ D/ M1 l9 C) y
- K$ P8 H; ~$ f  }& C: x' ~! m
一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。
3 l7 T- S/ Y. [& s
+ \4 Y( o# |# I+ q1 o例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理
" B" F! }* `7 V
8 }( L) [( k% K3 O. h这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式
7 q5 v- ^6 a6 p
% `3 X/ X* C1 M: k/ Z/ i% v* J2 k% o% J9 E

1 ~9 ~$ p, N1 r+ Z3 N* l+ S避免多个范围条件,只能对其中一个使用索引5 X, n8 f. G2 C* n" }
1 ^3 k3 _- [& Q; a  Y
& k9 ~* n2 V6 J7 N

; ~0 v' j* ~0 u, l索引和表维护+ g) `. o( V/ c8 g8 v) W# l# [% Z
5 ?' c/ g" p2 R3 x
表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.$ k- G2 v/ F% X6 n' [: t

( Z% v! m2 u$ ^check table table_name;
& `" u) D7 C, e( `( n+ j* ^$ \repair table table_name;$ ~8 t/ M/ ]5 v+ x( p/ i, @
Show index from table_name;检查索引的基数性" H" B' a; F1 ~5 F
6 c# K# g- h- A1 H# V. _9 l
主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量; E1 T1 k* k- R
2 ?; s3 P. ]% _" c& w+ t5 m+ E* v% b
" M' e" I& r0 l2 X! M  v6 T! p
( ~) G6 W; K; G7 _
B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。$ ?& h. s$ C. l# U8 F

/ [' l4 J; ~; |8 y  F" a! x表数据也能变成碎片化。两种类型:8 E1 \, w" @; ^: ~% t: [
% F& T  o( j/ u: v( H  R) Q
1,行碎片1 y7 o- z8 A6 `  K
" i# z) H0 Q5 ~
当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
! n6 X, I$ f6 O# ]6 _; G% s! s) M4 j! H. W7 B2 v
" x" H) o  B1 g! ^5 z! v9 p- O7 U

  A: O2 q5 I% g: P! l# M7 s9 b: e2,内部行碎片
+ ^% P' f! @; ]5 H! I8 d8 X9 r6 f
当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和) ?6 U1 j& ?8 n: s) \" |$ P! e
; r* N; M6 h2 ]. P
聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。8 q) ~, z0 u4 `% ^& P5 K0 H  C1 D

8 z* L% i  U# C* I/ w  g
" |- c# s; B, Z& y. C+ L4 x
4 \  D1 `! ^/ T. _为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。
  |7 n6 q, m" L; j9 k2 f
* `, V- v5 w9 n/ Z+ d  [5 ~1 \: q7 D1 V. N' @
' Z/ @2 K% k6 o( j
ALTER TABLE <table> ENGINE=<engine>
# j4 e- L9 G% i- n& ^7 i4 E' a# X, L" c5 S3 |; ^- u
; L) D7 H+ E. T% Y, F2 r
) B) J5 `& H/ T
加速ALTER TABLE# _2 @4 I$ c7 Z3 u

1 p+ \6 u) B8 D2 m' g
! w9 E8 O3 l! r; y7 G
' v3 k, Z( ^3 ]$ c; W" W9 [# }MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需
( z" e& P# q( w
6 p8 y* t6 p3 A3 n要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,& r3 S4 I; J; ^
, d, W4 y# D6 [, |2 |
而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。
& }) T/ t5 U2 L0 F3 D/ ^4 v" J5 Q/ M7 B6 R8 G/ N/ K% @* `
传统:/ R# Q% s- E: p. J: B: f( ?1 D
6 C" i: X3 m) x) C9 d& h% I5 l
ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;1 j( t, ~% h+ Y6 o
理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更: k+ Q' X2 `9 P) f
改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。
2 E) D$ |, I+ j! g7 t$ L/ \- ?" s, r; S9 ?" w7 i# _
变化:  g5 {; c; d2 F0 {/ z

0 L6 r$ |- N, C7 k& f2 U1 d* e8 BALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;
9 j' Y* C* f4 T( M. V这个命令更改了.frm文件并且没有改动表。它非常快。
4 Y2 T, O7 v9 O还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2026-4-12 10:53 , Processed in 0.038934 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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