近来接触到一些mysql优化的例子，也看了网上相关的一些文章，这里通过自己的实践来总结下近段时间的经验吧。为了以后也有个记录方便查询，也希望对大家有所帮助。
在此之前先简单介绍下几个mysql索引术语：
1、聚集索引
所谓的聚集索引也就是表的主键构建的索引，比如一张表是这种结构：
其中id即为主键，也就是所说的聚集索引
2、辅助索引
也就是非主键索引，其实就是我们自己创建的索引，比如
即我们在code列创建单列索引
那为什么叫辅助索引呢？是这样的，我们都知道索引的叶子节点是来存放数据的，一般通过辅助索引查到的叶子节点存放的是键值+书签值，这个书签值其实就是我们的id，即主键值，然后mysql会再通过这个主键值来通过聚集索引再查到具体的数据。
3、覆盖索引
覆盖索引其实不是一个索引的命名，而是一种查询优化方式，就是说我们需要查询的数据直接在辅助索引可以查到不需要回表查询（回表查询就是需要再去聚集索引查询一遍数据）就是覆盖索引。
索引优化测试：
1、准备测试数据
存储过程插入数据，字符串随机
生成随机字符串函数
调用存储过程，先插入10条记录
2、简单的sql语句优化
code列无索引的情况，耗时(0.75秒)
现在我们在code列建立单列索引
创建code列索引
现在再看下
耗时比没有索引的时候降低了不少
explain看下是命中了索引
ok，那是不是只要我在这列建立一个索引就百分百没问题呢？我们再来看一个例子
这个是我建立的索引
这个是通过code来进行查询，可以看到并没有用到索引
这个是为什么呢？先来看一下表里边的数据
这一列的索引选择性很低
通过这个数据大家可以看到了吧。对于这种索引选择性低的情况，mysql会根据实际返回的数据做一些优化，因为mysql这时候认为sql通过索引不如直接全表扫效率高，那是不是因为这样呢？我这边测试下，让sql强制走索引。
加上force index
再来看下profiles。
虽然差距不多，使用索引之后还是比不使用索引慢
所以说通过这个案例我们可以知道对于索引区分性不高的建立索引是没有意义的。而且会存在潜在的问题。下边说下这个问题，我这边再往数据库中加入1000000条记录。但是code列还都是'#qwqw'。
这个sql又开始使用索引了
我们看下使用索引和不使用索引的sql耗时
上边是使用索引的情况，下边是不使用索引的情况
我们发现使用索引比不使用索引查询的还慢，这个我理解是因为sql面对数据量大的情况如果有索引会优先走索引，所以我们对于选择性不高的列的情况下，最好不要在这一列建单独的索引，当然可以看情况和其他列建立组合索引，索引选择性可以通过show index from table查看。
Index Selectivity = Cardinality / #T
这个就是索引基数
所以建立索引不是只在这一列建一个索引就可以了，要建立适合的索引，要根据业务需求建立合适的索引类型，今天先写这个案例，之后会再详细补充下其他的case。
to be continue....
网友评论