博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
mysql索引
阅读量:5105 次
发布时间:2019-06-13

本文共 6852 字,大约阅读时间需要 22 分钟。

索引

索引

推荐博客

创建表

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)

查询优化

索引是为了增加查询的效率,查询需要注意:

  1. 避免使用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)
  1. 索引尽量短
  2. 查询条件复杂使用联合索引

转载于:https://www.cnblogs.com/JuncaiF/p/11303877.html

你可能感兴趣的文章
debian下安装wps office
查看>>
装备属性转移脚本
查看>>
Python控制电脑
查看>>
在LINQ TO SQL 中使用MVC3中的DataAnnotations 【MetadataType】
查看>>
MSSQL2005约束(五)-default约束
查看>>
[Codeforces Round #159 (Div. 2)]A. Sockets
查看>>
【转载】Cacti安装的详细步骤
查看>>
weblogic设置数据库自动重连
查看>>
[数据结构][LINUX内核编程]学习笔记(二)
查看>>
Spring AOP 理论
查看>>
Java EE、Java SE和Java ME
查看>>
为什么要使用MQ消息中间件?它解决了什么问题?
查看>>
二手书网页版mis系统
查看>>
Oracle_高级功能(9) 性能优化
查看>>
OpenGL入门程序二:绘制简单的圆
查看>>
计算线段和圆的交点
查看>>
extjs中修改confirm的显示按钮
查看>>
linux2.4.0源码下载地址(配合毛德操情景分析)
查看>>
vnc Unable to licence server: "XML error 0:0 Error: First Tag not found"问题的解决
查看>>
java项目移植出现的错误
查看>>