Mysql 高性能索引

当数据量急剧增加时，查询会变慢，这时候你可能需要索引。

创建的索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序十分重要，因为mysql只能高效的使用最左前缀列。创建一个包含两个列的索引和两个包含一个列的索引是大不相同的。
索引的类型：
1.B-Tree索引：

默认情况下msyql创建的是B-Tree索引，并且也是运用得最多的索引方式。

create table test(
last_name varchar(50),
first_name varchar(50),
dob date,
gender enum("m", "f")
key(last_name, first_name, date)
);

创建复合索引的时候顺序非常重要，查询能不能命中索引取决于查询条件的写法:一般遵从一下标准:
1.全值匹配	查询 last_name="df" and first_name="af" and dod = "2018"
2.匹配最左前缀  只查询last_name ="cui"的列
3.匹配列前缀  查询last_name = "" and first_name like "J"
4.匹配范围值 查询 last_name > "ss"
5.精确匹配某一列并范围匹配另一列  last_name="asf" and first_name > "c"

动态创建索引的方式:

create table USER_DEMO
(
 ID     int not null auto_increment comment '主键',
 LOGIN_NAME   varchar(100) not null comment '登录名',
 PASSWORD    varchar(100) not null comment '密码',
 CITY     varchar(30) not null comment '城市',
 AGE     int not null comment '年龄',
 SEX     int not null comment '性别(0:女 1：男)',
 primary key (ID)
);

ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE);

建表时，LOGIN_NAME长度为100，这里用16，是因为一般情况下名字的长度不会超过16，这样会加快索
引查询速度，还会减少索引文件的大小，提高INSERT，UPDATE的更新速度。
	 
如果分别给LOGIN_NAME,CITY,AGE建立单列索引，让该表有3个单列索引，查询时和组合索引的效率是
大不一样的，甚至远远低于我们的组合索引。虽然此时有三个索引，但mysql只能用到其中的那个它认为
似乎是最有效率的单列索引，另外两个是用不到的，也就是说还是一个全表扫描的过程。
建立这样的组合索引，就相当于分别建立如下三种组合索引：

LOGIN_NAME,CITY,AGE
LOGIN_NAME,CITY
LOGIN_NAME

如果索引列长度过长,这种列索引时将会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行索引，
前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值就可以创建)。像这里的
LOGIN_NAME只截取前16位作为索引列那样。

EXPLAIN可以帮助开发人员分析SQL问题,explain显示了mysql如何使用索引来处理select语句以及连接表,
可以帮助选择更好的索引和写出更优化的查询语句。

explain select * from user_demo;

尽量避免不使用索引的查询:

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引,因为所有索引列参与了计算
 
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引,因为使用了函数运算,原理与上面相同
 
SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引
 
SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引
 
-- 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因
 
-- 字符串与数字比较不使用索引;
CREATE TABLE `a` (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引
 
select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字
 
-- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

使用索引时，有一些技巧：

1.索引不会包含有NULL的列

只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。

2.使用短索引

对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.索引列排序

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。

4.like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%'不会使用索引，而like ‘aaa%'可以使用索引。

5.不要在列上进行运算

6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

7.索引要建立在经常进行select操作的字段上。

这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

8.索引要建立在值比较唯一的字段上。

9.对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。

10.在where和join中出现的列需要建立索引。

11.where的查询条件里有不等号(where column != …),mysql将无法使用索引。

12.如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。

13.在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。

2.Hash索引：
假设你有一张表用来存放大量的url，如果使用B-Tree来存储URL，存储的内容会很大，因为本身url可能就很长。列入一下查询：

create table url_table(
	id BIGINT unsigned not null auto_increment,
	url varchar(255) not null,
	url_crc BIGINT unsigned not null default 0,
	primary key(id)
);
mysql>select * from url_table where url = "http://www.mysql.com";

如果删除原来的索引，而新增一个被索引的url_crc列，使用CRC32做哈希，如下：

mysql>select * from url_table where url_crc = CRC32("http://www.mysql.com") and url="http://www.mysql.com";

如果想偷下懒可以在mysql中创建一个触发器：

delimiter $
create trigger url_table_crc_ins before insert on url_table for each row 
begin set NEW.url_crc=crc32(NEW.url);
end
$
create trigger url_table_crc_upd before update on url_table for each row 
begin set NEW.url_crc = crc32(NEW.url);
end $

这样你插入数据是就可以这样：

mysql>insert into url_table(url) values("http://www.mysql.com");
得到如下的数据行:
mysql>1	http://www.mysql.com	1560514994

如果数据量非常大的时候可能会出现大量的hash冲突，这时你可以自己实现一个简单的64位hash函数。不过也可以通过md5函数返回值取一部分数据来作为自定义的hash函数。取md5的前16位 转换为整数进行存储。

小知识：CONV(N,from_base,to_base)
N是要转换的数据，from_base是原进制，to_base是目标进制。

select conv(right(md5("http:///www.baidu.com"), 16), 16, 10) as hash_v;

创建新的触发器：

delimiter &
create trigger url_table_crc_ins before insert on url_table for each row
begin set NEW.url_crc = conv(right(md5(NEW.URL), 16), 16, 10);
end 
&
create trigger url_table_crc_upd before update on url_table for each row
begin set NEW.url_crc = conv(right(md5(NEW.URL), 16), 16, 10);
end &
delimiter ;

使用hash索引的缺点：
（1）hash 索引仅仅能满足=，<=>，IN，IS NULL或者IS NOT NULL查询，不能使用范围查询。
由于 hash 索引比较的是进行 hash 运算之后的 hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 hash 算法处理之后的 hash 值的大小关系，并不能保证和hash运算前完全一样。

（2）hash 索引无法被用来避免数据的排序操作。