MySQL数据库解析.docx
《MySQL数据库解析.docx》由会员分享，可在线阅读，更多相关《MySQL数据库解析.docx（16页珍藏版）》请在冰豆网上搜索。
MySQL数据库解析
MySQL数据库优化常见方法：
1.表的设计要合理（满足3NF，即第三范式）
2.适当创建索引（主键索引、唯一索引、普通索引、全文索引、空间索引）
3.对SQL语句优化-》定位慢查询（explain）
4.使用分表技术（水平分表，垂直分表），分区技术
5.配置读写分离
6.创建适当存储过程、函数、触发器
7.对my.ini优化，优化配置
8.软件硬件升级
构建海量表，定位慢查询
为了讲解优化需要构建海量表（8000000条数据），而且每条数据不一样，使用存储过程完成该过程。
1.构建还量表：
（1）创建一个测试数据库：
createdatabasetestdb;
（2）创建表：
Createtabledept（/*部门表*/
deptnoMEDIUMINTUNSIGNEDNOTNULLDEFAULT0,
dnameVARCHAR（20）NOTNULLDEFAULT“”,
locVARCHAR（13）NOTNULLDEFAULT“”
）ENGINE=MyISAMDEFAULTCHARSET=UTF8;
CREATETABLEemp（/*雇员表*/
EmpnoMEDIUMINTUNSIGNEDNOTNULLDEFAULT0,/*编号*/
EnameVARCHAR（20）NOTNULLDEFAULT“”,/*名字*/
JobVARCHAR（9）NOTNULLDEFALUT“”,/*工作*/
MgrMEDIUMINTUNSIGNEDNOTNULLDEFAULT0,/*上级编号*/
HiredateDATENOTNULL,/*入职时间*/
SalDECIMAL（7,2）NOTNULL,/*薪水*/
CommDECIMAL（7,2）NOTNULL,/*红利*/
DeptnoMEDIUMINTUNSIGNEDNOTNULLDEFAULT0/*部门编号*/
）ENGINE=MyISAMDEFAULTCHARSET=UTF8;
CREATETABLEsalgrade（/*工资级别表*/
GradeMEDIUMINTUNSIGNEDNOTNULLDEFAULT0,
LosalDECIMAL（17,2）NOTNULL,
HisalDECIMAL（17,2）NOTNULL
）ENGINE=MyISAMDEFAULTCHARSET=UTF8;
#测试数据
INSERTINTOsalgradeVALUES（1,700,1200）;
INSERTINTOsalgradeVALUES（2,1200,1400）;
INSERTINTOsalgradeVALUES（3,1401,2000）;
INSERTINTOsalgradeVALUES（4,1540,1700）;
INSERTINTOsalgradeVALUES（5,1401,2000）;
INSERTINTOsalgradeVALUES（6,2001,3000）;
1.1使用索引：
一个表（存储引擎是MyISAM），对应三个文件，xx.frm结构文件，xx.myd数据文件，xx.myi索引文件
●给emp表添加主键索引
alerttableempaddprimarykey（empno）;
没加索引之前
加了索引之后
1.2如何定位慢查询（slowquery）
介绍：
默认情况下，mysql是不会记录慢查询的，所以我们在测试时可以指定mysql记录慢查询。
●启动时，这样启动：
Mysql>bin/mysqld.exe--safe-mode--slow-query-log
也可以在mysql中使用：
Setglobalshow_query_log=‘ON’;//开启慢查询
Setlong_query_time=1;//设置慢查询时间为1秒
说明：
query_time：
是查询时间（如果大的话加索引）
Lock_time：
是等待时间（如果大的话读写分离）
1.3如何分析一个SQL语句的问题：
-explain
Id:
查询序列号，值越大优先级越高
Select_type:
查询类型，simple：
简单
Table:
查询的表
Type：
all表示全表扫描，效率低
Possible_keys：
对SQL可用的索引
Key:
索引
Key_len:
Row:
1.3mysql的变量和参数：
Mysql>showvariables;
Mysql>showvariableslike‘long_query_time’;//慢查询时间
第2章索引的详解
2.1索引创建
2.1.1主键索引的创建
主键索引的创建有两种形式：
1.在创建表的时候，直接指定某列或某几列为主键，这时就有主键索引
2.添加表后再指定主键索引
●直接创建主键索引
●先创建表，再指定主键
增加主键：
ALTERTABLE表名ADDPRIMARYKEY（列名1,列名2,...）
●主键索引的特点：
1）一个表最多只能有一个主键
2）一个主键可以指向多列（复合主键）
3）主键索引的效率最高，因此我们应该给id,一般id是自增
4）主键索引列是不能重复，也不能为null
2.1.2唯一索引的创建
●直接在创建表的时候，指定某列或某几列为唯一索引
●把表创建好后，再指定某列或某几列为唯一索引
第一种方式：
createuniqueindexindex_nameONtableName（col_name）
第二种方式：
altertabletableNameaddunique（col_name）
为ddd表增加一个字段
说明：
使用createuniqueindex指令，必须指定索引名
说明：
使用altertable指令，可以指定索引名，也可以不指定索引名
⏹唯一索引的特点
1）一个表可以有多个唯一索引
2）唯一索引列的数据不能重复，但是如果没有指定notnull，唯一索引是可以为null，而且可以有多个但是不能为‘’
3）什么时候使用唯一索引，当某列数据不会重复，才能使用
4）唯一索引的效率也很高，仅次于主键索引，可以考虑优先使用。
2.1.3普通索引的创建：
●直接在创建表的时候，指定某列或某几列为索引
●把表创建好后，再指定某列或某几列为索引
●特点：
1）一张表中可以有多个普通索引，一个普通索引页可以指向多列
2）普通索引列的数据可以重复
3）普通索引的效率相对较低
2.1.4全文索引创建：
概述：
全文索引是针对文章，汉字，英文的检索，可以快速的检索到文章中的某个关键字。
●案例：
CREATETABLEarticles（
idINTUNSIGNEDAUTO_INCREMENTNOTNULLPRIMARYKEY,
titleVARCHAR（200）,
bodyTEXT,
FULLTEXT（title,body）
）ENGINEMyISAMDEFAULTCHARSET=UTF8;
INSERTINTOarticles（title,body）VALUES
（'MySQLTutorial','DBMSstandsforDataBase...'）,
（'HowToUseMySQLWell','Afteryouwentthrougha...'）,
（'OptimizingMySQL','Inthistutorialwewillshow...'）,
（'1001MySQLTricks','1.Neverrunmysqldasroot.2....'）,
（'MySQLvs.YourSQL','Inthefollowingdatabasecomparison...'）,
（'MySQLSecurity','Whenconfiguredproperly,MySQL...'）;
使用：
要使用MySQL默认的全文索引，需要使用match（字段名）against（关键字）;
●特点：
1）MySQL默认的全文索引，只针对MyISAM引擎。
2）MySQL默认的全文索引，只支持英文。
3）停止词：
对于特别普通的字母，不会建立索引。
4）匹配度：
全文索引是按照一定概率来匹配的。
●如何解决MySQL全文索引不支持中文的问题：
1）使用MySQL中文全文索引插件mysqlcft。
2）使用中文检索引擎sphinx中文版coreseek
2.2索引的查询
1.desc表名
2.showkeysfrom表名\G
3.showindexfrom表名\G
4.showindexesfrom表名\G
2.3索引的修改
2.4索引的删除
DROPINDEX索引名ON表名;
ALTERTABLE表名DROPINDEX索引名;
2.5索引的原理
2.6索引的注意事项
第3章SQL语句的优化和索引的正确使用
3.1对于创建的多列（复合索引），只要查询条件使用了最左边的列，索引一般就会被使用
为了演示效果创建一张测试表：
Delimiter$$
createprocedureinsert_dept（instart_numint（10）,inmax_numint（10））
begin
declareiintdefault0;
setautocommit=0;
repeat
seti=i+1;
insertintodeptvalues（（start_num+i）,rand_string（10）,rand_string（8））;
untili=max_num
endrepeat;
commit;
end$$
说明：
dname是左边的列，因此我们发现使用到dname就是使用到了索引，而下面的SQL语句，没有使用到索引
3.2对于使用like的查询，查询如果是‘%aaa’或‘_aa’不会使用到索引，但是‘aa%’或’aa_’会使用到索引。
（%代表任意的，_代表任意一个）
1
2
3
说明：
在like语句中，如果’’中最前面有_或%就使用不到索引，如果在中间或者最后有_或%可以使用索引。
3.3如果SQL语句条件中使用了or，则要求or的所有字段必须有索引，否则不能用到索引。
说明：
因为deptno字段没有建立索引所以该SQL语句没有使用到索引。
如果在deptno上也创建索引就可以使用到索引了。
3.4对于多列索引，不是使用的第一部分，则不会使用索引。
3.5如果列类型是字符串，那一定要在条件中将字符串用’’引起来，否则使用不到索引。
3.6MySQL如果估计使用全表扫描比使用索引快，就不适用索引。
3.7优化groupby语句
默认情况下，MySQL对所有的groupbycol1,col2进行排序，这与在查询中指定orderbycol1,col2类似。
如果查询中包括groupby但用户想要避免排序结果，则可以使用orderbynull来避免排序，如下：
3.8有些情况下，可以使用连接来代替
升级会员 