MYSQL索引的概念
mysql索引是一种特殊文件(InnoDB数据表上的索引是表空间的一个组成部分),能加快数据库的查询时间。
索引分为聚族索引和非聚族索引,聚族索引是按照数据存放的物理位置为顺序的,能提高多行检索的速度,而非聚族索引对于单行的检索很快。
Mysql索引类型
1、普通索引
最基本的索引,没有任何限制。
(1)直接创建索引
| CREATE INDEX index_name ON table ( column (length)) |
| (2)修改表结构的方式添加索引 |
| ALTER TABLE table_name ADD INDEX index_name ON ( column (length)) |
| (3)创建表的时候同时创建索引 |
| (4)删除索引 |
| DROP INDEX index_name ON table |
| (1)创建唯一索引 |
| CREATE UNIQUE INDEX indexName ON table ( column (length)) |
| (2)修改表结构 |
| ALTER TABLE table_name ADD UNIQUE indexName ON ( column (length)) |
| (3)创建表的时候直接指定 |
MySQL从3.23.23版开始支持全文索引和全文检索,FULLTEXT索引仅可用于 MyISAM 表;
可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,
或是随后使用ALTER TABLE 或CREATE INDEX被添加。
一般的数据库搜索都是用的SQL的 like 语句,like 语句是不能利用索引的,每次查询都是从第一条遍历至最后一条,查询效率极其低下。
一般数据超过10万或者在线人数过多,like查询都会导致数据库崩溃。
这也就是为什么很多程序都只提供标题搜索的原因了,因为如果搜索内容,那就更慢了,几万数据就跑不动了。
Mysql 全文索引是专门为了解决模糊查询提供的,可以对整篇文章预先按照词进行索引,搜索效率高,能够支持百万级的数据检索。
| (1)创建表的适合添加全文索引 |
| (2)修改表结构添加全文索引 |
| ALTER TABLE article ADD FULLTEXT index_content(content) |
| (3)直接创建索引 |
| CREATE FULLTEXT INDEX index_content ON article(content) |
4、单列索引与多列索引
多个单列索引与单个多列索引的查询效果不同
5、组合索引(最左前缀)
平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。
t_index_id_replynum_status 组合索引
相当于建立了id单列索引和id_replynum、id_replynum_status多列索引(和索引实现有关)
索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。
因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。
索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。