《索引结构原理、性能分析与优化.》由会员分享，可在线阅读，更多相关《索引结构原理、性能分析与优化.（27页珍藏版）》请在金锄头文库上搜索。
1、Benwin s blog PHP|APACHE|MYSQL|别以为你很厉害，大牛多的是！ 碎语碎语 细节灵感细节灵感 作品作品 on 2012-6-1 14:51 Friday by Benwin 分类:mysql 由浅入深探究 mysql 索引结构原理、性能分析与优化 摘要： 第一部分：基础知识 第二部分：MYISAM 和 INNODB 索引结构 1、 简单介绍 B-tree B+ tree 树 2、 MyisAM 索引结构 3、 Annode 索引结构 4、 MyisAM 索引与 InnoDB 索引相比较 第三部分：MYSQL 优化 1、表数据类型选择 2、sql 语句优化 (1) 最左
2、前缀原则 (1.1) 能正确的利用索引 (1.2) 不能正确的利用索引 (1.3) 如果一个查询 where 子句中确实不需要 password 列，那 就用“补洞”。 (1.4) like (2) Order by 优化 (2.1) filesort 优化算法. (2.2) 单独 order by 用不了索引，索引考虑加 where 或加 limit (2.3) where + orerby 类型，where 满足最左前缀原则，且 orderby 的列和 where 子句用到的索引的列的子 集。即是(a,b,c)索引，where 满足最左前缀原则且 order by 中列 a、b、c 的任意
3、组合 (2.4) where + orerby+limit (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 查询执行流程 第一部分：基础知识：第一部分：基础知识： 索引索引 官方介绍
4、索引是帮助 MySQL 高效获取数据的数据结构。笔者理解索引相当于一 本书的目录，通过目录就知道要的资料在哪里，不用一页一页查阅找出需要的 资料。关键字 index - 唯一索引唯一索引 强调唯一，就是索引值必须唯一，关键字 unique index 创建索引： 1、create uniqueunique indexindex 索引名 on 表名(列名); 2、alter table 表名 add uniqueunique indexindex 索引名 (列名); 删除索引： 1、 drop index 索引名 on 表名; 2、 alter table 表名 drop index 索引名;
5、- 主键主键 主键就是唯一索引的一种，主键要求建表时指定，一般用 auto_increatment 列， 关键字是 primary key 主键创建： creat table test2 (id int not null primaryprimary keykey auto_increment); - 全文索引全文索引 InnoDB 不支持，Myisam 支持性能比较好，一般在 CHAR、VARCHAR 或 TEXT 列 上创建。 Create table 表名( id int not null primary anto_increment,title varchar(100),FULLTEX
6、TFULLTEXT(title)type=myisammyisam - 单列索引与多列索引单列索引与多列索引 索引可以是单列索引也可以是多列索引（也叫复合索引）。按照上面形式创建 出来的索引是单列索引，现在先看看创建多列索引： create table test3 (id int not null primary key auto_increment,uname char (8) not null default ,password char(12) not null,INDEXINDEX(uname,password)type =myisam; 注意：INDEX(a, b, c)可以当做 a
7、 或(a, b)的索引来使用，但和 b、c 或(b,c) 的索引来使用这是一个最左前缀最左前缀的优化方法，在后面会有详细的介绍，你只要 知道有这样两个概念 - 聚集索引聚集索引 一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。 聚集索引 确定表中数据的物理顺序。Mysql 中 myisam 表是没有聚集索引的，innodb 有 （主键就是聚集索引），聚集索引在下面介绍 innodb 结构的时有详细介绍。 - 查看表的索引查看表的索引 通过命令：Show index from 表名 如： 1.mysql show index from test3; 2.+-+-+-+-+-+-+-
8、-+-+-+-+ 3.| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | C ardinality | Sub_part | 4.Packed | Null | Index_type | Comment | 5.+-+-+-+-+-+-+- -+-+-+-+ 6.| test3 | 0 | PRIMARY | 1 | id | A | 0 | NULL | 7.NULL | | BTREE | | 8.+-+-+-+-+-+-+- -+-+-+-+ Table：表名 Key_name：什么类型索引
9、（这了是主键） Column_name：索引列的字段名 Cardinality：索引基数，很关键的一个参数，平均数值组=索引基数/表总数据 行，平均数值组越接近 1 就越有可能利用索引 Index_type：如果索引是全文索引，则是 fulltext,这里是 b+tree 索引， b+tre 也是这篇文章研究的重点之一 其他的就不详细介绍，更多： 第二部分：第二部分：MYISAMMYISAM 和和 INNODBINNODB 索引结构索引结构 1 1、 简单介绍简单介绍 B-treeB-tree B+B+ treetree 树树 B-tree 结构视图 一棵 m 阶的 B-tree 树，则有以下
10、性质 （1）Ki 表示关键字值，上图中，k1 create table one ( 2.id smallint(10) not null auto_increment primary key, 3.username char(8) not null, 4.password char(4) not null, 5.level tinyint (1) default 0, 6.last_login char(15) not null, 7.index(username,password,last_login)engine=innodb; 这是 test 表，其中 id 是主键，多列索引(usern
11、ame,password,last_login),里面有 10000 多条 数据. 1.| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | C ardinality | Sub_part | Packed | Null | 2. Index_type | Comment | 3.+-+-+-+-+-+-+- -+-+-+-+ 4.| one | 0 | PRIMARY | 1 | id | A |20242 | NULL | NULL | | 5. BTREE | | 6.+-+-+-+-+-+-
12、+- -+-+-+-+ 7.| one | 1 | username | 1 | username | A |1012 1 | NULL | NULL | | 8.BTREE | | 9.+-+-+-+-+-+-+- -+-+-+-+ 10.| one | 1 | username | 2 | password | A |1012 1 | NULL | NULL | YES | 11. BTREE | | 12.+-+-+-+-+-+-+- -+-+-+-+ 13.| one | 1 | username | 3 | last_login | A |20 242 | NULL | NULL | | 14. BTREE | | 15.+-+-+-+-+-+-+- -+-+-+-+ (1) 最左前缀原则最左前缀原则 定义定义：最左前缀原则指的的是在 sql where 字句中一些条件或表达式中出现的列的顺 序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序出现、断层都无法 利用到多列索引。 举例说明：上面给出一个多
