金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 删除表索引,索引介绍

删除表索引,索引介绍

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-22 11:08

二 .设计索引的原则:

  1. 索引使用在where后的列,而不是select 选择的列。
  2. 索引列的基数越大,索引效果越好。
  3. 使用短索引, 如果对字符串进行索引,应该指定一个前缀长度。如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。这样能够节省索引空间。
    4.不过度使用索引。因为占用磁盘空间,降低写操作性能。
    5. innodb表的普通索引都会保存主键的键值,所以主键的键值尽可能选择较短的类型。
  4. 利用最左前缀,在创建一个n列索引时,实际是创建了mysql 可利用的n个索引,多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。

    -- 创建city表的多列复合索引 CREATE INDEX ix1 ON city(cityname(10),citycode); -- 走索引的语句 EXPLAIN SELECT FROM city WHERE cityname='' AND citycode=''; EXPLAIN SELECT FROM city WHERE cityname='' -- 不走索引 EXPLAIN SELECT * FROM city WHERE citycode=''

6. 长度要短,使用短索引,如果对字符串列进行索引,应该指定一个前缀长度(利用最左前缀)

mysql 索引

二级索引??
mysql中每个表都有一个聚簇索引(clustered index ),除此之外的表上的每个非聚簇索引都是二级索引,又叫辅助索引(secondary indexes)。

以InnoDB来说,每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键,该主键索引是聚集索引。如果你不定义为您的表的主键时,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚集索引。如果没有这样的列,InnoDB就自己产生一个这样的ID值,它有六个字节,而且是隐藏的,使其作为聚簇索引。

聚簇索引主要是为了方便存储。。所以二级索引应该都是对聚簇索引的索引。
下面是Mysql Manual上的原话,也可能我理解有误。
Every InnoDB table has a special index called the clustered index where the data for the rows is stored. If you define a PRIMARY KEY on your table, the index of the primary key is the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index recor......余下全文>>  

Mysql索引概述 所有MySQL列类型可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎定...

加索引
mysql> alter table 表名 add index 索引名 (字段名1[,字段名2 …]);

一.概述

  所有mysql 列类型都可以被索引,是提高select查询性能的最佳方法。 根据存储引擎可以定义每个表的最大索引数和最大索引长度,每种引擎对每个表至少支持16个索引,总索引长度至少为256字节。
  myisam和innodb引擎的表默认是btree索引,支持前缀索引,前缀索引长度跟存储引擎相关,对于myisam引擎 ,长度可达1000字节长,对于innodb 长度可达767字节,在使用多字节字符集的列指定前缀长度时要考虑。
  支持全文索引(fulltext),只有myisam引擎支持,只限于char,varchar,text列。默认memory引擎使用hash索引,也支持tbree索引。

   1.  例如,要为city 表创建了10 个字节的前缀索引,语法是:

-- 为city表的cityname字段创建10个字节的前缀索引
CREATE INDEX ixcityname ON city(cityname(10));
--  索引查看
EXPLAIN SELECT * FROM city WHERE cityname='';

金沙棋牌app手机下载 1

  2.  删除索引

DROP INDEX  ixcityname ON  city;

金沙棋牌app手机下载 2

值高说明查询效率低,表索引不正确或写入的查询没有用到索引

mysql索引类型解释

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。
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]金沙棋牌app手机下载, 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个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然......余下全文>>  

create table test (blob_col blob, index(blob_col(10)));在mysql 5.1中,对于myisam和innodb表,前缀可以达到1000字节长。请注意前缀的限制应以字节为单位进行测量,而create table语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。

三. btree索引与hash索引 

memory 引擎可以选择使用btree或hash索引, 两种不同类型的索引各有其不同的适用范围, hash索引使用需要注意:

  1. 只用于使用=或 <=>操作符的等式比较。
  2. 优化器不能使用hash索引来加速order by 操作。
  3. mysql 不能确定在两个值之间大约有多少行。如果将一个myisam表改为hash索引的memory表,会影响一些查询的执行效率。
  4. 只能使用整个关键字来搜索一行。

下面来演示下:

-- 在city表上添加city_memory表
 CREATE TABLE city_memory SELECT * FROM city
-- 添加外键
ALTER TABLE city_memory ADD KEY idx_fk_country_id(country_id) ;
-- 添加主键
 ALTER TABLE city_memory ADD PRIMARY KEY(city_id);

-- city的btree 走索引
EXPLAIN SELECT * FROM city WHERE country_id > 1 AND country_id < 10

金沙棋牌app手机下载 3

--  city_memory的 hash 不走索引
EXPLAIN SELECT * FROM city_memory WHERE country_id > 1 AND country_id < 10
EXPLAIN SELECT * FROM city_memory WHERE country_id > 1
EXPLAIN SELECT * FROM city_memory WHERE country_id < 10

金沙棋牌app手机下载 4

