mysql数据库之索引index

mysql数据库之索引index

1. 什么是索引
   mysql数据库索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
   在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排好序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
   索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。
   索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
   索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

2. mysql索引种类/类型有哪些
   根据数据库的功能，可以在数据库设计器中创建4三种索引：
   主键索引、唯一索引、普通索引、全文索引、空间索引。
   主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
   唯一索引unique：加速查询 + 列值唯一（可以有null）
   普通索引normal：仅加速查询
   全文索引fulltext：对文本的内容进行分词，进行搜索
   空间索引spatial：空间索引。
   【单列索引：单个列上面建的索引
   联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于多个单列索引合并】

3. mysql索引方法有哪些
   mysql目前主要有右边几种索引方法：HASH,FULLTEXT，BTREE，RTREE。

   二叉树：已被放弃 。
   红黑树：虽然是平衡二叉树，但已被放弃 。
   HASH：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索 引，但效率不高。并且只支持等值查询，不支持范围查询。也被放弃了。
   FULLTEXT：全文索引，目前只有MyISAM存储引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
   BTREE：B树索引，就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引方法。
   RTREE:RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

4. 什么情况需要建立索引（索引是建立在数据库表中的某些列的上面的）
   在主键列上、唯一键列上、外键；
   在经常用在join连接的列上，这些列主要是一些外键，可以加快连接的速度；
   在经常需要搜索的列上，也就是where条件后的；
   在经常需要排序的列上，也就是order by后的；
   在经常需要分组的列上，也就是group by后的；
   在经常需要根据范围进行搜索的列上；

5. 什么情况不需要建立索引
   在查询中很少使用的列；
   在查询中参考的列不应该创建索引,(比如name名字)；
   对于那些只有很少数据值的列也不应该增加索引,(比如age年龄、sex性别)；
   当修改操作远远多于检索操作时，这个列不应该创建索引；
   对于那些定义为text, image和bit数据类型的列不应该增加索引；

6. 索引的优点缺点分别是
   优点：
   1.大大加快数据的检索速度;
   2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
   3.加速表和表之间的连接;
   4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
   缺点：
   1.索引需要占物理空间。
   2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

7. 什么情况索引会失效
   1.如果条件中有or，即使其中有条件带索引，只要有其他条件不是索引的会失效(这也是为什么尽量少用or的原因，解决办法是把or的所有条件都建一个索引)
   比如：select * from Student where id=‘XXX’ or name=’;其中id是索引，name不是索引，会失效。
   2.对于联合索引，不按照最左原则（不按照左前缀），索引会失效（少查不会失效，跳空查询会失效，顺序乱也会失效）
   3.like模糊查询是以%开头的，会失效
   比如：select * from Student where id like ‘%XXX’ ;其中id是索引，会失效。
   4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引。
   比如：select * from Student where id =1008; 其中id是索引，会失效。
   比如：select * from Student where id =‘1008’ ;其中id是索引，不会失效。

8. 创建索引语句
   单独创建索引：
   例如create UNIQUE INDEX 索引的名字 ON 表名(表的某一列列名)；
   例如create INDEX 索引的名字 ON 表名(表的某一列列名)；
   例如create index 索引的名字 on 表名(列名1,列名2);
   创建表的时候指定索引：
   例如CREATE TABLE tablename ( […], UNIQUE 索引的名字 (表的某一列列名) )；
   修改表：
   例如ALTER TABLE 表名 ADD UNIQUE 索引的名字 (表的某一列列名)；

9. 修改索引语句
   alter index 旧索引名字 rename to 新索引名

10. 删除索引语句
    drop index 索引名 on 表名
    alter table 表名 drop index 索引名;

11. 查询索引的语句
    select * from user_indexs；(当前用户下所有索引)
    select * from user_indexs where table_name=‘XXX’; (查询某个表的所有索引，表名要大写)
    select * from user_indexs where index_name=‘XXX’;(查询具体的索引，索引名要大写)

注意事项
考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。
建议单张表，包含唯一索引在内最好不要超过3个索引。