• 前言
• 为什么要优化Mysql
  • 如何优化
• 字段设计
• 关联表的设计
• 索引类型
• 索引常见模型
• 查看索引
• 新建索引
• 执行计划explain
• 前缀索引
• 索引的存储结构
• 常见问题之忘记Mysql密码处理
• mysql索引最左前缀原则

前言

前段时间还报了极客学院的mysql教程，中间断了一段时间，偶然发现了mysql教程，记录在此。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型

mysql有哪几种索引？？

主键索引 普通索引 惟一索引 复合索引=联合索引 外建索引 全文索引

普通索引会引入change buffer,可加速数据更新，但实时性稍微差些，也就是适用写多读少、且页面写完以后马上读取的概率较小的场景。当然change buffer也可以关掉，建议能和这个类型的索引的地方尽量用这个。

为什么要优化Mysql

• 系统的吞吐量很多时候在数据库的访问速度上
• 随着应用程序的增多，数据库中存放的数据会越来越多，处理时间会相应变慢
• 数据存放在磁盘上，读写速度无法与内存的速度相比

针对这三点可以做优化

如何优化

• 设计数据库时：数据库表、字段的设计，存储引擎
• 利用好MYSQL自身提供的功能，如索引等
• 横向扩展：MYSQL集群、负载均衡、读写分离等
• SQL语句的优化

其实这些知识，感觉即使只有数据库的基础知识，也能提炼出一部分，关键的是提炼总结的能力。

字段设计

定长char，非定长varchar,text

尽可能使用not null，需要额外空间存储

单表字段不宜过多，二三十个极限

关联表的设计

外键foregin key只能实现一对一、一对多的映射；多对多单独新建一张表，将拆成两个一对多

索引类型

普通索引（对关键字没有要求）、主键索引（要求关键字唯一且不能为null）、唯一索引（要求记录的关键字不能重复）、全文索引

索引常见模型

哈希表、有序数组、搜索树

查看索引

show create table 表名；
OR
desc 表名;

新建索引

• 创建表后建立索引

  create TABLE user_index(
    id int auto_increment primary key,
    first_name varchar(16),
    last_name VARCHAR(16),
    id_card VARCHAR(18),
    information text
  );
  -- 更改表结构
  alter table user_index
  -- 创建一个first_name和last_name的复合索引，并命名为name
  add key name (first_name,last_name),
  -- 创建一个id_card的唯一索引，默认以字段名作为索引名
  add UNIQUE KEY (id_card),
  -- 鸡肋，全文索引不支持中文
  add FULLTEXT KEY (information);
  

• 创建表时指定索引

  CREATE TABLE user_index2 (
    id INT auto_increment PRIMARY KEY,
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
  );
  

• 删除索引

  alter table 表名 drop KEY 索引名
  

执行计划explain

CREATE TABLE innodb1 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);
insert into innodb1 (first_name,last_name,id_card,information) values ('张','三','1001','华山派');

上述新建的表，现在在执行sql时，前面可以先加个explain，看下执行计划，如：

EXPLAIN select * from innodb1 where id<0;

建立基础索引：在where、order by、join字段上建立索引

优化，组合索引：基于业务逻辑

如果条件经常性出现在一起，那么可以考虑将多字段索引升级为==复合索引==

如果通过增加个别字段的索引，就可以出现==索引覆盖==，那么可以考虑为该字段建立索引

查询时，不常用到的索引，应该删除掉

前缀索引

索引的存储结构

• btree

• b+ btree
  聚簇结构（也是在BTree上升级改造的）中，关键字和记录是存放在一起的。

在MySQL中，仅仅只有Innodb的==主键索引为聚簇结构==，其它的索引包括Innodb的非主键索引都是典型的BTree结构。

• 哈希索引 在索引被载入内存时，使用哈希结构来存储。

更多访问

常见问题之忘记Mysql密码处理

[root@backups1 mysql5.7]# bin/mysqld_safe --skip-grant-tables --user=mysql
connect to your mysql without password.
mysql> flush privileges;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> flush privileges;
#然后重启

mysql索引最左前缀原则