目录
2.4.3 如果表的统计数据不准确，使用analyze table t
4.5 使用hash来处理身份证（使用crc32来进行操作）
前言：
Mysql一张表有多个索引，但是如果我们sql语句不强制使用哪个索引。
那么mysql 就会自己去选取。
但是有的时候，为什么执行很快的语句，mysql却执行的很慢？
下面是学习Mysql实战45讲的笔记。
1. 实例分析
下面是Mysql实战45讲中的实例内容
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=InnoD
然后插入10w的数据(1,1,1) (2,2,2)...(10w,10w,10w)这样。
1.1 如何创建循环函数
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();这个语句中我看的有点迷糊哈。
delimiter 作用是改变结束的字符。
比如，
DELIMITER $$，那么
DROP TRIGGER IF EXISTS `updateegopriceondelete`$$
这个符号表示sql语句这句话已经结束执行。
在上面代码这样就比较清晰了，;;代表着语句结束。
最后还需要改回定义";", MYSQL的默认结束符为";"
其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。
因为循环到end;; 这里才写完。
1.2 进行查询操作
select * from t where a between 10000 and 20000;
因为a上面有索引，所以用到了索引a。
之后模拟情况：
B事务的话，把数据全删了，在执行idata().
这时候，session B 的查询语句 select * from t where a between 10000 and 20000 就不会再选择索引 a 了.
为什么这时候的B就不走索引了？
如果使用force index(a)可以强制使用索引 速度会加快。
知识点：
set long_query_time=0; //这个语句会让下面的语句都会被记录到慢查询日志里面
select * from t where a between 10000 and 20000; /*Q1*/ //session b的查询 ，这个的结果是进行了全表扫描
select * from t force index(a) where a between 10000 and 20000;/*Q2*/ //强制使用索引
结论：mysql选错了索引。用了更长的执行时间。
场景：因为我们不停的删除历史数据和新增数据。
2. 索引选择
2.1 优化器
选择索引是优化器的工作。优化器是找到一个最优执行方案。
在数据库中，扫描行数是最影响速度的因素之一。扫描行数越少，消耗的CPU资源也就越少。
扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
2.2 扫描行数是怎么判断的？
MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。
统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。
总之 索引是有区分度的~
可以使用 show index 方法，看到一个索引的基数。
应该是cardinality，英文翻译也是基数。
并不是很准确。但是这三个字段其实是一样的。
2.3 Mysql怎么得到索引的基数的呢？
因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择采样统计。
有点像统计学的抽样，采用统计的时候，InnoDB默认会选择N个数据页，统计这些页面的不同值，得到一个平均值，
然后乘上这个索引的页面数，就得到了索引的基数。
而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。
在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：设置为 on 的时候，表示统计信息会持久化存储。默认的 N 是 20，M 是 10。设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。
2.4 选错索引的原因？
有的时候不使用索引的原因是优化器判断的结果。
2.4.1 怎么看预计扫描的行数？
这个row就是预计扫描的行数。
自己试一下。
还是有一定的差距的，但是我没明白为什么count(*)不用主键的key来进行索引？
count(*)不是全表扫描吗？之后研究一下
2.4.2 为什么选错了索引？
如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。
而如果选择扫描 10 万行，是直接在主键索引上扫描的，没有额外的代价。
优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。
但是为什么发生了这个问题，还是因为MYSQL扫描行数错误了，本来应该10000条数据，竟然认为扫描行数10W？？？
所以可以说：优化器会看回表的代价和直接扫描的代价。（这个是根据扫描行数也有关）
2.4.3 如果表的统计数据不准确，使用analyze table t
重新统计索引信息之后，执行便正确了。
在实践中，如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用这个方法来处理。
3. 选错索引，处理方式
3.1 使用force index 强制使用索引。
MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行。如果 force index 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。
但是force不优雅，而且索引改了名字可能会出现错误
而且线上爆出的问题，如果修改这个SQL，加上force index 还要测试和筏板，不够迅速。
3.2 在数据库内部修改解决
有时候通过SQL语句来诱导数据库使用那个索引。
在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
4. 字符串加索引(使用前缀索引)
场景比如邮箱上面需要邮箱登录，邮箱就需要加上索引。
mysql是支持前缀索引的，所以可以定义字符串的一部分作为索引。
所以创建字符串索引的时候，如果不指定索引的长度，那么就会包含整个字符串。
比如把邮箱的前六位作为索引
alter table SUser add index index2(email(6));
如果这样就是全部作为索引：alter table SUser add index index1(email);
如果取前六位作为索引，占用空间会很小，这就是使用前缀索引的优势。
但是使用前缀索引的缺点是会增加额外的记录扫描次数。
select * from user where email = 'hehehelaozhu@xxx.com';
使用整个字符串作为索引：
先从索引树找到等于这个邮箱的记录，然后取出主键值然后去主键的树里面拿这个记录，进行比较是否满足条件，
加入到结果集里面。
然后找下一个记录，发现如果不满足，循环结束。
只需要回表取一次数据，那么这时候系统认为扫描了一行。
如果使用前缀索引：
找到一个值就回表去判断一下是不是符合的，符合加入结果集。
重复到前6个不是的时候停止循环。
使用前缀索引后，可能会导致查询语句读数据的次数变多。
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。(实战)
4.1 怎么确定前缀索引要多少位？
区分度越好则重复键值越好，那么也就可以说越准确。
select count(distinct email) as L from user;
这样可以算出列上面有多少不同的值
比如：
可以根据这个查看区分度。
4.2 前缀索引对覆盖索引的影响？
因为前缀索引不全，所以覆盖索引并不能用到，得回表去判断。
所以是否使用前缀索引最重要的因素是看用没用到覆盖索引
PS: 突然有个问题，如果我这个字段是varchar(70)，但是实际存的都只有5个长度的字符，那么给这个列加上索引，实际占用的是多少呢？
搜集资料的结果：
MySQL建立索引时如果没有限制索引的大小，索引长度会默认采用的该字段的长度，也就是说varchar(100)建立的索引存储大小要比varchar(10)建立索引存储大小大的多，加载索引使用的内存也更多。
4.3 如果索引值大影响什么？
如果索引值太大，那么一次取出来的数据页也就能放下的索引也就越少，那么搜索的效率就会很低。
4.4 倒叙存储？（这个可以结合项目来谈）
比如身份证号类，如果使用前缀索引，那么区分度会很低。
正好结合我们的项目，进行身份证核验的需要身份证号。
表里海量数据。
那么如果倒叙存储一个列的话，那么就会可以增加一定的区分度，
select field_list from t where id_card = reverse('input_id_card_string'）
实践中使用 count(distinct) 方法进行区分。
4.5 使用hash来处理身份证（使用crc32来进行操作）
在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
alter table t add id_card_crc int unsigned, add index(id_card_crc);
因为整数索引会很快，所以建立整型会好很多，
然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。
mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
如何将一个字符型的数据，唯一性地转为数值型呢？
一般我们会采用CRC32函数进行这个转换，CRC32会把字符串，生成一个long长整形的唯一性ID（虽然科学证明不绝对唯一，但是还是可用的）。
因为并不一定一定唯一，所以还需哟啊用全部数值来校验一下。
PS:
int 为4字节，那么比字符串就小了很多。
- 最小值是 -2,147,483,648（-2^31）；
- 最大值是 2,147,483,647（2^31 - 1）；
- 相当于10位，但是数据库中还有一位存放符号。
4.6 varchar（m） 与字节数的关系？
汉字和字母在MYSQL里长度是不一样的。
一个汉字占多少长度与编码有关：
UTF－8：一个汉字＝3个字节
GBK：一个汉字＝2个字节
经过试验，varchar(1) 可以存放一个英文，也可以存一个中文字符。
但是他们占用的字节数是不一样的。
UTF-8编码:一个英文字符等于一个字节,一个中文(含繁体)等于三个字节。中文标点占三个字节,英文标点占一个字节
4.7 倒叙存储和hash字段存储处理身份证
都不支持范围查询。
只能等值。
不过真的需要查询某一个市的这种操作，我觉得可以用hash + 给身份证的前缀索引
这样就完美解决了哈~
我更倾向于使用hash~
在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。
4680
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
