Understanding MySQL Slow Queries, Elasticsearch, and HBase: Causes and Practical Solutions

1. MySQL slow query causes – Most internet applications are read‑heavy, so query speed matters. Slow queries often stem from improper or missing indexes, index misuse, or optimizer choices. Common index‑related problems include using !=, LIKE '%pattern', functions on indexed columns (e.g., where length(a) = 6), implicit type/charset conversion, low‑cardinality columns (e.g., gender), and not matching the leftmost prefix of a composite index.

When an index is present but still slow, it may be ineffective; use EXPLAIN to diagnose. Remedies include index push‑down, covering indexes, prefix indexes, avoiding functions on indexed columns, and considering maintenance cost for write‑heavy tables.

1.1 Index failure reasons are listed and explained, followed by practical tips such as using composite indexes for multi‑condition queries and avoiding low‑selectivity columns.

1.2 MDL lock waiting – MySQL 5.5+ adds metadata locks; a statement holding a write MDL can block others. Check with show processlist for status Waiting for table metadata lock.

1.3 Flush waiting – Flush commands can be blocked by other statements; monitor with show processlist for Waiting for table flush.

1.4 Row lock waiting – Occurs when a transaction holds an uncommitted write lock.

1.5 Current read (repeatable read) – InnoDB uses undo logs to present a consistent snapshot for long‑running reads.

1.6 Large‑table handling – For tables with billions of rows, IO or CPU becomes bottlenecks. Solutions include sharding (horizontal or vertical) and read‑write splitting. Horizontal sharding distributes rows across many tables; vertical sharding separates columns into different databases. Tools: Sharding‑Sphere, TDDL, Mycat. Consider ID generation, non‑shard key queries, and scaling strategies.

2. Elasticsearch overview – ES is a Lucene‑based real‑time distributed search engine used for full‑text search, log analysis, and as a NoSQL JSON store. Typical usage pairs ES with Logstash and Kibana (ELK stack). Example search request:

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

ES stores data in indexes (formerly types) and relies on inverted indexes with a term dictionary and an in‑memory term index (FST) for fast lookup.

2.3 Why ES queries are fast – Inverted index and term index enable rapid term‑to‑document mapping, especially for tokenized (full‑text) searches. Exact term queries may be comparable to MySQL covered indexes.

2.4 When to use ES – Ideal for full‑text search, log analytics, and scenarios where MySQL LIKE '%...%' is inefficient. Chinese tokenization often requires the IK analyzer.

Example analyzer request:

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

Combining ES with MySQL (store searchable fields in ES, full records in MySQL) or with HBase for massive write‑heavy workloads is a common pattern.

3. HBase basics – HBase is a column‑family NoSQL store. Data is stored by column families rather than rows. Example relational table vs. HBase column‑family representation is shown. RowKey is the primary key and determines data locality; only three query types are supported: single‑row get, range scan, and full table scan.

HBase excels at write‑intensive workloads but is not suited for real‑time random reads or complex queries. It is typically used for OLTP‑type ingestion, while analytical workloads remain in dedicated OLAP systems.

4. Summary – The article enumerates common MySQL slow‑query causes and mitigation techniques, introduces Elasticsearch’s speed advantages and appropriate use cases, and outlines HBase’s storage model and scenarios. Choosing the right tool—optimizing indexes, sharding, read‑write splitting, or adding a search layer—depends on data volume, query patterns, and performance requirements.