MySQL的优化

表字段的类型设计

字符串 char 定长字符串 如 char(8) 低于8个,剩下的用空格填充,高于8个,报异常 varchar 不定长字符串 如 varchar(8)低于8个,经历收缩过程,变为实际大小.高于8个,报异常 如果字段长度固定,选用char,不固定,选用varchar

姓名: varchar 学号 char 城市 varchar 数值 类型越小,效率越高.但是必须符合业务逻辑. 主键 long, 不用int 增长到最大,变为最小值.1~2^32 时间戳 long 1970.1.1 凌晨开始计数 到现在的毫秒数. 日期类型 合适的为标准 date 年月日 time 时分秒 datetime 年月日时分秒 timestamp 年月日时分秒毫秒 时刻.

生日: date , 操作时间timestamp 可以排序

优化目标

七个子句构成 ,每个子句具有关键字引导 + 内容构成. select 挑选字段 from 挑选表 where 挑选行 group by 分组 having 分组条件 order by 排序 limit 分页

1 书写顺序 select,from,where,group by,having,order by,limit 2 执行顺序 from,where,group by,having,select,order by,limit

主键的选择

一个表中最多有一个主键. 如果你不定义主键,系统会为表 建立一个隐藏的主键. 1 主键选择与业务无关的字段,作为主键. 如:id等 2 能用单值的,就不用联合主键. 3 常见主键,如 自增长, uuid, 雪花算法. 1) 自增长, 最好的主键. 有序, 长度短. 类型,尽量不要使用int类型. int 四个字节, 应该使用 big int 类型 八个字节. 缺点: 数据可能不连续. 2) uuid 长度: 36个 中32个字母 4个- 无序(缺点), 唯一(优点). 3) 雪花算法:(网上) 内含时间戳, 所有有序的. 保证唯一.

什么情况下,索引会失效

1 like 模糊查找的时候, %放在前面,索引会失效 2 or 后面没有索引,也可能索引失效 3 范围查找(> >= < <= !=),第一个范围之后的索引,可能失效. in和between and 虽然是范围,有限个数比较, 但是不会引起索引失效 4 组合索引中,前面的字段没有指定,后面的字段索引会失效. 如 （a,b,c） 没有a 5 条件中使用函数,可能引起索引失效 6 条件中使用运算符,可能引起索引失效

7. 隐式类型转换导致 索引失效 例如 where t1.tid = t2.id tid 和 id 的类型不一致 需要类型转换

   8.字符串条件没加

SQL优化策略

语句方面：

提高type的级别

尽量避免使用子查询 用 left join

给group by 后面的字段,增加索引 用驱动表的字段

如果分组不需要排序 可以用 order by null 去 禁止排序

用in 来替换 or

读取适当的记录 limit m，n ：

对于 limit m, n 的分页查询，越往后面翻页（即m越大的情况下）SQL的耗时会越来越长

因为MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下

起始位置重定义（效率最高，但是要记住ID值）：

select id,name from table_name where id > 866612 limit 20

禁止不必要的Order By排序

如果我们对结果没有排序的要求，就尽量少用排序；

尽量用索引 用驱动表的字段 防止临时表的产生

区分in和exists

in 小 exists大

优化join语句

在执行join的时候，数据库会选择一个表把他要返回以及需要进行和其他表进行比较的数据放进join_buffer。

所以可以加大join_buffer的大小

增大sort buffer 大小 减少临时表产生的几率

 

索引方面：

表的结果少于2000行的也不适合做索引

 

1 提高type的级别

2 尽量不使用函数,

33尽量不使用表达式

4 选择索引选择性接近1的字段,做索引

5 主键选择自增长字段,类型是bigint

6 表的结果少于2000行的也不适合做索引

7 内连接的时候, 默认是小表驱动大表, 可以通过STRAIGHT_JOIN 改变驱动表

8 给连接条件增加索引

9 增加join buffer 大小

10 连接条件数据的长度尽量短

