Mysql学习笔记二

接着上面继续学习，下面主要是以索引为主。

 

第四章索引与查询优化

创建索引

有四种类型的索引：主键、唯一索引、全文索引和普通索引

 

创建主键：

它是值惟一并且没有值为NULL的域的索引。

如:create table tableName(filename columntype not null,filedname2....] primary key (fieldname);

注意关键字not null在创建主键时是强制性的，主键不能包含null值。

create table pk_tets(f1 int,primary key(f1));

在一个已经存在的表上创建主键，可以使用alter关键字：

alter table tablename add primary key(filedname);

下面是修改列属性为not null的同时增加主键。

alter table customer modify id int not null,add primary key(id);

 

创建普通索引

一般在建表的同时创建索引。

create table tablename(filedname columntype,filedname2 columntype ,index [indexname] (filedname1,filedname2));;

 

也可以使用下面的代码在以后创建索引：

alter table tablename add index [idexname] (filedname);

或

create index indexname on table_name(filedname));

 

创建全文索引

可以在表myisam中针对任意的char、varchar或text域创建全文索引。它是用来对大表中的文本进行索引的。

create table tablename (filedname columntype,fulltext index(filedname)):

 

alter table tablename add fulltext [indexname] (filedname);

或

create fulltext index indexname on tableName(filedname);

可以使用match()函数匹配域，against()匹配值，来返回全文检索的结果。如：

查询单词master出现的次数：

select * from t2 where match(f1) against('master');

 

创建唯一索引

惟一索引除了不容许有重复的记录以外，其它与普通索引一样。

create table tablename(fieldname columntype,unique (filedname));

或表已经存在直接增加：

alter table tablename add unique [indexname] (fieldname);

或

create unique index indexname on tablename(fiedldname);

 

create table u1_test(f1 int,f2 int,unqiue(f1));

可以对域的部分创建索引，如alter table customer add index(name (10));  对表中的姓氏域创建10个字符的索引。

 

 

 

使用自动增加域

这个域必须是数字主键或数字惟一索引

如：

create table tableName(fieldname int auto_increment,{filedname2...} primary key (fieldname));

 

alter table tablename modify filedname columntype auto_increment;

查看表列的属性

show columns from customer;

注意：一个最重要的特征是即使记录被删除了，mysql的自动增加计数器也会记住最近增加的数，这可以保证新插入的记录有一个新的ID值并且不和旧的任务记录冲突。

 

使用last_insert_id()函数来返回最近插入的自动增加值。

select last_insert_id() from customer limit 1;

 

重置计数器的值：

alter table ai_tets auto_increment=1;

 

删除或改变索引

删除主键

alter table tablename drop primary key;

删除普通、全文索引，只需要指定索引名

alter table tablename drop index idexname；

或drop index indexname on tablename;

 

显示索引的名字：

show keys from tablename;

使用explain 分析mysql如何使用索引

explain select * from aa where name='test';

 

各列的描述：

Table	显示其余的行是关于哪张表的
Type	这是重要的表，显示连接使用了何种类型，最好到最差的类型为：const\eq_ref,ref,range,index和all
Possible_keys	显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句选择一个合适的
Key	实际使用的索引，如果为NULL，则没有使用索引。可以select语句使用use index(indexname)来强制使用一个索引或用ignore index(idexname)来强制忽略索引
key_len	使用的索引的长度。长度越短越好
Ref	显示索引的哪一列被使用了，如果可能的话，是一个常数
Rows	Mysql认为必须检查的用来返回请求数据的行数
Extra	关于mysql如何解析查询的额外信息。如果是using temporary或using filesort,意思是根本不能使用索引，结果是检查会很慢

extra explain列返回的描述的意义

Extra column描述
Distinct	一旦找到与行联合匹配的行，就不再搜索了
Not exists
Range checked for each record(index map:#)	没有找到理想的索引
Using filesort	这个需要优化。需要进行额外的排序
Using index	列数据是从仅仅使用了索引中的信息而没有读取实际的行的表返回的，
Using temporary	需要创建一个临时表来存储结果，需要优化
Where used	使用了where 从句来限制哪些行将与下一张表匹配或是返回给用户。或查询有问题。

 

optimize table对表进行优化和管理。

 

 

不同的连接类型

System	表只有一行，这是const连接类型的特殊情况
Const	表中的一个记录的最大值能够匹配这个查询
Eq_ref	从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
Ref	这个连接类型只有在查询使用了不是惟一或主键的键或是这些类型的部分时发生。对于之前的表的每一个行联合，全部记录都将从表中读出，这个连接类型严重信赖于根据索引匹配的记录多少，越少越好。
Range	使用索引返回一个范围中的行，如使用><或查询东西时发生的
Index	对前面的表中的每一个记录联合进行完全扫描比all更好，因为索引一般小于表数据。
ALL	这个连接类型对前面的表中的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。