索引

索引

索引相关语句

创建索引

1. CREATE INDEX

CREATE INDEX indexName ON tableName (columnName(length));

2. ALTER

ALTER TABLE tableName ADD INDEX indexName(columnName);

3. 建表时创建

CREATE TABLE tableName(  
id INT NOT NULL,     columnName  columnType,  INDEX [indexName] (columnName(length))  );

查看索引

SHOW INDEX FROM ttablename;

删除索引

ALTER TABLE t_user_action_log DROP INDEX index_ip_addr;

key_len的计算

latin1编码的，一个字符占用一个字节，gbk编码的，一个字符占用两个字节，utf8编码的，一个字符占用三个字节。

适用原则

• 频繁进行写操作的列加索引慎重
• 索引越多占用磁盘空间越大
• 考虑维度（查询结果超过全表30%时会跳过索引，进行全盘扫描）
• 避免为输出列添加索引
• 为字符串前缀加索引
• 为字段长度少的列添加索引（索引所占的空间更小，可以减少I/O活动，同时比较索引的速度也更快）
• 复合索引的左侧索引
• 索引加锁
• 覆盖索引（索引包含满足查询的所有数据）
• 聚簇索引（保证关键字的值相近的元组存储的物理位置也相同，且一个表只能有一个聚簇索引，如果使用聚簇索引，最好使用AUTO_INCREMENT列作为主键，字符串类型不建议使用聚簇索引）
• 选择合适的索引类型（B树索引、Hash索引）

explain分析查询语句

select_type

• SIMPLE： 简单SELECT,不使用UNION或子查询等。
• PRIMARY： 查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY。
• UNION： UNION中的第二个或后面的SELECT语句。
• DEPENDENT UNION： UNION中的第二个或后面的SELECT语句，取决于外面的查询。
• UNION RESULT： UNION的结果。
• SUBQUERY： 子查询中的第一个SELECT。
• DEPENDENT SUBQUERY： 子查询中的第一个SELECT，取决于外面的查询。
• DERIVED： 派生表的SELECT, FROM子句的子查询。
• UNCACHEABLE SUBQUERY： 一个子查询的结果不能被缓存，必须重新评估外链接的第一行。

type

type表示MySQL在表中找到所需行的方式。

• ALL： Full Table Scan，MySQL将遍历全表以找到匹配的行。
• index: Full Index Scan，index与ALL区别为index类型只遍历索引树。
• range: 只检索给定范围的行，使用一个索引来选择行。
• ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
• eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件。
• const: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
• NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

key

如果没有选择索引，null。选择索引的话显示的是数据库决定的索引

possible_keys

possible_keys指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上如果存在索引则该索引将被列出，但不一定被查询使用。

ref

ref表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

rows

rows表示MySQL根据表统计信息，以及索引选用的情况，找到所需记录需要读取的行数。这个行数是估算的值，实际行数可能不同。

数值类型代替字符串

MySQL对数值类型的处理速度要远远快于字符串，而且数值类型往往更加节省空间。
例如对于“Male”和“Female”可以用“0”和“1”进行代替。
查询的时候做成配置而不是case when也更加灵活

考虑使用ENUM类型

如果你的数据列的取值是确定有限的，可以使用ENUM类型代替字符串。因为MySQL会把这些值表示为一系列对应的数字，这样处理的速度会提高很多。

CREATE TABLE shirts (    name VARCHAR(40),    size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),  ('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';