您的当前位置:首页正文

MySQL创建index示例_MySQL

2023-11-20 来源:健身之家
导读在数据库表中,使用索引可以大大提高查询速度。假如我们创建了一个 testIndex表:CREATE TABLE testIndex(i_testID INT NOT NULL,vc_Name VARCHAR(16) NOTNULL);我们随机向里面插入了 1000条记录,其中有一条 i_testID vc_Name 555 erquan在查找 vc_Name="erquan"的记录 SELECT *FROM testIndex WHERE vc_Name='erquan'; 时,如果在vc_

bitsCN.comMySQL创建index示例 环境:MySQL Sever 5.1 + MySQL命令行工具 问题:创建索引 CREATE INDEX 语法: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name[index_type] ON tbl_name (index_col_name,...)[index_type] index_col_name:col_name [(length)] [ASC| DESC] index_type:USING {BTREE | HASH |RTREE} 示例: [sql] -- 创建无索引的表 create table testIndex ( id int not null primary key, name varchar(10) ); -- 创建普通索引 create index IDX_test_testIndex _Name on testIndex(name); 说明:索引的命名最好采用此种方式:IDX_databaseName_tableName_columnName(IDX表示索引,databaseName表示数据库名,tableName表示表名,columnName表示字段名,如果Oracle下不存在数据库的概念,取而代之的是表空间,所以databaseName替换成用户名即可)。 参考资料:MySQL里创建索引(Create Index)的方法和语法结构及例子http:///database/201209/155472.htmlbitsCN.com

小编还为您整理了以下内容,可能对您也有帮助:

MySQL如何使用索引 较为详细的分析和例子_MySQL

在数据库表中,使用索引可以大大提高查询速度。假如我们创建了一个 testIndex表:

CREATE TABLE testIndex(i_testID INT NOT NULL,vc_Name VARCHAR(16) NOTNULL);

我们随机向里面插入了 1000条记录,其中有一条 i_testID vc_Name 555 erquan

在查找 vc_Name="erquan"的记录 SELECT *FROM testIndex WHERE vc_Name='erquan'; 时,如果在vc_Name 上已经建立了索引,MySql 无须任何扫描,即准确可找到该记录!相反,MySql 会扫描所有记录,即要查询 1000。以索引将查询速度提高 100 倍。

一、索引分单列索引和组合索引

单列索引:即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引:即一个索包含多个列。

二、介绍一下索引的类型

1、普通索引。

这是最基本的索引,它没有任何。它有以下几种创建方式:

(1)创建索引:CREATE INDEX indexName ONtableName(tableColumns(length));如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是 BLOB 和 TEXT 类型,必须指定 length,下同。

(2)修改表结构:ALTER tableName ADD INDEX[indexName] ON (tableColumns(length))

