查看数据库类型
-- 划分
1.查询密集型 -- read
2.修改密集型 -- deleted,inserted,updated
-- 查询累计插入和返回数据条数
show global status like 'Innodb_rows%';
注意：
当查询修改比为8:2左右时，就可以使用索引来进行优化
索引
索引的作用
索引（index）是帮助MySQL高效获取数据的数据结构（有序）。
索引分类
-- 主键索引
主键约束+提高查询效率
-- 唯一索引
唯一约束+提高查询效率
-- 普通索引
提高查询效率
-- 组合索引
多个字段组成索引，提高查询效率
-- 全文索引
solr，es
-- Hash索引
根据key-value效率高
创建索引
在已有表的字段上创建
-- 创建普通索引
create index 索引名 on 表名(字段);
-- 创建唯一索引
create unique index 索引名 on 表名(字段);
-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2,..);
-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2,..);
在已有表的字段上修改
-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(字段); --默认索引名：primary
-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(字段); -- 默认索引名：字段名
-- 添加普通索引，索引值可以出现多次。
alter table 表名 add index(字段); -- 默认索引名：字段名
创建表时指定
-- 创建学生表
CREATE TABLE student3(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
name VARCHAR(32),
telephone VARCHAR(11) UNIQUE, -- 唯一索引
sex VARCHAR(5),
birthday DATE,
INDEX(name) -- 普通索引
);
查看索引
show index from 表名;
删除索引
-- 直接删除
drop index 索引名 on 表名;
-- 修改表时删除
alter table 表名 drop index 索引名;
查看是否使用索引
explain select 字段1,字段2,... from 表名;
参数说明
type:查询语句的性能
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
possible_keys:SQL查询时用的的索引key:SQL实际决定查询结果的键row:MySql查询时必须检查的行数
| 字段 | 含义 |
|---|---|
| id | select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。 |
| select_type | 表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等 |
| table | 输出结果集的表 |
| type | 表示表的连接类型 |
| possible_keys | 表示查询时，可能使用的索引 |
| key | 表示实际使用的索引 |
| key_len | 索引字段的长度 |
| rows | 扫描行的数量 |
| extra | 执行情况的说明和描述 |
索引的优缺点
-- 优点
提高数据检索的效率，降低数据库的IO成本。
索引底层就是排序，通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
-- 缺点
需花费较多的时间去建立并维护索引。
需要占用一定的物理存储空间。
对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦。
索引创建原则
1.字段内容可识别度不能低于70%，字段内数据唯一值的个数不能低于70%。
2.经常使用where条件搜索的字段。
3.经常使用表连接的字段。
4.经常排序的字段。
索引的数据结构
B+Tree:优化BTree（非叶子节点：索引+指针、叶子节点：索引+数据【地址】）
注意：
非叶子节点：存储索引+指针域
叶子节点：存储索引+数据或者数据的地址值
注意：
1）不同的存储引擎叶子节点存储的内容不一样
有可能是：索引+数据
也有可能是索引+数据的地址值
2）B+Tree是可以允许有冗余索引出现的，每个节点都有索引
注意：
一个索引占8字节，一个指针域占6字节，一个节点总共容量是16KB
一个节点可以存储的元素个数：
16*1024字节 / (8+6)=1170个元素
注意：
一次IO：读取1170个子节点
二次IO：读取1170*1170=135W个子节点
三次IO：读取135W*16=2000W+个元素数据
假设索引+数据域总大小是1KB。而每个节点一共能存储16KB。所以一个第三层一个节点大概可以存储16个元素即16条记录。
B+Tree的好处
- 降低树的高度
- 叶子节点按照索引排好序，支持范围查找，速度会很快
- 千万条数据，只有2次磁盘IO
查看索引节点
-- 查看mysql索引节点大小
show global status like 'innodb_page_size';