召隆企博汇论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 792|回复: 1

MySQL索引详解和优化技巧

[复制链接]

24

主题

5

回帖

199

积分

公司现有员工

积分
199
发表于 2019-12-9 11:33:39 | 显示全部楼层 |阅读模式
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
- U  w1 ~  I7 @+ T3 q' v有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
  h4 _: D# S- |& k* B. n1 x+ O一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
5 H, [+ m8 f5 V, W0 G+ Q; [3 T在InnoDB中,只有事务提交后才会解锁$ u; p4 U& U6 V

" T. ?5 m  T- A2 ~索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只* \; A. Y' r1 i9 `
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
# h% }: G* K7 e: g# z4 [
$ I7 Y( ]/ j. q7 t% t. D8 \B-TREE' M. g/ S! M- D3 D
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找* @, e& [1 m% B) ]# I6 }( F
使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。
( f5 ]7 Q# U2 N" C) J2 H  R, ~
  1. 1 T( u2 K/ ^2 y/ w/ ~
  2. CREATE TABLE People(, v' c' `# N: d
  3. last_name varchar(50)   not  null
    * i9 Q, R" t7 E1 E
  4.           first_name  varchar(50)     not   null
    * h, P; J) o" e" J1 Q  w* H
  5.           dob  date      not    null. h! f& G. Q' H+ p7 W
  6.       gende       enum('m','f')    not    null
    % R; ^4 e5 F- B  h1 |, ]+ J7 |, b; [
  7.         key(last_name,first_name,dob)
复制代码
匹配全名$ Z$ Q: s, O9 z& O2 {* B$ F
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。1 s1 B9 T4 o  [/ N' l
的人。" L- h9 i. j0 O8 `" c
匹配最左前缀
* ]1 a+ {- ^8 w/ iB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。- C! ^/ L- G: t& b% @7 w( ^; L
匹配列前缀  y, |$ w  @3 v' G
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。$ O+ w1 s; c' ]& d. R9 j/ k
匹配范围值
6 t6 n6 [! Y) D# `2 n8 F" z- U这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列./ ^7 _* Z" S% V  u: f0 O" @* O& m9 I+ I
精确匹配一部分并且匹配某个范围中的另一部分* S. G( B1 T% a9 M0 o( P
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last  n! d, L; K, y4 Q2 T
列并且对first name列进行了范囤查询。4 F4 j% _7 v6 U3 p* x
name) J, e- Y% Q8 t  s3 x8 _1 {& o
只访问索引的查询
* ?  ^7 i% P; S/ jB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。% Z! X* V6 ]4 I8 k

2 r  y" |1 F9 O7 z# n' ?4 Y2 E由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,
9 N; A. e1 U4 ^1 a  z# @0 X如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
* @) A: [. n0 U' X. i找方式也可以同等地应用于ORDER BY。$ v+ U5 O0 A% \. Q3 p

8 J0 r2 v9 j. q  h9 u下面是B-Tree索引的一些局限:2 h- B" @$ N/ b1 ^9 F+ k- E" ~8 d0 ~
$ |  Q, l8 B9 n% _, ~! `4 [0 g
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
, `8 I, p4 N; i3 Q% n# }也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓
( ?7 z+ R; U2 H% N. Q氏以特定字符结尾的人。0 h# p$ p7 X1 O- H$ H* M

9 b  r2 b  m& v! l9 _& \, H2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定1 B. E" A7 s) V+ R2 ?  M
义first_name列的值,MySQL就只能使用索引的第一列。* h3 W+ e4 p, T' g! w8 ?% G
. H  ^4 B1 {, v7 p8 `
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
) d  H7 I* ?& R1 ^. x范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
( M: C- ?# l7 [  w( N& A不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
' |' Y7 S; g. P& x1 l# R
) |2 k9 O! s& {& `哈希索引,空间索引和全文索引等,暂时没有设计+ k4 o; c- K3 k( Z

1 Z8 E' u! [$ M7 E* L! j高性能索引策略
" i$ ?' {. i( z7 G8 n5 f% w0 |! e/ p: y! K: d
1,隔离列,意思就是不要对查询条件中列进行计算等操作; t. B+ [7 B2 _7 |& o
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引3 ~, B: ~8 o! u: ?& V0 @
Select count(distinct 列) /count(*) from table;5 S2 j+ l) F3 K
看看这个值时多少,如0.03129 O* }5 [0 m; U: I
那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算
( ?, \. R, C4 {# x6 y# z7 t,这对于大表很有用。
) O4 e8 e3 T7 o9 W/ V2 e5 XSelect  count(distinct left(列,3)) /count(*)  as  sel1,3 {& y3 y- m& Y* x
count(distinct left(列,4)) /count(*)  as  sel1  ,
6 I; d( P0 v$ J8 t4 K: L count(distinct left(列,5)) /count(*)  as  sel1,
  d9 M$ y: L( [5 p" y count(distinct left(列,6)) /count(*)  as  sel1,4 A  {0 w/ s' n1 I5 R. ^( _! ^
count(distinct left(列,7)) /count(*)  as  sel1   from table;7 p# s0 F5 [( r& w8 V  Z5 h' X
找到接近0.0312即可。
) C/ q- s' I9 o' ~& D' K) n6 _" q8 q! }$ ^$ G* W* z8 `. X: Q
Alter table table_name add key (列(7))2 J) p9 U, _0 k) a
3,覆盖索引
7 B& ~1 U+ r( R1 g$ Y% ?' v6 k0 x* s包含或者覆盖所有满足查询的数据索引叫做覆盖索引
5 \, ~  }7 ]5 C3 ?explain时,extra中的会显示using index
8 J5 ]( e# b8 u6 q2 U, C这里一个重要的原则是
# g' G8 I9 J! X5 Oselect后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
1 f5 [. b5 N: B: `1 O: C0 w  A如select id from table_name;& v: T5 z. l7 X8 `; Q8 r3 W% V

' _9 k. W! c( Q. [) ^很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不9 ^6 J7 n* t' p+ w' t
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
. {0 H) ?- Q. r9 |. \* a2 I+ c' T8 m! L4 u
  1. Explain Select * from table_name where col ='nam' and col1 like '%name%';) G% d  ~; A; I; O
  2. Extra:using where
复制代码
该索引不能覆盖查询的原因:
+ k/ J" \+ f. t' S8 [1,
) x3 n* U( _! v" l1 y没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
: {. ]! S$ a( j2 N, i9 Y# [" }2,4 K/ ^& F- j6 r7 z. @" ]
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。6 J* i9 i" y# ~+ I" U. J
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:( }' }; s+ z# m* S2 h0 O6 u

  E! f% d) {' K5 G- y4,为排序使用索引扫描) o: Z# l! r3 @' j% c
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
% ?/ G& ]/ Z/ `5 F1 D6 s, U+ Cexplain输出type为index,表示mysql会扫描索引# S. x& K+ U7 C) P
0 [9 n# M! b7 v1 A4 M6 S& L2 {
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
  c4 x/ D3 A% O; j; W: `8 ?) R% k- r% R$ |
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
" k: F- Z7 ^; c7 `, J2 A3 x- M" w3 e7 c9 R7 N; R2 U
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
; N' p/ m2 N  H  ^' g
, y- e2 T0 |$ L3 U! v3 gORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。) D' n, Z3 }; v$ P2 q6 a3 P& w
4 c( v& H2 e, C+ I* Z( f: p3 L1 F
使用join可能情况会有不同
  ^. c4 c0 c4 M! W) ^! o
# c4 V3 r7 B1 Q# k4 ]2 u5,压缩索引(myisam)
% K: I% f# y/ k6,多余和重复索引(应该避免)
. g) [) _; L, Q; h6 x- _4 V" U1 h% y% t) |' ^# C
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
( a- b' d8 B9 x3 Y: i6 _% ]上有索引,那么另外一个列(A)上的
8 W% A; t+ E3 w" G/ _索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
; \; h5 J  m6 x' F& P然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
; `+ P8 L- @0 K5 Y3 H7 R. y* Q8 L9 {% _$ K7 n
要点:! u: l+ l! P5 o$ Y1 E
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.2 i# C6 N, R$ b

( _* G4 P) p. R+ r- W" ^; |# f) L即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
% d' Q6 ~. c! I, n) V9 l7 Q  F7 {, N7 W% }
+ ]# Q1 _2 j8 I$ X8 L9 R

* A% c- P0 m* I1 I0 |4 F
回复

使用道具 举报

24

主题

5

回帖

199

积分

公司现有员工

积分
199
 楼主| 发表于 2019-12-9 11:34:27 | 显示全部楼层
创建索引时,; B4 E9 |9 q! c4 l+ `. }; }  {

3 q6 T& t% D" x4 g+ E; w拥有唯一值的列选择性最高,那些具有很多相同值的不适合创建索引
; S- G: Z# ^  L5 y2 W/ E6 K
. I8 i5 d( v$ g: F" ?" q! N0 r, n, e1 j: l  l, b
: I& K  ^: `0 t. F0 J
一个通用的规则:保持表上的所有选项。当你设计索引的时候,不要只想着已有查询需要的索9 d4 m$ b7 T9 ?7 W4 G
2 f3 a- }1 Y) b+ l; s, D
引,也要想着优化查询。如果看到需要某个索引,但是一些查询会因它而受到损害,就要问问自己是否应该改变这些查询。应该一起优化查询和索引,以找到最佳的折中。没有必要闭门造车,以得到最好的索引。
+ I# ^6 P* Z  B  H
" S. d, p) h9 P- c$ u  S; `% b+ t

1 U# b9 ]6 L- k- {2 I一个在多列上面的索引,为了是这个索引生效,必须满足最左原则。8 L; o1 ^6 g; n
6 O7 Z% g, @& `( c) j+ v0 X
例如inex(a,b,c),这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理
( X  E- _4 W% U! h' }/ S6 g% f" v2 v2 M$ t
这里如果b是一个可以枚举的类型那么可以使用in(…),将b全部列出。这样相当于b没有起到筛选的作用,但是却可以是索引发挥作用。这个方法也不能滥用,因为会出现n*n的结果,如果枚举数相乘过大,应该选择其他方式
4 P% y( U9 i% y* g* w- Z  [1 P. @2 s$ ], k9 ~
9 p- l/ L% f$ Y- D) T- L
  h8 m' c' v" b
避免多个范围条件,只能对其中一个使用索引
; T  s% i" c  m$ g6 a, N& f& X4 l1 I* I

1 L6 g$ B  Y- p9 W+ L2 X* b
% O* R/ z5 b$ h5 I; |6 A% ~! b索引和表维护2 Z6 I! A/ O  i0 l$ F) s0 P
) h- H% w/ E. z0 S7 J% ]. G! K
表维护的主要目标:查找和修复损坏,维护精确的索引统计,并且减少碎片.& k  o. W& R) _

  z# L/ }" g$ X( L8 \1 vcheck table table_name;
+ j; p3 I$ |* \- brepair table table_name;! `6 c+ L  U& v
Show index from table_name;检查索引的基数性# ?% D# ]2 v9 \6 U- `

  E7 O4 @) I" M! z4 b主要关注cardinality列,显示存储引擎估计的索引中唯一值的数量+ \' A8 n7 O, l) i

9 a$ J; a( T& m* N, J( \0 b, k, ?2 T% K. c

4 x$ C3 v/ g" e( y4 P/ l0 O0 cB-Tree索引能变成碎片,它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。, u! ?$ Q- D' u2 k; J" C
7 ?0 M- j/ l7 V4 {
表数据也能变成碎片化。两种类型:
3 Z# [. q5 V/ U# _, E+ I$ G( H  ]
, P* F% g% E9 Q% L, a1,行碎片
% L5 o5 e: L$ P9 X1 T5 D
8 h3 l1 H  p/ t当行披存储在多个地方的多个片段中时,就会是这种碎片。即使查询只从索引中找一行数据,行碎片也会降低性能。
. K0 |. N; b" t5 H
+ }: d7 F- {4 l3 k' C! T& N4 n8 L! _$ t8 O

; w& X1 {5 x1 V5 t3 J* F2,内部行碎片2 `+ q7 `" g& h$ Q8 r

$ u- W) ?6 K) u7 b* M% b当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候,就会产生这种碎片。它影响了诸如全表扫描和
8 e- e5 b' ]+ b: C! T8 U
. ^, D: \( ]$ y聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。
; \- a/ Z! F. a, g/ Z( g9 C& s, C5 J! G0 d/ S
/ s8 C; A7 \6 W" Q  d) Z, a8 k1 E
; a: x- [! C2 L8 r( t8 t
为了消除碎片,可以允许OPTIMIZE TABLE或转储并重新加载数据。3 E' v% m) Q! e- w. [9 Y
. C, d5 I' B  c# w

; w, w0 }& G6 E6 f# B
1 X/ O% }+ `- ~ALTER TABLE <table> ENGINE=<engine>' A! L% B5 c- V, y) U& I0 W# D  p

, z4 t4 A5 m- Q$ Z3 N0 Z& Y- j) a% L/ L1 x  D
1 H  |1 C: ]2 _  t/ J. g4 {
加速ALTER TABLE
' i8 ~; s2 f/ T) L+ \- q% D9 Q  S) X* P2 u1 p
% ?* S7 D1 Z% J" T% d
- |% T$ x, b' d' B+ {
MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需
; o9 \( h( a4 R( d# n5 F/ t% `9 ^; w
( c  g" W8 ?  m1 m+ N- Y% s  a要的结构的空表,然后把所有老的数据插入到新表中,最后删除旧表.这会耗费很多时间,尤其是在内存紧张,5 U1 o$ D, C+ Q$ D
3 y) u, `! F" L. C, E1 I
而表很大并含有很多索引的时候.许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。
" ]( G7 A  U+ l" f2 Z& L4 x, k1 |" j
传统:. q7 n! W3 U9 s) L( S2 Z

6 m1 E% I6 Z  n8 Q" S% C* _0 }ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;
! t( [9 r# P7 |0 a理论上,MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中,因此可以不接触表而更
  {4 U9 {+ c9 u改它。MySQL没有使用这种优化,然而,任何MODIFY COLUMN都会导致表重建。; Q' E2 `2 F- o/ E

* I8 g& I7 N) m( s6 @! S& L变化:5 B- l2 w3 J& p0 o
- x. P7 c6 q% Z
ALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;
3 ^( F; k0 x, E' j+ M: f这个命令更改了.frm文件并且没有改动表。它非常快。# k0 e% m" E8 Z) \! Q* v
还有一个CHANGE COLUMN
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2025-4-2 13:44 , Processed in 0.054487 second(s), 25 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

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