关于mysql的索引等的一些基础知识

        虽然经常使用mysql，但是对于数据库的只是却一直不怎么了解，只是简单的知道些sql（内连接和外连接），压根不知道什么引擎，实在是太安逸了，对这些潮流技术都不了解。

存储引擎：

            mysql采用不同的技术将数据等信息存储在对应的文件或是内存等，而这些技术都是运用了不同的存储机制，索引方案，而这个所谓的技术的实现方法就是存储引擎。（就是如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法）

            mysql中常用的引擎有InnoDB和myIsam。

mysql的InnoDB和MyISAM引擎 

            1.MyISAM引擎：

        是mysql的默认引擎（ISAM:Indexed Sequential Acess Method有索引的顺序访问方法），但是它没有对数据库事务的支持，同时也不支持行级锁和外键。只支持表级锁，因此在用户执行大量的insert和update的时候，会对整个表不断的锁定，效率比较低下。但它存储了表的行数，因此调用select count(*)（无where条件情况下）的时候，不需全表扫描。同时在select的时候因为会对表进行锁定，故不需要担心存在重复读，幻读，脏读问题。  

              作用： 多用于读操作远远大于写操作的情况且不需要事务的支持，以及数据分析。

             数据库中存储：运用该索引创建表时，会创建3个文件（以表名开头）。

（注：索引组织表(index organized table, IOT)就是存储在一个索引结构中的表。堆表：存储在堆中的表是无组织的(也就是说，只要有可用的空间，数据可以放在任何地方)，IOT中的数据则按主键存储和排序。）

           （1）.FRM文件  用于储存表的定义

             （2）.MYD文件  用于存放数据

            （3）.MYI文件 用于存放索引

   

2.InnoDB引擎：

         提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别（分别为读未提交、读已提交、可重复读、串行化），支持外键，行锁。 支持行数表明了该引擎在大量insert和update的情况下有很大的优处。但同时由于没有存储表的行数，因此调用select count(*)的时候，会进行全表扫描。

         作用： 多用于事务处理以及数据量更改插入处理大的情况

         数据库中存储：InnoDB属于索引组织表  ，有共享表空间存储和多表空间存储。其中表结构的存储也是.FRM文件。共享表空间的情况下，所有表的数据和索引存储在一个表空间里，表空间可以有多个文件。如果使用多表空间，那么每个表都有一个表空间文件存储每个表的数据和索引（表名.ibd）。

 

 

3.索引问题

        InnoDB和MyISAM同时都使用了树做索引（B+Tree），其中MyISAM引擎的自动增长列必须为索引，如果是组合索引，自动增长列可以不是第一列。innodb引擎的自动增长列必须为索引，如果是组合索引页必须为组合索引的第一列。myisam允许没有任何索引和主键的表存在，myisam的索引都是保存行的地址。innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，innodb的数据是主索引的一部分，附加索引保存的是主索引的值。

        Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。

       MyISAM引擎的索引结构为B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。

   

4.什么情况下适合创建索引

1.where后面使用频繁的搜索列，但不一定是所有where 后面的列都要建索引；

2.索引要建在选择性高，重复记录少的字段上

3.索引尽量建在小的字段上，如果碰到大的字段(超过20字符)，考虑使用前缀索引

5.什么情况下适合创建索引

1.唯一性查的列，比如只有true和false的列

2.有%xx的查询条件，不要创建普通索引

3.不会出现在where条件下的列

 

6.什么情况下索引会失效（会进行全表查询）

1.进行null值判断（尽量用default值进行替代）

2.使用or条件连接一条非索引列

3.like中的%开始 如name like '%test'  

4.表达式或函数操作