word索引-mysql 索引整理

一、MySQL 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

为何要有索引?

​ 一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的是一些复杂的查询操作，查询速度相当慢。​ 加索引是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

什么是索引？

​索引在MySQL中也叫做“键”或者"key"（primary key，unique key，还有一个index key），是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要，减少io次数，加速查询。

索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

当然索引也并不是越多越好，例如：某台MySQL服务器iostat显示磁盘使用率一直处于100%，经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。

磁盘IO与预读

磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转/min，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms，也就是半圈的时间；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的消耗的时间段下cpu可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难，所以我们要想办法降低IO次数。

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

索引的类型

索引是帮助mysql获取数据的数据结构。最常见的索引是Btree索引和Hash索引。

不同的引擎对于索引有不同的支持：Innodb和MyISAM默认的索引是Btree索引；而Mermory默认的索引是Hash索引。

一、BTree

BTree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，例如

二、Hash

Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

二、查看索引

查看索引

show index from tbl_users; G

查看关键字

show keys from tbl_users; G

三、普通索引

创建索引

CREATE INDEX token ON tbl_users(token)

添加索引(修改表结构)

ALTER table tableName ADD INDEX indexName(columnName)

删除索引

DROP INDEX [indexName] ON mytable;

四、唯一索引

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

添加索引

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):

五、主键

添加主键

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

六、全文本索引

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

七、重建索引的方法

DROP INDEX + RECREATE INDEX.

ALTER TABLE方法

REPAIR TABLE方法：这种方法对于InnoDB存储引擎的表无效。

REPAIR TABLE `users_balance` QUICK;

OPTIMIZE TABLE方法：

查询索引碎片

SHOW TABLE STATUS LIKE 'users_balance' G

如果Data_free大于0 表示有索引碎片产生

可以使用如下语句清理碎片

OPTIMIZE TABLE `logs` -------logs表名称

第三方工具pt-online-schema-change方法：

pt-osc模仿MySQL内部的改表方式进行改表，但整个改表过程是通过对原始表的拷贝来完成的，即在改表过程中原始表不会被锁定，并不影响对该表的读写操作。

首先，osc创建与原始表相同的不包含数据的新表并按照需求进行表结构的修改，然后将原始表中的数据按chunk大小逐步拷贝到新表中，当拷贝完成后，会自动同时修改原始表和新表的名字并默认将原始表删除。

pt-online-schema-change原理

1、如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。

2、创建一个新的表，表结构为修改后的数据表，用于从源数据表向新表中导入数据。

3、创建触发器，用于记录从拷贝数据开始之后，对源数据表继续进行数据修改的操作记录下来，用于数据拷贝结束后，执行这些操作，保证数据不会丢失。

4、拷贝数据，从源数据表中拷贝数据到新表中。

5、修改外键相关的子表，根据修改后的数据，修改外键关联的子表。

6、rename源数据表为old表，把新表rename为源表名，并将old表删除。

7、删除触发器。

添加字段

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25 --alter "ADD COLUMN content text" D=aaa,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

删除字段

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25 --alter "DROP COLUMN content " D=aaa,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute

修改字段

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25 --alter "MODIFY COLUMN age TINYINT NOT NULL DEFAULT 0" D=aaa,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute

字段改名

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25 --alter "CHANGE COLUMN age address varchar(30)" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute

增加索引

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25 --alter "ADD INDEX idx_address(address)" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

删除索引

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25 --alter "DROP INDEX idx_address" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

八、复制表结构

create table select 会将原表中的数据完整复制一份，但表结构中的索引会丢失。

create table like 只会完整复制原表的建表语句，但不会复制数据