MySQL索引怎么优化限于网页篇幅,部分结果未予显示

数据库索引优化关键在于两方面：

一是优化 SQL 语句写法，将那些无法应用索引或易引发大数据量检索的语句，修改为精准匹配表述。比如避免使用可能导致索引失效的语法结构，像以 % 开头的 like 关键字进行左模糊或全模糊查询，对字段进行空值判断，在条件查询 = 前对字段做运算或使用函数等情况都需谨慎对待。!=、!、notin、notlike、or 等操作也可能致使索引失效，必要时可采用其他替代语法，例如用 union all 替代 or。同时，SQL 应尽量写完整，避免使用缩写形式，如为字段取别名和内连表查询等操作都采用完整语法，以减少 MySQL 底层转换带来的性能损耗。另外，指定连接条件时，满足查询条件的小数据表可作为驱动表；未指定连接条件时，数据总行数少的表担当驱动表，且通常使用 in 做子查询时，要确保 in 的条件位于所有条件最后，以此最大程度降低多表查询的数据匹配量。在对索引结构充分熟悉的状况下，必要时可通过 force index 指定索引。

二是在合适的字段构建索引，保证常作为查询条件的字段能够命中索引来检索数据。同时要注意基于联合索引查询时要确保字段的顺序性，遵循索引最左前缀原则。

连接查询时不宜关联过多表，因为关联过多会使执行效率降低。多表查询时要遵循大驱小原则，即先用小数据集去驱动大的数据集，先查小表，再用小表结果在大表中检索数据。MySQL 的优化器在多表联查时采用 NestLoopJoin 算法，会依据驱动表结果集作为循环基础数据，用该结果集中每条数据作为过滤条件去下一个表查询数据，最后合并结果得到最终数据集。

应避免频繁创建与销毁临时表。临时表作为一种数据缓存，对于常用查询结果可建立临时表，后续查询可直接基于临时表获取数据。MySQL 默认在内存开辟临时表数据存放空间，走临时表查询速度比磁盘检索快很多倍。但仅针对经常查询的数据建立临时表，不可盲目无限制创建，否则频繁的创建与销毁会给 MySQL 造成较大负担。

尽量把大事务拆分成小事务执行。一个事务执行时，若包含写操作，会先获取锁再执行，直至事务结束 MySQL 才释放锁。事务较大时，部分数据锁定周期长，高并发情况下会有大量事务阻塞，最终可能拖垮整个 MySQL 系统。可通过 show status like 'innodb_log_waits' 查看是否有大事务因 redo_log_buffer 不足而等待写入日志。大事务还会导致日志写入阻塞，强制触发刷盘机制，大事务日志需阻塞到有足够空间才能继续写入缓冲区，这也可能引发线上阻塞。

从业务设计层面减少大量数据返回情况。一次性返回数据量过大，会引发网络阻塞、内存占用过高、资源开销过大等问题。若项目存在此类业务，要进行拆分，例如采用分批返回给客户端的方式，即经典的分页场景。通过分页思想可提升单次查询速度，避免大数据量带来的后续隐患。同时要避免深分页情况，如查询第 1w 页数据，先查询 10w 条数据再丢弃前 10w 条返回最后 10 条数据这种方式效率较低。若查询结果集存在递增且连续的字段，可基于有序字段先筛选再获取分页数据，如通过 between 进行优化，或者舍弃 limit 关键字实现分页。若搜索分页数据无序，业务允许时可限制深分页情况，如百度搜索结果显示大约一亿条数据，最大只显示 76 页并提示部分结果未予显示。若业务必须展现所有分页数据且不存在递增连续字段，要么采用慢的分页方式，要么每次随机十条数据给用户，若不想重复可对随机过的数据加标识。

客户端一些操作可批量化完成。如 xxDao.insertBatch (xxObjs) 对应的 SQL 为 insert into tb_xxx values (......),(......),(......),(......),.....，会组合成一条 SQL 发送给 MySQL 执行，能大幅节省网络资源开销，提升批量操作执行效率。修改场景同理，若有批量修改业务，不应使用 for 循环调用 update 语句接口，而应编写 update/replace 语句的批量修改接口。

明确仅返回一条数据的语句可使用 limit 1。如 select from zz_users where user_name = "竹子"; 和 select from zz_users where user_name = "竹子" limit 1;，后者多数情况下优于前者，加上 limit 1 关键字后，程序匹配到一条数据就会停止扫描，不加则会扫描所有数据。但在一些极端情况，如查询数据位于表 / 索引文件最后面，性能相差不大；基于主键 / 唯一索引字段查询时，由于字段值本身唯一，MySQL 匹配到第一个值就会自动停止扫描，所以此方案适用于普通索引字段或表中的普通字段。