来源：蜘蛛抓取(WebSpider)
时间：2016-10-21 02:38
标签：
mysql添加组合索引
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
您的访问请求被拒绝 403 Forbidden - ITeye技术社区
您的访问请求被拒绝
亲爱的会员，您的IP地址所在网段被ITeye拒绝服务，这可能是以下两种情况导致：
一、您所在的网段内有网络爬虫大量抓取ITeye网页，为保证其他人流畅的访问ITeye，该网段被ITeye拒绝
二、您通过某个代理服务器访问ITeye网站，该代理服务器被网络爬虫利用，大量抓取ITeye网页
请您点击按钮解除封锁&MySQL索引分为普通索引、唯一性索引、全文索引、单列索引、多列索引等等。这里将为大家介绍着几种索引各自的用途。
一、 MySQL: 索引以B树格式保存
Memory存储引擎可以选择Hash或BTree索引，Hash索引只能用于=或&=&的等式比较。
1、普通索引：create index on Tablename(列的列表
一、 MySQL: 索引以B树格式保存 Memory存储引擎可以选择Hash或BTree索引，Hash索引只能用于=或&=&的等式比较。 1、普通索引：create index on Tablename(列的列表) alter table TableName add index (列的列表) create table TableName([...], index [IndexName] (列的列表) 2、唯一性索引：create unique index alter ... add unique 主键：一种唯一性索引，必须指定为primary key 3、全文索引：从3.23.23版开始支持全文索引和全文检索，FULLTEXT， 可以在char、varchar或text类型的列上创建。 4、单列索引、多列索引： 多个单列索引与单个多列索引的查询效果不同，因为： 执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。 5、最左前缀(Leftmost Prefixing)：多列索引，例如：fname_lname_age索引，以下的搜索条件MySQL都将使用 fname_lname_age索引：firstname,lastname,firstname,firstname，其他情况将不使用。 二、根据sql查询语句确定创建哪种类型的索引，如何优化查询 选择索引列： a.性能优化过程中，选择在哪个列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有 两种类型的列：在where子句中出现的列，在join子句中出现的列。 b.考虑列中值的分布，索引的列的基数越大，索引的效果越好。 c.使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。 d.利用最左前缀 e.不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。 在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。 MySQL只对一下操作符才使用索引：&,&=,=,&,&=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)。 mysql 索引分类 在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令 MySQL的查询和运行更加高效。索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。 1、普通型索引 这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建： （1）创建索引，例如CREATE INDEX 索引的名字 ON tablename (列名1，列名2,...); （2）修改表，例如ALTER TABLE tablename ADD INDEX 索引的名字 (列名1，列名2,...); （3）创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX 索引的名字 (列名1，列名 2,...) ); 2、唯一索引 这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建： （1）创建索引，例如CREATE UNIQUE INDEX 索引的名字 ON tablename (列的列表); （2）修改表，例如ALTER TABLE tablename ADD UNIQUE 索引的名字 (列的列表); （3）创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE 索引的名字 (列的列 表) ); 3、主键 主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。 （主键相当于聚合索引，是查找最快的索引） 4、单列索引和多列索引 索引可以是单列索引，也可以是多列索引。 （1）单列索引就是常用的一个列字段的索引，常见的索引。 （2）多列索引就是含有多个列字段的索引 alter table student add index sy(name,age，score); 索引sy就为多列索引，多列索引在以下几中情况下才能有效： select * from student where name='jia' and age&='12' //where条件中含有索引的首列字段和 第二个字段 select * from student where name='jia' //where条件中只含有首列字段 select * from student where name='jia' and score&60//where条件中含有首列字段和第三个字 段 总结：多列索引只有在where条件中含有索引中的首列字段时才有效 5、选择索引列 应该怎样选择索引列，首先要看查询条件，一般将查询条件中的列作为索引SQL与数据库（71）
MySQL单列索引是我们使用MySQL数据库中经常会见到的，MySQL单列索引和组合索引的区别可能有很多人还不是十分的了解，下面就为您分析两者的主要区别，供您参考学习。
为了形象地对比两者，再建一个表：
CREATE TABLE myIndex (
&i_testID INT NOT NULL AUTO_INCREMENT,&
vc_Name VARCHAR(50) NOT NULL,&
vc_City VARCHAR(50) NOT NULL,&
i_Age INT NOT NULL,&
i_SchoolID INT NOT NULL,&
PRIMARY KEY (i_testID)&
在这 10000 条记录里面 7 上 8 下地分布了 5 条 vc_Name=&erquan& 的记录，只不过 city,age,school 的组合各不相同。
来看这条T-SQL：
SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='郑州' AND i_Age=25;
首先考虑建MySQL单列索引：
在vc_Name列上建立了索引。执行 T-SQL 时，MYSQL 很快将目标锁定在了vc_Name=erquan 的 5 条记录上，取出来放到一中间结果集。在这个结果集里，先排除掉 vc_City 不等于&郑州&的记录，再排除 i_Age 不等于 25 的记录，最后筛选出唯一的符合条件的记录。
虽然在 vc_Name 上建立了索引，查询时MYSQL不用扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样的，在 vc_City 和 i_Age 分别建立的MySQL单列索引的效率相似。
为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。就是将 vc_Name,vc_City，i_Age 建到一个索引里：
ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);
建表时，vc_Name 长度为 50，这里为什么用 10 呢？因为一般情况下名字的长度不会超过 10，这样会加速索引查询速度，还会减少索引文件的大小，提高 INSERT 的更新速度。
执行 T-SQL 时，MySQL 无须扫描任何记录就到找到唯一的记录。
肯定有人要问了，如果分别在 vc_Name,vc_City，i_Age 上建立单列索引，让该表有 3 个单列索引，查询时和上述的组合索引效率一样吗？大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。
建立这样的组合索引，其实是相当于分别建立了
(vc_Name,vc_City,i_Age) & ( vc_Name,vc_City ) ( vc_Name)
这样的三个组合索引!为什么没有 vc_City，i_Age 等这样的组合索引呢？这是因为 mysql 组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个
T-SQL 会用到：
SELECT * FROM myIndex WHREE vc_Name=&erquan& AND vc_City=&郑州&
SELECT * FROM myIndex WHREE vc_Name=&erquan&
而下面几个则不会用到：
SELECT * FROM myIndex WHREE i_Age=20 AND vc_City=&郑州&&
SELECT * FROM myIndex WHREE vc_City=&郑州&
1. 为什么使用索引
在无索引的情况下，MySQL会扫描整张表来查找符合sql条件的记录，其时间开销与表中数据量呈正相关。对关系型数据表中的某些字段建索引可以极大提高查询速度（当然，不同字段是否selective会导致这些字段建立的索引对查询速度的提升幅度不同，而且索引也并非越多越好，因为写入或删除时需要更新索引信息）。
对于MySQL的Innodb储存引擎来说，大部分类型的index均以B-Tree数据结构的变种B+Tree来存储（MEMORY类型的表还支持hash类型的索引）。B-Tree是或文件中常用的一种数据结构，它是一种N叉平衡树，这种树结构保证了同层节点保存的key有序，对于某个节点来说，其左子树保存的所有key均小于该节点保存的key，其右子树保存的所有key均大于该节点保存的key。此外，在工程实现上，还结合操作系统的局部性原理做了很多优化，总之，b-tree的各种特性或优化技巧能保证：1)
查询磁盘记录时，读盘次数最少；2) 任何insert和delete操作对树结构的影响均很小；3) 树本身的rebalance操作很高效。
2. MySQL使用索引的场景
MySQL在以下操作场景下会使用索引：
1) 快速查找符合where条件的记录
2) 快速确定候选集。若where条件使用了多个索引字段，则MySQL会优先使用能使候选记录集规模最小的那个索引，以便尽快淘汰不符合条件的记录。
如果表中存在几个字段构成的联合索引，则查找记录时，这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找。
例如，若为某表创建了3个字段(c1, c2, c3)构成的联合索引，则(c1), (c1, c2), (c1, c2, c3)均会作为索引，(c2, c3)就不会被作为索引，而(c1, c3)其实只利用到c1索引。
4) 多表做join操作时会使用索引（如果参与join的字段在这些表中均建立了索引的话）
5) 若某字段已建立索引，求该字段的min()或max()时，MySQL会使用索引
6) 对建立了索引的字段做sort或group操作时，MySQL会使用索引
& 3. 哪些SQL语句会真正利用索引
从MySQL官网文档&Comparison of B-Tree and Hash Indexes&可知，下面这些类型的SQL可能会真正用到索引：
1) B-Tree可被用于sql中对列做比较的表达式，如=, &, &=, &, &=及between操作
2) 若like语句的条件是不以通配符开头的常量串，MySQL也会使用索引
比如，SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'或SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'可以利用索引，而SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'（以通配符开头）和SELECT
* FROM tbl_name WHERE key_col LIKE other_col（like条件不是常量串）无法利用索引。
对于形如LIKE '%string%'的sql语句，若通配符后面的string长度大于3，则MySQL会利用Turbo Boyer-Moore algorithm算法进行查找。
3) 若已对名为col_name的列建了索引，则形如&col_name is null&的SQL会用到索引
4) 对于联合索引，sql条件中的最左前缀匹配字段会用到索引，示例请参考本文第2节第3条对联合索引的说明
5) 若sql语句中的where条件不只1个条件，则MySQL会进行Index Merge优化来缩小候选集范围
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：1884809次
积分：29636
积分：29636
排名：第130名
原创：1105篇
转载：47篇
评论：497条
大海无边，网际无涯，本博客乃一小站，收集笔记与备忘录，供参考。基本没有原创，又基本全是原创。
(3)(10)(2)(1)(5)(12)(17)(10)(2)(17)(20)(4)(4)(4)(32)(13)(5)(3)(4)(12)(1)(4)(5)(11)(5)(4)(7)(17)(16)(16)(6)(29)(46)(28)(10)(11)(11)(7)(14)(10)(14)(1)(3)(14)(18)(14)(8)(31)(33)(22)(9)(33)(26)(48)(20)(81)(102)(59)(30)(1)(1)(2)(8)(12)(3)(4)(5)(16)(15)(3)(10)(33)(27)(10)问题对人有帮助，内容完整，我也想知道答案
问题没有实际价值，缺少关键内容，没有改进余地
因什么需求，要创建‘联合索引’？最实际好处在于什么？如果是为了更快查询到数据，有单列索引不是ok了，为什么有‘联合索引’的存在？求经验丰富老手谈谈。
现在大家普遍的说法是因为，查询条件出现类似这类情况时‘where xx=xx && xx=xx && xx>xx’使用联合索引会比单列索引高效，所以要使用多列索引，但是经过测试，事情并非如此，反而是单列索引处理时间比多列索引还快....详见测试代码及结果(大家一起来测试下，看看是不是单一偶然情况......)。
最后的答案我截取了Filix Suo第二个观点里一段话为答案，创建多列索引的意义就是为了‘减少io操作’。可能使用这个会相应失去什么，这个就有待大量测试考证了。
23：37更新：
今天刚入手mariadb，结果在手册里翻到了一份多列索引结构的解释（嘿嘿，真开源果然不同），贴出来供大家参考：
创建多列索引(列1，列2，列3)后的结构类似这样(index结构，附属列，附属列)
附属列可能直接跟在叶节点上，或单独存放（这个没有看到说明）...
附带可以说明单列索引就是多列索引一种形式，只不过没有附属列罢了。。。
答案对人有帮助，有参考价值
答案没帮助，是错误的答案，答非所问
简单的说有两个主要原因：
"一个顶三个"。建了一个(a,b,c)的复合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，因为每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，这可是不小的开销！
覆盖索引。同样的有复合索引（a,b,c），如果有如下的sql: select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一
索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出0w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知
答案对人有帮助，有参考价值
答案没帮助，是错误的答案，答非所问
如下的有a,b,c 三个key的table
create table test(
需要执行很多的类似于 select * from test where a=10, b&50, c&20
这类的组合查询 那么，我们可能需要创建 包含[a，b，c] 的联合索引，而单独的[a][b] [c]上的索引是不够的。
（可以把一个索引想象成 sorted list）.创建了 （a，b，c）的索引相当于 按照a,b,c 排序（排序规则是
if(X.a&Y.a)
return '&';
else if(X.a&Y.a)
return '&';
else if(X.b&Y.b)
return '&';
else if (X.b&Y.b)
return '&';
else if (X.c&Y.c)
else if (X.c&Y.c)
return '=='
和分别 按a 排序 分别按b排序 分别按照c排序是不一样的。
其中 a b c 的顺序也很重要，有时可以是a c b，或者b c a等等。
如果创建 （a,b,c)的联合索引，查询效率如下：
优: select * from test where a=10 and b&50 差: select * from test where
优: select * from test order by a 差: select * from test order by b 差:
select * from test order by c
优: select * from test where a=10 order by a 优: select * from test
where a=10 order by b 差: select * from test where a=10 order by c
优: select * from test where a&10 order by a 差: select * from test
where a&10 order by b 差: select * from test where a&10 order by c
优: select * from test where a=10 and b=10 order by a 优: select * from
test where a=10 and b=10 order by b 优: select * from test where a=10
and b=10 order by c
优: select * from test where a=10 and b=10 order by a 优: select * from
test where a=10 and b&10 order by b 差: select * from test where a=10
and b&10 order by c
答案对人有帮助，有参考价值
答案没帮助，是错误的答案，答非所问
我认为是为了更快的区分出相同数据，有很多数据要区分多个属性是否相同，所以用到联合索引
答案对人有帮助，有参考价值
答案没帮助，是错误的答案，答非所问
在一次查询中，MySQL只能使用一个索引。
在真实项目中，SQL语句中的WHERE子句里通常会包含多个查询条件还会有排序、分组等。
若表中索引过多，会影响INSERT及UPDATE性能，简单说就是会影响数据写入性能。因为更新数据的同时，也要同时更新索引。
最实际的好处当然是查询速度快，性能好。
创建一张数据表
CREATE TABLE `student` (
`studentId` int(11) NOT NULL,
`studentName` varchar(255) DEFAULT NULL,
`gradeId` int(11) DEFAULT NULL,
`schoolId` int(11) DEFAULT NULL,
PRIMARY KEY (`studentId`),
KEY `s_g_s` (`schoolId`,`gradeId`,`studentId`)
假设每个班有50名学生，一个年级有10个班，一所学校有4个年级，共有3所学校，那么总共会有6000名学生
若要查询出第2所学校3年级学生的姓名，SQL如下
SELECT studentName FROM student WHERE schoolId=2 AND gradeId=3
如果使用名为s_g_s的联合索引，那通过索引，MySQL可筛选掉大部分不满足查询条件的学生信息，在这个例子中，可筛掉5500条记录，这样MySQL只需回表查询剩余的500条记录即可得到结果。
如果只使用schoolId上的单列索引，只能筛掉4000条记录，需回表扫描过滤剩余的2000条记录才能得到结果，从数量上看差了一个数量级。性能自然不佳。
若要查询出第2所学校3年级学生的姓名并按照studentId倒排，SQL如下
SELECT studentName FROM student WHERE schoolId=2 AND gradeId=3 ORDER BY studentId DESC
这条SQL若没有多列索引，在较大数据量下性能会很差。但有了s_g_s索引，排序可以在索引上直接完成，不用MySQL取回记录后，再在内存或者磁盘上进行一次排序。性能提升很大。
若要查询出第2所学校3年级学生的studentId，SQL如下
SELECT studentId FROM student WHERE schoolId=2 AND gradeId=3
对于这条查询，s_g_s索引包含所有需要查询的字段的值，MySQL根本不需要再去读取表中的记录，直接全部在索引上完成，这是性能最高的一种索引，通常称为“覆盖索引”。
这个例子有点“简陋”，但还是可以说明一些问题。
综上，在实际项目中，
- 联合索引的使用要远多于单列索引。
- 联合索引使用正确对性能提升很有帮助。
- 怎么加索引，把哪些列加索引，加几个索引都是需要根据项目中使用到的SQL及数据表中数据的分布、数据的区分度去衡量的。
- 使用MySQL explain工具来解析SQL查询执行计划。
- 推荐看下这本书：
答案对人有帮助，有参考价值
答案没帮助，是错误的答案，答非所问
创建多列索引的意义就是为了‘减少io操作
在mysql5.6版本之前大部分情况是不行的，即使你创建的多列索引，但是你的sql写的不对的话，用到的往往是第一列索引。
建议你去看看mysql的5.6新特性ICP。
再给你推荐一篇博文，能够较好的理解为什么会这样
同步到新浪微博
分享到微博？
Hi，欢迎来到 SegmentFault 技术社区！⊙▽⊙ 在这里，你可以提出编程相关的疑惑，关注感兴趣的问题，对认可的回答投赞同票；大家会帮你解决编程的问题，和你探讨技术更新，为你的回答投上赞同票。
明天提醒我
关闭理由：
删除理由：
忽略理由：
推广（招聘、广告、SEO 等）方面的内容
与已有问题重复（请编辑该提问指向已有相同问题）
答非所问，不符合答题要求
宜作评论而非答案
带有人身攻击、辱骂、仇恨等违反条款的内容
无法获得确切结果的问题
非开发直接相关的问题
非技术提问的讨论型问题
其他原因（请补充说明）
我要该，理由是：
扫扫下载 App
SegmentFault
一起探索更多未知5381人阅读
MySQL单列索引和组合索引究竟有何区别呢？下文形象地对比了MySQL单列索引和组合索引的区别，希望可以让您对这两种索引有更深的认识。
MySQL单列索引和组合索引的区别可能有很多人还不是十分的了解，下面就为您分析两者的主要区别，供您参考学习。
为了形象地对比两者，再建一个表：CREATE TABLE myIndex ( i_testID INT NOT NULL AUTO_INCREMENT, vc_Name VARCHAR(50) NOT NULL, vc_City VARCHAR(50) NOT NULL, i_Age INT NOT NULL, i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID) );在这 10000 条记录里面 7 上 8 下地分布了 5 条 vc_Name="erquan" 的记录，只不过 city,age,school 的组合各不相同。来看这条 T-SQL：SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='郑州' AND i_Age=25;首先考虑建MySQL单列索引：在 vc_Name 列上建立了索引。执行 T-SQL 时，MYSQL 很快将目标锁定在了 vc_Name=erquan 的 5 条记录上，取出来放到一中间结果集。在这个结果集里，先排除掉 vc_City 不等于"郑州"的记录，再排除 i_Age 不等于 25 的记录，最后筛选出唯一的符合条件的记录。虽然在 vc_Name 上建立了索引，查询时MYSQL不用扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样的，在 vc_City 和 i_Age 分别建立的MySQL单列索引的效率相似。为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。就是将 vc_Name,vc_City，i_Age 建到一个索引里：ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);建表时，vc_Name 长度为 50，这里为什么用 10 呢？因为一般情况下名字的长度不会超过 10，这样会加速索引查询速度，还会减少索引文件的大小，提高 INSERT 的更新速度。执行 T-SQL 时，MySQL 无须扫描任何记录就到找到唯一的记录！！肯定有人要问了，如果分别在 vc_Name,vc_City，i_Age 上建立单列索引，让该表有 3 个单列索引，查询时和上述的组合索引效率一样吗？大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。建立这样的组合索引，其实是相当于分别建立了vc_Name,vc_City,i_Agevc_Name,vc_Cityvc_Name这样的三个组合索引！为什么没有 vc_City，i_Age 等这样的组合索引呢？这是因为 mysql 组合索引&最左前缀&的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个 T-SQL 会用到：SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="郑州"SELECT * FROM myIndex WHREE vc_Name="erquan"而下面几个则不会用到：SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="郑州"SELECT * FROM myIndex WHREE vc_City="郑州"
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：182498次
积分：2484
积分：2484
排名：第11074名
原创：54篇
转载：53篇
评论：16条
(14)(37)(14)(20)(13)(9)
