MYSQL日常

最新推荐文章于 2024-07-09 08:45:00 发布

zxc7928932

最新推荐文章于 2024-07-09 08:45:00 发布

阅读量283

点赞数

本文链接：https://blog.csdn.net/zxc7928932/article/details/50726225

版权

一：

"insert into tblPointDetail (courseId,gradeId,title,ext,parentId,inTree)values(3,1,unhex('".bin2hex('后的结果')."'),'{"level":3}',21748,1);"

bin2hex

unhex

这样插入数据库转换可以防止乱码

二

字段命名不要用关键字会有很多坑

字段名用关键字执行查询要用``

三

执行大量插入时，单条插入的效率要比一条插入n条数据的概率要低很多

详情

http://stackoverflow.com/questions/1793169/which-is-faster-multiple-single-inserts-or-one-multiple-row-insert

四

数据量大时，执行join查询不管怎么优化都会花大量时间建临时表

select tblA.tid,tblB.pointId from tblA left join pv on tblA .tid=tblB.tid order by tblB.id desc limit 1;
+----------+---------+
| tid | pointId |
+----------+---------+
| 66457568 | 4509 |
+----------+---------+
1 row in set (29.35 sec)

mysql> describe select tblA.tid,tblB.pointId from tblA left join pv on tblA .tid=tblB.tid order by tblB.id desc limit 1;
+----+-------------+-----------------+-------+---------------+-----------+---------+------------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+-----------+---------+------------------------+---------+----------------------------------------------+
| 1 | SIMPLE | tblA| index | NULL | main_sort | 12 | NULL | 9085324 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | pv | ref | ti_pv | ti_pv | 4 | tblB.tblA.tid | 1 | Using index |
+----+-------------+-----------------+-------+---------------+-----------+---------+------------------------+- --------+----------------------------------------------+
2 rows in set (0.00 sec

rows列为查询必须检查的行数
join查询为两个simple查询
第一个扫描全表建立临时表（left查询的左表tblSubjectPoint ）
然后根据join的字段tid去查询pv表然后查到返回（tid,id覆盖查询）
数据检索的顺序是按pv.id排序

五关于NULL

SELECT XXX WHERE A IS NULL;
NULL与任何返回都是假，WHERE A !=1 ,NULL的行不返回。
null为不知道

六关于索引使用的情况

where条件筛选度低可能不用索引
筛选度低的索引条件应该放在右侧

七关于随机取数

SELECT floor(RAND() * (SELECT MAX(id) FROM`users`));

+-----------------------------------------------+

| floor(RAND() * (SELECT MAX(id) FROM`users`)) |

+-----------------------------------------------+

| 2665|

+-----------------------------------------------+

1 row in set (0.00 sec)

describe SELECT floor(RAND() * (SELECTMAX(id) FROM `users`));

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

2 rows in set, 1 warning (0.00 sec)

(Select tables optimized away :

Thequery contained only aggregate functions (MIN(), MAX()) that were all resolvedusing an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizerdetermined that only one row should be returned.

Basically this means your query uses datathat is directly available to MySQL and the query will run in constant time.

)

Max（某索引）是最优化的查询立即返回//Select tables optimized away

SELECT FLOOR( MAX(id) * RAND()) FROM users;

+--------------------------+

| FLOOR( MAX(id) * RAND()) |

+--------------------------+

| 27006 |

+--------------------------+

1 row in set (0.03 sec)

describe SELECT FLOOR( MAX(id) * RAND())FROM users;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+