MySQL(六):MySQL常见的问题

大纲内容

• 一条order by语句是如何执行的?
• 如何准确的统计总条数?
• 如何解决查询慢问题?
• 常见的优化手段

---

一条order by语句是如何执行的?

MySQL内部支持两种方式排序，filesort和index，走了index说明是走了内存排序，走了filesort说明是文件排序。

一般我们用explain执行计划来计算extra字段

分析核心字段

type：优先级，当前存在system > const > eq_ref > ref > range > index > all，一般all代表走了全表扫描，我们要保证type位于range以上，最好达到ref级别。

常见的级别： system: 只有一条语句查询时，比如主键索引, where id=1。 ref：普通索引查询。
range：使用索引的范围查询。 index：未遵循最左前缀原则，扫描全索引树。 all：未走索引，全表扫描了。

system: 只有一条语句查询时，比如主键索引, where id=1。

ref：普通索引查询。

range：使用索引的范围查询。

index：未遵循最左前缀原则，扫描全索引树。

all：未走索引，全表扫描了。

extra：Using index, Using where, Using index condition, Using temporary,Using filesort, Select tables optimized away，本文讲述的就是using filesort。

key_len：这一列显示mysql在索引里使用的字节数, 通过这个值可以计算使用了索引中的哪些列。

字符串 如果是char,则长度就是3n个字节, 如果是varcahr,则长度就是3n+2个字节。 数值类型 tinyint: 1个字节
smallint: 2个字节 int: 4个字节 bigint: 8个字节 时间类型 date: 3个字节 timestamp: 4个字节
datetime: 8个字节 如果字段允许为null,则一个null等于1个字节 索引最大长度是768字节,
当字符串过长时,mysql会做一个类似于最左前缀索引的原理,将前半部分的字符提取出来做索引。

#文件排序(using filesort)：拓展知识，我们实际开发应该避免文件排序

文件排序：如果排序的数据量大于sort_buffer_size的参数值时，会走文件排序，在磁盘中完成排序，相当于内存排序而言，耗时(使用归并算法，会拆分很多小文件，最终汇总一个大文件)。
排序可能在内存中，也可能在磁盘中，合理设置sort_buffer_size，如果排序的量小于sort_buffer_size，则在内存中排序。如果大于，则在磁盘中排序。
文件排序存在两种，假设city加了普通索引。

全字段排序

select city,name,age from t where city='杭州' order by name limit 1000 ;

MySQL会给每个线程分配一个内存，用来排序，内存中称之为sort_buffer。
1：初始化sort_buffer空间，确定放入city，name，age三个字段
2：从二级索引树中找到满足city=杭州的值，取对于的主键id，一条一条回表查找值，复杂度为(n)
3：依次将name，city，age三个字段的值都放进内存中。
4：对name字段进行排序，默认是升序，按照排序结果返回前1000条数据给前端。

是把所有符合city=杭州的主键id，回表，可能name的数量大于1000, 最终是在sort_buffer中返回排序好的前1000条数据。

一句话：先根据select的字段，初始化sort_buffer, 然后依次回表, 填充sort_buffer，最终排序。

rowId排序

select city,name,age from t where city='杭州' order by name limit 1000 ;

1：初始化sort_buffer，确定放入name,id两个字段

2：从二级索引树中找到满足city="杭州"的所有值，取对于的id，依次回表，把name和id放进sort_buffer中。

3：按照name字段排序。

4：然后按照id的值，查找city，name，age字段返回给客户端，又回表了。

4中的回表目的是为了找到city和age两个字段的值，返回给客户端。

可以理解为改: select id，name from t where city=“杭州” order by name limit 1000

如果内存太小，可以使用rowId，利用时间换空间的思想。如果内存足够，可以利用全字段排序，减少回表的次数。

如何避免文件排序?

答：使用联合索引+覆盖索引+遵循最左前缀原则的方式，案例如下

select city,name,age from t where city='杭州' order by name limit 1000 ;

假设联合索引(city, name)，因为索引是排好序的数据结构，当city=杭州时，联合索引还存在name字段，在city相同的情况下，name对于的顺序也是相同的。

1：从索引(city，name)中找到第一个满足city的主键id

2：通过主键id回表，将name，city，age的值放入结果集合中

3：重复1，2操作，一直查询第1000条记录或(不满1000条并且不等于杭州)时，循环结束。

order by使用索引最左原则，是可以借用覆盖索引的原理。此案例回表是为了查找age的字段，如果age也在这个联合索引(city，name，age)中，压根就不需要回表。

要注意：优化器不支持索引最左原则的顺序打乱，

使用where子句与order by子句条件列组合遵循索引最左原则, 但是order by不支持顺序互调 。比如联合索引为(name，city)，优化器无法帮忙纠正order by的顺序问题。

如何精准的统计总条数count(*)

count(*)=count(1)>count(id)>count(字段)

count(*)：一行一行的数据统计，如果存在二级索引，MySQL优化器会优先去二级索引树上统计。

count(1)：InnoDB遍历整张表，但不取值，直接返回数据页，每一行数据标识为1，同时判断非空，然后累加统计。

个人理解：count()跟count(1)差别不大，唯一的好处是count()如果存在普通索引，他是可以去普通索引上面统计的。

count(id)：把每一行的id值都解析出来，返回给server层，server层拿到了id后，判断不为空，则累加统计, 除了id值，其他值都不要，中间有解析的过程

count(字段)：把每一行的字段值都解析出来，返回给server层，server层拿到了字段后，判断不为空，则累加统计, 除了字段值，其他值都不要，中间有解析的过程。

以后看书再找原理吧，感觉有点模糊，先记住结论吧。

如何发现查询慢问题?

1：判断是否是锁导致的

答：通过show processlist命令，查看是否产生阻塞，是否产生死锁。如果是表锁，则读读不互斥，读写互斥，如果是MDL元数据锁，手动kill掉持有MDL写锁的线程，或者设置MDL锁的等待时间。如果是行锁，则kill掉持有行锁的线程，如果是死锁，则用死锁检测，死锁超时等待时间，业务避免死锁产生。

总之：大部分都是kill掉导致阻塞的线程即可，一般我们要在业务层避免造成锁导致，锁都是针对于索引对象加的锁！

2：判断是否CPU打满

一般是锁导致的。

3：判断是否全表扫描(常见)

使用explain来查看，加索引可以解决全表扫描问题。

4：判断是否在进行redolog的flush操作

那这个没办法，只能等着，可以把redo log日志内存升级一下，一般为4个G

常见的优化手段

• 在列上做任何操作，尽量要加索引，防止行锁升级为表锁，同时提高查询效率
• 尽量使用覆盖索引(查询结果包含索引列)，减少select *，避免回表次数
• 复杂的模糊查询可以尝试使用ES
• 少用or或in，MySQL不一定使用到索引，用不用优化器说了算
• 范围查询优化，单次数据量查询过大，会导致不走索引，要加上limit限制
• select * from user limit 10000，10

含义：从user表中取第100001行开始的后10条数据，看似只取了10条数据，实际上是把前10000条都丢弃了，取了后10条，如果要查询很大一张表，执行效率是很低的，也会带来长事务的超时风险。
假设id=0，select * from t where id>#{id} limit 500，id是主键索引，排好序的。
或者倒序，取最大id值+1，select * from t where id<#{id} order by id desc limit
500。 这样做的好处也可以防止OOM

• 不要基数小的字段上加索引，维护索引也有成本

• 长字符串可以尝试使用前缀索引

• order by排序时，尽量使用覆盖索引+联合索引+遵循索引最左原则。