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的表可以放到不同数据库的不同服务器上
比如:资金、订单、会员三个数据库
水平分库:根据用户水平拆分,水平分库在垂直分库之后
读写分离:缓解数据库的压力,通过代理服务器实现,读还是写交给代理服务器处理