短索引如何建立

liyihongcug

我的表aaa有一个字段是 varchar（255）每个值如何
AA6A89E41691778829D24C5754B4DFB3.tomcat212729622803

他记录每个登录用户的cookie
现在我据说mysql 可以使用短索引 ---利用前20个字符进行索引。 一方面节约索引空间，另一方面设计磁盘io少，查询快。

问下 这个短索引如何建立？

liyihongcug

create index ix_test on aaa(col(20))
恰当的索引可以加快查询速度，可以分为四种类型：主键、唯一索引、全文索引、普通索引。
主键：唯一且没有null值。
create table pk_test(f1 int not null,primary key(f1));
alter table customer modify id int not null, add primary key(id);

普通索引：允许重复的值出现。
create table tableanme (fieldname1 columntype,fieldname2 columntype,index [indexname] (fieldname1 [,fieldname2...]));
create table tablename add index [indexname] (fieldname1 [fieldname2...]);
alter table slaes add index(value);

全文索引：用来对大表的文本域(char，varchar，text)进行索引。
语法和普通索引一样－fulltext。
使用全文索引：create table ft2 (f1 varchar(255)，fulltext(f1));
insert into ft2 values('wating for the bvarbariands'),('in the heart of the country'),('the master of petersburg'),('writing and being'),('heart of the beast'),('master master');
select * from ft2 where match(f1) against('master'); // match()－匹配域；against()匹配值。
mysql会对某些字忽略，造成查询的误差：a. 50％以上的域出现的单词；b.少于三个字的单词；c.mysql预定义的列表，包括the。查询语句：select * from ft2 where match(f1) against('the master'); // 与希望的结果是不同的
相关性分数查询：select f1,(match(f1) against('master')) from ft2;
mysql4的新功能－布尔全文查询：select * from ft2 where match(f1) against('+master -pet' in boolean mode); // 运算符类型 +-<>()~*"

唯一索引：除了不能有重复的记录外，其它和普通索引一样。
create table ui_test (f1 int,f2 int,unique(f1));
alter table ui_test add unique(f2);
对域(varchar，char，blob，text)的部分创建索引：alter table customer add index (surname(10));

自动增加域：每次插入记录时会自动增加一个域的值,只能用于一个域，且这个域有索引。
create table tablename(fieldname int auto_increment,[fieldname2...,] primary key(filedname));
alter table tablename modify fieldname columntype auto_increment;
last_insert_id()函数返回最新插入的自动增加值。
select last_insert_id() from customer limit 1;
此函数在多个连接同时进行时，会发生错误。

重置自动增加计数器的值：
create table tablename(fieldname int auto_increment,[fieldname2...,] primary key(filedname) auto_increment=50);
alter table tablename auto_increment=50;
如果重置的值比存在的值小，自动增加计数器会从记录中最大的那个值开始增加计数，比如customer表中的id已经有1、2、3、15、16、20，当把自动增加计数器的值设为1时，下次插入的记录会从21开始。
自动增加计数器的越界：有效值为1～2的127次方，即2147483647。如果超过这个值(包括负值)，mysql会自动把它设为最大值，这样就会产生一个重复键值的错误。
自动增加域在多列索引中的使用：
create table staff(rank enum('employee','manager','contractor') not null,position varchar(100),id int not null auto_increment,primary key(rank,id));
insert into staff(rank,position) values('employee','cleaner'),('cotractor','network maintenance'),('manager','sales manager');
在对每个级别添加一些数据，会看到熟悉的自动增加现象：
insert into staff(rank,position) values('employee','cleaner1'),('employee','network maintenance1'),('manager','sales manager1');
在这种情况下是不能重置自动增加计数器的。

删除或更改索引：对索引的更改都需要先删除再重新定义。
alter table tablename drop primary key;
alter table table drop index indexname;
drop index on tablename;

