mysql 索引

一, mysql索引的概念

　　01, 索引:

　　　　　　在mysql的列类型都可以添加索引, 在关系型数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容, 简单来说使用使用索引提高select操作性能的最佳途径, 根据这个存储引擎可以定义每个表的最大索引数,和最大索引长度.   MyISAM和InnoDB存储的引擎他默认是BTREE索引

　　02,索引的算法

　　　　　　二叉树索引,时间复杂度为O(lgN)

　　　　　　哈希表,时间复杂度O(1)

 

　　　　　　这两个索引都有自己使用的范围. HASH索引有一些重要的特性需要在使用的时候特别注意

• 　　只用于使用=或者<=>操作符的等式比较
•        优化器不能使用HASH索引来加速ORDER BY 操作
•        mysql 不能确定在两个值之间大概有多少行. 如果将一个myISAM表改为HASH索引的MEMORY表,会影响一些查询的执行效率.
•         只能使用整个关键字来搜索一行

　　　　　　BREE索引,使用>,<,>=,<=,BETWEEN,!=或者<>,或者LIKE'pattern'(其中'patter'不以通配符开始) 操作符时,都可以使用祥光列上的索引

　   03,索引的原则

• 　搜索的索引列,不一定是所要选择的列, 最适合的索引的列是出现在where子句的列, 或者连接自居中指定的列,而不是select关键字后选择列表中的列
• 　使用唯一的索引
• 　使用短索引
• 　不能过度使用索引
• 　利用最左前缀, 创建一个n列的索引时, 实际是创建了mysql可利用的n个索引. 多列索引可起几个索引的作用,因为可以利用索引中最左边的列集来匹配行,这样的列集称为最左前缀
• 　索引条件列(where后面最频繁的条件较适合索引)
• 　索引散列值, 过于集中的值不需要索引

　　04,索引缺点

　　　　　　降低了增删该查的速度(update/delete/insert)

　　　　　　增大了表的文件大小(索引文件可能超过数据)

　　05,索引分类

　　　　　　普通索引(index)：仅仅是加快查询速度

　　　　　　唯一索引(unique index)：行上的值不能重复

　　　　　　主键索引(primary key)：主键不能重复，主键索引必唯一，但是唯一索引不一定是主键

　　　　　　全文索引(fulltext index)：检索速度快，多服务器负荷降低

　　06,创建索引的语法

CREATE UNIQUE|FULLTEXT|SPATIAL|INDEX index_name
   USING index_type
   ON table_name(index_col_name,...)
   
 index_col_name:
    col_name[(LENGTH)][ASC | DESC]

 　　　　执行结果:

　　　　　　内容为提前创建号的

　　　　　　　　　　　

CREATE INDEX hellword ON salary(salary)

alter table 表名 add index /unique/fulltext等  (索引名);
索引名可以不加

alter table 表名 add primary key(列名);         //不要加索引名，因为主键只有一个

　  07,索引的查看

　　　　　　show index from  表名

　　　　　　show index from member \G

　　　　　　EXPLAIN SELECT * FROM salary WHERE salary='1000'

 

 　　　07,索引的删除

　　　　　　　语法:

DROP INDEX index_name ON tab1_name
　　　

alter table 表名 drop index 索引名;    //删除普通索引
alter table 表名 drop primary key;     //删除主键索引　

全文索引的用法：

match(全文索引名) against('keyword');

 

关于全文索引的停止词：

全文索引不针对非常频繁的词做索引

如：this,is,you,my等等