召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 1072|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
& q) }2 x5 Y  ^; Q/ M8 t有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成, }% Y6 q3 N* a/ H
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
' o1 r+ K  q* k* R5 p在InnoDB中,只有事务提交后才会解锁  p0 q7 V/ D, p  j. |& j

0 g$ {9 c5 P% h  H7 P6 R索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只
  R/ o, {- m! |4 o7 a能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
7 k6 M+ ~* E% x2 O. r1 U4 A0 z8 I  ~0 {9 v2 X/ u
B-TREE
. v3 Y) A1 N1 o( {8 H能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找) s( G$ u) n% _7 E; A
使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。
4 B# z2 F2 K7 |' f! E

  1. : Z( c! @& D+ z. H! k! e8 n+ B
  2. CREATE TABLE People(4 e" n2 Q* f* H+ w, u8 ?$ P
  3. last_name varchar(50)   not  null
    " E, @- q2 L1 ?$ i- ?5 o( m$ L. P; }
  4.           first_name  varchar(50)     not   null% @' C5 m, \5 K. |$ k$ i
  5.           dob  date      not    null
    . K' f, ~* w$ ~9 |
  6.       gende       enum('m','f')    not    null7 J5 G& l) N" U+ e' t% c* Z4 j
  7.         key(last_name,first_name,dob)
复制代码
匹配全名  Z9 l  B% N6 i$ X' }
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
( h7 m& n: W. c的人。9 `) Y9 j6 E6 D$ t
匹配最左前缀
5 }+ [: T; r" c+ f! j7 QB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。! e' A7 T: b! z9 @" X% [+ p( u
匹配列前缀
3 ?$ D8 E- l0 C+ s2 V% ^可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
$ Q0 F9 V# R& j+ h: B, d2 p+ R3 u匹配范围值
# x) C* N! M5 {3 E! A, F3 G这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.
! k: C: E* \# b; T# c& h( @精确匹配一部分并且匹配某个范围中的另一部分# y& n+ U3 J8 H2 N5 S5 L8 N1 V
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last! O- {) W( A( l" ]" R
列并且对first name列进行了范囤查询。7 j' O* J  f# m" s
name9 n4 N$ F* D& @# ^9 `! F
只访问索引的查询
' r8 V2 g; D9 o8 b! AB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
( h5 ]7 K9 ?8 Q/ I8 V! G9 b4 A6 L* p2 i" z" c4 z6 b- d$ }
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,4 g+ E% l8 [* Q  E
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查7 b. w- W+ y/ r0 Z
找方式也可以同等地应用于ORDER BY。3 b. p5 u% a+ ~9 ?7 d

6 b; E. W3 p2 z1 x% q3 z0 A7 f下面是B-Tree索引的一些局限:# Q/ [" o4 z: }! s- z* c

+ c! [0 r# t! z% c1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
- c9 f4 o' Q% K$ X6 D1 X也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓
! h/ g* w. i3 j8 ~6 p# M氏以特定字符结尾的人。
" H+ L# l2 L! L6 z; o9 d; ~  V! D* m, r% h- }) H  ^
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定! ~, ~( z) Q4 m$ m& H8 ^
义first_name列的值,MySQL就只能使用索引的第一列。
2 r+ S4 }* y( _) t+ F3 K+ c* u0 V2 N: b' X! B+ T" t% e
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是  Q( s  ^, x/ k$ \
范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而2 A0 a/ r% m& \/ H6 }# ]* X
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
5 R& s: Q& y+ w: @' h/ f( o  L2 j
哈希索引,空间索引和全文索引等,暂时没有设计
* _* A" p$ _  G% y# ~! Y& j3 G4 A* F! L& S
高性能索引策略
: h4 u) r' I, e
; [) I9 a  y# p4 e+ [1 S9 j. z3 T1,隔离列,意思就是不要对查询条件中列进行计算等操作5 A" }3 g9 `! [8 r, s
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
: d" y5 |% t7 tSelect count(distinct 列) /count(*) from table;
; w% P4 ^" Z+ p4 Z3 D4 y3 z; m看看这个值时多少,如0.0312
! W$ o% ^* T& r) R7 \  s那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算& z* c& r, s5 v* \1 U
,这对于大表很有用。: [% j' z; B0 W; H
Select  count(distinct left(列,3)) /count(*)  as  sel1,' {: h3 F* a3 Q) }! F+ H& I; }
count(distinct left(列,4)) /count(*)  as  sel1  ,; {3 D  S& c3 C- s% c' Q
count(distinct left(列,5)) /count(*)  as  sel1,2 D6 D9 m( H! f( C0 C
count(distinct left(列,6)) /count(*)  as  sel1,2 E, r9 Q$ a+ u8 G* d: W# Y* H+ l
count(distinct left(列,7)) /count(*)  as  sel1   from table;
/ n) d4 Q) y! q! L找到接近0.0312即可。
! H2 A7 g" l6 S3 Z* G6 N/ \" M# ^8 f/ o6 |0 Y% c
Alter table table_name add key (列(7))
9 X3 T% S  @  F! {7 S3,覆盖索引) [5 m% N4 O5 `2 A
包含或者覆盖所有满足查询的数据索引叫做覆盖索引" t) |! R+ q7 U, ]# X
explain时,extra中的会显示using index% a0 j& U8 S# q+ {8 Q4 G: V
这里一个重要的原则是8 j9 r! `9 A2 |$ w- k' O& M
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列8 v: [3 ^0 F& n5 m3 [0 B
如select id from table_name;
8 l( t& u1 S8 z. W0 T/ P! x
* i) R" J+ u* X很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不* ^3 T" R" G' I' U4 o  Y
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
. T: }8 m0 }: p, z6 d' f8 W1 C5 X8 H1 i9 j2 Y3 J3 S9 r
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';3 @+ o  j. M" c, s
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:% N% ~6 d$ W' I- [6 R- t
1,2 ^9 Q5 }7 k2 Z" B5 {& B* b
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
6 v0 p4 H/ V( y" e( E2 B- g& ?2,
9 |' d- S& a4 s# p/ wMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
% e7 {/ W0 m$ U" Q8 r8 T有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:1 H% i4 g4 b- w6 a0 q* k

, u6 S- Z3 @6 {% }& q4,为排序使用索引扫描+ \+ p) j+ E6 }$ M* l
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。3 B; d; s, Z7 O/ Y+ P$ }+ {
explain输出type为index,表示mysql会扫描索引2 n% A" o1 z& s! q- R1 G
* c; V0 \8 U8 [( U. D) X
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.( q& g5 b  w  e3 A1 @4 E  Q/ [

, w& N, S  B; [$ }% dMySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
7 M+ u6 L- M$ p/ y* f: S2 ?8 r8 a) Y2 b. P1 U0 q: l
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
; J- q1 N3 `$ _& h& n2 ]+ Q0 |% Y# @$ l  y, U% J$ [9 f: D9 u0 d/ M
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。
8 W8 q0 I; s* ^& j: W
+ b3 z# `7 L4 A1 I使用join可能情况会有不同
1 u4 U2 w, M: o0 X! F4 ~' a" m* N
5,压缩索引(myisam)! M4 m+ T. Y6 N" X( R! l. @
6,多余和重复索引(应该避免)$ c3 H( @% k- v) W

5 R8 `2 T) ^) @; @  L- r多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)/ ^8 H- ?2 c8 t( E+ L9 @( M
上有索引,那么另外一个列(A)上的
- b* m* S; w" H索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)) _& X- _3 v7 m
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
! C9 M9 ]/ O) r9 ?3 _8 a0 v, @! M) s7 x) M' w6 U( {4 Z+ k
要点:: }5 G  f! {2 ?: G
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.. J% N! w3 F* _# }1 |( I! p
3 B8 Z. U# G& m
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
% `4 m; [+ O, n- Q# b: i$ w8 s/ C/ c/ j

$ n. K% Y: X6 R7 V4 ^. ~, B% l( i
& B, }6 }$ o; E9 E' F
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,
# p+ v  ]+ j, L1 w) J4 B/ p3 {) \8 r# H# ~3 f$ ~6 ~
拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引
* @( w' E1 U( }* r$ O) Z
" Y8 o6 G& l* G3 f; {& F% d7 C! p7 [4 U4 B
8 Z4 E: Q" j2 g% V/ v( {- z
一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索
3 ^4 |/ w( O  p: I
" }/ F. y9 I4 e2 N引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。0 ~& ~9 m, t! r- {) E; p+ f/ e* u3 g7 W
0 \1 W1 j. j+ I' J, X
. w8 d( Q4 ~7 ?* y2 b2 M

' K/ N5 N2 R& Z/ V9 i一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。
8 M* `* {! p6 e: B
7 g4 e6 A, [1 B( S# f5 q例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理
$ G0 F# z& N0 f5 {. w$ C7 ]7 v  U. b; {8 H3 ^/ a9 h! S2 p
这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式
# @% q$ T5 ?( x; W5 T" U
) j7 D3 I. b, z- {+ W& G8 s4 t8 m0 {

7 |( D0 k, ?6 O" m1 Z避免多个范围条件,只能对其中一个使用索引7 f: T# [# H) S5 s0 L  [5 G' r

6 }. y/ F2 D, O
+ t6 F. S( P- O9 a5 J% R4 ]/ B& i0 s' R& {
索引和表维护6 K5 Y& @% u) B" u2 }9 t7 M

1 h+ u; Z1 T2 c. p% B表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.
: t  ~: ^9 H; L3 j+ Q+ L/ j7 |7 A! H* `/ S3 t+ y  `; [
check table table_name;' C# ?1 Z* J' Z0 ?
repair table table_name;' ^6 B5 B& A4 `* B# Z8 s3 @
Show index from table_name;检查索引的基数性7 F* l9 x# F5 H% j; ~4 [
2 z7 R1 C5 o1 {* L1 U$ U7 U
主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量
8 @7 j6 G, V2 c. T9 \# Q2 d
6 w8 w7 G: e' e
+ s9 m. G! f' q- N7 G& @. h( _7 \; c! p0 e: v( z
B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。
0 h: }& L' Q" J/ h' y" I) a5 y2 p+ P
表数据也能变成碎片化。两种类型:6 d- n+ m0 B' ~! W7 w7 U

: c. ]- a5 v. F. W' n/ B5 E1,行碎片
8 N: _" H5 {% t9 j+ P, u& B8 T5 a# n# ~
当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。/ Y, g. g) Z$ c9 s% \8 |

2 f- X7 g: R+ _/ S4 i  T+ _  M; m& f' }; \2 u

% S. `2 O. K# U2 M/ b5 s+ }2,内部行碎片; m# e) Q3 ~' L1 e9 R9 b
( G! `" {* ^5 N! Q0 X4 ]% r) d
当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和' B- G8 y% v: G# W

  c! L1 D1 q! M聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。
. t! I7 T: {: `. m! D# A$ n4 L6 \! Y0 Z0 I8 L' s0 u
+ r/ i; X8 N; \8 P* ?7 M
$ t% v+ U% P. ?- N- {  t
为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。( Y9 u# m9 a8 s1 C* Y6 y

2 n( n) e5 }# ?  e$ i! N2 [  ~
" B, [1 R; \9 y: I" p
, H  f6 ^% o* w+ S* X- i* t; r( }ALTER TABLE <table> ENGINE=<engine>$ K, W4 W, n# R* w
5 h; U) L) p! z$ W1 C

1 m" l5 [" M. q, S1 @9 P& ~
: s2 y7 J" i7 j$ O0 F" F加速ALTER TABLE
) c( E/ h8 K3 b1 [  x! F' x& s" W0 \$ u8 P' N4 ]
5 w" @3 Z1 A3 ~, E4 ~
! o  X/ @0 h& z' D
MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需# s) t4 F6 d# e1 G0 d

+ A1 l$ e1 e( w2 J2 G要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,
$ f( B  Q$ j3 g2 L  c/ Q- X
. ^4 c) Q" Z$ R而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。
. d+ {6 F, w* q1 M3 w4 V* w9 ]
+ l' {% D1 y, {2 X传统:
. K5 V% C; T9 b2 n: s" W! A2 [* b" \2 h* p5 e
ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;7 T- M. S3 U; ?( {
理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更7 p! }4 h# ^. C* F5 R( k1 a
改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。
) e% I! w' E) w9 r" Y7 J' O! Z+ x' V/ U# W) N9 Y
变化:2 V9 a8 }0 v! H- D% i$ @5 |% U
* b7 z* s; o" d4 g+ V/ j; e
ALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;
3 ^" X9 m6 I# l2 s5 \! i/ a9 l这个命令更改了.frm文件并且没有改动表。它非常快。
5 ~0 O" V5 O8 w, a还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-4-24 15:36 , Processed in 0.031283 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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