11 提高max_length_for_sort_data 阈值, 增加单路排序的几率

12 不要使用select * 会增加排序字段的长度

13 增大sort buffer 大小 减少临时表产生的几率

14 给group by 后面的字段,增加索引

15 给distinct 里面的字段增加索引

16 不要在group by或者order by后面使用被驱动表的字段, 减少临时表的产生

 

explain的 type(级别) 从上往下,效率依次降低的.

system 内存表 从缓存中获取数据 const 条件=常量 如: emp_no = ‘10001’ eq_ref 条件=主键索引. a.emp_no = b.emp_no 其中 b.emp_no 是主键索引 ref 条件=辅助索引(有重复) 如 a.emp_no = b.emp_no 其中emp_no是联合主键中的一部分, 可能重复. ref_or_nul ref or null 如 user_id=100 or user_id is null; index_merge 索引合并. 一个字段上具有多个索引. 触发索引合并. emp_no=110022 or dept_no=’d001′; 其中 dept_no 这个字段具有两个索引, 一个是primary key 一个普通索引. range 范围检索

= < <= != in between and explain select * from employees where emp_no > 10001 emp_no 是索引, 符号是一个范围符号. index 遍历整个索引文件 explain select count(*) from employees ALL 全表扫描, 遍历整个数据文件. 没有添加索引

一 几方面

（1） 硬件优化

（2) 架构优化，

使用Redis缓存 ，主从复制 读写分离 ， 分库分表（垂直拆分 水平拆分）

(3) 数据库优化（利用redolog 和 buffer 持久化 数据 的 过程）

日志不能小 ，（buffer）缓存足够大 ，连接足够多

(4) SQL优化

语句优化 + 索引优化

二 步骤

MySQL 优化步骤 – walkersss – 博客园 (cnblogs.com)

1.开启慢查询 set global slow_query_log=1

long_query_time默认值为10

2.explain + 慢查询分析 3. show Profile 查询当前 SQL 语句得资源消耗得情况

 

查看优化器中的执行计划 针对参数进行解析 分析问题出现在哪里 然后进行优化

1.id列：id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。 id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

2.select_type 列：

select_type 表示对应行是简单还是复杂的查询.

1）simple：简单查询。查询不包含子查询和union 1 mysql> explain select * from film where id = 2; 2）primary：复杂查询中最外层的 select 综合后最外面的select 3）subquery：包含在 select 中的子查询（不在 from 子句中） 4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含 义）

3. table列: 查询的是哪张表

4. type列: MySQL决定如何查找表中的行

   ALL：全表扫描 扫描聚簇索引的所有叶子节点 需要添加索引来优化

   index：扫描全部索引 select count(*) from employees

   range：范围扫描 一般出现在 in between > < 中 使用一个索引来 检索给定的范围的行

   ref：使用普通索引

   eq_ref: 使用主键索引

   const： 在where 语句中使用常量

   system 内存表 从缓存中获取数据

5. possible_keys列： 可能会用到的索引

6. key列 ：实际用到的索引

7. key_len列：索引使用的字节数 通过这个值可以算出具体使用了索引的哪些列

8. rows列：mysql估计要读取并检测的行数

9.Extra 列 ：using file sort ：如果在排序得时候 （order by 或者 Group by 先排序· 后分组）

没有建立索引 那么就会采取 其他排序方式 （文件排序）

以及using temporary: 有没有用到 临时表

在group by的时候，如果，被group by的列不在索引上，那么就需要临时表来进行group by，另外如果没有order by null，也许会要file sort进行排序。 在order by的时候，如果被排序的列，分布在多余一个表上

临时表个数 ： 总行数/一次放入sort buffer的行数 所以说增大sort buffer 可以减少临时表的产生

3. show Profile 查询当前 SQL 语句得资源消耗得情况

如果出现 查询结果过大 内存不够 放入磁盘中

是否创建 内存临时表 或者 将内存中得临时表放入了磁盘中 都是需要优化的