MySQL索引效率对比_MySQL 关于索引以及使用效率对比，附测试数据代码

MySQL 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

普通索引

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16)NOT NULL,INDEX[indexName](username(length)));

删除索引的语法DROP INDEX[indexName]ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构ALTER table mytable ADD UNIQUE[indexName](username(length))

创建表的时候直接指定CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16)NOT NULL,UNIQUE[indexName](username(length)));

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

以下实例为在表中添加索引。mysql>ALTER TABLE testalter_tbl ADD INDEX(c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:mysql>ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空(NOT NULL)。实例如下：mysql>ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;mysql>ALTER TABLE testalter_tbl ADD PRIMARY KEY(i);

你也可以使用 ALTER 命令删除主键：mysql>ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

尝试以下实例:mysql>SHOW INDEX FROM table_name;\G........

对于20w条数据，无索引下查询  0.18s

20w条数据，有索引下查询  0.09s

总结：因此我们可以看出，在适当的场景下，建立索引的优势。查询速度显著加快。如果数据量更大，那么差距也会更为明显。当然，索引，也不能滥用，过多的建立索引，会使增删改的时候，索引也会随之更新，导致效率降低。适当合理的使用，还需要更多的探讨。

另外：放出一次性插入20w条数据到mysql的代码，大家可以试试。代码转载自 个人网站:www.fanwencong.com QQ交流群：494389786 略有修改

数据库my.ini文件修 max_allowed_packeta属性修改为100M，否则单个文件过大会报错。修改后重启mySql服务package com.lengxiaxi.test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Date;

import java.util.UUID;

import com.mysql.jdbc.PreparedStatement;

/**

*

*

项目名称：test_knowledge

* 类名称：Insert1000kToMySQL

* 类描述： 一次性插入20w条数据进数据库

* 创建人：lxx

* 创建时间：2018年4月25日 下午7:54:04

* 修改人：lxx

* 修改时间：2018年4月25日 下午7:54:04

* 修改备注：

* @version

*/

public class Insert1000kToMySQL {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

final String url = "jdbc:mysql://127.0.0.1/teacher";

final String name = "com.mysql.jdbc.Driver";

final String user = "root";

final String password = "root";

Connection conn = null;

Class.forName(name);//指定连接类型

conn = DriverManager.getConnection(url, user, password);//获取连接

if (conn!=null) {

System.out.println("获取连接成功");

insert(conn);

}else {

System.out.println("获取连接失败");

}

}

/**

*

insert(执行插入sql的方法)

* 创建人：lxx

* 创建时间：2018年4月25日 下午8:35:21

* 修改人：lxx

* 修改时间：2018年4月25日 下午8:35:21

* 修改备注：

* @param conn

*/

public static void insert(Connection conn) {

// 开始时间

Long begin = new Date().getTime();

// sql前缀

String prefix = "INSERT INTO t_teacher (id,t_name,t_password,sex,description,pic_url,school_name,regist_date,remark) VALUES ";

try {

// 保存sql后缀

StringBuffer suffix = new StringBuffer();

// 设置事务为非自动提交

conn.setAutoCommit(false);

// 比起st，pst会更好些

PreparedStatement pst = (PreparedStatement) conn.prepareStatement("");//准备执行语句

// 外层循环，总提交事务次数

for (int i = 1; i <= 2; i++) {

suffix = new StringBuffer();

// 第j次提交步长 每10w条拼接一次stringBuffer

for (int j = 1; j <= 100000; j++) {

// 构建SQL后缀

suffix.append("('" + UUID.randomUUID()+"','"+i*j+"','123456'"+ ",'男'"+",'教师'"+",'www.bbk.com'"+",'XX大学'"+",'"+"2016-08-12 14:43:26"+"','备注'" +"),");

}

// 构建完整SQL

String sql = prefix + suffix.substring(0, suffix.length() - 1);

// 添加执行SQL

pst.addBatch(sql);

// 执行操作

pst.executeBatch();

// 提交事务

conn.commit();

// 清空上一次添加的数据

suffix = new StringBuffer();

}

// 关闭连接

pst.close();

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

// 结束时间

Long end = new Date().getTime();

// 耗时 毫秒转秒

System.out.println("1000万条数据插入花费时间 : " + (end - begin) / 1000 + " s");

System.out.println("插入完成");

}

}