Mysql数据库 索引的原理和优化

本文主要讲述了如何加速动态网站的MySQL索引分析和优化。
参考原文 https://www.2cto.com/data /201707/660712.html
一、什么是索引?

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。

如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，
如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速
得到目标记录所在的位置。

假设我们创建了一个名为people的表：

CREATE TABLE people ( peopleid SMALLINT NOT NULL,
name CHAR(50) NOT NULL );

随机把1000个不同name值插入到people表。数据文件无次序。
如果创建了name列的索引，MySQL将在索引中排序name列,
对于索引中的每一项，MySQL在内部保存一个“指针”，指向数据文件。
如果要查找name等于“Mike”
记录的peopleid(SQL命令为“SELECT peopleid FROM people WHERE name=\'Mike\';”)
MySQL能够在name的索引中查找“Mike”值，然后直接转
到数据文件中相应的行，准确地返回该行的peopleid(999)。

二、索引的类型

MySQL提供多种索引类型供选择：

普通索引 :

它没有唯一性之类的限制。

创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);

修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);

创建表的时候指定索引，例如CREATE TABLE tablename ( [...],
 INDEX [索引的名字] (列的列表) );

唯一索引:

索引列的值唯一，可以为空。

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);

修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);

创建表的时候指定索引，例如CREATE TABLE tablename ( [...],
 UNIQUE [索引的名字] (列的列表) );

主键索引 :

一个表只能有一个主键索引，不允许为空。

主键创建表 CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); 

修改表 “ALTER TABLE tablename ADD PRIMARY KEY (列的列表); 

全文索引:

在MySQL中，全文索引的索引类型为FULLTEXT

全文索引可以在VARCHAR或者TEXT类型的列上创建。
它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或
CREATE INDEX命令创建。

对于大规模的数据集，通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。
具体参考 MySQL documentation。

三、单列索引与多列索引

索引可以是单列索引，也可以是多列索引。
假设有这样一个people表：

CREATE TABLE people ( peopleid
SMALLINT NOT NULL AUTO_INCREMENT,
firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) );

下面是我们插入到这个people表的数据：

这个数据片段中有四个名字为“Mikes”的人(其中两个姓Sullivans，两个姓McConnells)，有两个年龄为17岁的人，还有一个名字与众不同的Joe Smith。

这个表的主要用途是根据指定的用户姓、名以及年龄返回相应的peopleid。例如，我们可能需要查找姓名为Mike Sullivan、年龄17岁用户的peopleid(SQL命令为SELECT peopleid FROM people WHERE firstname=‘Mike’ AND lastname=‘Sullivan’ AND age=17;)。由于我们不想让MySQL每次执行查询就去扫描整个表，这里需要考虑运用索引。

首先可以考虑在单个列上创建索引，比如firstname、lastname或者age列。如果我们创建firstname列的索引(ALTER TABLE people ADD INDEX firstname (firstname)