MySql索引提高查询速度常用方法代码示例_Mysql

来源:脚本之家  责任编辑:小易  

使用索引提高查询62616964757a686964616fe58685e5aeb931333337383230速度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.003msb.建立索引后复制代码 代码如下: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,而建立索引后为1Extra: 这里可能出现许多不同的选项,其中大多数将对查询产生负面影响。在本例中,MySQL只是提醒我们它将用using where,using index子句限制搜索结果集。4.最常用的存储引擎:(1)Myisam存储引擎:每个Myisam在磁盘上存储成三个文件。文件名都和表名相同,扩展名分别为.frm(存储表定义)、.MYD(存储数据)、.MYI(存储索引)。数据文件和索引文件可以放置在不同目录,平均分布io,获得更快的速度。对存储大小没有限制,MySQL数据库的最大有效表尺寸通常是由操作系统对文件大小的限制决定的,(2)InnoDB存储引擎:具有提交、回滚、奔溃恢复能力的事务安全。与Myisam相比,InnoDB的写效率差一些并且会占用更多的磁盘空间以保留数据和索引。(3)如何选择合适的引擎下面是常用存储引擎适用的环境:Myisam:它是在Web、数据仓储和其他应用环境下最常使用的存储引擎;InnoDB:用于事务处理应用程序,具有更多特性,包括ACID事务特性www.zgxue.com防采集请勿采集本网。

使用索引提高查询速度

主键/聚集 索引: 该索引中键值的逻辑顺序决定了表中相应行的物理顺序。 如果用 新华字典 作例子来一个例子的话。 [拼音]就可以看作是聚集索引 例如 吖、阿、啊 在字典的最前面。 左、作、坐 在字典的最后面。 拼音[逻辑顺序]很接近,在字典中页

1.前言

索引就好像一本字典中的目录一样,假如你要找一个字的话,你是盲目的去翻阅整本字典找出来快呢,还是在目录中按照一定的规则找出来再翻到相应的页数快呢?显而易见的。但索引也不是越多越好,建了索引会降低写入和修改的效率,合理的建立索引也

  在web开发中,业务模版,业务逻辑(包括缓存、连接池)和数据库这三个部分,数据库在其中负责执行SQL查询并返回查询结果,是影响网站速度最重要的性能瓶颈。本文主要针对Mysql数据库,在淘宝的去IOE(I 代表IBM的缩写,即去IBM的存储设备和小型机;O是代表Oracle的缩写,去Oracle数据库,采用Mysql和Hadoop代替;E是代表EMC2,去EMC2的设备性,用PC server代替EMC2),大量使用Mysql集群!而优化数据的重要一步就是索引的建立,对于Mysql出现的慢查询,可以用索引提升查询速度。索引用于快速找出在某个列中有一特定值的行,不使用索引,Mysql将全表扫描,从第一条记录开始,然后读完整个表直到找出相关的行。

索引只是优化的一种方式 查询的效率受限于网络、服务主机的资源情况以及数据库的存储结构以及索引等综合情况来说的,并不是说建了索引就一定很快 建议你综合的看一下哪条SQL语句查询慢,解释一下,看看瓶颈在哪里,去学习一些数据库的优化知识以

2.Mysql索引类型及创建

索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下情况,假设数据库中一个表有10^6条记录,DBMS的页面大小为4K,并存储100条记录。如果没有索引,查询将对整个表进行扫描,最坏的情况下,如果所有数据页都不

索引相关知识:

你的理解其实没啥问题。索引就是通过事先排好序,从而在查找时可以应用二分查找等高效率的算法。 一般的顺序查找,复杂度为O(n),而二分查找复杂度为O(log2n)。当n很大时,二者的效率相差及其悬殊。 举个例子: 表中有一百万条数据,需要在其中

PRI主键约束;UNI唯一约束;MUL可以重复。查看索引mysql> show index from tblname;mysql> show keys from tblname;· Table表的名称。· Non_unique如果索引不能包括重复词,则为0。如果可以,则为1。· Key_name索引的名称。· Seq_in_index索引中的列序列号,从1开始。· Column_name列名称。· Collation列以什么方式存储在索引中。在MySQL中,有值‘A'(升序)或NULL(无分类)。· Cardinality索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机 会就越大。· Sub_part如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。· Packed指示关键字如何被压缩。如果没有被压缩,则为NULL。· Null如果列含有NULL,则含有YES。如果没有,则该列含有NO。· Index_type用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。· Comment

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_email on user(email(20));create index idx_name on user(name(20));

