mysql简单优化的学习笔记

前言

以前光是会简单的使用数据库，除了使用主键之外还真的从来没有考虑过效率的问题，当然这对于我们平时练习用的数据量是没有问题。但是考虑到以后自己难免会接触到大数据量，所以至少基本的mysql优化还是得学一下。本篇博客也就是这段时间查的资料的整理，依旧是开头先感谢那些已经写了博客供我们学习的前辈们。

优化顺序

之前在知乎上看到一个人说的，感觉挺有道理。
第一步：先优化sql语句和索引
第二步：添加查询缓存，不管是mysql自带的也好，还是使用redis来做辅助缓存也好
第三步：主从复制和主主复制(面向多服务器项目)
第四步：垂直拆分，将大的系统做成分布式系统。
第五步：水平切分。如果某个表的数据太多，就拆表
而本文主要记录的是第一步和第二步的方法

开启查询缓存

什么是查询缓存？当你频繁使用相同的sql语句进行查询的时候，mysql数据库会记录下来你这些频繁查询后的数据集，这样当你再次调用sql语句的时候，mysql数据库会直接从缓存中把数据拿出来，而不会重新再对表进行一次遍历。
当然，查询缓存对于那些不经常变动的数据是非常有用的。但如果你要查的数据是一个经常变动的量，那么使用缓存反而适得其反。
大多情况下，mysql数据库是自动将查询缓存打开的。当然对于这些经常变动的数据，你希望直接绕过缓存功能，那么就用下面这条语句进行查询即可select sql_no_cache * from tablename
如果要看是否开启了缓存，使用命令show variables like '%query%'即可。以下是效果图

还需要注意的一点是，如果我们在sql语句中添加了函数，比如Rand()，now()或者一些自己定义的函数，那么mysql数据库是不会启用缓存的。道理很简单，因为这些函数返回的是一个不定值，而缓存是对不经常变动的数据有效。
现在还有很多其他的手段进行缓存，比如使用nosql数据库Redis之类的当做辅助缓存。不过这些技术本篇仅是提一句。

对查询语句使用explain关键字以及添加索引

很多情况下，我们对自己的select语句的执行过程并不是很清楚。这个时候为了方便我们对数据库进行优化，我们需要了解自己select语句的执行过程并加以分析。具体方法是在select语句前面加上explain关键字即可。
explain select * from tablename where column1=xxxx
一下举一个简单的例子
我们有一个user表，里面有999条数据，里面所有的信息全部是唯一的。

我们要查一条tel=1的数据，使用语句explain select * from user where tel=1 查出来的结果如图所示

我们可以发现明明只有一条数据，但是我们却遍历了整张表。这时候就能体现explain的作用，就是报告select语句查询过程中的一些参数，从这些参数中，我们就能发现一些问题。
之所以会遍历所有的数据，是因为数据库并不知道它是唯一的，他要继续查找数据库以便找出所有tel=1的数据（尽管并不存在）。
这个时候为了提高效率，我们就可以在tel列上添加索引。添加完成后，我们再使用同样的语句查询，就可以发现只搜索了一行。这样就极大的增加了效率。

explain的使用详解可以参考这篇博客
如果不清楚索引的基本用法是什么，可以查看我这篇学习笔记

精准的使用limit关键字

有时候我们能明确的知道我们想要的数据的数量，比如，注册，我们在判断用户名是否有重复的时候，只要查出来一条数据，就能确定该用户名已被使用。这时候我们很清楚的知道我们只需要一条数据，所以就在sql语句后面加上一个 limit 1来限制搜索的进程。如果不加，每次都要进行全表搜索，这是相当浪费资源的一种做法。

不要使用order by rand()

我估计这句sql语句的原意是想将查出来的数据的顺序打乱。但mysql中的rand()函数是相当耗CPU时间的，使用这个函数会使sql的效率呈指数级下降。想要实现“将查出来的数据顺序打乱并随机挑选”这个功能完全有其他更好的效率更高的解决方案。所以绝对不要用order by rand()

若非必要，尽量不要用select *

虽然select * 使用起来很方便。新手的思想就是不管想要什么，先全查出来再说，然后从查出来的整体数据里抽取想要的就行，没必要花大工夫去拼接字符串。
当然还是那句话，数据量小的时候，优不优化很难看出来。但是数据量级上去以后，使用select *在一定程度上就会影响效率。如果你的存储服务器和web服务器还不是同一个的话，大量多余的数据会对服务器之间的传输造成没必要的压力，那么自然就影响效率了。

主键策略

不管如何，都为一张表设置一个名为id的主键，而且设置为unsigned型。使用varchar型的数据作为主键或者索引会在一定程度上影响效率。

如果能使用ENUM，就不要使用varchar

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。 如果我们有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，我们知道这些字段的取值是有限而且固定的，那么，我们应该使用 ENUM 而不是 VARCHAR。
创建ENUM的方法

CREATE TABLE table1(
    id  
    sex ENUM('Male','Female','Uncertain') DEFAULT 'Uncertain'
);

这样table1里面的ENUM就只能插入‘Male’,’Female’和‘Uncertain’三个值
而且由于在底层保存的方式是TINYINT，所以where sex = 'Male'和where sex=1是等效的。不过需要注意的一点是，如果你插入了规定以外的字符串（在我举的这个例子中就是插入了除‘Male’，‘Female’和‘Uncertain’以外的字符串），mysql数据库会将你插入的这个错误字符串转换为空字符串(‘’)，并将其索引值调整到0。也就是说你可以通过select * from table1 where sex=0这句话来查看错误插入的语句。
枚举最大可以有 65535 个成员值。
推荐在使用enum的时候只保存字符串类型的数据，并且按字母大小排好。

如果可能，不要使用NULL

如果不是特殊情况，尽可能的不要使用NULL。在MYSQL中对于INT类型而言，EMPTY是0，而NULL是空值。而在Oracle中 NULL和EMPTY的字符串是一样的。NULL也需要占用存储空间，并且会使我们的程序判断时更加复杂。
另外一点，如果你在一个添加了索引的列里面添加了NULL值，将会使整个索引失效。

在后台使用 Prepared Statements

不同的语言实现预处理（Prepared Statements）有不同的方法，但都大同小异，即将想要插入的参数绑定，避免“sql注入式”的攻击。并且预处理是使用二进制进行传输，效率自然不错。

选用合适的数据引擎

不知道什么是数据引擎的可以查看我的这篇笔记
针对不同的业务，选用不同的数据引擎是一个提高效率的好办法。举个例子：
因为MyISAM相对简单，所以在效率上要优于InnoDB.如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。
如果系统读少，写多的时候，尤其是并发写入高的时候。InnoDB就是首选了。
mysql是支持混用数据引擎的。同一个数据库里的不同表，或者是主从数据库里的数据引擎都可以设置为不同的。

在join表的时候使用相当类型的列，并将其索引

如果在程序中有很多JOIN查询，应该保证两个表中join的字段时被建立过索引的。这样MySQL颞部会启动优化JOIN的SQL语句的机制。注意：这些被用来JOIN的字段，应该是相同类型的。例如：如果要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）
例如：SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = “user_id”
两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。

待补充。。。。。。

参考资料

https://blog.csdn.net/kaka1121/article/details/53395587
https://www.cnblogs.com/xuanzhi201111/p/4175635.html
https://www.zhihu.com/question/19719997
https://blog.csdn.net/u013087513/article/details/77899412