mysql 索引和优化总结

MySQL的索引

Mysql索引是一种将单列或者多列的值进行排序的结构,应用索引可以大幅度的提交查询的效率,也可以降低负载均衡,应用索引同时也有两面性,创建和维护索引需要耗费时间,并且耗费时间与数据量成正比,除此之外,索引需要占用物理空间。

(1)   普通索引

普通索引,即是不应用任何限制条件的索引,可以应用在任何的字段上。

create table score(

   id  int  primary key auto_increment not null,

   name  varchar(50)  not null,

   math  int(5)  not null,

   english  int(5)  not null,

chinese  int(5) not null,

index(id)

);

mysql 索引和优化总结

(2)唯一索引(unique)

创建唯一索引的时候,索引的值必须唯一,主键就是一种特殊的唯一索引。

create  table address(

id int not nullprimary key auto_increment not null,

namevarchar(50),

address varchar(200),

unique indexaddress(id));


mysql 索引和优化总结

全文索引

使用fulltext来设置全文索引,全文索引只能用在char、varchar、或者text类型的字段之上,查询数据量较大的字符串类型的时候可以使用,全文索引默认情况下是大小写不敏感,如果索引使用二进制进行排序的话,就可以执行大小写敏感的全文索引。

 

create  table cards(

id int not nullprimary key auto_increment not null,

name varchar(50),

number bigint(11),

infovarchar(50),

fulltext keycards_info(info)

);

mysql 索引和优化总结

(4)单列索引,只对应一个字段的索引,其可以包括前面三种索引,只要保证该索引值对应一个字段即可。

create  table telphone(

id int not nullprimary key auto_increment not null,

name varchar(50)not null,

 tel varchar(50) not null,

indextel(tel(20))

);

mysql 索引和优化总结


(5)多列索引

多列索引实在表的多个字段上建立索引,该索引指向创建时的多个索引,应用该索引的时候,必须使用第一个索引。

多列索引的生效原则是  从前往后依次使用生效,如果中间某个索引没有使用,那么断点前面的索引部分起作用,断点后面的索引没有起作用;

create  table information(

id int not nullprimary key auto_increment not null,

name varchar(50)not null,

sex varchar(5)not null,

birthdayvarchar(50) not null,

indexinfo(name,sex)

);

mysql 索引和优化总结

(6)空间索引

使用spatial 参数可以设置空间索引,空间索引只能建立在空间数据类型上,这样可以提高系统获取空间类型的效率,只有MyISAM支持此索引方式,字段值不能为空。

create  table list(

id int not nullprimary key auto_increment not null,

goods geometrynot null,

spatial indexlistinfo(goods)

)engine=myisam;

mysql 索引和优化总结

MySQL的视图

视图的作用

1、 简单性

简化用户对数据库的理解,也可以简化对数据库的操作。

2、 安全性

视图的安全性可以防止威授权用户查看特定的行和列

3、 视图逻辑数据的的独立性

视图可以使得应用程序和数据库的表有一定的独立性,有了视图之后,应用程序可以建立在视图上,从而使得应用程序与数据库表被视图分割开。


MySQL性能的优化

可以使用show status like ‘value’来查看MySQL数据库的性能

参数1:connections ,连接MySQL服务器的次数

mysql 索引和优化总结

参数2:uptime MySQL服务器的上线时间


mysql 索引和优化总结

参数3:slow_queries:慢查询的次数


mysql 索引和优化总结

参数4:com_select查询操作的次数

mysql 索引和优化总结
参数5:com_insert:插入操作的次数

mysql 索引和优化总结

参数6:com_delete删除操作的次数


mysql 索引和优化总结


优化查询

分析语句查询

1.1可以使用explain或者desc来分析查询语句


mysql 索引和优化总结


  1.2索引对查询速率的影响

数据库表的数据如下:


mysql 索引和优化总结

未使用索引来查看数据,可以发现是从第一个开始进行查找,一直到查找到最后一个

数据多的话比较耗时间。


mysql 索引和优化总结

在username上建立一个索引

Create index username on user(username);


mysql 索引和优化总结

然后再使用索引来查询


mysql 索引和优化总结

可以看到rows=1,使用索引直接查到数据。

从中可以看书适当的使用索引可以提高查询的效率,而且也可以降低服务器的开销。

1.3使用索引来查询

应用like关键字进行优化查询


mysql 索引和优化总结

使用like的时候第一个不能是%否则不能起到优化作用

mysql 索引和优化总结

查询中使用多列索引

创建多列索引:create index index_user_info on user(username,sex);


mysql 索引和优化总结

mysql 索引和优化总结

mysql 索引和优化总结

mysql 索引和优化总结

分析以上结果可以看出多列索引的时候第一个索引必须使用,如果不使用索引就不能起作用。


 

1、 优化数据库结构

2.1将字段很多的表分解成多个表

将不常用的字段分解。可以提高查询的效率。

2.2增加中间表

对于多表查询的时候,进行联合查询,会降低MySQL查询你的效率,但是如果建立中间表的话就可以提高查询的效率,先分析经常需要查询那几个表的字段,然后将这些字段建立一个中间表,将原来的数据进行插入进去,就可以提高查询的效率。

2.3优化插入记录的速度

插入记录的时候,索引和唯一性检查都会对插入的记录进行排序,会降低插入的速率,

所以再插入的时候禁用索引和唯一性检查,插入完成之后再进行重新的设置。

1、 禁用索引

Alter  table 表名 disablekeys;

重新开启索引的命令如下:

Alter table 表名 enable keys;

2、 禁用唯一性检查

Set unique checks=0;

重新开启唯一性检查

Set unique checks=-1;

3、 优化insert 语句

插入多条记录的时候,可以一条语句insert 插入多条记录,也可以使用一条命令插入一条记录,但是第一种与数据库建立连接只需要一次,性能更加好一点。

2.4分析表、检查表和优化表

  分析表:主要作用是分析关键字的分布

命令:analyze table表名


mysql 索引和优化总结

 检查表:主要是检查表中是否有错误

     能够检查InnoDB和MyISAM类型的表是否有错误,checktable会对表加上之都锁

Check table 表名[option]

  优化表:主要是消除删除或者更新造成的空间浪费。

  Optional table 来对表的优化,该语句只对InnoDB和MyISAM 类型的表有效,只能优化表中varchar,blob,text类型的字段

可以消除删除和更新造成的磁盘碎片,从而减少对空间的浪费,执行过程中会对表加上只读锁。

 

 

查询高速缓存

用户在查询的时候会将结果集保存到一个特殊的缓存之中,当再次查询的时候直接从高速缓存中读取,加快速度。

1、 检查是否开启告诉缓存

show variableslike '%query_cache%';


mysql 索引和优化总结


mysql 索引和优化总结

优化多表查询

使用联合查询代替子查询

 

优化表设计

  优先考虑定长字段,然后考虑变长字段,尽量设置少的列宽,减少磁盘空间,同样也可以减少数据处理时候的IO,经常使用optimize table 来对表进行优化