mysql优化

[kekobin]

优化准则

1.应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。
2.在建有索引的字段上尽量不要使用函数进行操作
3.在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的
4.应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
5.应尽量避免在 where 子句中对字段进行 null 值判断(应该尽量把字段设置为NOTNULL)，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0
6.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
7.应尽量避免在 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
8.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
10.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
11.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
12.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
13.尽量避免大事务操作，提高系统并发能力。
12.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

mysql百万级数据查询优化1

1.两种查询引擎查询速度（myIsam 引擎 ）

InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行。

MyISAM只要简单的读出保存好的行数即可。

注意的是，当count()语句包含 where条件时，两种表的操作有些不同，InnoDB类型的表用count()或者count(主键)，加上where col 条件。其中col列是表的主键之外的其他具有唯一约束索引的列。这样查询时速度会很快。就是可以避免全表扫描。

总结：
mysql 在300万条数据（myisam引擎）情况下使用 count(*) 进行数据总数查询包含条件（正确设置索引）运行时间正常。对于经常进行读取的数据我们建议使用myIsam引擎。

2.百万数据下mysql分页问题
在开发过程中我们经常会使用分页，核心技术是使用limit进行数据的读取，在使用limit进行分页的测试过程中，得到以下数据：

我们惊讶的发现mysql在数据量大的情况下分页起点越大查询速度越慢，100万条起的查询速度已经需要7秒钟。这是一个我们无法接受的数值！

改进方案 1

查询时间 0.365秒，提升效率是非常明显的！！原理是什么呢？？？

我们使用条件对id进行了筛选，在子查询 (select id from news order by id desc limit 1000000, 1) 中我们只查询了id这一个字段比起select * 或 select 多个字段 节省了大量的查询开销！

改进方案2
适合id连续的系统，速度极快！

不适合带有条件的、id不连续的查询。速度非常快！

3. 百万数据下mysql条件查询、分页查询的注意事项
   接上一节，我们加上查询条件：
   查询时间 20 秒

好恐怖的速度！！利用第一节知识进行优化：

查询时间 15 秒

优化效果不明显，条件带来的影响还是很大！在这样的情况下无论我们怎么去优化sql语句就无法解决运行效率问题。那么换个思路：建立一个索引表，只记录文章的id、分类信息，我们将文章内容这个大字段分割出去。

表 news2 [ 文章表 引擎 myisam 字符集 utf-8 ]

---

id int 11 主键自动增加

cate int 11 索引

在写入数据时将2张表同步，查询是则可以使用news2 来进行条件查询：

注意条件 id > 后面使用了news2 这张表！

运行时间 1.23秒，我们可以看到运行时间缩减了近20倍！！数据在10万左右是查询时间可以保持在0.5秒左右，是一个逐步接近我们能够容忍的值！

但是1秒对于服务器来说依然是一个不能接受的值！！还有什么可以优化的办法吗？？我们尝试了一个伟大的变化：

将 news2 的存储引擎改变为innodb，执行结果是惊人的！

只需要 0.2秒，非常棒的速度。

MySQL性能优化的一些经验

• 学会使用EXPLAIN
• 当只要一行数据时使用LIMIT 1
• 避免SELECT *

永远小表驱动大表

in 和 exists 选择：