本文记录了一些MySQL中使用的语句优化资料，随时补充，随时查看。
1、优化SQL语句的一般步骤
① 查询慢查询日志
(1) show variables like '%slow%';
------------------------------
log_slow_queries ON --慢查询日志
(2) 开启慢查询
配置文件my.cnf，新增行
log_slow_queries=slow.log --开启慢查询
long_query_time=5 --慢查询时间
② explain或desc分析SQL语句
格式：在sql语句前加explain或desc，如explain|desc select * from t1;
explain列的解释：
table:显示这行数据关于哪张表
type:重要列，显示使用了哪种类型。从最好到最差的依次为：
a) system 表仅一行
b) const 只一行匹配
c) eq_reg 对于前面的每一行使用主键和唯一
d) ref 普通索引，同rq_ref，但没有使用主键和唯一
e) ref_or_null 同前面对null查询
f) index_merge 索引合并优化
g) unique_subquery 主键子查询
h) index_subquery 非主键子查询
i) range 表单中的范围查询
j) index 都通过查询索引来得到数据
k) all 通过全表扫描得到的数据
possible_keys:显示可能应用的索引。如果为空，没有可能的索引。
key:实际使用的索引，如果为NULL，则没有使用索引。
key_len:使用的索引长度，索引中某一列的最大长度。在不损失精确性的情况下，长度越短越好
ref:显示索引的那一列被使用了，如果可能的话，是一个常数
rows:影响行数，重点观察
2、索引问题
① 使用like关键字对索引的影响
a、%在前，则无法使用索引，匹配时数据库会搜索所有行，如：select * from t1 where name like '%5';
b、%在后，可以使用索引，如：select * from t1 where name like 'user%';
c、null查询，也可以使用索引，如：select * from t1 where name is null;
② 这些情况存在索引但MySQL不使用索引
a、如果MySQL估计使用索引比全表扫描更慢，则不使用索引。如列key_part均匀分布在1到100之间，查询时不建议使用索引。
b、如果使用menory/heap表并且where条件中不使用"="进行索引列，则不会使用索引。Heap表只有在"="的条件下才会使用索引。
c、用or分隔开的条件，如果or前面的条件列有索引，而后面的条件列没有索引，则设计的索引均不使用。
d、字段类型为字符串，但搜索时使用数字匹配，则不使用索引。如name="88",SQL:select * from t1 where name=55;
③ 分析Handler_read_rnd_next值，判断是否需要使用索引。
show status like 'Handler_read%';
Handler_read_rnd_next值代表"在数据文件中读取下一行的请求数"，Handler_read_rnd_next值越大，则说明查询低效，应结合慢查询日志尽快建立相应的索引补救。
3、两个简单使用的优化方法
① 定期检查和优化表
1) 检查表
check table table_name --检查表的状态，是否有错误
2) 优化表。如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很大的改动，则需要做定期优化。这个命令可以将表的空间碎片进行合并。
OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE table_name [,table_name]
注：optimize只能优化MyISAM、BDB和InnoDB表起作用
如：optimize table t1;
② 大批量导入导出数据Load
1) 导出数据
例：select name from t1 into outfile "/test/test.txt"; --导出数据，导出文件中只保存数据，导出数据速度很快,windows文件路径格式如"D:\\test.txt"
2) 导入数据
例：load data infile "/test/test.txt" into table t1(name); --导入数据
几个提高导入速度的小技巧：
(1) 导入时再次优化，对MyISAM表进行数据导入时，可以暂时关闭非唯一索引，导入完成后开启并统一设置索引，这样能提高整体速度。
例：a、alter table t1 disable keys; --暂时关闭非唯一索引
b、load data infile "/test/test.txt" into table t1(name); --导入数据
c、alter table t1 enable keys; --开启索引
(2) 如确定导入数据不重复，可暂时关闭唯一索引，提高导入速度
set unique_checks=0; --关闭唯一性校验
set unique_checks=1; --恢复唯一性校验
InnoDB表，可以暂时关闭自动提交，可提高导入速度
set autocommit=0; --关闭自动提交
set autocommit=1; --开启自动提交
4、常用SQL的优化
① insert插入数据时，每插入一条数据，客户端与数据库服务端便进行一次连接和关闭，非常消耗资源，可一次性插入全部数据，最后关闭连接
格式：insert into t1(name) values("user1"),("user2"),("user3");
order by null优化group by语句。使用group by语句时，结果通常会按升序排序，会消耗性能和时间，可使用order by null来禁止排序
③ 尽量避免使用嵌套查询，嵌套外层可能无法使用索引
如: select * from t1 where id=(select id from t2); --此时t1表id无法使用索引，应尽量避免使用这种语句
->应改成： select t1.* from t1,t2 where t1.id=t2.id;
5、MySQL读锁与写锁
① 读锁。设置读锁后，所有的客户端只允许读取数据，不允许新增、修改和删除数据。
读锁：lock table t1 read;
解锁：unlock tables; --只能解锁所有表，不能针对一个表进行解锁
注：当一个表设置读锁后，当其他客户端对该表进行新增、修改或删除操作时，客户端执行语句会"卡"住，直至解除表锁，客户端语句会自动执行。
② 写锁。设置写锁后，当前客户端可以进行增删改查操作，但其他客户端不允许进行增删改查操作。
设置写锁：lock table t1 write;
解锁：unlock tables;
6、四种字符集。建议将四种字符集均设置成utf8。
mysql > \s --查看当前数据库字符集配置状态
mysql > show character set; --查看字符集与对应的校验字符集状态
7、root密码丢失，常用处理办法。
1) 结束mysql进程：service mysqld stop
2) 启动mysql，跳过授权表：mysqld_safe --skip-grant-tables --user=mysql &
3) 登陆mysql：mysql -uroot
4) 设置密码：a、update user set password=password("123456") where user='root' and host='localhost';
b、set password for root@localhost=password("wei");
5) 刷新权限：flush privileges;
8、查询中文首字符
Select f_name ,
ELT(INTERVAL(CONV(HEX(left(CONVERT(f_name USING gbk),1)),16,10),
0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,
0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P',
'Q','R','S','T','W','X','Y','Z') as PY
FROM tableView Code
1、db优化
1) 是否需要外键？优点：db层保证数据一致性 缺点：写数据会执行一致性检查，会带来额外开销
2) 索引：修改性能与检索性能的矛盾体 ① 经常搜索的列加索引；② 经常排序的列加索引；
3) 表引擎：（show engines查看当前引擎）
a、MyISAM：【表锁】不支持事务、行级锁、外键
①静态MyISAM（所有字段长度固定，如chat） ②动态MyISAM（存在长度不固定的字段，如varchar） ③压缩MyISAM，存储空间小，但读取需要解压缩
b、InnoDB：【行锁】支持事务、行级锁、外键，读取效率低
c、memory(heap)：内存，散列索引，常用于临时表
d、archive：只支持select和insert语句，不支持索引。常用于日志记录和聚合分析
4) 动态/静态字段： 如varchar和char，对于固定长度的字段，强烈建议使用固定长如类型，如char
2、sql优化
1) 使用limit 1避免全表扫描：对于未加索引的字段检索记录时，使用limit 1，检索到1条即停止；否则会执行全表扫描
