优德娱乐场w88手机版:一个值得你收藏的教程网站

最新下载

深入分析MySQL索引结构原理、性能分析与优化详解

时间:2016-07-02 00:00:00 编辑:简简单单 来源:转载

第一部分:基础常识:

索引

官方先容索引是帮助MySQL高效获取数据的数据结构。笔者理解索引相当于一本书的目录,通过目录就知道要的资料在哪里,不用一页一页查阅找出需要的资料。关键字index

————————————————————-

唯一索引

强调唯一,就是索引值必须唯一,关键字unique index

创建索引:

1、create unique index 索引名 on 表名(列名);

2、alter table 表名 add unique index 索引名 (列名);

删除索引:

1、 drop index 索引名 on 表名;

2、 alter table 表名 drop index 索引名;

————————————————————-

主键

主键就是唯一索引的一种,主键要求建表时指定,一般用auto_increatment列,关键字是primary key

主键创建:

1
creat tabletest2 (id intnotnullprimarykeyauto_increment);
————————————————————-

全文索引

InnoDB不支撑,Myisam支撑性能比较好,一般在 CHAR、VARCHAR 或 TEXT 列上创建。

Create table 表名( id int not null primary anto_increment,title

varchar(100),FULLTEXT(title))type=myisam

 

——————————

单列索引与多列索引

索引可以是单列索引也可以是多列索引(也叫复合索引)。按照上面形式创建出来的索引是单列索引,现在先看看创建多列索引:


createtabletest3 (id intnotnullprimarykeyauto_increment,uname char
(8) notnulldefault'',passwordchar(12) notnull,INDEX(uname,password))type
=myisam;


注意:INDEX(a, b, c)可以当做a或(a, b)的索引来使用,但和b、c或(b,c)的索引来使用这是一个最左前缀的优化方法,在后面会有详细的先容,你只要知道有这样两个概念

————————————————————-

 

聚集索引

一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引确定表中数据的物理顺序。Mysql中myisam表是没有聚集索引的,innodb有(主键就是聚集索引),聚集索引在下面先容innodb结构的时有详细先容。

————————————————————-

 

查看表的索引

通过命令:Show index from 表名

如:


mysql> show indexfromtest3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+
| Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
Packed | Null| Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+
| test3 | 0 | PRIMARY| 1 | id | A | 0 | NULL|
NULL| | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
Table:表名

Key_name:什么类型索引(这了是主键)

Column_name:索引列的字段名

Cardinality:索引基数,很关键的一个参数,平均数值组=索引基数/表总数据行,平均数值组越接近1就越有可能利用索引

Index_type:如果索引是全文索引,则是fulltext,这里是b+tree索引,b+tre也是这篇文章研究的重点之一

其他的就不详细先容,更多:

第二部分:MYISAM和INNODB索引结构

1、 简单先容B-tree B+ tree树

深入分析MySQL索引结构原理、性能分析与优化详解

 

一棵m阶的B-tree树,则有以下性质

(1)Ki表示关键字值,上图中,k1

(2)Pi表示指向子节点的指针,左指针指向左子节点,右指针指向右子节点。即是:p1[指向值]

(3)所有关键字必须唯一值(这也是创建myisam 和innodb表必须要主键的原因),每个节点包含一个说明该节点多少个关键字,如上图第二行的i和n

(4)节点:

l 每个节点最可以有m个子节点。

l 根节点若非叶子节点,至少2个子节点,最多m个子节点

l 每个非根,非叶子节点至少[m/2]子节点或叫子树([]表示向上取整),最多m个子节点

(5)关键字:

l 根节点的关键字个数1~m-1

l 非根非叶子节点的关键字个数[m/2]-1~m-1,如m=3,则该类节点关键字个数:2-1~2

(6)关键字数k和指向子节点个数指针p的关系:

l k+1=p ,注意根据储存数据的具体需求,左右指针为空时要有标志位表示没有

B+tree结构示意图如下:

深入分析MySQL索引结构原理、性能分析与优化详解

B+树是B-树的变体,也是一种多路搜索树:

l 非叶子结点的子树指针与关键字个数相同

