MySQL索引背后的数据结构及算法原理( 八 ) _MySQL索引

Index Selectivity = Cardinality / #T
显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。例如，上文用到的employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;+-------------+| Selectivity |+-------------+|0.0000 |+-------------+title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。
有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。
从图12可以看到employees表只有一个索引<emp_no>，那么如果我们想按名字搜索一个人，就只能全表扫描了：
EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table| type | possible_keys | key| key_len | ref| rows| Extra|+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+|1 | SIMPLE| employees | ALL| NULL| NULL | NULL| NULL | 300024 | Using where |+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：
SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;+-------------+| Selectivity |+-------------+|0.0042 |+-------------+SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;+-------------+| Selectivity |+-------------+|0.9313 |+-------------+<first_name>显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：
SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;+-------------+| Selectivity |+-------------+|0.7879 |+-------------+选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：
SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;+-------------+| Selectivity |+-------------+|0.9007 |+-------------+这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引建上：
ALTER TABLE employees.employeesADD INDEX `first_name_last_name4` (first_name, last_name(4));此时再执行一遍按名字查询，比较分析一下与建索引前的结果：
SHOW PROFILES;+----------+------------+---------------------------------------------------------------------------------+| Query_ID | Duration| Query|+----------+------------+---------------------------------------------------------------------------------+|87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' ||90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |+----------+------------+---------------------------------------------------------------------------------+性能的提升是显著的，查询速度提高了120多倍。
前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。
InnoDB的主键选择与插入优化在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。
经常看到有帖子或博客讨论主键选择问题，有人建议使用业务无关的自增主键，有人觉得没有必要，完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点，大多数论据都是业务层面的。如果从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。
上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

MySQL索引背后的数据结构及算法原理( 八 )

推荐阅读

第一讯点|传承中华国粹弘扬中医文化

沈翰之|年轻女子行刑前被迫临时结婚，实拍伊朗60年前神秘的女子监狱

【5G】4G升5G，变更后才知手机不支持？营业员一句“别录了”竟逃离现场

电科技|Note 20 Ultra实机照曝光用料十足边框更窄，三星

【金钥匙】机械育苗成为辣椒种植产业振兴的“金钥匙”

秦国的河西是现在的哪里秦魏河西之争

变化多端?变化多端的意思

河南最无奈的火车站，每天只有一趟普速火车，堪称最荒凉火车站

啥叫小规模食品经营者、煎饼摊还能摆吗？官方解释来了

“这群学生没白疼！”

5G手机卢伟冰你这是要加速5G普及啊

特力和乐这家店的商品咋样

远康军事：俄罗斯真是敢想敢干，技术和资金双重困难，万吨战舰今年就要开工

悠家娱乐 X3，小米10无奈下跌黯然神伤，支持六个万花筒小米POCO

唐僧师徒5人，其实在取经路上被佛祖偷偷换掉两人，这两人是谁

肖战|肖战在内娱荷尔蒙天花板票选中断层领先，为了角色不断自我突破！

泡脚好处多多，但泡时要注意方式，否则会给人带来危害

cos是什么意思

完美主义■32岁赵丽颖和38岁张娜拉撞脸

睿辰知道|双眼皮和美瞳线一起做，你的眼睛会放电！，听说