mysql 支持索引前缀,一般姓名不超过20字符,所以建立索引限定20长度,节省索引文件大小

3).唯一索引

它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,列值的组合必须唯一。

CREATE UNIQUE INDEX idx_email ON user(email);

4).组合索引

create table sb_man(id int PRIMARY key auto_increment,new_name char(30) not null,old_name char(30) not null,index name(new_name,old_name));# name索引是一个对new_name和old_name的索引。查询方法:select * from sb_man where new_name='yu';select * from sb_man where new_name='yu' and old_name='yu1';提示:>>>>>> 组合索引是最左前缀创建, 所以不能用如下sqlselect * from sb_man where old_name='yu1'; <<<< 错误

3.什么时候用索引

1.索引引用

在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。

例如,有2个未索引的表t1、t2、分别只包含列c1、c2 每个表分别含有1000行数据组成,值为111的数值,然后设置三张表,不同的几个值,

(这里我是用pymysql 执行 while 创建的数据)

查找对应值相等行的查询如下所示。

在无索引的情况下处理此查询,必须寻找3个表所有的组合,以便得出与WHERE子句相配的那些行。

select c1,c2 from t1,t2 where c1!=c2

结果查询过程>>

2.创建索引

在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

1.ALTER TABLE

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引

删除索引: alter table tab_name drop {index|key} index_name;

     alter table t1 drop index idx_c1;

添加索引: alter table t1 add index idx_c1(c1);

     alter table t2 add index idx_c2(c2);

查询结果虽然感觉没什么卵用。。。但是索引查询就是如此了。。。

