普通索引
创建索引:
alter table 表名 add index 索引名(属性名);
alter table message add index title_index(title);
唯一索引
创建索引:
alter table 表名 add unique 索引名 (属性名);
alter table message add unique title_index (title);
联合索引
创建索引:
ALTER TABLE 表名 ADD INDEX 索引名 (属性名);
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
建表时,name长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。
如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。
建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:
1.usernname,city,age
2.usernname,city
3.usernname
为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:
SELECT * FROM mytable WHREE username="admin" AND city="郑州"
SELECT * FROM mytable WHREE username="admin"
而下面几个则不会用到:
SELECT * FROM mytable WHREE age=20 AND city="郑州"
SELECT * FROM mytable WHREE city="郑州"
主键
创建主键:
ALTER TABLE 表名 ADD PRIMARY KEY (属性名);
ALTER TABLE mytable ADD PRIMARY KEY (id);
删除:
如果有auto_increment,先删除自增长再删除主键
Alter table tb change id id int(10);//删除自增长
Alter table tb drop primary key;//删除主建
删除索引及查看表索引
删除索引:
drop index 索引名 on 表名;
drop index title_index on message;
查看创建的索引:
show index from 表名\G;
show index from message\G;
索引优化
在每条查询语句前加入explain
explain SELECT * FROM `message ` WHERE formid = 30000
+----+-------------+---------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | message | system | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+---------+--------+---------------+------+---------+------+------+-------+
id:
MySQL Query Optimizer 选定的执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序,id 值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。
table:
显示这一行的数据是关于哪张表的
type:
这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys:
显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key:
实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
key_len:
使用的索引的长度。在不损失精确性的情况下,长度越短越好
ref:
显示索引的哪一列被使用了,如果可能的话,是一个常数
rows:
MYSQL认为必须检查的用来返回请求数据的行数
Extra:
关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