(3)创建表的时候直接指定:CREATE TABLE tableName ( [...],INDEX [indexName] (tableColumns(length)) ;

2、唯一索引。

它与前面的"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

(1)创建索引:CREATE UNIQUE INDEX indexName ONtableName(tableColumns(length))

(2)修改表结构:ALTER tableName ADD UNIQUE[indexName] ON (tableColumns(length))

(3)创建表的时候直接指定:CREATE TABLE tableName ( [...],UNIQUE [indexName] (tableColumns(length));

3、主键索引

它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:CREATE TABLE testIndex(i_testID INT NOT NULL AUTO_INCREMENT,vc_NameVARCHAR(16) NOT NULL,PRIMARY KEY(i_testID));当然也可以用ALTER 命令。记住:一个表只能有一个主键。

4、全文索引

MySQL从 3.23.23 版开始支持全文索引和全文检索。

删除索引的语法:DROP INDEX index_name ON tableName

三、单列索引和组合索引

为了形象地对比两者,再建一个表:

CREATE TABLE myIndex ( i_testID INT NOT NULL AUTO_INCREMENT, vc_NameVARCHAR(50) NOT NULL, vc_City VARCHAR(50) NOT NULL, i_Age INT NOT NULL,i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID) );

在这 10000条记录里面 7 上 8 下地分布了 5 条 vc_Name="erquan" 的记录,只不过 city,age,school 的组合各不相同。

来看这条 T-SQL:SELECT i_testID FROM myIndex WHEREvc_Name='erquan' AND vc_City='郑州' AND i_Age=25;

首先考虑建单列索引:

在 vc_Name列上建立了索引。执行 T-SQL 时,MYSQL 很快将目标锁定在了 vc_Name=erquan 的 5 条记录上,取出来放到一中间结果集。在这个结果集里,先排除掉 vc_City 不等于"郑州"的记录,再排除 i_Age 不等于 25 的记录,最后筛选出唯一的符合条件的记录。

虽然在 vc_Name上建立了索引,查询时MYSQL不用扫描整张表,效率有所提高,但离我们的要求还有一定的距离。同样的,在 vc_City 和 i_Age 分别建立的单列索引的效率相似。

为了进一步榨取 MySQL的效率,就要考虑建立组合索引。就是将 vc_Name,vc_City,i_Age 建到一个索引里:

ALTER TABLE myIndex ADD INDEX name_city_age(vc_Name(10),vc_City,i_Age);

建表时,vc_Name长度为 50,这里为什么用 10 呢?因为一般情况下名字的长度不会超过 10,这样会加速索引查询速度,还会减少索引文件的大小,提高 INSERT 的更新速度。

执行 T-SQL时,MySQL 无须扫描任何记录就到找到唯一的记录!!

肯定有人要问了,如果分别在 vc_Name,vc_City,i_Age 上建立单列索引,让该表有 3 个单列索引,查询时和上述的组合索引效率一样吗?大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引,其实是相当于分别建立了

vc_Name,vc_City,i_Age

vc_Name,vc_City

vc_Name

这样的三个组合索引!为什么没有 vc_City,i_Age 等这样的组合索引呢?这是因为 mysql 组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个 T-SQL 会用到:

SELECT * FROM myIndex WHREE vc_Name="erquan" ANDvc_City="郑州"

SELECT * FROM myIndex WHREEvc_Name="erquan"

而下面几个则不会用到:

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="郑州"

SELECT * FROM myIndex WHREE vc_City="郑州"

四、使用索引

到此你应该会建立、使用索引了吧?但什么情况下需要建立索引呢?一般来说,在 WHERE和 JOIN 中出现的列需要建立索引,但也不完全如此,因为 MySQL 只对 =,BETWEEN,IN,以及某些时候的LIKE(后面有说明)才会使用索引。

SELECT t.vc_Name FROM testIndex t LEFT JOIN myIndex m ONt.vc_Name=m.vc_Name WHERE m.i_Age=20 AND m.vc_City='郑州'时,有对 myIndex 表的 vc_City 和 i_Age 建立索引的需要,由于testIndex 表的 vc_Name 开出现在了 JOIN 子句中,也有对它建立索引的必要。

刚才提到了,只有某些时候的 LIKE才需建立索引?是的。因为在以通配符 % 和 _ 开头作查询时,MySQL 不会使用索引,如 SELECT * FROM myIndex WHERE vc_Name like'erquan%'

会使用索引,而 SELECT * FROM myIndex WHEREt vc_Namelike'%erquan' 就不会使用索引了。

五、索引的不足之处

上面说了那么多索引的好话,它真的有像传说中那么优秀么?当然会有缺点了。

1、虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行 INSERT、UPDATE 和DELETE。因为更新表时,MySQL 不仅要保存数据,还要保存一下索引文件。

2、建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

讲了这么多,无非是想利用索引提高数据库的执行效率。不过索引只是提高效率的一个因素。如果你的MySQL有大数据的表,就需要花时间研究建立最优秀的索引或优化查询语句。

mysql索引的创建和使用

我们可以通过查看索引的属性来判断创建索引的方法。
查看索引的语法格式如下:
SHOW INDEX FROM <表名> [ FROM <数据库名>]
语法说明如下:
<表名>:指定需要查看索引的数据表名。
<数据库名>:指定需要查看索引的数据表所在的数据库,可省略。比如,SHOW INDEX FROM student FROM test; 语句表示查看 test 数据库中 student 数据表的索引。
示例
使用 SHOW INDEX 语句查看《MySQL创建索引》一节中 tb_stu_info2 数据表的索引信息,SQL 语句和运行结果如下所示。
mysql> SHOW INDEX FROM tb_stu_info2\G
1. row
Table: tb_stu_info2
Non_unique: 0
Key_name: height
Seq_in_index: 1
Column_name: height
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.03 sec)
其中各主要参数说明如下:
参数 说明
Table 表示创建索引的数据表名,这里是 tb_stu_info2 数据表。
Non_unique 表示该索引是否是唯一索引。若不是唯一索引,则该列的值为 1;若是唯一索引,则该列的值为 0。
Key_name 表示索引的名称。
Seq_in_index 表示该列在索引中的位置,如果索引是单列的,则该列的值为 1;如果索引是组合索引,则该列的值为每列在索引定义中的顺序。
Column_name 表示定义索引的列字段。
Collation 表示列以何种顺序存储在索引中。在 MySQL 中,升序显示值“A”(升序),若显示为 NULL,则表示无分类。
Cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL 使用该索引的机会就越大。
Sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引,则该列的值为被编入索引的字符的数目;若整列被编入索引,则该列的值为 NULL。
Packed 指示关键字如何被压缩。若没有被压缩,值为 NULL。
Null 用于显示索引列中是否包含 NULL。若列含有 NULL,该列的值为 YES。若没有,则该列的值为 NO。
Index_type 显示索引使用的类型和方法(BTREE、FULLTEXT、HASH、RTREE)。
Comment 显示评注。

