MySQL听讲（六）——查询

2019-02-16 本文已影响0人靈08_1024

说到查询，首先想到了的是索引。本节基于MySQL听讲（三）——索引的基础来对查询进行展开。

mysql索引选择策略

首先，我们必须了解到，mysql在选择索引时，优化器会从以下角度来考虑是否选择索引：

使用force index(a-name)会强制使用a-name的索引（强制）；
根据统计条件对应的行数（行数越少越好，占用的CPU资源就少）；
根据是否排序，及其排序要花费的时间；
回表的代价。

下面来逐条讲解一下每条需要注意的情况：
除了第一条是强制执行某索引，其他条都是综合考虑，通过估值来确定是否选择索引以及选择哪个索引的问题。

第二条，mysql在选择行数时，会采样统计[1]，根据统计信息估算在查询条件的范围内大概有多少条记录。而这个时候就需要依靠于索引的区分度，一个索引上不同的值越多，其区分度就越好。
所以在建立索引（或者前缀索引）时，既要考虑索引的占用页数大小，也要考虑索引的使用效能，即区分度。

第三条，因为索引都是排好序的，所以有索引的可以直接忽略掉这部分花费的时间的考虑。如果一个查询语句中，order by和where中都有的字段，会更偏向于该字段的索引，尽管此时where中有别的索引有更优的效果。
ps：
[1] 采样统计：因为整表统计代价太大，所以选择采样统计。而采样统计会默认选择N个数据页，统计这些页上不同值，得到一个均值，再乘以这个索引的页数，就得到了这个索引的基数。而当数据库变更数据超过1/M时，会自动重新做一次索引统计。参数为innodb_stats_persistent，为on表示统计信息会持久化，此时默认N=20，M=10；为off时表示统计信息只存储于内存中，此时默认N=8，M=16。可以执行ANALYZE TABLE t;手动校正。

示例一
有索引index(a)，index(b)，在where (a between 1 and 1000) and (b between 10000 and 20000) order by b limit 1中，索引会选取b，而此时a才是更好的索引方案。所以可以修改为order by b,a limit 1。这种修改要确保想拿的数据还是原来的那条数据。
而如果没有limit 1,则b才是最好的方案。
因为索引都是排好序的，在a中选择1000条数据后，在b中从20000开始找一条好了。

示例二
针对上面的情况，还可以执行强制索引。如上面的语句可以写为from t force(index(a)) where (a between 1 and 1000) and (b between 10000 and 20000) order by b limit 1。

索引优化

MySQL是根据数据采样来选择使用哪个索引的。执行show index from test3;，出现下面的界面：

image.png
上面红框标注的字段Cardinality，表示索引的取样，即该索引有多少种不同的值。如果差距过大，则索引失效。可以使用ANALYZE local TABLE test3;来修复(local为避免写入bin log)。

count

关于count(*)，MyISAM将表的总行数存在磁盘上了，而InnoDB需要逐行统计。
InnoDB不放在磁盘上，与其事务有关。读的时候会生成一致性视图。

而count(1)效率大于count(id)，count(1)是遍历整张表，但不取值，对于返回的每一行放一个数字“1”进去，判断不为空就累加。而count(id)是统计主键索引，取出id后判断不为空就累加。

所以，结论是count(*)≈count(1)>count(id)。

多表关联

多表关联分为以下几种情况：
下面的分析为两表连接，具体的多表连接，MySQL会根据整体情况进行考量。

在使用LEFT JOIN，RIGHT JOIN，且没有条件时：
驱动表一般跟随方向走。即left的驱动表在左边，right的驱动表在右边。无论关联字段是否有索引。
其他的JOIN和情况：
在有条件时：

若有前表的条件，则驱动表为前表；反之，则为后表。
若前后表的条件都有，则查看下面红框中的值cardinality，谁的关联字段的值大，驱动表就是哪一个。
image.png
一般情况，都是小表驱动大表（N+λN*logM，N为小表扫描行数，M为大表总行数，λ为大表扫描索引次数）。

索引失效的情况

对索引字段使用函数；
索引里有null；
对索引使用表达式[0]；
隐式类型转换[1]；
隐式字符集转换[2]；
日期类型不能使用in+字符串[3]；
对索引使用like'%xxx'或者like'%xxx%'；
对联合索引跨列使用；
对联合索引没有依照最左原则使用。

ps：[0]：如id +1=10，要表达成id=9，否则索引失效。
[1]：MySQL会对字段类型进行转换，但也只是将字符串转成数字。而转换的一方很有可能是索引字段的一方，所以此时索引就失效了。日期和字符串除外。
[2]：MySQL在多表关联时会对字符集进行转换，一般会转换为其父字符集。如utf8转换为utfmb4。
[3]：针对于日期和字符串类型的比较运算，字符串会强制转换为日期，但是in里面如果是字符串，则不会使用索引。

慢查询

指查询超过指定参数long_query_time对应的时间的查询。该值在my.cnf中有，也可以手动设值，set long_query_time=0，表示超过0秒的查询记为慢查询。