l 为所有叶子结点增加一个链指针(红点标志的箭头)

 

B+树是B-树的变体,也是一种多路搜索树:

l 非叶子结点的子树指针与关键字个数相同

l 为所有叶子结点增加一个链指针(红点标志的箭头)

2、 MyisAM索引结构

MyisAM索引用的B+tree来储存数据,MyisAM索引的指针指向的是键值的地址,地址存储的是数据,如下图:

深入分析MySQL索引结构原理、性能分析与优化详解

 

(1)结构讲解:上图3阶树,主键是Col2,Col值就是改行数据保存的物理地址,其中红色部分是说明标注。

l 1标注部分也许会迷惑,前面不是说关键字15右指针的指向键值要大于15,怎么下面还有15关键字?因为B+tree的所以叶子节点包含所有关键字且是按照升序排列(主键索引唯一,辅助索引可以不唯一),所以等于关键字的数据值在右子树

l 2标注是相应关键字存储对应数据的物理地址,注意这也是之后和InnoDB索引不同的地方之一

l 2标注也是一个所说MyiAM表的索引和数据是分离的,索引保存在”表名.MYI”文件内,而数据保存在“表名.MYD”文件内,2标注的物理地址就是“表名.MYD”文件内相应数据的物理地址。(InnoDB表的索引文件和数据文件在一起)

l 辅助索引和主键索引没什么大的区别,辅助索引的索引值是可以重复的(但InnoDB辅助索引和主键索引有很明显的区别,这里先提醒注意一下)

3、 Annode索引结构

(1)首先有一个表,内容和主键索引结构如下两图:

Col1  Col2  Col3

1  15  phpben

2  20  mhycoe

3  23  phpyu

4  25  bearpa

5  40  phpgoo

6  45  phphao

7  48  phpxue

……

深入分析MySQL索引结构原理、性能分析与优化详解

 

结构上:由上图可以看出InnoDB的索引结构很MyisAM的有很明显的区别

l MyisAM表的索引和数据是分开的,用指针指向数据的物理地址,而InnoDB表中索引和数据是储存在一起。看红框1可一看出一行数据都保存了。

l 还有一个上图多了三行的隐藏数据列(虚线表),这是因为MyisAM不支撑事务,InnoDB处理事务在性能上并发控制上比较好,看图中的红框2中的DB_TRX_ID是事务ID,自动增长;db_roll_ptr是回滚指针,用于事务出错时数据回滚恢复;db_row_id是记录行号,这个值其实在主键索引中就是主键值,这里标出重复是为了容易先容,还有的是若不是主键索引(辅助索引),db_row_id会找表中unique的列作为值,若没有unique列则系统自动创建一个。关于InnoDB跟多事务MVCC点此:

(2)加入上表中Col1是主键(下图标错),而Col2是辅助索引,则相应的辅助索引结构图:

深入分析MySQL索引结构原理、性能分析与优化详解

 

可以看出InnoDB辅助索引并没有保存相应的所有列数据,而是保存了主键的键值(图中1、2、3….)这样做利弊也是很明显:

l 在已有主键索引,避免数据冗余,同时在修改数据的时候只需修改辅助索引值。

l 但辅助索引查找数据事要检索两次,先找到相应的主键索引值然后在去检索主键索引找到对应的数据。这也是网上很多mysql性能优化时提到的“主键尽可能简短”的原因,主键越长辅助索引也就越大,当然主键索引也越大。

4、 MyisAM索引与InnoDB索引相比较

l MyisAM支撑全文索引(FULLTEXT)、压缩索引,InnoDB不支撑

l AnnoDB支撑事务,MyisAM不支撑

l MyisAM顺序储存数据,索引叶子节点保存对应数据行地址,辅助索引很主键索引相差无几;AnnoDB主键节点同时保存数据行,其他辅助索引保存的是主键索引的值

l MyisAM键值分离,索引载入内存(key_buffer_size),数据缓存依赖操作系统;InnoDB键值一起保存,索引与数据一起载入InnoDB缓冲池

