召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2656|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能' c; G- d7 f% {
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
, T& `# M5 W5 N6 j, T8 Y一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
! e0 n* t* z4 R' D& s9 _0 }, F在InnoDB中,只有事务提交后才会解锁: J& P4 V) f2 Z  X% P9 [
. g8 q+ h% m3 O$ l1 `3 P/ [
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只8 W: t7 F$ ^: ]( ]8 J
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。2 G- v6 x# X0 Z  Y+ D

2 n  [5 @8 h& @2 I4 gB-TREE  O3 s& X9 O, |# s# `: Z
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找) z! i6 ^/ b  M& m% N; ~9 g
使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。4 |" p1 f& g# t6 c% N, R& e  Z/ i

  1. 4 }2 d  E3 l' n; |
  2. CREATE TABLE People(+ _9 Q3 T4 p7 I
  3. last_name varchar(50)   not  null: K* a5 s/ s; M- O& p0 H; m  X# ~
  4.           first_name  varchar(50)     not   null: c3 ^% s7 F" y2 z9 O
  5.           dob  date      not    null- E8 G/ W/ `9 s$ G5 y
  6.       gende       enum('m','f')    not    null; w! {, s% w( m8 \
  7.         key(last_name,first_name,dob)
复制代码
匹配全名
/ c% [( f9 a! L* k全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
; f! A# v+ L# M1 P+ y/ E3 W6 _/ _的人。8 D8 f9 C  ]$ E
匹配最左前缀
- u* Y8 \- ]5 `+ e, o  y  U, BB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
: Q8 R0 P, J- w: E' b7 P% N4 q匹配列前缀
+ U  g2 a" Z" I  ^' H* {* U9 B4 H可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。2 k& [$ l4 V5 ^( E
匹配范围值# d5 R9 L- C0 r  q3 H- x
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列./ D3 L5 T6 h6 `% E* @8 M
精确匹配一部分并且匹配某个范围中的另一部分
* q! ]- ^! j; h9 S* B" g0 ^0 f这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
% i( ^" L7 ~; m1 u  M/ s0 g# ^/ ~+ X: _列并且对first name列进行了范囤查询。1 T0 q* }$ Z+ T/ p4 l( c# }" W
name
9 L; q  y. d( Z& \8 h只访问索引的查询" m/ Q* K+ l! W! P& V
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。% `' S6 g+ G# ^) M% a% M3 Z2 ~8 h
- R: ?$ E: ]9 g4 W* `4 h9 f5 V
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,# h2 M7 ^. Q# p' F; [
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查+ F; X; o( C' }( I" O4 j! o' V" Q
找方式也可以同等地应用于ORDER BY。
" V. a/ i) e+ ?. d$ b5 ]; [) `2 P8 H9 k5 Q& J1 N
下面是B-Tree索引的一些局限:
0 y0 G* _* W8 \3 E9 s8 m  ^8 b5 q+ F& G  M. I( P( v
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
$ \3 R$ s" l+ G# H也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓3 f3 R+ O5 B% H0 ]; W! H
氏以特定字符结尾的人。4 v" v. ^0 @# l" o; s$ O9 P

7 `# h# S7 C* M3 {1 n2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定9 Z7 A* _7 E& |* [
义first_name列的值,MySQL就只能使用索引的第一列。7 _0 t* X3 n5 E1 F4 k% y/ Y
2 b; B2 A$ M" S( y  B& z; e  g
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
9 ~! {" m: `: [3 Q范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
" N. E  H/ @. B( H, Q  |& ^5 A不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
/ m, X# x8 K& z4 O
0 k- z. x$ g5 y. |4 a# {. y9 P哈希索引,空间索引和全文索引等,暂时没有设计4 k5 i4 c+ ~. O8 j, s" l

. n% F& J4 Z" A" Y高性能索引策略
0 W' U% t9 f0 o& T7 n& l" y7 E4 F
( c5 M0 ^; |2 t1,隔离列,意思就是不要对查询条件中列进行计算等操作
  T, j1 y: `7 z2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引$ L! a# D. O+ ^8 Y, z- S- P
Select count(distinct 列) /count(*) from table;
% j4 z" B- k8 l4 K6 I. T2 g5 d看看这个值时多少,如0.03127 E: ?4 V  d; l0 w" S
那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算% W) `5 E# @2 A9 l7 K5 b
,这对于大表很有用。
0 ~) Z' G5 N7 P# ]5 Q6 BSelect  count(distinct left(列,3)) /count(*)  as  sel1,% E0 \( [# }1 m, m( i
count(distinct left(列,4)) /count(*)  as  sel1  ,
9 g) e9 i9 f* I# M# k: _& u count(distinct left(列,5)) /count(*)  as  sel1,
- @6 J' c2 c4 D' ^5 ]. h count(distinct left(列,6)) /count(*)  as  sel1,) S0 w3 ]& X! h
count(distinct left(列,7)) /count(*)  as  sel1   from table;
5 m# W" Y# g* i" J找到接近0.0312即可。
5 z: I& [7 z6 F" X3 _) ~9 {' q9 o9 h7 C) G( w& x- M- d9 u* R
Alter table table_name add key (列(7))
7 u3 K8 t" J* r# c4 a+ g# A0 A! Q3,覆盖索引
% G3 r& w5 I8 u8 Z! o3 b# r5 K包含或者覆盖所有满足查询的数据索引叫做覆盖索引
* [  t: K1 J, Oexplain时,extra中的会显示using index$ |- l5 j: F( `7 `/ f) k
这里一个重要的原则是
, q; Q0 B, Q& M8 J: u* x  J' W0 s8 Nselect后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
3 o' c3 I9 Z/ m# Y如select id from table_name;* L, }+ y( J* J6 n# F6 _

- A; M5 k- r* z: j% _很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
' [' }8 T9 Q0 m  X2 Z/ E$ a+ A一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。  Z2 {4 |! Q4 v( G1 j* d

8 d. ~$ e/ E0 p2 V) N& E$ o
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';; z+ ]6 [! f$ E
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:2 W2 \( c( b0 l6 m% z6 N8 W
1,+ r& G! a' t- v1 @  I& X6 ]
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
! g: b& y5 e+ n! Q& ~2,
& S2 I" b0 r0 x$ ^& |$ p, LMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。4 d) J4 }' g3 v% k) Q
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:
( L; z) y; h2 b% k$ L
; _1 ]- ^# m  e& |% g) Z( T2 L4,为排序使用索引扫描/ s9 m  l0 i5 h5 [* p
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。. P) x/ a0 b; q# }3 F2 r! f
explain输出type为index,表示mysql会扫描索引' K9 }% B/ K$ k! S! W6 H

+ Q  ~6 c, R2 C. K+ |9 O' w6 y. U: F( Y扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
5 Z( t7 }. ^7 R# U$ ~6 H* Y& z: V; D" M2 W0 I
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。/ h: k$ s7 e$ `; D

$ O# J& Z6 b. E* y/ n按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。. ?0 d( s2 K% K& t6 X- t% _

& Y5 p7 l8 m; i1 Q) C$ @# QORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。8 k% C8 D% n4 C# T( u

( W8 F2 J: U' d8 O- T. C) n使用join可能情况会有不同
" y/ }* G) {- z3 v: j0 W. F% l2 K& E
5,压缩索引(myisam)
/ o5 F5 d+ ?* I  I$ \( e5 E# `6,多余和重复索引(应该避免)
0 o7 K* t2 k/ ]: [! V7 p( C5 P9 k! A- w; t! V# t1 w; r0 ^; U
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)! C5 I' p0 W6 ?" `% C2 `
上有索引,那么另外一个列(A)上的( j; s/ `8 e) B1 g/ l5 q$ }
索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
6 V8 T% I% ?% a& R- P1 m然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
+ `$ f$ ?$ x0 V  i- Q: ?
) t2 v! q( u$ s5 e* U) J要点:5 ?: C+ y6 _% F6 H+ P) n) l7 \& Q4 ]
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
4 Z, p% F7 I+ |1 B9 N5 {
7 m* G2 [/ d; S即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行4 _1 \) E9 l, Z% r  _) K
0 _2 _+ b7 Z8 R2 x8 }: t

