开始
最近在网络上找到了一个关于sql优化的建议，现在针对这些建议去做一些测试。
1、对查询进行优化，应该尽量的避免全表扫描，考虑在where条件或者order by上面建立索引。
理解：《数据库索引设计与优化》这本书里面提到了三星索引的概念，在设计索引的时候尽可能的要满足三星索引的要求。
第一颗星，where后面的谓词条件能匹配到索引，而且要尽量的匹配一个窄的索引片。
第二颗星，排序要能用到索引，毕竟索引中的数据是一种有序的状态，可以利用索引避免排序。
第三颗星，覆盖索引，使用覆盖索引能大大的减少查询过程中回表的次数。回表对于数据库来说是一个开销较大的操作，往往意味着大量的随机读取，如果select的列通过索引就能获取，那么就只需要对这个索引进行扫描就好，不需要回表做查询。
2、在创建表的时候，如果确认里面的数据不需要存储null值，就应该使用not null约束。
理解：记得《高性能Mysql》中有一段关于not null的描述，Mysql是难以优化引用了可空列的查询，这样会使得查询更加复杂难以优化，况且在可以为null的列中，Mysql还需要额外的一个字节去存储该值是否为null。
3、应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用UNION合并查询：select id from t where num=10 union all select id from t where num=20
理解：对于这个建议，个人有一点点不同的看法。在这个例子中使用的都是num，等同于num in (10,20),不需要做其它的union操作。就算使用了不同的索引列比如A=1 or B=2问题也不大，在5.1之前的mysql中，是只能使用一个索引的，在这种情况下很显然需要把or拆成两个查询然后做并集操作。但是在5.1的版本中就已经引入了索引合并(index merge),所谓的索引合并就是利用多个索引分别进行扫描，然后将他们的结果进行合并（intersect/union） 在5.5~5.7的版本中均做过测试，在A=1 or B=2这种类型的SQL中，都可以用到索引合并技术
mysql> explain select * from country where country_id='14' or country='Zambia';
+----+-------------+---------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
| 1 | SIMPLE | country | NULL | index_merge | PRIMARY,idx_country | PRIMARY,idx_country | 2,152 | NULL | 2 | 100.00 | Using union(PRIMARY,idx_country); Using where |
+----+-------------+---------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
通过optimizer_trace的分析也可以看出，最终or在执行的时候是通过索引扫描完成的
4、in和not in也要慎用，否则会导致全表扫描，对于连续的数值，能用between就不要用in了：Select id from t where num between 1 and 3。 blog.csdn.net/u011781769/…
理解：创建测试数据
mh01@3306>show create table abc;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc | CREATE TABLE `abc` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` varchar(256) DEFAULT NULL,
KEY `c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
在对表进行explain select * from abc where c1 between 100 and 104;和explain select * from abc where c1 in (100,101,102,103,104);的时候，他们的执行计划都是一样的。（不贴图了）
分别使用optimizer_trace验证性能： 使用between的方式：
使用in的方式
5、下面的查询也将导致全表扫描：select id from t where name like‘%abc%’或者select id from t where name like‘%abc’
理解：这个是很显然的问题，不满足最左匹配原则。
6、如果在where子句中使用计算，也会导致全表扫描
理解：这个规范在《高性能Mysql》中有详细的说明，如果查询中的列不是独立的，则Mysql就不会使用索引。独立的列指的是索引列不能是表达式的一个部分，也不能是函数的参数。
mh01@3306>explain select * from sakila.country where country_id=10;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | country | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mh01@3306>explain select * from sakila.country where country_id + 1=11;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | country | NULL | ALL | NULL | NULL | NULL | NULL | 109 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
7、很多时候用exists代替in是一个好的选择：select num from a where num in(select num from b)。用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)
理解：对于exists和in来说，不能单纯的说用exists代替in就好好，主要还是要看两个表数据量的问题，核心的思想就是小表驱动大表。比如说在原文中提到的例子，如果说b的数据量要小于a表，那么in的方式就是合适的，因为in是先执行子查询里面的内容，类似于用子查询去驱动外层查询。
如果说a表的数据量要小于b表，那么就应该使用exists，对于exists来说，是用外层查询去驱动内部的查询。把外部的一条数据传递到内层查询中，如果成立就返回。
下面使用sakila示例数据库做一个简单的测试
mh01@3306>select count(*) from film_actor;
+----------+
| count(*) |
+----------+
| 5462 |
+----------+
1 row in set (0.01 sec)
mh01@3306>select count(*) from actor where last_name like 'A%';
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
在这种情况下如果想要获取last_name为A开头的人对应的film信息，就有两种方式，也就是用in或者是exists的方式。区别就是驱动表不同。现在测试一下两种方式的开销
使用in的方式：
使用exists的方式：
8、索引固然可以提高相应的select的效率，但同时也降低了insert及update的效率，因为insert或update时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
理解：太多的索引很显然对数据的插入有影响，毕竟需要额外维护索引，同时也需要更多的存储空间。但在《数据库索引设计与优化》这本书中提到一个思想，就是不应该对索引的个数有一个硬性的要求，如果对提高业务的RT时间由帮助（尤其是以读为主的业务），就算多几个索引也是没有关系的，优化的核心思想不在于CPU使用率下降了多少，IO繁忙度下降了多少，关键在于用户感知度也就是响应时间是否有提高。
在这里我简单的测试了一下索引过多对insert的影响：
--表结构如下：
CREATE TABLE `abc` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` varchar(256) DEFAULT NULL,
`c3` varchar(256) DEFAULT NULL,
`c4` varchar(256) DEFAULT NULL,
`c5` varchar(256) DEFAULT NULL,
`c6` varchar(256) DEFAULT NULL,
`c7` varchar(256) DEFAULT NULL,
`c8` varchar(256) DEFAULT NULL,
`c9` varchar(256) DEFAULT NULL,
`c10` varchar(256) DEFAULT NULL,
`c11` varchar(256) DEFAULT NULL,
KEY `c1` (`c1`));
在这个结构中插入100W的数据，使用mysqladmin监控到insert速度大约为14000
[root@mh01 ~]# mysqladmin ext -i 2 -r | grep -i Com_insert
| Com_insert | 13855 |
| Com_insert_select | 0 |
| Com_insert | 14963 |
| Com_insert_select | 0 |
| Com_insert | 12624 |
| Com_insert_select | 0 |
修改表结构，多加一些索引
CREATE TABLE `abc` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` varchar(256) DEFAULT NULL,
`c3` varchar(256) DEFAULT NULL,
`c4` varchar(256) DEFAULT NULL,
`c5` varchar(256) DEFAULT NULL,
`c6` varchar(256) DEFAULT NULL,
`c7` varchar(256) DEFAULT NULL,
`c8` varchar(256) DEFAULT NULL,
`c9` varchar(256) DEFAULT NULL,
`c10` varchar(256) DEFAULT NULL,
`c11` varchar(256) DEFAULT NULL,
KEY `c1` (`c1`),
KEY `c2` (`c2`),
KEY `c3` (`c3`),
KEY `c4` (`c4`),
KEY `c5` (`c5`),
KEY `c6` (`c6`),
KEY `c7` (`c7`),
KEY `c8` (`c8`),
KEY `c9` (`c9`),
KEY `c10` (`c10`),
KEY `c11` (`c11`)
);
此时会发现insert的速度有一个明显的下降
[root@mh01 ~]# mysqladmin ext -i 2 -r | grep -i Com_insert
| Com_insert | 1571723 |
| Com_insert_select | 1 |
| Com_insert | 10091 |
| Com_insert_select | 0 |
| Com_insert | 9939 |
| Com_insert_select | 0 |
| Com_insert | 10254 |
| Com_insert_select | 0 |
9、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
理解：如果是数字类型的数据就不要设计为字符类型，Mysql更加擅长处理数字类型的数据类型。另外，如果本身是数字类型的数据被定义为字符类型，那么该列做表关联的时候可能会发生数据类型转换导致索引失效。
10、尽可能的使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
理解：相对来说不是很赞同这个规范，作为一个一个变长的字段类型，在处理行长度相差较大的环境中有天生的优势，比如能节省空间，节省内存和CPU等等。但是varchar也有一些缺点，比如它更容易产生碎片等，而且更加浪费空间（对于行长度小且行长度变化不大的来说）因为他需要额外的一到两个字节来存储长度。总的来说如果应用具有如下特点可以使用varchar：
- 行长度变化很大的
- 变更不是太频繁的
以下特点的可以使用char：
- 定长的，比如就存一个Y、N，或者是MD5这种类型的数据
- 经常变更的
设计非变长字符的时候特别要注意考虑拓展性，在Mysql中做数据类型的变更是不能通过online ddl完成的，是采用copy + not allow dml方式创建的。
11、最好不要使用返回所有：select * from t，用具体的字段列表代替“*”，不要返回用不到的任何字段。
理解：自己能做的事情就不要交给Mysql了，Mysql在需要把*替换为真实的字段，而且*是肯定不能用到覆盖索引的
