Mysql优化002
索引
索引分类:
主键索引(primary key 默认会添加索引)
唯一索引 (unique关键字修饰的)
组合索引 alert table test add INDEX `indexname` (列1,列2)
全文索引 (innodb不支持 MyISAM支持)
CREATE TABLE `article` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `content` text, PRIMARY KEY (`id`), FULLTEXT KEY `title` (`title`,`content`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);
使用
SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’);
普通索引 CREATE (unique) INDEX index_name ON table_name (column_name)
执行计划:
explain select * from tablename where name='ljx';
type:rel->使用索引进行查找 all->全表扫描