索引
索引
推荐博客
创建表
CREATE TABLE emp(id INT ,NAME VARCHAR(20),gender VARCHAR(10),email VARCHAR(50));
插入数据
临时增加插入效率
mysql> set autocommit =off;mysql> show variables like 'autocom%';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | OFF |+---------------+-------+1 row in set (0.00 sec)
存储过程快速插入
delimiter $$create procedure auto_insert1()BEGIN declare i int default 1; while(i<3000000)do insert into emp values(i,concat('jack',i),'male',concat('www.jack',i,'@qq.com')); set i=i+1; end while;END$$delimiter ;
调用call auto_insert1()
手动 commit;插入操作
mysql> delimiter $$mysql> create procedure auto_insert1() -> BEGIN -> declare i int default 1; -> while(i<300000)do -> insert into employer values(i,concat('egon',i),'male',concat('egon',i,'@oldboy')); -> set i=i+1; -> end while; -> END$$Query OK, 0 rows affected (0.04 sec)mysql> delimiter ;mysql> show create procedure auto_insert1 \G*************************** 1. row *************************** Procedure: auto_insert1 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `auto_insert1`()BEGIN declare i int default 1; while(i<300000)doinsert into employer values(i,concat('jack',i),'male',concat('www.jack',i,'@qq.com')); set i=i+1; end while;ENDcharacter_set_client: latin1collation_connection: latin1_swedish_ci Database Collation: utf8mb4_0900_ai_ci1 row in set (0.00 sec)mysql> call auto_insert1();Query OK, 1 row affected (5 min 50.89 sec)mysql> drop PROCEDURE auto_insert1;mysql> commit;Query OK, 0 rows affected (10.24 sec)mysql> exit;
不增加索引查询耗时
耗时4s
mysql> select * from emp where id = 33333;+-------+-----------+--------+----------------------+| id | NAME | gender | email |+-------+-----------+--------+----------------------+| 33333 | jack33333 | male | www.jack33333@qq.com |+-------+-----------+--------+----------------------+1 row in set (4.55 sec)
存在300万数据的时候增加索引耗时
mysql> create index indexid on emp(id);Query OK, 0 rows affected (21.24 sec)Records: 0 Duplicates: 0 Warnings: 0
增加索引的查询耗时
mysql> select * from emp where id = 33333;+-------+-----------+--------+----------------------+| id | NAME | gender | email |+-------+-----------+--------+----------------------+| 33333 | jack33333 | male | www.jack33333@qq.com |+-------+-----------+--------+----------------------+1 row in set (0.00 sec)mysql> select * from emp where id = 100333;+--------+------------+--------+-----------------------+| id | NAME | gender | email |+--------+------------+--------+-----------------------+| 100333 | jack100333 | male | www.jack100333@qq.com |+--------+------------+--------+-----------------------+1 row in set (0.01 sec)
删除主键
mysql> select * from emp where id =10000;+-------+-----------+--------+----------------------+| id | NAME | gender | email |+-------+-----------+--------+----------------------+| 10000 | jack10000 | male | www.jack10000@qq.com |+-------+-----------+--------+----------------------+1 row in set (0.00 sec)mysql> drop index indexid on emp;Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from emp where id =10001;+-------+-----------+--------+----------------------+| id | NAME | gender | email |+-------+-----------+--------+----------------------+| 10001 | jack10001 | male | www.jack10001@qq.com |+-------+-----------+--------+----------------------+1 row in set (4.79 sec)mysql> help create index^Cmysql> create index indexid on emp(id);Query OK, 0 rows affected (19.61 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from emp where id =20001;+-------+-----------+--------+----------------------+| id | NAME | gender | email |+-------+-----------+--------+----------------------+| 20001 | jack20001 | male | www.jack20001@qq.com |+-------+-----------+--------+----------------------+1 row in set (0.00 sec)mysql>
创建索引的语法
1.创建索引 -在创建表时就创建(需要注意的几点) create table s1( id int ,#可以在这加primary key #id int index #不可以这样加索引,因为index只是索引,没有约束一说, #不能像主键,还有唯一约束一样,在定义字段的时候加索引 name char(20), age int, email varchar(30) #primary key(id) #也可以在这加 index(id) #可以这样加 ); -在创建表后在创建 create index name on s1(name); #添加普通索引 create unique age on s1(age);添加唯一索引 alter table s1 add primary key(id); #添加住建索引,也就是给id字段增加一个主键约束 create index name on s1(id,name); #添加普通联合索引2.删除索引 drop index id on s1; drop index name on s1; #删除普通索引 drop index age on s1; #删除唯一索引,就和普通索引一样,不用在index前加unique来删,直接就可以删了 alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的,那么我们也用alter来删)
组合索引
创建组合索引
mysql> create index indexidname on emp(id,name);Query OK, 0 rows affected (30.87 sec)
组合索引可以命中的查询语句
mysql> select * from emp where id=10000 and name ='jack10000';+-------+-----------+--------+----------------------+| id | NAME | gender | email |+-------+-----------+--------+----------------------+| 10000 | jack10000 | male | www.jack10000@qq.com |+-------+-----------+--------+----------------------+1 row in set (0.00 sec)从左到右原则不会命中下面的查询语句mysql> select * from emp where name ='jack10000';+-------+-----------+--------+----------------------+| id | NAME | gender | email |+-------+-----------+--------+----------------------+| 10000 | jack10000 | male | www.jack10000@qq.com |+-------+-----------+--------+----------------------+1 row in set (5.48 sec)mysql> select * from emp where id =10000;+-------+-----------+--------+----------------------+| id | NAME | gender | email |+-------+-----------+--------+----------------------+| 10000 | jack10000 | male | www.jack10000@qq.com |+-------+-----------+--------+----------------------+1 row in set (0.00 sec)
查询优化
索引是为了增加查询的效率,查询需要注意:
- 避免使用select 、count*这样的操作
mysql> select name from emp where name ='jack10000';+-----------+| name |+-----------+| jack10000 |+-----------+1 row in set (4.15 sec)mysql> select * from emp where name ='jack10000';+-------+-----------+--------+----------------------+| id | NAME | gender | email |+-------+-----------+--------+----------------------+| 10000 | jack10000 | male | www.jack10000@qq.com |+-------+-----------+--------+----------------------+1 row in set (5.48 sec)mysql> select count(1) from emp;+----------+| count(1) |+----------+| 2999999 |+----------+1 row in set (2.47 sec)mysql> select count(*) from emp;+----------+| count(*) |+----------+| 2999999 |+----------+1 row in set (2.72 sec)
- 索引尽量短
- 查询条件复杂使用联合索引