mysql本身用错索引+给字符串字段加索引

mysql为什么有时会选错索引

场景例子：一张表里有a,b两个字段，并分别建立以下索引
```
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB；
```
- 表中数据从(1,1,1)到（100000，100000，100000）共10万行记录。
- 接下来分析一条sql语句：select * from t where a between 10000 and 20000;
- 做如下操作：
- 我们可以设置慢日志阈值为0，表示这个线程接下来的语句都会被记入慢查询日志中；set long_query_time=0;可以看到执行B时，走了全表扫描，没有走索引。
为什么没有按指定的a索引扫描呢？
- 而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。
  当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。我们这个简单的查询语句并没有涉及到临时表和排序，所以MySQL选错索引肯定是在判断扫描行数的时候出问题了。
- 基数：
  - mysql进行扫描前，并不知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。即索引区分度，一个索引上不同的值越多，这个区分度越好，而一个索引上不同的值的个数称为基数，基数越大，索引区分度越好。可以使用show index from 表名，查看结果。
- 优化器会估算回表的代价，同时评估主键索引扫描的没有额外代价，选择执行哪个扫描。
- 使用analyze table 表名命令，可以重新统计索引信息。纠正错误结果
另外一个语句：mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1
- 如果使用索引a进行查询，那么就是扫描索引a的前1000个值，然后取到对应的id，再到主键索引
  上去查出每一行，然后根据字段b来过滤。显然这样需要扫描1000行。
  如果使用索引b进行查询，那么就是扫描索引b的最后50001个值，与上面的执行过程相同，也是
  需要回到主键索引上取值再判断，所以需要扫描50001行。
- 可以看到，返回结果中key字段显示，这次优化器选择了索引b，而rows字段显示需要扫描的行
  数是50198。
  从这个结果中，你可以得到两个结论：
  1. 扫描行数的估计值依然不准确；
  2. 这个例子里MySQL又选错了索引。
- 原本可以执行得很快的SQL语句，执行速度却比你预期的慢很多，你应该怎么办呢？
  - 一种方法是，像我们第一个例子一样，采用force index强行选择一个索引。
  - 第二种方法就是，我们可以考虑修改语句，引导MySQL使用我们期望的索引。比如，在这个例子里，显然把“order by b limit 1” 改成“order by b,a limit 1” ，语义的逻辑是相同的。
    - 之前优化器选择使用索引b，是因为它认为使用索引b可以避免排序（b本身是索引，已经是有序的了，如果选择索引b的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。
      现在order by b,a 这种写法，要求按照b,a排序，就意味着使用这两个索引都需要排序。因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描1000行的索引a。
  - 第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。（删除索引b）

怎么给字符串字段加索引

场景例子：维护一个由邮箱构成的登录系统数据库
两种索引方式：
- alter table SUser add index index1(email);
  - 这一种包含了每个记录的整个字符串
- alter table SUser add index index2(email(6));
  - 对每个记录只取前6个字节
  - 优点：占用空间小
  - 增加额外的扫描次数
如果使用index1扫描顺序
- sql语句：select id,name,email from SUser where email='hjjxyz@xxx.com'
1. 先在index1索引树找到主键是ID2的行，判断email值是否正确，加入到结果集；
2. 到主键上查找主键值是ID2的行，判断email是否正确，加入到结果集
3. 在index1所引述刚刚查到的位置吓一跳记录，发现不满足条件，循环结束。
- 整个过程值回了一次表，可以认为只扫描了一行。
如果用index2顺序
1. 在index2索引树找到满足索引值是hjjxyz的记录，找到了ID1
2. 从主键上查找ID1的行，判断是否满足where条件，不是则丢弃
3. 在index2查到的位置继续查找下一条，仍然是hjjxyz，取出，再到ID索引树判断，满足where条件，加入到结果集
4. 重复上一步，直到index2不是hjjxyz，循环结束
- 在整个过程中，回表了多次，相当于扫描了多行
如果将index的长度变长，这样区分度更大，这样扫描的次数就减少了，但是占用了空间也变大了，那么如何控制好索引长度呢？
- 首先 select count(distinct email) as L from SUser;算出这个列有多少不同的值
- 查看4-7个字节的前缀索引，查出的个数
- ```
mysql> select
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;
```
- 如果可以得到95%以上的数据时就可以选取该长度
前缀索引一定要回表，无法使用覆盖索引
- 如果用index1可以直接返回结果，不需要回表，但是如果是index2即使字段很长，innodb还是要回表查询。
如何最大化使用前缀索引提高搜索性能？
- 比如身份证，前6位如果是一个区的完全相同，区分度不高
1. 倒叙存储 select field_list from t where id_card = reverse('身份证号码')
2. 使用hash字段，在表上创建一个新字段，来hash保存身份证号的值，重复概率很小，可以认为每次只扫描了一行
- 这两种方式都不支持范围扫描