Mysql慢查询的优化案例（二）

本节是对数据库索引的优化。

一、创建索引

1.CREATE INDEX

在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

1、ALTER TABLE

        ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

        ALTER TABLE table_name ADD INDEX index_name (column_list)

        ALTER TABLE table_name ADD UNIQUE (column_list)

        ALTER TABLE table_name ADD PRIMARY KEY (column_list)

        说明：其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

2、CREATE INDEX

        CREATE INDEX可对表增加普通索引或UNIQUE索引。

        CREATE INDEX index_name ON table_name (column_list)

        CREATE UNIQUE INDEX index_name ON table_name (column_list)

        说明：table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

2.索引类型

        创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。

        PRIMARY KEY索引和UNIQUE索引非常类似。

        事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。

        下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

ALTER TABLE students ADD PRIMARY KEY (sid)

3.删除索引

        可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

        DROP INDEX index_name ON talbe_name

        ALTER TABLE table_name DROP INDEX index_name

        ALTER TABLE table_name DROP PRIMARY KEY

        其中，前两条语句是等价的，删除掉table_name中的索引index_name。

        第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

        如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

4.如何使用索引

        1、表的主关键字

        2、自动建立唯一索引

        3、表的字段唯一约束

        4、直接条件查询的字段（在SQL中用于条件约束的字段）

        5、查询中与其它表关联的字段

        6、查询中排序的字段（排序的字段如果通过索引去访问那将大大提高排序速度）

        7、查询中统计或分组统计的字段

        8、表记录太少（如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块）

        9、经常插入、删除、修改的表（对一些经常处理的业务表应在查询允许的情况下尽量减少索引）

        10、数据重复且分布平均的表字段（假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。）

        11、经常和主字段一块查询但主字段索引值比较多的表字段

        12、对千万级MySQL数据库建立索引的事项及提高性能的手段

二、选择合适的列建立索引

        1、在where从句，group by从句，order by从句，on从句中的列添加索引

        2、索引字段越小越好（因为数据库数据存储单位是以“页”为单位的，数据存储的越多，IO也会越大）

        3、离散度大的列放到联合索引的前面

        例子：

select * from payment where staff_id =2 and customer_id =584;

        注意:

        是index（staff_id，customer_id）好，还是index（customer_id，staff_id）好，那我们怎么进行验证离散度好了？

        1>分别查看这两个字段中不同的id的数量，数量越多，则表明离散程度越大：因此可以通过下图看出：customer_id 离散程度大。

        结论：由于customer_id 离散程度大，使用index（customer_id，staff_id）好 

        2>mysql联合索引

        ①命名规则 ：表名_字段名

        1、需要加索引的字段，要在where条件中

        2、数据量少的字段不需要加索引

        3、如果where条件中是OR关系，加索引不起作用

        4、符合最左原则

        ②什么是联合索引

1. 两个或更多个列上的索引被称作联合索引，又被称为是复合索引。
2. 利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知 道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

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

三、索引优化SQL的方法

1、索引的维护及优化（重复及冗余索引）

        增加索引会有利于查询效率，但会降低insert，update，delete的效率，但实际上往往不是这样的，过多的索引会不但会影响使用效率，同时会影响查询效率，这是由于数据库进行查询分析时，首先要选择使用哪一个索引进行查询，如果索引过多，分析过程就会越慢，这样同样的减少查询的效率，因此我们要知道如何增加，有时候要知道维护和删除不需要的索引

2、如何找到重复和冗余的索引

        重复索引：

        重复索引是指相同的列以相同的顺序建立的同类型的索引，如下SQL语句中的 primary key和ID列上的索引就是重复索引

create table test(
    id int not null primary key,
    name varchar(10) not null,
    title varchar(50) not null,
    unique(id)
)engine=innodb;

        冗余索引：

        冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，下面这个例子中key（name，id）就是一个冗余索引。

create table test(
    id int not null primary key,
    name varchar(10) not null,
    title varchar(50) not null,
    key(name,id)
)engine=innodb;

        说明：对于innodb来说，每一个索引后面，实际上都会包含主键，这时候我们建立的联合索引，又人为的把主键包含进去，那么这个时候就是一个冗余索引。

3、索引维护的方法

        由于业务变更，某些索引是后续不需要使用的，就要进行删除。

四、注意事项

        设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。设计MySql索引的时候有一下几点注意：

1，创建索引

        对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。

        但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

2，复合索引

        比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;

        如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。

        因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

3，索引对NULL值的列的处理

        只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

        应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

        如下：

select id from t where num is null

        NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引逻辑，有些开发人员完全没有意识到，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默  认值。
        不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列    就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
        此例可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： 

select id from t where num=0

4，使用短索引

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

        例如：title的长度为255，怎么给他添加短索引呢

         添加短索引SQL语句：

ALTER TABLE film add INDEX  index_title(title(6))

5，索引的排序问题

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

6，like语句操作

        一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

7，不要在列上进行运算

        这将导致索引失效而进行全表扫描，例如：

        SELECT * FROM table_name WHERE YEAR(column_name)<2017;

8，不使用NOT IN操作和<> 操作

        这不属于支持的范围查询条件，不会使用索引。

        NOT IN操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替

9，如果条件中有or字段，则要求or的所有字段都必须有索引，否则将不会使用索引