MySQL优化，索引和索引的使用和检测索引是否有效

在哪些列上添加索引比较好：

1.比较频繁的作为查询的字段

2.唯一性太差的字段不适合加索引，要找唯一性比较好的

3.更新太频繁的字段不适合做索引

4.不会出现在where中的 不应该建立索引

查询某表的所有索引：

show index from books；

索引的类型：

主键索引：primary key，主键自动成为索引

唯一索引：unique 是自动成为索引的，又有索引，又有唯一性

index：普通索引

fulltext：全文索引，用来分词例如：select * from article where content like ‘%Java%’；但是值得注意的是，中文使用全文索引很少，外语使用全文索引比较多。中文一般使用sphinx+中文分词，只有MYISAM才支持

复合索引：多列合在一起的索引 create index on books（title，price）

如何创建索引：

1.unique和fulltext和index 使用  create unique index 索引名 on books（列名）

2.alter table books add unique index 索引名称（列名）；

如果要添加主键索引：

3.alter table books add primary key （id）；

只有alter table 可以建立无名称的索引，名称默认为表名。

如何删除索引

1.drop index 索引名 on 表名

2.alter table 表明 drop index 索引名

3.主键索引：alter table 表名 drop primarary key

删除未命名的索引：（索引名就是列名），但是如果有索引名，必须按照索引名来删除

显示索引：

show indexes from 表名；

show keys  from 表名

describe 表名；=desc 表名；（普通索引显示成mul）

注意：

1.当查询时where使用了组合索引（多列索引）中的左边的列就自动使用了索引，右边的不会使用索引。

但是查的时候不管使用了多列索引的哪一个（select id from books）就会使用索引。

mysql> explain select * from dept \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dept
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: NULL
1 row in set (0.00 sec)

mysql> explain select * from dept where deptno=1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dept
         type: ref
possible_keys: aa
          key: aa
      key_len: 3
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

mysql> explain select * from dept where dname='1' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dept
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where
1 row in set (0.00 sec)

2.当使用索引查询的时候 ‘%a’不会使用索引，使用‘a%‘会使用索引

mysql> explain select * from dept where deptno like 'a%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | dept  | ALL  | aa            | NULL | NULL    | NULL |   10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from dept where deptno like '%a';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | dept  | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

3.如果条件中带有or 即使条件带了索引也不会使用（不是where id=1 or id =2这种），但是使用and 不受影响，注意有一个左列索引条件or右列索引条件 没有用到索引，但是possible key 用到了。所以要少用or

mysql> explain select * from dept where deptno=1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | dept  | ref  | aa            | aa   | 3       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from dept where deptno=1 or deptno =2;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | dept  | range | aa            | aa   | 3       | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from dept where deptno=1 or dname ='aaa';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | dept  | ALL  | aa            | NULL | NULL    | NULL |   10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from dept where deptno=1 or 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | dept  | ALL  | NULL          | NULL | NULL    | NULL |   10 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from dept where deptno=1 and 1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | dept  | ref  | aa            | aa   | 3       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

4.多列索引如果不是使用的第一列的条件就不会使用索引（刚才证明了）

5.查询查询%开头的不会使用索引

6.如果列类型是字符串 那么一定要把字符串引用起来才会使用索引（如图）

mysql> desc dept;
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| deptno | mediumint(8) unsigned | NO   | MUL | 0       |       |
| dname  | varchar(20)           | NO   |     |         |       |
| loc    | varchar(13)           | NO   | MUL |         |       |
+--------+-----------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> explain select * from dept where loc ="aaa";
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | dept  | ref  | bb            | bb   | 41      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from dept where loc =123;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | dept  | ALL  | bb            | NULL | NULL    | NULL |   10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

如图所示：

7.如果估计mysql在使用全表扫描的时候比使用索引还要快 那就不使用索引

如何检测索引是否有效：

show status like ‘handler_read’,

handler_read_key 越大越好

handler_red_rnd_text越小越好

mysql> show status like 'handler_read%';
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Handler_read_first    | 1         |
| Handler_read_key      | 17        |
| Handler_read_last     | 0         |
| Handler_read_next     | 13        |
| Handler_read_prev     | 0         |
| Handler_read_rnd      | 0         |
| Handler_read_rnd_next | 126000105 |
+-----------------------+-----------+
7 rows in set (0.00 sec)

注：重启控制台就会清空这些。

还是看视频学习比较快！