# 联合索引 a b c
select * from table1 where a=xxx and c=xxx and b=xxx;#经过优化器优化后可以使用索引,
mysql> show engines
-> ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
TODO 具体存储引擎和相关使用场景待补充
Mysql支持的索引类型:我们最常用的是B-TREE索引,但是mysql还有另外一种索引,就是HASH索引https://blog.csdn.net/oChangWen/article/details/54024063
# hash
create table testhash(
fname varchar(50) not null,
lname varchar(50) not null,
key using hash(fname)) engine=memory;
# b-tree
CREATE TABLE t(
aid int unsigned not null auto_increment,
userid int unsigned not null default 0,
username varchar(20) not null default ‘’,
detail varchar(255) not null default ‘’,
primary key(aid),
unique key(uid) USING BTREE,
key (username(12)) USING BTREE — 此处 uname 列只创建了最左12个字符长度的部分索引
)engine=InnoDB;
使用场景
b-tree 索引原理示意图(二叉树为例) 中序
CREATE TABLE `t_user` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`email` varchar(30) DEFAULT NULL COMMENT '邮箱',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`telephone` varchar(30) DEFAULT NULL COMMENT '电话',
`status` tinyint(4) DEFAULT NULL COMMENT '0:正常 1:下线 ',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP comment '创建时间',
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP comment '更新时间',
PRIMARY KEY (`id`),
KEY `idx_email` (`email`),
KEY `idx_name` (`name`),
KEY `idx_telephone` (`telephone`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
# 插入一条数据
INSERT INTO test.t_user (name, email, age, telephone, status, created_at, updated_at) VALUES ('jimi', 'ffdsa', 11, '15001262936', 0, DEFAULT, DEFAULT);
# 批量插入数据
INSERT INTO test.t_user select null, name, email, age, telephone, 0, null, null from t_user;
mysql> select * from t_user where id='2424786gafafdfdsa';
+---------+------+-------+------+-------------+--------+------------+------------+
| id | name | email | age | telephone | status | created_at | updated_at |
+---------+------+-------+------+-------------+--------+------------+------------+
| 2424786 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL |
+---------+------+-------+------+-------------+--------+------------+------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_user where id='2426gafafdfdsa';
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
mysql> select * from t_user where email=0 limit 10;
+----+------+-------+------+-------------+--------+---------------------+---------------------+
| id | name | email | age | telephone | status | created_at | updated_at |
+----+------+-------+------+-------------+--------+---------------------+---------------------+
| 2 | jimi | ffdsa | 11 | 15001262936 | 0 | 2020-11-27 14:33:57 | 2020-11-27 14:33:57 |
| 3 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL |
| 4 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL |
| 5 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL |
| 7 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL |
| 8 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL |
| 9 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL |
| 10 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL |
| 14 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL |
| 15 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL |
+----+------+-------+------+-------------+--------+---------------------+---------------------+
10 rows in set, 10 warnings (0.00 sec)
mysql> select * from t_user where email='ffdsaADFG';
+---------+------+-----------+------+-------------+--------+------------+------------+
| id | name | email | age | telephone | status | created_at | updated_at |
+---------+------+-----------+------+-------------+--------+------------+------------+
| 2424786 | jimi | ffdsaADFG | 11 | 15001262936 | 0 | NULL | NULL |
+---------+------+-----------+------+-------------+--------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from t_user where email='ffdsaadfg';
+---------+------+-----------+------+-------------+--------+------------+------------+
| id | name | email | age | telephone | status | created_at | updated_at |
+---------+------+-----------+------+-------------+--------+------------+------------+
| 2424786 | jimi | ffdsaADFG | 11 | 15001262936 | 0 | NULL | NULL |
+---------+------+-----------+------+-------------+--------+------------+------------+
1 row in set (0.00 sec)
# 解决大小写问题
#utf8_general_ci,表示不区分大小写;utf8_general_cs表示区分大小写;utf8_bin表示二进制比较,也可以比较大小写
ALTER TABLE t_user MODIFY COLUMN email VARCHAR(30) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;
mysql> select * from t_user where email='ffdsaadfg';
Empty set (0.00 sec)
mysql> select * from t_user where email=123;
;
+---------+------+--------+------+-------------+--------+------------+------------+
| id | name | email | age | telephone | status | created_at | updated_at |
+---------+------+--------+------+-------------+--------+------------+------------+
| 2424789 | jimi | 123abc | 11 | 15001262936 | 0 | NULL | NULL |
+---------+------+--------+------+-------------+--------+------------+------------+
1 row in set, 65535 warnings (2.57 sec)
mysql> explain select * from t_user where email=123;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t_user | NULL | ALL | idx_email | NULL | NULL | NULL | 2090340 | 10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
/**
* Notes:布尔类型转换
* User: zhangguofu
* Date: 2020/12/1
* Time: 4:35 下午
*/
public function test1()
{
$a = 2;
$b = 3;
if ($a = 3 || $b = 6) {
$a++;
$b++;
}
echo $a . " " . $b;//1 4
}
/**
* Notes:字符串 数字类型转换
* User: zhangguofu
* Date: 2020/11/26
* Time: 8:01 下午
*/
public function test2()
{
$a = 'a';
$b = 'b';
$a++;
var_dump($a == $b);//true
}
/**
* Notes:字符串 数字 弱类型对比和转换
* User: zhangguofu
* Date: 2020/12/4
* Time: 3:12 下午
*/
function test3()
{
var_dump(md5('240610708') == md5('QNKCDZO')); //true
var_dump("admin" == 0); //true
var_dump("1admin" == 1); //true
var_dump("admin1" == 1);//false
var_dump("admin1" == 0);//true
var_dump("0e123456" == "0e4456789"); //true
var_dump(0 == "a"); // 0 == 0 -> true
var_dump("1" == "01"); // 1 == 1 -> true
var_dump("10" == "1e1"); // 10 == 10 -> true
var_dump(100 == "1e2"); // 100 == 100 -> true
}
mysql> explain select * from t_user where email=123;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t_user | NULL | ALL | idx_email | NULL | NULL | NULL | 2090340 | 10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
#课程表
create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
#教师表
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);
# 教师证
create table teacherCard
(xf
tcid int(3),
tcdesc varchar(200)
);
insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);
insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);
insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;
mysql> explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid
-> and t.tcid = tc.tcid and c.cid = 2 or tc.tcid=3 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | tc | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> explain select * from teacherCard limit 1;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | teacherCard | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
mysql> explain select cr.cname from ( select * from course where tid = 1 union select * from course where tid = 2 ) cr ;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 2 | DERIVED | course | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
| 3 | UNION | course | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
mysql> create table test01
-> (
-> tid int(3),
-> tname varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> insert into test01 values(1,'a') ;
Query OK, 1 row affected (0.01 sec)
mysql> alter table test01 add constraint tid_pk primary key(tid) ;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from (select * from test01 )t where tid =1 ;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test01 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
ref:非唯一性索引,对于每个索引键的查询,返回匹配的所有行(0,多)
mysql>
mysql> alter table test01 drop primary key ;
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> create index test01_index on test01(tid) ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from (select * from test01 )t where tid =1 ;
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test01 | NULL | ref | test01_index | test01_index | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
eq_ref:唯一性索引:对于每个索引键的查询,返回匹配唯一行数据(有且只有1个,不能多 、不能0)
mysql> alter table teacherCard add constraint pk_tcid primary key(tcid);
ERROR 1068 (42000): Multiple primary key defined
mysql> alter table teacher add constraint uk_tcid unique index(tcid) ;
ERROR 1061 (42000): Duplicate key name 'uk_tcid'
mysql>
mysql>
mysql> explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+
| 1 | SIMPLE | t | NULL | index | uk_tcid | uk_tcid | 5 | NULL | 3 | 100.00 | Using where; Using index |
| 1 | SIMPLE | tc | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t.tcid | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
range:检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效 ,从而转为 无索引all--5.7以前的版本)
mysql> alter table teacher add index tid_index (tid) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select t.* from teacher t where t.tid in (1,2) ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | tid_index | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select t.* from teacher t where t.tid <3 ;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t | NULL | range | tid_index | tid_index | 5 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
index:查询全部索引中数据,不需要回表查找,黄金索引
mysql> explain select tid from teacher ;
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | teacher | NULL | index | NULL | tid_index | 5 | NULL | 3 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
all:查询全部表中的数据,全表扫描
mysql> explain select * from teacher
-> ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | teacher | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid
-> and t.tcid = tc.tcid and c.cname = 'sql' ;
+----+-------------+-------+------------+--------+-------------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-------------------+-----------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
| 1 | SIMPLE | t | NULL | ref | uk_tcid,tid_index | tid_index | 5 | test.c.tid | 1 | 100.00 | Using where |
| 1 | SIMPLE | tc | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t.tcid | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+-------------------+-----------+---------+-------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
mysql> create table test_kl
-> (
-> name char(20) not null default ''
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> alter table test_kl add index index_name(name) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from test_kl where name =''
-> ;
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_kl | NULL | ref | index_name | index_name | 80 | const | 1 | 100.00 | Using index |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.06 sec)
# 字符集utf8mb4 char 20 就是 80,如果有null 则null 占一个字节,如果是varchar 则需要1-2个字节存储值的长度
mysql> alter table test_kl add column name1 char(20) ;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test_kl add index name_name1_index (name,name1) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from test_kl where name1 = '' ;
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test_kl | NULL | index | NULL | name_name1_index | 161 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_kl where name = ''
->
-> ;
+----+-------------+---------+------------+------+-----------------------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test_kl | NULL | ref | index_name,name_name1_index | index_name | 80 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+-----------------------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from myTest where b=3 and c=4;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | myTest | NULL | ALL | NULL | NULL | NULL | NULL | 32893 | 1.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from myTest where a=3 and c=4;
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | myTest | NULL | ref | a | a | 5 | const | 1 | 10.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
mysql> alter table course add index tid_index (tid) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from course c,teacher t where c.tid = t.tid and t.tname ='tw' ;
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | tid_index | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 1 | SIMPLE | c | NULL | ref | tid_index | tid_index | 5 | test.t.tid | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
mysql> explain select * from course c,teacher t where c.tid = t.tid
-> and t.tname = 'tz' ;
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | tid_index | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 1 | SIMPLE | c | NULL | ref | tid_index | tid_index | 5 | test.t.tid | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> create table test02
-> (
-> a1 char(3),
-> a2 char(3),
-> a3 char(3),
-> index idx_a1(a1),
-> index idx_a2(a2),
-> index idx_a3(a3)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> explain select * from test02 where a1 ='' order by a1 ;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test02 | NULL | ref | idx_a1 | idx_a1 | 13 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from test02 where a1 ='' order by a2 ;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | test02 | NULL | ref | idx_a1 | idx_a1 | 13 | const | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
select * from test02 where a1='x' and a1='y'
那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节,如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff,也就是 Unicode 中的基本多文种平面(BMP)。也就是说,任何不在基本多文本平面的 Unicode字符,都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码,常见于 ios 和 android 手机上 表情),和很多不常用的汉字,以及任何新增的 Unicode 字符等等。
- 我认为 合理表应该这样设计
CREATE TABLE `demo` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`uuid` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '业务id',
`create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
`update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',
`status` tinyint(2) NOT NULL DEFAULT 0 COMMENT '状态 0:正常 1:下线',
`logical_del` tinyint(2) NOT NULL DEFAULT 0 COMMENT '逻辑删除标识',
PRIMARY KEY `id`(`Id`) USING BTREE,
INDEX `uuid`(`uuid`) USING BTREE
) ENGINE = InnoDB CHARSET=utf8mb4 COMMENT = 'demo';
举栗子
CREATE TABLE `cnk_new_video` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`uuid` varchar(12) NOT NULL DEFAULT '' COMMENT '业务id',
`video_id` varchar(12) NOT NULL DEFAULT '' COMMENT '视频的id',
`title` varchar(100) NOT NULL DEFAULT '' COMMENT '视频标题 默认冗余视频id自带的标题,用户也可以自定义标题',
`pic` varchar(100) NOT NULL DEFAULT '' COMMENT '封面图',
`type` tinyint(2) NOT NULL DEFAULT 0 COMMENT '视频分类 默认0 1:美味春节.健康饮食 2:愉悦春节.身心健康 3:健康春节.为抗疫加油 4:安全春节.急救知识必备 ',
`color_type` tinyint(2) NOT NULL DEFAULT 0 COMMENT '标题颜色 0:深色 1:浅色',
`source_type` tinyint(2) NOT NULL DEFAULT 0 COMMENT '视频id来源 默认:0 1:急救视频 2:医院医生视频',
`hot` tinyint(2) NOT NULL DEFAULT 0 COMMENT '是否热门 0:非热门 1:热门',
`sort` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '排序 数值越大越靠前',
`hot_sort` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '热门视频排序 数值越大越靠前',
`create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
`update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',
`status` tinyint(2) NOT NULL DEFAULT 0 COMMENT '状态 0:正常 1:下线',
`logical_del` tinyint(2) NOT NULL DEFAULT 0 COMMENT '逻辑删除标识',
PRIMARY KEY `id`(`Id`) ,
UNIQUE `uuid`(`uuid`) ,
UNIQUE `video`(`source_type`,`video_id`)
) ENGINE = InnoDB CHARSET=utf8mb4 COLLATE =utf8_bin COMMENT = '新视听表';