mysql索引的创建和使用

我们可以通过查看索引的属性来判断创建索引的方法。
查看索引的语法格式如下:
SHOW INDEX FROM <表名> [ FROM <数据库名>]
语法说明如下:
<表名>:指定需要查看索引的数据表名。
<数据库名>:指定需要查看索引的数据表所在的数据库,可省略。比如,SHOW INDEX FROM student FROM test; 语句表示查看 test 数据库中 student 数据表的索引。
示例
使用 SHOW INDEX 语句查看《MySQL创建索引》一节中 tb_stu_info2 数据表的索引信息,SQL 语句和运行结果如下所示。
mysql> SHOW INDEX FROM tb_stu_info2\G
1. row
Table: tb_stu_info2
Non_unique: 0
Key_name: height
Seq_in_index: 1
Column_name: height
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.03 sec)
其中各主要参数说明如下:
参数 说明
Table 表示创建索引的数据表名,这里是 tb_stu_info2 数据表。
Non_unique 表示该索引是否是唯一索引。若不是唯一索引,则该列的值为 1;若是唯一索引,则该列的值为 0。
Key_name 表示索引的名称。
Seq_in_index 表示该列在索引中的位置,如果索引是单列的,则该列的值为 1;如果索引是组合索引,则该列的值为每列在索引定义中的顺序。
Column_name 表示定义索引的列字段。
Collation 表示列以何种顺序存储在索引中。在 MySQL 中,升序显示值“A”(升序),若显示为 NULL,则表示无分类。
Cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL 使用该索引的机会就越大。
Sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引,则该列的值为被编入索引的字符的数目;若整列被编入索引,则该列的值为 NULL。
Packed 指示关键字如何被压缩。若没有被压缩,值为 NULL。
Null 用于显示索引列中是否包含 NULL。若列含有 NULL,该列的值为 YES。若没有,则该列的值为 NO。
Index_type 显示索引使用的类型和方法(BTREE、FULLTEXT、HASH、RTREE)。
Comment 显示评注。

怎样正确创建MySQL索引的方法详解

索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,所以大数据量建立索引是非常有必要的。MySQL提供了Explain,用于显示SQL执行的详细信息,可以进行索引的优化。

什么是索引?

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为:快速查找排好序的一种数据结构。Mysql索引主要有两种结构:B+Tree索引和Hash索引。我们平常所说的索引,如果没有特别指明,一般都是指B树结构组织的索引(B+Tree索引)。索引如图所示:

最外层浅蓝色磁盘块1里有数据17、35(深蓝色)和指针P1、P2、P3()。P1指针表示小于17的磁盘块,P2是在17-35之间,P3指向大于35的磁盘块。真实数据存在于子叶节点也就是最底下的一层3、5、9、10、13??非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如17、35。

查找过程:例如搜索28数据项,首先加载磁盘块1到内存中,发生一次I/O,用二分查找确定在P2指针。接着发现28在26和30之间,通过P2指针的地址加载磁盘块3到内存,发生第二次I/O。用同样的方式找到磁盘块8,发生第三次I/O。

真实的情况是,上面3层的B+Tree可以表示上百万的数据,上百万的数据只发生了三次I/O而不是上百万次I/O,时间提升是巨大的。

