5.9 数据库索引及优化

分类支持情况

索引分类

　　普通索引  :

　　　　-index ：加速查找

　　唯一索引

　　　　主键索引：primary key ：加速查找+约束（不为空且唯一）

　　　　唯一索引：unique：加速查找+约束 （唯一）

　　联合索引

　　　　-primary key(id,name): 联合主键索引

　　　　-unique(id,name): 联合唯一索引

　　　　-index(id,name): 联合普通索引

　　全文索引 fulltext 

　　　　用于搜索很长一篇文章的时候，效果最好。

　　空间索引 spatial 

　　　　了解就好，几乎不用

类型分类

hash类型索引：

　　　　查询单条快，范围查询慢

btree类型索引：

　　　　b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

引擎支持情况

InnoDB

　　　　支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

MyISAM

　　　　不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

Memory

　　　　不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

NDB

　　　　支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；

Archive

　　　　不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

索引使用 

创建/删除 索引 

#方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                );


#方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
                             

#删除索引：DROP INDEX 索引名 ON 表名字; 

示例

1.创建索引
   
-在创建表时就创建（需要注意的几点）
    create table s1(
    id int ,　　# 可以在这加primary key
    # id int index 　　# 不可以这样加索引，因为index只是索引，没有约束一说，
    # 不能像主键，还有唯一约束一样，在定义字段的时候加索引
    name char(20),
    age int,
    email varchar(30)
   
　  #primary key(id) #也可以在这加
    index(id) #可以这样加
    );
    

-在创建表后在创建
    create index name on s1(name);　　 # 添加普通索引
    create unique age on s1(age);　　# 添加唯一索引
    alter table s1 add primary key(id); 　　# 添加住建索引，也就是给id字段增加一个主键约束
    create index name on s1(id,name);　　 # 添加普通联合索引


2.删除索引
    drop index id on s1;
    drop index name on s1; 　　# 删除普通索引
    drop index age on s1;　　 # 删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
    alter table s1 drop primary key; 　　# 删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

正确使用索引的方式

覆盖索引

select * from s1 where id=123; 　　# * 需要将其他的所有没有索引的数据拉取出来，都要一个一个查
select id from s1 where id=123; 　　# id 是有索引的，替换 * 之后直接用索引即可查出，效率很高

联合索引

select * from s1 where id=123 and name="yangtuo";  # 耗时 0.83s
create index idn on s1(id,name)
select * from s1 where id=123 and name="yangtuo";  # 耗时 0.00s

索引合并

#索引合并：把多个单列索引合并使用

#分析：
组合索引能做到的事情，我们都可以用索引合并去解决，比如
create index ne on s1(name,email);#组合索引
我们完全可以单独为name和email创建索引

组合索引可以命中：
select * from s1 where name='egon' ;
select * from s1 where name='egon' and email='adf';

索引合并可以命中：
select * from s1 where name='egon' ;
select * from s1 where email='adf';
select * from s1 where name='egon' and email='adf';

乍一看好像索引合并更好了：可以命中更多的情况，但其实要分情况去看，如果是name='egon' and email='adf',
那么组合索引的效率要高于索引合并，如果是单条件查，那么还是用索引合并比较合理

索引使用规则

#1.最左前缀匹配原则，非常重要的原则，
create index ix_name_email on s1(name,email,)
- 最左前缀匹配：必须按照从左到右的顺序匹配
select * from s1 where name='egon'; #可以
select * from s1 where name='egon' and email='asdf'; #可以
select * from s1 where email='alex@oldboy.com'; #不可以
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，
d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

#2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器
会帮你优化成索引可以识别的形式

#3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，
表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、
性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，
这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

#4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’
就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，
但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。
所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

 索引无法命中的情况

- like '%xx' 
    select * from tb1 where email like '%cn';
    
    
- 使用函数不走索引
    select * from tb1 where reverse(email) = 'wupeiqi';
    
    
- or 不走索引
    select * from tb1 where nid = 1 or name = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'
            
            
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where email = 999;
    

- !=  不等于不走索引
    select * from tb1 where email != 'alex'
    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123

- >  大于号不走索引
    select * from tb1 where email > 'alex'    
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
        
        
#排序条件为索引，则select字段必须也是索引字段，否则无法命中
- order by
    select name from s1 order by email desc;
    当根据索引排序时候，select查询的字段如果不是索引，则不走索引
    select email from s1 order by email desc;
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
 


- 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引


- count(1)或count(列)代替count(*)在mysql中没有差别了



- create index xxxx  on tb(title(19)) # text类型，必须制定长度

索引使用原则 

- 避免使用select *

- count(1)或count(列) 代替 count(*)

- 创建表时尽量时 char 代替 varchar

- 表的字段顺序固定长度的字段优先

- 组合索引代替多个单列索引（经常使用多个条件查询时）

- 尽量使用短索引

- 使用连接（JOIN）来代替子查询(Sub-Queries)

- 连表时注意条件类型需一致

- 索引散列值（重复少）不适合建索引，　　例：性别

慢查询优化的基本步骤

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/shijieli/p/10344655.html