MySql系列：查询优化简单总结

前言

有时候圈子很重要，就像上学期间大家都会发现班里有这样一类学生不怎么听课，但是就是学习好。那假如让他回家呆着，不能在课堂里呢？类似的圈子还有；图书馆、网吧、车友群、技术群等等，都可以给你带来同类爱好的人所分享出来的技能或者大家一起烘托出的氛围帮你成长。

有人要问博主为什么要对MySql进行优化呢？

这个问题我该怎么说呢，只要是一个有过中型以上项目开发经验的人都知道，一条MySql执行性能关乎了你的系统的稳定性以及性能。

数据库的四大特征，数据库的隔离级别

首先说一个老生常谈的话题，也是面试最喜欢问的东西，希望博主的分享对现在的你有所帮助！

数据库的四大特征：

• 原子性（Atomicity）：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。

• 一致性（Consistency）：一个事务执行之前和执行之后都必须处于一致性状态。

• 隔离性（Isolation）：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

• 持久性（Durability）:持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。

数据库的隔离级别：

• Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
• Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
• Read committed (读已提交)：可避免脏读的发生。
• Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

数据库设计范式：

• 什么是范式：简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式。

• 什么是三大范式：
• 第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
• 第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。
• 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.

注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性

第一范式：

• 每一列属性都是不可再分的属性值，确保每一列的原子性
• 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。

第二范式：

• 每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。
• 一条数据做一件事，不掺杂复杂的关系逻辑。同时对表数据的更新维护也更易操作。

第三范式：

• 数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。像：a-->b-->c 属性之间含有这样的关系，是不符合第三范式的。

例如：

• 比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）
• 这样一个表结构，就存在上述关系。 学号--> 所在院校 --> (院校地址，院校电话)
• 这样的表结构，我们应该拆开来，如下。
• （学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，就要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库，各位小伙伴要记牢哦！

MySql的优化建议

注意：（尽量减少 SELECT * 的出现下文用到请忽略，用具体的字段列表代替 * 号，不要返回用不到的任何字段，用字段能够大大的节省数据传输量，与数据库的内存使用量哟。）

划重点：

EXPLAIN:

• 首先做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。
• type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
• key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
• key_len列，索引长度。
• rows列，扫描行数。该值是个预估值。
• extra列，详细说明。注意，常见的不太友好的值，如：Using filesort，Using temporary。

SELECT语句务必指明字段名称：

• SELECT*增加很多不必要的消耗（CPU、IO、内存、网络带宽）；
• 增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。
• 所以要求直接在SELECT后面接上字段名。

一些常见的SQL实践

• 负向条件查询不能使用索引

SELECT * FROM order WHERE order_status!=0 AND order_status !=1
 not in/not exists都不是好习惯

• SQL语句中IN包含的值不应过多：

• 如果排序字段没有用到索引，就尽量少排序

---

• 尽量用union all代替union

• 前导模糊查询不能使用索引（会导致索引失效而进行全表扫描）

SELECT * from order WHERE order_desc LIKE '%XX'

• 而非前导模糊查询则可以：（不会导致索引失效而进行全表扫描）

• 数据区分度不大的字段不宜使用索引

SELECT * FROM user WHERE user_sex=1 原因：性别只有男，女，每次过滤掉的数据很少，不宜使用索引。

只要能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引。

---

• 在属性上进行计算不能命中索引

SELECT * FROM order WHERE YEAR(date) < = '2019'

• 即使date上建立了索引，也会全表扫描，可优化为值计算：

SELECT * FROM order WHERE date < = CURDATE() 或者： SELECT * FROM order WHERE date < = '2019-05-08'

并非周知的SQL实践

---

• 分段查询：（扫描的行数成百万级以上的时候就可以使用分段查询）

在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。 原因是扫描行数过多，这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。

---

• 避免在where子句中对字段进行null值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

---

• 避免隐式类型转换

WHERE子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定WHERE中的参数类型

---

• 避免在where子句中对字段进行表达式操作比如：

SELECT * FROM user WHERE age*2=36; 中对字段就行了算术运算，这会造成引擎放弃使用索引，

• 建议改成：SELECT * FROM user WHERE age=36/2;

---

• 注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

---

• 如果业务大部分是单条查询，使用Hash索引性能更好，例如用户中心

SELECT * FROM user WHERE user_id=?
SELECT * FROM user WHERE login_name=?

