文档章节

MYSQL学习笔记——sql语句优化之索引

rakshasa
 rakshasa
发布于 2014/12/27 16:33
字数 1844
阅读 10
收藏 0

      上一篇博客讲了可以使用慢查询日志定位耗时sql,使用explain命令查看mysql的执行计划,以及使用profiling工具查看语句执行真正耗时的地方,当定位了耗时之后怎样优化呢?这篇博客会介绍mysql中最简单快速的优化方法——添加索引。

一、索引的添加                                                                             

      mysql一共有四类索引,分别是主键索引、唯一索引、普通索引以及全文索引。

1.1、主键索引的添加  

      创建一张表时,把某个列设为主键的时候,则该列就是主键索引。添加主键索引有两种方法,我们可以在建表的时候指定主键,这样就可以在建表时添加索引;我们也可以在建表以后添加主键索引,下面的table1的主键索引是在建表时创建,table2是在建表后添加的主键索引:

create table table1(
	id int unsigned primary key auto_increment ,
	name varchar(32) not null
);

//添加主键索引语法:alter table 表名 add primary key (列名);
create table table2(
	id int unsigned,
	name varchar(32) not null
);

alter table table2 add primary key (id);

1.2、唯一索引的添加

      当表的某列被指定为unique约束时,这列就是一个唯一索引。同样,唯一索引的添加也可以分创建时添加和创建后添加,下例中table3的唯一索引是创建时添加,table4的唯一索引是创建后添加的:

create table table3(
	id int unsigned primary key auto_increment ,
	name varchar(32) unique
);

//添加唯一索引语法:create unique index 索引名  on 表名 (列表..);
create table table4(
	id int unsigned primary key auto_increment ,
	name varchar(32) 
);

create unique index my_uni on table4(name);

1.3、普通索引的添加

      普通索引应该是mysql中最常用的索引,因为一张表只能有一个主键索引,数量较少,唯一索引又必须保证内容唯一,限制较多,所以在mysql中最常用的还是普通索引。普通索引的创建也可以分建表时创建和建表后创建,下面例子中table5的普通索引是建表时创建,table6是的普通索引是建表后创建的:      

create table table5(
	id int unsigned,
	name varchar(32),
	KEY KEY_ID_NAME(id, name)
);

//添加普通索引语法:create index 索引名 on 表 (列1,列名2);
create table table6(
	id int unsigned,
	name varchar(32)
);

create index KEY_ID_NAME on table6 (id, name);

1.4、全文索引

      全文索引主要是针对文本或者文件的搜索,在这里就不再举例介绍了。

二、索引的查询                                                                       

      索引的查询很简单,可以使用以下两条命令显示表上的索引:  

show index from 表名
 或
show keys from 表名

      以table5为例,我们可以看看怎么查询索引:

mysql> show index from table5\G;
*************************** 1. row ***************************
       Table: table5
  Non_unique: 1
    Key_name: KEY_ID_NAME
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: table5
  Non_unique: 1
    Key_name: KEY_ID_NAME
Seq_in_index: 2
 Column_name: name
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
2 rows in set (0.00 sec)

  从查询结果可以看出,索引类型是B树,两列结果的Key_name一样,说明是复合索引等等信息。不过这样看很累,在平时查看索引时,我喜欢使用'show create table'命令查看表上的索引:

mysql> show create table table5\G;
*************************** 1. row ***************************
       Table: table5
Create Table: CREATE TABLE `table5` (
  `id` int(10) unsigned DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  KEY `KEY_ID_NAME` (`id`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

  这样看结果就清晰多了,'KEY KEY_ID_NAME(id, name)'直接把索引名称,索引作用列以及索引类型都显示出来了,清晰明了。

三、索引的删除                                                                             

      可以使用以下命令删除索引:alter table 表名 drop index 索引名; 例如我们想删除table5上的KEY_ID_NAME索引只需使用以下命令即可:

alter table table5 drop index KEY_ID_NAME;

  比较特殊的是删除主键索引,删除主键索引的语法如下:alter table tablename drop primary key; 例如我们要删除table2上的主键索引可以使用以下命令:

alter table table2 drop primary key;

四、索引的利弊                                                                            

      索引为我们带来的好处主要有以下两点:1、缩小检索范围,加快检索速度;2、在索引上排序及group by资源消耗极低。

      同时,大量使用索引也会给我们带来以下弊端:1、增删改操作将比原来更加耗时;2、索引的存储会占用存储空间。

五、创建索引的考虑点                                                                   

      当要为表创建索引时,我们可以从以下几个方面考虑是否应该创建索引:

        1、较频繁的作为查询条件的字段应该创建索引;

        2、唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件; 

        3、更新非常频繁的字段不适合创建索引,因为添加索引后字段更新将更加耗时;

六、使用索引的注意项                                                                  

      1、对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。例如在a,b,c列上创建索引,那么查询条件"a=5","a=5 and b=6","a=7 and b=8 and c=6"都会使用该索引;

      2、对于使用like的查询,查询如果是‘%aaa’不会使用到索引‘aaa%’会使用到索引;

      3、如果条件中有or,那么除非or条件都带有索引,否则还是会全表扫描;

      4、如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引;

      5、如果字符串较长时,直接使用'create index KEY_NAME on table6 (name)' 命令创建的索引也将较长,浪费磁盘空间;这时候我们可以通过'create index KEY_NAME on table6 (name(4))' 命令限定索引长度;

      6、连接查询时,在连接键上增加索引可以加快速度,同时遵循小表驱动大表原则;

      7、如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

七、直观感受索引的威力                                                              

      上一篇博客中我们创建了一张包含1000w数据的数据表,在该表上我们没有加任何索引,然后查询一条数据话费了6.5s时间。这次我们依然构造一张包含1000w数据的同样结构数据表,不同的是我们为它加上索引,然后查询数据,看一下索引优化的效果。

      首先,创建数据库表:

CREATE TABLE emp
(
  empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '编号',   ename VARCHAR(20) NOT NULL DEFAULT "" COMMENT '名字',   job VARCHAR(9) NOT NULL DEFAULT "" COMMENT '工作',   mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级编号',   hiredate DATE NOT NULL COMMENT '入职时间',   sal DECIMAL(7,2) NOT NULL COMMENT '薪水',   comm DECIMAL(7,2) NOT NULL COMMENT '红利',   deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '部门编号',   KEY KEY_NO(empno) )ENGINE=InnoDB DEFAULT CHARSET=utf8;  

  然后调用存储过程插入1000w条数据:

call insert_emp(10000000);

  最后,调用和上篇博客一模一样的查询语句,看这次查询需要多长时间:

mysql> select * from emp where empno=413345;
+--------+--------+----------+-----+------------+---------+--------+--------+
| empno  | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
| 413345 | JpugvK | SALESMAN |   1 | 2014-10-27 | 2000.00 | 400.00 |    157 |
+--------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (0.10 sec)

  查询时间从原来的6.5s提升到了0.1s,提高了65倍。

本文转载自:http://www.cnblogs.com/timlearn/p/4055512.html

rakshasa
粉丝 34
博文 142
码字总数 16820
作品 0
昆明
高级程序员
私信 提问
Golang 学习笔记(10)—— mysql操作

本文为转载,原文:Golang 学习笔记(10)—— mysql操作 Golang go-sql-driver/mysql go操作mysql的驱动包很多,这里讲解当下比较流行的go-sql-driver/mysql 安装 执行下面两个命令: 安装完...

ChainZhang
2018/01/04
0
0
Innodb索引和锁的学习笔记

附录:前段时间学习了下innodb锁的相关知识,对锁和事务有了大体理解,这里做个小总结。 1.Innodb事务和锁的关系。 Innodb区别于MyISAM的两个特点就是Innodb对于事务的支持和对行锁的支持。事...

天天顺利
2015/09/18
136
0
MySQL学习笔记一

MySQL目录结构 配置my.ini MySQL5.7的my.ini位于ProgramDataMySQLMySQL Server 5.7目录下(可能有的版本的my.ini就在安装目录下),该该目录下还有一个data目录存放我们的创建的数据库。 打开...

Aaron_DMC
2016/12/16
40
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
如何高效快速地优化MySQL、SQL语句(附源码)

作者介绍 韩锋,宜信技术研发中心数据库架构师。精通多种关系型数据库,曾任职于当当网、TOM在线等公司,曾任多家公司首席DBA、数据库架构师等职,多年一线数据库架构、设计、开发经验。著有...

Yomut
2016/10/10
208
1

没有更多内容

加载失败,请刷新页面

加载更多

OSChina 周一乱弹 —— 人生,还真是到处是意外

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @这次装个文艺青年吧 :#今日歌曲推荐# 分享lil peep的单曲《High School》 《High School》- lil peep 手机党少年们想听歌,请使劲儿戳(这里...

小小编辑
33分钟前
4
0
Spring使用ThreadPoolTaskExecutor自定义线程池及实现异步调用

多线程一直是工作或面试过程中的高频知识点,今天给大家分享一下使用 ThreadPoolTaskExecutor 来自定义线程池和实现异步调用多线程。 一、ThreadPoolTaskExecutor 本文采用 Executors 的工厂...

CREATE_17
今天
6
0
CSS盒子模型

CSS盒子模型 组成: content --> padding --> border --> margin 像现实生活中的快递: 物品 --> 填充物 --> 包装盒 --> 盒子与盒子之间的间距 content :width、height组成的 内容区域 padd......

studywin
今天
7
0
修复Win10下开始菜单、设置等系统软件无法打开的问题

因为各种各样的原因导致系统文件丢失、损坏、被修改,而造成win10的开始菜单、设置等系统软件无法打开的情况,可以尝试如下方法解决 此方法只在部分情况下有效,但值得一试 用Windows键+R打开...

locbytes
昨天
10
0
jquery 添加和删除节点

本文转载于:专业的前端网站➺jquery 添加和删除节点 // 增加一个三和一节点function addPanel() { // var newPanel = $('.my-panel').clone(true) var newPanel = $(".triple-panel-con......

前端老手
昨天
8
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部