MYSQL索引知识点梳理

        1.为什么要使用索引？

                通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

                可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。

                帮助服务器避免排序和临时表。

                将随机IO变为顺序IO

                可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

        2.索引这么多优点，为什么不对表中的每一个列创建一个索引呢？

                当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

                索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

                创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

        3.使用索引的注意事项？

                在经常需要搜索的列上，可以加快搜索的速度；

                在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

                在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

                对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引

                在经常用在连接的列上，这 些列主要是一些外键，可以加快连接的速度；

                避免 where 子句中对字段施加函数，这会造成无法命中索引。--哪些操作会引起索引失效

                在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。

        4.什么是覆盖索引

                如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

        5.选择索引和编写利用这些索引的查询的3个原则

                单行访问是很慢的。读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引，用以提升效率。

                按顺序访问范围数据是很快的，这有两个原因。第一，顺序 I/O 不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUPBY查询也无须再做排序和将行按组进行聚合计算了。

                索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就

                不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访

问是很慢的。

        6.最左前缀原则

                当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性，如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到

        7.注意避免冗余索引

                冗余索引指的是索引的功能相同，能够命中 就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

        8.1.添加PRIMARY KEY（主键索引）

                ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

                2.添加UNIQUE(唯一索引)

                ALTER TABLE `table_name` ADD UNIQUE ( `column` )

                3.添加INDEX(普通索引)

                ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

                4.添加FULLTEXT(全文索引)

                ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

                5.添加多列索引

                ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )