mysql查询优化

1. 索引按顺序排列存储
2. 选择索引
　　搜索的索引列，不一定是所要选择的列。出现在ON，WHERE和GROUP BY后的列适合作索引。
　　使用惟一索引
　　　　对于惟一值的列，索引的效果最好。例如：存放年龄的列具有不同值，所以很容易区分各行；而用来记录性别的列，只有’M'和’F'，不论搜索哪个值，都会得出大约一半的行，所以对其进行索引没有多大用途。
　　使用短索引
　　　　如果对字符串类型的列进行索引，应该指定一个前辍长度。比如一个varchar(200)的列，最好指定索引为前10个或20个字符内。（短的索引节省存储空间，并可能使查询更快）
　　利用最左前辍
　　　　在创建一个包含n列的索引时，实际上是创建了MySQL可以使用的n个索引。
多列索引可起几个索引的作用，因为可利用索引中最左边的列来匹配行。这样的列的集称为最左前辍。

　　比如在一个表中的state, city和zip三个列上创建索引，那么索引中的行是按state/city/zip的次序存放的。因此，索引中的行也会自动按state/city的顺序和state的顺序来存放。所以，该索引可以用来搜索下列的列的组合：
　　　　state/city/zip
　　　　state/city
　　　　state

　　不要过度索引
　　考虑在列上进行的比较类型
　　　　索引可用于<, <=, =, >=, >和BETWEEN运算，也可以用于LIKE运算。对于其他类型的计算（如STRCP()），则索引不起作用。

3. MySQL查询优化程序
　　EXPLAIN SELECT * FROM student WHERE 1=0;

　　3.1 优化程序怎样工作
　　　　MySQL查询优化程序有几个目标，但主要目标是尽量利用索引，而且尽量使用最具有限制性的索引以排除尽可能多的行。
　　　　比如：WHERE col1=’aaa’ AND col2=’bbb’，col1和col2都是索引。
　　　　假设整个表内，满足这col1=’aaa’的行有900行，满足col2=’bbb’的行有300行，两个条件都满足的行有30行。
　　　　那么，如果首先测试col1，必须检查900行以找到同时与col2值相符的30行，那么在测试col2时，有870行会失败。如果首先测试 col2，要找到同时与col1相符的30行，只需要检测300行，这个过程中会有270行失败，这样所涉及的计算较少，磁盘I/O也较少，所以相对来说更快。

　　　　遵循下列准则，有助于优化程序利用索引：
　　　　　　a. 比较具有相同类型的列。
　　　　　　b. 比较中应尽量使用索引列独立。两个例子：
　　　　　　　　1. 比如WHERE col1 < 4 / 2的效果好于WHERE col1 * 2 < 4，后者不会使用索引，且会扫描表中所有行并进行计算。
　　　　　　　　2. 某表中的一个索引列date_col，日期类型
　　　　　　　　　　对于查询WHERE YEAR(date_col) < 1990，并不会使用索引与1990比较，而是将从列值计算出的值与1990比较，而且必须计算每一行。索引没有得到应用。
　　　　　　　　　　改进：WHERE date_col < ‘1990-01-01’

――――――――――――――――――――――――――――
但如果没有特定的日期值，比如要查询到今天为止100天内的记录，有3条语句可以完成这个任务
WHERE TO_DAYS(date_col) – TO_DAYS(CURRENT_DATE) < 100
WHERE TO_DAYS(date_col) < 100 + TO_DAYS(CURRENT_DATE)
WHERE date_col < DATE_ADD(CURRENT_DATE, INTERVAL 100 DAY)

第一条查询不能利用索引，因为必须检索每一行，以计算TO_DAYS(date_col)。
第二条查询要好一些，100和TO_DAYS(CURRENT_DATE)都是常量，因为表达式右边的值可以在查询处理前由优化程序一次计算出来，而不是每行计算一次，但date_col仍然在函数中，所以仍然没有使用索引。
第三条查询是最好的方法，表达式右边会在查询处理前一次性计算出来，其值是一个日期，可以直接和date_col比较，不需要再转换为天数，索引得到利用。

　　　　　　c. 在LIKE模式的起始处不要使用通配符。
　　　　　　　　查询WHERE name LIKE ‘%zhang%’的效率是很差的。
　　　　　　　　如果要查询以Mac开始的行，可以写成WHERE name LIKE ‘Mac%’，
　　　　　　　　但是WHERE name >= ‘Mac’ AND name < ‘Mad’的效率好于前者。

4. 列类型的选择与查询效率
　　a. 使用定长列，不使用可变长列。特别对于经常修改的表，变长列更容易产生碎片。
　　b. 在较短的列能够满足要求时不要使用较长的列
　　　　比如能使用CHAR(40)就不要使用CHAR(60)，好处：节省空间、节省I/O操作时间。
　　c. 将列定义为NOT NULL
　　　　这样处理速度更快，所需空间也更少。而且有时还能简化查询，因为不需要检查是否存在行例NULL。
　　d. 考虑使用ENUM列
　　　　如果某列的值的数量有限，应该考虑将其转换为ENUM类型。ENUM在内部使用数值表示，具有更快的处理速度。
　　e. 使用PROCEDURE ANALYSE()
　　　　比如SELECT * FROM commodity PROCEDURE ANALYSE();
　　　　会告诉你该列的最大值、最小值、平均值，以及推荐的列类型等等（主要是ENUM）。
　　f. 对容易产生碎片的表使用OPTIMIZE TABLE
　　　　以常进行修改的表、特别包含了变长列（特别是BLOB类型）的表，容易产生碎片。
　　g. 除非需要，应避免检索BLOB或TEXT值　――节省网络传输时间
　　h. 将BLOB或TEXT列分离到一个独立的表中
　　　　在某些情况下，将BLOB或TEXT列从表中移出可能具有一定意义，比如可将剩下的字段设置为定长格式，可以减少碎片，加快处理速度。

5. 有效地装载数据
　　基本理论：
　　a. 成批装载比单行装载更快，因为不需要在装载每个记录后就刷新索引。
　　b. 在表无索引的时候装载比有索引装载快，因为有索引的时候不仅需要写到数据文件，还需要写到索引文件。
　　c. 较短的语句比较长的语句快，因为服务器分析较少，网络传输量也较少。

　　实际结论：
　　a. LOAD DATA比INSERT效率高。
　　b. LOAD DATE比LOAD DATA LOCAL效率高。因为使用LOAD DATA，文件必须在服务器上（需要有FILE权限），节省了网络传输时间。
　　c. 如果必须使用INSERT，应该使用其多行插入形式。比如：
　　　　INSERT INTO student VALUES (1, ‘AAA’), (2, ‘BBB’), …
　　　　这样会减少索引创建的次数，也可以减少网络传送SQL语句的时间。

　　　　如果使用mysqldump来生成SQL备份文件，应该使用—extended-insert选项，使备份文件生成为多行插入形式。或者使用—opt参数。
　　d. 使用压缩参数。当需要在客户机/服务器间传输数据时，对于大多数客户机，可以使用—compress参数。但一般只用于较慢的网络，因为—compress参数需要占用更多的处理器时间。
　　e. 让MySQL来插入缺省值　――减少传输时间和服务器分析语句时间。
　　f. 在装载大量数据之前不要建立索引，待装载完成后再建立索引。或者在装载前删除索引，完成后再重建。