mysql基本用法及SQL优化全面总结（不断更新）

使用规范注意点：

1、版本：最新为2018年发布的8.0版本，与5.7版本有很多差异，5.7为目前最稳定版本，本文以5.7为例。

2、字符集：创建库时必须选择好字符集，引擎默认使用InnoDB。如创建数据库基字符集使用utf8 ，数据库排序规则使用utf8_general_ci字符集；建库设置好字符集，建表默认就行，也可以再次确认表字符集和引擎：show table status from 库名 like  “表名”;  (注意此时表名必须加英文双引号，库名不必)。

3、管理软件：mysql可以使用数据库管理软件来管理，也可以在cmd命令行还管理。考虑到实际项目的运维场景，推荐使用命令行来管理数据库。在开发测试阶段也可以使用数据库管理软件，运行各项指标可视化，节省调试时间。推荐sqlyog或者navicat。

    就二者的sql语句而言，是可以通用的，也就是说，在数据库管理软件里执行的sql，在cmd命令行也可以直接执行。

4、sql写法的一般规范：字符型字段，使用`包裹(英文输入的上波浪线)，字符型数值，使用英文状态下的双引号" 。规范的写法可以避免数据库内部的隐性类型转换，消耗性能。比如查询字符型数据，但刚好数据刚好是数字，此时不加"也可以查询结果，但是mysql内部需要将类型先做转换，是很消耗性能的。

5、出于极致性能考虑的设计原则(SQL优化)：

 1) 参考第4点，书写格式要规范

 2)尽量只查询需要的字段，而非使用通配符*，查询全部数据。

 3)建立索引，索引优先级：主键索引-唯一索引-普通索引-复合索引，但索引不是越多越好，经常做查询条件的字段，必须建索引。索引建立之后，执行事务时数据库将有表锁变成行锁，大大提高效率。索引过多，将导致表增删改操作时，更新索引消耗系统性能。如果建索引，就是使用innodb引擎，依然默认是表锁，而非行锁。

4)where子句，尽量不使用函数或者条件表达式，使用后索引失效，降低性能。

5)执行事务的sql语句，尽量以主键作为查询条件。达到最佳性能。

6)多表联合查询，建立外键，因为外键本身就是索引。

7)like条件子句中，条件子句第一个使用%通配符，将导致索引失效，一定要用才使用。不然尽量不要在条件首字符使用%。

8)避免使用!=或<>或IS NULL、IS NOT NULL、IN、NOT IN，使用后索引失效。全表扫描。

9）如果可以同时使用字符型和数值型的字段，优先使用数值型。

10)主查询相对于子查询数据量明显大，高效的方式，where子句使用where 字段 in(子查询)，反之，使用where exists(子查询)；

11)使用order by 语句时，如果数据量大，需要调大buffer区容量，提高查询速度。执行 set max_lenght_for_sort_data = 2048; 单位是字节byte，默认1024。统一使用升序或统一使用降序。

12)同时执行多条SQL语句时，使用慢查询日志，找出执行缓慢的SQL语句。

6、索引失效的原因：

1)使用!=或<>,IS NULL、IS NOT NULL、IN、NOT IN，可以使用or代替。

2)错误的不匹配的数据类型。

3)使用函数。

4)like条件表达式以%或者_等通配符开头。如果需要使用通配符，考虑使用索引覆盖，在使用通配符的情况下提高一定的性能。

5)复合索引中，除上述情形，跨列或无序使用索引时，索引也将失效。

7、sql语句语法总结：

切换数据库：use 数据库名;

查看表已创建索引：show index from 表名;

创建普通索引(含复合索引)：alter table 表名 add index  索引名(column1,column2...);

         或者使用：create (unique) index 索引名 on 表名(column1,column2....);

创建唯一索引：alter table 表名 add unique(column);  创建之后如果插入该列已存在的记录，将报错。

创建主键索引：alter table 表名 add primary key(column);

查询重复字段的所有信息：select * from user group by name having count(name)>1;  

查看慢查询日志 是否开启：show variables like '%slow_query_log%';

临时开启慢查询日志(重启失效)：set global slow_query_log = 1;

永久开启慢查询日志，修改mysql配置文件[mysqld]标签下增加两行配置。

使用for update给查询语句加锁：select name from user where id = 1 for update;