If you have ever worked with MySQL, you inevitably came across character sets and collations. In this blog post, we will try to give you a more in-depth look at what those two are and how you should use them. What Are Character Sets and Collations? Simply put, character sets in MySQL are sets of symbols and encodings – collations are sets of rules for comparing characters in a character set....
对于order by的优化，MySQL若可以利用索引的有序性进行排序，则优先使用索引进行排序，这种情况的执行效率是最快的；若无法有效利用索引的情况下，MySQL主要有3排序种算法对其进行优化每个算法都有一定的适用场景。 一、 利用索引排序 B-tree索引可以很好的支持单点查询、范围查询、有序性查询。所以对于order by 的排序查询，我们可以利用B-tree的有序性来有效的利用索引进行排序查询。当然，如果可以利用索引进行排序对我们的SQL查询本身也是有一定的要求限制的。 1.1 利用索引排序的特点 排序列必须有B-tree索引 如果为多表关联查询，排序列必须是对驱动表字段的排序 1.2、示例 ##建表语句，sbtest3与sbtest4表字段与索引一致，sbtest3的表数据量为30000，sbtest4的表数据量为60000 CREATE TABLE `sbtest4` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_4` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=62768 DEFAULT CHARSET=utf8mb4 ##单表排序查询 ##order by字段为B-tree索引字段，可以看到执行计划有效利用了索引进行排序查询 root@mysql57 13:25: [db2]> explain select * from sbtest4 t4 order by t4.k desc limit 5; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t4 | NULL | index | NULL | k_4 | 4 | NULL | 5 | 100....
长度范围是0到65535 varchar(255) 和 varchar(256)的区别 长度超过255时，用2个字节存储列的实际长度，未超过时用一个字段