mysql索引及其优化

1、索引:类似于书本的目录,能够加快数据库的查询速度。但索引也并不是越多越好,建立太多的索引会影响更新和插入的速度。对于一个经常更新和插入的表,也没有必要为了一个很少使用的where字句而单独建立一个索引,对于比较小的表,排序的开销不会很大,所以也没有必要建立另外的索引。

普通索引:由关键字KEY或INDEX定义的索引,唯一的任务是加快对数据的访问速度,所以应该只为那些经常出现在查询条件(where colum=)和排序条件的(order by cloum)中的数据列建立该索引。

- –直接创建索引(length表示使用名称前1ength个字符)

- CREATE INDEX index_name ON table_name(column_name(length))

- –修改表结构的方式添加索引

- ALTER TABLE table_name ADD INDEX index_name ON (column_name)

- –创建表的时候同时创建索引

- CREATE TABLE `table_name` (

- `id` int(11) NOT NULL AUTO_INCREMENT ,

- `title` char(255) NOT NULL ,

- PRIMARY KEY (`id`),

- INDEX index_name (title)

- ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

- –删除索引

- DROP INDEX index_name ON table_name;

-

- 建立复合索引 。

- CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);

-   注意命名时的习惯了吗?使用"表名_字段1名_字段2名"的方式

唯一索引:与普通索引类似,但有一点不同:唯一索引列的值必须唯一,可以有空值,若是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。只要能确定某列的值各不相同(如email),在创建该列时就应该用UNIQUE关键字把他定义为唯一索引。

唯一索引的好处:

1、提高访问速度

2、mysql在有新数据插入表时,会自动检查该数据在唯一字段的值是否会重复,如果重复,mysql将拒绝插入该数据,也就是唯一索引可以保证数据记录的唯一性。事实上,很多时候,唯一索引仅仅是为了避免数据重复。

- –创建唯一索引

- CREATE UNIQUE INDEX index_name ON table_name(column_name)

- –修改表结构

- ALTER TABLE table_name ADD UNIQUE index_name ON (column_name)

- –创建表的时候直接指定

- CREATE TABLE `table_name` (

- `id` int(11) NOT NULL AUTO_INCREMENT ,

- `title` char(255) NOT NULL ,

- PRIMARY KEY (`id`),

- UNIQUE index_name (title)

- );

主键索引:主键索引是一个特殊的唯一索引,他与唯一索引的区别是:主键索引使用PRIMARY定义而不是用UNIQUE定义

外键索引:用于建立和加强2个表数据间链接的一列或者多列。外键约束主要用于维护2个表数据之间的一致性,简单说,一个表的外键就是另一个表的主键。所以要删除一个表的主键时,要先确定该主键不是另一个表的外键。

全文索引:只能用于MyISAM表,适用于LIKE等模糊查询条件,当你需要检索那些包含一个或者多个给定单词的数据时,创建全文索引是一个不错的选择。但是,对于大数据集,生成全文索引非常的耗费时间和硬盘。

- –创建表的适合添加全文索引

- CREATE TABLE `table_name` (

- `id` int(11) NOT NULL AUTO_INCREMENT ,

- `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

- PRIMARY KEY (`id`),

- FULLTEXT (content)

- );

- –修改表结构添加全文索引

- ALTER TABLE table_name ADD FULLTEXT index_name(column_name)

- –直接创建索引

- CREATE FULLTEXT INDEX index_name ON table_name (column_name)

组合(复合)索引:组合索引又叫联合索引,对于组合索引:mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部分字段,但只能使用最左侧的部分--“最左缀”eg:一个联合索引key index(a,b,c),就可以支持a|a,b|a,b,c三种组合方式进行查询,但不支持b,c进行查找,所以当最左侧的字段是常量引用时,联合索引就非常有用。所以创建联合索引时,应该仔细考虑列的顺序,如果是对索引中的所有列或者前机列进行查询,那么联合索引就非常有用,如果只对后面任意列进行查询,那么联合索引的效果是非常差的。


2、索引的优化

虽然MySQL索引能大大加快查询速度,但同时会降低表的更新速度(如对表进行INSERT,UPDATE和DELETE操作),所以如何适当的使用索引是一个问题,下面是网上一些总结的索引优化的方法:

1、不要对有NULL值的列使用索引。

2、使用短索引:对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3、索引列排序;mysql只使用一个索引,因此如果在where字句中使用了索引,那么order by字句中的列就不会索引,所以在mysql默认排序能满足要求的情况下不要使用排序操作,尽量不要包含多个列的排序,如果需要,最好对这些列使用复合索引。

4、一般情况下不要使用like查询,如果必须使用,如何使用也是一个问题。like '%aaa%'不会使用索引而like 'aaa%'能够使用索引。

5、当where子句中有“!=”或者where子句中使用了mysql函数时,索引将无效,例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate<’2007-01-01′。

6、总结一下,MySQL只对以下操作符使用索引:<,<=,=,>,>=,between,in以及不以'%'和'_'开头的like查询,理论上每张表最多可以建立16个索引

7、当结果集只有1行时,使用limit 1

8、避免select *,始终指定你需要的列

9、使用join来代替子查询:join之所以更有效是因为,mysql不要在内存上创建临时表来完成这个逻辑上需要2个步骤的查询工作

10、拆分大的DELETE和INSET语句

11、固定长度的表会更快

mysql key和index的区别:key除了索引外,还具有约束作用,如:primary key除了建立主键索引外,还具有约束作用,用来规范一个存储主键和唯一性;unique key除了在key上建立索引,还具有唯一性约束。而index仅具有声明索引的作用。

聚簇索引和非聚簇索引:聚簇索引指的是索引中的键值和表数据存放在一起,主要是指innodb表索引,聚簇索引的好处是获得数据的效率更高;非聚簇索引指的是索引的键值和表数据分开存放,主要指MyISAM表索引,其索引保存在“表名.MYI”的文件中,数据则保存在“表名.MYD”的文件中。


书山有路勤为径 学海无涯苦作舟