常用命令
索引
索引是一种将数据库中单列或多列的值进行排序的数据结构,使用它是为了提高数据库表访问速度。
索引类型
1 普通索引 :不应用任何限制条件的索引,该索引可以在任何数据类型中创建。
2 唯一性索引:使用UNIQUE参数可以设置唯一索引,索引的值必须唯一。主键是一种特殊的唯一索引。可以约束字段的唯一性。
**** 实践:添加值相同的多条记录会失败(ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘i_id’)。
3 全文索引:使用 FULLTEXT 参数可以设置索引为全文索引。全文索引只能创建在 CHAR, VARCHAR, TEXT类型的字段上。
4 单列索引:只对应一个字段的索引。上述三种为单列索引。
5 多列索引:在表的多个字段上创建的索引。=== 要想用该索引,用户必须使用这些字段中的第一个字段。===
6 空间索引:使用 SPATIAL 参数可以设置索引为空间索引。只能建立在空间数据类型上。 Mysql中只有MyISAM 存储引擎只会空间检索,且该索引字段不能为空。
创建索引
create table score {
id int(11) NOT NULL auto_increment,
name
varchar(50) not null,
math
int(5) NOT NULL,
PRIMARY KEY(id
).
UNIQUE INDEX index_name(name
(10) ASC)
}ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE INDEX stuo_info ON student(sid);
alter table student add UNIQUE INDEX stu_info(sid);
DROP INDEX index_name ON table_name;
索引失效场景
锁
事务
Mysql 优化
drop table if exists student;
create table `student` (
`id` varchar(20) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(5),
`birthday` datetime DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
drop table if exists teacher;
create table `teacher` (
`id` varchar(20) NOT NULL,
`name` varchar(255) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
drop table if exists course;
create table `course` (
`id` varchar(20) NOT NULL,
`name` varchar(255) NOT NULL,
`tid` varchar(20) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table `score` (
`id` varchar(20) NOT NULL,
`sid` varchar(255) NOT NULL,
`cid` varchar(255) NOT NULL,
`score` decimal(5,0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
没加索引
mysql> explain select * from student where id=‘000099’;
±—±------------±--------±-----±--------------±-----±--------±-----±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±--------±-----±--------------±-----±--------±-----±-----±------------+
| 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL| 839 | Using where |
±—±------------±--------±-----±--------------±-----±--------±-----±-----±------------+
创建索引
create unique index i_id on student(id);
mysql> explain select 8 from student where id=‘0000000988’;
±—±------------±--------±------±--------------±-----±--------±------±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±--------±------±--------------±-----±--------±------±-----±------------+
| 1 | SIMPLE | student | const | i_id | i_id | 62 | const | 1 | Using index |
±—±------------±--------±------±--------------±-----±--------±------±-----±------------+
=== 隐式类型转换,导致索引失效====
mysql> explain select * from student where id=0000000988;
±—±------------±--------±-----±--------------±-----±--------±-----±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±--------±-----±--------------±-----±--------±-----±-----±------------+
| 1 | SIMPLE | student | ALL | i_id | NULL | NULL | NULL | 1005 | Using where |
±—±------------±--------±-----±--------------±-----±--------±-----±-----±------------+
mysql> explain select (select 1 from course where id = ‘000001’) from (select * from teacher where id = ‘0000001’) der;
±—±------------±-----------±-------±--------------±-----±--------±-----±-----±--------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-----------±-------±--------------±-----±--------±-----±-----±--------------------+
| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 0 | const row not found |
| 3 | DERIVED | teacher | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 2 | SUBQUERY | course | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
±—±------------±-----------±-------±--------------±-----±--------±-----±-----±--------------------+
mysql> explain select name from course where id = ‘000001’ union all select name from teacher where id = ‘0000001’ ;
±—±-------------±-----------±-----±--------------±-----±--------±-----±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±-------------±-----------±-----±--------------±-----±--------±-----±-----±------------+
| 1 | PRIMARY | course | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 2 | UNION | teacher | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
±—±-------------±-----------±-----±--------------±-----±--------±-----±-----±------------+
select_type :
simple:简单查询。查询不包含子查询和union
primary:复杂查询中最外层的 select
subquery:包含在 select 中的子查询(不在 from 子句中)
derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表
union:在 union 中的第二个和随后的 select
union result:从 union 临时表检索结果的 select