mysql-索引篇-设计原则 - 《博客专栏》

1.索引的创建(create)
">
2.索引的修改(alter)
3.索引的查看(show)
4.索引的删除（drop）
5.索引设计基本原则

这里上一张不同存储引擎对数据结构支持情况表

索引	InnoDB	MyISAM	Memory
Btree	支持	支持	支持
Hash	不支持	不支持	支持
R-tree	不支持	支持	不支持
FULL-TEXT	5.60版本后支持	支持	不支持

show VARIABLES like '%version%'; //我的mysql version配置为5.5.40，所以不支持FULL-TEXT

sql脚本film表

CREATE TABLE `film` (
  `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `description` text,
  `release_year` year(4) DEFAULT NULL,
  `language_id` tinyint(3) unsigned NOT NULL,
  `original_language_id` tinyint(3) unsigned DEFAULT NULL,
  `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
  `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
  `length` smallint(5) unsigned DEFAULT NULL,
  `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`film_id`),
  KEY `idx_title` (`title`),
  KEY `idx_fk_language_id` (`language_id`),
  KEY `idx_fk_original_language_id` (`original_language_id`),
  CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4;

city表

CREATE TABLE `city` (
  `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`city_id`),
  KEY `idx_fk_country_id` (`country_id`),
  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8mb4;

1.索引的创建(create)

create index index_update_time ON city(last_update);

在表的对象信息中可以看到下面新增了一条index_update_time索引

可以通过show INDEX from city;

show INDEX from city; //查看city表中的所有索引命令

2.索引的修改(alter)

ALTER TABLE film ADD FULLTEXT film(description);

2.1在表中增加一个唯一索引

ALTER TABLE staff ADD UNIQUE index_email (email);  //staff表中增加email的索引

— PRIMARY和unique 索引的区别 都可以表示唯一，但是primary表示唯一的且非空的，unique表示是唯一的但是可以为null（可以有多个）的

NOTE
:对于mysql 5.6版本之前的innodb引擎是不支持FULLTEXT索引的，报错The used table type doesn’t support FULLTEXT indexes
MyIsam是支持全文索引的

总结

3.索引的查看(show)

show INDEX from staff; //查看staff表建立了那些索引

4.索引的删除（drop）

DROP INDEX index_update_time ON city;  //删除city表索引

5.索引设计基本原则

在搜索的索引列，不一定的要选择的列，讲人话就是：最适合建立索引的列是在where子句中的列，或是连接子句的列，而不是select关键字后的选择展示的列
使用唯一索引，要考虑列的值的分布，索引的列的基数越大，索引效果越好，比如存放出生日期的列具有不同的值，很容易区分各行，而性别的列只有”M”和”F”不容易区分，不管选择哪个，都是搜索出大约一半的行
使用短索引，较短的值比较起来更快，占用的内存页更小
利用最左前缀原则，比如
- create index idx_name_email_status on tb_seller(name,email,status); 等价于
  - 对name 创建索引
  - 对name，email索引
  - 对name，email,status创建索引

NOTE:
对于最左前缀索引（包含最左，不能跳跃列）是否生效做一个说明，
通俗的理解可以理解为像爬楼梯一样：必须从第一层楼开始爬，不能跨楼层爬，
比如不能从1楼直接飞到3楼，对应上面的例子 where name = ‘zhangsan’ and status = ‘1’ ，status是不走索引的，只有name走索引了

总结：
最后拿个例子来说明是否走索引
新建一张consumer表

CREATE TABLE `consumer` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `USERNAME` varchar(50) NOT NULL COMMENT '用户名',
  `FULLNAME` varchar(50) DEFAULT '' COMMENT '真实姓名',
  `ID_NUMBER` varchar(50) DEFAULT NULL COMMENT '身份证号',
  `USER_NO` varchar(50) DEFAULT NULL COMMENT '用户编码,生成唯一,用户在存管系统标识',
  `MOBILE` varchar(50) DEFAULT NULL COMMENT '平台预留手机号',
  `USER_TYPE` varchar(50) DEFAULT NULL COMMENT '用户类型,个人or企业，预留',
  `ROLE` varchar(50) DEFAULT NULL COMMENT '用户角色.B借款人or I投资人',
  `AUTH_LIST` varchar(50) DEFAULT NULL COMMENT '存管授权列表',
  `IS_BIND_CARD` tinyint(1) DEFAULT NULL COMMENT '是否已绑定银行卡',
  `LOAN_AMOUNT` decimal(10,0) DEFAULT NULL,
  `STATUS` tinyint(1) DEFAULT NULL COMMENT '可用状态',
  `REQUEST_NO` varchar(50) DEFAULT NULL COMMENT '请求流水号',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='c端用户信息表';

插入一条sql

INSERT INTO `consumer` (
    `ID`,
    `USERNAME`,
    `FULLNAME`,
    `ID_NUMBER`,
    `USER_NO`,
    `MOBILE`,
    `USER_TYPE`,
    `ROLE`,
    `AUTH_LIST`,
    `IS_BIND_CARD`,
    `LOAN_AMOUNT`,
    `STATUS`,
    `REQUEST_NO`
)
VALUES
    (
        '1',
        'zhangsan',
        'zhangsan',
        '360752199812033213',
        'ID0001',
        '18942123126',
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        '1',
        NULL
    );

创建一个组合索引

CREATE INDEX idx_username_MOBILE_status ON consumer(username,MOBILE,status);

查看第1条sql和执行计划

EXPLAIN SELECT * from consumer WHERE username = 'zhangsan';

查看第2条sql和执行计划

EXPLAIN SELECT * from consumer WHERE username = 'zhangsan' and mobile = '18942123126';

查看第3条sql和执行计划

EXPLAIN SELECT * from consumer WHERE username = 'zhangsan' and mobile = '18942123126' and STATUS = '1';

可以看到type属性都是为ref，说明都是走了索引的，符合我们的最左前缀原则，其实我们还要关注一个字段就是key_len的大小

那么对于WHERE username = ‘zhangsan’ and STATUS = ‘1’(跳过了中间的mobile),这种是不是走索引呢（还记得爬楼的例子么,不能跳跃的例子）

SELECT * from consumer WHERE username = 'zhangsan' and STATUS = '1';

我们看一下执行计划：

EXPLAIN SELECT * from consumer WHERE username = 'zhangsan' and STATUS = '1';

大家仔细看type=ref,这是走了索引的，但是只是对于第一个WHERE username = ‘zhangsan’走了索引，而后面的and STATUS = ‘1’并没有走索引，那我是怎么知道的呢，因为这里面的key_len和前面的第1条只有一个查询的WHERE username = ‘zhangsan’的长度是一样的都是152，

其实也就是说最左前缀原则要满足（包含最左并且列不能跳跃），你对最左前缀原则是否又加深了一点呢