优化流程
-
先排除缓冲sql nocache
1. SELECT SQL_NO_CACHE * FROM TABLE 2. SHOW VARIABLES LIKE 'query_cache_type'; SET SESSION query_cache_type = OFF;
-
预发跑sql explain
-
看一下行数对不对,不对可以用analyze table tableName矫正
-
添加索引 索引不一定是最优的 force index
-
存在回表的情况,覆盖索引避免回表,不用* 主键索引
-
联合索引 不能无限建 合理安排联合索引的位置
-
最左前缀原则 安装索引定义的字段写sql
-
索引下推合理减少索引位置
CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
上一段我们说到满足最左前缀原则的时候,最左前缀可以用于在索引中定位记录。这时,你可能要问,那些不符合最左前缀的部分,会怎么样呢?
我们还是以市民表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是10岁的所有男孩”。那么,SQL语句是这么写的:
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
你已经知道了前缀索引规则,所以这个语句在搜索索引树的时候,只能用 “张”,找到第一个满足条件的记录ID3。当然,这还不错,总比全表扫描要好。
然后呢?
当然是判断其他条件是否满足。
在MySQL 5.6之前,只能从ID3开始一个个回表。到主键索引上找出数据行,再对比字段值。
而MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
图3和图4,是这两个过程的执行流程图。
在图3和4这两个图里面,每一个虚线箭头表示回表一次。
图3中,在(name,age)索引里面我特意去掉了age的值,这个过程InnoDB并不会去看age的值,只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此,需要回表4次。
图4跟图3的区别是,InnoDB在(name,age)索引内部就判断了age是否等于10,对于不等于10的记录,直接判断并跳过。在我们的这个例子中,只需要对ID4、ID5这两条记录回表取数据判断,就只需要回表2次。