MySql 小技巧
1）修改默认时区
select now(); 查看 MySql 系统时间。和当前时间做对比
set global time_zone = ‘+8:00’;设置时区更改为东八区
flush privileges; 刷新权限
2）批量删除以字段开头的表
# 先查询 SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema='itstyle' AND table_name LIKE 'add%' # 拷贝出来 DROP TABLE add_student,add_teacher
3）查看所有连接进程
show full processlist
看一下所有连接进程，注意查看进程等待时间以及所处状态 是否locked
如果进程过多，就把进程打印下来，然后查看
mysql -e 'show full processlist;' > list.txt
4）创建一个只读权限的用户
# 创建查询用户、允许外网访问 CREATE USER 'select'@'%' IDENTIFIED BY '123456'; # 给新用户赋予查询权限 GRANT SELECT ON * . * TO 'select'@'%'; # 刷新权限 FLUSH PRIVILEGES;
5）left join 避坑
在left join语句中，左表过滤必须放where条件中，右表过滤必须放on条件中，这样结果才能不多不少，刚刚好。
优化
优化MySQL 参数优化
在插入几万条的数据时，可以修改my.ini配置文件，原本插入需要几十秒的东西，瞬间就只需要几秒钟。默认是128M
innodb_buffer_pool_size = 512M
Innodb的缓冲池会缓存数据和索引，设置的越大访问表中的数据所需的磁盘I/O就越少。
负向查询不能使用索引
select * from order where status!=0 and stauts!=1
还有 not in/not exists都不是好习惯
select name from order where status not in (0,1);
可以优化为in查询：
select * from order where status in(2,3)
子查询优化
执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。
可以使用连接查询（JOIN）代替子查询，连接查询不需要建立临时表，因此其速度比子查询快。
前导模糊查询不能使用索引
如:
select name from user where name like '%xxx'
非前导则可以:
select name from user where name like 'xxx%' • 1
MyISAM 存储引擎也可以做全文检索，不过只支持英文，相信现在应该也没人使用它了。建议使用solr 、es 等第三方开始工具实现全文检索功能。
limit优化
limit 的用法是 limit [offset], [rows]，其中 offset 表示偏移值， rows 表示需要返回的数据行。
mysql 的 limit 给分页带来了极大的方便，但数据偏移量一大，limit 的性能就急剧下降。
以下是两条查询语句，都是取10条数据，但性能就相去甚远
select * from table_name limit 10000,10 select * from table_name limit 0,10
第一次优化
根据数据库这种查找的特性，就有了一种想当然的方法，利用自增索引（假设为id）：
select * from table_name where (id >= 10000) limit 10
由于普通搜索是全表搜索，适当的添加 WHERE 条件就能把搜索从全表搜索转化为范围搜索，大大缩小搜索的范围，从而提高搜索效率。
这个优化思路就是告诉数据库：「你别数了，我告诉你，第10001条数据是这样的，你直接去拿吧。」
但是！！！你可能已经注意到了，这个查询太简单了，没有任何的附加查询条件，如果我需要一些额外的查询条件，比如我只要某个用户的数据 ，这种方法就行不通了。
可以见到这种思路是有局限性的，首先必须要有自增索引列，而且数据在逻辑上必须是连续的，其次，你还必须知道特征值。
如此苛刻的要求，在实际应用中是不可能满足的。
第二次优化
说起数据库查询优化，第一时间想到的就是索引，所以便有了第二次优化：先查找出需要数据的索引列（假设为 id），再通过索引列查找出需要的数据。
Select * From table_name Where id in (Select id From table_name where ( user = xxx )) limit 10000, 10;
select * from table_name where( user = xxx ) limit 10000,10
相比较结果是（500w条数据）：第一条花费平均耗时约为第二条的 1/3 左右。
同样是较大的 offset，第一条的查询更为复杂，为什么性能反而得到了提升？
这涉及到 mysql 主索引的数据结构 b+Tree ，这里不展开，基本原理就是：
子查询只用到了索引列，没有取实际的数据，所以不涉及到磁盘IO，所以即使是比较大的 offset 查询速度也不会太差。
利用子查询的方式，把原来的基于 user 的搜索转化为基于主键（id）的搜索，主查询因为已经获得了准确的索引值，所以查询过程也相对较快。
第三次优化
在数据量大的时候 in 操作的效率就不怎么样了，我们需要把 in 操作替换掉，使用 join 就是一个不错的选择。
select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)
数据区分不明显的不建议创建索引
如 user 表中的性别字段，可以明显区分的才建议创建索引，如身份证等字段。
select * from user where sex=1
原因：性别只有男，女，每次过滤掉的数据很少，不宜使用索引。
经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引。
字段的默认值不要为 null
这样会带来和预期不一致的查询结果，建议参考注意事项。
在属性上进行计算不能命中索引
select * from order where YEAR(date) < = '2017'
即使date上建立了索引，也会全表扫描，可优化为值计算：
select * from order where date < = CURDATE()
复合索引最左前缀
用户中心建立了(login_name, passwd)的复合索引
select * from user where login_name=? and passwd=? select * from user where passwd=? and login_name=?
但是使用
select * from user where passwd=?
不能命中索引，不满足复合索引最左前缀
如果明确知道只有一条记录返回
select name from user where username='xxxx' limit 1
提高效率，可以让数据库停止游标移动，停止全表扫描。
强制类型转换会全表扫描
select * from user where phone=13800001234
这样虽然可以查出数据，但会导致索引失效。
需要修改为
select * from user where phone='13800001234'
