mysql数据库的sql优化

前言

我们在做项目的时候，必须会跟数据库打交道，其实好多程序员做的最多的也就是数据库的crud，但是即使是crud，好多人也不会在意自己的sql写的好坏，如果能让我们数据库发挥出最大的性能，那么就要涉及到我们数据库的优化，其实数据库优化，并不是去优化数据库，只是优化我们的sql语句，或者建立索引等，更高级的可能就要设计到分表分库，主从同步等。而我们本篇博客主要会针对于数据库的sql的调优，这个也是我们大部分程序员所应该具备的。

什么是数据库sql调优

sql优化：可能大家听得比较多，网上一查也有很多资料，但是在我们写项目中，却没有几个人会注意这一点，只有等到项目上线后，出现了一系列的问题，才会去修复，这时候就会比开发时候调优麻烦多了，sql优化其实就是通过合理建立索引以及合理写sql来达到以最优的方式查询数据。因为我们现在web中使用的比较多的持久层框架是mybatis，mybatis的好处我就不说了，其中最大的好处之一就是我们可以自己写sql，然后来调优我们写的sql。总得来说就是通过sql语句来达到查询数据耗时最短，还有更高级的之后再讲吧（ps，分表分库，主从同步）。
其实我们在项目中，好多时候是不会在意sql的好坏，只要能查到正确的数据并不会在意这过程耗时多少，性能是咋样的（最差的是全表扫描）。可能你觉得即使自己写了一个很烂的sql（自己还不知道），发现查询数据库耗时也不多，可能也就0.4毫秒吧，可是你要这样想啊，你是在开发环境上面的，数据量最多也就几千条，你想过没有，但数据是几千万条的时候，会怎么样（ps，不敢想不敢想），如果你是按照全表扫描的话，恰巧是最后一条，那么耗时的话就是0.4*10000，也就是40秒，这个时间段对用户来说是什么概念，所以优化sql就显得特别重要了，那么如何优化呢？我们主要会从这两方面介绍：

1. 如何建立索引
2. 如何优化sql

如何建立索引

在我们谈到索引的时候，我们有必要了解一下什么是索引，用个最简单的例子说一下，其实就相当于我们字典的目录，我们可以通过某个关键字然后找到具体的，而不用一一去找。索引也有好多类型的：比如主键，这个是我们最常用的一种，他是唯一性的，所以我们经常使用id并且自增，还有普通索引，聚集索引等。
其实我们每次去建立一张数据库表比给定主键的时候，其实我们也就建立了一个索引，索引的好处就是不用进行全表扫描。这样大大的优化了我们的查询的时间。
建立索引需要注意一下几点:
1、选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
2、为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
3、为常作为查询条件的字段建立索引
经常查询的字段建立索引可以有效的减少查询的时间，避免全表扫描
4、限制索引的数目
可能有人能问，既然索引能有效的减少查询时间，那么我们就将所有的字段都建立索引。其实建立索引确实能提高查询效率，但是他也会增加写入的效率，因为索引是需要去存的，所以并不是索引越多越好。
5．尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
6、尽量选择区分度高的列作为索引。
7、索引列不能参加计算。
8、针对组合索引，比如我们建立一个组合索引（a,b,c），那么我们where a>0 and b>0 是可以使用索引的，但是如果少了a，那么索引就会失效。

sql语句的优化

其实sql语句的优化是针对于索引的，我们上面已经说了如何建立了索引，可能有人会问，索引建立好了，我们直接用不就可以了吗？如果真是那样的话，那还用考虑sql的优化吗，其实在sql中并不是建立了索引并使用了索引就能生效的，因为我们sql语句的某个过程可能会导致我们的索引的实效，所以我们需要注意，否则既白白浪费了建立索引的空间，还导致索引无效，我们下面就会讲解如何避免索引无效。
1、首先避免使用not in和！-，这个可能导致索引实效，记住是可能，虽然我试了几次好像都不会，可以使用exist和not exist替换。
2、使用like的时候，千万别使用这种模式%a%，这个会导致索引失效，可以使用a%，这个是可以使用索引。
3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null，索引列上面最好别使用null，可以使用0或者1等。
4、.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2 ，这个之前我们在建立索引的时候就说过，索引字段不要参与计算。
6、索引是针对于查询的数据是少的有效，如果检索数据量超过30%的表中记录数，使用索引将没有显著的效率提高。
7、尽量少使用select * ，最后就是使用到哪些字段就返回哪些字段
8、字段尽量不要大写小写夹杂，因为mysql还要进行翻译。
9、尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间， 其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

使用explain工具

在我们开发的过程中，可能不单单注意上面的还不够，毕竟不能一一列举出来，但是我们在写sql的时候，尤其是当我们sql比较复杂的时候，在写完，确定我们的sql没有问题的时候，记住使用explain来分析一下你写的sql的性能，这个命令使用其实很简单，只需要在你执行sql的命令前加上一个explain的命令即可，不过我们的mysql的可视化也提供了这个功能，如图，我使用的是natival for mysql

在我们执行完sql后可以点击解释这个按钮，下面就会出现sql的各个性能的问题。这里面每一个字段的意思大家可以自己百度一下，我这边就主要讲最重要的几个字段吧。其实就是extra和type，当我们的extra出现如下的情况就证明mysql根本不能使用索引,效率会受到重大影响，需要优化

而还有一个就是type,type有以下几个参数:
1.const
如果是根据主键或唯一索引 只取出确定的一行数据。是最快的一种。
2.range
索引或主键，在某个范围内时
3.index
仅仅只有索引被扫描
4.all
全表扫描,这个是最差的情况
我们可以通过这种方式查看我们的sql的效率，我习惯每次写复杂的语句都使用这个命令测试一下。