这么骚的SQL进阶技巧，不怕被揍么？( 五 ) _SQL

不推荐：
SELECTfirst_name,last_name,homeroom_nbrFROM StudentsWHERE homeroom_nbr LIKE 'A-1%'; 推荐：
SELECT first_name, last_name homeroom_nbrFROM StudentsWHERE homeroom_nbr LIKE 'A-1__'; --模式字符串中包含了两个下划线 ?尽量使用自增 id 作为主键
比如现在有一个用户表，有人说身份证是唯一的，也可以用作主键，理论上确实可以，不过用身份证作主键的话，一是占用空间相对于自增主键大了很多，二是很容易引起频繁的页分裂，造成性能问题。
主键选择的几个原则：自增，尽量小，不要对主键进行修改。
?如何优化 count(*)
使用以下 SQL 会导致慢查询：
SELECT COUNT(*) FROM SomeTable SELECT COUNT(1) FROM SomeTable 原因是会造成全表扫描，有人说 COUNT(*) 不是会利用主键索引去查找吗，怎么还会慢，这就要谈到 MySQL 中的聚簇索引和非聚簇索引了。
聚簇索引叶子节点上存有主键值+整行数据，非聚簇索叶子节点上则存有辅助索引的列值+主键值，如下：

文章插图

所以就算对 COUNT(*) 使用主键查找，由于每次取出主键索引的叶子节点时，取的是一整行的数据，效率必然不高。
但是非聚簇索引叶子节点只存储了「列值+主键值」，这也启发我们可以用非聚簇索引来优化，假设表有一列叫 status ，为其加上索引后，可以用以下语句优化：
SELECT COUNT(status) FROM SomeTable 有人曾经测过(见文末参考链接) ，假设有 100 万行数据，使用聚簇索引来查找行数的，比使用 COUNT(*) 查找速度快 10 几倍。不过需要注意的是通过这种方式无法计算出 status 值为 null 的那些行。
如果主键是连续的，可以利用 MAX(id) 来查找， MAX 也利用到了索引，只需要定位到最大 id 即可，性能极好，如下，秒现结果：
SELECT MAX(id) FROM SomeTable 说句题句话，有人说用 MyISAM 引擎调用 COUNT(*) 非常快，那是因为它提前把行数存在磁盘中了，直接拿，当然很快，不过如果有 WHERE 的限制。
?避免使用 SELECT * ，尽量利用覆盖索引来优化性能
SELECT * 会提取出一整行的数据，如果查询条件中用的是组合索引进行查找，还会导致回表(先根据组合索引找到叶子节点，再根据叶子节点上的主键回表查询一整行) ，降低性能。
而如果我们所要的数据就在组合索引里，只需读取组合索引列，这样网络带宽将大大减少,假设有组合索引列 (col_1, col_2) 。
推荐用：
SELECT col_1, col_2FROM SomeTableWHERE col_1 = xxx AND col_2 = xxx 不推荐用：
SELECT *FROM SomeTableWHERE col_1 = xxx ANDcol_2 = xxx ?如有必要，使用 force index() 强制走某个索引
业务团队曾经出现类似以下的慢 SQL 查询：

SELECT *FROMSomeTableWHERE `status` = 0AND `gmt_create` > 1490025600AND `gmt_create` < 1490630400AND `id` > 0AND `post_id` IN ('67778', '67811', '67833', '67834', '67839', '67852', '67861', '67868', '67870', '67878', '67909', '67948', '67951', '67963', '67977', '67983', '67985', '67991', '68032', '68038'/*... omitted 480 items ...*/) order by id asc limit 200;

post_id 也加了索引，理论上走 post_id 索引会很快查询出来，但实现了通过 EXPLAIN 发现走的却是 id 的索引(这里隐含了一个常见考点，在多个索引的情况下, MySQL 会如何选择索引) 。
而 id > 0 这个查询条件没啥用，直接导致了全表扫描，所以在有多个索引的情况下一定要慎用。
可以使用 force index 来强制走某个索引，以这个例子为例，可以强制走 post_id 索引，效果立杆见影。
这种由于表中有多个索引导致 MySQL 误选索引造成慢查询的情况在业务中也是非常常见。
一方面是表索引太多，另一方面也是由于 SQL 语句本身太过复杂导致，针对本例这种复杂的 SQL 查询，其实用 ElasticSearch 搜索引擎来查找更合适，有机会到时出一篇文章说说。