[MySQL]Mysql自己怎么选取索引，怎么给字符串字段加上索引？(涉及到身份证怎么加索引)

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编码:一个英文字符等于一个字节,一个中文(含繁体)等于三个字节。中文标点占三个字节,英文标点占一个字节