As front-end developers, most of us don’t have much experience with database operations, let alone optimizing database queries.

However, as the front-end of the future, Serverless can serve a large number of businesses now, operating and understanding the database is also necessary. Xinzai thinks that the front end is only the domain of a certain service at present, and any developer in this field should not consider himself as “front end development engineer”, but should be “software development engineer” as the direction of continuous armed wings.

In short: “front and back”, you can slice and dice pixels so that you can manipulate data in SQL so that you can become popular.

Of course, this article is not about the basics of databases, nor is it about first or second paradigms. I will talk about database indexes.

There was an operation and maintenance person who logged into their company’s database through a jumper and wiped out all the data, causing the entire network to become a melon eater. This destructive operation makes users instantly feel abnormal.

Once there was a technical director, because of dissatisfaction with the company, silently deleted the company’s important database database index, and then their home system slowly slowly abnormal.

The former operation is like killing and beheading, very direct. After a kind of operation is a kind of poison, although not immediately cut off the life of the system, but over time, the system slowly lost consciousness, the new data can not come, the old data can not go, with dead also have no difference.

So what exactly is a database index?

And look at my js way to slowly say:

So let’s say I now have 100,000 pre-prepared random numbers, and the conventional storage would be a 100,000 length array of data, which looks something like this.

{id: 0, content: 10116170.397752885}, {id: 1, content: 9690201.810982745}, {id: 2, content: 10116170.397752885}, 5731487.435941743}, {id: 3, content: 11888385.207056893}, {id: 4, content: 11932241.467477538}, {id: 5, content: 11932241.467477538}, {id: 5, content: 2078797.6020045406}, {id: 6, content: 6265169.710994962}, {id: 7, content: 11201556.057179725},.......Copy the code

When I want to query for something with id 100, the most common way to query is through a loop. We’ll write a function like this:

function selectContent(data,key,value){ for(let i =0; i<data.length; i++){ if(data[i][key] === value){ return data[i] } } return null; }Copy the code

That doesn’t seem like a problem. To search for a value with id 100, do this:

let result = selectContent(data,"id",100);Copy the code

The following result is returned:

{id: 100, Content: 12727639.986723457}Copy the code

With this query, I ran 100 consecutive searches for random ids, which took an average of 10 milliseconds. Because of its simple data structure, it does not take too much time. One drawback is that if the ID doesn’t exist, the array will still be iterated 100,000 times, which can be a huge waste of resources to some extent, especially as the array gets longer and longer.

We can compare The Times by indexing them.

First look at the definition of index :(from baidu baike)

An index is a structure that sorts the values of one or more columns of a database table. Using an index allows quick access to specific information in a database table. If you want to look up a particular employee by his or her last name, an index helps you get information faster than if you searched all the rows in a table.

Write vaguely understand. In short, creating an index allows you to create an additional data structure that builds up the key/value relationship without having to iterate through the query as described above, which is easy to understand.

An Object is used to host the data.

Back to the 100,000 pieces of data mentioned above, we create an index for them. The key of the index is ID.

Create an Object and iterate over the array before the query operation. Each key of Object is the ID value of each row in the array.

function buildIndex(data,key){ let re ={}; for(var i in data){ re[data[i][key]]= data[i]; } return re; }Copy the code
let dataIndexForId = buildIndex(data,"id")Copy the code

Next, we use dataIndexForId as the data based query, first write a query function:

function selectContentByIndex(data,key){    return data[key]}Copy the code

In this way, I performed 100 consecutive random ID searches, which took an average of 0 milliseconds, and also solved the problem of wasting resources in the case of non-existent ID values.

Let’s increase the number of queries to 1,000.

The non-indexed query took about 20 milliseconds on my machine, and the indexed query was still 0-1ms.

As you can imagine, if a system is running stably online, when we unintentionally delete the index of the database, the database query will start to slow down.

In fact, it is a very simple principle, but in the actual database processing process, it is certainly much more complicated than what I described, the actual dimension of the query is much more, but its final principle is to abandon the loop traversal, create additional data structure for storage. This will increase memory, but controlling what you store will make even a slight difference. But at least you know how to improve query efficiency in this way, which is used in many front-end application scenarios.

For example, the VUE and React frameworks suggest adding key attributes to list items based on arrays.

VUE: < li v - for = "(item, I) in the list" : the key = "I" > {{item}} < / li >Copy the code
REACT:list.map((item,i)=>{    <li key={i}>{item}</li>})Copy the code

The key is also used to retrieve the contents of the virtual DOM during rendering, rather than iterating through the array repeatedly during rendering.

That’s a simple popular science article. I haven’t updated my official account for a long time, and I think I will be abandoned by Tencent if I don’t write anything. Finally, I salute every medical worker fighting against the coronavirus on the front line, you are all heroes. At the same time, we also pay tribute to every ordinary people who respond to the call of the country and do not go out to cause trouble, you are all heroes ~2020 although the year is not good, everyone is also full of expectations for the future, this is this period of time we dormant in their small corner of the biggest power, 2020 come on ~