Home >Database >Mysql Tutorial >数据库查询优化Mysql索引_MySQL

数据库查询优化Mysql索引_MySQL

PHP中文网Original: 2016-06-08 08:50:321129browse

工作一年了，也是第一次使用Mysql的索引。添加了索引之后的速度的提升，让我惊叹不已。隔壁的老员工看到我的大惊小怪，平淡地回了一句“那肯定啊”。

对于任何DBMS，索引都是进行优化的最主要的因素。对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。

小宝鸽试了一下，2.5万数据单表中，无索引：200ms-700ms，添加索引后10ms-15ms，使用redis缓存1ms-7ms，如果数据量更大的时候，索引效果将会更加明显。更甚者，多表查询。

索引原理

1、除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

2、另外，比如学生信息表，添加学生姓名索引，索引是在name上排序的。现在，当查找某个学生信息时，就不需要逐行搜索全表，可以利用索引进行有序查找（如二分查找法），并快速定位到匹配的值，以节省大量搜索时间。

3、是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。
例如，有3个未索引的表t1、t2、t3，分别只包含列c1、c2、c3，每个表分别含有1000行数据组成，指为1～1000的数值，查找对应值相等行的查询如下所示。

SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3

此查询结果应该为1000行，每行包含3个相等的值。在无索引的情况下处理此查询，必须寻找3个表所有的组合，以便得出与WHERE子句相配的那些行。而可能的组合数目为1000×1000×1000（十亿），显然查询将会非常慢。

如果对每个表进行索引，就能极大地加速查询进程。利用索引的查询处理如下。

（1）从表t1中选择第一行，查看此行所包含的数据。

（2）使用表t2上的索引，直接定位t2中与t1的值匹配的行。类似，利用表t3上的索引，直接定位t3中与来自t1的值匹配的行。

（3）扫描表t1的下一行并重复前面的过程，直到遍历t1中所有的行。

在此情形下，仍然对表t1执行了一个完全扫描，但能够在表t2和t3上进行索引查找直接取出这些表中的行，比未用索引时要快一百万倍。

利用索引，MySQL加速了WHERE子句满足条件行的搜索，而在多表连接查询时，在执行连接时加快了与其他表中的行匹配的速度。

`索引的类型`

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

1．普通索引

在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。建立索引以后，查询时可以通过索引进行查询。例如，在student表的stu_id字段上建立一个普通索引。查询记录时，就可以根据该索引进行查询。

2．唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。例如，在student表的stu_name字段中创建唯一性索引，那么stu_name字段的值就必需是唯一的。通过唯一性索引，可以更快速地确定某条记录。主键就是一种特殊唯一性索引。

3．全文索引

使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，student表的information字段是TEXT类型，该字段包含了很多的文字信息。在information字段上建立全文索引后，可以提高查询information字段的速度。MySQL数据库从3.23.23版开始支持全文索引，但只有MyISAM存储引擎支持全文检索。在默认情况下，全文索引的搜索执行方式不区分大小写。但索引的列使用二进制排序后，可以执行区分大小写的全文索引。

4．单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

5．多列索引

多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。例如，在表中的id、name和sex字段上建立一个多列索引，那么，只有查询条件使用了id字段时该索引才会被使用。

6．空间索引

使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

`索引的操作`

1.添加PRIMARY KEY（主键索引）

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

<code class="hljs autohotkey">2.添加UNIQUE(唯一索引)

mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` )

<code class="hljs autohotkey"><code class="hljs autohotkey">3.添加INDEX(普通索引)

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey">4.添加FULLTEXT(全文索引)

mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey">5.添加多列索引

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><strong>创建索引</strong>

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey">在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey">1．ALTER TABLE

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey">ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs sql">其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs sql">2．CREATE INDEX

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs sql"><code class="hljs oxygene">table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs sql"><code class="hljs oxygene">在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。<br>PRIMARY KEY索引和UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。

ALTER TABLE students ADD PRIMARY KEY (sid)

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs sql"><code class="hljs oxygene"><code class="hljs sql">可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs sql"><code class="hljs oxygene"><code class="hljs sql"><code class="hljs sql">其中，前两条语句是等价的，删除掉table_name中的索引index_name。

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs sql"><code class="hljs oxygene"><code class="hljs sql"><code class="hljs sql">第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

<code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs autohotkey"><code class="hljs sql"><code class="hljs oxygene"><code class="hljs sql"><code class="hljs sql">如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

以上就是数据库查询优化Mysql索引_MySQL的内容，更多相关内容请关注PHP中文网（www.php.cn）！

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：mysql常用的语句_MySQLNext article：MySQL索引设计一些策略_MySQL

See more

数据库查询优化Mysql索引_MySQL

索引原理

索引的类型

索引的操作

Related articles

`索引的类型`

`索引的操作`