您现在的位置：首页 > 实用文 > 其他范文

正确理解Mysql中的列索引和多列索引

时间：2023-07-26 09:21:10 其他范文收藏本文下载本文

下面是小编为大家整理的正确理解Mysql中的列索引和多列索引，本文共8篇，供大家参考借鉴，希望可以帮助您。

正确理解Mysql中的列索引和多列索引

篇1：正确理解Mysql中的列索引和多列索引

-06-06Mysql CPU占用高的问题解决方法小结

-06-06浅析mysql 语句的调度优先级及改变

2013-07-07mysql中engine=innodb和engine=myisam的区别介绍

-12-12mysql 忘记密码的解决方法(linux和windows小结)

2012-02-02什么是blob,mysql blob大小配置介绍

2012-11-11MySQL DNS的使用过程详细分析

-09-09MySQL 随机密码生成代码

-05-05mysql too many open connections问题解决方法

2014-06-06Mysql索引会失效的几种情况分析

-08-08mysql免安装制作使用说明

篇2：正确理解Mysql中的列索引和多列索引

热点排行

mysql安装图解 mysql图文安装教程

超详细mysql left join,right jo

Can''t connect to MySQL server

Mysql命令行导入sql数据

MYSQL 数据库导入导出命令

Mysql字符串截取函数SUBSTRING的

MySQL数据库备份与恢复方法

MySQL server has gone away 问题

windows下mysql忘记root密码的解

MySQL日期数据类型、时间类型使用

篇3：千万级别数据表，单列索引和多列索引性能对比

由于Contact表存在多个（单列）索引，造成Delete，update，insert操作时需要花费大量的时间删除索引和重建索引，

通过把多个（单列）索引合并成一个（多列）索引后，测试得出Delete，update，insert操作时需要花费的时间大大缩短。

由于多个（单列）索引合并成一个（多列）索引，可能会对之前单列索引字段的查询性能有影响，做了对比测试。结果如下

测试

Sql语句

查询结果行数

多个（单列）索引运行时间

单个（多列）索引运行时间

结论

删除一天数据

Declare@minCreatedDateDatetime；Set@minCreatedDate=Convert(DateTime,'-4-25 00:00:00',120)；

DELETEfromu_ch_ContactwhereCreatedDatebetween@minCreatedDateanddateadd(day,1,@minCreatedDate);

40822行

SQL Server Execution Times:

CPU time = 17031 ms, elapsed time = 633199 ms.

（00：10：34）

SQL Server Execution Times:

CPU time = 10405 ms, elapsed time = 39571 ms.

（00：00：39）

单个（多列）索引明显比多个（单列）索引在Del数据时花费的时间要短很多。

CustomerID（单列）索引和（多列）索引下查询性能对比

SELECT*FROMu_ch_contactWHERECustomerId='F9F268C1-A234-4716-9FC8-00022B2DE8E4'

42行

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1345 ms.

（00：00：01）

SQL Server Execution Times:

CPU time = 31049 ms, elapsed time = 21414 ms.

（00：00：21）

（单列）索引比（多列）索引在查询数据花费时间要短很多，主要表现在（单列）索引用的是索引查找，（多列）索引用的是索引扫描

CreatedBy（单列）索引和（多列）索引下查询性能对比

SELECT*FROMu_ch_contactWHERECreatedBy='B8056067-5DBA-41A0-B6CB-01CDFBAC517E'

19099行

SQL Server Execution Times:

CPU time = 422 ms, elapsed time = 37038 ms.

（00：00：37）

SQL Server Execution Times:

CPU time = 35842 ms, elapsed time = 19708 ms.

（00：00：19）

奇怪！（多列）索引比（单列）索引花费时间要短，但（单列）索引第二次运行Sql语句时间缩短为(CPU time = 218 ms, elapsed time = 733 ms.),而（多列）索引第二次运行Sql语句时间缩短接近一半(CPU time = 35734 ms, elapsed time = 10806 ms.)。同样表现为（单列）索引用的是索引查找，（多列）索引用的是索引扫描

ContactTime（单列）索引和（多列）索引下查询性能对比

SELECT*FROMu_ch_contactWHEREContactTimeBETWEEN'2010-03-01'AND'2010-03-30'

886469行

SQL Server Execution Times:

CPU time = 8047 ms, elapsed time = 120984 ms.

（00：02；01）

SQL Server Execution Times:

CPU time = 7579 ms, elapsed time = 143798 ms.

（00：02：24）

（单列）索引比（多列）索引在查询数据花费时间要短些，但差距不是大很多，

查看执行计划发现（单列）索引和（多列）索引用的都是索引扫描

ContactTelNo（单列）索引和（多列）索引下查询性能对比

SELECT*FROMu_ch_contactWHEREContactTelNo='15121007351'

36行

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 972 ms.

（00：00：01）

SQL Server Execution Times:

CPU time = 47282 ms, elapsed time = 17972 ms.

（00：00：41）

（单列）索引比（多列）索引在查询数据花费时间要短很多，主要表现在（单列）索引用的是索引查找，（多列）索引用的是索引扫描

