alter table message_text add index(from_id); 
 alter table message_text add index(to_id);
 
CREATE INDEX 语句用于在表中创建索引。 
 在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。
 
您可以在表中创建索引，以便更加快速高效地查询数据。 
 用户无法看到索引，它们只能被用来加速搜索/查询。 
 注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。
 
SQL CREATE INDEX 语法 
 在表上创建一个简单的索引。允许使用重复的值： 
 CREATE INDEX index_name 
 ON table_name (column_name) 
 注释：”column_name” 规定需要索引的列。 
 CREATE INDEX 实例 
 本例会创建一个简单的索引，名为 “PersonIndex”，在 Person 表的 LastName 列： 
 CREATE INDEX PersonIndex 
 ON Person (LastName) 
 
SQL CREATE UNIQUE INDEX 语法 
 在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。 
 CREATE UNIQUE INDEX index_name 
 ON table_name (column_name)
 
如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC： 
 CREATE INDEX PersonIndex 
 ON Person (LastName DESC) 
 
假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开： 
 CREATE INDEX PersonIndex 
 ON Person (LastName, FirstName)
 
假如我们创建了一个 testIndex 表： 
 　　CREATE TABLE testIndex(i_testID INT NOT NULL,vc_NameVARCHAR(16) NOT NULL); 
 　　我们随机向里面插入了 1000 条记录，其中有一条i_testID vc_Name 555 erquan 
 　　在查找 vc_Name=”erquan” 的记录 SELECT * FROM testIndex WHERE vc_Name=’erquan’; 时，如果在vc_Name 上已经建立了索引，MySql 无须任何扫描，即准确可找到该记录！相反，MySql 会扫描所有记录，即要查询 1000。以索引将查询速度提高 100 倍。 
 　　一、索引分单列索引和组合索引 
 　　单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引：即一个索包含多个列。 
 　　二、介绍一下索引的类型 
 　　1、普通索引。 
 　　这是最基本的索引，它没有任何限制。它有以下几种创建方式： 
 　　（1）创建索引：CREATE INDEX indexNameON tableName(tableColumns(length));如果是CHAR，VARCHAR类型，length可以小于字段实际长度;如果是 BLOB 和 TEXT 类型，必须指定 length，下同。 
 　　（2）修改表结构：ALTER tableName ADDINDEX [indexName] ON (tableColumns(length)) 
 　　（3）创建表的时候直接指定：CREATE TABLEtableName ( […], INDEX [indexName] (tableColumns(length)) ; 
 　　2、唯一索引。 
 　　它与前面的”普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式： 
 　　（1）创建索引：CREATE UNIQUE INDEXindexName ON tableName(tableColumns(length)) 
 　　（2）修改表结构：ALTER tableName ADDUNIQUE [indexName] ON (tableColumns(length)) 
 　　（3）创建表的时候直接指定：CREATE TABLEtableName ( […], UNIQUE [indexName] (tableColumns(length)); 
 　　3、主键索引 
 　　它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：CREATE TABLEtestIndex(i_testID INT NOT NULL AUTO_INCREMENT,vc_Name VARCHAR(16) NOTNULL,PRIMARY KEY(i_testID));当然也可以用 ALTER 命令。记住：一个表只能有一个主键。 
 　　4、全文索引 
 　　MySQL 从 3.23.23 版开始支持全文索引和全文检索。 
 　　删除索引的语法：DROP INDEX index_name ON tableName 
 　　三、单列索引和组合索引 
 　　为了形象地对比两者，再建一个表： 
 　　CREATE TABLE myIndex ( i_testID INT NOT NULLAUTO_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 FROMmyIndex WHERE vc_Name=’erquan’ AND vc_City=’郑州’ ANDi_Age=25; 
 　　首先考虑建单列索引： 
 　　在 vc_Name 列上建立了索引。执行 T-SQL 时，MYSQL 很快将目标锁定在了 vc_Name=erquan 的 5 条记录上，取出来放到一中间结果集。在这个结果集里，先排除掉 vc_City 不等于”郑州”的记录，再排除 i_Age 不等于 25 的记录，最后筛选出唯一的符合条件的记录。 
 　　虽然在 vc_Name 上建立了索引，查询时MYSQL不用扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样的，在 vc_City 和 i_Age 分别建立的单列索引的效率相似。 
 　　为了进一步榨取 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 WHREEvc_Name=”erquan” 
 　　而下面几个则不会用到： 
 　　SELECT * FROM myIndex WHREE i_Age=20 AND vc_City=”郑州” 
 　　SELECT * FROM myIndex WHREE vc_City=”郑州” 
 　　四、使用索引 
 　　到此你应该会建立、使用索引了吧？但什么情况下需要建立索引呢？一般来说，在 WHERE和 JOIN 中出现的列需要建立索引，但也不完全如此，因为 MySQL 只对 <，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE(后面有说明)才会使用索引。 
 　　SELECT t.vc_Name FROM testIndex t LEFT JOIN myIndex mON t.vc_Name=m.vc_Name WHERE m.i_Age=20 AND m.vc_City=’郑州’ 时，有对 myIndex 表的vc_City 和 i_Age 建立索引的需要，由于testIndex表的 vc_Name 开出现在了 JOIN 子句中，也有对它建立索引的必要。 
 　　刚才提到了，只有某些时候的 LIKE 才需建立索引？是的。因为在以通配符 % 和 _ 开头作查询时，MySQL 不会使用索引，如 SELECT * FROM myIndex WHERE vc_Name like’erquan%’ 
 会使用索引，而 SELECT * FROM myIndex WHEREt vc_Namelike’%erquan’ 就不会使用索引了。 
 　　五、索引的不足之处 
 　　上面说了那么多索引的好话，它真的有像传说中那么优秀么？当然会有缺点了。 
 　　1、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。 
 　　2、建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
 
　　讲了这么多，无非是想利用索引提高数据库的执行效率。不过索引只是提高效率的一个因素。如果你的MySQL有大数据的表，就需要花时间研究建立最优秀的索引或优化查询语句。

创建索引的语法是:
 
 
 
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...)
 
index_col_name:
    col_name [(length)] [ASC | DESC]
 
 
对于CHAR和VARCHAR列，只用一列的一部分就可创建索引。创建索引时，使用col_name(length)语法，对前缀编制索引。前缀包括每列值的前length个字符。BLOB和TEXT列也可以编制索引，但是必须给出前缀长度。
 
此处展示的语句用于创建一个索引，索引使用列名称的前10个字符。
 
 
 
CREATE INDEX part_of_name ON customer (name(10));
 
 
因为多数名称的前10个字符通常不同，所以此索引不会比使用列的全名创建的索引速度慢很多。另外，使用列的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。
 
前缀最长为255字节。对于MyISAM和InnoDB表，前缀最长为1000字节。注意前缀的限长以字节计，而CREATE INDEX语句中的前缀长度指的是字符的数目。对于使用多字节字符集的列，在指定列的前缀长度时，要考虑这一点。
 
在MySQL中：
 
·         只有当您正在使用MyISAM, InnoDB或BDB表类型时，您可以向有NULL值的列中添加索引。
 
·         只有当您正在使用MyISAM, BDB或InnoDB表类型时，您可以向BLOB或TEXT列中添加索引。
 
一个index_col_name规约可以以ASC或DESC为结尾。这些关键词将来可以扩展，用于指定递增或递减索引值存储。目前，这些关键词被分析，但是被忽略；索引值均以递增顺序存储。
 
部分储存引擎允许在创建索引时指定索引类型。index_type指定语句的语法是USING  type_name。不同的储存引擎所支持的type_name值已显示在下表中。如果列有多个索引类型，当没有指定index_type时，第一个类型是默认值。
 
 
存储引擎
 
 
允许的索引类型
 
 
MyISAM
 
 
BTREE
 
 
InnoDB
 
 
BTREE
 
 
MEMORY/HEAP
 
 
HASH, BTREE
 
 
 
 
 
 
 
 
示例：
 
 
 
CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);
 
 
TYPE type_name可以作为USING type_name的同义词，用于指定索引类型。但是，USING是首选的格式。另外，在索引规约语法中，位于索引类型前面的索引名称不能使用TYPE。这是因为，与USING不同，TYPE不是保留词，因此会被认为是一个索引名称。
 
如果您指定的索引类型在给定的储存引擎中不合法，但是有其它的索引类型适合引擎使用，并且不会影响查询功能，则引擎应使用此类型。
 
FULLTEXT索引只能对CHAR, VARCHAR和TEXT列编制索引，并且只能在MyISAM表中编制。
 
SPATIAL索引只能对空间列编制索引，并且只能在MyISAM表中编制。
 
 
使用alter的方法创建索引
 
 
 
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) ;
 
 
 