• 原因：

B-Tree索引的时间复杂度是O(log(n)) Hash索引的时间复杂度是O(1)

---

• 允许为null的列，查询有潜在大坑

单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集 SELECT * FROM user WHERE user_name != 'Chenyongjia' 如果name允许为null，索引不存储null值，结果集中不会包含这些记录。

• 所以，请使用not null约束以及默认值。

---

• 复合索引最左前缀，并不是值SQL语句的WHERE顺序要和复合索引一致
• 用户中心建立了(login_name, passwd)的复合索引

SELECT * FROM user WHERE login_name=? AND user_passwd=?
SELECT * FROM user WHERE user_passwd=? AND login_name=?

• 都能够命中索引

SELECT * FROM user WHERE login_name=?

• 也能命中索引，满足复合索引最左前缀

SELECT * FROM user WHERE user_passwd=?

• 不能命中索引，不满足复合索引最左前缀

---

• 使用ENUM而不是字符串

ENUM保存的是TINYINT，别在枚举中搞一些“中国”“上海”“技术部”这样的字符串，字符串空间又大，效率又低。

---

小众但非常有用的SQL实践

看累了嘛！别松懈跟着博主继续学习。

---

• 如果明确知道只有一条结果返回，LIMIT 1能够提高效率，这是为了使EXPLAIN中type列达到const类型

SELECT * FROM user WHERE login_name=?

• 可以优化为：

SELECT * FROM user WHERE login_name=? LIMIT 1

• 原因：

• 把计算放到业务层而不是数据库层，除了节省数据的CPU，还有意想不到的查询缓存优化效果

SELECT * FROM order WHERE date < = CURDATE()

• 这不是一个好的SQL实践，应该优化为：

curDate=date(′Y−m−d′);curDate = date('Y-m-d');curDate=date(′Y−m−d′);res = mysql_query( 'SELECT * FROM order WHERE date < = $curDate');

• 原因：

释放了数据库的CPU

• 多次调用，传入的SQL相同，才可以利用查询缓存

---

• 强制类型转换会全表扫描

SELECT * FROM user WHERE user_phone=15638589820

• 你以为会命中phone索引么？大错特错了，这个语句究竟要怎么改？

---

注意：以上SQL语句尽量全部用大写不要问我为什么哈哈！

MySQL无法使用索引的情况总结

---

• 字段使用函数，将无法使用索引 (2)Join 语句中 Join 条件字段类型不一致的时候 MySQL 无法使用索引
• 复合索引的情况下，如果查询条件不包含索引列的最左边部分，即不满足最左前缀原则，则不会使用索引
• 如果mysql估计使用索引扫描比全表扫描更慢，则不使用索引。(扫描数据超过30%，都会走全表)
• 以%开头的like查询
• 数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在where条件中把字符串常量值用引号引起来，否则即便这个列上有索引，MySQL也不会用到，因为MySQL默认把输入的常量值进行转换以后才进行检索
• 用or分割开的条件，如果 or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

 最后

对于程序员来说，要学习的知识内容、技术有太多太多，要想不被环境淘汰就只有不断提升自己，从来都是我们去适应环境，而不是环境来适应我们！

不用多说，相信大家都有一个共识：无论什么行业，最牛逼的人肯定是站在金字塔端的人。所以，想做一个牛逼的程序员，那么就要让自己站的更高，成为技术大牛并不是一朝一夕的事情，需要时间的沉淀和技术的积累。

现在竞争这么激烈，只有通过不断学习，提高自己，才能保持竞争力。

对于一些不知道学习什么，没有一个系统路线的程序员，这里给大家提供一些学习资料

需要的小伙伴，可以一键三连，点击这里获取免费领取方式！

《Java核心知识点合集（283页）》

内容涵盖：Java基础、JVM、高并发、多线程、分布式、设计模式、Spring全家桶、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat、数据库、云计算等

《Java中高级核心知识点合集（524页）》

《Java高级架构知识点整理》

《Docker从入门到实践》

《spring could 学习笔记》

《JVM与性能调优知识点整理》

《MySQL性能调优与架构设计解析文档》305页

《Nginx入门到实战》319页

《Java并发编程》385页

《1000道 互联网Java工程师面试题 (485页)》

需要的小伙伴，可以一键三连，点击这里获取免费领取方式！