数据表索引可以提高数据的检索效率,也可以降低数据库的IO成本,并且索引还可以降低数据库的排序成本。排序分组操作主要消耗的就是CPU资源和内存,所以能够在排序分组操作中好好的利用索引将会极大地降低CPU资源的消耗。下面我们将简单的分析一下怎样正确创建MySQL数据索引。

怎样判断是否需要创建索引?

1、某些字段需要频繁用作查询条件时需要为它建立索引

这个应该都知道,什么样才是频繁呢?综合分析你执行的所有SQL语句。最好将他们一个个都列出来。然后分析,发现其中有些字段在大部分的SQL语句查询时候都会用到,那么就果断为他建立索引。

2、唯一性太差的字段不适合建立索引

什么是唯一性太差的字段?如状态字段、类型字段。那些只存储固定几个值的字段,例如用户登录状态、消息的status等。这个涉及到了索引扫描的特性。例如:通过索引查找键值为A和B的某些数据,通过A找到某条相符合的数据,这条数据在X页上面,然后继续扫描,又发现符合A的数据出现在了Y页上面,那么存储引擎就会丢弃X页面的数据,然后存储Y页面上的数据,一直到查找完所有对应A的数据,然后查找B字段,发现X页面上面又有对应B字段的数据,那么他就会再次扫描X页面,等于X页面就会被扫描2次甚至多次。以此类推,所以同一个数据页可能会被多次重复的读取,丢弃,在读取,这无疑给存储引擎极大地增加了IO的负担。

3、更新太频繁地字段不适合创建索引

当你为某个字段创建索引时候,如果再次更新这个字段数据时,数据库就会自动更新他的索引,所以当这个字段更新太频繁地时候那么就会不断的更新索引,性能的影响可想而知。大概被检索几十次才会更新一次的字段才比较符合建立索引的规范。而如果一个字段同一个时间段内被更新多次,那么果断不能为他建立索引。

4、不会出现在where条件中的字段不该建立索引

这个其实没什么好说的,不会用作查询条件的字段建立了索引也没用。

总结

mysql怎么创建索引

1.添加PRIMARY KEY(主键索引)
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (
`column`
)
3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
下面是更加详细的方法
MySQL中可以使用alter table这个SQL语句来为表中的字段添加索引。

使用alter table语句来为表中的字段添加索引的基本语法是:
ALTER TABLE <表名> ADD INDEX (<字段>);

我们来尝试为test中t_name字段添加一个索引。

mysql> alter table test add index(t_name);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

执行成功后,我们来看看结果。

mysql> describe test;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| t_id | int(11) | YES | | NULL | |
| t_name | varchar(50) | NO | MUL | NULL | |
| t_password | char(32) | YES | | NULL | |
| t_birth | date | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

结果可以看出,t_name字段的Key这一栏由原来的空白变成了MUL。这个MUL是什么意思呢?简单解释一下:如果Key是MUL,那么该列的值可以重复,该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL。

mysql怎么创建索引

1.添加PRIMARY KEY(主键索引)
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (
`column`
)
3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
下面是更加详细的方法
MySQL中可以使用alter table这个SQL语句来为表中的字段添加索引。

使用alter table语句来为表中的字段添加索引的基本语法是:
ALTER TABLE <表名> ADD INDEX (<字段>);

我们来尝试为test中t_name字段添加一个索引。

mysql> alter table test add index(t_name);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

执行成功后,我们来看看结果。

mysql> describe test;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| t_id | int(11) | YES | | NULL | |
| t_name | varchar(50) | NO | MUL | NULL | |
| t_password | char(32) | YES | | NULL | |
| t_birth | date | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

结果可以看出,t_name字段的Key这一栏由原来的空白变成了MUL。这个MUL是什么意思呢?简单解释一下:如果Key是MUL,那么该列的值可以重复,该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL。

如何正确建立MYSQL数据库索引

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。

在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin。

在查找username="admin"的记录 SELECT * FROM mytable WHERE username=‘admin‘;时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描所有记录,即要查询10000条记录。

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。

MySQL索引类型包括:

(1)普通索引

这是最基本的索引,它没有任何。它有以下几种创建方式:

◆创建索引

CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。

◆修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

◆创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

删除索引的语法:

DROP INDEX [indexName] ON mytable;

(2)唯一索引

它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

◆创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

◆修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

◆创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

(3)主键索引

它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); 当然也可以用 ALTER 命令。记住:一个表只能有一个主键。

(4)组合索引

为了形象地对比单列索引和组合索引,为表添加多个字段:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); 为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。

如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:

usernname,city,age usernname,city usernname 为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:

SELECT * FROM mytable WHREE username="admin" AND city="郑州"

SELECT * FROM mytable WHREE username="admin"

而下面几个则不会用到:

SELECT * FROM mytable WHREE age=20 AND city="郑州"

SELECT * FROM mytable WHREE city="郑州"

比如有一条语句是这样的:select * from users where area=’beijing’ and age=22;

如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。

如果我们创建了 (area, age,salary)的复合索引,那么其实相当于创建了 (area,age,salary)、(area,age)、(area)三个索引,这被称为“最佳左前缀”特性。因此我们在创建复合索引时应该将最常用作条件的列放在最左边,依次递减。

(5)建立索引的时机

到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city=‘郑州‘ 此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也要对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:

SELECT * FROM mytable WHERE username like‘admin%‘

而下句就不会使用:

SELECT * FROM mytable WHEREt Name like‘%admin‘ 因此,在使用LIKE时应注意以上的区别。

对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加

索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下

降。但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过渡索引。

(6)索引的不足之处

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

◆虽然索引大大提高了查询速度,但同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件就会膨胀的很快。

索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

(7)使用索引的注意事项

使用索引时,有以下一些技巧和注意事项:

◆索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

◆使用短索引

对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

◆索引列排序

Mysql查询每次只能使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

◆like语句操作

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引,而like “aaa%”可以使用索引。

◆不要在列上进行运算

select * from users where YEAR(adddate)<2007; 将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:

select * from users where adddate<‘2007-01-01’;

◆不使用NOT IN和<>操作

以上,就对其中MySQL索引类型进行了介绍。

######################################################################################################################

表的主键

自动建立唯一索引

如zl_yhjbqk(用户基本情况)中的hbs_bh(户标识编号)

表的字段唯一约束

ORACLE利用索引来保证数据的完整性

如lc_hj(流程环节)中的lc_bh+hj_sx(流程编号+环节顺序)

直接条件查询的字段

在SQL中用于条件约束的字段

如zl_yhjbqk(用户基本情况)中的qc_bh(区册编号)

select * from zl_yhjbqk where qc_bh=<2000 andqc_bh>=5000;

查询中与其它表关联的字段

字段常常建立了外键关系

如zl_ydcf(用电成份)中的jldb_bh(计量点表编号)

select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’

查询中排序的字段

排序的字段如果通过索引去访问那将大大提高排序速度

select * from zl_yhjbqk order by qc_bh(建立qc_bh索引)

select * from zl_yhjbqk where qc_bh=’7001’ order by cb_sx(建立qc_bh+cb_sx复合索引,注:只是一个索引,其中包括qc_bh和cb_sx字段)

查询中统计或分组统计的字段

select max(hbs_bh) from zl_yhjbqk

select qc_bh,count(*) from zl_yhjbqk group by qc_bh

什么情况下应不建或少建索引

表记录太少

如果一个表只有5条记录,采用索引去访问记录的话,那首先需访问索引表,再通过索引表访问数据表,一般索引表与数据表不在同一个数据块,这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出,处理速度显然会比用索引快。

如表zl_sybm(使用部门)一般只有几条记录,除了主键外对任何一个字段建索引都不会产生性能优化,实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引,而是自动执行全表访问。如:

select * from zl_sybm where sydw_bh=’5401’(对sydw_bh建立索引不会产生性能优化)

经常插入、删除、修改的表

对一些经常处理的业务表应在查询允许的情况下尽量减少索引,如 zl_yhbm,gc_dfss,gc_dfys,gc_fpdy等业务表。

数据重复且分布平均的表字段(如:性别字段)

假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为 50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。

经常和主字段一块查询但主字段索引值比较多的表字段

如gc_dfss(电费实收)表经常按收费序号、用户标识编号、抄表日期、电费发生年月、操作 标志来具体查询某一笔收款的情况,如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间,从实际上分析一笔收款如果按收费序号索引就已 经将记录减少到只有几条,如果再按后面的几个字段索引查询将对性能不产生太大的影响。

