目录
问题09：避免随机取记录,千万不要 ORDER BY RAND()
写在最前：
1、慢SQL可能会消耗了70%~90%的数据库CPU资源
2、SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低
3、SQL语句可以有不同的写法
案例SQL01：
mysql> select count(*) as sum, target_id
from `t_bim_like_target`
where `target_id` in ('135', '124')
and `target_type` = '1'
and `status` = '1'
group by `target_id`;
问题01：mycat报Java非法参数异常
ERROR 1105 (HY000): java.lang.IllegalArgumentException: all columns in group by clause should be in the selected column list.!`target_id`
解决方案：在mycat中Group by务必使用SQL标准语法（这在之前MySQL与MyCAT一些规范分享中提到过）：
SELECT
c1, c2,..., cn, aggregate_function(ci)
FROM
table
WHERE
where_conditions
GROUP BY c1 , c2,...,cn;
因为mycat使用的SQL解析器为Druidparser,标准SQL不允许使用GROUP BY子句中的别名、无引号（反引号），但MySQL支持这些选项。
即：group by target_id;
问题02：避免数据类型不一致
where `target_id` in ('135', '124')
and `target_type` = '1'
and `status` = '1'
解决方案：为了方便，用模板都统一加上，隐式类型转换有无法命中索引的风险，在高并发、大数据量的情况下，命不中索引带来的后果非常严重。由于 MySQL 隐式类型转换规则比较复杂，依赖 MySQL 隐式转换很容易出现各种想想不到的问题，而且 MySQL 隐式类型转换本身也是非常耗费 MySQL 服务器性能的，所以非常不推荐这样使用。【价值百万的 MySQL 的隐式类型转换】
where target_id in (135, 124)
and target_type = 1
and status = 1
*亮点：用IN来替换OR
案例SQL02：没有按照排序取出最新snapshot_time条件的值（默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序）
mysql> select a.account_id,a.snapshot_time
from (
select account_id,snapshot_time
from t_bim_user_wallet_snapshot
where account_id in (166233104627157953,261686155106473944)
order by snapshot_time desc
) as a
group by a.account_id;
+--------------------+---------------------+
| account_id | snapshot_time |
+--------------------+---------------------+
| 166233104627157953 | 2018-05-03 00:00:00 |
| 261686155106473944 | 2018-05-03 00:00:00 |
+--------------------+---------------------+
mysql> select account_id,snapshot_time
from t_bim_user_wallet_snapshot
where account_id in (166233104627157953,261686155106473944)
order by snapshot_time desc;
+--------------------+---------------------+
| account_id | snapshot_time |
+--------------------+---------------------+
| 166233104627157953 | 2018-05-05 00:00:00 |
| 261686155106473944 | 2018-05-05 00:00:00 |
| 166233104627157953 | 2018-05-04 00:00:00 |
| 261686155106473944 | 2018-05-04 00:00:00 |
| 166233104627157953 | 2018-05-03 00:00:00 |
| 261686155106473944 | 2018-05-03 00:00:00 |
+--------------------+---------------------+
解决方案01：
select a.account_id,a.snapshot_time
from t_bim_user_wallet_snapshot as a
where a.account_id in (166233104627157953,261686155106473944)
and a.snapshot_time =
(select max(snapshot_time)
from t_bim_user_wallet_snapshot
where account_id=a.account_id);
解决方案02：
select a.account_id,a.snapshot_time
from t_bim_user_wallet_snapshot as a
where a.account_id in (166233104627157953,261686155106473944)
and not exists(select 1
from t_bim_user_wallet_snapshot
where account_id=a.account_id
and snapshot_time > a.snapshot_time);
解决方案03：
select a.account_id,a.snapshot_time,a.power_total,a.coin_total
from t_bim_user_wallet_snapshot as a,
(select account_id,max(snapshot_time) as snapshot_time
from t_bim_user_wallet_snapshot
where account_id in (166233104627157953,261686155106473944)
group by account_id) as b
where a.account_id=b.account_id
and a.snapshot_time=b.snapshot_time;
问题03：不使用子查询
子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。
在MySQL5.6+版本里，采用join关联方式对其进行了优化。但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，固生产环境应避免使用子查询。
子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句再临时表中查询记录。查询完毕 后，MySQL需要撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。在MySQL中可以使用连接查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快。
不能优化的子查询：
1、mysql不支持子查询合并和聚合函数子查询优化；
2、mysql不支持from子句子查询优化；
3、mysql对子查询展开提供有限的支持，如对主键的操作才能进行上拉子查询优化；
4、mysql不支持exists子查询优化；
5、mysql不支持not exists子查询优化；
6、mysql对in子查询，对满足半连接语义的查询进行半连接优化，再基于代价评估进行优化；
7、mysql不支持not in子查询优化；
8、mysql对>all非关联子查询使用max函数，<all非关联子查询使用min函数，对=all和非关联子查询使用exists优化；
9、对>some和>any非关联子查询使用min函数，对<some和<any非关联子查询使用max函数，=any 和=some子查询使用半连接进行优化，对>some和>any关联子查询以及<some和<any关联子查询只有exists 优化。
解决方案04：
select a.account_id,a.snapshot_time,a.power_total,a.coin_total
from t_bim_user_wallet_snapshot as a
inner join(select account_id,max(snapshot_time) as snapshot_time
from t_bim_user_wallet_snapshot
where account_id in (166233104627157953,261686155106473944)
group by account_id) as b
on a.account_id=b.account_id
and a.snapshot_time=b.snapshot_time;
问题04：事务型存储引擎Innodb的锁定特性？
1.InnoDB锁定模式及实现机制
InnoDB的行级锁定分为两种类型，共享锁和排他锁（行锁），而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。
当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说InnoDB的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：
如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE
用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。
但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。
2.InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。
（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
3.InnoDB行锁优化建议
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
（1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：
a)尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定；
b)合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；
c)尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；
d)尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
e)在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。
（2）由于InnoDB的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的小建议：
a)类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；
b)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
c)对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。
（3）可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：
mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
问题05：字符集不一致问题
insert into `t_bim_notify_msg` (`sub_id`, `trigger_owner_id`, `trigger_id`, `trigger_type`, `target_id`, `target_type`, `target_owner_id`, `content`, `ext`) values (0, 238929325792721985, 4, 3, 237491036517069931, 3, 237491036517069931, '开始关注你了！', '{"jump_url":"\/pages\/others?accountId=238929325792721985","target_owner_id":237491036517069931,"account":{"account_id":"238929325792721985","nick_name":"?","avatar":"https:\/\/pic.lehe.com\/pic\/_o\/aa\/95\/ace0d98f1ba1505faf0f79b3f118_450_800.cz.jpg_1e5fcc8b_s1_q1_90_750_4000.jpg"},"raw_data":{"target_id":237491036517069931,"target_type":"user","account_id":238929325792721985,"tend_type":"follow","reverse_search":1}}');
ERROR 1366 (HY000): Incorrect string value: '\xE7\x8E\x9E\x97' for column
众所周知UTF-8是3个字节, 其中已经包括我们日常能见过的绝大多数字体. 但3个字节远远不够容纳所有的文字, 所以便有了utf8mb4, utf8mb4是utf8的超集, 占4个字节, 向下兼容utf8. 我们日常用的emoji表情就是4个字节了.
所以在此我们像utf8的数据表插入数据就会报出Incorrect string value这个错误.
解决方案：统一字符集为utf8mb4
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
修改后如下：(vim my.cnf)
character-set-server=utf8mb4
#跳过mysql程序起动时的字符参数设置 ，使用服务器端字符集设置
skip-character-set-client-handshake=1
collation-server=utf8mb4_bin
init-connect='SET NAMES utf8mb4'
mysql> show variables like '%char%';
+--------------------------+------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /data/mysql-5.7.16/share/charsets/ |
+--------------------------+------------------------------------+
问题06：避免函数索引
例：SELECT a,b,c FROM t WHERE YEAR(d) >= 2016;
由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。
解决方案：应改为—–>
SELECT a,b,c FROM t WHERE d >= ‘2016-01-01’;
问题07：LIKE左百分号无法使用到索引
SELECT a,b,c FROM t WHERE name LIKE ‘%de%’;
—–>
SELECT a,b,c FROM t WHERE name LIKE ‘de%’;
目前只有MySQL5.7支持全文索引（支持中文）
问题08：读取适当的记录LIMIT M,N
SELECT a,b,c FROM t;
—–>
SELECT a,b,c FROM t LIMIT 10;
问题09：避免随机取记录,千万不要 ORDER BY RAND()
如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得 不去执行RAND()函数（很耗CPU时间），而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序）
SELECT username FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 1;
MySQL不支持函数索引，会导致全表扫描
可以这样—–>
SELECT username FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 1;
这样会更好—–>
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user WHERE id > $rand LIMIT 1");
问题10：避免 SELECT *
从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。
问题11：永远为每张表设置一个ID
应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。
就算是你 users 表有一个主键叫“account_id”的字段，也别让它成为主键。使用 VARCHAR 类型或者bigint来当主键会使用得性能下降。
问题12：把IP地址存成 UNSIGNED INT
很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当 你需要使用这样的WHERE条件：IP between ip1 and ip2。
我们必需要使用UNSIGNED INT，因为 IP地址会使用整个32位的无符号整形。而你的查询，你可以使用 INET_ATON() 来把一个字符串IP转成一个整形，并使用 INET_NTOA() 把一个整形转成一个字符串IP。在PHP中，也有这样的函数 ip2long() 和 long2ip()。
3575
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
