由浅入深探究mysql索引结构原理、性能分析与优化


12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 1/25www.phpben.com/?post=74 PHP|APACHE|MYSQL|WEB|LINUX|ZEND 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 2/25www.phpben.com/?post=74 (2.5) 如何考虑order by 来建索引 (3) 隔离列 (4) OR⃝IN⃝UNION ALL,可尝试用UNION ALL (4.1 ) or会遍历表就算有索引 (4.2)关于in (4.2) UNION All (5) 范索引选择性 (6) 复或多余索引 3、系统配置维优化 (1) 要的一些量 (2) Fds optimize⃝Analyze⃝check⃝repair维操作 (3) 表结构的更新维 第四部分说mysql查询执行流程 第一部分:基础知识: 索引 官方介绍索引是帮助MySQL高效获数据的数据结构⃞笔者理解索引相于一书的目录,通过目录就知道要的资料在哪,用一页一页查 阅出需要的资料⃞关键index ------------------------------------------------------------- 唯一索引 强调唯一,就是索引值必唯一,关键unique index 创建索引 1⃝create unique index 索引 on 表(列); 2⃝alter table 表 add unique index 索引 (列); 删除索引 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 3/25www.phpben.com/?post=74 1、 drop index 索引 on 表; 2、 alter table 表 drop index 索引; ------------------------------------------------------------- 主键 键就是唯一索引的一种,键要求建表时指定,一般用auto_increatment列,关键是primary key 键创建 creat table test2 (id int not null primary key auto_increment); ------------------------------------------------------------- 全文索引 InnoDB支持,Myisam支持性能比较好,一般在 CHAR⃝VARCHAR 或 TEXT 列创建⃞ Create table 表( id int not null primary anto_increment,title varchar(100),FULLTEXT(title))type=myisam ------------------------------ 单列索引多列索引 索引可是单列索引可是多列索引复合索引⃞按照面形式创建出来的索引是单列索引,现在先看看创建多列索引 create table test3 (id int not null primary key auto_increment,uname char (8) not null default '',password char(12) not null,INDEX(uname,password))type =myisam; 注意INDEX(a, b, c)可做a或(a, b)的索引来使用,但和b⃝c或(b,c)的索引来使用是一个最左前缀的优化方法,在面会有细的介 绍,你要知道有样个概念 ------------------------------------------------------------- 聚集索引 一种索引,索引中键值的逻辑序定了表中相行的物理序⃞ 聚集索引确定表中数据的物理序⃞Mysql中myisam表是没有聚集索引 的,innodb有键就是聚集索引,聚集索引在面介绍innodb结构的时有细介绍⃞ ------------------------------------------------------------- 查看表的索引 通过命Show index from 表 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 4/25www.phpben.com/?post=74 如 0. mysql> show index from test; 0. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+ 0. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | 0. Packed | Null | Index_type | Comment | 0. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+ 0. | test | 0 | PRIMARY | | id | A | 0 | NULL | 0. NULL | | BTREE | | 0. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+ Table表 Key_name什类型索引了是键 Column_name索引列的段 Cardinality索引基数,很关键的一个参数,平均数值组=索引基数/表总数据行,平均数值组越接1就越有可能利用索引 Index_type如果索引是全文索引,则是fulltext,是b+tree索引,b+tre是篇文章研的点之一 他的就细介绍,更多 第二部分:MYISAM和INNODB索引结构 1、 简单介绍B-tree B+ tree树 B-tree结构视 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 5/25www.phpben.com/?post=74 一棵m阶的B-tree树,则有性质 1Ki表示关键值,中,k15.0则保留 储空间 固定空间,比 喻char(10)管符串是 否有10个符都分配10个 符的空间 Varchar内节空间,但更 新可能发生化, 若varchar(10),开始若储 5个符, update7个时 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 12/25www.phpben.com/?post=74 有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语句优化 0. mysql> create table one 0. id smallint 0 not null auto_increment primary key , 0. username char not null , 0. password char not null , 0. `level ` tinyint default 0, 0. last_login char not null , 0. index username, password ,last_loginengine=innodb; 是test表,中id是键,多列索引userae,password,last_logi,面有多条数据. 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 13/25www.phpben.com/?post=74 0. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | 0. Index_type | Comment | 0. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+ 0. | one | 0 | PRIMARY | | id | A |0 | NULL | NULL | | 0. BTREE | | 0. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+ 0. | one | | username | | username | A |0 | NULL | NULL | | 0. BTREE | | 0. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+ 0. | one | | username | | password | A |0 | NULL | NULL | YES | . BTREE | | . +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+ . | one | | username | | last_login | A |0 | 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) 0. mysql> explain select * from one where username= 'abgvwfnt' ; 0. +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 0. +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+ 0. | | SIMPLE | one | ref | username | username | | const | | Using where | 0. +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+ 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 14/25www.phpben.com/?post=74 0. row in set 0.00 sec l Where子表达式 序是(username,password) 0. mysql> explain select * from one where username= 'abgvwfnt' and password ='' ; 0. +----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 0. +----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+ 0. | | SIMPLE | one | ref | username | username | | const,const | | Using where | 0. +----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+ 0. row in set 0.00 sec l Where子表达式 序是(username,password, last_login) 0. mysql> explain select * from one where username= 'abgvwfnt' and password ='' and last_login= '0' ; 0. +----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | 0. +----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+ 0. | | SIMPLE | one | ref | username | username | | const,const,const | | Using where | 0. +----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+ 0. row in set 0.00 sec 面可看出type=ref 是多列索引,key_len分别是24⃝43⃝83,说明用到的索引分别是(username), (username,password), (username,password, last_login );row分别是5⃝1⃝1检索的数据行都很少,因个查询都按照索引前缀原则,可利用到索引⃞ (1.2)能正确的利用索引 l Where子表达式 序是(password, last_login) 0. mysql> explain select * from one where password ='' and last_login= '0' ; 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. | | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 0 | Using where | 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. row in set 0.00 sec l Where 子表达式序是(last_login) 0. mysql> explain select * from one where last_login= '' ; 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 15/25www.phpben.com/?post=74 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. | | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 0 | Using where | 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. row in set 0.00 sec 的条语都是username开始,样是用了索引,通过type=all全表扫描,key_len=null,rows都很大20146 Psone表有20003条数据,什出现20146,是优化器对表的一个估算值,精确的⃞ l Where 子表达式虽然序是(username,password, last_login)或(username,password)但第一个是有范围’<’⃝’>’,’<=’,’>=’等出现 0. mysql> explain select * from one where username> 'abgvwfnt' and password = '' and last_login= '0' ; 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. | | SIMPLE | one | ALL | username | NULL | NULL | NULL | 0 | Using where | 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. row in set 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) 0. mysql> explain select * from one where username= 'abgvwfnt' and last_login= '' ; 0. +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 0. +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+ 0. | | SIMPLE | one | ref | username | username | | const | | Using where | 0. +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+ 0. row in set 0.00 sec 注意的key_len=24=8*3(8是username的长度,3是utf8编码),rows=5,和面一条sql语搜索出来一样 0. mysql> select * from one where username= 'abgvwfnt' ; 0. +-------+----------+----------+-------+------------+ 0. | id | username | password | level | last_login | 0. +-------+----------+----------+-------+------------+ 0. | | abgvwfnt | | 0 | 0 | 0. | | abgvwfnt | | 0 | | 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 16/25www.phpben.com/?post=74 0. | | abgvwfnt | | 0 | | 0. | | abgvwfnt | | 0 | | 0. | | abgvwfnt | | 0 | | 0. +-------+----------+----------+-------+------------+ . rows in set 0.00 sec . . mysql> select * from one where username= 'abgvwfnt' and last_login= '' ; . +-------+----------+----------+-------+------------+ . | id | username | password | level | last_login | . +-------+----------+----------+-------+------------+ . | | abgvwfnt | | 0 | | . +-------+----------+----------+-------+------------+ . row in set 0.00 sec 个就是要的返回结果,所可知道断层(username,last_login),样用到username索引,把用到索引的数据再新检查last_login条 ,个相对全表查询来说是有性能优化,是很多sql优化文章中提到的where 范围查询要放在绝对,但可利用一部分索 引 (1.3)如果一个查询where子句中确实需要password列,那就用补洞 0. mysql> select distinct password from one; 0. +----------+ 0. | password | 0. +----------+ 0. | | 0. | | 0. | | 0. | | 0. +----------+ 0. rows in set 0.0 sec 可看出password列中有几个值,然在现实中可能密码有多一样的,再说数据可能断更新,是举例说明补洞的方法 0. mysql> explain select * from one where username= 'abgvwfnt' and password in '' ,'' ,'' ,'' 0. and last_login= '0' ; 0. +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 0. +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ 0. | | SIMPLE | one | range | username | username| | NULL | | Using where | 0. +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ 0. row in set 0.00 sec 可看出ref=83 所有的索引都用到了,type=range是因用了in子⃞ 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 17/25www.phpben.com/?post=74 个被‚补洞‛列中的值是有限的,可预知的,如性别,值有男和女加多一个男女无妨⃞ ‚补洞‛方法有瓶颈,很多列,且需要补洞的相列可多列的值虽有限但很多如中城的时候,优化器在优化时组合来的 数量是很大,样的话就要做好基准测试和性能分析,衡得失,得一个合理的优化方法⃞ (1.4)like 0. mysql> explain select * from one where username like 'abgvwfnt%' ; 0. +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref | 0. rows | Extra | 0. +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ 0. | | SIMPLE | one | range | username | username | | NULL | 0. | Using where | 0. +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ 0. row in set 0.00 sec 0. mysql> explain select * from one where username like '%abgvwfnt%' ; . +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ . | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | . +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ . | | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 0 | Using where | . +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ . row in set 0.0 sec 对比就知道like操作abgvwfnt%能用到索引,%abgvwfnt%用到 --------------------------------------------------------------------------------------------- (2) Order by 优化 (2.1)filesort优化算法. 在mysql version()<4.1之前,优化器采用的是filesort第一种优化算法,先提键值和指针,排序再去提数据,前要搜索数据次,第一 次若能使用索引则使用,第次是机读然引擎⃞mysql version()>=4.1,更新了一个新算法,就是在第一次读的时候 把selcet的列读出来,然在sort_buffer_size中排序够大则建临时表保排序序,算法需要一次读数据⃞所有个广人 传的一个优化方法,那就是增大sort_buffer_size⃞Filesort第种算法要用到更的空间,sort_buffer_size够大反而会影响速度,所mysql开 发团队定了个量max_length_for_sort_data,算法中读出来的需要列的数据的大小超过量的值才使用,所一般性能分析的时候会尝 试把max_length_for_sort_data改小⃞ (2.2)单独order by 用了索引,索引考虑加where 或加limit 先建一个索引(last_login),建的过程就给出了 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 18/25www.phpben.com/?post=74 0. mysql> explain select * from one order by last_login desc ; 0. +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref | rows 0. | Extra | 0. +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+ 0. | | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 0 0. | Using filesort | 0. +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+ 0. row in set 0.00 sec 0. . mysql> explain select * from one order by last_login desc limit 0; . +----+-------------+-------+-------+---------------+------------+---------+------+------+-------+ . | id | select_type | table | type | possible_keys | key | key_len | ref . | rows | Extra | . +----+-------------+-------+-------+---------------+------------+---------+------+------+-------+ . | | SIMPLE | one | index | NULL | last_login | | NULL . | 0 | | . +----+-------------+-------+-------+---------------+------------+---------+------+------+-------+ . row in set 0.00 sec 开始没limit查询是遍历表的,加了limit,索引可使用,看key_len 和key (2.3)where + orerby 类型,where满足左前缀原则,且orderby的列和where子用到的索引的列的子集⃞即是(a,b,c)索引,where满足左 前缀原则且order by中列a⃝b⃝c的任意组合 0. mysql> explain select * from one where username= 'abgvwfnt' and password =' 0. ' and last_login=' 00' order by password desc ,last_login desc ; 0. 0. +----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref 0. 0. | rows | Extra | 0. +----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+ 0. | | SIMPLE | one | ref | username | username | | const,c 0. onst,const | | Using where | . +----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+ . row in set 0.00 sec . . mysql> explain select * from one where username= 'abgvwfnt' and password =' . ' and last_login=' 00' order by password desc ,level desc ; . +----+-------------+-------+------+---------------+----------+---------+-------------------+------+----------------------- -----+ . | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | . +----+-------------+-------+------+---------------+----------+---------+-------------------+------+----------------------- ------+ . | | SIMPLE | one | ref | username | username | | const,c 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 19/25www.phpben.com/?post=74 0. onst,const | | Using where ; Using filesort | . +----+-------------+-------+------+---------------+----------+---------+-------------------+------+----------------------- ------+ . . row in set 0.00 sec 面条语明显的别是多了一个非索引列level的排序,在extra列对了Using filesort 笔者测试结果where满足左前缀且order by中的列是多列索引的子集时就是说orerby中没左前缀原则限制,管是否有asc ,desc混合出现,都能用索引来满足order by⃞ 笔者测试过,因篇幅比较大,就一一列出⃞ Ps:很优化博文都说order by中的列要where中出现的列是索引的序一,笔者认够谨⃞ (2.3) where + orerby+limit 个实差多,要where左前缀,orderby确,limit在影响大 (2.4)如何考虑order by来建索引 个回到创建索引的题来,在比较常用的oder by的列和where中常用的列建立多列索引,样优化来的广度和扩张性都比较好,然如 果要考虑UNION⃝JOIN⃝COUNT⃝IN等来就复很多了 (3) 隔离列 隔离列是查询语中把索引列隔离出来,就是说能在语中把列包含表达式中,如id+1=2⃝inet_aton('210.38.196.138')---ip转换整 数⃝convert(123,char(3))---数转换符串⃝date函数等mysql内置的大多函数⃞ 非隔离列影响性能很大甚是命的,就是集网石展的⃣十六军规⃤中的一条,虽然他没说明是隔离列⃞ 就测试一 首先建立一个索引(last_login ),就给出建立的码了,且把last_login改整型是了方便测试,并是影响条 0. mysql> explain select * from one where last_login = 0; 0. +----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 0. +----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+ 0. | | SIMPLE | one | ref | last_login | last_login | | const 0. | | Using where | 0. +----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+ 0. row in set , warning 0.00 sec 容易看出建的索引已效 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 20/25www.phpben.com/?post=74 0. mysql> explain select * from one where last_login += 0 ; 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref | rows 0. | Extra | 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. | | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 0 0. | Using where | 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. row in set 0.00 sec last_login +1=8388608非隔离列的出现导查的列20197,说明是遍历整张表且索引能使用⃞ 是因条语要出所有last_login的数据,然+1再和20197比较,优化器在方面比较差,性能很差⃞ 所要可能的把列隔离出来,如last_login +1= 8388606改login_login=8388607,或者把计算⃝转换等操作先用php函数处理过再传递 给mysql服务器 (4) ORINUNION ALL,可以尝试用UNION ALL (4.1)or会遍历表就算有索引 0. mysql> explain select * from one where username = 'abgvwfnt' or password ='' ; 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. | | SIMPLE | one | ALL | username | NULL | NULL | NULL | 0 | Using where | 0. +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 0. row in set 0.00 sec (4.2)对于in,个是有争议的,网很多优化方案中都提到量少用in,全面,实在in面如果是常量的话,可一大胆的用in,个 是集网石展⃝阿hellodab的点笔者从微博中获知⃞用hellodab一话‚MySQL用IN效率好,通常是指in中嵌套一个子查询,因 MySQL的查询写可能会产生一个好的执行计划,而如果in面是常量的话,认性能没有任何题,可放心使用‛---------然对 于个比较的话,没有实战数据的话很难辩解,就算有,影响性能的因素很多,许会每个dba都有的测试结果.签左前缀 中‚补洞‛一个方法 (4.3)UNION All 直接返回并集,可避免去的开⃞之所说‚尝试‛用UNION All OR来优化sql语,因是一直能优化的了, 是作一个方法去尝试⃞ (5) 索引选择性 索引选择性是复的索引值基数cardinality表中数据行数的比值,索引选择性=基数/数据行,基数可通过‚show index from 表 ‛查看⃞ 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 21/25www.phpben.com/?post=74 高索引选择性的好处就是mysql查配的时候可过滤更多的行,唯一索引的选择性佳,值1⃞ 那对于非唯一索引或者说要被创建索引的列的数据内容很长,那就要选择索引前缀⃞就简单说明一 0. mysql> select count distinct username/ count * from one; 0. +------------------------------------+ 0. | count distinct username/ count * | 0. +------------------------------------+ 0. | 0.0 | 0. +------------------------------------+ 0. row in set 0.0 sec count(distinct(username))/count(*)就是索引选择性的值,0.2太小了⃞ 假如username列数据很长,则可通过 select count(distinct(concat(first_name, left(last_name, N))/count(*) from one;测试出接1的索引选择性,中N是索引的长度,举法去 出N的值,然再建索引⃞ (6) 重复或多余索引 很多phper开始都建索引相对多点性能就好点,压根没考虑到有些索引是复的,比如建一个(username),(username,password), (username,password,last_login),很明显第一个索引是复的,因者都能满足功能⃞ 要有个意识就是,在满足功能需求的情况建少索引⃞对于INNODB引擎的索引来说,每次修改数据都要把键索引,辅助索引中相索引 值修改,可能会出现大量数据迁移,分页,及碎片的出现⃞ 3系统配置维护优化 (1) 重要的一些变量 l key_buffer_size索引块缓大小, 针对MyISAM储引擎,值越大,性能越好.但是超过操作系统能的大值,反而会使mysql得稳 定. ----是很要的参数 l sort_buffer_size 是索引在排序缓大小,若排序数据大小超过值,则创建临时文,注意和myisam_sort_buffer_size的别---- 是很要的参数 l read_rnd_buffer_size排序按排序的序读行时,则通过缓读行,避免搜索硬盘⃞将量置较大的值可大大改 ORDER BY的性能⃞但是,是每个客户端分配的缓,因你将全局量置较大的值⃞相反,需要行大查询的客 户端更改会话量 l join_buffer_size用于表间关联(join)的缓大小 l tmp_table_size缓表的大小 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 22/25www.phpben.com/?post=74 l table_cache允许 MySQL 打开的表的大个数,并且些都cache在内中 l delay_key_write针对MyISAM储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但up索引,将索引在内,表关 时,将内索引,写到磁盘 更多参数查看http://www.phpben.com/?post=70 (2) optimizeAnalyzecheckrepair维护操作 l optimize 数据在插入,更新,删除的时候难免一些数据迁移,分页,之就出现一些碎片,久而久之碎片累来影响性能,就需 要DBA定期的优化数据减少碎片,就通过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 0. +----------+-------+--------------+-------------+ 0. | Table | Op | Msg_type| Msg_text | 0. +----------+-------+--------------+-------------+ 0. | test.one | check | status | OK | 0. +----------+-------+--------------+-------------+ 中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更改表⃞外一 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 23/25www.phpben.com/?post=74 种就是建一个要修改的表,个表除了要修改的结构属性外他的和原表一模一样,样就能得到一个相的.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查询执行流程 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 24/25www.phpben.com/?post=74 1、 查询缓,判断sql语是否完全配,再判断是否有限,个判断假则到解析器解析语,真则提数据结果返回给用户⃞ 2、 解析器解析⃞解析器先词法分析,语法分析,检查错误比如引有没合等,然生解析树⃞ 3、 预处理⃞预处理解解析器无法解的语,如检查表和列是否在,别是否有错,生新的解析树⃞ 4、 优化器做大量的优化操作⃞ 5、 生执行计划⃞ 6、 查询执行引擎,负责调度引擎获相数据 7、 返回结果⃞ 这篇博文准备,写,将用了一个时间!终于写完,但真的学了很多东西! 纰漏请联系:benwin(bw@7bus.net/445235728@qq.com) 参考: 12-8-21 由浅入深探究mysql索引结构原理、性能分析与优化 - Benwin 's blog 25/25www.phpben.com/?post=74 « 各种浏览器审查、监听http头工具介绍 查看表的储引擎结构“show table status like ‘表名’”» http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html http://www.cnblogs.com/oldhorse/archive/2009/11/16/1604009.html http://blog.csdn.net/zuiaituantuan/article/details/5909334 http://www.codinglabs.org/html/theory-of-mysql-index.html http://isky000.com/database/mysql_order_by_implement http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html http://www.docin.com/p-211669085.html 标签: mysql索引结构 inndbo b-树 sql优化 Powered by benwin Entries RSS
还剩24页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 5 人已下载

下载pdf

pdf贡献者

y6fn

贡献于2014-11-15

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf