Mysql索引的优化和使用原则

mysql高级特性：
一、索引：
分类：
1、主键索引：设置为主键的列会创建主键索引，主键唯一非空
如果你给一个数据表字段设置为主键，呢么他会自动创建一个主键索引，在数据库中，索引分为主键索引（聚簇索引）和非主键索引（二级索引），
他们之间的区别是：非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据。
任何二级索引的调用，都是先查询到叶子节点的主键值，然后再根据主键索引查询数据，这个过程也叫回表。
2、单列索引：即索引中只包含一个列，一张表可以有多个单列索引。
3、唯一索引：索引列的值必须唯一，允许有空值。
4、复合索引：索引中包含多个列

二、创建索引
  可以在创建表的同时创建索引，也可以在已有表的基础上去创建索引。
  create index idx_city_name on city(city_name)
    
    查看city表中的索引
    
    show index from city
    show index from tb_name
    
 删除city表上的索引idx_city_name
 drop index idx_city_name on city

    使用alter命令添加索引：
        1). alter table tb_name add primary key(column_list);
    该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
    2). alter table tb_name add unique index_name(column_list);
    这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
    3). alter table tb_name add index index_name(column_list);
    添加普通索引， 索引值可以出现多次。
    4). alter table tb_name add fulltext index_name(column_list);
    该语句指定了索引为FULLTEXT， 用于全文索引

三、索引的设计原则
  1、对经常需要进行查询并且数据量很大的表创建索引
    
    2、在选择索引字段的时候一般选择作为查询条件的字段，如果作为查询条件的字段比较多则选择最常用的一个
    
    3、选择区分度高的字段作为索引，区分度越高索引的效率越高。
    
    4、虽然索引可以极大的提高查询效率，但是也不要太多。在做增删改查操作的时候需要对索引进行维护，如果索引
    太多，维护的成本相应就会变大。而且索引过多，选择索引也会消耗一些资源。
    
    5、使用短索引，因为索引也是存储在磁盘上的，磁盘的io效率对索引查找的性能也有较大的影响，索引越短，在一个存储块能存储的索引值就越多，io效率越高。
    
    6、创建组合索引，n个列创建的组合索引相当于创建了n个索引
    
四、单列索引和组合索引的区别
    比如一个sql语句：
    SELECT * FROM city WHERE city_name = '北京' AND country_id = 1
    
    如果在city_name上创建单列索引
    
    则执行该sql语句的时候会使用索引先查询出city_name 为北京的记录放到中间表中，再在结果中过滤出country_id 为1的数据；
    也就是说，数据库会优先选择速度最快的方式为我们查询sql语句，优先使用索引查询。
    
    如果在city_name和country_id上都创建单列索引
    
    则执行该sql语句时只会使用city_name上的索引，country_id的索引不会用上，因为这里涉及到了mysql优化器的优化策略，当多个条件查询的时候，
    优化器会评估用哪个条件的索引效率最高，会选择效率最高的索引来使用，所以这两个索引都有可能被使用，也可能会同时使用。
    
    如果在city_name和country_id上创建组合索引，且顺序为city_name在前则测试如下:

        1)SELECT * FROM city WHERE city_name = '北京'

        会使用该索引

        2)SELECT * FROM city WHERE country_id=1

        不会使用该索引

        3)SELECT * FROM city WHERE city_name = '北京' AND country_id = 1

        会使用该索引

        4)SELECT * FROM city WHERE country_id = 1 and city_name = '北京'

        会使用该索引

        为什么会产生这样的结果呢？

        我们可以把联合索引看成是电话簿

        人名由姓和名组成，联合索引首先按姓氏进行排序，如果姓氏相同则按名字排序，如果您知道姓就可以通过姓快速查找改姓的人，如果您知道姓和名就可以快速定位到人,如果您只知道名则无法利用电话簿的这种排序方式提高查找速度

        所以在创建联合索引时应该仔细考虑索引列的顺序，对索引列的所有列执行搜索或者仅对索引列的前几列进行搜索时联合索引非常有用，仅对后面任意列进行搜索时联合索引没有用处。

        最左前缀原则：

        也就是以最左边为起点任何连续的索引都能匹配上，所以在创建联合索引的时候where查询语句使用最频繁的列放在最左边扩展性比较好，比如上例中经常需要根据city_name来查询在创建联合索引的时候就可以把city_name放在最左边。

        总结：多个单列索引在多条件查询时优化器会选择最优索引策略，有可能是其中一个也有可能都用上，但是多个单列索引每个索引都会占用磁盘空间，也会降低搜索效率，所以在只有多条件联合查询时最好使用联合索引。
    
