面试题：MariaDB结合SphinxSE存储引擎应对复杂业务场景的设计

表结构设计

商品基本信息表（products_base）
- product_id：INT(11) PRIMARY KEY AUTO_INCREMENT，商品唯一标识。
- product_name：VARCHAR(255)，商品名称。
- product_description：TEXT，商品描述。
- price：DECIMAL(10, 2)，商品价格。
- sales_volume：INT(11)，商品销量。
Sphinx关联表（products_sphinx）
- id：INT(11) PRIMARY KEY AUTO_INCREMENT，与products_base的product_id对应。
- 其他字段可以根据搜索需求冗余部分products_base中的字段，如product_name，product_description等，以方便Sphinx索引。

索引策略

全文索引

在products_base表的product_name和product_description字段上添加MySQL原生的全文索引：

ALTER TABLE products_base ADD FULLTEXT(product_name, product_description);

对于SphinxSE存储引擎，在products_sphinx表中对需要全文搜索的字段建立Sphinx索引。配置Sphinx的sphinx.conf文件：

source products_src {
    type = mysql
    sql_host = 127.0.0.1
    sql_user = your_user
    sql_pass = your_password
    sql_db = your_database
    sql_query = SELECT id, product_name, product_description FROM products_sphinx
    sql_attr_uint = sales_volume
    sql_attr_float = price
}

index products_idx {
    source = products_src
    path = /var/lib/sphinxsearch/data/products_idx
    docinfo = extern
    mlock = 0
    morphology = stem_en
    min_word_len = 2
}

数值字段索引
- 在products_base表的price和sales_volume字段上添加普通索引，以加速数值过滤和排序：
```
ALTER TABLE products_base ADD INDEX idx_price (price);
ALTER TABLE products_base ADD INDEX idx_sales_volume (sales_volume);
```
- 在Sphinx配置中，通过sql_attr_uint（用于无符号整数，如sales_volume）和sql_attr_float（用于浮点数，如price）将这些数值字段作为属性索引，方便在Sphinx搜索时进行过滤和排序。

与应用层交互

全文搜索

应用层通过Sphinx客户端库（如sphinxapi.php for PHP）连接Sphinx服务器进行全文搜索。例如在PHP中：

require_once('sphinxapi.php');
$cl = new SphinxClient();
$cl->SetServer('127.0.0.1', 9312);
$cl->SetMatchMode(SPH_MATCH_ALL);
$query = 'search_term';
$result = $cl->Query($query, 'products_idx');
if ($result === false) {
    echo "Search error: ". $cl->GetLastError();
} else {
    foreach ($result['matches'] as $match) {
        $product_id = $match['id'];
        // 根据product_id从products_base表获取完整商品信息
    }
}

数值过滤和排序
- 应用层可以在Sphinx搜索时通过SetFilter和SetSortMode方法进行数值过滤和排序。例如，按销量降序且价格大于100过滤：
```
$cl->SetFilter('price', array(100, PHP_INT_MAX));
$cl->SetSortMode(SPH_SORT_ATTR_DESC,'sales_volume');
```
- 也可以在从Sphinx获取结果后，根据product_id从products_base表中查询数据时，使用MySQL的WHERE和ORDER BY子句进行数值过滤和排序，如：
```
SELECT * FROM products_base WHERE product_id IN (result_product_ids_from_sphinx) AND price > 100 ORDER BY sales_volume DESC;
```

可能面临的挑战及解决方案

数据同步问题
- 挑战：products_base表数据更新后，products_sphinx表数据可能不同步，导致搜索结果不准确。
- 解决方案：
  - 使用触发器（Trigger），在products_base表的INSERT、UPDATE、DELETE操作时，相应地更新products_sphinx表。例如：
```
-- INSERT触发器
DELIMITER //
CREATE TRIGGER after_products_base_insert
AFTER INSERT ON products_base
FOR EACH ROW
BEGIN
    INSERT INTO products_sphinx (id, product_name, product_description, price, sales_volume) VALUES (NEW.product_id, NEW.product_name, NEW.product_description, NEW.price, NEW.sales_volume);
END //
DELIMITER ;
```
  - 定期重建Sphinx索引，使用indexer --all --rotate命令，在业务低峰期执行，以确保索引数据的一致性。
性能问题
- 挑战：随着数据量的增加，全文搜索和数值过滤排序的性能可能下降。
- 解决方案：
  - 对于全文搜索，优化Sphinx配置，如调整min_word_len、morphology等参数，提高索引效率。同时，对数据进行合理的分区分表，将数据分散存储，减轻单个索引的压力。
  - 对于数值过滤和排序，确保MySQL的查询优化，使用覆盖索引（covering index），尽量减少回表操作。在Sphinx中，合理设置属性索引的类型和排序模式，以提高排序和过滤的速度。
Sphinx服务器负载问题
- 挑战：高并发的搜索请求可能导致Sphinx服务器负载过高。
- 解决方案：
  - 部署多个Sphinx服务器，采用负载均衡（如Nginx），将搜索请求均匀分配到各个服务器上。
  - 启用Sphinx的分布式索引（distributed index），将索引数据分布在多个节点上，提高查询处理能力。在sphinx.conf中配置分布式索引：
```
source products_src_all {
    type = distributed
    local = products_idx
    agent = 192.168.1.10:9312:products_idx
    agent = 192.168.1.11:9312:products_idx
}

index products_idx_all {
    source = products_src_all
    path = /var/lib/sphinxsearch/data/products_idx_all
}
```

星途面试题库

面试题：MariaDB结合SphinxSE存储引擎应对复杂业务场景的设计

知识考点

面试题答案

表结构设计

索引策略

与应用层交互

可能面临的挑战及解决方案