My SQL 性能优化基础
**MySQL性能优化基础**
**1.执行计划**
■ 使用 EXPLAIN 可以查看 SQL 的执行方式。
■ 从 MySQL 5.6.3 开始，可用 EXPLAIN 解释的语句有 SELECT、DELETE、INSERT、REPLACE、UPDATE 。
■ 在 MySQL 5.6.3 之前，可用 EXPLAIN 解释的语句只有SELECT。
■ 执行方法
□ 在查询的开头运行 EXPLAIN。
□ 如）EXPLAIN SELECT * FROM users WHERE id = 1
**2.索引操作**
■ 索引作用
索引是一种可以让SELECT语句提高效率的数据结构，可以起到快速定位的作用。
■ 索引的优缺点
□ 优点：
某些情况下使用select语句大幅度提高效率，合适的索引可以优化MySQL服务器的查询性能，从而起到优化MySQL的作用。
□ 缺点：
表行数据变化时（insert、update、delete），建立在表列上的索引也会自动维护，一定程度上会使DML操作变慢。索引还会占用磁盘额外的存储空间。
■ MySQL索引操作：
□ 给表列创建索引：
● 建表时创建索引：
create table t(id int,name varchar(20),index idx_name (name));
● 给表追加索引：
alter table t add unique index idx_id(id);
● 给表的多列上追加索引
alter table t add index idx_id_name(id,name);
或者：create index idx_id_name on t(id,name);
□ 查看索引使用show语句
● 查看t表上的索引（ mysql中索引也被称作keys ）：
show index from t; 或者：show keys from t;
□ 使用show create table语句查看索引：
show create table t
□ 删除索引：
● 使用alter table命令删除索引：
alter table 表 drop index 索引名
● 使用drop index命令删除索引：
drop index 索引名 on 表
**3.索引原理**
■ 索引原理：
假设有一个学生信息表，设置学号（stu_id）为索引：
索引页之间存在一定的关联关系，一般为树形结构;
分为根节点、分支节点、和叶子节点。
□ 根节点页中存放分段stu_id的起始值，以及值所对应的分支索引页号
□` 分支节点中存放分段stu_id的起始值，以及值所对应的叶子索引页号
□ 叶子节点中存放排序后的stu_id值，该值所对应的表页号, 下一个叶子索引页的页号
□ stu_id建立索引后，执行
select name,sex,height from stu where stu_id=13
查询过程如下：
● 先找索引页号20的根节点，13在>=11和<17的范围内，需要查找25号索引页
● 读取25号索引页，13在>=11和<14范围内，需要查找26号叶子索引页
●
读取26号叶子索引页，找到了13这个值，以及该值所对应表页的页号161，目前只得到了stu_id的值，还要得到name,sex,height等，因此需要再读一次编号为161的表页
● 读取161号表页，获得sname,sex,height等值
以上4步，只读取了3个索引页1个表页，共4个页，比读取所有表页（5000个页），按照stu_id=13挨个翻一遍效率要高，这也是有些情况下索引可以加速查询的原因。
**4.索引扩展**
■ 索引扩展（Index Extensions）
□ 可用 SELECT @@optimizer_switch 来查看
□ 默认为有效：use_index_extensions=on
□ 有效时 InnoDB 的二级索引（Secondary Index）会自动补齐主键
□ 举例说明
CREATE TABLE t1 (
i1 INT NOT NULL DEFAULT 0,
i2 INT NOT NULL DEFAULT 0,
d DATE DEFAULT NULL,
PRIMARY KEY (i1, i2),
INDEX k_d (d)
) ENGINE = InnoDB;
这个t1表包含主键和二级索引 k_d，二级索引 k_d（d）的元组在InnoDB
内部实际被扩展成（d,i1,i2），即包含主键值。因此在设计主键的时候，常见的一条设计原则是要求主键字段尽量简短，以避免二级索引过大（因为二级索引会自动补齐主键字段）。
**-THE END-**
**“**
**”**
作者：明洪哲
审核：梁 空 超
编辑：朱思聪
