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)
);
(2)唯一索引(unique)
创建唯一索引的时候,索引的值必须唯一,主键就是一种特殊的唯一索引。
create table address(
id int not nullprimary key auto_increment not null,
namevarchar(50),
address varchar(200),
unique indexaddress(id));
全文索引
使用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)
);
(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))
);
(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)
);
(6)空间索引
使用spatial 参数可以设置空间索引,空间索引只能建立在空间数据类型上,这样可以提高系统获取空间类型的效率,只有MyISAM支持此索引方式,字段值不能为空。
create table list(
id int not nullprimary key auto_increment not null,
goods geometrynot null,
spatial indexlistinfo(goods)
)engine=myisam;
MySQL的视图
视图的作用
1、 简单性
简化用户对数据库的理解,也可以简化对数据库的操作。
2、 安全性
视图的安全性可以防止威授权用户查看特定的行和列
3、 视图逻辑数据的的独立性
视图可以使得应用程序和数据库的表有一定的独立性,有了视图之后,应用程序可以建立在视图上,从而使得应用程序与数据库表被视图分割开。
MySQL性能的优化
可以使用show status like ‘value’来查看MySQL数据库的性能
参数1:connections ,连接MySQL服务器的次数
参数2:uptime MySQL服务器的上线时间
参数3:slow_queries:慢查询的次数
参数4:com_select查询操作的次数
参数6:com_delete删除操作的次数
优化查询
分析语句查询
1.1可以使用explain或者desc来分析查询语句
1.2索引对查询速率的影响
数据库表的数据如下:
未使用索引来查看数据,可以发现是从第一个开始进行查找,一直到查找到最后一个
数据多的话比较耗时间。
在username上建立一个索引
Create index username on user(username);
然后再使用索引来查询
可以看到rows=1,使用索引直接查到数据。
从中可以看书适当的使用索引可以提高查询的效率,而且也可以降低服务器的开销。
1.3使用索引来查询
应用like关键字进行优化查询
使用like的时候第一个不能是%否则不能起到优化作用
查询中使用多列索引
创建多列索引:create index index_user_info on user(username,sex);
分析以上结果可以看出多列索引的时候第一个索引必须使用,如果不使用索引就不能起作用。
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表名
检查表:主要是检查表中是否有错误
能够检查InnoDB和MyISAM类型的表是否有错误,checktable会对表加上之都锁
Check table 表名[option]
优化表:主要是消除删除或者更新造成的空间浪费。
Optional table 来对表的优化,该语句只对InnoDB和MyISAM 类型的表有效,只能优化表中varchar,blob,text类型的字段
可以消除删除和更新造成的磁盘碎片,从而减少对空间的浪费,执行过程中会对表加上只读锁。
查询高速缓存
用户在查询的时候会将结果集保存到一个特殊的缓存之中,当再次查询的时候直接从高速缓存中读取,加快速度。
1、 检查是否开启告诉缓存
show variableslike '%query_cache%';
优化多表查询
使用联合查询代替子查询
优化表设计
优先考虑定长字段,然后考虑变长字段,尽量设置少的列宽,减少磁盘空间,同样也可以减少数据处理时候的IO,经常使用optimize table 来对表进行优化