日常命令
链接远程数据库
1 2 mysql -h主机地址 -P端口 -u用户名 -p用户密码 mysql -h127.0 .0 .1 -P1234 -u"myname" -p"test123"
显示所有库
进入数据库
显示当前库中所有的表
显示表字段
创建表
1 create table table_name (name char(100 ), path char(100 ), count int(10 ), firstName char(100 ), firstMD5 char(100 ), secondName char(100 ), secondMD5 char(100 ), thirdName char(100 ), thirdMD5 char(100 ));
修改表名
1 rename table table_name_old to table_name_new
插入数据
1 insert into table_name (name, path, count, firstName, firstMD5, secondName, secondMD5, thirdName, thirdMD5) VALUES ('test' , 'test' , 1 , 'name1' , 'md1' , 'name2' , 'md2' , 'name3' , 'md3' );
查询表中数据
1 2 3 4 5 select * from table_name; select * from table_name where name = 'test'; select * from table_name order by id limit 0,2;
where、group by和having区别
1 2 3 4 5 6 7 8 9 // 1) 在SQL语句中，where子句并不是必须出现的 // 2) where子句是对检索记录中每一行记录的过滤 // 3) having子句出现在group by子句后面 // 4) 如果在一句SQL语句中，where子句和group by……having子句同时都有的话，必须where子句在前，group by……having子句在后。（where先执行，再groupby分组；groupby先分组，having再执行） // 5) group by子句是对检索记录的分组，后面跟随的字段名是分组的依据。根据语法，在select子句中，除聚合函数语句外，SELECT子句中的每个列名称都必须在GROUP BY子句中作为分组的依据。 select vend_id, count(*) , sales from products group by vend_id; 这个语句，其中sales字段，在group by中没有，所以查询的结果，sales的值是错误的！
更新表中某一行数据
1 update table_name set folderName ='Mary' where id=1;
删除表中某一行数据
1 delete from table_name where folderName = 'test';
添加表字段
1 alter table table_name add id int auto_increment not null primary key;
删除字段
1 alter table table_name drop folderName;
left join、right join和inner join区别 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 表A aID aNum 1 a1 2 a2 3 a3 4 a4 5 a5 表B bID bNum 1 b1 2 b2 3 b3 4 b4 6 b6
left join left join（左连接）返回包括左表中的所有记录和右表中联结字段相等记录。
1 2 3 4 5 6 7 8 select * from A left join B on A.aID = B.bID;aID aNum bID bNum 1 a1 1 b1 2 a2 2 b2 3 a3 3 b3 4 a4 4 b4 5 a5 NULL NULL
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
right join right join（右连接）返回包括右表中的所有记录和左表中联结字段相等记录。
1 2 3 4 5 6 7 8 select * from A right join B on A.aID = B.bID;aID aNum bID bNum 1 a1 1 b1 2 a2 2 b2 3 a3 3 b3 4 a4 4 b4 NULL NULL 6 b6
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
inner join inner join（等值连接）只返回两个表中联结字段相等行。
1 2 3 4 5 6 7 select * from A inner join B on A.aID = B.bID;aID aNum bID bNum 1 a1 1 b1 2 a2 2 b2 3 a3 3 b3 4 a4 4 b4
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
优化规则 核心
字段名不要使用‘key’ ‘index’ ‘like’‘time’ 等等关键字
表字段尽量少，上限控制在20~50个，适当可以冗余(平衡范式和冗余)。
不在数据库做强运算，不要使用存储过程、触发器等，数据库主要负责存储，不承担大量业务逻辑。
拒绝大sql，大事务。批量删除时，请控制在操作行数1万以内的小事务。批量插入时，请控制并发，并发搞很容易死锁。
索引
能不加的索引尽量不加，最好不超过字段数的20%(如：性别不加)，结合核心SQL优先考虑覆盖索引(https://my.oschina.net/BearCatYN/blog/476748 )
字符字段必须建前缀索引。由于字符串很长，通常可以索引开始的几个字符，而不是全部值，以节约空间并得到好的性能。(http://www.educity.cn/wenda/402373.html )
不在索引列进行数学运算和函数运算(会导致无法使用索引 => 全表扫描)，如where id+1 = 100 和 id = 100 - 1，效率差很远
自增列或全局ID做INNODB的主键
尽量不用外键(由程序保证约束)，高并发的时候容易死锁。
sql语句
尽量少用select *，只取需要数据列，为使用覆盖索引提供可能性，减少临时表生成，更安全
用in()代替or，因为or的效率是O(n)，而in()的效率是O(Log n)。如：where a = 1 OR a = 100 与 where a IN (1, 100)
尽量避免负向查找，如NOT、!=等
减少COUNT(*)，使用COUNT(col)，前者资源开销大，尽量少用。
大批量更新凌晨操作，避开高峰，零点附近往往定时任务量比较大，如果可以，尽量安排在03:00-07:00.
LIMIT高效分页：传统的方法是select from t limit 10000, 10，推荐的方法是select from t where id > 23423 limit 10。LIMIT的偏移量越大则越慢。还有一些高效的方法有：先取id来LIMIT偏移，减少整体的数据偏移；取到需要的id，与原表JOIN；程序取ID，然后用IN来填写。select from t where id >= (select id from t limit 10000, 1) limit 10 , select from t INNER JOIN (select id from t limit 10000, 10) USING (id) , select id from t limit 10000, 10; select * from t where id in (123, 456…)
尽量避免%前缀模糊查询，由于使用的是B+ Tree，前缀模糊使用不了索引，导致全表扫描(后缀模糊速度相对快很多)
优化总结
SQL语句优化，尽量精简，去除非必要语句
索引优化，让所有SQL都能够走索引
如果是表的瓶颈问题，则分表，单表数据量维持在1000W以内
如果是单库瓶颈问题，则分库，读写分离
如果是物理机器性能问题，则分多个数据库节点
mysql执行过程
解析：词法解析->语法解析->逻辑计划->查询优化->物理执行计划，过程中会检查缓存是否可用，如果没有可用缓存则进入下一步mysql_execute_command执行
执行：检查用户、表权限->表加上共享读锁->取数据到query_cache->取消共享读锁
