数据库在接收到查询语句后会先判断是否命中缓存,如果命中则会直接返回缓存的数据结果。

查询缓存会保存最新数据结果,所有表数据的任何变化(INSERT、UPDATE、DELETE或其他可能产生数据数据变化的操作)都会刷新查询缓存。因此查询缓存特别适用于表数据变化不频繁,且有相同语句频繁查询的场景。推荐 99% 以上只读,很少更新的情况下可以考虑开启查询缓存。

具体了解可以参考:线上环境到底要不要开启query cache

目前常用的做法是在应用程序层面增加缓存。

当明确知道需要返回的记录的条数时,使用 LIMIT 限制查询数据的条数。当查询结果条数满足时,会提前终止查询返回结果。

例如:当我们知道某条 SQL 的返回结果只可能是一条记录时,就使用 LIMIT 1 。

只查询需要的列,有助于减少额外的 I/O、内存和 CPU 消耗,并且可能会利用覆盖索引返回数据,无须再回表查询。

当一条 SQL 影响的行数很大时,可以每次只操作一定量的行数,在应用程序层面循环处理至所有数据都已进行完操作。

例如:我们要删除 id 小于 的所有数据,可以使用如下写法:

DELETE FROM log WHERE id < LIMIT 10000;应用程序层面可以使用如下写法:

int deletedRows = 0;do { deletedRows = dao.deleteLogs();} while(deletedRows > 0);优点

减少锁的持有时间

减少 MySQL 的复制延迟

分散数据库的压力

将关联查询分解成几个单表查询语句执行,在应用程序中进行关联。

例如:查询一班所有学生的成绩

SELECT * FROM score JOIN student ON student.id = score.student_id WHERE class = '一班';就可以分解成两个单表查询

SELECT student_id WHERE class = '一班'; -- 假设返回结果为 1,2,3,4SELECT * FROM score WHERE student_id IN (1,2,3,4);优点

减少锁竞争

提升查询效率

提高代码复用率

对于 MIN() 和 MAX() 查询,可以在相应的字段加上索引,然后 MySQL 会使用最优方式 SELECT tables optimized away 通过索引直接一次定位到所需的数据行完成整个查询。

SELECT MAX(score) FROM math_score WHERE score >= 60;SELECT MAX(score) FROM math_score WHERE score >= 60 AND exam_id = 1;若 SELECT 的字段和查询条件中的字段均为同一个索引字段,则会使用最优方式;否则会使用索引。

MySQL 并不是跳过 offset 行,而是取 offset + N 行,然后放弃前 offset 行,返回 N 行数据。所以数据库库需要先扫描大量无用的行,导致执行效率降低。

SELECT * FROM person ORDER BY `name` LIMIT , 10;优化方案:

使用覆盖索引扫描

SELECT id FROM person ORDER BY `name` LIMIT , 10;SELECT * FROM person WHERE id in (...);先利用覆盖索引扫描,使得第一个查询只需要扫描 name 字段的索引,而不用去回表查询,大大减少了需要扫描的数据量;然后用第二个查询使用主键查询返回结果。

使用标记

SELECT * FROM person WHERE `name` > '赵一' ORDER BY `name` LIMIT 10;选用排序的字段为标记,会使用该字段索引。

除非确实需要服务器消除重复的行,否则一定要使用 UNION ALL 。如果没有 ALL 关键字,MySQL 会给临时表加上 DISTINCT 选项,这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。

当在 SQL 语句中连接多个表时,使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间,并减少相同列名引起的歧义语法错误。

避免使用大事务操作,以提高系统并发能力。

将与 SQL 无关的校验和参数组装等逻辑放在事务外进行,事务中尽量只进行 SQL 的执行。

idx_name_age_gender (name,age,gender) 就比 idx_gender_age_name (gender,age,name) 好很多。

当我们使用第一个联合索引进行查询时,会快速过滤掉大量数据,从而减少查询时间。

当我们建了一个联合索引 idx_name_age_gender (name,age,gender) 后,则其最左前缀字段的索引都不需要再建立,即:idx_name_age (name,age) 和 idx_name (name) 不需要再建立。

写 WHERE 条件时,要按照联合索引的顺序写条件,如果有跳过索引的一个字段,则其后的索引不生效。

联合索引的顺序是根据最左前缀匹配,与 WHERE 条件后字段的先后顺序无关。

不好的索引不但不会起作用,反而给数据库带来负担,插入、修改都会重新调整索引结构,存储成本增加,写效率降低,同时数据库系统也要消耗资源去维护。

能扩展原有索引,就不新建索引

能用单索引,就不用联合索引

索引尽量建立在小字段、区分度较高的字段上

尽量只给最频繁的语句建立索引

范围查询(<, > , BETWEEN , LIKE)之后的索引(联合索引中的顺序)无效。

age + 1 < 13 和 ADDDATE(date, 1) = '2019-02-28 18:00:00'这样的条件不会使用索引,可以将其转换等价的操作,如:age < 13 - 1 和 date = SUBDATE('2019-02-28 18:00:00', 1) ;也可以直接在内存中计算好再查询。

隐式转换会有额外开销。

SELECT * FROM person WHERE `name` = 17; --不走索引SELECT * FROM person WHERE `name` = '17' and age = '17'; --走索引字符字段与数字类型做比较判断不会走索引,而数字字段与字符类型做比较会走索引。

使用这些条件时,通常选取的结果集比较大,所以 MySQL 不会使用索引,而进行全部表扫描。

如果确定返回的结果集比较小,那么可以调整逻辑上等价的条件,比如将 != 替换成 > 或 < 等方式。

也可以尽量将联合索引中的这些字段往后放。

如果对字段进行 null 判断,则不会使用索引,而进行全部表扫描。

可以采用默认值的方式避免 null 字段产生。