Mysql 优化与测试
由于经常被抓取文章内容，在此附上博客文章网址：，偶尔会更新某些出错的数据或文字，建议到我博客地址 : --> 点击这里
以下的测试数据根据环境的不同所耗费的时间有所不同，例如我在腾讯云上的测试，以及在本机的测试速度相差三倍。但当环境因素一样的时候，优化sql能够带来显著的提升。
测试表数据：3百万条数据，12个字段，存储引擎：myisam
1 确定搜索一条记录的时候，加上limit 1 ，以此让游标查到第一条结果时停止，不需要遍历下面的结果
使用这样子数据递增的varchar字段，在最优情况下，结果在第一条：
SQL ： select * from tbl_game_user_test where channel= 'jieduan1';
TIME ： 1.292 s
而当我们加上了limit 1 ：
SQL : select * from tbl_game_user_test where channel= 'jieduan1' limit 1;
TIME : 0.0001 s
对比上面，提高了很多。而在最坏情况下，结果在最后一条，那么两者使用时间是一致的。
而当我们explain的时候，两者是一致的：
+----+-------------+--------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | tbl_game_user_test | ALL | NULL | NULL | NULL | NULL | 3006343 | Using where |
+----+-------------+--------------------+------+---------------+------+---------+------+---------+-------------+
2 like查询以%开头索引会失效，没有命中索引，查询效率差别是很大的。而%在结尾，可以命中索引
未优化的sql：
SQL：select * from tbl_game_user_test where deviceID LIKE '%deviceID1111111'
TIME : 2.342 s
而当我们将%放到末尾：
SQL : select * from tbl_game_user_test where deviceID LIKE 'deviceID1111111%';
TIME : 0.001 s
花费的时间相差挺大
3 对于分页的优化，可以采用覆盖索引的原理来优化。
在数据量不大的时候，limit 分页效率差别不大，当数据量大的时候，数据越大，效率差距越大，因此我们可以采用覆盖索引的原理，来优化sql。
未优化前SQL：
SELECT * FROM tbl_game_user_test ORDER BY id LIMIT 1000000,10
Times ： 4.55 s
而优化的SQL：
select * from tbl_game_user_test JOIN (SELECT id FROM tbl_game_user_test ORDER BY id LIMIT 100000,10)a USING(id)
Times ： 0.014s
我们把第二种检索方法叫做延迟关联，因为延迟了对列的访问。在查询的第一阶段MySQL可以使用覆盖索引，在FROM字句的查询中找到匹配的id，然后根据这些id值在外层查询匹配获取需要的列值
上述的语句，我们也可以这么写：
select * from tbl_game_user_test where id >=(SELECT id FROM tbl_game_user_test ORDER BY id LIMIT 100000,1) ORDER BY id limit 10
4 永远为每张表设置一个ID
我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的 AUTO_INCREMENT标志。
就算是你 users 表有一个主键叫 “email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。
而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……
5 合适的存储引擎
InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。
同样的情况下，执行相同的查询，myisam 比innodb 快接近4倍；
存储引擎 ： myisam SQL ：select * from tbl_game_user_test where channel = 'jieduan65'; time ： 1.2 s
而 ： innodb 是 4.6s 【平均】
6 Mysql SQL Mode 可以禁止截断保存操作
当我们对一个varchar(3)的字段进行更新操作：
update tbl_game_user_test set platform = 'jieduanTest' where id = 1;
我们发现插入的字符被自动截断了，
存入的是：
jie
出现这种情况往往会发生一些意料之外的事情，等到发生了，再来找问题，再扩大字段长度就比较晚。我们可以在长度超过限制就报错，通过报错来提前报警。
可以通过：
set session sql_mode='STRICT_TRANS_TABLES'
就会提示：
[Err] 1406 - Data too long for column 'platform' at row 1
7 负向条件查询不能使用索引
select * from order where status!=0 and stauts!=1
not in/not exists都不是好习惯
可以优化为in查询：
select * from order where status in(2,3)
8 优化order by 语句
在某些情况中，MySQL 可以使用一个索引来满足ORDER BY 子句，而不需要额外的排序。where 条件和order by 使用相同的索引，并且order by 的顺序和索引顺序相同，并且order by 的字段都是升序或者都是降序。
例如：下列sql 可以使用索引。
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
但是以下情况不使用索引：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC； --order by 的字段混合ASC 和DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1； --用于查询行的关键字与ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2； --对不同的关键字使用ORDER BY：
其他优化小技巧：
1 字符字段只建前缀索引
2 导入大数据量时，先删除索引，导入数据，再创建索引
3 在设计表的时候，可以按照定长字段与变长字段相分离，分开两个表存放。例如论坛标题是我们经常获取的，而论坛个人创建时间等，几乎是不会去看，这种我们可以放于几乎不查的表中。同时，我们也可以把较长内容的字段独立存放于一个表中。
4 冗余字段。很多时候我们设计表都要遵守第一，二，三范式，但有时我们为了加快查询，而应该添加冗余字段。例如论坛栏目表中，一般而言，我们会有个栏目表，有文章表，而当我们获取该栏目有多少篇文章的时候，联表查询显得稍费时间，而我们可以在栏目表添加一个文章数量字段，每次添加或者删除都更新一下该字段。