高效使用索引：下面讨论的是用了索引会给我们带来什么？
1.) 获得域where从句中匹配的行：select * from customer where surname>'c';
2.) 查找max()和min()值时，mysql只需在排序的索引中查找第一个和最后一个值。
3.) 返回的部分是索引的一部分，mysql就不需要去查询全表的数据而只需看索引：select id from customer;
4.) 对域使用order by的地方：select * from customer order by surname;
5.) 还可以加速表的连接：select first_name,surname,commission from sales,sales_rep where sales.sales_rep=sales_rep.employee_number and code=8;
6.) 在通配符的情况下：select * from sales_rep where surname like 'ser%';
     这种情况就不能起作用：select * from sales_rep where surname like '%ser%';

选择索引：
1.) 有查询需要使用索引(比如where从句中条件的域)的时候，要创建索引；不要不使用的域(不如第一个字符是通配符的)创建索引。
2.) 创建的索引返回的行越少越好，主键最好，枚举类型的索引不什么用处。
3.) 使用短索引(比如，名字的头十个字符而不是全部)。
4.) 不要创建太多的索引，虽然加快了查询的速度，但增加了更新的添加记录的时间。如果索引在查询中很少使用，而没有索引只是轻微的影响速度，就不要创建索引。
最左边规则：这种情况发生在多个有索引的域上，mysql从索引列表的最左边开始，按顺序使用他们。

alter table customer add initial varchar(5);
alter table customer add index(surname,initial,first_name);
update customer set initial='x' where id=1;
update customer set initial='c' where id=2;
update customer set initial='v' where id=3;
update customer set initial='b' where id=4;
update customer set initial='n' where id=20;
update customer set initial='m' where id=21;
如果在查询中使用了这三个域，那就最大限度的利用了索引：select * from customer where surname='clegg' and initial='x' and first_name='yvonne';
或者是利用索引的大部分：select * from customer where surname='clegg' and initial='x';
或仅仅是surname：select * from customer where surname='clegg';
如果打破最左边规则，下面的例子就不会用到索引：select * from customer where  initial='x' and first_name='yvonne';
select * from customer where initial='x' ;
select * from customer where first_name='yvonne';
select * from customer where surname='clegg' and first_name='yvonne';

使用explain－解释mysql如何使用索引来处理select语句及连接表的。
输入 explain select * from customer; 后，出现一张表，个行的意思如下：
table－显示此行数据属于哪张表；type－重要的一列，显示使用了何种连接，从好到差依次为const、eq_ref、ref、range、index、all，下面会详细说明；possible_keys－可以应用在这张表中的索引，如果为null，则表示没有可用索引；key－实际使用的索引，如为null，表示没有用到索引；key_len－索引的长度，在不损失精确度的情况下，越短越好；ref－显示索引的哪一列被使用了，如果可能的话，是个常数；rows－返回请求数据的行数；extra－关于mysql如何解析查询的额外信息，下面会详细说明。