对千万级MySQL数据库建立索引的事项及提高性能的手段

一、注意事项:

首先,应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据,在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

其次,在对建立索引的时候要对表进行加锁,因此应当注意操作在业务空闲的时候进行。

二、性能调整方面:

首当其冲的考虑因素便是磁盘I/O。物理上,应当尽量把索引与数据分散到不同的磁盘上(不考虑阵列的情况)。逻辑上,数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。

其次,我们知道,在建立索引的时候要对表进行全表的扫描工作,因此,应当考虑调大初始化参数 db_file_multiblock_read_count的值。一般设置为32或更大。

再次,建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作,因此,应当调整排序区的大小。

9i之前,可以在session级别上加大sort_area_size的大小,比如设置为100m或者更大。

9i以后,如果初始化参数workarea_size_policy的值为TRUE,则排序区从pga_aggregate_target里自动分配获得。

最后,建立索引的时候,可以加上nologging选项。以减少在建立索引过程中产生的大量 redo,从而提高执行的速度。

######################################################################################################################

一般来说,以下的栏位都必须记得加上索引:

1、关联查询的外键字段要加索引(Foreign key)

2、会被排序的栏位(被放在order方法中)

3、会被查询的栏位(被放在where方法中)

4、会被group的栏位(被放在group方法中)

写少读多就多建索引, 写多读少就少建索引, 数据量少就不建索引。

设计好MySql的索引可以让你的数据库飞起来,大大的提高数据库效率。

如何正确建立MYSQL数据库索引

标签:

如何创建mysql索引以及索引的优缺点