l MyisAM主键(唯一)索引按升序来存储存储,InnoDB则不一定

l MyisAM索引的基数值(Cardinality,show index 命令可以看见)是精确的,InnoDB则是估计值。这里涉及到信息统计的常识,MyisAM统计信息是保存磁盘中,在alter表或Analyze table操作更新此信息,而InnoDB则是在表第一次打开的时候估计值保存在缓存区内

l MyisAM处理字符串索引时用增量保存的方式,如第一个索引是‘preform’,第二个是‘preformence’,则第二个保存是‘7,ance‘,这个明显的好处是缩短索引,但是缺陷就是不支撑倒序提取索引,必须顺序遍历获取索引

 

第三部分:MYSQL优化

mysql优化是一个重大课题之一,这里会重点详细的先容mysql优化,包括表数据类型选择,sql语句优化,系统配置与维护优化三类。

1、 表数据类型选择

(1)能小就用小。表数据类型第一个原则是:使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu缓存的使用。

(2)避免用NULL,这个也是网上优化技术博文传的最多的一个。理由是额外增加字节,还有使索引,索引统计和值更复杂。很多还忽略一

个count(列)的问题,count(列)是不会统计列值为null的行数。更多关于NULL可参考:http://www.phpben.com/?post=71

(3)字符串如何选择char和varchar?一般phper能想到就是char是固定大小,varchar能动态储存数据。这里整理一下这两者的区别:

属性  Char  Varchar

值域大小  最长字符数是255(不是字节),不管什么编码,超过此值则自动截取255个字符保存并没有报错。  65535个字节,开始两位存储长度,超过255个字符,用2位储存长度,否则1位,具体字符长度根据编码来确定,如utf8,则字符最长是21845个

如何处理字符串末尾空格  去掉末尾空格,取值出来比较的时候自动加上进行比较  Version<=4.1,字符串末尾空格被删掉,version>5.0则保留

储存空间  固定空间,比喻char(10)不管字符串是否有10个字符都分配10个字符的空间  Varchar内节约空间,但更新可能发生变化,若varchar(10),开始若储存5个字符,当update成7个时有myisam可能把行拆开,innodb可能分页,这样开销就增大

适用场合    适用于存储很短或固定或长度相似字符,如MD5加密的密码char(33)、昵称char(8)等    当最大长度远大于平均长度并且发生更新的时候。

 

注意当一些英文或数据的时候,最好用每个字符用字节少的类型,如latin1

(4)整型、整形优先原则

Tinyint、smallint、mediumint、int、bigint,分别需要8、16、24、32、64。

值域范围:-2^(n-1)~ 2^(n-1)-1

很多程序员在设计数据表的时候很习惯的用int,压根不考虑这个问题

笔者建议:能用tinyint的绝不用smallint

误区:int(1) 和int(11)是一样的,唯一区别是mysql客户端显示的时候显示多少位。

整形优先原则:能用整形的不用其他类型替换,如ip可以转换成整形保存,如商品价格‘50.00元’则保存成50

(5)精确度与空间的转换。在存储相同数值范围的数据时,浮点数类型通常都会比DECIMAL类型使用更少的空间。FLOAT字段使用4字节存储

数据。DOUBLE类型需要8 个字节并拥有更高的精确度和更大的数值范围,DECIMAL类型的数据将会转换成DOUBLE类型。

2、 sql语句优化


mysql> createtableone (

id smallint(10) notnullauto_increment primarykey,

username char(8) notnull,

passwordchar(4) notnull,

`level` tinyint (1) default0,

last_login char(15) notnull,

index(username,password,last_login))engine=innodb;

这是test表,其中id是主键,多列索引(username,password,last_login),里面有10000多条数据.

 

| Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null|

Index_type | Comment |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

| one | 0 | PRIMARY| 1 | id | A |20242 | NULL| NULL| |

BTREE | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

| one | 1 | username | 1 | username | A |10121 | NULL| NULL| |

BTREE | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

| one | 1 | username | 2 | password| A |10121 | NULL| NULL| YES |

BTREE | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

| one | 1 | username | 3 | last_login | A |20242 | NULL| NULL| |