extra行的描述：distinct－mysql找到了域行联合匹配的行，就不再搜索了；
not exists－mysql优化了left join，一旦找到了匹配left join的行，就不再搜索了；
range checked for each－没找到理想的索引，一次对于从前面表中来的每一个行组合；
record(index map: #)－检查使用哪个索引，并用它从表中返回行，这是使用索引最慢的一种；
using filesort－看到这个就需要优化查询了，mysql需要额外的步骤来发现如何对返回的行排序。他根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
using index－列数据是从单单使用了索引中的信息而没有读取实际行的表返回的，这发生在对表的全部的请求列都是同一个索引时；
using temporary－看到这个就需要优化查询了，mysql需要创建一个临时表来查询存储结果，这通常发生在多不同的列表进行order by时，而不是group by；
where used－使用了where从句来限制哪些行将与下一张表匹配或是返回给用户。如不想返回表中用的全部行，并连接类型是all或index，这就会发生，也可能是查询有问题。

type的描述：system－表只有一行，这是const连接类型的特例；const－表中一个记录的最大值能够匹配这个查询(索引可以是主键或唯一索引)。因为只有一行，这个值实际就是常数，因为mysql先读这个值，再把它当作常数对待；eq_ref－从前面的表中，对每一个记录的联合都从表中读取一个记录。在查询使用索引为主键或唯一索引的全部时使用；ref－只有使用了不是主键或唯一索引的部分时发生。对于前面表的每一行联合，全部记录都将从表中读出，这个连接类型严重依赖索引匹配记录的多少－越少越好；range－使用索引返回一个范围中的行，比如使用>或<查找时发生；index－这个连接类型对前面的表中的每一个记录联合进行完全扫描(比all好，因为索引一般小于表数据)；all－这个连接类型多前面的表中的每一个记录联合进行完全扫描，这个比较糟糕，应该尽量避免。
举个例子：create index sales_rep on sales(sales_rep);  // 可以比较一下创建索引前后的变化
explain select * from sales_rep left join sales on sales.sales_rep = sales_rep.employee_number;
结果如下：
table          type         
possible
_keys
         key           key_len           ref           rows           extra
sales_rep          all          null          null          null           null           5           
sales          ref          sales_rep          sales_rep          5        
sales_rep.
employee_number
         2          
这个结果表示sales_rep表有个不好的连接类型－all，没用到索引，要查询的行数为5；sales的连接类型为ref，可用的索引是 sales_rep，实际也使用sales_rep索引，这个索引的长度是5，对应的列是employee_number，要查询的行数为2，所以这次查询对表共进行了5×2次查询。

查看索引信息：show index from tablename;
列的描述：table－正在查看的表名；non_unique－1或1.0表示索引不能包含重复值(主键和唯一索引)，1表示可以；key_name－索引名；seq_in_index－索引中列的顺序，从1开始；column_name－列名；collation－a或null，a表示索引以序升排列，null表示不排序；cardinality－索引中唯一值的个数；sub_part－如果整个列为索引，值为null，否则以字符表示索引的大小；packed－是否打包；null－如果列能包含null，则为yes；comment－各种注释。
本文阅读链接:http://www.seo-help.cn/blog/id/97
转摘
原文来自: 360doc

jimnopq

假设表名test，字段名称为a，要建前3个字符的索引。

1. mysql>alter table test add index `a`(`a`(3));

复制代码

即可！

liyihongcug

、命名规范
1. 数据库、表、字段、别名规范
识别符         最大长度(字节)         允许的字符
数据库         64         [a-z_] (所有字符均小写, 字之间用 _ 分割)
表         64         [a-z_] (所有字符均小写, 字之间用 _ 分割)
列         64         [a-z_] (所有字符均小写, 字之间用 _ 分割)
索引         64         [a-z_] (所有字符均小写, 字之间用 _ 分割)
别名         255         [a-z_] (所有字符均小写, 字之间用 _ 分割)

数据库、表、列、索引、别名的命名应尽可能描述其真实的意思。
2. 统一命名

    字段

命名空间为：
数据库::表（数据库_表名）:: 字段(简)

* 名称或标题      name (char[])
* 创建时间  create_time (datetime)
* 更新时间  update_time (datetime)
* 过期时间  expire_time (datetime)
* 数据状态  status (tinyint) ''0:正常 1:隐藏''
* ID       id (int)
* IP       ip (char[19])
* 资源文件/图片Id   resource_id (int)
* 标签  tag  (char[])
* 类型 type (tinyint)

索引 命名空间为：

index_table_field
unique_table_field
key_table_field

3. 所有日志表均以 log_ 开头 如 ：log_user_login
4. 各模块表以模块名开头 如奖品：award award_exchange
5. 数据库、表的备份，请使用数据库、表加备份时间 如：

数据库 '''camp camp_20091130'''
  表 '''award award_20091130'''

6. 对于与用户表关联的其它表，对于用户表的关联字段，除非有特殊需要，请使用 username 关联而不要使用 user_id 关联。因为许多查询中都使用 username，这样可以避免不必要的查询 (从 user_id 查得 username
二、设计规则

    在设计过程中，应该从实际需求出发，以性能提升为根本目标来展开工作，很多时候为了尽可能提高性能，必须做反范式设计。

1. 适度冗余，让查询尽量减少 JOIN (MYSQL JOIN 性能不是很高)
2. 大字段垂直分表

    大字段垂直分表简单来说就是将自己身上的字段拆分出去放到另外的表里。
    大字段一般都是存放着一些较长的 Detail 信息，如文章内容、帖子内容、产品的介绍等。
    其次是和表中的其它字段相比访问频率明显要少很多。

3. 合适的数据类型

   1. 通过选用更 "小" 的数据类型减少存储空间, 使查询相同数据需要的IO资源降低.
   2. 通过合适的数据类型加速数据的比较.
   3. 选择字段时尽量不要选用 SET, EMNUM 类型, 不便于扩展.
   4. 除了像 TEXT, BLOB, AUTO_INCREMENT 等这些列不能指定默认值的列类型之外, 应尽量为每个字段指定默认值. 这样可以增强数据的移植性和减少严格模式下出错的机会.
   5. 关联字段尽可能地建成相同列类型, 这样可以加快表关联搜索.
   6. 尽可以为每列指定 NOT NULL, 除了在确实需要 NULL 值的情况下. 这样可以减少存储空间和索引优化.
   7. 如果一个表没有像 text 这类字段，一个表尽可能用 char 替代 varchar，因为固定长度的表有更高的查询和恢复性能.
   8. 所有表、字段均应用 comment 列属性来描述此表、字段所代表的真正含义，除了意思明了的字段如： id

4. 适度的空间换时间

　　比如一个查询频繁的表，如果大部分字都时静态长度的；可以全部都换成静态的长度的（静态表），以提高查询效率。

注意:

CHAR[M] 属于静态长度类型, 存放长度完全以字符数来计算, 所以最终的存储长度是基于字符集的, 如 latin1 其最大存储长度为 255 字节, 但是如果使用 gbk 则最大存储长度为 510 (255x2) 字节. CHAR 类型的存储特点是不管实际存放的数据多长, 在数据库中都会存放 M 个字符, 不够通过空格补上, M 默认为1. 虽然 CHAR 会通过空格补齐存放空间, 但是在访问数据时, MYSQL会忽略最的的所有空格, 所以如果实数据在最后确实需要空格, 则不能使用 CHAR 类型来存放. 在MYSQL 5.03 之前的版本中, 如果定义 CHAR时 M值超过 255, MYSQL 会自动将 CHAR 类型转换为可以存入对应数据量的 TEXT类型, 如 CHAR(1000) 会自动转换为 TEXT, CHAR(10000) 则会转为 MEDIUMTEXT. 而从 MYSQL 5.0.3 开始, 所有超过 255 的定义 MYSQL 都会直接拒绝并给出错误信息, 不再自动转换.

VARCHAR[M] 属于动态存储长度类型, 仅存储占用实际存储数据的长度. 其存放的最大长度与 MYSQL 版本有关, 在 5.0.3 之前的版本 VARCHAR 以字符数控制存储的最大长度, 最大只能存放 255 个字符, 占用存储空间的实际大小与字符集有关. 但是从 5.0.3 开始, VARCHAR 的最大存储限制已经更改为字节数限制了, 扩展到可以存放 65535 字节的数据, 不同的字符集可能存放的字符数并不一样. 也就是说, 在 MYSQL 5.0.3 之前的版本, M 所代表的是字符数, 而从 5.0.3 版本开始, M 代表字节数了. VARCHAR 的存储特点是不管设定 M 为多大值, 真正占用的存储空间只有存入的实际数据的大小, 和 CHAR 不同的是 VARCAHR 会保留存入数据最后的空格, 也就是说我们存入什么, MYSQL 返回的就是什么. 在 VARCHAR 类型字段的数据中, MYSQL 会在每个 VARCHAR 数据中使用 1 到 2 个字节来存放 VARCHAR 数据的实际长度, 当实际数据在 255 字节之内时, 会使用 1 字节来存放实际长度, 而大于 255 字节时, 则需要使用 2 字节来存放.

TINYTEXT, TEXT, MEDIUMTEXT 和 LONGTEXT 这 4 种类型同属于一种存储方式, 即动态存储长度类型, 不同的仅是最大长度的限制. 4 种类型的定义都是通过最大字符数来限制, 但它们的字符数限制实际上是可以理解为字节数限制, 因为当使用多字节字符集时, 实际能存放的字符数并没最大字符数那么多, 而是以单字节字符来计算的字符数. 此外, 由于是动态存储长度类型, 所以和 VARCHAR 一样, 每个字段数据之前都需要一个存放实际长度的空间. TINYTEXT 需要 1 个字节来存放, TEXT 需要 2 个字节, MEDIUMTEXT 和 LONGTEXT 则分别需要 3 个和 4 个字节来存放实际数据长度. 实际上, 除了 MYSQL 内嵌的最大长度限制之外, 它们还受到客户端与服务器端的网络通信缓冲区最大值 (max_allowed_packet 默认为 1M, 也就是说, MEDIUMTEXT 和 LONGTEXT 在默认情况可能存不进去值) 的限制.

这 4 种 TEXT 类型和 CHAR 及 VARCHAR 在实际使用中存在几个不一样的地方:

1. 不能设置默认值.
2. 只有 TEXT 可以使用 TEXT[M] 这样的方式通过 M 设置大小.
3. 基于这 4 种类型的索引必须指定前缀长度.

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别占用的字节为 1, 2, 3, 4, 8. INT 类型的值就上亿了.
对于 INT[M] 中 M 值的解释: 以前我遇到很多人他们认为 INT(4), INT(10) 其取值范围分别是 (-9999 到 9999), (-9999999999 到 9999999999). 这种理解是错误的. 其实对整型中的 M 值, 与 ZEROFILL 属性结合使用时, 可以实现列值等宽. 不管 INT[M] 中 M 值是多少, 其取值范围还是 (-2147483648 到 2147483647 有符号时), (0 到 4294967295 无符号时). 官方文档说明: 显示宽度并不限制可以在列内保存的值的范围, 也不限制超过列的指定宽度的值的显示. 当结合可选扩展属性ZEROFILL使用时, 默认补充的空格用零代替. 例如: 对于声明为INT(5) ZEROFILL的列, 值4检索为00004. 请注意如果在整数列保存超过显示宽度的一个值, 当MySQL为复杂联接生成临时表时会遇到问题, 因为在这些情况下MySQL相信数据适合原列宽度.如果为一个数值列指定ZEROFILL, MySQL自动为该列添加UNSIGNED属性.
三、创建索引

a. 较频繁的作为查询条件的字段应该创建索引.

b. 唯一性太差的字段不适合单独创建索引, 即使频繁作为查询条件.

唯一性太差的字段主要是指哪些呢? 如状态字段, 类型字段等这些字段中存放的数据可能总共就那么几个或几十个值重复使用, 每个值都会存在于成千上万或更多的记录中. 对于这类字段, 完全没有必要创建单独的索引. 因为即使创建了索引, MYSQL QUERY OPTIMIZER 大多数时候也不会去选择使用, 如果什么时候 MYSQL QUERY OPTIMIZER 选择了这各索引, 那么非常遗憾地告诉你, 这可能会带来极大的性能问题. 由于索引字段中每个值都会含有大量的记录, 那么存储引擎在根据索引访问数据的时候会带来大量的随机 IO, 甚至有些时候还会出现大量的重复 IO.

c. 更新频繁的字段不适合创建索引

索引中的字段被更新的时候, 不仅要更新表中的数据, 还要更新索引数据, 以确保索引信息是准确. 这个问题致使 IO 访问量较大增加, 不仅仅影响了更新 Query 的响应时间, 还影响了整个存储系统资源消耗, 加大了整个存储系统负载.

d. 不会出现在 WHERE 子句中的字段不该创建索引.

注意:
MYSQL 中索引的限制
1. MYISAM 存储引擎索引长度的总和不能超过 1000 字节.
2. BLOB 和 TEXT 类型的列只能创建前缀索引.
3. MYSQL 目前不支持函数索引.
4. 使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引.
5. 过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引.
6. Jion 语句 中 Jion 条件字段类型不一致的时候, MYSQL无法使用索引.
7. 使用 LIKE 操作的时候如果条件以通配符开始 (如 '%abc...')时, MYSQL无法使用索引.
8. 使用非等值查询的时候, MYSQL 无法使用 Hash 索引.

建表 SQL 语句示例：

http://hi.baidu.com/thk_phper/bl ... 0d7c330dd7da6f.html

CREATE TABLE `admin` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '所属组ID',
  `username` char(16) NOT NULL COMMENT '用户名',
  `password` char(33) NOT NULL COMMENT '密码',
  `acl` text NOT NULL COMMENT '资源控制',
  `ctime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户表';

ruochen

LZ最近转载了很多文章

liyihongcug

恰当的索引可以加快查询速度，可以分为四种类型：主键、唯一索引、全文索引、普通索引。
主键：唯一且没有null值。
create table pk_test(f1 int not null,primary key(f1));
alter table customer modify id int not null, add primary key(id);
普通索引：允许重复的值出现。
create table tableanme (fieldname1 columntype,fieldname2 columntype,index [indexname] (fieldname1 [,fieldname2...]));
create table tablename add index [indexname] (fieldname1 [fieldname2...]);
alter table slaes add index(value);
全文索引：用来对大表的文本域(char，varchar，text)进行索引。
语法和普通索引一样－fulltext。
使用全文索引：create table ft2 (f1 varchar(255)，fulltext(f1));
insert into ft2 values('wating for the bvarbariands'),('in the heart of the country'),('the master of petersburg'),('writing and being'),('heart of the beast'),('master master');
select * from ft2 where match(f1) against('master'); // match()－匹配域；against()匹配值。
mysql会对某些字忽略，造成查询的误差：a. 50％以上的域出现的单词；b.少于三个字的单词；c.mysql预定义的列表，包括the。查询语句：select * from ft2 where match(f1) against('the master'); // 与希望的结果是不同的
相关性分数查询：select f1,(match(f1) against('master')) from ft2;
mysql4的新功能－布尔全文查询：select * from ft2 where match(f1) against('+master -pet' in boolean mode); // 运算符类型 +-<>()~*"
唯一索引：除了不能有重复的记录外，其它和普通索引一样。
create table ui_test (f1 int,f2 int,unique(f1));
alter table ui_test add unique(f2);
对域(varchar，char，blob，text)的部分创建索引：alter table customer add index (surname(10));
自动增加域：每次插入记录时会自动增加一个域的值,只能用于一个域，且这个域有索引。
create table tablename(fieldname int auto_increment,[fieldname2...,] primary key(filedname));
alter table tablename modify fieldname columntype auto_increment;
last_insert_id()函数返回最新插入的自动增加值。
select last_insert_id() from customer limit 1;
此函数在多个连接同时进行时，会发生错误。
重置自动增加计数器的值：
create table tablename(fieldname int auto_increment,[fieldname2...,] primary key(filedname) auto_increment=50);
alter table tablename auto_increment=50;
如果重置的值比存在的值小，自动增加计数器会从记录中最大的那个值开始增加计数，比如customer表中的id已经有1、2、3、15、16、20，当把自动增加计数器的值设为1时，下次插入的记录会从21开始。
自动增加计数器的越界：有效值为1～2的127次方，即2147483647。如果超过这个值(包括负值)，mysql会自动把它设为最大值，这样就会产生一个重复键值的错误。
自动增加域在多列索引中的使用：
create table staff(rank enum('employee','manager','contractor') not null,position varchar(100),id int not null auto_increment,primary key(rank,id));
insert into staff(rank,position) values('employee','cleaner'),('cotractor','network maintenance'),('manager','sales manager');
在对每个级别添加一些数据，会看到熟悉的自动增加现象：
insert into staff(rank,position) values('employee','cleaner1'),('employee','network maintenance1'),('manager','sales manager1');
在这种情况下是不能重置自动增加计数器的。
删除或更改索引：对索引的更改都需要先删除再重新定义。
alter table tablename drop primary key;
alter table table drop index indexname;
drop index on tablename;
高效使用索引：下面讨论的是用了索引会给我们带来什么？
1.) 获得域where从句中匹配的行：select * from customer where surname>'c';
2.) 查找max()和min()值时，mysql只需在排序的索引中查找第一个和最后一个值。
3.) 返回的部分是索引的一部分，mysql就不需要去查询全表的数据而只需看索引：select id from customer;
4.) 对域使用order by的地方：select * from customer order by surname;
5.) 还可以加速表的连接：select first_name,surname,commission from sales,sales_rep where sales.sales_rep=sales_rep.employee_number and code=8;
6.) 在通配符的情况下：select * from sales_rep where surname like 'ser%';
     这种情况就不能起作用：select * from sales_rep where surname like '%ser%';
选择索引：
1.) 有查询需要使用索引(比如where从句中条件的域)的时候，要创建索引；不要不使用的域(不如第一个字符是通配符的)创建索引。
2.) 创建的索引返回的行越少越好，主键最好，枚举类型的索引不什么用处。
3.) 使用短索引(比如，名字的头十个字符而不是全部)。
4.) 不要创建太多的索引，虽然加快了查询的速度，但增加了更新的添加记录的时间。如果索引在查询中很少使用，而没有索引只是轻微的影响速度，就不要创建索引。
最左边规则：这种情况发生在多个有索引的域上，mysql从索引列表的最左边开始，按顺序使用他们。
alter table customer add initial varchar(5);
alter table customer add index(surname,initial,first_name);
update customer set initial='x' where id=1;
update customer set initial='c' where id=2;
update customer set initial='v' where id=3;
update customer set initial='b' where id=4;
update customer set initial='n' where id=20;
update customer set initial='m' where id=21;
如果在查询中使用了这三个域，那就最大限度的利用了索引：select * from customer where surname='clegg' and initial='x' and first_name='yvonne';
或者是利用索引的大部分：select * from customer where surname='clegg' and initial='x';
或仅仅是surname：select * from customer where surname='clegg';
如果打破最左边规则，下面的例子就不会用到索引：select * from customer where  initial='x' and first_name='yvonne';
select * from customer where initial='x' ;
select * from customer where first_name='yvonne';
select * from customer where surname='clegg' and first_name='yvonne';

使用explain－解释mysql如何使用索引来处理select语句及连接表的。
输入 explain select * from customer; 后，出现一张表，个行的意思如下：
table－显示此行数据属于哪张表；type－重要的一列，显示使用了何种连接，从好到差依次为const、eq_ref、ref、range、index、all，下面会详细说明；possible_keys－可以应用在这张表中的索引，如果为null，则表示没有可用索引；key－实际使用的索引，如为null，表示没有用到索引；key_len－索引的长度，在不损失精确度的情况下，越短越好；ref－显示索引的哪一列被使用了，如果可能的话，是个常数；rows－返回请求数据的行数；extra－关于mysql如何解析查询的额外信息，下面会详细说明。
extra行的描述：distinct－mysql找到了域行联合匹配的行，就不再搜索了；
not exists－mysql优化了left join，一旦找到了匹配left join的行，就不再搜索了；
range checked for each－没找到理想的索引，一次对于从前面表中来的每一个行组合；
record(index map: #)－检查使用哪个索引，并用它从表中返回行，这是使用索引最慢的一种；
using filesort－看到这个就需要优化查询了，mysql需要额外的步骤来发现如何对返回的行排序。他根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
using index－列数据是从单单使用了索引中的信息而没有读取实际行的表返回的，这发生在对表的全部的请求列都是同一个索引时；
using temporary－看到这个就需要优化查询了，mysql需要创建一个临时表来查询存储结果，这通常发生在多不同的列表进行order by时，而不是group by；
where used－使用了where从句来限制哪些行将与下一张表匹配或是返回给用户。如不想返回表中用的全部行，并连接类型是all或index，这就会发生，也可能是查询有问题。
type的描述：system－表只有一行，这是const连接类型的特例；const－表中一个记录的最大值能够匹配这个查询(索引可以是主键或唯一索引)。因为只有一行，这个值实际就是常数，因为mysql先读这个值，再把它当作常数对待；eq_ref－从前面的表中，对每一个记录的联合都从表中读取一个记录。在查询使用索引为主键或唯一索引的全部时使用；ref－只有使用了不是主键或唯一索引的部分时发生。对于前面表的每一行联合，全部记录都将从表中读出，这个连接类型严重依赖索引匹配记录的多少－越少越好；range－使用索引返回一个范围中的行，比如使用>或<查找时发生；index－这个连接类型对前面的表中的每一个记录联合进行完全扫描(比all好，因为索引一般小于表数据)；all－这个连接类型多前面的表中的每一个记录联合进行完全扫描，这个比较糟糕，应该尽量避免。
举个例子：create index sales_rep on sales(sales_rep);  // 可以比较一下创建索引前后的变化
explain select * from sales_rep left join sales on sales.sales_rep = sales_rep.employee_number;
结果如下：
table          type           possible_keys          key           key_len           ref           rows           extra
sales_rep          all          null          null          null           null           5           
sales          ref          sales_rep          sales_rep          5          sales_rep.employee_number          2          
这个结果表示sales_rep表有个不好的连接类型－all，没用到索引，要查询的行数为5；sales的连接类型为ref，可用的索引是 sales_rep，实际也使用sales_rep索引，这个索引的长度是5，对应的列是employee_number，要查询的行数为2，所以这次查询对表共进行了5×2次查询。

查看索引信息：show index from tablename;
列的描述：table－正在查看的表名；non_unique－1或1.0表示索引不能包含重复值(主键和唯一索引)，1表示可以；key_name－索引名；seq_in_index－索引中列的顺序，从1开始；column_name－列名；collation－a或null，a表示索引以序升排列，null表示不排序；cardinality－索引中唯一值的个数；sub_part－如果整个列为索引，值为null，否则以字符表示索引的大小；packed－是否打包；null－如果列能包含null，则为yes；comment－各种注释。

MySQL索引经验之浅见
2009-08-14 14:34:15 来源:程序员之家(www.sunxin.org) 【大 中 小】 评论：0 条
-

【IT168技术文档】

    大家都知道在数据库表中，使用索引可以大大提高查询速度。本文讲了这么多，也无非是想利用索引提高数据库的执行效率。不过索引只是提高效率的一个因素。如果你的MySQL有大数据的表，就需要花时间研究建立最优秀的索引或优化查询语句。

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

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

    二、介绍一下索引的类型

    1.普通索引。
    这是最基本的索引，它没有任何限制。它有以下几种创建方式：
    （1）创建索引：CREATE INDEX indexName ON tableName(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 ON tableName(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_Name VARCHAR(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_Name VARCHAR(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 WHERE vc_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" AND vc_City="郑州" SELECT * FROM myIndex WHREE vc_Name="erquan"

    而下面几个则不会用到：

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

1

四、使用索引

    到此你应该会建立、使用索引了吧？但什么情况下需要建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为 MySQL只对 <，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE(后面有说明)才会使用索引。
SELECT t.vc_Name FROM testIndex t LEFT JOIN myIndex m ON t.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_Name like@#%erquan@#

    就不会使用索引了。

    五、索引的不足之处

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

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

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

http://www.itxuexi.com/tech/shujuku/mysql/10291250231655.html

0vk0

这些文章真的很不错