mysql优化（待续）

ゝ一世哀愁。 2022-07-31 15:22 102阅读 0赞

# 索引与优化like查询 #

--------------------

## like查询遇到的问题 ##

--------------------

1.  like %keyword 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。
2.  like keyword% 索引有效。
3.  like %keyword% 索引失效，也无法使用反向索引。

## 如何解决 ##

--------------------

**一 使用下面的函数来进行模糊查询，如果出现的位置〉0，表示包含该字符串。**

查询效率比like要高。

如果： ***table.field like ‘%AAA%’*** 可以改为 ***locate (‘AAA’ , table.field) > 0***

LOCATE(substr,str)
    POSITION(substr IN str)

返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0。

***使用instr***

select count(*) from table t where instr(t.column,’xx’)> 0

这种查询效果很好，速度很快。

**二 查询%xx的记录**

select count(c.c_ply_no) as COUNT
    from Policy_Data_All c, Item_Data_All i
    where c.c_ply_no = i.c_ply_no
    and i.C_LCN_NO like ’%245′

在执行的时候，执行计划显示，消耗值，io值，cpu值均非常大，原因是like后面前模糊查询导致索引失效，进行全表扫描

解决方法：这种只有前模糊的sql可以改造如下写法

select count(c.c_ply_no) as COUNT
    from Policy_Data_All c, Item_Data_All i
    where c.c_ply_no = i.c_ply_no
    and reverse(i.C_LCN_NO) like reverse(‘%245′)

使用翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全扫描。有效降低消耗值，io值，cpu值这三个指标，尤其是io值的降低。

# 优化大批量插入数据 #

使用load命令导入数据时，适当的设置可以提高导入的速度

## MyISAM存储引擎 ##

// MyISAM存储引擎快速导入大量数据
    alter table tbl_name disable keys;
    loading the data
    alter table tbl_name enable keys;

disable keys 和 enable keys 涌来打开或者关闭 MyISAM表**非唯一索引的更新**。

1.  在导入大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入效率。
2.  在导入大量数据到一个空的 MyISAM 表时，默认就是先导入数据然后才创建索引。

## InnoDB 存储引擎 ##

1.  InnoDB类型的表按照逐渐的顺序保存，所以导入的数据按照主键的顺序排列，可以提高导入数据的效率。
2.  在导入数据前执行 `set unique_checks=0`，关闭唯一性校验，在导入结束后执行 `set unique_check=1`，恢复唯一性校验，可以提高导入效率。
3.  如果应用使用自动提交的方式，建议在导入前执行 `set autocommit=0`，关闭自动提交，导入结束后再执行 `set autocommit=1`，打开自动提交，可以提高导入效率。

# 优化INSERT语句 #

1.  如果从同一客户插入很多行，应尽量使用多个值表的insert语句，这种方式大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行单个insert语句块。例如：`insert into test values(1,2), (1, 3), (1,4)`
2.  如果从不同客户端插入很多行，可以通过使用 insert delayed 语句得到更高的速度。delayed 的含义是让 insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，比每条语句分别插入要快的多；low\_priority刚好相反，在所有其他用户对标的读写完成后才进行插入。
3.  将索引文件和数据文件分别在不同的磁盘上存放（利用建表中的选项）。
4.  如果进行批量插入，可以通过bulk\_insert\_buffer\_size变量值的方法来提过速度。这只对 MyISAM 表使用。
5.  从一个文件装载一个表时，使用 load data infile 。通常比使用很多 insert语句快20倍。