& y" J; w1 \; m  Y0 v) J; i) ^9 B5 X! j7 @' w% |& B5 _
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,% {4 v$ ~3 K+ U9 ]* ^  z
: ?2 }/ J  U& S4 z" {
拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引
4 x( C. V! D5 U" ~  p! A1 }$ A) p
- W- E/ s* W, w' J2 r2 f7 @5 V# X3 R3 g

3 S" q* b7 D$ U5 \一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索
6 ^5 w& d% B! Q  P
7 Y9 h7 G5 V) G$ o  B& ?2 y) f引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。! W0 L% i; |* r

2 ~7 z3 x+ B/ I; P/ G* C& m0 t" x4 W/ \- \; ~: O- T
/ U" y, u# c8 D& D0 E- ]' Q
一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。9 ]7 ?* V0 ~: n+ M4 l' e; d9 R% v, r
2 T$ i3 O" k: @8 f" J
例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理
: f6 ], P5 O* v! D) j
  ]6 x" `+ Y# u这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式
2 E6 v3 O, G, N9 O3 d8 p0 ^2 M* [- T" T! u$ U

+ G; w/ F0 q8 }: P0 @* g+ ]: p3 o) x4 G" b: y7 r. q
避免多个范围条件,只能对其中一个使用索引
2 D% j, S2 ^! d) y0 Q5 a
/ P3 k. ~% z& c" m. V) ]$ }/ v" G" N9 v- X; }) l. J7 t; c
7 j, N; b: t; V, ^: V
索引和表维护
! r1 g6 g' b1 n/ ~0 t2 k& D+ u) W
, ^8 E) E7 J5 q2 L表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.
) U$ l1 @6 A. M+ P& {. u, D5 C
$ R4 v/ i2 v: c$ _; t% [/ x* P. \check table table_name;" x  R- W3 n# S) g% D" K4 y
repair table table_name;* q' w7 W) |# k- S
Show index from table_name;检查索引的基数性) g9 A3 o* k( v. ?, a7 ?

) A1 f, a: Y- X& \! Y  R# ]9 h* Q主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量& U7 e7 P" p3 g+ K) C) [
6 c9 v2 |5 k6 B. L
* L- W, j5 X0 A

, c* E+ M2 f+ ^, q, D  WB-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。2 g% N4 }. [/ i: Y! u6 ^

# [: u% W$ }  ]0 e: _- L表数据也能变成碎片化。两种类型:5 e3 v" }  j; J6 `5 w4 U, w) @8 e

3 H7 s/ x6 w5 I/ U1,行碎片
5 v  y6 r. P0 E
7 @8 e$ |# s& M- Z3 X. Q3 O0 e当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。! Y2 T1 y# C+ G3 p2 P

5 f+ j6 O6 i+ F7 {$ O, r9 ]; V9 q, v7 s

) [5 f6 h8 F6 f4 o& h2,内部行碎片
: ?' [3 i% m: Y- D
  t: A! Y3 R: L+ Z6 Z: x* S当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和
2 W4 {% W7 V3 J, i, h7 V: z. ]6 l/ z, I- A3 r
聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。
3 O' x( c# @; g8 w- N% F, M2 V+ n! Q! K1 T

, y, ~4 w/ ^9 g5 H( ]( N
: J* @+ z9 P2 y$ }为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。
- S% g6 L4 q. ?7 ^2 W5 w" J
' o. L( V; l: g! H: N: D! N2 J
; q/ G$ J' w0 o  k& E7 S
$ n$ h: s7 N: H' e! n7 W0 [% \ALTER TABLE <table> ENGINE=<engine>3 i7 F: P2 n/ P  y. ~5 X. U  t

5 |  |- w) T  ^2 c# z! s, V. x, ?1 l* v( p* T

