文章图片



文章图片



文章图片


系列文章三、MySQL强人“锁”难《死磕MySQL系列 三》
四、S 锁与 X 锁的爱恨情仇《死磕MySQL系列 四》
五、如何选择普通索引和唯一索引《死磕MySQL系列 五》
六、五分钟 ， 让你明白MySQL是怎么选择索引《死磕MySQL系列 六》

相信大多数小伙伴跟咔咔一样 ， 给字符串添加索引从未设置过长度 ， 今天就来聊聊如何正确的给字符串加索引 。
一、如何建立索引大多数系统都会存在用户表 ， 并且系统初始设计使用了手机号码登录的 。
这是产品提出了一个需求 ， 让系统也可以支持邮箱登录 。

肯定知道的是若不给邮箱字段添加索引执行查询是会全表扫描 。
此时你心里窃喜这还不简单 ， 给邮箱字段加个索引完事呗！但要做到复杂的需求做好 ， 简单的需求要最好 ， 减轻一切对系统的压力 。
此时的你拿起键盘就执行了alter table table_name add index idx_field (field)

有部分小伙伴不喜欢命令行创建索引 ， 喜欢使用phpmyadmin工具来操作MySQL ， 那么在建立索引时有没有发现后边可以设置大小呢？

通过上边给大家展示的图片知道字符串建立索引是可以定义长度的 ， 那么两者有什么区别 。
使用命令行alter table table_name add index idx_field (field)直接创建的索引默认是包含整个字符串 。
若这样执行就指定了索引前缀长度alter table table_name add index idx_field (field(6))
一图解千愁 ， 看一下建立的两个索引结构是什么样的 。
索引一结构图

索引二结构图

从图中可以看到 ， 指定了索引长度为6那么就只取邮箱字段的前6个字段 ， 相对索引包含整个字符串来说每个节点存储的数据会更多 。
索引那篇文章也给大家说了建立索引在合适的范围内越小越好 。
万物皆两面 ， 有坏就有好 ， 第六期文章误选索引的因素之一就是扫描行数 。
索引长度减少带来的影响就是索引基数变大 ， 从而增加额外的扫描记录数（执行explain的row字段） 。
此时要执行select idnameemail from mac_user where email='1397393964@qq.com';
给整个字符串添加索引执行流程
1、从email索引树找到满足1397393964@qq.com的记录 ， 得到主键ID为1
2、根据ID为1到主键索引树找到这条记录并判断email是否正确 ， 将这行记录假如结果集 。
3、重复第一步 ， 直到不满足查询条件 ， 循环结束 。
指定索引长度执行流程
1、从email索引树找到满足139739的记录 ， 得到主键ID为1
2、根据ID为1到主键索引树找到这条记录并判断email不正确 ， 丢弃这行记录 。
3、在email索引树找刚刚查询的下一条记录 ， 发现还是139739 ， 去除ID2 ， 再到ID的索引树进行判断 ， 当值对后加入结果集 。
4、再继续重复上一步 ， 直到不满足查询条件 ， 循环结束 。
结论
在模拟执行流程过程中很容易就发现 ， 使用前缀索引会导致读取数据的次数增加 ， 那是不是就代表使用前缀索引会增加查询代价呢？
肯定不是的 ， 试想此时定义的长度是6那么设置为7或者8呢！是不是会好很多 ， 图中的案例为了方便设置了三个一样的数据 ， 但实际情况基本不会出现这样的情况 。
建立索引关注的是区分度 ， 只有区分度越高 ， 重复值就越少 ， 查询效率就越高 。
所以使用前缀索引 ， 只要定义好长度 ， 就可以坐到既节省空间 ， 又不用额外增加太多的查询代价 。
二、创建索引如何确定使用多长的前缀MySQL中关键词distinct可以返回本列不同的结果集 。
例如查询email列有多少个不同的值select count(distinct email) as num from mac_user 。
如何计算列不同前缀有多少行
结合MySQL自带的函数left来实现 ， 例如select count(distinct left (email4)) as num4 from mac_user ， 截取email的前四个字符串计算有多少行 。
再用这个值去除总数得到的就是比例 ， 根据业务情况来判断多少比例可以 。
三、使用前缀索引的影响使用前缀索引会增加扫描行数 ， 同时也会使覆盖索引失效 。

稿源：(原来是咔咔)

【傻大方】网址：http://www.shadafang.com/c/110c525032021.html

标题：死磕mysql系列 七|字符串可以这样加索引，你知吗？《死磕MySQL系列 七》