MYSQL优化

MYSQL优化

一、基本的数据库表设计优化

1.建表选择合适的字段类型，尽量正确尽量小

占用更少的次磁盘、内存、及缓存空间

• 能用int就不要用bigint
• 字段是定长的尽量用char不定长的用varcha

检索时char类型后的空格被删掉，而varchar类型的保存。

2.建表要满足三大范式

• 基本建表要满足三大范式

通常占用的内存空间会小
没有冗余字段更新的时候会快

• 必要情况下要反三范式

可能出现字段冗余，但是会减少join的次数，以空间换时间（联单业务主表设计了冗余字段 而且业务上还不允许次更新这个字段）

二、创建高性能的索引避免全表扫描

1.创建合适的索引

• 经常在where、groupBy、orderBy后面的字段及表与表关联的字段。
• 区分度不高的字段不适合加索引 例 性别。
• 更新频繁的列不适合加索引。
• 覆盖索引和Mysql5.6后提出的索引下推 （当使用索引条件下推优化时，如果where条件中包含复合索引中一个以上的字段，MySQL会将这一部分判断条件交给给存储引擎去做，避免再回到服务端去处理，减少 I/O 次数回表的次数，expalin计划的Extra列显示Using index condition代表用到了索引下推 Using index代表索引覆盖 ）。
• 利用好前缀索引 有效的减少索引长度，减少物理存储空间，降低修改数据维护索引的代价。
• 利用组合索引对于需要orderBy的语句。

覆盖索引：使用二级索引就可直接查出数据，不需要查出主键索引再去根据主键查需要的数据。

索引下推： 当使用索引条件下推优化时，如果where条件中包含复合索引中一个以上的字段，MySQL会将这一部分判断条件交给给存储引擎去做，避免再回到服务端去处理，减少 I/O 次数。

2.避免索引失效

• 避免以like%开头的用法
• 避免or两边没同时用索引
• 避免where条件发生类型转换 例vachar字段 where a = 3
• 避免用!=、<>，可以用 in 替代
• 避免where后字段进行 计算、函数、表达式 操作
• 避免使用 is not null 这样 创建字段可给字段赋值为0
• 范围条件右边的列不能使用索引（范围列可以用到索引）

• 以上是否用到索引可用explain执行计划查看 key 列

三、SQL语句优化

• 关联查询的优化 外连接
• 子查询的优化 子查询会产生临时表 （这个临时表的创建和销毁会增加内存和IO消耗） 尽量改为关联查询
• 分组查询是能在where中筛选的就不要在having中做筛选
• 善用limit能加快查询效率，当偏移量大的时候可利用覆盖索引
• exist和in的使用要遵循小表驱动大表的原则 in后面的表要小于前面的表，exist则相反

#开启慢查询功能
set global slow-query-log=on
#指定慢查询日志文件位置
set global slow_query_log_file= ‘/var/log/mysql/mysql-slow.log’ ;
#记录没有使用索引的查询
set global log_queries_not_using_indexes=on;
#只记录处理时间1s以上的慢查询
set global long_query_time=1;

四、读写分离

五、分库分表

提示：这里对文章进行总结：