BTREE | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

(1) 最左前缀原则

定义:最左前缀原则指的的是在sql where 字句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现,只要出现非顺序出现、断层都无法利用到多列索引。

举例说明:上面给出一个多列索引(username,password,last_login),当三列在where中出现的顺序如(username,password,last_login)、(username,password)、(username)才能用到索引,如下面几个顺序(password,last_login)、(passwrod)、(last_login)—这三者不从username开始,(username,last_login)—断层,少了password,都无法利用到索引。

因为B+tree多列索引保存的顺序是按照索引创建的顺序,检索索引时按照此顺序检索

测试:以下测试不精确,这里只是说明如何才能正确按照最左前缀原则使用索引。还有的是以下的测试用的时间0.00sec看不出什么时间区别,因为数据量只有20003条,加上没有在实体机上运行,很多未可预知的影响因素都没考虑进去。当在大数据量,高并发的时候,最左前缀原则对与提高性能方面是不可否认的。

Ps:最左前缀原则中where字句有or出现还是会遍历全表

(1.1)能正确的利用索引

l Where子句表达式顺序是(username)

 

mysql> explain select* fromone whereusername='abgvwfnt';

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

| id | select_type | table| type | possible_keys | key| key_len | ref |rows| Extra |

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where|

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

1 row inset(0.00 sec)

l Where子句表达式顺序是(username,password)

 

mysql> explain select* fromone whereusername='abgvwfnt'andpassword='123456';

+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+

| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |

+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+

| 1 | SIMPLE | one | ref | username | username | 43 | const,const | 1 | Using where|

(1.2)不能正确的利用索引

l Where子句表达式顺序是(password, last_login)

mysql> explain select* fromone wherepassword='123456'andlast_login='1338251170';
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one | ALL| NULL| NULL| NULL| NULL| 20146 | Using where|
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row inset(0.00 sec)


l Where 子句表达式顺序是(last_login)

 

mysql> explain select* fromone wherelast_login='1338252525';

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| 1 | SIMPLE | one | ALL| NULL| NULL| NULL| NULL| 20146 | Using where|

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row inset(0.00 sec)

以上的两条语句都不是以username开始,这样是用不了索引,通过type=all(全表扫描),key_len=null,rows都很大20146

Ps:one表里只有20003条数据,为什么出现20146,这是优化器对表的一个估算值,不精确的。

l Where 子句表达式虽然顺序是(username,password, last_login)或(username,password)但第一个是有范围’<’、’>’,’<=’,’>=’等出现

 

mysql> explain select* fromone whereusername>'abgvwfnt'andpassword='123456'andlast_login='1338251170';

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| 1 | SIMPLE | one | ALL| username | NULL| NULL| NULL| 20146 | Using where|

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row inset(0.00 sec)

这个查询很明显是遍历所有表,一个索引都没用到,非第一列出现范围(password列或last_login列),则能利用索引到首先出现范围的一列,也就是“where username=’abgvwfnt’ and password >’123456′and last_login=’1338251170′;”或则“where username=’abgvwfnt’ and password >’123456′and last_login<’1338251170′;”索引长度ref_len=43,索引检索到password列,所以考虑多列索引的时候把那些查询语句用的比较的列放在最后(或非第一位)。

l 断层,即是where顺序(username, last_login)

 

mysql> explain select* fromone whereusername='abgvwfnt'andlast_login='1338252525';

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where|

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

1 row inset(0.00 sec)

注意这里的key_len=24=8*3(8是username的长度,3是utf8编码),rows=5,和下面一条sql语句搜索出来一样

 

mysql> select* fromone whereusername='abgvwfnt';

+-------+----------+----------+-------+------------+

| id | username | password| level| last_login |

+-------+----------+----------+-------+------------+

| 3597 | abgvwfnt | 234567 | 0 | 1338251420 |

| 7693 | abgvwfnt | 456789 | 0 | 1338251717 |

| 11789 | abgvwfnt | 456789 | 0 | 1338251992 |

| 15885 | abgvwfnt | 456789 | 0 | 1338252258 |

| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |

+-------+----------+----------+-------+------------+

5 rowsinset(0.00 sec)

 

mysql> select* fromone whereusername='abgvwfnt'andlast_login='1338252525';

+-------+----------+----------+-------+------------+

| id | username | password| level| last_login |

+-------+----------+----------+-------+------------+

| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |

+-------+----------+----------+-------+------------+

1 row inset(0.00 sec)

这个就是要的返回结果,所以可以知道断层(username,last_login),这样只用到username索引,把用到索引的数据再重新检查last_login条件,这个相对全表查询来说还是有性能上优化,这也是很多sql优化文章中提到的where 范围查询要放在最后(这不绝对,但可以利用一部分索引)

(1.3)如果一个查询where子句中确实不需要password列,那就用“补洞”。

 


mysql> selectdistinct(password) fromone;

+----------+

| password|

+----------+

| 234567 |

| 345678 |

| 456789 |

| 123456 |

+----------+

4 rowsinset(0.08 sec)

可以看出password列中只有这几个值,当然在现实中不可能密码有这么多一样的,再说数据也可能不断更新,这里只是举例说明补洞的方法


mysql> explain select* fromone whereusername='abgvwfnt'andpasswordin('123456','234567'数据库减少碎片,这就通过optimize命令。

如对MyisAM表操作:optimize table 表名

对于InnoDB表是不支撑optimize操作,否则提示“Table does not support optimize, doing recreate + analyze instead”,当然也可以通过命令:alter table one type=innodb; 来替代。

l Analyze 用来分析和存储表的关键字的分布,使得系统获得准确的统计信息,影响 SQL 的实行计划的生成。对于数据基本没有发生变化的表,是不需要经常进行表分析的。但是如果表的数据量变化很明显,用户感觉实际的实行计划和预期的实行计划不 同的时候,实行一次表分析可能有助于产生预期的实行计划。

Analyze table 表名

l Check检查表或者视图是否存在错误,对 MyISAM 和 InnoDB 存储引擎的表有作用。对于 MyISAM 存储引擎的表进行表检查,也会同时更新关键字统计数据

l Repair optimize需要有足够的硬盘空间,否则可能会破坏表,导致不能操作,那就要用上repair,注意INNODB不支撑repair操作

以上的操作出现的都是如下这是check

+———-+——-+————–+————-+

| Table | Op | Msg_type| Msg_text |

+———-+——-+————–+————-+

| test.one | check | status | OK |

+———-+——-+————–+————-+

其中op是option 可以是repair check optimize,msg_type 表示信息类型,msg_text 表示信息类型,这里就说明表的状态正常。如在innodb表使用repair就出现note | The storage engine for the table doesn’t support repair

注意:以上操作最好在数据库访问量最低的时候操作,因为涉及到很多表锁定,扫描,数据迁移等操作,否则可能导致一些功能无法正常使用甚至数据库崩溃。

(3)表结构的更新与维护

l 改表结构。当要在数据量千万级的数据表中使用alter更改表结构的时候,这是一个棘手问题。一种方法是在低并发低访问量的时候用平常的alter更改表。另外一种就是建另一个与要修改的表,这个表除了要修改的结构属性外其他的和原表一模一样,这样就能得到一个相应的.frm文件,然后用flush with read lock 锁定读,然后覆盖用新建的.frm文件覆盖原表的.frm,最后unlock table 释放表。

l 建立新的索引。一般方法这里不说。

1、 创建没索引的a表,导入数据形成.MYD文件。

2、 创建包括索引b表,形成.FRM和.MYI文件

3、 锁定读写

4、 把b表的.FRM和.MYI文件改成a表名字

5、 解锁

6、 用repair创建索引。

这个方法对于大表也是很有效的。这也是为什么很多dba坚持说“先导数据库在建索引,这样效率更快”

l 定期检查mysql服务器

定期使用show status、show processlist等命令检查数据库。这里就不细说,这说起来也篇幅是比较大的,笔者对这个也不是很了解

第四部分:图说mysql查询实行流程

 

文章评论

热门栏目

XML 地图 | Sitemap 地图