mysql教程:索引的使用以及索引的优缺点
1. 索引(index)是帮助MySQL高效获取数据的数据结构。
它对于高性能非常关键,但人们通常会忘记或误解它。
索引在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能有好的性能, 但是当数据增加的时候,性能就会下降很快。
Tip:蠕虫复制,可以快速复制大量的数据
例:insert into emp select * from emp;
2. MySQL中常见的索引
◆普通索引 ◆唯一索引 ◆主键索引 ◆组合索引 ◆全文索引◆外键 (只有innodb存储引擎才支持)
2.1普通索引:
这是最基本的索引,它没有任何*。有以下几种创建方式:
有以下几种创建方式:
◆创建索引
CREATE INDEX indexName ON tablename(username(length));
◆修改表结构
ALTER tablename ADD INDEX indexName (username(length))
Tip:length可以小于字段实际长度;如果是BLOB 和 TEXT 类型,必须指定length ,下同
◆创建表的时候直接指定
CREATE TABLE mytableuuu( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX indexName (username(length)) );
CREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL);
create index index1 on mytable(id); //创建普通索引
◆删掉索引:
drop index index1 on mytable;
有一个概念,
行定义:在声明字段(列)的时候定义的,比如primary key
表定义:在所有字段(列)声明完之后定义的,比如primary key,index
CREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL,index index1(username));
3.0唯一索引(unique)
索引列的值必须唯一,但允许有空值。
1)创建索引:Create UNIQUE INDEX indexName ON tableName(tableColumns(length))
2)修改表结构:Alter tableName ADD UNIQUE [indexName] ON (tableColumns(length)
3)创建表的时候直接指定:Create TABLE tableName ( [...], UNIQUE [indexName](tableColumns(length));
4.0主键索引(primary key)

如何创建mysql索引以及索引的优缺点

mysql教程:索引的使用以及索引的优缺点
1. 索引(index)是帮助MySQL高效获取数据的数据结构。
它对于高性能非常关键,但人们通常会忘记或误解它。
索引在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能有好的性能, 但是当数据增加的时候,性能就会下降很快。
Tip:蠕虫复制,可以快速复制大量的数据
例:insert into emp select * from emp;
2. MySQL中常见的索引
◆普通索引 ◆唯一索引 ◆主键索引 ◆组合索引 ◆全文索引◆外键 (只有innodb存储引擎才支持)
2.1普通索引:
这是最基本的索引,它没有任何*。有以下几种创建方式:
有以下几种创建方式:
◆创建索引
CREATE INDEX indexName ON tablename(username(length));
◆修改表结构
ALTER tablename ADD INDEX indexName (username(length))
Tip:length可以小于字段实际长度;如果是BLOB 和 TEXT 类型,必须指定length ,下同
◆创建表的时候直接指定
CREATE TABLE mytableuuu( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX indexName (username(length)) );
CREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL);
create index index1 on mytable(id); //创建普通索引
◆删掉索引:
drop index index1 on mytable;
有一个概念,
行定义:在声明字段(列)的时候定义的,比如primary key
表定义:在所有字段(列)声明完之后定义的,比如primary key,index
CREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL,index index1(username));
3.0唯一索引(unique)
索引列的值必须唯一,但允许有空值。
1)创建索引:Create UNIQUE INDEX indexName ON tableName(tableColumns(length))
2)修改表结构:Alter tableName ADD UNIQUE [indexName] ON (tableColumns(length)
3)创建表的时候直接指定:Create TABLE tableName ( [...], UNIQUE [indexName](tableColumns(length));
4.0主键索引(primary key)

mysql如何创建多个索引mysql如何创建多个索引文件

关于MySQL复合索引的用法

可以创建多个MySQL的复合索引,每个复合索引可以包含一个或多个列。复合索引的基本原理是左对齐。例如,一个复合索引包含字段A、B和C,这实际上相当于创建五个索引,即:

那么问题来了,如果我们创建两个复合索引,复合索引1:包含A、B、C列,复合索引2:包含B、C列,MySQL会如何执行?

按照正常逻辑和复合索引原理,应该能命中的索引是A_B_C_index。让我们拭目以待!

结果:与上次检测不一致。虽然这个测试中有三个ABC列,但是命中索引是B_C_index。

结论:当命中两个或多个不同的复合索引时,MySQL会有不同的策略根据创建顺序选择其中一个复合索引。

mysql联合索引建立规则?

MySQL中联合索引规则:

1、需要加索引的字段,要在where条件中

2、数据量少的字段不需要加索引

3、如果where条件中是OR关系,加索引不起作用

4、符合最左原则。

create怎么加索引?

1.方式:在执行CREATE

2.方式:使用ALTERTABLE命令去增加索ALTERTABLEtable_nameADDINDEXindex_name(column_list);1ALTERTABLE用来创建普通索引、UNIQUE索引或PRIMARYKEY索引。其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可自己命名,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTERTABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。

3.方式:使用CREATEINDEX命令创建CREATEINDEXindex_nameONtab

mysql多表查询索引怎么使用?

1select*fromtable1,table2wheretable1.id=table2.id;2手册上有3是的4定期清理数据碎片

mysql联合索引最左匹配原因?

最左前缀匹配原则

在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,

示例:

对列Gid、列Cid和列Sid建一个联合索引

联合索引uni_Gid_Cid_SId实际建立了(Gid)、(Gid,Cid)、(Gid,Cid,SId)三个索引。

插入模拟数据

查询实例:

上面这个查询语句执行时会依照最左前缀匹配原则,检索时会使用索引(Gid,Cid)进行数据匹配。

注意

索引的字段可以是任意顺序的,如:

这两个查询语句都会用到索引(Gid,Cid),mysql创建联合索引的规则是首先会对联合合索引的最左边的,也就是第一个字段Gid的数据进行排序,在第一个字段的排序基础上,然后再对后面第二个字段Cid进行排序。其实就相当于实现了类似orderbyGidCid这样一种排序规则。

有人会疑惑第二个查询语句不符合最左前缀匹配:首先可以肯定是两个查询语句都保函索引(Gid,Cid)中的Gid、Cid两个字段,只是顺序不一样,查询条件一样,最后所查询的结果肯定是一样的。既然结果是一样的,到底以何种顺序的查询方式最好呢?此时我们可以借助mysql查询优化器explain,explain会纠正sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。

那么问题产生了?既然结果是一样的,到底以何种顺序的查询方式最好呢?

所以,而此时那就是我们的mysql查询优化器该登场了,sql语句中字段的顺序不需要和联合索引中定义的字段顺序一致,查询优化器会自己调整顺序,mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。所以,当然是我们能尽量的利用到索引时的查询顺序效率最高咯,所以mysql查询优化器会最终以这种顺序进行查询执行。

为什么要使用联合索引

减少开销。建一个联合索引(Gid,Cid,SId),实际相当于建了(Gid)、(Gid,Cid)、(Gid,Cid,SId)三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!

覆盖索引。对联合索引(Gid,Cid,SId),如果有如下的sql:selectGid,Cid,SIdfromstudentwhereGid=1andCid=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。

效率高。索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:selectfromtablewhereGid=1andCid=2andSId=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到符合Gid=2andCid=3的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出1000w10%10%*10%=1w,效率提升可想而知!

缺点。联合索引越多,索引列越多,则创建的索引越多,索引都是存储在磁盘里的,通过索引算法(Btree代表索引算法使用二叉树的形式来做索引的)来查找数据,的确可以极大的提高查询效率,但是与此同时增删改的同时,需要更新索引,同样是需要花时间的,并且索引所占的磁盘空间也不小。

建议。单表尽可能不要超过一个联合索引,单个联合索引不超过3个字段。

引申

对于联合索引(Gid,Cid,SId),查询语句SELECT*FROMstudentWHERECid=465176354;是否能够触发索引?大多数人都会说NO,实际上却是YES。

原因:

观察上述两个explain结果中的type字段。查询中分别是:

type:index

type:ref

index:这种类型表示mysql会对整个该索引进行扫描。要想用到这种类型的索引,对这个索引并无特别要求,只要是索引,或者某个联合索引的一部分,mysql都可能会采用index类型的方式扫描。但是呢,缺点是效率不高,mysql会从索引中的第一个数据一个个的查找到最后一个数据,直到找到符合判断条件的某个索引。所以,上述语句会触发索引。

ref:这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引,而不是会对索引中每一个数据都进行一一的扫描判断,也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找,索引却是有要求的,要实现这种能快速查找的算法,索引就要满足特定的数据结构。简单说,也就是索引字段的数据必须是有序的,才能实现这种类型的查找,才能利用到索引。

索引类型?

索引就是一种将数据库中的记录按照特殊形式存储的数据结构。

通过索引,能够显著地提高数据查询的效率,从而提升服务器的性能。

专业一点来说呢,索引是一个排好序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。

在数据库十分庞大的时候,索引可以大大加快查询的速度,这是因为使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

说起索引,其实并不是MySQL数据库特有的机制,在关系型数据库中都会有类似不同的实现。

这里我们也只是讨论MySQL数据库中的索引实现。

事实上,说是MySQL的索引其实并不准确。

因为在MySQL中,索引是在存储引擎层而不是服务器层实现的。

这意味着我们所讨论的索引准确来说是InnoDB引擎或MyISAM引擎或其它存储引擎所实现的。

所以索引即便是在MySQL中也没有统一的标准,不同存储引擎的所实现的索引工作方式也并不一样。

不是所有的存储引擎都支持相同类型的索引,即便是多个引擎支持同一种类型的索引,其底层的实现也可能不同。

mysql索引怎么重建,或者重新收集索引统计信息

需要重新创建索引,因为长度不同会认为是两个不同的所索引。

创建和删除索引

索引的创建可以在CREATE TABLE语句中进行,也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。

(1)使用ALTER TABLE语句创建索引。

语法如下:

alter table table_name add index index_name (column_list) ;

alter table table_name add unique (column_list) ;

alter table table_name add primary key (column_list) ;

其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式,table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以同时创建多个索引。

创建索引的示例如下:

mysql> use tpsc

Database changed

mysql> alter table tpsc add index shili (tpmc ) ;

Query OK, 2 rows affected (0.08 sec)

Records: 2 Duplicates: 0 Warnings: 0

(2)使用CREATE INDEX语句对表增加索引。

能够增加普通索引和UNIQUE索引两种。其格式如下:

create index index_name on table_name (column_list) ;

create unique index index_name on table_name (column_list) ;

说明:table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。

(3)删除索引。

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理,其格式如下:

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

其中,在前面的两条语句中,都删除了table_name中的索引index_name。而在最后一条语句中,只在删除PRIMARY KEY索引中使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

如果从表中删除某列,则索引会受影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

删除索引的操作,如下面的代码:

mysql> drop index shili on tpsc ;

Query OK, 2 rows affected (0.08 sec)

Records: 2 Duplicates: 0 Warnings: 0

该语句删除了前面创建的名称为“shili”的索引。

本文如未解决您的问题请添加抖音号:51dongshi(抖音搜索懂视),直接咨询即可。

精彩推荐

更多阅读

Top