mysql 索引的使用 之 创建 、 删除

创建数据表创建索引create index username_index on users(username);删除索引drop index username_index on usersselect count(*) from users where username like 'f%'; // 在没有创建索引 与 创建索引的情况下二者查询所耗时分别为 -- 0.037 -- 0.002相差百倍以上唉.... (数据库八万多条记录) 

// 插入记录

Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");String sql = "insert into USERS values (null,?,?)";PreparedStatement pstmt = conn.prepareStatement(sql);long begin = System.currentTimeMillis();System.out.println("开始...");for(int i=0; i<60348; i++){pstmt.setString(1, RandomUtils.generateString(10));pstmt.setString(2, RandomUtils.generateString(20));pstmt.addBatch();if((i+1)%1000 == 0){pstmt.executeBatch();}}System.out.println("共耗时.."+(System.currentTimeMillis()-begin)+"ms");pstmt.close();conn.close();

// sql 结构

使用如下指令：

show create table users

 

'users', 'CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL COMMENT '用户名', `password` varchar(45) NOT NULL COMMENT '密码', PRIMARY KEY (`id`), KEY `username_index` (`username`)) ENGINE=InnoDB AUTO_INCREMENT=140006 DEFAULT CHARSET=utf8 COMMENT='用户表''