####################################################################################################################################################
索引的优化：

所使用的表结构和数据：
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、查询的时候使用索引的所有字段进行精确匹配：
 explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
  explain select * from tb_seller where  status='1' and address='北京市' and name='小米科技'   ;
 当按照索引中所有的列进行精确匹配的时候索引可以被用到,不论顺序
 
 2、最左前缀原则：
 如果创建多个列的组合索引要遵守最左前缀原则，指的是查询从索引的最左边的列开始，不能跳过索引中的列，所以我们在创建组合索引的时候要把where子句中出现最频繁的一列放在最左边。
 
 本例中我们建立了组合索引(NAME,STATUS,address)，相当于创建了(NAME)，(NAME,STATUS)，(NAME,STATUS,address) 三个索引。所以在创建联合索引时应该仔细考虑索引列的顺序，对索引列的所有列执行搜索或者仅对索引列的前几列进行搜索时联合索引非常有用，仅对后面任意列进行搜索时联合索引没有用处。
 
 
 所以在查询时想让索引生效只能根据这三个索引的顺序查找：
 
 1）(NAME)
   explain select * from tb_seller where name='小米科技';
    
 2)(NAME,STATUS)
  explain select * from tb_seller where name='小米科技' and status='1' ;
    explain select * from tb_seller where status='1'and name='小米科技' ;

 3)(NAME,STATUS,address) -- 只要where条件全部包含这几列就行
  explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
  explain select * from tb_seller where  status='1' and address='北京市' and name='小米科技';
 
其他方式均不会生效：

例如：

explain select * from tb_seller where status='1'
 
如果是：name，address ，就是我中间不按照顺序来，我又不使用索引的全部字段，呢么mysql使用索引只会到有序的呢一个索引

explain select * from tb_seller where name='小米科技'and address='北京市'
只会使用name的索引，不会使用address的索引。

 打个比方就好比穿桥洞

Name是第一个桥洞，Status是第二个，Address是第三个

我们只能穿过第一个桥洞以后才能穿第二个第三个，不能绕过第一个直接穿第二个，第三个，也不能穿过第一个以后直接跳到第三个。
 
 
为什么呢？不是最左匹配吗？原来MySql的查询优化器会会判断究竟以什么顺序查询效率最高，然后根据情况调整查询的顺序，让我们也可以用上索引。
 比如：
 Status,Name方式；
 explain select * from tb_seller where status='1'and name='小米科技';
 
 3、范围查询右边的列，不能使用索引
   explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'
     可以用到全部索引；
     explain select * from tb_seller where name='小米科技' and status>'1' and address='北京市'
   只能用到name和status索引，address的索引用不到
    
    4、不要再索引列上进行运算操作，否则索引会失效；
     explain select * from tb_seller where substring(name,3,2)='科技'
        
    5、字符串不加单引号不会使用索引
  explain select * from tb_seller where name='小米科技' and status='1' ;
  explain select * from tb_seller where name='小米科技' and status=1 ;
 
    第一个都使用了索引，而第二个只有name使用了索引，因为mysql的查询优化器会进行自动类型转换导致索引失效
    
    6、查询的字段尽量都是建立索引的字段，不要用select *
     explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
   explain select name,status,address from tb_seller where name='小米科技' and status='1' and address='北京市';

    第一个查询的extra为Using where 表示在查找使用索引的情况下，需要回表去查询所需的数据。
  第二个查询的extra为Using where,Using index表示查找使用了索引，并且要查找的数据都在索引里能找到所以不需要回表查询。
    
  7、用or分开的条件，都不会使用索引
    
    8、以% 开头的like模糊查询索引会失效，如果% 在结尾则不会失效。
    explain select * from tb_seller where name like '小米科技%'
    使用了索引
    
    explain select * from tb_seller where name like '%小米科技'
  不会使用索引
    
    如何解决这个问题呢？可以通过覆盖索引来解决
  可以让我们的查询列都在索引列里
    
   查询的列sellerid,name都是索引列，所以即使使用了模糊查询也可以使用索引
 
 
 9、如果mysql评估使用索引比全表扫描更慢则不会使用索引
 
 10、in走索引，not in不走索引