索引
索引
推荐博客MySQL索引原理以及查询优化
创建表
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)do
insert into employer values(i,concat('jack',i),'male',concat('www.jack',i,'@qq.com'));
set i=i+1;
end while;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: utf8mb4_0900_ai_ci
1 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: 0
mysql> 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^C
mysql> create index indexid on emp(id);
Query OK, 0 rows affected (19.61 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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)
- 索引尽量短
- 查询条件复杂使用联合索引