CreateDate（单列）索引和（多列）索引下查询性能对比

SELECT*FROMdbo.u_ch_ContactWHERECreatedDateBETWEEN'2010-03-01'AND'2010-03-30'

886461行

SQL Server Execution Times:

CPU time = 7078 ms, elapsed time = 125751 ms.

（00：02：05）

SQL Server Execution Times:

CPU time = 7750 ms, elapsed time = 129782 ms.

（00：02：10）

（单列）索引比（多列）索引在查询数据花费时间要短些，但非常细微的差距。查看执行计划发现（单列）索引和（多列）索引用的都是索引扫描

EscalatedTo,Escalated（2列）索引和（多列）索引下查询性能对比

SELECT*FROMdbo.u_ch_ContactWHEREEscalatedTo='BDD4DE94-A75E-4F00-9FD8-06917B856CC1'ANDEscalated=0

229行

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 311 ms.

（00：00：00）

SQL Server Execution Times:

CPU time = 35204 ms, elapsed time = 11806 ms.

（00：00：11）

（单列）索引比（多列）索引在查询数据花费时间要短很多，主要表现在（单列）索引用的是索引查找，（多列）索引用的是索引扫描

EscalatedTo,status（2列）索引和（多列）索引下查询性能对比

SELECT*FROMdbo.u_ch_ContactWHEREEscalatedTo='BDD4DE94-A75E-4F00-9FD8-06917B856CC1'ANDstatus=3

6004行

SQL Server Execution Times:

CPU time = 328 ms, elapsed time = 7449 ms.

(00:00:07)

SQL Server Execution Times:

CPU time = 34811 ms, elapsed time = 13253 ms.

（00：00:13）

（单列）索引比（多列）索引在查询数据花费时间要短很多，主要表现在（单列）索引用的是索引查找，（多列）索引用的是索引扫描

通过以上对比发现：

查询结果行数不大的情况下:（单列）索引用索引查找明显比（多列）索引用的索引扫描有效率。

查询结果行数大的情况下：都是用索引扫描，相差的数据就不是特别明显。

摘自：踏雪无痕的blog

篇4：如何对调MySQL字段中的某些列

问题：表 t 有个字段叫做 c，现在想要把 c 里面的第 10 和第 11 列位置对调一下，咋办啊?

答案：用下面的办法吧，不过本例只对ascii字符有作用，中文或其他的就另外想办法，

mysql>set @pos1 = 10;

mysql>set @pos2 = 11;

mysql>UPDATE t SET c = CONCAT(

LEFT(c,@pos1-1), -- 第 10 列以前的值

SUBSTR(c,@pos2,1), -- 第 11 列的值

SUBSTR(c,@pos1+1,@pos2-@pos1-1), -- 第 10 到第 11 列之间的值

SUBSTR(c,@pos1,1), -- 第 10 列

RIGHT(c, LENGTH(c) - @pos2)); -- 第 11 列之后的值

其实很简单的吧 :)

篇5：如何更改表中的Mysql列顺序

首先，请考虑是否的确需要更改表中的列顺序，SQL的核心要点是从数据存储格式获取应用。总应指定检索数据的顺序。在下面的第1条语句中，以col_name1、col_name2、col_name3顺序返回列；在第2条语句中，以col_name1、col_name3、col_name2顺序返回列：

MySQL>SELECT col_name1, col_name2, col_name3 FROM tbl_name;

mysql>SELECT col_name1, col_name3, col_name2 FROM tbl_name;

如果决定更改表列的顺序，可执行下述操作：

用具有新顺序的列创建新表，

执行该语句：

mysql>INSERT INTO new_table

->SELECT columns-in-new-order FROM old_table;

撤销或重命名old_table。

将新表重命名为原始名称：

mysql>ALTER TABLE new_table RENAME old_table;

SELECT *十分适合于测试查询。但是，在应用程序中，永远不要依赖SELECT *的使用，不要依赖根据其位置检索列。如果添加、移动或删除了列，所返回的列的顺序和位置不会保持相同。对表结构的简单更改也会导致应用程序失败。

篇6：通过实例认识MySQL中前缀索引的用法

作者：罗龙九字体：[增加减小] 类型：

这篇文章主要通过实例来介绍MySQL中的前缀索引,包括前缀在实际使用中需要考虑到的长度问题等,需要的朋友可以参考下

今天在测试环境中加一个索引时候发现一警告

root@test 07:57:52>alter table article drop index ind_article_url;Query OK, 144384 rows affected (16.29 sec)Records: 144384 Duplicates: 0 Warnings: 0root@test 07:58:40>alter table article add index ind_article_url(url);Query OK, 144384 rows affected, 1 warning (19.52 sec)Records: 144384 Duplicates: 0 Warnings: 0root@test 07:59:23>show warnings;+―――+――+―――――――――――――――――――+| Level | Code | Message |+―――+――+―――――――――――――――――――+| Warning | 1071 | Specified key was too long; max key length is 767 bytes |+―――+――+―――――――――――――――――――+1 row in set (0.00 sec)

