Mysql 索引优化

1、索引的存储分类

MyISAM存储引擎的表数据和索引是自动分开存储的，各自是独一的一个文件

Innodb存储引擎的表数据和索引是存储在同一个表空间里面，但可以由多个文件构成。

Mysql 目前不支持函数索引，但是能对列的前面某一部分进行索引

例如name字段，可以只取name的前4个字符进行索引，可降低索引文件大小。

Mysql> create index ind_company_name on company(name(4));

说明：ind_company_name索引名，company表名

2、使用like的查询，后面如果是常量，并且%号不在第一个字符，索引才可能被用上

eg: Mysql> select * from t1 where id like "3%"; 推荐

      Mysql> select * from t1 where id like "3%"; 不推荐 (索引用不上)

3、如果对大的文本进行索引，使用全文索引而不使用like"%....%"

4、如果列名是索引，使用column_name is Null 将使用索引。

eg: Mysql> desc select * from company where name is null\G

5、存在索引但不使用索引

6、如果Mysql估计使用索引比全表扫描更慢，则不使用索引。

例如：如果列Key_part均匀的分布在1-100之间，查询时使用索引就不好。

eg：Mysql> select * from table_name where key_part>1 and key_part<90;

7、如果使用MEMORY/HEAP 表并且where条件中不适用“=”进行索引列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。

8、用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被使用。

9、如果不是索引列的第一部分，那么查询中这个索引也不会被Mysql采用。

eg: Mysql> desc select * from sales where moneys=1\G

Moneys上面建有复合索引，但是moneys不是索引的第一列。

10、如果列类型是字符串，但是查询时把一个数值型常量复制给了一个字符型的列名name，那么虽然name列有索引，也不会使用。

eg: Mysql> desc select * from company where name=123\G  (字符串索引使用整形数据查询，不会使用索引)

      Mysql> desc select * from company where name="123"\G (使用上索引)

查看Mysql索引使用情况

Mysql> show status like 'Handle_read%';

如果索引正在工作，Handler_read_key的值将很高，这个值代表索引被使用次数。

Handler_read_rnd_next的值高意味着查询运行低效，并且应该建立索引补救。

2个简单实用的优化技巧

a、定期分析表和检查表

Mysql> ANALYZE  [LOCAL | NO_WRITE_TO_BINLOG] TABLE tab_name [,tab_name1]...;

本语句用于分析和存储表的关键字分布，分析结果可以使系统得到准确的统计信息。使SQL能够生成正确的行计划。

eg: Mysql> analyze table sales;

分析表的语法如下: (检查一个或多个表是否有错误)

Mysql> CHECK TABLE tab_name [,tab1_name]...[option]...option={QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

eg: Mysql> check table sales;

b、定期优化表

优化表的语法格式：

Mysql> OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] tab_name [,tab_name1]..

如果已经删除了表的一大部分，或者已经对含有可变长度行的表进行了很多的改动，则需要定期优化。

这个命令可以将表中的空间碎片进行合并，但是只对MyISAM、BDB和Innodb表起作用。

eg: Mysql> optimize table sales;   (不要在Mysql访问数大的情况下使用)

常用SQl的优化 

大批量插入数据

当用load命令导入数据的时候，适当设置可以提高导入速度

对于MyIASM存储引擎的表，可以用个一下方式快速导入大量数据

ALTER TABLE Tab_name DISABLE KEYS loading the data

ALTER TABLE Tab_name ENABLE KEYS

DISABLE KEYS 和 ENABLE KEYS 用来打开或关闭MyISAM表非唯一索引的更新，可以提高速度，对Innodb无效。