-- city_memory的 hash 走索引
EXPLAIN SELECT * FROM city_memory WHERE country_id= 10
EXPLAIN SELECT * FROM city_memory WHERE country_id IN (10,11);

金沙棋牌app手机下载 5

  总结:大多数mysql 索引(如 primary key, unique index , index, fulltext index)在btree中存储,只有空间列类型索引使用rtree,并且memory表支持hash索引

6.创建复合索引:

Mysql索引详细介绍,mysql索引

Mysql索引概述

所有MySQL列类型可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。大多数存储引擎有更高的限制。

在MySQL 5.1中,对于MyISAM和InnoDB表,前缀可以达到1000字节长。请注意前缀的限制应以字节为单位进行测量,而CREATE TABLE语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。

还可以创建FULLTEXT索引。该索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行,不支持局部(前缀)索引。也可以为空间列类型创建索引。只有MyISAM存储引擎支持空间类型。空间索引使用R-树。默认情况MEMORY(HEAP)存储引擎使用hash索引,但也支持B-树索引。

设计索引的原则

1) 搜索的索引列,不一定是所要选择的列。

换句话说,最适合索引的列是出现在WHERE 子句中的列,或连接子句中指定的列,而不是出现在SELECT 关键字后的选择列表中的列。

2) 使用惟一索引。

考虑某列中值的分布。对于惟一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分 各行。而用来记录性别的列,只含有“ M”和“F”,则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)。

3) 使用短索引。

如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,如果有一个CHAR(200) 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘I/O 较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL也可以在内存中容纳更多的值。这增加 了找到行而不用读取索引中较多块的可能性。(当然,应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的,因为这个索引中不会有许多不 同的值。)

4) 利用最左前缀。

在创建一个n 列的索引时,实际是创建了MySQL可利用的n 个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同,索引一个列的前缀是利用该的前n 个字符作为索引值。)

5) 不要过度索引。

不要以为索引“越多越好”,什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍 过。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表 的修改速度。此外,MySQL在生成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使 MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左 索引。如果是,则就不要费力去增加这个索引了,因为已经有了。

6) 考虑在列上进行的比较类型。

索引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和BETWEEN 运算。在模式具有一个直接量前缀时,索引也用于LIKE 运算。如果只将某个列用于其他类型的运算时(如STRCMP( )),对其进行索引没有价值。

btree索引与hash索引

 对于BTREE和HASH索引,当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时,关键元素与常量值的比较关系对应一个范围条件。Hash索引还有一些其它特征:它们只用于使用=或<=>操作符的等式比较(但很快)。优化器不能使用hash索引来加速ORDER BY操作。(该类索引不能用来按顺序搜索下一个条目)。MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索引)。如果你将一个MyISAM表改为hash-索引的MEMORY表,会影响一些查询。只能使用整个关键字来搜索一行。(用B-树索引,任何关键字的最左面的前缀可用来找到行)。

对于BTREE索引,当使用>、<、>=、<=、BETWEEN、!=或者<>,或者LIKE 'pattern'(其中 'pattern'不以通配符开始)操作符时,关键元素与常量值的比较关系对应一个范围条件。“常量值”系指:查询字符串中的常量、同一联接中的const或system表中的列、无关联子查询的结果、完全从前面类型的子表达式组成的表达式。

下面是一些WHERE子句中有范围条件的查询的例子。

下列范围查询适用于 btree索引和hash索引:    

复制代码 代码如下:

SELECT * FROM t1     WHERE key_col = 1     OR key_col IN (15,18,20);

 下列范围查询适用于btree索引

复制代码 代码如下:

SELECT * FROM t1     WHERE key_col > 1     AND key_col < 10;
    SELECT * FROM t1     WHERE key_col LIKE 'ab%'     OR key_col BETWEEN 'bar' AND 'foo';

Mysql如何使用索引

索引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大,花费的时间越多。如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据。如果一个表有1000行,这比顺序读取至少快100倍。注意如果你需要访问大部分行,顺序读取要快得多,因为此时我们避免磁盘搜索。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树,并且MEMORY表还支持hash索引。

关于什么情况下数据库会使用索引以及什么情况下数据库不会使用索引的详细解释请看优化篇的相关章节,这里就不再累述。

例子: mysql> show index from employee;

1.建立索引,是为了方便检索、排序和分组。索引可以分为:普通索引 前缀索引全文本索引复合索引(最左前缀索引)聚族索引

还可以创建fulltext索引。该索引可以用于全文搜索。只有myisam存储引擎支持fulltext索引,并且只为char、varchar和text列。索引总是对整个列进行,不支持局部(前缀)索引

2. 大多数mysql索引(如primary key,unique,index和fulltext等)在btree中存储。只是空间列类型的索引使用rtree,并且memory表还支持hash索引。

mysql按这样的方式使用多列索引:当你在where子句中为索引的第1个列指定已知的数量时,查询很快,即使你没有指定其它列的值。

