mysql知识点整理

1.Mysql版本

目前主流的版本是5.x, 5.0-5.1：版本4的延续，是对版本4的升级维护，5.4-5.x:mysql整合了三方公司的新存储引擎。

2. mysql 核心目录（linux）

/var/lib/mysql ：mysql 安装目录

/usr/share/mysql: mysql配置文件

/usr/bin: mysql命令目录（mysqladmin，mysqldump等）

/etc/init.d/mysql启停脚本

3.mysql配置文件

my-huge.cnf 高端服务区 1-2G内存

my-large.cnf 中等规模

还有my-medium.cnf,my-small.cnf等配置文件

但是以上配置文件mysql都不能识别，默认只能识别/etc/my.cnf(5.5版本)。将配置文件移动到etc目录下并更名为my.cnf即可识别，命令如下：

cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

4.mysql清屏命令（linux）

ctrl+L system clear

5.查看数据库性能参数

查看性能参数的语法如下：

show STATUS like ‘value’

其中，value是要查询的参数值，一些常用的性能参数如下：

• Connections：连接mysql服务器的次数
• Uptime:Mysql服务器上线的时间
• Slow_queries:慢查询的次数
• Com_select: 查询操作的次数
• Com_insert: 插入操作的次数
• Com_update：更新操作的次数
• Com_delete: 删除操作的次数
  例如查看慢查询的次数： show status like ‘Slow_queries’

6.mysql执行计划关键字explain

查询执行计划语法： explain + SQL查询语句

describe 语句的使用方法和explain一样，分析结构也一样。

执行计划的字段简要说明：

id: 编号，id值相同，表的查询顺序从上往下，顺序执行。数据量小的表，优先查询。 如果id值不同，id值越大的表越优先查询（以嵌套子查询为例，先查询内层，再查询外层）

select_type:查询类型。primary：最外层查询或主查询。subquery：子查询的第一个select。dependent sunquery:子查询的第一个select,取决于外面的查询。 simple:简单查询（不包括子查询和连接查询）。 derived:衍生查询（使用到了临时表）例如： explain select cr.cname from
(select * from course where tid >2)cr;还有一种情况，在from子查询中，如果有table1 union table2,则table1就是derived，table2就是union。union也是select_type类型。
union_result:连接查询的结果

table: 查询表

type: 表的连接类型 ，常见的包括system>const>eq_ref>ref>range>index>all。排序顺序就是性能顺序。其中system，const只是理想状况，实际的优化能达到ref，range就可以了

• system: 只有一条数据的系统表或者衍生表只有一条记录的主查询，是const的一个特例

• const：仅仅能查到一条数据的SQL，用于primarykey或者unique索引（和索引类型有关）

• eq_ref:搜索时使用primary key 或 unique类型。唯一性索引，根据该索引查询出的数据，索引列的值是唯一的（即使用主键索引或者唯一索引查询，并且满足“有且只有1个，不能多，不能为0”），多表查询，多表的查询字段（索引）必须全部匹配才行

• ref:非唯一性索引，对于每个索引键的查询，返回匹配的所有行

• range:使用索引进行范围查询，where后面是一个范围查询（between,in,>,<），in有时会导致索引失效，从而转换为all（全表查询）

• index：全索引表扫描，查询全部索引数据

• all:全数据表扫描，查询全部表中的数据

possible_keys:可以使用的的索引

key:实际使用的索引

key_len：实际使用索引的长度

ref:表之间的引用

rows:mysql在表中进行查询时必须读取的行数。一般使用索引查询时，读取的行数要比没使用索引的查询读取的行数小得多。

extra:处理查询时的额外信息

6.索引查询优化

• 将含in的范围查询，放到where条件的最后，防止失效
• 复合索引，保持索引的定义顺序和使用顺序一致（最左前缀原则）
• 小表驱动大表原则：联合查询条件中，数据量小的表字段放在查询条件左侧，数据量大的表字段放在查询条件的右侧，比如有a,b两张表,a有10条数据，b有1000条数据，联合查询条件应该是a.id=b.a_id. 编程中的嵌套循环，也应遵循此原则，外层循环小，内层循环大。
• 一般情况下，左连接给左表加索引；右连接给右表加索引
• 不要在索引上进行任何操作（计算，函数，类型转换），否则索引失效
• 复合索引不能使用不等于（！= <>）或is null,is not null,否则自身和右侧全部失效
• like不要以“%”开头，否则索引失效。只有“%”不在第一个位置，索引才会起作用。如果必须以“%”开头，使用索引覆盖(查询的字段是where条件的字段（索引列）)，可以挽救一部分性能
  -使用or连接查询条件时，只有前后的两个查询条件都是索引时，查询时才会使用索引，否则将不使用索引。
• 使用连接查询代替子查询，子查询效率低

7.数据库结构优化

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

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表.因为当一个表的数据量很大时，会影响查询速度。

7.2 增加中间表

对于需要经常联合查询的表，可以建立中间表提高查询效率。通过建立中间表，把经常需要联合查询的数据插入到中间表中，然后将原先的联合查询改为对中间表的查询，提高查询效率。

7.3 增加冗余字段

对于一个经常需要两张表或者多张表联合查询才能展示的字段，可以考虑将该字段设计到一张表中，这样就不需要联合查询，直接查询一张表即可。不过冗余字段的添加需要按照实际需求综合分析，数据库的设计尽可能遵循范式理论规约。

7.4 优化插入记录速度

插入数据时，影响插入速度的因素有：索引，唯一性校验，一次插入记录条数等

1. 禁用索引，禁用索引语法如下：alter table table_name disable keys ;重新开启索引的语句如下：alter table table_name enable keys
2. 禁用唯一性检查，禁用唯一性检查语法如下： set unique_checks=0;重新开启语法如下： set unique_checks=1
3. 使用批量插入，批量插入效率更高：例如

insert into fruit values
(1,5.6,"苹果"),
(2,6.6,"桃子"),
(3,5.0,"李子");

8.常见优化方法及慢查询SQL排查

8.1慢查询日志

慢查询日志默认关闭；建议开发调优时打开，部署时关闭。

检查是否卡开启慢查询日志命令： show variables like %slow_query_log%

临时开启(内存中)慢查询日志命令： set global slow_query_log=1;

永久开启(修改配置文件,开启日志并设置日志路径)：,在/etc/my.cnf中追加配置： show_query_log=1;show_query_log_file=/var/lib/mysql/slow.log

慢查询阈值： show variables like “%long_query_time%”

临时设置慢查询阈值(重新登录后生效)：set global long_query_time=5

查询超过阈值的慢sql数量： show global status like “%slow queries%”

慢查询的sql被记录在了日志中，可以通过设置的日志查看具体的慢sql。或者通过mysqldumpslow工具(mysql自带工具)查看慢sql

9.分析海量数据profiles:

profiles 默认关闭
查看profiles是否打开： show variables like “%profiling%”

开启profiles： set profiling =on（消耗性能，生产环境应关闭）

查看profiles开启后，所有查询花费的时间： show profiles

缺点：只能看到每条sql执行的时间，不能看到详情

查看执行详情的命令: show profile all for query “query_id” "query_id"是show profiles查询中的id

查看特定的硬件设备花费时间： show profile cpu, block io for query “query_id”