MySQL索引使用

MySQL slow_log(slow.log?) 定位查询速度慢的SQL语句。

 

索引的存储分类：

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

InnoDB表的数据和索引时存储在同一个表空间里面，但可以由多个文件组成。

MyISAM已做优化查询速度高于InnoDB；在涉及到外键、事务处理时使用InnoDB。

 

/*查看索引*/
SHOW INDEX FROM tbl_name;
/*增加索引*/
ALTER TABLE tbl_name ADD INDEX index_name(column_name);
/*删除索引*/
ALTER TABLE tbl_name DROP INDEX index_name;

 

使用索引：
①使用LIKE的查询，后面如果是常量并且只有"%"不在第一个字符，索引才可能被使用。
②对大的文本进行搜索，使用全文索引而不使用"%……%"
③如果列名是索引，使用column_name IS NULL将使用索引。

 

存在索引但不使用

①MySQL估计使用索引比全表扫描更慢，则不使用索引。如column_name均匀分布在1到100之间，查询时使用索引就不是很好:

SELECT * FROM table_name WHERE column_name>1 AND column_name<100;

②如果使用MEMORY/HEAP表并且WHERE条件中不使用"="进行索引，那么不会用到索引。HEAP表只有在"="的条件下会使用索引。

③用OR分割开，如果OR前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到，前后都要加入索引；使用AND连接查询条件，若查询条件为false，索引不会被用到。

④复合索引:(column_a, column_b)在WHERE column_a = x / WHERE column_a = x OR column_b = y时会用到索引，在WHERE column_b = y 时不会用到索引。 

⑤查询条件中“=”右边使用的数据类型与定义表时使用的数据类型不一致时，不会用到索引。

CREATE TABLE t (
`id` INT AUTO_INCREMENT,
`name` VARCHAR(30),
`age` VARCHAR(3),
PRIMARY KEY (`id`),
INDEX idx_age(`age`)
);
SELECT * FROM t WHERE age = 1;　　/*不会用到索引。*/
SELECT * FROM t WHERE age = '1';　　/*会用到索引。*/

 

查看索引使用情况：

SHOW STATUS LIKE 'Handler_read%';

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

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