MySQL常见优化方案汇总

本文整理自网络，侵删。

目录

• 思考sql优化的几个地方，我把他做了个分类，方便理解
• key_len计算方式简单介绍
• 一、优化点1：字段优化
• 覆盖索引尽量用
• 二、优化点2：where优化
• 1.尽量全值匹配
• 2.最佳左前缀法则
• 3.范围条件放最后
• 4.不在索引列上做任何操作
• 5.不等于要甚用
• 6.Null/Not null有影响
• 7、Like 查询要当心 like
• 8.字符类型加引号
• 三、优化3
• 1.OR 改ｕｎｉｏｎ效率高

mysql优化是我们日常工作经常遇到的问题，今天给大家说下MySQL常见的几种优化方案。

注：原始资料来自享学课堂，自己加上整理和思考

思考sql优化的几个地方，我把他做了个分类，方便理解

ｓｅｌｅｃｔ [字段 优化1]：主要是覆盖索引
from []
where [条件 优化2]
union [联合查询 优化3]
新建表格

CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',  `name` varchar(50) DEFAULT NULL COMMENT '姓名',  `age` int(11) DEFAULT NULL COMMENT '年龄',  `phone` varchar(12) DEFAULT NULL,  `create_time` datetime DEFAULT NULL COMMENT '创建时间',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

添加索引，添加索引之后

key_len：根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。

key_len计算方式简单介绍

latin1占用1个字节，gbk占用2个字节，utf8占用3个字节

不允许为空：

varchar(10)：10*3

ｃｈａｒ(10)：10*3+2

int：4

允许为空：

varchar(10)：10*3+1

ｃｈａｒ(10)：10*3+2+1

int：4+1

使用完全索引key_len=name(50*3+2+1=153)+age(4+1)+phone(12*3+2+1=39)

alter table studen add index name_age_phone(name, age, phone);

添加数据

ｉｎｓｅｒｔ into student(name,age,phone,create_time) values('赛文',1000,'15717177664',now());ｉｎｓｅｒｔ into student(name,age,phone,create_time) values('雷欧',1200,'15733337664',now());ｉｎｓｅｒｔ into student(name,age,phone,create_time) values('泰罗',800,'15714447664',now());

一、优化点1：字段优化

覆盖索引尽量用

简单解释解释，索引是哪几个列，就查询哪几个列： 覆盖索引的原因：索引是高效找到行的一个方法，但是一般数据库也能使用 索引找到一个列的数据，因此它 不必读取整个行。毕竟索引叶子节点存储了它们索引的数据; 当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引 包含了(或 覆盖了)满足查询结果的数据就叫做覆盖索引 注意：有索引尽量不要使用ｓｅｌｅｃｔ *

#未覆盖索引EXPLAIN ｓｅｌｅｃｔ * FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';#覆盖了索引EXPLAIN ｓｅｌｅｃｔ name,age,phone FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';#包含了索引EXPLAIN ｓｅｌｅｃｔ name FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';#加上主键也还是覆盖索引EXPLAIN ｓｅｌｅｃｔ id, name,age,phone FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';

未使用覆盖索引

使用完全覆盖索引

使用包含覆盖索引

加上主键还是覆盖索引

二、优化点2：where优化

1.尽量全值匹配

EXPLAIN ｓｅｌｅｃｔ * FROM student WHERE NAME = '赛文';EXPLAIN ｓｅｌｅｃｔ * FROM student WHERE NAME = '雷欧' AND age = 1200;EXPLAIN ｓｅｌｅｃｔ * FROM student WHERE NAME = '泰罗' AND age = 800 AND phone = '15714447664';

执行结果，三个都用到了索引，但是key_len是不同的，key_len=197，表示所有索引都使用到了

当建立了索引列后，能在 wherel 条件中使用索引的尽量所用。

2.最佳左前缀法则

最左前缀法则：指的是查询从索引的最左前列开始并且不跳过索引中的列。 我们定义的索引顺序是 name_age_phone ，所以查询的时候也应该从name开始，然后age，然后phone 情况1：从age、phone开始查询，tpye=All，key = null，没使用索引

阅读剩余部分

相关阅读 >>

java嵌入数据引擎从sqlite到spl详解

sql怎么加密表

分析sql查询语句是如何执行的

sqlserver如何查看索引缺失及dmv使用介绍

mysql索引以及查询优化的详细介绍

navicat怎么运行sql文件

删除索引的sql语句是什么

c#操作sqlite数据库帮助类详解

sql中distinct只作用于一个字段的方法

sql server中identity(自增)的用法详解

更多相关阅读请进入《sql》频道 >>

书籍

数据库系统概念 第6版

￥41.1元   机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。