怎样优化MySql数据库的索引？

这篇文章给大家分享的是MySql数据库的索引优化的详细介绍，相信大部分人都还没学会这个技能，为了让大家学会，给大家总结了以下内容，话不多说，一起往下看吧。

索引是数据库优化最重要的手段，当我们遇到数据库性能问题的时候首先想到的就应该是索引优化，我们通过一个例子来看看索引对查询效率的影响究竟有多大：
我们还是沿用上一讲的数据库里面有一百万条数据:
根据id查询index_test表看一下它的查询计划：
查询速度很快，因为id上有主键索引，这里是索引查询
再来根据user字段查询一下：
花了0.57秒，
如果在user字段上加上索引效果会如何呢？
create index idx_item_user on index_test(user);
再来查询一下:
所用时间趋近于0，是不是很完美，这就是索引的魔力
索引如此神奇我们是不是该好好利用，可惜有时候明明建了索引在查询的时候却不通过索引查询，这又是什么原因呢？下面就来好好研究一下吧。
准备环境:
创建tb_seller表
create table `tb_seller` (
       `sellerid` varchar (100),
       `name` varchar (100),
       `nickname` varchar (50),
       `password` varchar (60),
       `status` varchar (1),
       `address` varchar (100),
       `createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('itcast','联想科技','联想','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('itheima','中粮集团','中粮','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
创建一个联合索引
CREATE INDEX idx_seller_name_sta_addr ON tb_seller(NAME,STATUS,address);
如何才能避免索引失效呢?
1） 查询的时候使用索引的所有字段进行精确匹配
当按照索引中所有的列进行精确匹配时索引可以被用到。
 
2） 最左前缀法则:
如果创建多个列的组合索引要遵守最左前缀法则，指的是查询从索引的最左边的列开始，不能跳过索引中的列,所以我们在创建组合索引时把where子句中出现最频繁的一列放在最左边。
本例中我们建立了组合索引(NAME,STATUS,ADDRESS),相当于创建了单列索引(NAME),组合索引(NAME,STATUS),组合索引(NAME,STATUS,ADDRESS)
所以在查询时想让索引生效where条件只能使用：
 
Name
Name,Status
Name,Status,Address
这样的组合
 
其它方式索引不会生效:
Status,
Status,Address
如果是这种方式:Name,Address,则只会用到Name的索引，不会用到Address的索引
打个比方就好比穿桥洞
Name是第一个桥洞，Status是第二个，Address是第三个
我们只能穿过第一个桥洞以后才能穿第二个第三个，不能绕过第一个直接穿第二个，第三个，也不能穿过第一个以后直接跳到第三个。
当然如果是这种方式:
Status,Name
Status,Name,Address
也是可以用到索引的
为什么呢？不是最左匹配吗？原来MySql的查询优化器会会判断究竟以什么顺序查询效率最高，然后根据情况调整查询的顺序，让我们也可以用上索引。
 
3） 范围查询右边的列，不能使用索引
比较一下这两个查询:
第一个查询的key_len长度为813三个索引都用到了，第二个查询key_len长度为410Name和status这两个条件走了索引，address没有走索引
4） 不要在索引列上进行运算操作,否则不会使用索引
5） 字符串不加单引号不会使用索引
比较下面两个查询
第一个查询key_len为410说明name和status都使用了索引，第二个查询key_len为403说明name使用了索引而status没有使用，原因是mysql的查询优化器会进行自动类型转换导致索引失效。
6） 查询的字段尽量都是建立索引的字段，不要用select *
第一个查询的extra为Using where 表示在查找使用索引的情况下，需要回表去查询所需的数据。
第二个查询的extra为Using where,Using index表示查找使用了索引，并且要查找的数据都在索引里能找到所以不需要回表查询。
7） 用or分开的条件,如果or前的条件中的列有索引，后面的列中没有索引，那么涉及的索引都不会被用到。
8） 以%开头的like模糊查询索引失效，如果%在结尾索引不会失效：
这里使用了索引
这里没有使用索引
如何解决这个问题呢？可以通过覆盖索引来解决
可以让我们的查询列都在索引列里
查询的列sellerid,name都是索引列，所以即使使用了模糊查询也可以使用索引
9） 如果mysql评估使用索引比全表扫描更慢则不会使用索引
先在address上创建索引
create index idx_item_address on tb_seller(address);
    Address上建了索引为什么这里没用索引呢？因为这张表中总共十条记录有九条都是北京市，所以用索引去查不如全表扫描快。
再来看一个查询:
 
这里用到了索引。
10) in走索引，not in不走索引

关于MySql数据库的索引优化就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果喜欢这篇文章，不如把它分享出去让更多的人看到。