Mysql索引和事务优化

1,什么是索引?

索引是通过某算法,构建出一个数据模型,用于快速找出在某个列中有一特定值的行,不使用索引,mysql必须从第一条记录开始读完整个表,直到找到相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,mysql能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么会节省很大一部分时间.

2,索引分为哪些类?按实现方式划分?按功能划分?

按实现方式分为hash索引和b+树索引
按功能划分为单列索引,组合索引,全文索引,空间索引 
单列索引又分为普通索引,唯一索引,主键索引

3,介绍一下按功能划分的索引

1,单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引;
2,普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
​

4,创建索引的所有语法

1,创建表的时候直接指定  
create table stu(
    id int,
    name varchar(20),
    gender varchar(10),
    age int,
    index  index_name(name) --给name列创建索引
)
​
2,直接创建
create index index_gender on stu(gender)
3,修改表结构(添加索引)
alter table student add index index_age(age);
​

5,如何查看数据库所有的索引?

1,查看数据库所有索引
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5'

6,如何查看表中的所有索引?

1,查看表中所有索引
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5' and a.table_name like '%stu%';
2,show index from student;

7,描述删除索引的语法

1,drop index 索引名 on 表名
2,alter table 表名 drop index 索引名 

8,描述一下唯一索引

唯一索引和普通索引类似,不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

9,创建唯一索引的语法

1,创建表的时候直接指定
create table stu(
    id int,
    cou_id varchar,
    phone_num varchar(20),
    unique index_card_id(card_id) -- 给card_id列创建索引  
)
2,直接创建
create unique index index_card_id on stu(card_id)
3,修改表结构(添加索引)
alter table stu add unique index_phone_num(phone_num)

10,什么叫主键索引?

每张表一般都会有自己的主键，当我们在创建表时，MySQL会自动在主键列上建立一个索引，这就是主键索引。主键是具有唯一性并且不允许为NULL，所以他是一种特殊的唯一索引。

11,介绍一下组合索引

组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段，例如同时使用身份证和手机号建立索引，同样的可以建立为普通索引或者是唯一索引。
复合索引的使用复合最左原则。

12,创建组合索引的语法

create index indexname on table_name(column1(length),column2(length))
例如:create index index_phone_name on stu(phone_num,name);
创建索引的基本语法唯一索引:create  unique index index_phone_name on student(phone_num,name)

13,介绍一下全文索引

全文索引的关键字是fulltext,主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询，而不是简单的where语句的参数匹配.用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题

14,简述一下索引的优缺点

1,索引的优点:
大大加快数据的查询速度
使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间
创建唯一索引，能够保证数据库表中每一行数据的唯一性
在实现数据的参考完整性方面，可以加速表和表之间的连接
2,索引缺点
创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加
索引需要占据磁盘空间
对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度

15,创建索引的原则是什么?

1,更新频繁的列不应设置索引
2,数据量小的表不要使用索引
3,重复数据多的字段不应设为索引（比如性别，只有男和女，一般来说：重复的数据超过百分之15就不该建索引）
4,首先应该考虑对where 和 order by 涉及的列上建立索引

16,索引的原理是什么?

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。
换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

17,简述一下索引相关算法的优点和缺点

hash算法:
1,优点：通过字段的值计算的hash值，定位数据非常快。
2,缺点：不能进行范围查找，因为散列表中的值是无序的，无法进行大小的比较。
​
B+树算法:
MySQL数据库默认的索引结构是B+树
优点:
1,更适合磁盘读写：在大型数据库系统中，索引往往不能全部放入内存，需要存储在硬盘上。磁盘的I/O操作相比内存操作要慢得多。B+树的高度比B树低，因此在查找数据时需要进行的磁盘I/O次数会较少。这是为什么在磁盘存储的大数据量场景下，B+树比B树更优秀的一个主要原因。
​
2,更适合范围查询：由于B+树的所有叶子节点都是链表结构，所有数据都存储在叶子节点。因此在进行范围查询时，只需要找到范围的起始点，然后沿着链表顺序遍历即可，效率非常高。而在B树中，由于数据分布在整个树中，范围查询需要进行多次的深度遍历，效率相对较低。
​
3,哈希索引的局限性：哈希索引在处理等值查询时效率非常高，因为可以直接通过哈希函数定位到具体的数据，但是哈希索引却无法进行范围查询和排序操作。此外，由于哈希冲突的问题，哈希索引在某些情况下的查找效率也无法得到保证。
​
4,更有利于索引的维护：由于B+树只有在叶子节点存储数据，当数据发生增加、删除等变动操作时，B+树相比B树和哈希索引在索引的维护上要简单，效率也更高。

18,简述一下各数据引擎的不同

19,mysql中什么是事务?

在MySQL中的事务（Transaction）是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务。事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。事务用来管理 DDL、DML、DCL 操作，比如 insert,update,delete 语句，默认是自动提交的。

20,简述一下mysql中的事务操作

1、开启事务：Start Transaction
2、提交事务：Commit Transaction
3、回滚事务：Rollback Transaction

21,事务的特性有哪些?

1,原子性:事务是一个不可分割的整体,事务开始后的所有操作,要么全部完成,要么全部不做
2,一致性:系统从一个正确的状态迁移到另一个正确的状态
3,隔离性:每个事务的对象对其他事务的操作对象互相分离,事务提交前对其他事务不可见
4,持久性:事务一旦提交,其结果是永久性的

22,事务的隔离级别有哪些?

1,读未提交
2,读提交
3,可重复读
4,序列化

23,说明一下事务的隔离级别

1,读未提交
一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证,会造成脏读。
2,读已提交
一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读
3,可重复读
 就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生，但是会造成幻读。
4,串行
是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

24,mysql可以从哪些地方进行优化?

1,从设计上优化
2,从查询上优化
3,从索引上优化
4,从存储上优化

25,说明一下explain分析执行计划

可以通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

26,用show profile分析sql

Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
例如:通过show  profile for  query  query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间

27,使用索引优化

上面索引部分,通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

28,讲一讲sql优化

(1)主键顺序插入:因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。
(2)如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快.
(3)优化子查询:使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

29,介绍一下mysql视图

1,视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。
2,数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。
3,使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。
​
视图作用:
1,简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。
2,安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图视，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，可以对不同的用户，设定不同的视图。

30,说明一下视图创建的语法

create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]
​
参数说明：
（1）algorithm：可选项，表示视图选择的算法。
（2）view_name ：表示要创建的视图名称。
（3）column_list：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
（4）select_statement
：表示一个完整的查询语句，将查询记录导入视图中。
（5）[with [cascaded | local] check option]：可选项，表示更新视图时要保证在该视图的权限范围之内。

31,说明一下修改视图的语法

alter view 视图名 as select语句

32,说明一下更新视图

某些视图是可更新的。也就是说，可以在UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。如果视图包含下述结构中的任何一种，那么它就是不可更新的：
聚合函数（SUM(), MIN(), MAX(), COUNT()等）
DISTINCT
GROUP BY
HAVING
UNION或UNION ALL
位于选择列表中的子查询
JOIN
FROM子句中的不可更新视图
WHERE子句中的子查询，引用FROM子句中的表。
仅引用文字值（在该情况下，没有要更新的基本表）
​
视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。
​

33,说明一下视图的其他操作

1,重命名视图:
rename table 视图名 to 新视图名;   eg:rename table view1_emp to my_view1
2,删除视图:
drop view 视图名[,视图名…];       eg:drop view if exists view_student;
​