MYSQL索引的创建与设计原则

1、索引

1.1、创建索引

• 普通索引

ALTER TABLE table_name ADD INDEX index_name (column); 

• 主键索引 添加PRIMARY KEY 

ALTER TABLE table_name ADD PRIMARY KEY (column); 

• 唯一索引 添加UNIQUE 

ALTER TABLE ‘table_name’ ADD UNIQUE (column); 

• 全文索引 添加FULLTEXT 

ALTER TABLE table_name ADD FULLTEXT (column); 

• 多列索引 

ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3);

索引的优点：极大地加速了查询，减少扫描和锁定的数据行数

索引的缺点：占用磁盘空间，减慢了数据更新速度，增加了磁盘IO

1.2、添加索引的原则

• 字段的数值有唯一性的限制，推荐添加唯一索引

如果一个字段具有唯一性，我们既没有加唯一约束，又没有加唯一索引就不好了，此时使用这个字段去查，性能和数据的完整性都是较差的。所以我们在添加索引的时候，首先要为有 具有唯一性的字段添加索引。

• 频繁作为where查询条件的字段

建立索引本质上是提前使用b+树按照指定的字段作为索引进行升序或降序排序。如果where后面的查询条件是索引的话，显而易见的是可以利用b+树独特 二分查找 的查询检索优势优化查询速度。

• 经常使用group by 和order by的字段

索引本身就是已经排好序的， 使用order by 和group by (相同的已经排在一起了)自然会提高效率。如果两者同时使用，则可以创建联合索引，分组的字段在前，排序的字段在后，顺序不同可能不会使用联合索引，因为sql执行顺序分组在前，排序在后。

• UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。

原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

• DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。 因为b+树是按照索引字段进行排序的，而排序好的字段去重效率大大增加。如果没有索引，首先需要将相同的字段记录都找出来，然后再去重。

• 多表 JOIN 连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增 长会非常快，严重影响查询的效率。

其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下， 没有 WHERE 条件过滤是非常可怕的。

最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。如果不一致，如一个为int，一个为varchar，如果可以默认转换，这时会使用函数，索引会失效

1.3、不适合创建索引的情况

• 表记录少
• 经常插入、删除、修改的表
• 数据重复的字段
• 经常与主字段一块查询但主字段索引值比较多的字段

1.4、索引检索为什么快

关键字相对于数据本身，数据量小，关键字是有序的，二分查找可快速的确定位置

• 普通索引：对关键字没有限制
• 唯一索引：要求记录提供的关键字不能重复
• 主键索引：要求关键字唯一且不能重复

1.5、like字段添加索引

select * from user where name like 'shen%';（可以添加索引）
select * from user where name like '%shen';（不可以添加索引）

1.6、主键与唯一索引的区别

• 主键是一种约束，唯一索引是一种索引
• 主键不能有空值，唯一索引可以为空
• 主键可以是其他表的外键，唯一索引不可以
• 一个表只可以有一个主键，索引可以有多个

1.7、mysql优化

• 尽量避免全表扫描，首先考虑在where与order by列上建索引，尽量避免在where子句中对字段进行null判断
• 避免使用!=、<>、or、in、not in、%前缀模糊查询、对字段进行函数操作，否则引擎放弃索引而进行全表扫描
• sql优化，架构成面: 主从复制、读写分离、分库分表

<、<=、>、>=、in、between、like '123%' 操作符可以添加索引

1.8、drop、delete、truncate区别

• drop 直接删掉表（删除表数据同时删除表结构，将表所占的空间都释放掉，删除效率最高）
• delete 删除表中的数据，可以加where子句（仅删除表数据，支持条件过滤，支持回滚，记录日志）
• truncate 删除表中的数据，再次添加数据自增长id从1开始（仅删除表数据，不支持条件过滤，不支持回滚，不支持记录日志）

2、mysql的数据导入导出

• 服务器连接mysql数据库

mysql -h172.21.54.220 -P60001 -uccshen -p
password:1234abcd

• 服务器连接Db2数据库

db2 connect to mcsvcdb user ccshen
password:1234abcd

• mysql服务器导出语句

mysql --default-character-set=gbk --quick -h172.21.24.220 -P60001 -uccshen -p$1 -Dmcmgmdb  -NBe "select concat('\"',concat_ws('\",\"',a,b,c),'\"') from user_info" >>user_info.del

• concat与concat_ws区别

select concat('大','小') as size from user
-- 大小

select concat('大',NULL) as size from 表
：null

• concat中有一个参数为NULL，查出来的就为NULL

select concat_ws('_','大','小','中') as size from 表
-- 大_小_中
select concat_ws('_','大','小',NULL) as size from 表
-- 大_小 

• mysql服务器导出语句

load data local infile 'user_info.del' into table user_info FILES TERMINATED BY ',' ENCLOSED by '"'(a,c,b);

• 查询文件中的号码

less abnc.java | grep 18895315906
less server*.log | grep 18895315906

3、mysql的事务

        3.1、4种事务特性

• 原子性：语句要么全执行，要么全不执行，实现主要基于undo log日志实现的

• 持久性：保证事务提交后不会因为宕机等原因导致数据的丢失，实现主要基于redo日志实现的
• 隔离性：保证事务执行之间互不影响
• 一致性：事务的执行的前后数据的完整性保持一致

3.2、并发的情况下，mysql同时读写可能会导致脏读、不可重复读、幻读

• 脏读 :一个事务读到了另一个事务的未提交的数据
• 不可重复读 :一个事务读到了另一个事务已经提交的 update 的数据导致多次查询结果不一致.
• 虚幻读 :一个事务读到了另一个事务已经提交的 insert 的数据导致多次查询结果不一致.

3.3、四个隔离级别

• 未提交读（read uncommited） :脏读，不可重复读，虚读都有可能发生
• 已提交读 （read commited）:避免脏读。但是不可重复读和虚读有可能发生
• 可重复读 （repeatable read） :避免脏读和不可重复读.但是虚读有可能发生.
• 串行化的 （serializable） :避免以上所有读问题.

mysql 默认:可重复读
oracle 默认:读已提交

4、分区、分库、分表

• 分区：就是把一张表的数据分成N个区块，表面看只是一张表，底层是N个物理区块组成

水平分区（一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录）
垂直分区（一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度）

• 分表：一张表按照一定的规则分成N个表（主键划分）
• 分库：一旦分表，数据库的表越来越多，要分库

数据库是一个图书馆，一张表就是一本书，书的目录就是分区

• 分区的实现：

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    amount DOUBLE NOT NULL,
    order_day DATETIME NOT NULL,
    PRIMARY KEY(id, order_day)
) ENGINE=Innodb 
PARTITION BY RANGE(YEAR(order_day)) (
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_catchall VALUES LESS THAN MAXVALUE);

• 分库思路：垂直分库-->水平分库-->读写分离

垂直分库：系统中不存在关联关系的表或join的表可以放到不同数据库的不同服务器上
比如：资金、订单、会员三个数据库

水平分库：根据用户水平拆分，水平分库在垂直分库之后

读写分离：缓解数据库的压力，通过代理服务器实现，读还是写交给代理服务器处理