查询索引
 
 
 
SHOW INDEX FROM table_name;
 
 
 
删除索引
 
 
 
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
 
 
其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。
 
参考  http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#create-index

mysql 创建索引和删除索引
 
 
 
索引的创建可以在CREATE TABLE语句中进行，也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。
 
（1）使用ALTER TABLE语句创建索引。
 语法如下：
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) ;
 其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式，table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。
 创建索引的示例如下：
mysql> alter table table_test add index index_test1(name) ;
Query OK, 2 rows affected (0.08 sec)
 （2）使用CREATE INDEX语句对表增加索引。
 能够增加普通索引和UNIQUE索引两种。其格式如下：
create index index_name on table_name (column_list) ;
 create unique index index_name on table_name (column_list) ;
 
创建索引的示例如下：
 
mysql>create index index_test2 on table_test(age);
 
Query OK, 2 rows affected (0.08 sec)
 
说明：table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。
 （3）删除索引。
 删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
 其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
 如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
 删除索引的操作，如下面的代码：
mysql> drop index name on table_test ;
Query OK, 2 rows affected (0.08 sec)

MySQL 创建索引和索引效率验证
 
对于一张存储数据的表来说，通常来说数据量会很大。
 
项目中总会有一些表数据量非常大，并且在使用过程中，需要频繁地到这些表中查询数据。
 