(二)索引缺点

mysql教程 修改表/字段 增加/删除表索引

handler_read_key值:一行被索引值读的次数

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 last_name='widenius'    and (first_name='michael' or first_name='monty'); select * from test    where last_name='widenius'    and first_name >='m' and first_name < 'n';然而,name索引不用于下面的查询:

select * from test where first_name='michael'; select * from test    where last_name='widenius' or first_name='michael';

7.删除索引:Drop index index_name on tbl_name;

修改原字段名称及类型:mysql> alter table table_name change old_field_name new_field_name field_type;

7. 不要过度索引

多列索引可以视为包含通过连接索引列的值而创建的值的排序的数组。

| Handler_read_first    | 261         |

修改表/字段 增加/删除表索引 create table test (blob_col blob, index(blob_col(10)));在mysql 5.1中,对于myisam和innodb表,前缀可以达到1000字节长...

(四)查看索引使用效率

删除某个索引
mysql> alter table 表名 drop index 索引名;

+-----------------------+-------------+

例子: mysql>alter table employee drop index emp_name;

handler_read_rnd_next:在数据文件中读取下一行的请求数

最后补充一点

1. 数据内容的变更(增、删、改)都需要修订索引,索引存在而外的维护成本

删除字段:mysql alter table table_name drop field_name;

1. 数据量要多,如果我们的数据量很少,索引并不会带来什么作用,不建议使用

例子: mysql> alter table employee add index emp_name (name);
加主关键字的索引
mysql> alter table 表名 add primary key (字段名);
例子: mysql> alter table employee add primary key(id);
加唯一限制条件的索引
mysql> alter table 表名 add unique 索引名 (字段名);
例子: mysql> alter table employee add unique emp_name2(cardnumber);
mysql alter语法运用:查看某个表的索引
mysql> show index from 表名;

5. 字段顺序要佳,字段的顺序对组合索引的效率有至关重要的作用,过滤效果好的字段需要更靠前

修改表:增加字段:mysql> alter table table_name add field_name field_type;
查看表:mysql> select * from table_name;

+-----------------------+-------------+

 

| Handler_read_rnd      | 0           |

多列索引
mysql可以为多个列创建索引。一个索引可以包括15个列。对于某些列类型,可以索引列的前缀(参见7.4.3节,“列索引”)。

| Variable_name         | Value       |

假定表具有下面的结构:

 低则说明增加索引得到的性能改善不高

(三) 索引使用原则

  show  global status like 'handler_read%';

 

2. 查找翻阅索引系统需要消耗时间,索引存在而外的访问成本

3. 磁盘空间要够,如果存储基础数据的空间已经非常少,建议尽量减少低效或者是去除索引

| Handler_read_key      | 22542789    |

Create index ind_sales2 on sales2(id,moneys);

Create index cityname on city(city(10));

| Handler_read_next     | 14863439517 |

       每个MyISAM表最大索引数是64。 这可以通过重新编译来改变。每个索引最大的列数是16个。最大的键长度是1000字节。这也可以通过编译来改变。对于键长度超过250字节的情况,一个超过1024字节的的键块被用上

 

3. 索引系统需要一个地方来存放,索引存在额外的空间成本

| Handler_read_prev     | 50741626    |

+-----------------------+-------------+

3.前缀索引的长度跟存储引擎有关,对于myisam存储引擎的表,索引的前缀长度最长可以达到1000字节长,而对于innodb的存储引擎的表,索引的前缀最长是767字节。前缀的限制应以字节为单位进行测量,而create table语句中的前缀长度解释为字符数,在为其使用多字节字符集的列指定前缀长度时一定要加以考虑

(一)notice

 

| Handler_read_last     | 19          |

4.聚族索引。对于innodb存储引擎的表,记录默认会按照一定的顺序保存,如果有明确定义的主键,按主键顺序保存,如果没有主键,但是有唯一索引,那么就按照唯一索引的顺序保存。如果既没有主键也没有唯一索引,那么表中会自动生成一个内部列,按照这个列的顺序保存。按照主键或内部列的顺序访问是最快的,所以innodb的表尽量自己指定主键,当表中同时有几个列是唯一的,可以作为主键的时候,要选择最常作为访问条件的列作为主键。Innodb表的索引都会保存主键的键值,所以主键尽可能选择较短的数据类型。可以有效的减少索引的磁盘占用,提高索引的缓存效果

| Handler_read_rnd_next | 21018305873 |

 

2. 数据更新要少,如果存在非常大更新量的数据,索引的维护成本会非常高,如果其检索需求很少,而且对检索效率并没有非常高的要求的时候,不建议使用索引

5.创建前缀索引:

4. 过滤效果要好(索引列的基数要大),当我们需要读取的数据量占整个数据量的比例较大或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描(使用唯一索引)

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:删除表索引,索引介绍

关键词: