什么是索引?
1)索引就好比一本书的目录,它能让你更快的找到自己想要的内容。
2)让获取的数据更有目的性,从而提高数据库检索数据的性能。
给指定字段创建索引,索引会将该字段中所有数据进行排序
索引不是越多越好,创建索引,会占用磁盘空间

索引的排序方式

1)BTREE:B+树索引
2)HASH:HASH索引
3)FULLTEXT:全文索引
4)RTREE:R树索引

BTree

Img
树索引可以分为:
根节点
枝节点
树叶节点
Img
B+tree:
1)在叶子节点添加了相邻节点的指针
2)优化了范围查询,提升了范围查询的执行效率
Img
Img
Img
B*tree:
Img

索引管理

索引分类:

  • 主键索引(聚簇索引)
    特性:唯一且非空

    • 联合索引
  • 唯一键索引
    特性:唯一 可以为空

    • 前缀索引
    • 联合索引
  • 普通索引
    特性:可以不唯一,可以为空

    • 前缀索引
    • 联合索引

索引的增删查

表数据准备:

#创表
root@localhost [test] >create table test.student(
id int,
name varchar(10),
age tinyint,
gender enum('m','f'),
phone char(11)
);
#写入数据
root@localhost [test] >insert into student values
(1,'zls',18,'m',11111111111),
(2,'a',12,'f',11111131111),
(3,'b',13,'f',11241131111),
(4,'a',14,'m',11121131111);
root@localhost [test] >select * from student;
+------+------+------+--------+-------------+
| id   | name | age  | gender | phone       |
+------+------+------+--------+-------------+
|    1 | zls  |   18 | m      | 11111111111 |
|    2 | a    |   12 | f      | 11111131111 |
|    3 | b    |   13 | f      | 11241131111 |
|    4 | a    |   14 | m      | 11121131111 |
+------+------+------+--------+-------------+

主键索引

#增
root@localhost [test] >alter table student add primary key(id);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0
#删
root@localhost [test] >alter table student drop primary key;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0
#查 
root@localhost [test] >desc student;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | NO   | PRI | 0       |       |
| name   | varchar(10)   | YES  |     | NULL    |       |
| age    | tinyint(4)    | YES  |     | NULL    |       |
| gender | enum('m','f') | YES  |     | NULL    |       |
| phone  | char(11)      | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
root@localhost [test] >show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

唯一键索引

#唯一键索引有个必须前提,需对列段数据进行统计去重,如果两个数值直接有差异则无法创建唯一键索引
#统计
root@localhost [test] >select count(age) from student;
+------------+
| count(age) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)
#去重
root@localhost [test] >select count(distinct(age)) from student;
+----------------------+
| count(distinct(age)) |
+----------------------+
|                    4 |
+----------------------+
1 row in set (0.00 sec)
#创建唯一键索引
#不定义索引名字://在创建唯一键索引的时候如果没有自定义名字。那么系统会默认以字段名位名;在删除的时候具有一定的风险
root@localhost [test] >alter table student add unique key(age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test] >show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | age      |            1 | age         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
定义索引名字:
root@localhost [test] >alter table student add unique key un_age(age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
root@localhost [test] >show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | un_age   |            1 | age         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
#删除
没有定义名字的索引删除:
root@localhost [test] >alter table student drop index age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
定义名字的索引删除:
root@localhost [test] >alter table student drop key un_age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
注:删除的时候不管drop后面跟的是index或者key都可以删除

普通索引

## 创建
#不起名情况下以字段名,命名  key和index都可以创建普通索引
root@localhost [test] >alter table student add key id_age(age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
root@localhost [test] >alter table student add index id_age(age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
## 删除
root@localhost [zls] >alter table student drop index id_age;
root@localhost [zls] >alter table student drop key id_age;

前缀索引

什么是前缀索引?
创建索引时,按照指定数值对列进行排序
为什么使用前缀索引?
1)给大列创建索引时,可以减少排序时间,提升创建索引速度
2)insert,update,delete插入数据时,提升写入速度
3)提升查询速率

# 唯一键前缀索引创建
root@localhost [test] >alter table student add unique key id_age(age(1));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
## 普通索引创建
root@localhost [test] >alter table student add  key id_age(age(1));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

联合索引

## 主键联合索引创建
root@localhost [test] >alter table student add primary key(id,name);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

