Mysql索引怎么设置更加合理

MySQL是我们经常使用到的数据库，因为很多时候是免费的，所以用的比较多，我们在设计表的时候应该会使用到索引，所以我们一起来聊下索引应该怎么去设置

1、索引的定义

什么叫索引 ，索引是帮助Mysql高效获取数据的数据结构(有序)，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引，如图所示

索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上索引是数据库中用来提高性能的最常用工具。

索引的优势：1）类似于书籍中的目录索引，提高数据检索的效率，降低数据库中的IO成本 2）通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

索引的劣势：1）实际上索引也是一张表，该表中保存了主键和索引字段，并指向实体类的记录，所以索引列也是要占用空间的。

2）虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行insert、update、delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

2、存储引擎对各种索引类型的支持

平常所说的索引，若没有特别指明，都是指B+数(多路搜索树，并不一定是二又的)结构组织的所有。其中聚集索引、非聚集索引( 普通索引，前缀索引、唯一索引，全文索引)默认都是使用B+tree树索引，统称为索引。

3、Btree结构

Btree又叫多路平衡搜索树，一颗m又的Btree特性如下树中每个节点最多包含M个孩子除根节点和叶子节点外，每个节点至少有ceil(m/2)个孩子若根节点不是叶子节点，则至少有两个孩子。所有的叶子节点都在同一层。

每个非叶子节点有n个key与n+1个指针组成，其中[ceil(m/2)-1]<=n<=m-1。

以5叉Btree为例，key的数量:工时推导[ceil(m/2)-1]<n<=m-1。所以2<=n<n<=4。当n>4时，

中间节点分裂到父子节点，两边节点分裂。

插入CNGAHEKOMFWLTZDPRXYS数据为例。

到此，该Btree树就已经构建完成了，Btree树和二叉树相比，查询数据的效率更高，因为相同的数据量来说，Btree的层级结构比二叉树小，因此搜索速度快

B+tree为Btree的变种，B+tree与Btree的区别为:

1）n又B+tree最多包含n个key，而Btree最多包含n-1个key

2）B+tree的叶子节点保存所有的key信息，依key大小顺序排列

3）所有的非叶子节点都可以看做是key的索引部分。

由于B+tree只有叶子节点保存key信息，查询任何key都要从root走到叶子，所以B+tree的查询效率更加稳定

Mysql索引数据结构对经典的B+bree进行了优化，在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+bree，提高区间访问性能。

4、索引的种类

• 聚集索引
• 非聚集索引(辅助索引、二级索)

CREATE TABLEmerchandiseidint(11)NOT NULLserial no varchar(20) DEFAULT NULLname varchar(255)DEFAULT NULL.unit_price decimal(10, 2) DEFAULT NULLPRIMARY KEY Cid)USING BTREECHARACTER SET = utf8 COLLATE =utf8_general_ci ROW_FORMAT = Dynamic;

非聚集索引

MyISAM 使用的是辅助索引，索引中每一个叶子节点仅仅记录的是每行数据的物理地址，即行指针

聚集索引

Inndb主键索引是聚簇索引，其叶子节点则记录了主键值事务id、用于事务和MVCC的回流指针以及所有的剩余列，其他非主键索引是非聚集索引

5、MySQL的索引-设计原则

1. 对查询频次较高，且数据量比较大的表建立索引。
2. 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句的组合比较多，那么应当选择最常用、过滤效果最好的列的组合
3. 使用唯一索引，区分越高，使用索引的效率越高。
4. 索引可以有效提升查询数据库的效率，但是索引数量不是多多益善，索引越多，维护索引的代，价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会映入相当高的维护代价，境地DML操作的效率，增加相应操作的时间消耗。另外索引过多的话MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提交了选择的代价
5. 使用短索引，索引创建之后也是使用硬盘来存储，因此提升索引访问的I/0效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以以存储更多的索引值，相对应的可以有效的提升MySOL访问索引的I/O效率
6. 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SOL可以利用组合索引来提高查询效率