MySQL索引用法实例分析

本文实例分析了MySQL索引用法。分享给大家供大家参考，具体如下：

MYSQL描述：

一个文章库，里面有两个表：category和article。category里面有10条分类数据。article里面有20万条。article里面有一个"article_category"字段是与category里的"category_id"字段相对应的。article表里面已经把 article_category字义为了索引。数据库大小为1.3G。

问题描述：

执行一个很普通的查询：

代码如下:

SELECT * FROM `article` WHERE article_category=11 ORDER BY article_id DESC LIMIT 5

执行时间大约要5秒左右

解决方案：

建一个索引：

代码如下:

create index idx_u on article (article_category,article_id);

代码如下:

SELECT * FROM `article` WHERE article_category=11 ORDER BY article_id DESC LIMIT 5

减少到0.0027秒

继续问题：


代码如下:

SELECT * FROM `article` WHERE article_category IN (2,3) ORDER BY article_id DESC LIMIT 5

执行时间要11.2850秒。

使用OR:

select * from article
where article_category=2
or article_category=3
order by article_id desc
limit 5

执行时间：11.0777

解决方案：避免使用in 或者 or (or会导致扫表)，使用union all

使用UNION ALL：

(select * from article where article_category=2 order by article_id desc limit 5)
UNION ALL (select * from article where article_category=3 order by article_id desc limit 5)
ORDER BY article_id desc
limit 5

执行时间：0.0261

注:UNION 和UNION ALL 的区别

在数据库中，UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select * from gc_dfys union select * from ls_jg_dfys

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

而UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL，如下：

select * from gc_dfys union all select * from ls_jg_dfys

注：mysql中union all的order by问题

今天写mysql数据库代码的时候，发现union的结果不是预期的

$stime = date("H:i:s");
$sql1 = "select * from T where '$stime'>stime order by stime desc";
$sql2 = "select * from T where stime>'$stime' order by stime asc";
$sql = "($sql) union all ($sql2)";

分别执行$sql1 和 $sql2 的时候结果是对的

但是执行$sql的时候，发现结果反了，$sql1的部分变升序，$sql2的部分变成降序

搜索也没有得到满意的答案，好像有些数据库还是不支持字句order by 的

无意中发现这样可以：

代码如下:

$sql = "select * from ($sql1) as temp1 union all select * from ($sql2) as temp2";

这是因为你的union的用法不正确的原因。在union操作中，order by语句不能出现在由union操作组合的两个select语句中。排序可以通过在第二个select语句后指定order by子句。

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL索引操作技巧汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》

希望本文所述对大家MySQL数据库计有所帮助。

赞 (0)

