Mysql之索引小结

一．MySQL通常采用下面两种方式来访问表中的行： （1）顺序访问 从头到尾逐行遍历整张表，实现全表扫描。当表中行数非常多时，非常浪费时间，并且大大降低数据库的查询效率。 （2）索引访问 所谓索引，就是根据原表中的一列或者若干列建立的列值与对应记录行之间的关系表。在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快速地查找到数据。

二．索引的存储 索引是在存储引擎中实现的，每种存储引擎所支持的索引类型不一定完全相同，而且并非所有的存储引擎都会支持所有的索引类型。

三．索引的分类 根据存储方式的不同，在物理上分为以下两类： （1）B-树索引（BTree) 目前大部分索引都是以B-树来存储的，就是将索引采用树的数据结构形式存储。 （2）哈希索引 在mysql中目前只有memory存储引擎和heap存储引擎支持这类索引。其中，memory存储引擎是将哈希索引作为默认索引，而且它支持Ｂ－树索引。

根据索引的具体用途，在逻辑上分为以下几类： （１）普通索引 （２）唯一性索引（unique） （３）主键 （４）全文索引（fulltext） （５）空间索引

在实际使用中，索引通常被创建成单列索引和组合索引 （１）单列索引 一个索引只包含原表中的一个列。 （２）组合索引 一个索引包含原表中的多个列。

高性能的索引策略 （１）聚集索引 （２）覆盖索引

四．创建索引的三种方法 （１）create index语句 create　index　index_customers on mysql_test.customers(cust_name(3) asc);//根据客户姓名列的前三个字符采用默认的索引类型创建一个升序索引index_customers。

create　index　index_cust on mysql_test.custommers(cust_name,cust_id) using btree;//根据cust_name和cust_id采用btree的索引类型创建一个复合索引index_cust

（２）create table语句 create table seller ( seller_id int not null auto_increament, seller_name char(50) not null, seller_address char(50) null, seller_contact char(50) null, PRoduct_type int(5) null, sales int null, primary key(seller_id,product_type), index index_seller(sales) );

（３）alter table语句 alter table mysql_test.seller add index index_seller_name(seller_name);//在表seller的姓名列上添加一个非唯一的索引，取名为index_seller_name

五．查看索引 show index;

六．索引的删除 （１）drop index index_cust on mysql_test.customers; （２）alter table mysql_test.customers drop primary key,drop index index_customers;

七．对索引的进一步说明 （１）索引在提高查询速度的同时，会降低更新表的速度。索引文件会占用磁盘空间。

创建下面的索引进行说明： create table people ( last_name varchar(50)　 not null, first_name varchar(50)　not null, dob date　not null, gender enum(‘m’, ‘f’)　 not null, key(last_name, first_name, dob) ); （２）复合索引的使用要严格遵循最左前缀法则。如你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。但你不能利用索引查找在某一天出生的人。 （３）存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为where last_name=”Smith” and first_name like’J%’ and dob=’1976-12-23’，则该查询只会使用索引中的前两列，因为like是范围查询。 （４）不能用“not in ”和“＜＞”操作索引。 （５）索引不会包含有空值的列，若有空值，则索引无效。因此在数据库设计时不要让字段的默认值为null。