如何优化含特殊字符的MySQL查询？附数据及系统信息

Hey there! Let’s tackle your MySQL query performance issue when dealing with special characters like ., -, /, ', and commas—especially given your 12 million-row, 3GB dataset. Here are practical, actionable steps tailored to your setup:

• Ditch leading wildcards in LIKE queries: If you’re running something like WHERE column LIKE '%text-with-dashes%', MySQL can’t leverage indexes here. Instead, use trailing wildcards (LIKE 'text%') if your use case allows, or switch to full-text search for mid-string matches. Full-text indexes are way more efficient for large datasets than LIKE with leading wildcards.
• Pick the right index type: For string columns with special characters, regular B-tree indexes work great as long as your queries don’t force full scans. If you need frequent substring searches, add a full-text index with:

  ALTER TABLE your_table ADD FULLTEXT INDEX idx_fulltext_col (your_column);
  

  Then query with MATCH() AGAINST() instead of LIKE:

  SELECT * FROM your_table WHERE MATCH(your_column) AGAINST('search-term-with-special-chars');
  

• Check index selectivity: Ensure your indexed columns have enough unique values. If a column has low selectivity (e.g., most rows share the same value), MySQL might skip the index and do a full table scan anyway—so focus indexing on columns that narrow down results quickly.

• Store normalized versions alongside formatted data: If special characters are just for readability (like phone numbers with dashes or emails with dots), add a normalized column that strips out these characters. For example:

  CREATE TABLE your_table (
      id INT PRIMARY KEY AUTO_INCREMENT,
      original_value VARCHAR(255),
      normalized_value VARCHAR(255) -- no dots, dashes, commas, etc.
  );
  

  Then query the normalized column for exact matches, which are index-friendly and fast:

  SELECT * FROM your_table WHERE normalized_value = 'stripped-version-of-search-term';
  

• Fix collation mismatches: Since your system uses Turkish locale, make sure your table/column collation is set to something like utf8mb4_tr_ci. Mismatched collations force MySQL to convert values on the fly, which kills index performance. Double-check with:

  SHOW TABLE STATUS LIKE 'your_table';
  

• Avoid wrapping columns in string functions: If you’re doing WHERE REPLACE(column, '-', '') = 'value', MySQL can’t use the index on column. Instead, apply the transformation to your search value:

  -- Bad: Full table scan inevitable
  SELECT * FROM your_table WHERE REPLACE(column, '-', '') = '123-456';
  -- Good: Uses index on column (if exists)
  SELECT * FROM your_table WHERE column = REPLACE('123-456', '-', '');
  

• Use parameterized queries: Not only do they prevent SQL injection, but MySQL can cache the query plan for repeated executions, which speeds things up. Plus, you won’t have to manually escape single quotes like '' every time.

• Boost InnoDB buffer pool size: Assuming you’re using InnoDB (the default for most modern MySQL setups), set innodb_buffer_pool_size to 8-10GB (you have 16GB RAM, so leave enough for the OS and other processes). This lets MySQL cache most of your data and indexes in memory, cutting down on slow disk I/O.
• Enable query caching (if you’re on an older MySQL version): Since your system is from 2018, you’re probably using MySQL 5.x where query caching is still supported. Set query_cache_size to a reasonable value (like 256MB) and query_cache_type = ON to cache repeated identical queries.
• Adjust sort/join buffers: If your queries involve sorting or joining large datasets, bump up sort_buffer_size and join_buffer_size (start with 2MB each—don’t go too high, since these are per-connection).

• Over time, deletes and updates can fragment your table, making indexes less efficient. Run OPTIMIZE TABLE your_table; during off-peak hours to defragment it and reclaim space. Note that this locks the table, so schedule it when traffic is low.

• If your dataset keeps growing and queries are still slow, partition your table by a logical key (like date, if your data has a time component). Partitioning lets MySQL scan only the relevant partitions instead of the entire table, which can drastically speed up filtered queries.

内容的提问来源于stack exchange，提问作者B. Mert