关于32313133353236313431303231363533e4b893e5b19e31333335343461mysql处理百万级以上的数据时如何提高其查询速度的方法 最近一段时间由于工作需要,开始关注针对Mysql数据库的select查询语句的相关优化方法。 由于在参与的实际项目中发现当mysql表的数据量达到百万级时,普通SQL查询效率呈直线下降,而且如果where中的查询条件较多时,其查询速度简直无法容忍。曾经测试对一个包含400多万条记录(有索引)的表执行一条条件查询,其查询时间竟然高达40几秒,相信这么高的查询延时,任何用户都会抓狂。因此如何提高sql语句查询效率,显得十分重要。以下是网上流传比较广泛的30种SQL查询语句优化方法: 1、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。 2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 3、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 4、尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num=10 or num=20 可以这样查询: select id from t where num=10 union all select id from t where num=20 5、下面的查询也将导致全表扫描:(不能前置百分号) select id from t where name like ‘%c%’ 若要提高效率,可以考虑全文检索。 6、in 和 not in 也要慎用,否则会导致全表扫描,如: select id from t where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了: select id from t where num between 1 and 3 7、如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描: select id from t where num=@num 可以改为强制查询使用索引: select id from t with(index(索引名)) where num=@num 8、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如: select id from t where num/2=100 应改为: select id from t where num=100*2 9、应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如: select id from t where substring(name,1,3)=’abc’–name以abc开头的id select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id 应改为: select id from t where name like ‘abc%’ select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′ 10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。 11、在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使 用,并且应尽可能的让字段顺序与索引顺序相一致。 12、不要写一些没有意义的查询,如需要生成一个空表结构: select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样: create table #t(…) 13、很多时候用 exists 代替 in 是一个好的选择: select num from a where num in(select num from b) 用下面的语句替换: select num from a where exists(select 1 from b where num=a.num) 14、并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。 15、索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。 16.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。 17、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。 18、尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。 19、任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。 20、尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。 21、避免频繁创建和删除临时表,以减少系统表资源的消耗。 22、临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使 用导出表。 23、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。 24、如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。 25、尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。 26、使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。 27、与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。 28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。 29、尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。 30、尽量避免大事务操作,提高系统并发能力,先安装 Apache Spark,查询数据库的速度可以提升10倍。在已有的 MySQL 服务器之上使用 Apache Spark (无需将数据导出到 Spark 或者32313133353236313431303231363533e4b893e5b19e31333433643663 Hadoop 平台上),这样至少可以提升 10 倍的查询性能。使用多个 MySQL 服务器(复制或者 Percona XtraDB Cluster)可以让我们在某些查询上得到额外的性能提升。你也可以使用 Spark 的缓存功能来缓存整个 MySQL 查询结果表。思路很简单:Spark 可以通过 JDBC 读取 MySQL 上的数据,也可以执行 SQL 查询,因此我们可以直接连接到 MySQL 并执行查询。那么为什么速度会快呢?对一些需要运行很长时间的查询(如报表或者BI),由于 Spark 是一个大规模并行系统,因此查询会非常的快。MySQL 只能为每一个查询分配一个 CPU 核来处理,而 Spark 可以使用所有集群节点的所有核。在下面的例子中,我们会在 Spark 中执行 MySQL 查询,这个查询速度比直接在 MySQL 上执行速度要快 5 到 10 倍。另外,Spark 可以增加“集群”级别的并行机制,在使用 MySQL 复制或者 Percona XtraDB Cluster 的情况下,Spark 可以把查询变成一组更小的查询(有点像使用了分区表时可以在每个分区都执行一个查询),然后在多个 Percona XtraDB Cluster 节点的多个从服务器上并行的执行这些小查询。最后它会使用map/reduce 方式将每个节点返回的结果聚合在一起形成完整的结果内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • 保障mysql数据安全的一些建议
  • mysql编程中的6个实用技巧
  • mysql中文乱码以及导出为sql语句和excel问题解决方法[图文]
  • mysqli预处理编译的深入理解
  • windows 10 下安装mysql 5.7.17的简单笔记
  • mysql innodb 异常修复经验分享
  • mysql优化之缓存优化
  • debian中完全卸载mysql的方法
  • mysql临时表用法分析【查询结果可存在临时表中】
  • mysql 整表加密解决方案 keyring_file详解
  • 如何提高上百万级记录MySQL数据库查询速度
  • 如何使用索引提高查询速度
  • 我mysql数据查询的数据过多,查询很慢,大家说用索...
  • 如何使用索引提高查询速度
  • mysql一般索引和主键索引查询速度一样吗
  • mysql查询索引能提高速度怎么做到的
  • mysql在索引值大量重复的时候怎么加快查询速度
  • mysql索引为什么可以提升查询性能
  • mysql使用索引为什么查询速度变快很多
  • mysql 创建完索引之后 ,查询反而变慢了,这是为什...
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页一篇文章掌握mysql的索引查询优化技巧mysql全文索引、联合索引、like查询、json查询速度哪个快mysql查询条件中in会用到索引吗mysql使用命令创建、删除、查询索引的介绍解决mysql中in子查询会导致无法使用索引问题mysql使用索引实现查询优化美团网技术团队分享的mysql索引及慢查询优化教程mysql5.6.19下子查询为什么无法使用索引mysql前缀索引导致的慢查询分析总结保障mysql数据安全的一些建议mysql编程中的6个实用技巧mysql中文乱码以及导出为sql语句和excel问题解决方法[图文]mysqli预处理编译的深入理解windows 10 下安装mysql 5.7.17的简单笔记mysql innodb 异常修复经验分享mysql优化之缓存优化debian中完全卸载mysql的方法mysql临时表用法分析【查询结果可存在临时表中】mysql 整表加密解决方案 keyring_file详解mysql安装图解 mysql图文安装教程can""""t connect to mysql servwindows下mysql5.6版本安装及配置mysql字符串截取函数substring的mysql创建用户与授权方法mysql提示:the server quit withmysql日期数据类型、时间类型使用mysql——修改root密码的4种方法mysql之timestamp(时间戳)用法mysql update语句的用法详解使用jmeter连接mysql图文教程小记一次mysql主从配置解决方案mysql查询表是否被锁的方法sql与各个nosql数据库使用场景的讲解mssql output使用mysql常用日期时间/数值函数详解(必看)mac mysql数据库中文乱码问题解决mysql代码执行结构实例分析【顺序、分支、将mysql查询结果按值排序的简要教程java连接mysql的详细介绍
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved