Understanding MySQL Slow Queries, Index Optimization, and Related Technologies (Elasticsearch, HBase)

MySQL slow queries often stem from improper or missing indexes; the article reviews B+‑tree indexes, left‑most prefix rules, index push‑down, covering indexes, and common reasons for index failure such as using NOT/<>/OR, leading wildcards in LIKE, implicit type conversion, low cardinality columns, and not matching the leftmost prefix.

It then discusses why indexes may still be ineffective, describing how function calls on indexed columns, implicit conversions, and operations that break index ordering cause the optimizer to ignore them, and explains why low‑cardinality fields like gender should not be indexed.

Practical indexing tips are provided, including using index push‑down, covering indexes, prefix indexes, avoiding functions on indexed columns, and considering maintenance cost for frequently updated fields.

The article also covers MySQL concurrency issues: MDL locks, flush waits, row locks, and current‑read (repeatable‑read) behavior, as well as performance challenges on very large tables and solutions such as sharding (horizontal and vertical) and read‑write separation.

Sharding strategies are outlined, recommending vertical sharding for I/O bottlenecks and horizontal sharding for CPU bottlenecks, with tools like Sharding‑Sphere, TDDL, and Mycat, and noting challenges like unique ID generation, non‑partition‑key queries, and scaling.

Elasticsearch is introduced as a Lucene‑based near‑real‑time distributed search engine useful for full‑text search, log analysis, and JSON document storage; its index‑type hierarchy, mapping, settings, and the role of the in‑memory term index that makes queries fast are explained.

Example Elasticsearch query syntax is shown:

GET yourIndex/_search { "from" : 0, "size" : 10, "query" : { "match_phrase" : { "log" : "xxx" } } }

. The article also demonstrates how to analyze text with

POST yourindex/_analyze { "field":"yourfield", "text":"我可真是个机灵鬼" }

and discusses Chinese tokenization.

When to use Elasticsearch is covered, emphasizing its strength in full‑text and log search, while noting that precise term queries may not benefit as much.

HBase is then described as a column‑family NoSQL store with row‑key‑based access, contrasting its column‑oriented storage with relational row‑oriented tables, and explaining that it is suited for write‑heavy workloads rather than real‑time queries.

The article differentiates OLTP (transactional) and OLAP (analytical) workloads, stating that HBase is not an OLAP system and lacks transactions, but excels at high‑throughput writes.

Finally, the author concludes that software development should prioritize appropriate, incremental solutions over flashy new technologies, and that fixing bugs and proper indexing are the fastest ways to improve query performance.