. A4 |( X. [3 `: s; g& }加速ALTER TABLE
, O; Q# H  D% I0 I% `9 R2 h; }: c" b# T' m

! I2 o; g/ R) K/ X! F% m5 O  F2 A! \
MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需6 n$ C9 }5 E1 I( X" {! A+ K
7 U/ |; I9 l. z! j1 B1 S
要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,9 `8 ]! ]( d, U0 C! O; V
6 F; m$ `' E* w3 e2 Y
而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。: L: A4 |, q" o; P

8 d  B, R/ P# T- `传统:, P( Z- t3 ^( f! k+ M1 {1 K9 }' _9 _
! W8 |- {2 f2 Z, k- F* W3 G# f& s
ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;2 L+ i- m; C3 m2 I5 J) d
理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更
- a( h: A# {5 F) s1 }8 a改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。3 ?. }0 [& U$ \! k& \; v* S
# o" J) e3 ^3 e$ l  C' |
变化:
6 ^7 X( J& [* I$ W8 Z5 _% }
- ?. W7 n; S1 W; `% c7 xALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;
7 E% J. `3 \9 c4 C( u' D这个命令更改了.frm文件并且没有改动表。它非常快。
1 z1 M; x( A7 \还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-9-12 05:45 , Processed in 0.033250 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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