Mysql查询优化

Mysql查询优化最常见方法是索引
一. 索引
对于不同的MySql存储引擎，索引实现细节不同，这里不展开了。
1).索引的优缺点
优点: 可以在查询时避免全表扫描，加快查询速度。不仅可以加快单表查询速度，对多表查询加速更加明显
缺点: 索引占物理空间，降低了大部分与写入有关的操作速度(因为写入操作要相应修改索引)
2).索引的选取

1. 为用于搜索，分组，排序的列创建索引（即最佳候选索引选取列位于where字句中的列，连接子句中的列，出现在order by,group by子句中的列）
2. 考虑数据列的基数。 相对于表里的总行数来说，列的基数越大（该列包含的唯一值多，重复值少），在该列创建索引效果越好。
3. 索引短小值（索引尽量选取较小的数据类型）。短小值在如下几个方面提高索引检索性能： a. 让比较操作更快；b. 让索引短小，减少磁盘IO请求；c. 对于短小的键值，键缓存里的索引块可以容纳更多的键值，也能减少IO请求次数。
4. 不要建立过多索引
5. 利用最左前缀。
6. 索引字符串值得前缀
7. 让参加匹配得索引类型保持匹配
   二. 选择有利于高效查询的数据类型

1. 多用数字少用字符串运算
2. 当较小数据类型够用时就不用较大数据类型
3. 把数据列声明成not null
4. 考虑使用 ENUM 列
5. 整理表碎片。 碎片是有害的，会造成磁盘存储空间浪费。适用于各种存储引擎的碎片整理方法是：先用mysqldump转储表，然后再利用这个转储文件重建它。
   例如：

mysqldump db_name  table_name >  dump.sql
mysql db_name <  dump.sql

6. 把数据压缩到ELOB或TEXT列
   7）使用合成索引
   8）尽量避免检索很大的ELOB或TEXT列
   三. 选择有利于高效查询的表格式
   四. 高效加载数据
   1）LOAD DATA 效率比 INSERT高
   2）当必须用 INSERT 时，如果有多条插入语句，可以使用如下方式优化：
   a) 对于支持事务的存储引擎，可以在单个事务里执行这些SQL语句

start transiction;
insert into .......;
insert into .......;
...
insert into .......;
commit;

b) 对于不支持事务的存储引擎，可以先占用对表的写入锁，在表被锁定时执行insert语句。

lock tables table_name write;
insert into .......;
insert into .......;
...
insert into .......;
unlock tables;

以上两种方式只有在所有的sql语句执行完后才会刷新索引，而不是每个insert执行完后刷新一次，这样性能就提升了。
五. 调度，锁定和并发
Mysql的调度策略总结如下：
a) 写入优先级比读取高
b) 表的写入操作一次只能进行一个，多个写入操作按其到达的先后次序依次处理
c) 读取操作可以同时进行
InnoDB采用行级锁实现这种策略，但只在必要时候才会锁定行。