如何使用索引提高查询速度
发布网友
发布时间:2022-04-07 14:27
我来回答
共1个回答
热心网友
时间:2022-04-07 15:56
使用索引提高查询速度
1.前言
在web开发中,页面模板,业务逻辑(包括缓存、连接池)和数据库这三个部分,数据库在其中负责执行SQL查询并返回查询结果,是影响网站速度最重要的性能瓶颈。本文主要针对MySql数据库,双十一的电商大战,引发了淘宝技术热议,而淘宝现在去IOE(I代表IBM的缩写,即去IBM的存储设备和小型机;O是代表Oracle的缩写,也即去Oracle数据库,采用MySQL和Hadoop替代的解决方案,;E是代表EMC2,即去EMC2的设备性,用PC
Server替代EMC2),大量采用MySql集群!让MySql再次成为耀眼的明星!而优化数据的重要一步就是索引的建立,对于mysql中出现的慢查询,我们可以通过使用索引来提升查询速度。索引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL将进行全表扫描,从第1条记录开始然后读完整个表直到找出相关的行。
2.mysql索引类型及创建
常用的索引类型有
(1)主键索引
它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:
复制代码
代码如下:
CREATE TABLE user(
id int unsigned not
null auto_increment,
name varchar(50) not null,
email varchar(40) not
null,
primary key (id)
);
(2)普通索引
这是最基本的索引,它没有任何*。创建方式:
复制代码
代码如下:
create index idx_name on
user(
name(20)
);
mysql支持前缀索引,一般姓名不会超过20个字符,所以我们这里建立索引的时候限定了长度20,这样可以节省索引文件大小
(3)唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。创建方式:
复制代码
代码如下:
CREATE UNIQUE INDEX idx_email ON
user(
email
);
(4)全文索引
MySQL支持全文索引和搜索功能。MySQL中的全文索引类型为FULLTEXT的索引。
FULLTEXT 索引仅可用于 MyISAM表;
复制代码
代码如下:
CREATE TABLE articles (
id INT
UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);
mysql> SELECT *
FROM articles WHERE MATCH (title,body) AGAINST ('database');
查询结果:
+----+-------------------+------------------------------------------+
|
id | title | body
|
+----+-------------------+------------------------------------------+
|
5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 |
MySQL Tutorial | DBMS stands for DataBase ...
|
+----+-------------------+------------------------------------------+
2 rows in set (0.00
sec)
MATCH()函数对于一个字符串执行资料库内的自然语言搜索。一个资料库就是1套1个或2个包含在FULLTEXT内的列。搜索字符串作为对AGAINST()的参数而被给定。对于表中的每一行,
MATCH() 返回一个相关值,即, 搜索字符串和
MATCH()表中指定列中该行文字之间的一个相似性度量。
(5)复合索引
复制代码
代码如下:
CREATE TABLE test (
id INT NOT
NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT
NULL,
PRIMARY KEY (id),
INDEX name
(last_name,first_name)
);
name索引是一个对last_name和first_name的索引。索引可以用于为last_name,或者为last_name和first_name在已知范围内指定值的查询。因此,name索引用于下面的查询:
SELECT
* FROM test WHERE last_name='Widenius';
SELECT * FROM test WHERE
last_name='Widenius' AND first_name='Michael';
但是不能用于SELECT * FROM test WHERE
first_name='Michael';这是因为MySQL组合索引为“最左前缀”的结果,简单的理解就是只从最左面的开始组合。
3.在什么情况下使用索引
(1)为搜索字段建索引,如果在你的表中,某个字段你经常用来做搜索,那么,请为其建立索引吧。一般来说,在WHERE和JOIN中出现的列需要建立索引以提高查询速度。
例如从fps表(表中有name字段)中检索姓名为"李武"的人,
下面用explain来解释执行建立索引和未建立索引的区别:
a.未建立索引前
复制代码
代码如下:
explain select name from fps where
name="李武";
[SQL] select name from fps where name="李武";
影响的数据栏:
0
时间: 0.003ms
b.建立索引后
复制代码
代码如下:
create index idx_name on
fps(
name
);
explain select name from fps where
name="李武";
[SQL] select name from fps where name="李武";
影响的数据栏:
0
时间: 0.001ms
(2)下面我们就来看看这个EXPLAIN分析结果的含义。
table:这是表的名字。
type:连接操作的类型。下面是MySQL文档关于ref连接类型的说明:
“对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是
UNIQUE或PRIMARY
KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接
类型是不错的。”
在本例中,由于索引不是UNIQUE类型,ref是我们能够得到的最好连接类型。
如果EXPLAIN显示连接类型是“ALL”,而且你并不想从表里面选择出大多数记录,那么MySQL的操作效率将非常低,因为它要扫描整个表。你可以加入更多的索引来解决这个问题。预知更多信息,请参见MySQL的手册说明。
possible_keys:
可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称;如果索引没有昵称,则默认显示的是索引中第一个列的名字
(在本例中,它是“idx_name”)。
Key:
它显示了MySQL实际使用的索引的名字。如果它为空(或NULL),则MySQL不使用索引。
key_len:
索引中被使用部分的长度,以字节计。
ref:
它显示的是列的名字(或单词“const”),MySQL将根据这些列来选择行。在本例中,MySQL根据三个常量选择行。
rows:
MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然,这里最理想的数字就是1。
本例中未索引前遍历的记录数为1041,而建立索引后为1
Extra:
这里可能出现许多不同的选项,其中大多数将对查询产生负面影响。在本例中,MySQL只是提醒我们它将用using
where,using index子句*搜索结果集。
4.最常用的存储引擎:
(1)Myisam存储引擎:每个Myisam在磁盘上存储成三个文件。文件名都和表名相同,扩展名分别为.frm(存储表定义)、.MYD(存储数据)、.MYI(存储索引)。数据文件和索引文件可以放置在不同目录,平均分布io,获得更快的速度。对存储大小没有*,MySQL数据库的最大有效表尺寸通常是由操作系统对文件大小的*决定的,
(2)InnoDB存储引擎:具有提交、回滚、奔溃恢复能力的事务安全。与Myisam相比,InnoDB的写效率差一些并且会占用更多的磁盘空间以保留数据和索引。
(3)如何选择合适的引擎
下面是常用存储引擎适用的环境:
Myisam:它是在Web、数据仓储和其他应用环境下最常使用的存储引擎;
InnoDB:用于事务处理应用程序,具有更多特性,包括ACID事务特性。
如何使用索引提高查询速度
--- 在群集索引下,数据在物理上按顺序在数据页上,重复值也排列在一起,因而在范围查找时,可以先找到这个范围的起末点,且只在这个范围内扫描数据页,避免了大范围扫描,提高了查询速度。 --- 3.在place,date,amount上的组合索引 select count(*) from record where date >'19991201' and date < '19991214' a...
MySQL索引优化查询速度的必需工具mysql下的索引
使用索引也非常简单,只需要在查询语句中使用“WHERE”关键字和需要查找的列即可。例如,下面的查询语句使用“idx_name”索引来加速对“users”表中名字为“Tom”的行的查找:SELECT * FROM users WHERE name=’Tom’;如果没有使用索引,这个查询可能需要扫描整个“users”表来查找匹配条件的...
如何使用索引提高查询速度
mysql支持前缀索引,一般姓名不会超过20个字符,所以我们这里建立索引的时候限定了长度20,这样可以节省索引文件大小 (3)唯一索引 它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。创建方式:复制代码 代码如下:CREATE UNIQUE INDEX idx_ema...
怎么使用索引优化向导
1、主键就是聚集索引。2、只要建立索引就能显著提高查询速度。3、把所有需要提高查询速度的字段都加进聚集索引,以提高查询速度。(四)其他书上没有的索引使用经验总结 1、用聚合索引比用不是聚合索引的主键速度快,2、用聚合索引比用一般的主键作order by时速度快,特别是在小数据量情况下,3、使用聚...
MySQL中IN操作的高效优化mysql中in的优化
一、使用索引 索引是MySQL中最常用的查询优化方法之一、可以大幅度提高查询速度,因此我们可以考虑在需要进行“IN”操作的字段上创建索引。例如,如果我们需要在用户表(user)中查询某几个用户的信息,可以将对应的用户ID列(id)创建索引:CREATE INDEX idx_user_id ON user(id);这样,在执行查询语句时...
MySQL中limit等查询优化技巧mysql中limit等
1. 使用索引 索引是MySQL中重要的优化手段,通过在表中创建合适的索引,可以加快查询速度。在使用查询语句时,应使用where子句筛选出需要的数据集合,然后使用索引进行查询。例如:SELECT * FROM table_name WHERE column_name = value;在此模式下,如果column_name有索引,查询速度将会快很多。2. 分页...
处理上百万条的数据库如何提高处理查询速度
处理上百万条的数据库如何提高处理查询速度.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null可以在num上设置默认...
如何写索引,让查询速度快
首先来看看表是否有索引的命令 show index from 表名;看到主键索引,索引类型是BTREE(二叉树)正是因为这个二叉树算法,让查询速度快很多,二叉树的原理,就是取最中间的一个数,然后把大于这个数的往右边排,小于这个数的就向左排,每次减半,然后依次类推,每次减半,形成一个树状结构图 例如上面的...
数据库建立索引怎么利用索引查询?
在一些数据库服务器上,索引可能失效或者因为频繁操作而 使得读取效率降低,如果一个使用索引的查询不明不白地慢下来,可以试着用tbcheck工具检查索引的完整性,必要时进行修复。另外,当数据库表更新大量 数据后,删除并重建索引可以提高查询速度。(1)在下面两条select语句中:SELECT * FROM table1 WHERE field1<=10000...
为什么索引能提高查询速度?
使用索引之后 索引做了些什么可以让我们查询加快速度呢?其实就是将无序的数据变成有序(相对):要找到id为8的记录简要步骤:很明显的是:没有用索引我们是需要遍历双向链表来定位对应的页,现在通过 “目录” 就可以很快地定位到对应的页上了!(二分查找,时间复杂度近似为O(logn))其实底层结构就是...