MySQL.docx
《MySQL.docx》由会员分享，可在线阅读，更多相关《MySQL.docx（12页珍藏版）》请在冰豆网上搜索。
MySQL
MySQL
♦数据库存储引擎
11引入
1.MySQL5.5之前默认的存储引擎是MyISAM,5.5默认是InnoDB.
2.存储引擎就是指表的类型,数据库的存储引擎决定了表在计算机中的存储方式,也就是说用户可以根据自己的要求选择不同的存储方式,是否进行事务处理等.
3.查看存储引擎的命令:
方式一:
mysql>showengines;
方式二:
mysql>showvariableslike"have%";
查看默认的存储引擎:
mysql>showvariableslike"storage_engine";
4.更改存储引擎,修改my.ini文件.将”default-storage-engine=INNODB”修改为”default-storage-engine=MyISAM”,然后重启服务即可.
5.常用存储引擎比较
12InnoDB
1.MySQL从3.23.34a开始支持InnoDB存储引擎.
2.InnoDB给MySQL的表提供了事务,回滚,崩溃修复能力和多版本并发控制的事务安全.
缺点:
读写效率稍差,占用的数据空间相对较大
3.InnoDB是第一个提供外键约束的表引擎,而且InnoDB对事务的处理也是很强的.
4.InnoDB存储引擎创建的表的表结构存储在.frm文件中,数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中.
Ø自动增长列（AUTO_INCREMENT）
自动增长的列不能为空,且值必须唯一.MySQL中规定自增列必须为主键.在插入值时,如果自增长列不输入值,则插入的值为自增后的值;如果输入的值为0或null,则插入的值也为自增后的值;如果插入的值时某个确定的值,且值在前面没有出现过,则直接插入该值.
Ø支持外键（FOREIGNKEY）
外键的所在的表时子表,外键所依赖的表时父表,父表中被子表外键关联的字段必须为主键.当删除,更新父表的某条信息后,字表也有相应的改变.
13MyISAM
1.MyISAM是从ISAM存储引擎发展起来的.
2.MyISAM存储引擎的表存储成3个文件.文件的名字与表明相同.扩展名包括frm,MYD和MYI.frm:
存储表的结构;MYD:
存储数据;MYI:
存储索引
3.MyISAM存储引擎的表支持3中不同的存储格式:
静态型+动态型+压缩型.静态型是MyISAM默认的存储格式,其字段时固定的;动态型包含变长字段,记录的长度不是固定的;压缩型需要myisampack工具创建,占用的磁盘空间较小.
4.优势:
占用空间小,处理速度快;缺点:
不支持事务的完整性和并发性.
14MEMORY
1.使用存储在内存中的内容来创建表,并且数据都在内存中.
2.每个基于MEMORY引擎的表实际对应一个磁盘文件.该文件的名与表名相同,为frm类型,只存储表的结构.而数据文件都是存在内存中的.注意:
服务器需要有足够的内存.
3.该存储引擎默认使用哈希（HASH）索引,要比B树（BTREE）索引块
4.MEMORY表大小手限制,参数:
max_rows（创建表时指定）和max_heap_table_size（默认16MB）,
5.由于数据在内存中,声明周期短,所以慎用这个存储引擎.
15存储引擎的选择
ØInnoDB:
用于事务处理应用程序+支持外键.具体情况:
应用对事务有较高的要求+在并发条件下对数据的一致性有要求+数据除了插入和查询以外,还有很多更新,删除操作.适合计费系统和财务系统等对数据性准确性要求高的的系统
ØMyISAM:
读操作（select）和插入（insert）操作为主+只有很少的更新和删除操作+对事务完整性和并发性要求不是很高.适合在web,数据仓储等环境下的
ØMEMORY:
需要快速定位记录和其他数据的环境下.缺陷是对表的大小有限制,要确保表中的数据能恢复
ØMERGE:
用户将一些列等同的MyISAM表以逻辑方式组合在一起,并作为一个对象引用他们,优点:
突破单个MyISAM表大小的限制,还可以将表分布在多个磁盘上,有效的改变对MyISAM表的访问效率.
♦索引
16引入
MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
索引有两种存储类型：
B型树（BTREE）索引和哈希（HASH）索引,InnoDB和MyISAM存储引擎支持BTREE索引,MEMORY引擎支持HASH索引和BTREE索引,默认为HASH索引.
创建索引的优缺点:
优点:
可以提高数据的检索速度;缺点:
创建和维护索引需要时间
索引虽然可以提高查询速度,但是降低插入的速度（向有索引的表中插入记录时,会以索引排序）,解决办法就是:
先删除索引,插入,在建立索引
17索引分类
1.普通索引
普通索引不附加任何条件,可以创建在任何数据类型中,其值是否唯一和非空由本身的完整性约束条件决定.建立索引后,就可以通过索引查询了.
2.唯一性索引
使用UNIQUE设置唯一性索引,创建唯一性索引时,限制该索引的值必须是唯一的,主键是一种特殊的唯一性索引.
3.全文索引
使用FULLTEXT设置全文索引,全文索引只能创建在CHAR,VARCHAR或TEXT类型的字段上.查询数据量较大的字符串类型的字段时,使用全文索引可以提高检索速度.但只有MyISAM存储引擎支持全文索引.默认情况全文搜索的执行方式不支持大小写,但索引的列使用二进制排序后,可以执行区分大小写的排序.
4.单列索引
在表中的单个字段创建索引.单列索引只能根据该字段进行索引,单列索引可以是普通索引,唯一性索引,也可以是全文索引.只要保证该索引对应一个字段即可.
5.多列索引
在表中的多个字段上创建索引.该索引指向创建时对应的多个字段,可以通过这几个字段进行查询.但是只有查询条件中使用了该字段中的第一个字段时,才使用该索引.
6.空间索引
用SPATIAL（spatial）参数设置空间索引.空间索引只能创建在空间数据类型上,这样可以调高空间数据类型的检索速度,MySQL的空间数据类型包括GEOMETRY和POINT和LINESTRING和POLYGON等,目前只有MyISAM存储引擎支持空间检索,而且检索的字段不能为空值.
18索引的设计原则
1.选择唯一性索引
唯一性索引的值是唯一的,可以更快的通过该索引确定某条记录.如:
学生表中的学号是具有唯一性的字段.为该字段创建唯一性索引可以很快的确定某个学生的信息,但如果使用姓名的话,姓名可能有重复的,那么就降低了检索的速度.
2.为经常需要排序,分组和联合操作的字段建立索引
经常需要使用ORDERBY,GROUPBY,DISTINCT和UNION等操作的字段,建立索引排序就会快很多
3.为常作为查询条件的的字段建立索引
如果有某个字段经常用来查询,那么就可以为他创建一个索引.
4.限制索引的数目
索引的创建也会占用磁盘空间,索引越多,占用的空间就会越大,修改表时,对索引的重构和更新也会很麻烦.
5.尽量使用数据量少的索引
对一个VARCHAR（100）类型的字段进行全文检索需要的时间肯定比对VARCHAR（10）进行全文检索的时间要长的.
6.尽量使用前缀来进行索引
如果索引的字段的值很长,最好使用值的前缀来索引.如:
TEXT和BLOG类型的字段.
7.删除不在使用或很少使用的索引
表中的数据大量更新时,或者数据的使用方式改变后,原有的一些索引不用了,那么应该将这些索引删除掉
19创建索引
3种方式:
创建表的时候创建索引+在已经存在的表上创建索引+使用ALTERTABLE语句创建索引
19.1创建表的时候创建索引
CREATETABLE表名（属性名数据类型[完整性约束条件],
属性名数据类型[完整性约束条件],
…
[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY
[别名]（属性名1[（长度）][ASC|DESC]）
）;
UNIQUE:
唯一索引
FULLTEXT:
全文索引
SPATIAL:
空间索引
INDEX|KEY:
用来指定字段为索引的,任一一个就行
别名:
给索引取个别名
属性名1:
指定索引对应的字段的名称—就是数据库中的字段
长度:
可选参数,指索引的长度,字符串类型才可以使用
ASC:
升序排列
DESC:
降序排列
1.创建普通索引
不需要加UNIQUE,FULLTEXT,SPATIAL等参数
*创建一个表名index1的表,在表中的id字段上加索引.
CREATETABLEindex1（idINT,
nameVARCHAR（20）
sexBOOLEAN,
INDEX（id）
）;
*查看表的结构mysql>showcreatetableindex1;
*使用EXPLAIN语句查看索引是否被使用.
mysql>explainselect*fromindex1whereid=1;
2.创建唯一性索引
使用UNIQUE参数进行约束.
*创建一个index2的表,在表中的id字段上建立名为index2_id的唯一性索引.且以升序的形式排列.
CREATETABLEindex2（idINTUNIQUE,
nameVARCHAR（20）,
UNIQUEINDEXindex2_id（idasc）
）;
mysql>showcreatetableindex2;
3.创建全文索引
*在信息字段上创建一个全文索引.
CREATETABLEindex3（idINT,
infoVARCHAR（50）,
FULLTEXTINDEXindex3_info（info）
）ENGINE=MyISAM;
mysql>showcreatetableindex3;
4.创建单列索引
在表的单个字段上创建索引.
*创建一个index4表,在表中的subject字段上建立名为index4_st的单列索引.
CREATETABLEindex4（idINT,
subjectVARCHAR（30）,
INDEXindex4_st（subject（10））
）;
mysql>showcreatetableindex4;
5.创建多列索引
在表的多个字段上创建索引
*建立index5表,在表中的name和sex字段上建立名为index5_ns的多列索引.
CREATETABLEindex5（idINT,
nameVARCHAR（20）,
sexVARCHAR（4）,
INDEXindex5_ns（name,sex）
）;
mysql>showcreatetableindex5;
注意:
使用多列索引时,只有使用了索引中的第一个字段时才会触发索引.
5.创建空间索引.
需要使用SPATIAL（spatial）参数设置.创建空间索引时,表的存储引擎必须是MyISAM类型,且字段必须非空约束.
*创建表index6,在表中的space的字段上建立index6_sp的空间索引.
CREATETABLEindex6（idINT,
spaceGEOMETRY,
SPATIALINDEXindex6_sp（space）
）ENGINE=MyISAM;
mysql>showcreatetableindex6;
19.2在已经存在的表上创建索引
CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名
ON表名（属性名[（长度）][ASC|DESC]）
1.创建普通索引
CREATEINDEXindex7_idONexample0（id）;
2.创建唯一性索引
CREATEUNIQUEINDEXindex8_idONindex8（course_id）;
3.创建全文索引
CREATEFULLTEXTINDEXindex9_infoONindex9（info）;
4.创建单列索引
CREATEINDEXindex10_addrONindex10（address（4））;
5.创建多列索引
CREATEINDEXindex11_naONindex11（name,address）;
6.创建空间索引
CREATESPATIALINDEXindex12_lineONindex12（line）;
19.3用ALTERTABLE创建索引
ALTERTABLE表名ADD[UNIQUE|FULLTEXT|SPATIAL]INDEX
索引名（属性名[（长度）][ASC|DESC]）
1.创建普通索引
ALTERTABLEexample0ADDINDEXindex13_name（name（20））;
2.创建唯一性索引
ALTERTABLEindex14ADDUNIQUEINDEXindex14_id（course_id）;
3.创建全文索引
ALTERTABLEindex15ADDFULLTEXTINDEXindex15_info（info）;
4.创建单列索引
ALTERTABLEindex16ADDINDEXindex16_addr（address（4））;
5.创建多列索引
ALTERTABLEindex17ADDINDEXindex17_na（name,address）;
6.创建空间索引
ALTERTABLEindex18ADDSPATIALINDEXindex18_line（line）;
♦优化
19.4优化原则
可以通过以下命令,来具体对网站的优化,有针对性的.
SHOWSTATUSLIKE‘value’;
value参数:
connections:
连接MySQL服务器的次数
uptime:
MySQL服务器的上线时间
slow_queries:
慢查询的次数
com_select:
查询操作的次数
com_insert:
插入
com_update:
更新
com_delete:
删除
通过以上参数可以分析MySQL数据库的性能.然后分析结果,进行相应的方向的性能优化.
19.5优化查询
11.1.1分析查询优化的语法
ØEXPLAIN语句（与DESC语句的语法一样）
语法:
explainselect语句;
如:
mysql>explainselect*fromemp;
结果如下:
id:
表示select语句的编号
select_type:
select语句的类型.常用值:
SIMPLEà简单查询,不包括连接查询和子查询;
PRIMARYà主查询,或是最外层的查询语句;UNIONà连接查询的第二个或后面的的查询语句.
table:
查询的表名
type:
表的连接类型.常用值:
systemà只有一条记录;const:
表中有多条记录,但只查询一条出来;allà对表进行了完整的扫描;eq_refà多表连接时,后面的表使用了UNIQUE或者PRIMARYKEY;refà多表查询,后面使用了普通索引;unique_subqueryà子查询中使用了UNIQUE或者PRIMARYKEY;index_sbqueryà子查询使用了普通索引;rangeà查询语句中给出了查询范围;indexà表中的索引进行完整扫描
possible_keys:
查询中可能使用的索引
key:
查询中使用的索引
key_len:
查询中索引字段的长度
ref:
使用哪个列或常数与索引一起来查询记录
rows:
查询的行数
extra:
查询过程的附加信息
11.1.2索引对查询速度的影响
（待写）
升级会员 