写在开头
对于开发者来说，数据库几乎是不可避开的一道坎，我们平时的很多业务操作也就是对数据进行CRUD，数据库入门容易，但是入土也容易。在工作过程中对于海量数据的操作不能仅仅只依靠简单的SQL，更要对SQL底层有一定了解，熟练掌握对SQL的优化。
书到用时候方恨少，在小公司中，开发的技术和深度可能并不会要求很高很深。小公司业务的数据量太小，很多时候简单的SQL就够用，往往会让自己对自身技术产生懈怠。长此以往，难顶。深入技术，让自己更有价值。
插入数据优化
普通插入:
-
采用批量插入（一次插入的数据不建议超过1000条），如果数据量大，我们将总体数据分多次批量插入。避免对大数据量进行逐一单条插入，单条插入会进行开启数据库连接，基本上很多操作，open conn会花很多时间
insert into t_user values(1, 'A'),(2, 'B'); -
手动提交事务
start transaction; insert into t_user values(1, 'A'),(2, 'B'); insert into t_user values(...)(...); insert into t_user values(...)(...); commit; -
主键顺序插入
主键顺序插入 1, 2, 3, 4, 5, 6, 7 主键乱序插入 7, 3, 2, 5, 1, 4, 6
大批量插入：
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入。
# 客户端连接服务端时，加上参数 --local-infile（这一行在bash/cmd界面输入）
mysql --local-infile -u root -p
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
select @@local_infile;
# 执行load指令将准备好的数据，加载到表结构中,fields terminated by字段间使用什么分隔，lines terminated by行之间使用什么分隔
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
主键优化
数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（Index organized table, IOT）
页分裂
页分裂：页可以为空，也可以填充一半，也可以填充100%，每个页包含了2~N行数据（如果一行数据过大，会行溢出），根据主键排列。
上图所示，主键的顺序插入不会产生啥页分裂，但是主键的乱序插入会产生页分裂。请看如下
上图中，我的两个page已经按照顺序插入的方式占满了位置，但此时插入了一个id=50的主键，此时他会直接取写入第三个数据页吗？不会，因为叶子节点是有序的，50进来了，就应该存放在47之后
但此时，注意我的1#数据页已经写满了，此时InnoDB就会进行一个操作：开启一个新的数据页，然后找到1#数据页50%的位置，然后将这段数据移动到新的数据页，然后将50插入到47后面
但此时，InnoDB就需要对链表进行重新设置指向，保证有序性。这就是页分裂
页合并
页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录到达 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前后）看看是否可以将这两个页合并以优化空间使用。
上图就是2#数据页的数据被删除半数以上，此时InnoDB就会再前后页中查看是否可以合并页来优化空间使用
此时就发现3#数据也空出挺多，所以直接把3#数据页的数据移动到2#数据页中，此时再插入数据就往后插了
MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或创建索引时指定
主键设计原则：
● 满足业务需求的情况下，尽量降低主键的长度
● 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键
● 尽量不要使用 UUID 做主键或者是其他的自然主键，如身份证号
● 业务操作时，避免对主键的修改
为啥尽量降低主键的长度？
我们都知道，非聚集索引的叶子节点中存储的value其实就是主键id，当主键过长，就会增加检索时的磁盘IO
为啥不要使用UUID做主键？
因为UUID很多时候都是乱序的，很有可能会出现页分裂的现象
https://www.jianshu.com/p/fea005267083
对于InnoDB这种聚集主键类型的引擎来说，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力，此时不适合使用UUID做物理主键，可以把它作为逻辑主键，物理主键依然使用自增ID。
Order by优化
-
Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
-
Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高
如果order by字段全部使用升序排序或者降序排序，则都会走索引(默认索引都是升序的)，但是如果一个字段升序排序，另一个字段降序排序，则不会走索引，explain的extra信息显示的是Using index, Using filesort。
如果要优化掉Using filesort，则需要另外再创建一个索引，如：
create index idx_user_age_phone_ad on tb_user(age asc, phone desc);
此时使用select id, age, phone from tb_user order by age asc, phone desc;会全部走索引
总结：
● 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
● 尽量使用覆盖索引，避免使用select *, 如select id, age, phone from tb_user order by age asc, phone desc;其中要查询的id, age, phone不就是索引字段的值嘛，因此不需要额外查询，但是如果是select *，其他没有在索引中涉及到的，就需要回表查询
● 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
● 如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）
show variables like 'sort_buffer_size',如果大数据量排序超过了这个size，就会在涉及到磁盘文件，在磁盘文件中排序，性能就非常低
group by优化
● 在分组操作时，可以通过索引来提高效率
● 分组操作时，索引的使用也是满足最左前缀法则的
如索引为idx_user_pro_age_stat，则句式可以是select ... where profession order by age，这样也符合最左前缀法则
limit 优化
常见的问题如limit 9000000, 10，此时需要 MySQL 排序前9000000条记录，但仅仅返回9000000 - 9000010的记录，其他记录丢弃，查询排序的代价非常大。
优化方案：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化
覆盖索引：是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
例如：
-- 此语句耗时较长
select * from tb_sku limit 9000000, 10;
-- 通过覆盖索引加快速度，直接通过主键索引进行排序及查询
select id from tb_sku order by id limit 9000000, 10;
-- 下面的语句会报错，因为 MySQL 不支持 in 里面使用 limit，但是可以换种方式
-- select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
-- 通过将子查询数据变成一张表来进行连表查询即可实现与覆盖索引实现的同等效果
select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;
count 优化
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高（前提是不使用where，单纯看表中有多少条数据）；
InnoDB 在执行 count(*) 时，需要把数据一行一行地从引擎里面读出来，然后累计计数。
优化方案：自己计数，如创建key-value表存储在内存或硬盘，或者是用redis
count的几种用法：
● 如果count函数的参数（count里面写的那个字段）不是NULL（字段值不为NULL），累计值就加一，最后返回累计值
● 用法：count()、count(主键)、count(字段)、count(1)
● count(主键)跟count()一样，因为主键不能为空；count(字段)只计算字段值不为NULL的行；count(1)引擎会为每行添加一个1，然后就count这个1，返回结果也跟count(*)一样；count(null)返回0
各种用法的性能：
● count(主键)：InnoDB引擎会遍历整张表，把每行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为空）
● count(字段)：没有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
● count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一层，放一个数字 1 进去，直接按行进行累加
● count(*)：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加
按效率排序：count(字段) < count(主键) < count(1) <= count(*)，所以尽量使用 count(*)
update优化（避免行锁升级为表锁）
InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁，会降低数据库的并发访问性。
如以下两条语句：
update student set no = '123' where id = 1;，这句由于id有主键索引，所以只会锁这一行；
update student set no = '123' where name = 'Jack';，这句由于name没有索引，所以会把整张表都锁住进行数据更新，优化方式是给name字段添加索引
2198
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