数据量大的时候，查询速度会明显变慢，这时候就需要对查询速度进行优化了。
 
优化的方式很多，一个比较简单且低成本的方式就是创建索引。
 
一、索引简介
 
索引的目的是为了提高数据表的查询效率。
 
索引的作用类似于字典前面的拼音，笔画。拼音的顺序是固定的，在不知道一个字怎么写时，可以快速根据拼音来找到对应的字。看到一个字不知道怎么读时，通过前面的笔画，也可以快速找到对应的字。通过拼音或笔画找到一个字，与在一整本字典中找到一个字，这两种方式的速度差距是非常明显的。
 
索引可以通过层层的筛选，快速精确地定位到指定的数据，避免了查询数据时的全表扫描。这种定位速度是数量级的速度，数据量越大，效果越明显。
 
可见，索引的原理就是通过不断地缩小想要获得数据的范围来筛选出最终想要的结果。比如从1000中找到123，没有索引会从1开始逐一查找，直到找到123，有索引了先找到1开头的，再找第二个数是2的，然后找到123。索引的原理就类似这样，比全表扫描高效得多。
 
二、索引的使用
 
1. 连接数据库
 
先连接 mysql 数据库，进入到 MyDB_one 数据库中。
 
在 MyDB_one 中有一张表 Phone_table 。
 
 
2. 查看表中的索引
 
使用 show index from 表名; 查看表中的索引。
 
show index from Phone_table;
 
 
在上面的这张表中，现在还没有任何数据，所以也没有索引，查询结果为空 Empty 。
 
