mysql调优

• 使用正确合适的数据类型，数字类型不要使用字符类型代替。
• select后面的 * 尽量使用具体字段代替。
• where子句后面尽量避免使用!=、<>，否则引擎将放弃使用索引而使用全表扫描。
• where子句后面尽量避免使用or 可使用两个查询用union all做连接。
• where子句后面尽量避免in 、not in语句以避免全表扫描。能用between尽量不用in。能用exists尽量不用in。
• where子句后面尽量避免表达式的操作、避免函数操作。
• where子句中尽量避免 is null、is not null 。如果可能尽量为列设置为not null。
• 尽量避免大事务操作，提高系统并发性。
• 新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免造成大量log，以提高速度。
• 索引建立规则：

where子句的条件可以加索引，order by字段可以加索引。     

只有两个值比如性别的列不宜建立索引。

    

• 正确使用索引innodb和myisam默认使用的都是Btree索引。

联合索引的生效规则，从前往后依次生效，如果中间某个索引没有生效那么断点前面的索引生效，断点后面的索引不会生效。

例如联合索引(a,b,c)

where a=5 and b=6 and c=7； 三个索引顺序使用中间没有断点，全部生效；
where a=5 and c=7； 这种情况b就是断点，a生效，c不生效；
where b=6 and c=7； 这种情况a就是断点，b、c都不生效；
where b=6 and c=7 and a=5； mysql会自动对应查询条件的顺序，a、b、c索引都生效；
where a=5 and b>6 and c=7； a生效，b生效b处有断点，c不生效；
where a=5 order by b； a生效，b生效；
where a=5 order by c； a生效，c不生效；
where b=6 order by a； a、b都不生效；

• 存储引擎的优化

Innodb 是mysqlserver5.5以后的默认存储引擎，支持提交、回滚、崩溃恢复的事务的存储引擎，支持行锁定和外键，sql查询中可以自由的将Innodb类型的表与其他类型的表关联。 Innodb是为处理巨大数据量的最优设计，他的cpu效率是任何其他基于磁盘的关系型数据库引擎所不能匹敌的。使用Innodb时mysql将会创建ibdata1为10M的自动扩展数据文件，以及ib_logfile0和ib_logfile1的5M的两个日志文件。

MyIsam引擎拥有较高的插入、查询速度，不支持事务，使用MyIsam将创建三个文件：frm文件存储表定义、MYD文件存储数据、MYI文件存储索引。

其他mysql数据库存储引擎还有Memory、CSV等

• mysql单表百万数据记录分页优化

问题：表orders中各种字段类型都有varchar、datetime、text等，id字段为主键，表中有88万条数据，单表大小4.2G，越往后查询越慢。

select * from orders limit 10,20; ---0.015秒
select * from orders limit 1000,20; ---0.049秒
select * from orders limit 400000,20; ---3.162秒
select * from orders limit 850000,20; ---36.222秒  

解决办法（Myisam引擎）：利用表的覆盖索引来加速分页查询，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引)，那么这种情况会查询很快。因为利用

索引查询有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，mysql中也有相关的索引缓存，在并发高的时候利用缓存效果更好。

select * from orders where id>=(select id from orders limit 850000,1) limit 20; ---0.286秒

  mysql索引类型

普通索引

create index indexname on mytable(username(length));

如果是CHAR、VARCHAR类型，length可以小于实际长度；如果是blob和text类型，必须指定length

唯一索引

唯一索引与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。

create unique index indexname on mytable(username(length));

主键索引

创建主键时自动添加主键索引：非空、唯一

联合索引

alter table mytable add index name_city_age(name(10),city,age);