MySQL索引笔记

1. 索引概念

 索引是单独的，存储于磁盘上的一种数据结构。

2. 设计原则

• 索引并非越多越好。索引虽然查询快，但是会影响插入，修改，删除语句性能.

• 避免对经常更新的表做过多的索引。

• 对于经常查询的字段应该创建索引。

• 数据量小的表的不要使用索引。

• 在不同值较多的列上建立索引。不同值太少的列建立索引作用不大，比如性列就两个值，男和女。

• 频繁排序或分组的列建索引。

3. 索引语法

• 建表时建索引:

CREATE TABLE tablename (..., INDEX(column_name)); #普通索引
CREATE TABLE tablename (..., UNIQUE INDEX(column_name)); #唯一索引
CREATE TABLE tablename (...,  INDEX(column_name1, column_name2)); #组合索引

• 已存在的表上建索引：

ALTER TABLE tablename ADD INDEX index_name(column_name1, [column_name2, ...]);

• create index语法建索引:

CREATE INDEX index_name ON table_name(column_name1, [column_name2, ...]);

• 删除索引:

ALTER TABLE table_name DROP INDEX index_name;
DROP INDEX index_name ON table_name;

• 查看索引信息:

#查看表结构
mysql> SHOW CREATE TABLE table_name\G;
#查看索引信息
mysql> SHOW INDEX FROM table_name\G;

关于组合索引：
 组合所以遵循最左前缀原则，即利用索引中最左边的列来匹配行。
 假如索引的的字段组合为（id,name, age):
 where中只有id和其他非索引字段：只会使用id索引
 where中只有id，age和其他非索引字段：只会使用id索引
 where中只有id，name和其他非索引字段：会使用id,name索引
 where中只有name和其他非索引字段：不使用用索引
 where中只有name，age和其他非索引字段：不使用索引

关于ley_len计算：

通常情况下， key_len=字段字符数*字符集每个字符所占字节数

• 字段类型为int, 则key_len=4

• 字段类型为bigint, 则key_len=8

• 变长的字段如varchar，会使key_len+2

• default NULL，会使 key_len+1

• UTF-8字符集里，一个字符算3个长度， key_len=字段字符数*字符集每个字符所占字节数

  eg:

CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `multi_index` (`bookid`,`bookname`,`authors`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# key_len为711， 711=4+255*3+2
# 4是因为bookid为int
# 255*3 是因为authors列是varchar(255),utf8字符集每个字符3字节
# 2是因为varchar边长字段，所以加2
mysql> explain SELECT *FROM `book` WHERE 1 = 1 and bookname='2' and bookid='1'\G;

  可以同构ken_len 的值判断SQL的索引检索效率。