root@localhost [test] >show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | PRIMARY  |            2 | name        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
## 唯一键联合索引创建
root@localhost [test] >alter table student add unique key un_ai(id,age);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test] >show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | un_ai    |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | un_ai    |            2 | age         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
## 普通索引创建联合索引
root@localhost [test] >alter table student add  key id_age(id,age);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test] >show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | id_age   |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | id_age   |            2 | age         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec
## 查询规则
alter table student add index idx_all(a,b,c,d);
where a.女生 and b.身高 and c.体重 and d.身材
index(a,b,c)
特点:前缀生效特性
a,ab,ac,abc,abcd 在搜索以a开头字段的时候可以走索引或部分走索引
b bc bcd cd c d ba ... 在搜索不以a开头字段的时候不走索引

网站访问速度慢,如何排查,如何解决?

可以从以下几方面去分析:
网络问题
设备配置
代码bug
应用(nginx、lb、proxy)
数据库慢查询

使用explain分析SQL语句

慢查询,开启慢查询日志,记录执行速度慢的SQL语句
找出执行慢的SQL语句后,使用explain进行分析

#在my.cnf文件中mysqld添加如下即可:
slow_query_log = 1            # 启用慢查询日志,设置为1表示启用,设置为0表示禁用
slow_query_log_file = /path/to/slow_query.log   # 慢查询日志文件的路径和文件名
long_query_time = 1           # 查询执行时间超过该阈值(单位:秒)将被记录到慢查询日志
#explain语法
root@localhost [test] >explain select * from student;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | student | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

SQL语句执行区别

#从上到下,性能从最差到最好,我们认为至少要达到range级别
index:Full Index Scan,index与ALL区别为index类型只遍历索引树。(全索引扫描)
range:索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
ref:使用非唯一索引扫描或者唯一索引的前缀扫描,返回匹配某个单独值的记录行
eq_ref:类似ref,区别就在使用的索引是唯一索引,只有在连表查询时,使用join on才能出现
const、system:主键精确查询时
NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成
#在查询数据过多时可以使用limit分页查询方式优化结果集

没有走索引的原因?

1)该字段没创建索引
2)创建索引了,但是没走

# 1.使用select * 查询数据不接where条件(杀个程序员祭天优化)
mysql> explain select * from city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4188 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

# 2.查询结果集大于25%
mysql> explain select * from city where population > 1000;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

### 使用limit分页查询方式优化结果集
mysql> explain select * from city where population > 1000 limit 60;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | city  | range | pop           | pop  | 4       | NULL | 4067 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

# 3.使用字段列做计算(大嘴巴子抽前端优化)
mysql> explain select * from city where id-1=10;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

# 4.隐式转换导致索引失效
创建表时,数据类型和查询时不一致导致,不走索引
字符串类型:加引号
mysql> explain select * from mysql.student where phone='21111111111';
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | student | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
数字类型:不加引号
mysql> explain select * from mysql.student where phone=11111111111;
+----+-------------+---------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | student | ref  | sjh           | sjh  | 9       | const |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------+

# 5.使用like模糊查询时,%在前面的
mysql> explain select * from city where countrycode like '%H';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
如果需求是必须百分号在前面,不要使用MySQL 请使用elastsearch

# 6.使用 <> 、 not in查询数据
mysql> explain select * from city where countrycode not in ('CHN','USA');
mysql> explain select * from city where countrycode <> 'CHN' and countrycode <> 'USA';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | city  | ALL  | CountryCode   | NULL | NULL    | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

### 使用limit分页查询方式优化结果集
mysql> explain select * from city where countrycode <> 'CHN' and countrycode <> 'USA' limit 10;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | city  | range | CountryCode   | CountryCode | 3       | NULL | 3439 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+

# 7.联合索引,where条件后面没有按照创建索引的顺序查询
1.按顺序查
2.用户行为分析(数据分析)
# 8.索引损坏,失效
删了索引,重建,使用前缀索引

索引创建原则

1)索引不是越多越好
索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。
修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。
2)有必要添加索引的列,如何选择索引?
删除不再使用或者很少使用的索引
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。

# 1.优先选择,唯一键索引
# 2.给经常要排序、分组这种查询的列,创建联合索引
# 3.普通索引尽量使用前缀索引