源码仓库Gitee · GitHubLinux服务器安装MySQL后直接命令mysql进入服务需进行修改/usr/bin/mysqladmin -u root password 123456设置开机自启动chkconfig mysql on ---设置开机自启动mysql chkconfig --list|grep mysql --查看mysql的运行级别 ntsysv --看到[*]mysql这一行表示开机后自动启动mysqlMySQL客户端和服务器编码格式默认使用latin1导致插入中文乱码配置文件my.cnf进行修改字符集[client]节点下面添加 default-character-setutf8 [mysqld]节点下面添加 character_set_serverutf8 character_set_clientutf8 collation-serverutf8_general_ci [mysql]节点下面添加 default-character-setutf8MySQL主要配置文件信息二进制日志log-bin主要用于主从复制错误日志log-error默认关闭记录严重的警告和错误信息每次启动和关闭的详细信息等查询日志log默认关闭记录查询的sql语句如果开启会降低MySQL的整体性能数据文件【每个库对应在磁盘中有下面文件】frm文件存放表结构myd文件存放表数据myi文件存放表索引SQL执行加载顺序手写 select distinct 列表 form 表 连接类型 join 表2 on 连接条件 where 筛选条件 group by 分组列表 having 分组后的筛选 order by 排序列表 limit 偏移,条目数 机读 from 表 on 连接条件 连接类型 join 表2 where 筛选条件 group by 分组列表 having 分组后的筛选 select distinct 列表 order by 排序列表 limit 偏移,条目数7种join内连接,共有部分select A.*,B.* from A inner join B on A.idB.id;左连接共有部分右表没有的匹配补nullselect A.*,B.* from A left join B on A.id B.id;【若建立索引进行SQL优化需对右表B出现的字段建立索引因为left join条件用于确定如何从右表搜索行左边一定都有】左表独有select A.*,B.* from A left join B on A.id B.id where B.id is null;右连接共有部分左表没有的匹配补nullselect A.*,B.* from A right join B on A.id B.id;【若建立索引进行SQL优化需对左表A出现的字段建立索引因为right join条件用于确定如何从左表搜索行右边一定都有】左表独有select A.*,B.* from A right join B on A.id B.id where A.id is null;全连接union合并去重全连接select A.*,B.* from A left join B on A.id B.id union select A.*,B.* from A right join B on A.id B.id;左右表独有select A.*,B.* from A left join B on A.id B.id where B.id is null union select A.*,B.* from A right join B on A.id B.id where A.id is null;索引优势提高数据检索的效率降低数据库的IO成本通过索引列对数据进行排序降低数据排序的成本降低了CPU的消耗。劣势索引是一张表该表保存了主键和索引字段并指向实体表的记录所以索引列是要占用空间的索引提高了查找速度但降低了更新速度对表进行insert、update、delete时MySQL不仅要保存数据还要保存索引文件每次更新添加 了索引列的字段调整更新带来的键值变化后的信息。分类单值索引一个索引只有一个列一个表可以有多个单值索引唯一索引索引列的值必须唯一但可以有null值复合索引一个索引包含多个列BTree索引原理索引创建的情况主键自动建立唯一索引频繁作为查询条件的字段查询中与其他表关联的字段外键关系建立索引查询中排序的字段排序字段若通过索引去访问将大大提高排序速度【索引加快检索和排序速度】查询中统计或分组字段【分组与排序配合使用分组前必须排序】高并发下倾向创建组合索引频繁更新的字段不适合创建索引【需要更新索引信息】where条件里用不到的字段不创建索引字段存储的值重复该字段不适合创建索引explain分析查询语句和表机构性能瓶颈explain查询SQL作用查看表的读取顺序【id】数据读取操作的操作类型【select_type】哪些索引可以使用【possible_key】哪些索引实际被使用【key】表之间的引用【ref】每张表有多少行被优化器查询【rows】查看执行计划包含的信息idselect查询的序列号包含一组数字表示查询中执行select子句或操作表的顺序【id为null最后执行】id相同执行顺序从上到下id不同如果是子查询id的序号会递增id值越大优先级越高越先被执行id相同不同同时存在id如果相同可以认为是一组从上到下顺序执行在所有组中id值越大优先级越高越先被执行【衍生DERIVED】select_type:查询的类型主要是用于区分普通查询、联合查询、子查询等复杂查询SIMPLE简单的select查询查询中不包含子查询或UNIONPRIMARY查询中若包含任何复杂的子部分最外层查询则为PRIMARY【select *from A where id (select id from B)中A表为PRIMARY】SUBQUERY在select或where列表中包含了子查询DERIVED在from列表中包含的子查询为DERIVED(衍生)MySQL会递归执行这些子查询把结果放在临时表里UNION若第二个select出现在union之后则被标记为UNION若UNION包含在from子句的子查询中外层select被标记为DERIVED【分析前面7种join中的合并去重全连接sql】UNION RESULT从UNION表获取结果的selecttable:显示这一行的数据是关于哪张表的type:访问类型【all、index、range、ref、eq_ref、const、system、NULL】显示查询使用了何种类型从最好到最差依次是systemconsteq_refrefrangeindexallsystem表只有一行记录等于系统表const表示通过索引一次就找到了const用于比较primary key或unique索引因为只匹配一行数据所以很快如将主键置于where条件中MySQL就能将该查询转换为一个常量eq_ref唯一性索引扫描对于每个索引键表中只有一条记录与之匹配常见于主键或唯一索引扫描【由t2进行驱动查找】ref非唯一性索引扫描本质是一种索引访问它返回所有匹配某个单独值的行然而它可能会找到多个符合条件的行所以应该属于查找和扫描的混合体range只检索给定范围的行使用一个索引来进行一般在where条件中出现了between、、、in等的查询这种范围扫描索引比全表扫描要好因为它只需要开始于索引的某一点结束于另一点不用扫描全部索引【如果索引字段是a,b,c条件中使用了a的值是常量b是范围对c进行排序会导致索引失效】【优化对范围的字段从索引列中删除即覆盖索引a,b,c改为a,c从而变成ref级别】indexFull Index Scan全索引扫描index与all区别为index类型只遍历索引树通常比all快因为索引文件通常比数据文件小【all和index都是读全表但index是从索引中读取all是从磁盘中读取】all全表扫描遍历全表查找符合的数据行possible_keys显示可以应用在这张表中索引一个或多个查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用key实际使用的索引如果没null【进行全表扫描】则没有使用索引查询中若使用了覆盖索引则该索引仅出现在key列表中【查询的字段个数、顺序与索引一一对应】key_len表示索引中使用的字节数可通过该列计算查询中使用的索引的长度在不损失精确性的情况下长度越短越好key_len显示的值为索引字段的最大可能长度并非实际使用长度即key_len是根据表定义计算而得不是通过表内检索出的ref显示索引的哪一列被使用了【库名.表名,被使用列名】如果可能的话是一个常数【const】哪些列或常量被用于查找索引列上的值rows根据表统计信息及索引选用情况大致估算出找到所需记录所需要读取的行数extra:包含不适合在其他列中显示但十分重要的额外信息Using filesort说明MySQL会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取【创建了一个组合索引包含a,b,c但在where条件和order by排序时只使用了a,c导致这个索引出现了断层会出现文件排序】MySQL中无法利用索引完成的排序操作称为“文件排序”Using temporary使用了临时表保存中间结果MySQL在对查询结果进行排序时使用临时表常见于排序order by和分组查询group by【临时表增加数据库负担】Using index表示相应的select操作中使用了覆盖索引【覆盖索引创建的索引包含a,b进行select查询的列全包含或部分包含如a,b或a】避免访问了表的数据行效率不错如果同时出现using where表明索引被用来执行索引键值的查找如果没有同时出现using where表明索引用来读取数据而非执行查找动作Using where表明使用了where过滤using join buffer使用了连接缓存【出现在多表的inner join内连接中】【最好在my.cnf配置文件中的缓存调大点】impossible where where子句的值总是false不能用来获取任何元组select tables optimized away在没有group by子句的情况下基于索引优化MIN/MAX操作或对于MyISAM存储引擎优化count(*)操作不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化distinct优化distinct操作在找到第一匹配的元组后即停止找同样值的动作