目录
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。而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数。