召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 2918|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
4 z; ?; r# U. p. L# V) }有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
7 J% A; ~8 `; U9 V3 ?0 `+ y一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
% e2 x9 A* \, b$ k' C在InnoDB中,只有事务提交后才会解锁. P: u+ }0 h0 M" M% u2 s/ W! H

7 p& f& T# D& k6 b, ?索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只
  u% @0 E% m7 f2 a: r能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。: v2 {& r$ X% G8 n+ q4 {

' ~2 n3 S. b' X8 U/ U! |: `  TB-TREE( C% D3 C3 N" }
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
1 }% C# c: q, w- h7 \1 f使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。. a+ Z; Z) _4 u

  1. ' e) b- d- R& B5 m" [% X1 S' X4 w
  2. CREATE TABLE People(
    + A  d7 z) F# V- v+ k
  3. last_name varchar(50)   not  null
    4 _; p2 K' y/ c. K; h
  4.           first_name  varchar(50)     not   null4 o9 ?0 ?" F# q$ v
  5.           dob  date      not    null3 f0 g7 u  l8 Q8 b) t# y5 I
  6.       gende       enum('m','f')    not    null
    7 `! g" z. S$ d, S2 M
  7.         key(last_name,first_name,dob)
复制代码
匹配全名5 S# t- c/ l5 n; X
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
" c7 @  l3 g# K, K3 B的人。
& I8 C; C6 m! H匹配最左前缀
8 Q. z# s8 G$ s5 W0 IB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。3 N) r. k$ ^% M+ g  \5 j
匹配列前缀/ b7 k. _2 Y1 N7 r# z& J& ?$ g
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
6 g4 I" n, e0 O! C4 g# R匹配范围值
% D3 \: A. v5 G- y这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.8 W. Q1 T2 P- [6 x. R
精确匹配一部分并且匹配某个范围中的另一部分  M& H# L: {3 x  h
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
. S5 d6 x$ E1 r5 A列并且对first name列进行了范囤查询。
8 n3 |7 U: b2 N. P2 w9 U4 _name* C4 A' Y7 c8 [  t
只访问索引的查询7 O- X6 A( O- F, }7 y
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。& |2 ?& Q% \! C/ O3 Y. G

" Y% A/ _& {5 ^, T" u8 u: q: ?6 ^3 n由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,
4 X/ c# p8 d1 V- W( j9 L. l如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查( @, ^6 E0 O+ S6 ?9 V
找方式也可以同等地应用于ORDER BY。
0 ~. u( B$ `5 B. O* t1 @7 ~: H' V; C0 ]1 @8 h
下面是B-Tree索引的一些局限:8 \1 L7 ~* o/ s: O+ R6 O
4 b5 Y3 o8 R$ J7 \
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,  {8 l6 T# X+ ?) k$ L
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓
; D) L- V4 f( p4 u6 u氏以特定字符结尾的人。$ H; K. J1 d0 E7 X1 a; g" X
9 s& |% d+ V, x) i( ^  x) ]$ k
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
3 x& `4 P! }, u2 j6 p0 h4 [/ Z义first_name列的值,MySQL就只能使用索引的第一列。
1 ^+ D  n1 B8 ^' N, A- Q- t! J# p; \% N' k$ Q# u4 _* t4 k) d0 }
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是. O* |, w/ n2 a4 p( n- R
范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而' g+ ^% B1 X- ^, U" c( I
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。( w$ b, v( E0 U  g. B/ L8 A2 M/ M

* Q  k" q- ?- |( L& T' X4 L+ t哈希索引,空间索引和全文索引等,暂时没有设计
6 ?/ q. T$ D7 e% [
" @" p9 i' ~6 N高性能索引策略: @+ [( m: P3 d- _+ z& {7 F
6 q3 w3 z9 G: M+ |  W' P: a% Q# q
1,隔离列,意思就是不要对查询条件中列进行计算等操作$ V- v7 I& x& z) I) o
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
* s& h# K  M- BSelect count(distinct 列) /count(*) from table;/ }& {! \9 X6 J5 ]8 ?; J; l
看看这个值时多少,如0.0312
" p& X. {  a9 y, z0 D: f那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算$ j4 o& G$ w: M( Y1 K  L* d
,这对于大表很有用。
5 @5 g: M0 M" \8 X1 cSelect  count(distinct left(列,3)) /count(*)  as  sel1,
8 F9 q; D- @2 f$ Q. I count(distinct left(列,4)) /count(*)  as  sel1  ,
* @( t1 e. e0 K8 t7 ?7 P count(distinct left(列,5)) /count(*)  as  sel1,
- Y4 S8 w3 ?& ~7 e3 |( w# B$ E- b count(distinct left(列,6)) /count(*)  as  sel1,4 ]: {9 N: N: |/ y: d
count(distinct left(列,7)) /count(*)  as  sel1   from table;# O3 h1 N( @1 N5 ^, `& |
找到接近0.0312即可。
8 r4 x8 L1 i9 _" S; L9 k
& r3 i, S, g" m- YAlter table table_name add key (列(7))
% U- V# _  |! j9 d: K; e* l  j3,覆盖索引8 c8 n/ i& P- Y, d$ L
包含或者覆盖所有满足查询的数据索引叫做覆盖索引
& P# w: V* A& Z  `* j% jexplain时,extra中的会显示using index# y- o: [5 R# m
这里一个重要的原则是+ [$ g% N3 @9 p
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列( G5 v* m. G. P7 {- a
如select id from table_name;0 ~' `* d1 B8 ~( X( U8 p, F
& f0 y/ |2 b0 X% o
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
# l3 W: b+ |* N$ j一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
# h3 B$ P4 x. {1 d2 w
0 [1 C6 X$ v. e. w* A) K
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';# z! d6 |; |, U  u
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:
( u4 U8 b6 o/ @  P1,) a: p* o3 z7 {: I5 H
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。( H( m+ O9 H8 Q) y5 K
2,
: \+ X* o, ]; F. c! n) xMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
2 w# F8 T  n6 r0 }- p, K  u$ f9 w有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:
( ?/ E' C# g. \3 V. e# B/ s! P( C: Q% ^5 P
4,为排序使用索引扫描
$ B  u# I6 U1 O4 Z" ?5 ~mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
- z4 x1 M& _; B  B% Vexplain输出type为index,表示mysql会扫描索引$ V' s; k" V9 `+ i' d& M
# [* g. |2 M# ]4 P9 p
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
0 i5 [1 C: C0 w$ G: J* y) D4 h* f! c/ y* v0 C* n" s& H
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。/ s4 j# ~# `6 b. R' b8 b

6 O$ Z9 c* f* g- M按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。9 n! ?1 i' Y; L
4 |$ R: _+ H7 N* v
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。
1 \7 Z; z, h! T" b! ?* s
: a) `( S* S% P# [9 w, t! t" B使用join可能情况会有不同; W7 m+ Z/ A  l' G) c5 L
- G7 O9 W- n. t
5,压缩索引(myisam)5 f0 z# c' G% w9 g* O7 U
6,多余和重复索引(应该避免)
% j9 @, Y0 \' W& A# m
( ^" s' {7 @+ s, U8 b多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)4 A' C8 H. F7 e* Y; \( G* j! `7 d
上有索引,那么另外一个列(A)上的+ [% E# o7 U2 N1 ?, V
索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)1 y5 Z# b$ c$ B- V! N
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
# U$ s9 N- a" T, y5 h
) _( R' V) i1 o) f6 s  g1 f7 q  G7 T- i要点:& d% Y; V& m* g
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.- n1 y4 A, J8 X$ y& h  e' K1 G
0 k) d: v: K( c; s" d
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
" B2 K4 d/ f/ H; T) v* d: [+ i' _7 ]  t0 e1 q( G
) g% O# P* a/ s
/ r2 N( H1 z- P
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,/ Q- c: |% u* I1 u; P% c

; A3 U# U' n4 L+ K) P! \( Z# O3 a3 M拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引
4 P; |2 m' S) L2 `5 L  ?) b+ c1 i0 o3 W$ R! T" m

' |4 b3 e+ I1 E) _# E8 n8 v: n4 E" F
一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索5 n! q+ M4 L, I) S

4 V% X: n" A1 A* v引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。  |0 m& R( x2 V: O" k
, Z- |  ^" r- W# t

: a5 v& H" b. K
( s( d# u% h7 t. R# H7 Y$ w7 D一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。
# u2 r: G+ E0 K9 w- w5 c
$ N& L$ P9 I9 K例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理5 Q0 L2 x! {, u9 B7 X: u
3 k( p0 }9 U" B, d( p3 J/ v
这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式7 T: L; Q9 ^% U
1 E; i( d- P$ c9 L: ]5 N4 m4 G) c, R

' u0 x6 v4 V. T& U3 a8 z) Z6 s% ]* Z; _3 H
避免多个范围条件,只能对其中一个使用索引7 t2 n( [4 Q1 b7 U+ Z) [
3 J: q6 S4 z2 r: r0 C' V
* G+ U5 c  s) g- Z# X& e
/ @5 B; X& ], ~7 p) j8 ^  f
索引和表维护
( d9 Z3 ~4 }% f, _7 T
. x7 n4 I6 {( m) u$ M表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.
! i" _* p( P$ T
& q( t: l$ x) X/ a$ P& ?check table table_name;
1 l6 G- r6 I: k/ ?! q2 vrepair table table_name;
# x( Y4 H9 c# \# i; SShow index from table_name;检查索引的基数性2 S9 W) ^$ F  ?. p4 l' J
7 p% `1 h+ ]0 T2 |) h% z; ^$ C1 V
主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量. I( D4 g, N) i* F' ]. H8 [9 C
' H) C- U  g! O
6 J# u! r! j  r9 X5 y, E# t3 S+ n
4 t" R' h; Y- d1 U0 |! R" H; U
B-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。* ?0 n+ E- ^+ D, c
$ P# C$ H0 T2 ^1 y3 ]
表数据也能变成碎片化。两种类型:- W8 p2 V% x- t% @  U* a2 b8 Y
5 A( v# i' h3 c9 {7 n6 [$ m
1,行碎片
3 m* W& [. D0 |- y/ f
9 g5 i* m* p3 I  J  k- H! U/ U当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。2 D$ B% W/ l+ n6 Z# i6 e  ^8 x7 k

/ q# z* q1 x" k4 z$ x, M9 z- Q8 `8 p

% r& S  g* H. Y  I# m6 m+ Q2 H( e2,内部行碎片6 I) P3 F3 w. Q4 }7 Q

' y( ^0 R6 J5 f) K& H6 }8 c$ D, E3 y9 \0 \当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和
, s5 h5 M4 i" l# m. N
2 z2 R/ C$ J8 Z* s& `6 H) y聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。
; K6 v( A, X( c0 [. K1 U: J' `
) k, e3 j& {, F# _8 ?8 ^; Y- D: j" ?6 ]8 u4 h- L6 S, q* R7 j
+ |: X* d; |5 h
为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。6 d* I$ b: c2 @# a
  C, [( M. k# @. T1 z

9 I( B9 |0 E7 m/ w
: P' F+ s: ^7 [* B2 d' [8 ]/ IALTER TABLE <table> ENGINE=<engine>
- E6 D! Y+ r" J6 K- B& h% s* m) [$ a3 j

# x' M7 b; Z( v6 X3 n" l, \5 j7 U$ ^8 L  [6 t: r
加速ALTER TABLE
7 F# k. w0 b4 c6 g* K& Q, t( c! R8 D5 b5 d( O# d/ _+ R

1 V" [6 Z2 s2 |% _, V# l+ q/ X( x% k
0 y( Z  `: c7 z$ T  l7 h; tMySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需4 d4 l( N7 j2 J1 M9 ]% o
& m# ]% I7 ]& M4 F2 n4 s
要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,
7 Z! U7 [0 Z1 G7 f5 ^% |
( q' d% W8 `( P* V1 t而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。- n5 T. d: O% @1 \7 d1 X: |7 a

+ v0 {) J0 }* r# u9 B' r( d, C传统:
% G! b0 k- h. K3 y3 |: y. z% n& p0 S- Z& O. O, `
ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;; I! T4 x5 n4 i/ y  e7 o
理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更, r& b6 ~! Q3 t( L
改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。
$ o4 _! r% K# L) j- y$ |, \$ t( V
) C) X1 [4 i3 ~0 r$ j变化:2 Y$ X7 F& x7 s+ t' x& f, w5 q/ p

4 g$ g0 Z! m2 l: `2 fALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;# E5 [$ M7 G7 a
这个命令更改了.frm文件并且没有改动表。它非常快。2 n6 d. R) @8 M
还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2026-4-10 17:27 , Processed in 0.037286 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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