MySQL单列索引和组合索引的区别介绍 - liluxiang - 博客园
首页
新闻
博问
专区
闪存
班级
我的博客
我的园子
账号设置
简洁模式 ...
退出登录
注册
登录
Leon
人生苦短，我选python
博客园
首页
新随笔
联系
订阅
管理
MySQL单列索引和组合索引的区别介绍
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="郑州"
转：http://blog.sina.com.cn/s/blog_4e45b8430102v6qw.html
到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为 MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：
SELECT t.Name
FROM mytable t LEFT JOIN mytable m
ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'
刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：
SELECT * FROM mytable WHERE username like'admin%'
SELECT * FROM mytable WHEREt Name like'�min'
（6）索引的不足之处
◆虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
使用索引时，有以下一些技巧和注意事项：
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “�a%” 不会使用索引而like “aaa%”可以使用索引。
select * from users where YEAR(adddate)<2007;
select * from users where adddate<‘2007-01-01’;
◆不使用NOT IN和<>操作
一个Web报表项目的性能分析和优化实践(四)：MySQL建立索引,唯一索引和组合索引
先大致介绍下项目的数据库信息。
数据库A：主要存放的通用的表，如User、Project、Report等。
数据库B、C、D：一个项目对应一个数据库，而且这几个项目的表是完全一样的。
数据库表的特点
A中的表：数据量几乎都比较小，比如User表中用户数，顶多也就几百上千。
B中的表：X/Y/Z 3张表几乎是确定的，Data表 中的数据量比较大，几千万到上亿。
周期性的会加入一大批数据，比如，每月末增加几百万条数据。
即一般情况下，B中的表只有查询操作，而且特别是Data查询频繁且数据量很大。
建立索引
1.为所有的表建立了唯一索引，索引字段是主键id。
2.考虑到数据库A中表的数据量很小，暂时没有建立组合索引。
如有可能，对频繁查询的表和字段，后期尝试加入组合索引。
3.对Data表建立组合索引。
频繁查询的一条SQL语句
select from Data where projectId=? and (inputVersion in (201)) and (sideId in (10001)) and (breakId in (?)) and (periodId in (?)) order by id desc;
建立组合索引的语句ALTER TABLE Data ADD INDEX data_query_index (projectId,inputVersion,sideId,breakId,periodId);
建立索引之前，需要花费2.796秒。
建立索引之后，只需要0.136秒。
可以说是，大幅度提升了查询效率。
索引的弊端
随之而来的问题：如果已经建立了索引，那么批量增加数据的时候，会特别慢。
一种较快的方法是:批量插入数据之前，先删除索引，提高批量插入数据的效率。然后，再重新建立索引，提高查询效率。
1000万条记录，重建5个字段的组合索引需要2到3秒。（我的两个字段的组合索引过了好几分钟还没结束！!!!!!!!!!!!!怀疑他的数据太简单了，自己构造的，)
重建索引的问题是，这个过程中，查询会比较慢。
应对之策：导入数据，重建索引 应该选择 晚上/凌晨等用户较少使用系统的时间段。
一个建议 用 explain sql; 可以分析sql语句的执行情况，进而对sql语句进行优化。
天下武功，唯勤不破
性能优化，以前只是看过一些书，没啥实践经验。
最近项目需要由我来进行优化，只好硬着头皮一点点去实践。
网上搜资料、请教同事、请教大牛。
周末再多看看书，认真复习和学习Linux、MySQL、Tomcat、Redis等一大堆，先侧重系统优化。
使用索引提高查询速度1.前言在web开发中，页面模板，业务逻辑(包括缓存、连接池)和数据库这三个部分，数据库在其中负责执行SQL查询并返回查 询结果，是影响网站速度最重要的性能瓶颈。本文主要针对MySql数据库，双十一的电商大战，引发了淘宝技术热议，而淘宝现在去IOE(I代表IBM的缩 写，即去IBM的存储设备和小型机;O是代表Oracle的缩写，也即去Oracle数据库，采用MySQL和Hadoop替 代的解决方案，;E是代表EMC2,即去EMC2的设备性,用PC Server替代EMC2)，大量采用MySql集群！让MySql再次成为耀眼的明星！而优化数据的重要一步就是索引的建立，对于mysql中出现的慢 查询，我们可以通过使用索引来提升查询速度。索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL将进行全表扫描，从第1条记录开始然后读 完整个表直到找出相关的行。
2.mysql索引类型及创建常用的索引类型有(1)主键索引它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：
代码如下:
CREATE TABLE user(id int unsigned not null auto_increment,name varchar(50) not null,email varchar(40) not null,primary key (id));
(2)普通索引这是最基本的索引，它没有任何限制。创建方式：
代码如下:
create index idx_name on user(name(20));
mysql支持前缀索引，一般姓名不会超过20个字符，所以我们这里建立索引的时候限定了长度20，这样可以节省索引文件大小(3)唯一索引它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。创建方式：
复制代码 代码如下:
CREATE UNIQUE INDEX idx_email ON user(email);
(4)全文索引MySQL支持全文索引和搜索功能。MySQL中的全文索引类型为FULLTEXT的索引。 FULLTEXT 索引仅可用于 MyISAM表；
复制代码 代码如下:
CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) );
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');
查询结果：+----+-------------------+------------------------------------------+| id | title | body |+----+-------------------+------------------------------------------+| 5 | MySQL vs. YourSQL | In the following database comparison ... || 1 | MySQL Tutorial | DBMS stands for DataBase ... |+----+-------------------+------------------------------------------+2 rows in set (0.00 sec)MATCH()函数对于一个字符串执行资料库内的自然语言搜索。一个资料库就是1套1个或2个包含在FULLTEXT内 的列。搜索字符串作为对AGAINST()的参数而被给定。对于表中的每一行, MATCH() 返回一个相关值，即, 搜索字符串和 MATCH()表中指定列中该行文字之间的一个相似性度量。(5)复合索引
代码如下:
CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name));
name索引是一个对last_name和first_name的索引。索引可以用于为last_name，或者为last_name和first_name在已知范围内指定值的查询。因此，name索引用于下面的查询：SELECT * FROM test WHERE last_name='Widenius';SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';但是不能用于SELECT * FROM test WHERE first_name='Michael';这是因为MySQL组合索引为“最左前缀”的结果,简单的理解就是只从最左面的开始组合。
3.在什么情况下使用索引(1)为搜索字段建索引,如果在你的表中，某个字段你经常用来做搜索，那么，请为其建立索引吧。一般来说，在WHERE和JOIN中出现的列需要建立索引以提高查询速度。例如从fps表（表中有name字段）中检索姓名为"李武"的人，下面用explain来解释执行建立索引和未建立索引的区别：
a.未建立索引前
代码如下:
explain select name from fps where name="李武";
[SQL] select name from fps where name="李武";影响的数据栏: 0时间: 0.003msb.建立索引后
复制代码 代码如下:
create index idx_name on fps(name);
explain select name from fps where name="李武";[SQL] select name from fps where name="李武";影响的数据栏: 0时间: 0.001ms
(2)下面我们就来看看这个EXPLAIN分析结果的含义。table：这是表的名字。type：连接操作的类型。下面是MySQL文档关于ref连接类型的说明：“对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。” 在本例中，由于索引不是UNIQUE类型，ref是我们能够得到的最好连接类型。 如果EXPLAIN显示连接类型是“ALL”，而且你并不想从表里面选择出大多数记录，那么MySQL的操作效率将非常低，因为它要扫描整个表。你可以加 入更多的索引来解决这个问题。预知更多信息，请参见MySQL的手册说明。possible_keys：可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称；如果索引没有昵称，则默认显示的是索引中第一个列的名字（在本例中，它是“idx_name”）。Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。key_len：索引中被使用部分的长度，以字节计。ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。在本例中，MySQL根据三个常量选择行。rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。 本例中未索引前遍历的记录数为1041，而建立索引后为1Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响。在本例中，MySQL只是提醒我们它将用using where，using index子句限制搜索结果集。
4.最常用的存储引擎：(1)Myisam存储引擎：每个Myisam在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别为.frm（存 储表定义）、.MYD（存储数据）、.MYI（存储索引）。数据文件和索引文件可以放置在不同目录，平均分布io，获得更快的速度。对存储大小没有限 制，MySQL数据库的最大有效表尺寸通常是由操作系统对文件大小的限制决定的,(2)InnoDB存储引擎：具有提交、回滚、奔溃恢复能力的事务安全。与Myisam相比，InnoDB的写效率差一些并且会占用更多的磁盘空间以保留数据和索引。(3)如何选择合适的引擎下面是常用存储引擎适用的环境：Myisam：它是在Web、数据仓储和其他应用环境下最常使用的存储引擎;InnoDB：用于事务处理应用程序，具有更多特性，包括ACID事务特性。
懂的越多，不会的也就越多，知识之路是不断进取的
posted @
2018-11-23 10:48
liluxiang
阅读(1526)
评论(0)
编辑
收藏
举报
刷新评论刷新页面返回顶部
Copyright 2022 liluxiang
Powered by .NET 7.0 on Kubernetes