3. 批量插入数据
 
为了演示创建索引的方法和索引的效果，需要先在数据表中添加数据。而且，为了对比有无索引的效率差距，需要插入大量的数据，所以使用代码来批量插入数据到表中。
 
先使用 desc 表名; 查看表的字段。
 
desc Phone_table;
 
 
接下来使用 Python 的 pymysql 模块在数据库中插入数据。
 
使用 pymysql 进行数据库编程可以参考： https://blog.csdn.net/weixin_43790276/article/details/90345306
 
# coding=utf-8
from pymysql import connect


def insert_data():
    """
    在Phone_table中新增100000条数据
    """
    conn = connect(
        host="localhost",
        port=3306,
        user="admin",
        password="Mysql!123",
        database="MyDB_one",
        charset="utf8"
    )
    cursor = conn.cursor()
    for i in range(100000):
        sql = "insert into Phone_table values({id}, 'Phone{i}', 'black');".format(id=i, i=i)
        try:
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
    cursor.close()
    conn.close()


if __name__ == '__main__':
    insert_data()
 
运行上面的代码，会在数据表 Phone_table 中插入十万条数据，运行需要一些时间，等待一会。
 
select * from Phone_table;
 
 
当数据量达到十万条时，查询所有数据，也花了一些时间。
 
4. 给数据表创建索引
 
使用 create index 索引名 on 表名(字段名称(长度)); 来创建索引。
 
 如果指定的字段类型是字符串，需要指定长度，建议长度与数据表中定义字段时的长度一致。如果字段类型不是字符串，不用写长度。
 
create index phone_index on Phone_table(pid);
 
 
创建索引后，查看索引，可以看到刚创建的索引信息。
 
5. 删除索引
 
使用 drop index 索引名 on 表名; 来删除索引。
 
drop index phone_index on Phone_table;
 
 
删除索引后，数据表的索引为空。
 
三、验证索引的效率
 
有索引和没有索引的表查询效率差距是非常大的。在同一张表中，保持数据不变，查同一条数据，来对比有无索引的查询时间，就可以对比出效率的差异。
 
1. 开启 MySQL 运行时间监测
 使用 set profiling=1; 开启 MySQL 运行时间检测，通过运行时间来对比有无索引的效率。
 
开启运行时间检测后，数据库操作的运行时间会保存在 information_schema 数据库的 profiling 表中，如果退出数据库连接，profiling 表中的数据会被自动删除。
 
set profiling=1;
 
 
设置之后，进行数据库操作的运行时间会被记录下来。
 
2. 没有创建索引时查询
 
select * from Phone_table where pid=77777;
 
 
从查询结果下面的时间可以看出是0.04秒。
 
3. 创建索引后查询
 
上面已经将刚才创建的索引删掉了，现在重新创建索引，然后执行相同的查询语句。
 
 
从查询结果下面的时间可以看到是0.00秒，所以说这个时间的精度不够，需要使用更精确的监测时间来查看。
 
4. 查看执行的时间
 
可以使用 show profiles; 查看运行时间。
 
也可以到 information_schema 数据库的 profiling 表中去查询，这个时间的精度比查询结果下显示的时间精度高很多，可以方便对比。
 
show profiles;
 
 
通过 show profiles; 查看，创建索引之前的查询时间是 0.03757600 秒，创建索引之后的查询时间是 0.00038250 秒，时间相差了 将近 100 倍，这就是索引带来的效率提升。(运行时间每次不一样，不过数量级一般不会变)
 
在实际的项目中，数据远比十万条要多，那样索引带来的效率提升效果会更明显。
 
当然，不是所有的表都需要建立索引。如果表中数据很少，没有必要建立索引。如果一个表中的数据增删很频繁，不适合索引 ，因为只要数据发生增减，索引就要重新建立，建立索引是很耗时的，频繁建立索引反而效率更低了。索引只适合查询操作频繁的表。