相关推荐

  • 详解mysql建立索引的使用办法及优缺点分析

    前言 索引(index)是帮助MySQL高效获取数据的数据结构. 它对于高性能非常关键,但人们通常会忘记或误解它. 索引在数据越大的时候越重要.规模小.负载轻的数据库即使没有索引,也能有好的性能, 但是当数据增加的时候,性能就会下降很快. 为什么要创建索引呢? 这是因为,创建索引可以大大提高系统的性能. 第一.通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性. 第二.可以大大加快数据的检索速度,这也是创建索引的最主要的原因. 第三.可以加速表和表之间的连接,特别是在实现数据的参考完整性方

  • mysql索引必须了解的几个重要问题

    本文讲述了mysql索引必须了解的几个重要问题.分享给大家供大家参考,具体如下: 1.索引是做什么的? 索引用于快速找出在某个列中有一特定值的行.不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行. 表越大,花费的时间越多.如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据. 大多数MySQL索引(PRIMARY KEY.UNIQUE.INDEX和FULLTEXT)在B树中存储.只是空间列类型的索引使用R-树,并且MEMORY

  • Mysql性能优化案例 - 覆盖索引分享

    Mysql性能优化案例 - 覆盖索引分享

    场景 产品中有一张图片表,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录 现在有一个根据user_id建立的索引:uid 查询语句也很简单:取得某用户的图片集合 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化前 执行查

  • Mysql数据库之索引优化

    Mysql数据库之索引优化

    MySQL凭借着出色的性能.低廉的成本.丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库.虽然性能出色,但所谓"好马配好鞍",如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如"精通MySQL"."SQL语句优化"."了解数据库原理"等要求.我们知道一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,

  • mysql 索引详细介绍

    mysql 索引详解: 在mysql 中,索引可以分为两种类型 hash索引和 btree索引. 什么情况下可以用到B树索引?  1.全值匹配索引 比如: orderID="123" 2.匹配最左前缀索引查询  比如:在userid 和 date字段上创建联合索引. 那么如果输入 userId作为条件,那么这个userid可以使用到索引,如果直接输入 date作为条件,那么将不能使用到索引. 3.匹配列前缀查询 比如: order_sn like '134%' 这样可以使用到索引. 4

  • Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    场景 产品中有一张图片表pics,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录,现在有一个根据user_id建立的索引:uid,查询语句也很简单:取得某用户的图片集合: 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化

  • Mysql使用索引实现查询优化

    索引的目的在于提高查询效率,可以类比字典,如果要查"mysql"这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql.如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的. 1.索引的优点 假设你拥有三个未索引的表t1.t2和t3,每个表都分别包含数据列i1.i2和i3,并且每个表都包含了1000条数据行,其序号从1到1000.查找某些值匹配的数据行组合的查询可能如下所示: SELECT t1.i1, t2.i2, t3.i3 FROM t1, t2,

  • Mysql判断表字段或索引是否存在

    判断字段是否存在: DROP PROCEDURE IF EXISTS schema_change; DELIMITER // CREATE PROCEDURE schema_change() BEGIN DECLARE CurrentDatabase VARCHAR(); SELECT DATABASE() INTO CurrentDatabase; IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schem

  • MySQL索引用法实例分析

    本文实例分析了MySQL索引用法.分享给大家供大家参考,具体如下: MYSQL描述: 一个文章库,里面有两个表:category和article.category里面有10条分类数据.article里面有20万条.article里面有一个"article_category"字段是与category里的"category_id"字段相对应的.article表里面已经把 article_category字义为了索引.数据库大小为1.3G. 问题描述: 执行一个很普通的查

  • MySQL索引优化实例分析

    MySQL索引优化实例分析

    目录 1.数据准备 2.实例一 3.MySQL如何选择合适的索引? 4.常见 SQL 深入优化 4.1.Order by与Group by优化 4.2.分页查询优化 4.3.join关联查询优化 4.3.1.数据准备 4.3.2.MySQL 表关联常见的两种算法 4.4.in和exsits优化 4.5.count(*)查询优化 5.索引设计原则 1.数据准备 #1.建立员工表,并创建name,age,position索引,id为自增主键 CREATE TABLE `employees` (  `

  • mysql索引覆盖实例分析

    本文实例讲述了mysql索引覆盖.分享给大家供大家参考,具体如下: 索引覆盖 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.这种查询速度非常快,称为"索引覆盖". 假设有一张t15表,在表中建立了一个联合索引:cp(cat_id,price) 当我们使用下面的sql语句,会出现索引覆盖的情况.不信我们可以来查看一下,这里的Extra中显示了Using index,表示这条sql语句刚好用到了索引覆盖. select price from t1

  • mysql存储过程用法实例分析

    本文实例讲述了mysql存储过程用法.分享给大家供大家参考,具体如下: 概述: 简单的说,就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法: 存储过程跟触发器有点类似,都是一组SQL集,但是存储过程是主动调用的,且功能比触发器更加强大,触发器是某件事触发后自动调用: 示例 DELIMITER // CREATE PROCEDURE proc (IN num INT) BEGIN SELECT * FROM v9_qd_account limit num;

  • mysql变量用法实例分析【系统变量、用户变量】

    本文实例讲述了mysql变量用法.分享给大家供大家参考,具体如下: 本文内容: 系统变量 用户变量 局部变量 首发日期:2018-04-18 系统变量: 系统变量就是系统已经提前定义好了的变量 系统变量一般都有其特殊意义.比如某些变量代表字符集.某些变量代表某些mysql文件位置 系统变量中包括会话级变量(当次会话连接生效的变量,如names),以及全局变量(一直生效的变量) [系统变量中全局变量和会话变量其实是使用一套变量,不同的是会话变量仅当次会话生效.] 会话变量的赋值:set 变量名 =

  • Python iter()函数用法实例分析

    本文实例讲述了Python iter()函数用法.分享给大家供大家参考,具体如下: python中的迭代器用起来非常灵巧,不仅可以迭代序列,也可以迭代表现出序列行为的对象,例如字典的键.一个文件的行,等等. 迭代器就是有一个next()方法的对象,而不是通过索引来计数.当使用一个循环机制需要下一个项时,调用迭代器的next()方法,迭代完后引发一个StopIteration异常. 但是迭代器只能向后移动.不能回到开始.再次迭代只能创建另一个新的迭代对象. 反序迭代工具:reversed()将返回

  • Python callable()函数用法实例分析

    本文实例讲述了Python callable()函数用法.分享给大家供大家参考,具体如下: python中的内建函数callable( ) ,可以检查一个对象是否是可调用的 . 对于函数, 方法, lambda 函数式, 类, 以及实现了 _ _call_ _ 方法的类实例, 它都返回 True. >>> help(callable) Help on built-in function callable in module __builtin__: callable(...) calla

  • Python lambda函数基本用法实例分析

    本文实例讲述了Python lambda函数基本用法.分享给大家供大家参考,具体如下: 这里我们简单学习一下python lambda函数. 首先,看一下python lambda函数的语法,如下: f=lambda [parameter1,parameter2,--]:expression lambda语句中,冒号前是参数,可以有0个或多个,用逗号隔开,冒号右边是返回值.lambda语句构建的其实是一个函数对象. 1>无参数 f=lambda :'python lambda!' >>&

  • Tensorflow tf.tile()的用法实例分析

    tf.tile()应用于需要张量扩展的场景,具体说来就是: 如果现有一个形状如[width, height]的张量,需要得到一个基于原张量的,形状如[batch_size,width,height]的张量,其中每一个batch的内容都和原张量一模一样.tf.tile使用方法如: tile( input, multiples, name=None ) import tensorflow as tf a = tf.constant([7,19]) a1 = tf.tile(a,multiples=[

  • java队列之queue用法实例分析

    Queue: 基本上,一个队列就是一个先入先出(FIFO)的数据结构 Queue接口与List.Set同一级别,都是继承了Collection接口.LinkedList实现了Deque接 口. Queue的实现 1.没有实现的阻塞接口的LinkedList: 实现了java.util.Queue接口和java.util.AbstractQueue接口 内置的不阻塞队列: PriorityQueue 和 ConcurrentLinkedQueue PriorityQueue 和 Concurren

随机推荐