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

古城微笑少年丶 2021-11-11 04:28 304阅读 0赞

### 使用规范注意点： ###

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)错误的不匹配的数据类型。