mysql优化-索引

mysql优化-索引

索引的作用

合理的创建索引是提高查询速度最有效的方式之一。
在没有建立索引的情况下，查询一条数据的过程是这样：
MySQL是从第一条记录开始查表，在某一个地方查到了匹配的数据，停止？NO，继续查询剩下的数据，直到查完整张表。
在建立合适索引的情况下，查询一条数据的过程又是这样：
MySQL先根据索引快速到达一个位置去搜索数据文件，然后查出数据，而不必查看整张表的数据。
因此，为了提高查询速度，合理建立索引是非常有必要的。

索引的分类

常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

类型	描述
普通索引	基本的索引类型，允许定义索引的列中插入重复值和空值
唯一索引	索引列值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
主键索引	特殊的唯一索引，不允许有空值
全文索引	类型为FULLTEXT,支持值的全文查找，允许在索引列中插入重复值和空值
组合索引	在表的多个字段组合上创建的索引

索引的建立

1、普通索引：可以在创建表的时候创建索引，也可以在表创建完成之后进行索引的添加

CREATE TABLE `testtable` (
    `id` INT NULL,
    `name` VARCHAR(50) NULL,
    INDEX name (name(50))
);

ALTER table `testtable` ADD INDEX name(name)

2、唯一索引：可以在创建表的时候创建索引，也可以在表创建完成之后进行索引的添加

CREATE TABLE `testtable`(  
    `id` INT NOT NULL,
    `name` VARCHAR(50) NOT NULL,  
 UNIQUE name (name(50))  
 ); 

ALTER table testtable ADD UNIQUE name (name(50))

3、全文索引：可以在创建表的时候创建索引，也可以在表创建完成之后进行索引的添加

CREATE TABLE `testtable` (
    `id` INT NULL,
    `name` VARCHAR(50) NULL,
    FULLTEXT name (name(50))
);

ALTER table testtable ADD FULLTEXT name (name(50))

4、复合索引：可以在创建表的时候创建索引，也可以在表创建完成之后进行索引的添加

CREATE TABLE `testtable` (
    `id` INT NULL,
    `name` VARCHAR(50) NULL,
    `nick` VARCHAR(50) NULL,
    INDEX index_name ( `name`, `nick` )
);

ALTER TABLE `testtable` ADD INDEX index_name ( `name`, `nick` )

索引的查看

SHOW INDEX FROM testtable

索引的删除

ALTER TABLE `testtable`
    DROP INDEX `name`;

索引的设计原则及优劣

1.索引不是越多越好，一个表中如果存在大量的索引，不仅会占用磁盘空间，还会影响插入，修改和删除操作。因为当表中数据更新时，索引也会进行调整和更新。
2.避免对经常更新的表进行过多的索引，并且索引中的列尽可能的少。
3.对经常用于查询的字段应该建立索引，但是避免增加不必要字段。
4.在条件表达式中经常用到的不同值较多的列上添加索引，在不同值较少的列不要建立索引，这样不但不会提高查询效率，反而会严重降低更新速度。
5.当唯一性是数据本身的特征时，指定唯一索引。
6.在频繁进行排序和分组的列上建立索引。如group by 和order by
7.对于待排序的列是多个的时候，可以在这些列上增加索引。

如何使用索引

1.查看建立的索引有没有被使用，用EXPLAIN进行分析。

explain 
select id from testtable where name = 'test7'

单纯为了验证索引有没有被添加，直接关注 key就好。

总结

简单的了解MySQL索引，学会对索引的基本操作，建立索引的基本原则。对于索引的实际应用应该注意的很多细节以及正确的使用方式有待总结。