mysql总结

查询优化：

　　定位问题：

　　　　　　1.记录慢查询

　　　　　　show profile 

　　　　　　　　set profiling = 1 开启

　　　　　　show profiles 

　　　　　　　　show profile for query 表ID

　　　　　　show status          ：计数器

　　　　　　show processlist  ：记录线程

　　　　　　explain/desc 　　：分析单条语句

　　问题所在：

　　　　　　1.访问太多的数据

　　　　　　2.索引太多行/列

　　　　　　3.Mysql分析不必要的数据行

　　　　　　4.特定语句

　　常见问题的解决：

　　　　　　1.查询不必要的记录  　　　　　　　　：使用limit

　　　　　　2.多表关联返回全部列　　　　　　　  ：指定列

　　　　　　3.取出全部的列　　　　　　　　　　  ：

　　　　　　4.重复查询相同的数据　　　　　　　  ：保存到缓存中

　　　　　　5.扫描额外的记录　　　　　　　　　  ：使用索引

　　特定语句优化：

　　　　　　1.count（*）比count（列名）快

　　　　　　2.on或using字句的列上使用索引

　　　　　　3.确保group by和order by只有一个表中的列，才能使用索引

　　　　　　4.优化group by/distinct =>1.使用索引 ，2.关联查询使用标识列分组，3.group by 不用排序是使用 order by null

　　　　　　5.优化limit：记录上次最大的ID

　　　　　　6.union优化：使用union all在程序中对数据进行处理

　　　　　　7.优化子查询：使用关联查询替代

　　　　　　8.嵌套查询：不推荐使用=>效率不好控制

　　关联查询：

　　　　　　1.cross join ： 没有关联条件，结果是笛卡尔积，没有意义

　　　　　　2.Inner join  ：inner join （等值，大于小于，自连接） 缩写join

　　　　　　3.left join/right join：左=》以作为主，先查左，按on去匹配右表，null填充

　　　　　　4.union/union all :联合查询=》把多个结果集进行累加(列数要相等，相同记录合并)

　　　　　　5.full join：mysql不支持：使用left union right

　　　　

　　　　　　

 

 

 

索引：

　　　　1.普通索引：没有限制条件

　　　　2.唯一索引：具有唯一性

　　　　3.主键索引：唯一性切非空

　　　　　　主键索引和唯一索引的关系：主键索引是特殊的唯一索引，在一个表中唯一索引可以有多个，主键索引只能有一个，主键索引可以与外键构成参照完整性约束

　　　　4.组合索引：多个索引列（也叫复合索引）

　　　　5.外键索引：InnoDB支持，数据的一致性，完整性，级联操作

　　　　6.全文索引：MyISAM支持，仅英文

 

　　索引创建的原则：

　　　　1.where/on 字句中的列

　　　　2.索引列的基数越大，效果越好

　　　　3.对字符串进行索引是，指定一个前缀规则

　　　　4.避免过多的索引

　　　　5.主键使用小类型(int)

　　注意事项：

　　　　1.符合索引：前缀原则

　　　　2.like查询：%放前，索引失效

　　　　3.is null ：可以使用索引

　　　　4.mysql自动优化，可能放弃使用索引

　　　　5.or：前面使用索引，后面不使用，索引全部失效

　　　　6.列是字符串类型：使用引号，否则全部索引失效

　　索引的作用：

　　　　1.减少扫描的数据量

　　　　2.避免使用排序和临时表

　　　　3.将随机I/O变成顺序I/O

　　　　4.提高查询速度

　　　　5.减低写的速度，占用空间

 

 

 

 

常用命令：

　　　　1. \G =>垂直显示

　　　　2. \c  =>取消当前命令

　　　　3. \q =>退出

　　　　4. \s =>显示状态

　　　　5. \h =>帮助

　　　　6. \d =>改变执行符

 

mysql：引擎InnoDB，MyISAM,CSV等

　　InnoDB:

　　　　1.默认的事务型引擎

　　　　2.使用共享表空间

　　　　3.内部优化（读使用hash索引，出入使用缓存区）

　　　　4.支持安全恢复

　　　　5.支持外键

　　　　6.支持行级锁

　　MyISAM：

　　　　1.支持全文索引

　　　　2.不能安全回复

　　　　3.使用表级锁

　　　　4.存储两个文件（MYD<数据> MYI<索引>）

 

锁：共享锁（读锁），排它锁（写锁）

　

mysql的安全问题：

　　从程序角度

　　1.使用预处理语句，防止sql注入

　　2.特殊字符转义

　　3.将错误信息记录到日志，不要显示给用户

　　权限角度：

　　1.定期备份

　　2.限制查询用户的权限

　　3.关闭远程访问

　　4.使用复杂的root密码

　　5.删除多余的用户

　　6.更改root的名字

　　7.限制用户使用的库

　　8.限制用户对储存文件的权限