用show create table article查看索引以及表结构的信息：

`URL` varchar(512) default NULL COMMENT ‘外链url‘,……KEY `ind_article_url` (`URL`(383))…..DEFAULT CHARSET=gbk……drop table test;create table test(test varchar(767) primary key)charset=latin5;

C 成功

接下来未测试，在不同的字符集：

drop table test;create table test(test varchar(768) primary key)charset=latin5;

C 错误

ERROR 1071 (4): Specified key was too long; max key length is 767 bytesdrop table test;create table test(test varchar(383) primary key)charset=GBK;

C 成功

drop table test;create table test(test varchar(384) primary key)charset=GBK;

C 错误

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytesdrop table test;create table test(test varchar(255) primary key)charset=UTF8;

C 成功

drop table test;create table test(test varchar(256) primary key)charset=UTF8;

C 错误

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

MySQL的varchar索引只支持不超过768个字节或者 768/2=384个双字节或者 768/3=256个三字节的字段

而 GBK是双字节的，UTF-8是三字节的，

通过实例认识MySQL中前缀索引的用法

，

那么上面出现的原因就明了，我的字符集是为GBK为双字节，而url为512个字符，1024个字节，所以超过字符串索引的限制，报出了警告，mysql默认创建了383（766字节）长度的前缀索引。

我们知道小的索引大小不仅对空间存储，内存的降低和性能的提升有重大作用，那么在计算前缀索引的长度的时候，需要我们做出明智的选择，怎么明智？

全索引列的选择性：

root@test 08:10:35>select count(distinct(url))/count(*) from article;+――――――――――-+| count(distinct(url))/count(*) |+――――――――――-+|0.0750 |+――――――――――-+

对各种长度的前缀列计算其选择性：

root@test 08:16:41>select count(distinct left(url,76))/count(*) url_76,->count(distinct left(url,77))/count(*) url_77,->count(distinct left(url,78))/count(*) url_78,->count(distinct left(url,79))/count(*) url_79,->count(distinct left(url,80))/count(*) url_80,->count(distinct left(url,81))/count(*) url_81,->count(distinct left(url,82))/count(*) url_82,->count(distinct left(url,83))/count(*) url_83,->count(distinct left(url,84))/count(*) url_84,->count(distinct left(url,85))/count(*) url_85->from article;+――C+――C+――C+――C+――C+――C+――C+――C+――C+――C+| url_76 | url_77 | url_78 | url_79 | url_80 | url_81 | url_82 | url_83 | url_84 | url_85 |+――C+――C+――C+――C+――C+――C+――C+――C+――C+――C+| 0.0747 | 0.0748 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0750 |+――C+――C+――C+――C+――C+――C+――C+――C+――C+――C+1 row in set (1.82 sec)

我们看到选择85的长度的时候，该前缀列的选择性和全列的选择性相当了：

alter table article add index ind_article_url(url(85)),而不必选择383个字节作为前缀；

但是前缀索引还是有一点不足的地方，就是在查询语句中order by 和group by不能使用到前缀索引

篇7：关于复合索引中的2个索引列谁在前谁在后的进一步讨论实践篇

关于复合索引中的2个索引列谁在前谁在后的进一步讨论--实践篇：

上一次在长老的QQ群里边说了这么一个例子：

create table test_pk( id varchar2(10), create_dt date);alter table test_pk modify (id varchar2 (30 ));insert into test_pk select object_id, sysdate from dba_objects;

commit

需要执行的查询是：

select * from test_pk where create_dt>sysdate- 1/1440 and id like '13%'

索引有如下两个：当然任意时刻，只存在下面的任意一个，

create index idx_test_pk on test_pk(create_dt, id);create index idx_test_pk_id_create_dt on test_pk(id,create_dt);

也就是说：两个索引列的位置是反着的

首先要明确的一个问题：

where create_dt>sysdate- 1/1440 and id like ‘13%‘

不论 create_dt 和id 谁在前谁在后，这样的where条件是能走索引的，

其次需要明确的问题：

create_dt 和id 这两个索引列，哪个在前面时，如上查询的效率高？（当然，前提是：不考虑其他查询！！！）

经过实际验证，发现id 在前面时，如上查询的buffer get要比 create_dt 在前面时的 buffer get 低。

create index idx_test_pk_id_create_dt on test_pk(id,create_dt); ---bg 是 6

create index idx_test_pk on test_pk(create_dt,id); ---bg 是29.5

那么为啥id 在前面时的复合索引，查询语句的buffer get 要低呢？

原因不在于 id是什么数据类型的，

原因在于where条件中id的表现形式： id like ‘13%‘ ，相比而言，create_dt的表现形式是create_dt>sysdate- 1/1440

大家知道：

index是有序的，那么 id like ‘13%‘ 去索引的leaf block 检索时，检索过的leaf block的数量一般是要比 create_dt>sysdate- 1/1440 （也就是create_dt在前面）去索引的leaf block 检索过的leaf bock 的数量要少，正是这个原因，才导致id列在前面时的buffer get 要小。