Mysql 简单优化及思路

1，针对千万级别的数据检索，mysql通过优化完全能够胜任：

　　a，建立全文索引（优点是针对文本进行索引，速度较快，缺点是如果查询多表，查询语句较长，繁琐，易出错）；

　　b，创建分区表（优点是查询时会自动按照分区表进行检索，查询较简单）；

2，实用简单命令：

　　a,清空缓存：reset query cache

　　b,查看当前分区表：SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'frank_test';

　　c,查看当前表的所有索引：show indexes from frank_2017;

　　d,创建全文本索引：CREATE FULLTEXT INDEX [KEY_NAME] ON [TABLE_NAME]([TABLE_ROWS ]);

　　e,match([TABLE_ROWS]) AGAINST('led tv' [in boolean mode]) 

　　　　(1), in boolean mode 为忽略检索词组的百分比（默认不加的话，如果命中率达到50%则会查询为空）；

　　　    (2), 'led tv' --> 包含led或者包含tv  *********  '+led +tv'  -->既包含led又必须包含tv   ***************      '"led tv"'  --> 必须包含led tv的字符串           ；

　　　　(3), 当然还有模糊匹配的， 'led*' 即可；

3，关联表的简单优化：

　　a,select a.id,a.name,a.code from a join b on a.code=b.code where a.value=2 and b.value>2  可改写为  select a.id,a.name,a.code from a where exists(select 1 from b where b.value>=) and a.value=2;

4，mysql循环语句，传输数据时需要用到的（数据量大且表结构发生变化的情况下）

delimiter $$
DROP PROCEDURE
IF EXISTS wk ; CREATE PROCEDURE wk ()
BEGIN
DECLARE
i INT ; DECLARE
j INT ; DECLARE
k INT ;
SET i = 1 ;
SET j = 50000 ;
SET k = 0 ;
WHILE i < 250 DO

SET k = (i - 1) * j ; INSERT INTO frank_test (
id,
gid,
frankly_code,
total_amount,
total_weight,
weight_unit,
total_volume,
volume_unit,
businesses_id,
businesses_name,
businesses_did,
businesses_address,
suppliers_id,
suppliers_name,
suppliers_did,
suppliers_address,
product_description,
frankly_time,
contact_id,
contact_name,
create_time,
update_time,
is_deleted,
qiyun_prot_id,
qiyun_prot_name,
mudi_prot_id,
mudi_prot_name,
mudi_country,
mudi_district_did,
fact_weight,
originer_country,
originer_country_id,
vessel_name,
vessel_id,
hs_code
) SELECT
id,
gid,
frankly_code,
total_amount,
total_weight,
weight_unit,
total_volume,
volume_unit,
businesses_id,
businesses_name,
businesses_did,
businesses_address,
suppliers_id,
suppliers_name,
suppliers_did,
suppliers_address,
product_description,
frankly_time,
contact_id,
contact_name,
create_time,
update_time,
is_deleted,
qiyun_prot_id,
qiyun_prot_name,
mudi_prot_id,
mudi_prot_name,
mudi_country,
mudi_district_did,
fact_weight,
originer_country,
originer_country_id,
vessel_name,
vessel_id,
hs_code
FROM
frank_2012
LIMIT k,
j ;
SET i = i + 1 ;
END
WHILE ; END$$
delimiter ;
CALL wk ();